Running SQL Queries from a ‘.sql’ file in NodeJS (SQLite)

credit: https://i0.wp.com

In this post, I will show how I was able to get my NodeJS app to run my SQL queries from a SQL file using sqlite3 and NodeJS’ fs APIs. Even if you are not using SQLite, you might still find the technique I used here helpful (you can just skip the SQLite specific parts).

Background: A SQL file that I was required to use to get my SQLite database up and running had precisely 195,369 lines of SQL code! I needed to get those queries to the DB and the best way would be programmatically, but how?

If you find yourself in this situation, this post is about how I was able to complete this challenge safely and on time.

The following are the steps I took.

Step 1, install the NPM package called sqlite3 (Read the sqlite3 docs here). sqlite3 helps you to connect to your SQLite database and to run queries.

Step 2, In your the JS file where you want to run the SQLs, import/require sqlite3 and fs (No, you don’t need to install this one. It comes with NodeJS).

Step 3, Setup the in-memory database connection, like this:

let db = new sqlite3.Database('mydatabase', (err) => {
   if (err){
       return console.error(err.message);
   }
   console.log('Connected to the in-memory SQlite database.');
});

Close connection, like this:

db.close((err) => {
  if (err) {
    return console.error(err.message);
  }
  console.log('Closed the database connection.');
});

Step 4, run the file via node db.js or whatever, to see that the setup actually works.

Step 5, Read and parse the SQL queries as a string into your JS file using fs, like this:

const dataSql = fs.readFileSync('./data.sql').toString();

At this point, the whole 195,369 lines of the SQL queries were available to me in thedataSql variable.

Here is how I put them all together:

// Require or import the dependencies
const fs = require('fs');
const sqlite3 = require('sqlite3').verbose();

// Read the SQL file
const dataSql = fs.readFileSync('./data.sql').toString();

// Setup the database connection
let db = new sqlite3.Database('mydatabase', (err) => {
  if (err) {
    return console.error(err.message);
  }
  console.log('Connected to the in-memory SQLite database.');
});

// Convert the SQL string to array so that you can run them one at a time.
// You can split the strings using the query delimiter i.e. `;` in // my case I used `);` because some data in the queries had `;`.
const dataArr = dataSql.toString().split(');');

// db.serialize ensures that your queries are one after the other depending on which one came first in your `dataArr`
db.serialize(() => {
  // db.run runs your SQL query against the DB
  db.run('PRAGMA foreign_keys=OFF;');
  db.run('BEGIN TRANSACTION;');
  // Loop through the `dataArr` and db.run each query
  dataArr.forEach((query) => {
    if(query) {
      // Add the delimiter back to each query before you run them
      // In my case the it was `);`
      query += ');';
      db.run(query, (err) => {
         if(err) throw err;
      });
    }
  });
  db.run('COMMIT;');
});

// Close the DB connection
db.close((err) => {
  if (err) {
    return console.error(err.message);
  }
  console.log('Closed the database connection.');
});

If you are having a hard time reading the code above, here is a Github gist for you.

Yeah. That’s it! On running node index.js, the queries ran and the things I needed where loaded into my SQLite database.

If you have any question or suggestion, just leave me a comment below. If you found this write-up interesting, don’t forget to encourage me ;)

Thanks for reading!

Acknowledgement: I found this website very helpful https://www.sqlitetutorial.net/sqlite-nodejs/. Check it out.