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.
// 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;
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.
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.
$ 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' +
');'
}
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.
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.
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 != ' ');
}
// 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;