A SQLite Tutorial with Node.js

In this tutorial I will be demonstrating how to use SQLite in combination with JavaScript inside the Node.js environment with the help of the sqlite3 Node.js driver. For those not familiar with SQLite, it is a simple single file relational database that is very popular among smart devices, embedded systems, and even small web application.

Setup and Installation

I will begin by creating a new npm package using npm init inside an empty directory called node-sqlite-tutorial.

$ npm init
This utility will walk you through creating a package.json file.
It only covers the most common items, and tries to guess sane defaults.

See `npm help json` for definitive documentation on these fields
and exactly what they do.

Use `npm install <pkg> --save` afterwards to install a package and
save it as a dependency in the package.json file.

Press ^C at any time to quit.
name: (app) node-sqlite
version: (0.0.0) 0.1.0
description: Code for tutorial blog on node and sqlite
entry point: (index.js) main.js
test command: 
git repository: 
keywords: 
author: Adam McQuistan
license: (BSD) MIT
About to write to /node-sqlite/app/package.json:

{
  "name": "node-sqlite",
  "version": "0.1.0",
  "description": "Code for tutorial blog on node and sqlite",
  "main": "main.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "repository": "",
  "author": "Adam McQuistan",
  "license": "MIT"
}


Is this ok? (yes) 

Next I will need to install sqlite3 package via npm like so:

$ npm install --save sqlite3

In addition to sqlite3 I am going to install Bluebird so that I can use the familiar promise functionality in my database programming.

$ npm install --save bluebird

I will now create an empty file right next to the package.json file called database.sqlite3 that SQLite will store data in.

Designing the Database

As with almost every one of my other articles I will be using a made up application to help describe some of the important aspects of database programming with Node.js and SQLite. For this article I am going with the assumption that I am building out the data access layer for a project and task tracking application. The basic business rules for this application's data access layer are as follows:

• The application has projects
• Each project can have one or more tasks to complete

With the business rules stated I can take that info and start designing the necessary tables and their fields. It is clear that I will need a projects table as well as a tasks table. For the rest I will just use a little intuition, some made up test data, and roll with it (a common work characteristic for most developers).

projects table

id	name
1	Write Node.js - SQLite Tutorial

tasks table

id	name	description	isCompleted	projectId
1	Outline	High level overview of sections	1	1
2	Write	Write article contents and code examples	0	1

Ok, now that I know what I need to create I can now translate that into code.

Creating the Database

To start I will need to make a main.js file along with a dao.js (or Data Access Object) file in the same directory as the package.json file.

Inside dao.js, I will add imports for sqlite3 and Bluebird's Promise objects. After that I will scaffold out a data access class called AppDAO that will establish a connection to the database inside a constructor and assign it to a member field called db.

// dao.js

const sqlite3 = require('sqlite3')
const Promise = require('bluebird')

class AppDAO {
  constructor(dbFilePath) {
    this.db = new sqlite3.Database(dbFilePath, (err) => {
      if (err) {
        console.log('Could not connect to database', err)
      } else {
        console.log('Connected to database')
      }
    })
  }
}

module.exports = AppDAO

The connection is pretty straight forward. You just instantiate the sqlite3 Database class constructor by passing it the path to the SQLite database file you want to connect with and optionally check for errors that might occur. As noted above I am storing this connection object in a field called db on the AppDAO class.

I will progress by explaining how to use the connection object to submit queries to the database. The sqlite3 Node.js package gives a handful of different methods for executing queries, but the ones I will be focusing on in this tutorial are:

• run: used to create or alter tables and to insert or update table data
• get: select a single row of data from one or more tables
• all: select multiple rows of data from one or more tables

To begin I'd like to explore the run method. Its general syntax looks like this:

db.run('SOME SQL QUERY', [param1, param2], (err) => {
  if (err) {
    console.log('ERROR!', err)
  }
})

The first parameter passed to run(...) is a string of SQL to be executed and is the only required parameter. The second is an optional array of parameters that the sqlite3 library will swap in for any '?' placeholders within the query (I'll demonstrate this in a bit). The final is an error callback function.

As you might suspect I will be using the run(...) function to create and update my projects and tasks. However, I am actually going to wrap it in my own version of a run method on the AppDAO class because I would like to encapsulate it in a bluebird Promise to make things explicitly asynchronous and promise-based like so:

// dao.js

const sqlite3 = require('sqlite3')
const Promise = require('bluebird')

class AppDAO {
  // omitting constructor code
  
  run(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.run(sql, params, function (err) {
        if (err) {
          console.log('Error running sql ' + sql)
          console.log(err)
          reject(err)
        } else {
          resolve({ id: this.lastID })
        }
      })
    })
  }
}

With my custom AppDAO.run(...) method I can now put it to use to create the products and tasks tables.

