Db.raw() does it strip tags?

I am trying to create a stored procedure in MySQL with db.raw(), but the new lines are stripped out, which causes the following SQL to fail.

DROP PROCEDURE IF EXISTS fill_date_dimension;
    DELIMITER //
    CREATE PROCEDURE fill_date_dimension(IN startdate DATE,IN stopdate DATE)
    BEGIN
        DECLARE currentdate DATE;
        SET currentdate = startdate;
        WHILE currentdate < stopdate DO
            INSERT INTO dates VALUES (
                            YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate),
                            currentdate,
                            YEAR(currentdate),
                            MONTH(currentdate),
                            DAY(currentdate),
                            QUARTER(currentdate),
                            WEEKOFYEAR(currentdate),
                            DATE_FORMAT(currentdate,'%W'),
                            DATE_FORMAT(currentdate,'%M'),
                            'f',
                            CASE DAYOFWEEK(currentdate) WHEN 1 THEN 't' WHEN 7 then 't' ELSE 'f' END,
                            NULL);
            SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);
        END WHILE;
    END
    
    //
    DELIMITER ;
    TRUNCATE TABLE dates;
    CALL fill_date_dimension('2018-01-01','2030-12-31');
    OPTIMIZE TABLE time_dimension;

The reason is that DELIMITER // has to be on its own line.

Is there any way I can run db.raw() and tell it to just trust me and not mess around with my code?

1 Like

So it turns out the problem is not related to the client, but to the database engine which cannot accept multiple commands on one line.

In the end, I found a different way of doing the same thing without using a procedure, but I had to execute each line of code as a separate command like so.

await db.raw(`DROP TABLE IF EXISTS numbers_small;`)
    await db.raw(`DROP TABLE IF EXISTS numbers_small;`)
    await db.raw(`CREATE TABLE numbers_small (number INT);`)
    await db.raw(`INSERT INTO numbers_small VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);`)
    await db.raw(`DROP TABLE IF EXISTS numbers;`)
    await db.raw(`CREATE TABLE numbers (number BIGINT);`)
    await db.raw(`INSERT INTO numbers
    SELECT thousands.number * 1000 + hundreds.number * 100 + tens.number * 10 + ones.number
      FROM numbers_small thousands, numbers_small hundreds, numbers_small tens, numbers_small ones
    LIMIT 1000000;`)
    await db.raw(`DROP TABLE IF EXISTS dates;`)
    await db.raw(`CREATE TABLE dates (
      date_id          BIGINT PRIMARY KEY, 
      date             DATE NOT NULL,
      timestamp        BIGINT, 
      weekend          CHAR(10) NOT NULL DEFAULT "Weekday",
      day_of_week      CHAR(10),
      month            CHAR(10),
      month_day        INT, 
      year             INT,
      week_starting_monday CHAR(2),
      UNIQUE KEY date (date),
      KEY year_week (year,week_starting_monday)
    );`)
    await db.raw(`INSERT INTO dates (date_id, date)
    SELECT number, DATE_ADD( '2016-01-01', INTERVAL number DAY )
      FROM numbers
      WHERE DATE_ADD( '2016-01-01', INTERVAL number DAY ) BETWEEN '2016-01-01' AND '2030-01-01'
      ORDER BY number;`)
    await db.raw(`UPDATE dates SET
    timestamp =   UNIX_TIMESTAMP(date),
    day_of_week = DATE_FORMAT( date, "%W" ),
    weekend =     IF( DATE_FORMAT( date, "%W" ) IN ('Saturday','Sunday'), 'Weekend', 'Weekday'),
    month =       DATE_FORMAT( date, "%M"),
    year =        DATE_FORMAT( date, "%Y" ),
    month_day =   DATE_FORMAT( date, "%d" );`)
    await db.raw(`UPDATE dates SET week_starting_monday = DATE_FORMAT(date,'%v');`)
1 Like