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

> [email protected] 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.

Answer 1

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;