Go Back

The Best Nodejs SQLite Tutorial – Part 1: Setting Up Nodejs with SQLite

Posted by Charlie Ontiveros on 2018-06-26

Welcome to Part 1 of a multi-part series on working with SQLite within Nodejs. First, I'll be covering the basics of the SQLite3 Nodejs library and SQLite SQL. Later, I will cover techniques to organize functions related to SQLite. If you're new to SQLite and Nodejs, this tutorial is for you.

In Part 1, I cover installing SQLite Nodejs library, creating a schema, executing the schema, and linking the database to an actual file.

In Part 2, I will cover the different types of SQLite queries within Nodejs in more detail.

In Part 3, I will share with you my techniques for taking what we've learned in the first two parts and making them more usable in an actual application.

Why SQLite?

Recently, there’s been huge surge in the popularity of NoSQL databases. However, SQL databases remain relevant in today’s software environment and so are SQL skills. Although there are various SQL databases out there, I prefer SQLite for small applications for the following reasons:

  • Setting up SQLite is very easy.
  • SQLite is a lightweight application and thus very effective for deploying to the cloud
  • SQLite has been around for a very long time and thus very robust with ton of support.

You can check out my previous article on this topic here Why I Use SQLite Instead of MongoDB.

Pre-requisites

  • Nodejs 8 or higher

Initial Project Setup

  1. Create a folder for this project. I called mine NodejsSQL.
  2. Run the following inside that folder to start an NPM project.

    npm init
  3. Go through all the options npm asks for and fill them out accordingly. These really aren't important for the purposes of this project.
  4. Create a file called server.js.
  5. Install the SQLite library.

    npm install --save sqlite

Connect to the SQLite Database

First, we will connect to the in-memory database. Once that is tested and working, we will connect to an actual database.

  1. Import the sqlite3 module in server.js.

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

    The verbose() is optional, but adding this will turn on additional log information which may be helpful for troubleshooting.

  2. Define a variable to hold the path to the database.

    const DB_PATH = ':memory:'

    Normally this is a file path, but for now we will be connecting to an in-memory database which uses a special path denoted by ":memory:".

  3. Create an database instance.

    const DB = new sqlite3.Database(DB_PATH, function(err){
        if (err) {
            console.log(err)
            return
        }
        console.log('Connected to ' + DB_PATH + ' database.')
    });

    The Database method accepts a callback function as a second parameter. If an error occurs, it will pass the error as an argument in the callback. If no error occurs, we'll log out a message indicating we've successfully connected to the database.

  4. Close the database connection.

    DB.close()

    It's good practice to close the database connection. However, in a production environment, the expectation is that database connection is always open so I usually leave this line out.

Quick Test

Run server.js using node.

node server.js

You should see "Connected to :memory: database" in the console.

Create a Schema

Now that we've confirmed that the in-memory database is working, we can more on to creating a schema. A schema is essentially a file that defines the tables, columns, types of values for each column, and relationships between different data points a database has. In the section below, we'll step through each of the different parts.

Define a Table

A table is defined with the command "CREATE TABLE". That is then followed by the name of the schema (which is optional), a period, and then the table name.

CREATE TABLE Users
(Or if using a schema name)
CREATE TABLE NodejsSQL.Users

Additionally, the command to create a table also accepts another option called "IF NOT EXISTS". This simply checks if the table with the same name already exists and skips creating the table. An error will occur if you don't include this option and try to re-create a table that already exists.

CREATE TABLE IF NOT EXISTS Users

Define Columns

A column is defined with the column name, data type, and then options. Keep in mind there are many different data types and possible options. I'll just cover the tip of the iceberg. These instructions are to go between parentheses and followed by a semi-colon.

Let's define some columns for our Users table.

(
   id integer NOT NULL PRIMARY KEY,
   login text NOT NULL UNIQUE,
   password text NOT NULL,
   email text NOT NULL UNIQUE,
   first_name text,
   last_name text
);

In the first row, we are defining a column called id. The data type for is an integer. That column contains an instruction called NOT NULL, which means that the value cannot be empty. We're also defining that id as the PRIMARY KEY, which means the value will automatically be unique. Behind the scenes, the database will also treat this column with magic that allows for quicker access to those rows and further rules, which will be covered in the next section.

The first and last name are simply marked as text and have no other instructions. This means they have very little restrictions. For example, we can leave the empty or insert duplicate values and it would be fine.

What we have so far:

