Running a MySQL seed file in node js

Question

Summary

I have a schema file I use to set up my database and a seed file I use to populate it with some starter data. I'm running them in MySQL Workbench with no errors. When I try running them using the reset function I made in my SQL wrapper class, I get an SQL syntax error.

The Code

// I'd name this SQLHelper but it'd be too long. 
// I'd name it MySQL but... well, you know. 
import mysql2 from "mysql2";
import fs from "fs";

import SECRETS from "../secrets.js";

/* == MISC == */
const SQL_CONFIG = {
    host: 'localhost',
    user: 'root',
    password: SECRETS.SQL,
    database: 'employee_tracker'
}

/* == JOESQL HELPER CLASS == */
class JoeSQL {
    /* == CONSTRUCTOR == */
    constructor() {
        this.connection = null;
        return this;
    }

    /* == SUGAR FUNCTIONS == */
    // Connect to the DB
    connect() {
        return this.connection = mysql2.createConnection(SQL_CONFIG);
    }

    // Close the DB connection
    end() {
        this.connection.end();
    }

    /* == MAIN FUNCTIONS == */
    // Many removed for simplicity

    // FIXME
    reset() {
        const schema = fs.readFileSync("db/schema.sql", {encoding: "utf-8"});
        const seed = fs.readFileSync("db/seed.sql", {encoding: "utf-8"});
        
        this.connect();
        
        this.connection.query(schema);
        this.connection.query(seed);

        this.end();
    }
}

export default JoeSQL;

Behavior

Expected:

I expect this to load the schema and seed files into variables, open a connection to the database, send the data as queries, and have the database update based on those changes.

Actual:

The code does open the files, parse the data as a string, and load it into a query to the SQL server, but there is a syntax error. This syntax error not being present in workbench, I don't know how to move forward.

The Error

$ npm run seed

> employee-tracker@1.0.0 seed
> node db/reset.js

node:events:491
      throw er; // Unhandled 'error' event
      ^

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE DATABASE employee_tracker;

USE employee_tracker;

CREATE TABLE departmen' at line 3
    at Packet.asError (C:\Users\joeps\coding\homework\employee-tracker\node_modules\mysql2\lib\packets\packet.js:728:17)
    at Query.execute (C:\Users\joeps\coding\homework\employee-tracker\node_modules\mysql2\lib\commands\command.js:29:26)
    at Connection.handlePacket (C:\Users\joeps\coding\homework\employee-tracker\node_modules\mysql2\lib\connection.js:456:32)
    at PacketParser.onPacket (C:\Users\joeps\coding\homework\employee-tracker\node_modules\mysql2\lib\connection.js:85:12)
    at PacketParser.executeStart (C:\Users\joeps\coding\homework\employee-tracker\node_modules\mysql2\lib\packet_parser.js:75:16)
    at Socket.<anonymous> (C:\Users\joeps\coding\homework\employee-tracker\node_modules\mysql2\lib\connection.js:92:25)
    at Socket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
Emitted 'error' event on Query instance at:
    at Query.execute (C:\Users\joeps\coding\homework\employee-tracker\node_modules\mysql2\lib\commands\command.js:39:14)
    at Connection.handlePacket (C:\Users\joeps\coding\homework\employee-tracker\node_modules\mysql2\lib\connection.js:456:32)
    [... lines matching original stack trace ...]
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlState: '42000',
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE DATABASE employee_tracker;\n" +
    '\n' +
    'USE employee_tracker;\n' +
    '\n' +
    "CREATE TABLE departmen' at line 3",
  sql: 'DROP DATABASE IF EXISTS employee_tracker;\n' +
    '\n' +
    'CREATE DATABASE employee_tracker;\n' +
    '\n' +
    'USE employee_tracker;\n' +
    '\n' +
    'CREATE TABLE department (\n' +
    '    id INT AUTO_INCREMENT PRIMARY KEY,\n' +
    '    name VARCHAR(30) UNIQUE NOT NULL\n' +
    ');\n' +
    '\n' +
    'CREATE TABLE role (\n' +
    '    id INT AUTO_INCREMENT PRIMARY KEY,\n' +
    '    title VARCHAR(30) NOT NULL,\n' +
    '    salary DECIMAL NOT NULL,\n' +
    '    department_id INT NOT NULL,\n' +
    '    INDEX dp_ind (department_id),\n' +
    '    CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES department(id) ON DELETE CASCADE\n' +
    ');\n' +
    '\n' +
    'CREATE TABLE employee (\n' +
    '    id INT AUTO_INCREMENT PRIMARY KEY,\n' +
    '    first_name VARCHAR(30) NOT NULL,\n' +
    '    last_name VARCHAR(30) NOT NULL,\n' +
    '    role_id INT NOT NULL,\n' +
    '    INDEX role_ind (role_id),\n' +
    '    CONSTRAINT fk_role FOREIGN KEY (role_id) REFERENCES role(id) ON DELETE CASCADE,\n' +
    '    manager_id INT,\n' +
    '    INDEX manager_ind (manager_id),\n' +
    '    CONSTRAINT fk_manager FOREIGN KEY (manager_id) REFERENCES employee(id) ON DELETE SET NULL\n' +
    ');'
}

