Getting started with Node.js and SQLite3

Most of the real world applications require a database for their functioning. There are many types of databases out of which the most popular are relational (SQL) and non-relational (NoSQL) databases. For this tutorial we will be using a relational database with the help of a npm library named sqlite3.

What is SQLite ?

SQLite is a library which provides a serverless, transactional SQL and zero configuration database engine. It is a disk based database that doesn’t require a separate server process. All the data is stored in the form of a file. It is suitable for small to medium sized applications.

Installing SQLite

This will be needed so that we can interact with the DB without the Node.js application for operations like creation of table, etc. The steps below are for Windows users:

1. Go to the SQLite website.
2. Download the sqlite-dll and sqlite-tools zip file present under ‘Precompiled Binaries for Windows’.
3. Create a new folder named sqlite under C:\>sqlite.
4. Unzip the files and add all the contents of the unzipped files under this folder.
5. Add ‘C:\>sqlite’ in your PATH environment variable.
6. Run this command to verify that sqlite is working:
   sqlite3 -version

Getting Started

Setting up the Express.js server:

1. Create a new directory named getting-started-with-node-and-sqlite.
2. Navigate to the new directory and run the following commands:

npm init
npm install express
npm install sqlite3

Setting up table and data

We will be creating a table using sqlite terminal so that we can access that table using the Node.js application. Inside the project directory run the following command:
sqlite3 orderOfThePhoenix.db

This will take you to the sqlite3 terminal and create a file named orderOfThePhoenix.db. Run this command to create a new table named orderOfThePhoenix:

CREATE TABLE orderOfThePhoenix (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  firstName text NOT NULL,
  lastName text
);

After the creation of the table run this command to insert some values into this table:

INSERT INTO orderOfThePhoenix (firstName, lastName)       
VALUES ('Sirius','Black'),
       ('Albus','Dumbledore'),
       ('Mundungus','Fletcher'),
       ('Rubeus','Hagrid'),
       ('Remus','Lupin'),
       ('Alastor','Moody'),
       ('James','Potter'),
       ('Lily','Potter'),
       ('Peter','Pettigrew'),
       ('Severus','Snape'), 
       ('Minerva','McGonagall'),
       ('Nymphadora','Tonks'),
       ('Kingsley','Shacklebolt'),
       ('Arthur','Weasley'),
       ('Molly','Weasley');

Use this command to exit the sqlite3 terminal:
.exit

Setting up the Node.js server

package.json

{
  "name": "getting-started-with-node-and-sqlite",
  "version": "1.0.0",
  "description": "Getting started with Node.js and sqlite3",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "singhhr",
  "license": "ISC",
  "dependencies": {
    "express": "^4.19.2",
    "sqlite3": "^5.1.7"
  }
}

index.js

var express = require('express');
const sqlite3 = require('sqlite3');
const db = new sqlite3.Database('./orderOfThePhoenix.db');

var app = express();

function getMembers() {
    return new Promise((resolve, reject) => {
        const members = [];
        db.each('SELECT id, firstName, lastName FROM orderOfThePhoenix', (err, row) => {
            if(err)
                reject(err);
            else {
                members.push({
                    id: row.id,
                    firstName: row.firstName,
                    lastName: row.lastName
                });
            }
        }, (err, n) => {
            if(err)
                reject(err);
            else {
                resolve(members);
            }
        });
    });
}

app.get("/order", async function(req, res) {
    var members = await getMembers();
    res.send({members});
});

app.listen(process.env.PORT || 3000,function(req,res){
    console.log("Server Started!");
});

To run the server run this command in the terminal in the project directory:
node index.js

You will see the message in the console ‘Server Started!’ which means the server is up and running. You can go to Chrome and go localhost:3000/order to get response from your server:

If you have any questions you can ask them anytime in the comments below.

You can follow me on GitHub: @sahni-hargun