CREATE TABLE IF NOT EXISTS Users (
   id integer NOT NULL PRIMARY KEY,
   login text NOT NULL UNIQUE,
   password text NOT NULL,
   email text NOT NULL UNIQUE,
   first_name text,
   last_name text
);

Define Foreign Key Constraints

Another thing that is crucial to SQL databases is the concept of foreign keys. A foreign provides a relationship from one table to another. What we have so far is enough to create a table called users. However, a single table is rather useless on its own, so lets create a second table to demonstrate how to define foreign keys with a SQLite schema.

Let's assume we're building this database for a blog. So far we have a table for our users, so let's now add a second table to our schema to hold the blogs.

CREATE TABLE IF NOT EXISTS Blogs (
    id integer NOT NULL PRIMARY KEY,
    blog text,
    title text NOT NULL,
    publish_date date,
);

Let's assume that each blog must be tied to a user. How can this be done? The answer is with a foreign key.

Let's go ahead and add this constraint in.

CREATE TABLE IF NOT EXISTS Blogs (
    id integer NOT NULL PRIMARY KEY,
    user_id integer NOT NULL UNIQUE,  <-- Add this in
    blog text,
    title text NOT NULL,
    publish_date date,
        FOREIGN KEY (user_id) REFERENCES Users(id)  <-- Add this
);
  1. First define the column we want to associate with another table.
  2. Add a new instruction, which begins with FOREIGN KEY.

This will link the user_id column in the Blogs table to the id column in the Users table.

Enable Foreign Keys

One interesting thing about SQLite is that foreign key enforcement is turned off by default. So while the above code will execute without error, the foreign key constraint rule will be ignored when executing queries. In order to turn foreign key enforcement on, you'll have to use something called a pragma. While this word seems intimidating, a pragma is simply a special command you can run to change the way SQLite manages a database.

  1. To turn on foreign keys, add the following code inside of the function where the database connection is created.

    const DB = new sqlite3.Database(DB_PATH, function(err){
        if (err) {
            console.log(err)
            return
        }
        console.log('Connected to ' + DB_PATH + ' database.')
    
    // ADD THIS CODE BELOW
        DB.exec('PRAGMA foreign_keys = ON;', function(error)  {
            if (error){
                console.error("Pragma statement didn't work.")
            } else {
                console.log("Foreign Key Enforcement is on.")
            }
        });
    });

    This will enable the foreign key constraint and also let us know if an error occurs while attempting to turn this on.

Execute the Schema

SQLite provides us with the exec method which will execute all queries in a given SQL statement. Let's create a string with the two queries to create the tables. The string is created using back ticks, not apostrophes.

Note: Template strings are an ES6 feature. If you run into any issues, you may use string concatenation to create the SQL statement.

dbSchema = `CREATE TABLE IF NOT EXISTS Users (
        id integer NOT NULL PRIMARY KEY,
        login text NOT NULL UNIQUE,
        password text NOT NULL,
        email text NOT NULL UNIQUE,
        first_name text,
        last_name text
    );

    CREATE TABLE IF NOT EXISTS Blogs (
        id integer NOT NULL PRIMARY KEY,
        user_id integer NOT NULL UNIQUE,
        blog text&nbsp;,
        title text NOT NULL,
        publish_date date,
            FOREIGN KEY (user_id) REFERENCES Users(id)
    );`

DB.exec(dbSchema, function(err){
    if (err) {
        console.log(err)
    }
});

Like the other SQLite methods, a callback can be provided to exec which will be called if an error occurs.

Quick Test

Run server.js and confirm that no errors appear. As a side note, SQLite also contains command line tools that can be used to execute commands.

Data Persistence

Now that we are more familiar with SQLite, it's time to move from an in-memory database to an actual file. Before we begin, install the SQLite Studio tool here. This will let you open up SQLite database files and will make everything much easier.

  1. Create a file called sqlite.db under the NodejsSQL folder.
  2. Update the DB_PATH from ':memory:' to './sqlite.db'.
  3. Open up sqlite.db file with SQLite Studio. You will see a panel on the left hand side with two options, Tables and Views. Of course, these will be empty.
  4. Run server.js again.
  5. Go back to SQLite Studio, right click on the 'sqlite' on the left panel, and select the 'Refresh all database schemas' option. You should now see tables for Users and Blogs.

Conclusion

In this part of the tutorial, we went over installing the required SQLite libraries + SQL Studio, creating a schema file, executing the schema, and making a persistent SQLite database.

Part 2 will cover SQLite's query methods, which will allow us to add, remove, update, and get data so don't forget to check it out next week.