Postscript

My package.json contains a script seed that runs node /db/index.js.

/db/index.js is a two-line file (well 4 with linebreaks): an import statement, and new JoeSQL().reset().

All of this is a means to an end of being able to reset my database using an npm script. If there's a better option (and I'm betting there is), I'd love to switch to that because I'm going a bit crazy.

You've got multiple MySQL statements? Let's see the code where you've put the MySQL queries in. — FanoFN, Oct 15 '22 at 08:46
@FanoFN Yes, my schema and seed files contain multiple statements. I think that may be a key factor, since all my other mysql2 queries work and only make use of one sql statement. The schema and seed run correctly in workbench. The schema and seed are individually hyperlinked at the top of the question. The Repo code can be found at the hyperlinked header "the code". I only posted the node code of my attempting to run the files as I know the files in isolation are correct; again, no errors in workbench, same file. Therefore it must be how I'm running in it javascript. I think... — JShoe, Oct 15 '22 at 16:10
I know that `mysql` for node js have one option of `multipleStatements: true;` which allows multiple mysql statements execution as I do have a few old node apps still using it but I'm not sure if `mysql2` have the same thing. However, using the option is very discouraged due to sql injection. — FanoFN, Oct 16 '22 at 07:47

JShoe · Accepted Answer · 2022-10-16T19:10:06.240

I managed to find the answer here. I broke it up into two functions since I was reading and concatenating from two files. The functions are below, along with my fixed reset() code.

Helper functions

const parseSqlFile = (sqlFile) => {
    return sqlFile
        .toString()
        .replace(/(\r\n|\n|\r)/gm," ") // remove newlines
        .replace(/\s+/g, ' ') // excess white space
        .split(";") // split into all statements
}

const removeEmptyQueries = (queries) => {
    return queries
        .filter(q => q.length)
        .filter(q => q != ' ');
}

Reset()

// I'd name this SQLHelper but it'd be too long. 
// I'd name it MySQL but... well, you know. 
import mysql2 from "mysql2";
import fs from "fs";

import SECRETS from "../secrets.js";

/* == MISC == */
const SQL_CONFIG = {
    host: 'localhost',
    user: 'root',
    password: SECRETS.SQL,
    database: 'employee_tracker'
}

/* == JOESQL HELPER CLASS == */
class JoeSQL {
    /* == CONSTRUCTOR == */
    constructor() {
        this.connection = null;
        return this;
    }

    /* == SUGAR FUNCTIONS == */
    // Connect to the DB
    connect() {
        return this.connection = mysql2.createConnection(SQL_CONFIG);
    }

    // Close the DB connection
    end() {
        this.connection.end();
    }

    /* == MAIN FUNCTIONS == */
    // Many removed for simplicity

   reset() {
        const schema = parseSqlFile(fs.readFileSync("db/schema.sql"));
        const seed = parseSqlFile(fs.readFileSync("db/seed.sql"));

        const queries = removeEmptyQueries([...schema, ...seed]);
        
        this.connect();

        queries.forEach(q => {
            this.connection.query(q);
        });

        this.end();
    }
}

export default JoeSQL;