To begin, I add two more files to my project called project_repository.js and task_repository.js. Inside of project_repository.js I define a class called ProjectRepository which has a constructor that accepts an instance of the AppDAO object and a createTable method which executes some DDL (Data Definition Language) SQL like so:

// project_repository.js

class ProjectRepository {
  constructor(dao) {
    this.dao = dao
  }

  createTable() {
    const sql = `
    CREATE TABLE IF NOT EXISTS projects (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT)`
    return this.dao.run(sql)
  }
}

module.exports = ProjectRepository;

Then I do essentially the same thing again but, this time in the task_repository.js file.

// task_repository.js

class TaskRepository {
  constructor(dao) {
    this.dao = dao
  }

  createTable() {
    const sql = `
      CREATE TABLE IF NOT EXISTS tasks (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        description TEXT,
        isComplete INTEGER DEFAULT 0,
        projectId INTEGER,
        CONSTRAINT tasks_fk_projectId FOREIGN KEY (projectId)
          REFERENCES projects(id) ON UPDATE CASCADE ON DELETE CASCADE)`
    return this.dao.run(sql)
  }
}

module.exports = TaskRepository;

The DDL SQL for creating the tables is complete, so I will move on to methods for inserting data into the tables.

Inserting Data

In the ProjectRepository class I need to add a create method that receives the name of the project to create and executes the appropriate INSERT statement using the AppDAO.run(...) method. Notice how I have used '?' to represent the value for the name of the project and then put the name parameter in the optional params array argument to the run(...) method. This is known as a parameterized query statement which will cleanse the inputs to minimize SQL injection risks.

// project_repository.js

class ProjectRepository {
  // omitting other methods

  create(name) {
    return this.dao.run(
      'INSERT INTO projects (name) VALUES (?)',
      [name])
  }
}

module.exports = ProjectRepository;

A similar create method is needed for the TaskRepository class.

// task_repository.js

class TaskRepository {
  // omitting other methods

  create(name, description, isComplete, projectId) {
    return this.dao.run(
      `INSERT INTO tasks (name, description, isComplete, projectId)
        VALUES (?, ?, ?, ?)`,
      [name, description, isComplete, projectId])
  }
}

module.exports = TaskRepository;

Now that I have the ability to INSERT data into the database I would like to add the functionality to update it.

Updating Data

In the ProjectRepository class I will add an update method that takes a project object and updates all of the fields for that project's database record again utilizing the AppDAO.run(...) method, like so:

// project_repository.js

class ProjectRepository {
  // omitting other methods

  update(project) {
    const { id, name } = project
    return this.dao.run(
      `UPDATE projects SET name = ? WHERE id = ?`,
      [name, id]
    )
  }
}

module.exports = ProjectRepository;

Next up is to add the corresponding update method to TaskRepository class.

// task_repository.js

class TaskRepository {
  // omitting other methods

  update(task) {
    const { id, name, description, isComplete, projectId } = task
    return this.dao.run(
      `UPDATE tasks
      SET name = ?,
        description = ?,
        isComplete = ?,
        projectId = ?
      WHERE id = ?`,
      [name, description, isComplete, projectId, id]
    )
  }
}

module.exports = TaskRepository;

Deleting Data

The last mutational functionality to implement is to provide the ability to delete records from the database. For this I will again be using the AppDAO.run(...) method in conjunction with new delete methods for both the ProjectRepository and TaskRepository classes.

For ProjectRepository this looks like this:

// project_repository.js

class ProjectRepository {
  // omitting other methods

  delete(id) {
    return this.dao.run(
      `DELETE FROM projects WHERE id = ?`,
      [id]
    )
  }
}

module.exports = ProjectRepository;

And for TaskRepository it looks like this:

// task_repository.js

class TaskRepository {
  // omitting other methods

  delete(id) {
    return this.dao.run(
      `DELETE FROM tasks WHERE id = ?`,
      [id]
    )
  }
}

module.exports = TaskRepository;

Alright, that wraps up all the ways I will be using the run method. Next I will introduce the two other related get and all sqlite3 Node.js package methods.

Reading Data

In this section I am going to go over how to use the get and all methods of the sqlite3 Node.js library. As mentioned previously, get is used to retrieve a single row of data while all is used to query many rows of data.

The base syntax for using get looks like this:

db.get('SELECT ...', [param1, param2], (err, result) => {
  if (err) {
    console.log(err)
  } else {
    // do something with result
  }
})

Where db is a sqlite3 connection object. You'll notice that the syntax is essentially identical to the run method except that the callback has an additional parameter which holds the result object of the query, assuming no error was thrown.

The base syntax for all is essentially the same again except the second parameter to the callback is an array of results returned by the query, like so:

db.all('SELECT ...', [param1, param2], (err, results) => {
  if (err) {
    console.log(err)
  } else {
    // do something with results
  }
})

Just like I did with the sqlite3 run method I am going to implement the get and all methods utilizing the bluebird Promise within the AppDAO class as shown below:

// dao.js

const sqlite3 = require('sqlite3').verbose()
const Promise = require('bluebird')

class AppDAO {
  // omitting other methods
  
  get(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.get(sql, params, (err, result) => {
        if (err) {
          console.log('Error running sql: ' + sql)
          console.log(err)
          reject(err)
        } else {
          resolve(result)
        }
      })
    })
  }

  all(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.all(sql, params, (err, rows) => {
        if (err) {
          console.log('Error running sql: ' + sql)
          console.log(err)
          reject(err)
        } else {
          resolve(rows)
        }
      })
    })
  }
}

I can now use these methods in the ProjectRepository and TaskRepository classes to retrieve data from the SQLite database.

To start I will add getById methods to each class to select their records by id.

In ProjectRepository I add this:

// project_repository.js

class ProjectRepository {
  // omitting other methods

  getById(id) {
    return this.dao.get(
      `SELECT * FROM projects WHERE id = ?`,
      [id])
  }
}

module.exports = ProjectRepository;

And in TaskRepository similarly:

// task_repository.js

class TaskRepository {
  // omitting other methods

  getById(id) {
    return this.dao.get(
      `SELECT * FROM tasks WHERE id = ?`,
      [id])
  }
}

module.exports = TaskRepository;

To demonstrate the AppDAO.all(...) method I will add the ability to select all projects as well as all tasks for a given project.

The code to SELECT all projects looks like this:

// project_repository.js

class ProjectRepository {
  // omitting other methods

  getAll() {
    return this.dao.all(`SELECT * FROM projects`)
  }
}

module.exports = ProjectRepository;

Then to select all tasks for a project I'll use a method called getTasks(projectId) that expects the id of the project you want the tasks for.

// project_repository.js
class ProjectRepository {
  // omitting other methods

  getTasks(projectId) {
    return this.dao.all(
      `SELECT * FROM tasks WHERE projectId = ?`,
      [projectId])
  }
}

module.exports = ProjectRepository;

Putting the Data Access Code to Use

Thus far I have basically created a data access library for this fictitious project and task tracking application. What I would like to do now is to use it to load up my test data displayed in the tables in the Designing the Database section.

In the main.js file, I will want to pull in the AppDAO, ProjectRepository, and TaskRepository classes via require. Then I will use them to create the tables, populate them with data then, retrieve data from the database and display them to the console.

// main.js

const Promise = require('bluebird')
const AppDAO = require('./dao')
const ProjectRepository = require('./project_repository')
const TaskRepository = require('./task_repository')

function main() {
  const dao = new AppDAO('./database.sqlite3')
  const blogProjectData = { name: 'Write Node.js - SQLite Tutorial' }
  const projectRepo = new ProjectRepository(dao)
  const taskRepo = new TaskRepository(dao)
  let projectId

  projectRepo.createTable()
    .then(() => taskRepo.createTable())
    .then(() => projectRepo.create(blogProjectData.name))
    .then((data) => {
      projectId = data.id
      const tasks = [
        {
          name: 'Outline',
          description: 'High level overview of sections',
          isComplete: 1,
          projectId
        },
        {
          name: 'Write',
          description: 'Write article contents and code examples',
          isComplete: 0,
          projectId
        }
      ]
      return Promise.all(tasks.map((task) => {
        const { name, description, isComplete, projectId } = task
        return taskRepo.create(name, description, isComplete, projectId)
      }))
    })
    .then(() => projectRepo.getById(projectId))
    .then((project) => {
      console.log(`\nRetreived project from database`)
      console.log(`project id = ${project.id}`)
      console.log(`project name = ${project.name}`)
      return taskRepo.getTasks(project.id)
    })
    .then((tasks) => {
      console.log('\nRetrieved project tasks from database')
      return new Promise((resolve, reject) => {
        tasks.forEach((task) => {
          console.log(`task id = ${task.id}`)
          console.log(`task name = ${task.name}`)
          console.log(`task description = ${task.description}`)
          console.log(`task isComplete = ${task.isComplete}`)
          console.log(`task projectId = ${task.projectId}`)
        })
      })
      resolve('success')
    })
    .catch((err) => {
      console.log('Error: ')
      console.log(JSON.stringify(err))
    })
}

main()

Run using node like this:

$ node main.js

And you will see output as shown below.

Connected to database
Retrieved project from database
project id = 1
project name = 1
Retrieved project tasks from database
task id = 1
task name = Outline
task description = High level overview of sections
task isComplete = 1
task projectId = 1
task id = 2
task name = Write
task description = Write article contents and code examples
task isComplete = 0
task projectId = 1

Conclusion

In this tutorial I have reviewed the basics of the Node.js sqlite3 package API and demonstrated how you can wrap that functionality in object-oriented JavaScript with a focus on a Promise-based asynchronous implementation.

As always I thank you for reading and welcome comments and criticisms below.