How to Use SQLite with Node.js

Getting Started

Because the code we're writing for this tutorial is "standalone" (meaning it's not part of a bigger app or project), we're going to create a Node.js project from scratch. If you don't already have Node.js installed on your computer, read this tutorial first and then come back here.

Once you have Node.js installed on your computer, from your projects folder on your computer (e.g., ~/projects), create a new folder for our work:

Terminal

mkdir app

Next, cd into that directory and create an index.js file (this is where we'll write our code for the tutorial):

Terminal

cd app && touch index.js

Next, we want to install two extra dependencies, sqlite3 and lorem-ipsum:

Terminal

npm i sqlite3 lorem-ipsum

The first will give us access to a Node.js driver for SQLite (what we'll use to connect to the database in our code), while the second will help us generate some test data to insert into our database.

One last step: in the package.json file that was created for you, make sure to add the field "type": "module" as a property. This will enable ESModules support and allow us to use the import statements shown in the code below.

With that in place, we're ready to get started.

A brief primer on SQLite

When most people think of a database, they think of something that writes and retrieves data from the disk (like PostgreSQL), or, directly from memory (like Redis). In order for these databases to work, though, they need a database server: a long-running process that handles inbound connections.

For a full-blown application, these sorts of databases are helpful because they offer a rich feature set and allow you to manage significant amounts of data.

In some cases, though, these sorts of databases are problematic, namely, when you're trying to keep as light a footprint as possible or limit the number of "expensive" (in terms of CPU and memory) processes running alongside your application. To combat this, we have a different form of database known as an embedded database. These are databases that do not require a server to function, meaning they can run in resource-limited environments (e.g., a Raspberry Pi).

The most popular option for this type of database is SQLite. A SQL based-database that operates as a single file using a special format to store its data. A "database" in SQLite is just a file like posts.db or users.db. When you use a driver to interact with SQLite, you read from and write to this file. To keep things simple, SQLite offers a limited set of data types (just five: NULL, INTEGER, REAL(FLOAT), TEXT, and BLOB).

Adding a new database and tables

To get started with our code, we want to make sure we have two things available to us:

1. A database to write data to.
2. A table within that database to organize our data.

Let's get started by wiring up a connection to a database (if it doesn't exist, our driver will create it for us) and then add a table to it.

/index.js

import sqlite3 from 'sqlite3';

const SQLite3 = sqlite3.verbose();
const db = new SQLite3.Database('posts.db');

Just a few lines. First, up top, we need to import sqlite3 from the package we installed earlier via NPM. Keep in mind: this is the driver package (what we use to communicate with the database) and not SQLite itself. Next, we create a new variable SQLite3 (we use this casing because we expect to receive a JavaScript Class in return—this casing is a common pattern for signifying that) which is assigned to a call to sqlite3.verbose(). This is getting us a copy of the class we'll use to start the driver in verbose mode which means that it will include the full stack trace for any errors it encounters (helpful for debugging).

Next, with our class, we create one more variable db which gives us access to our actual instance/connection to our database by calling new SQLite3.Database('posts.db'). Here, posts.db is the name of the database we want to connect to. In the event that this database (a file at the root of our project) does not exist, the driver will create it for us.

Creating a promise wrapper

Before we get into creating our data, to make our work easier, we're going to quickly write a wrapper function that gives us a promised version of the sqlite3 driver. We want to do this because by default the package uses a callback pattern (which can lead to messy code).

/index.js

import sqlite3 from 'sqlite3';
import { LoremIpsum } from 'lorem-ipsum';

const SQLite3 = sqlite3.verbose();
const db = new SQLite3.Database('posts.db');

const query = (command, method = 'all') => {
  return new Promise((resolve, reject) => {
    db[method](command, (error, result) => {
      if (error) {
        reject(error);
      } else {
        resolve(result);
      }
    });
  });
};

Just below our setup code, here, we've added a new function query (the name is arbitrary) which takes two arguments: command which is the SQL statement we want to run and method which is the sqlite3 driver method we want to call.

Inside of that function, we return a new JavaScript Promise which wraps a call to db[method] where db is the connection/instance we just set up above and [method] is us using JavaScript bracket notation to say "call the method with the same name as the current value of our method variable." For example, if we don't pass anything for method, by default we pass all which means here we'd be running db.all(). If we passed method as get, we'd be doing db.get().

Because we expect that method to be a function, we call it as db[method](), passing our SQL command as the first argument and then passing a callback function receiving either an error or a result as the second argument.

Inside of that function, if we have an error we want to call the reject() method from our Promise passing the error that occurred and if all is well, we want to call the resolve() method from our Promise, passing the result we received.

With this, we're ready to start running commands on our database.

Inserting data into a table

Like the name implies, SQLite is just a SQL database. Save for certain limitations, if you're familiar with the basic SQL syntax used by other databases (e.g., PostgreSQL or MySQL), you'll feel write at home. First, in order to actually put data into our database, we need a table within that database to exist. To do it, we're going to use the query() function we just wired up.

/index.js

import sqlite3 from 'sqlite3';

const SQLite3 = sqlite3.verbose();
const db = new SQLite3.Database('posts.db');

const query = (command, method = 'all') => { ... };

db.serialize(async () => {
  await query("CREATE TABLE IF NOT EXISTS posts (date text, title text, author text, content text, tags text)", 'run');
});

At the bottom of our file, we've made a call to a new function db.serialize() which itself receives a function. This function tells the sqlite3 driver that we want to serialize our calls to the database, meaning, each SQL command we execute within the function we pass to it is run and completed before the next SQL command is allowed to be executed.

For now, we just have a single command inside. To run it, we make use of our query() function we just wired up, prefixing it with the await keyword (this is why we have the async keyword prefixing the function we pass to db.serialize()—without that, our await statement would throw an error).

To it, we pass the SQL command we want to run as the first argument and then the method we want to run on our database driver as the second argument: run. If we look close at the command, our goal here is to create a new table called posts in our database if it doesn't already exist. For that table, we're defining five columns:

1. date which is an ISO-8601 date string like 2022-04-29T00:00:00.000Z.
2. title which is the title of our post as a string.
3. author which is the name of the post author as a string.
4. content which is the content of our post as a string.
5. tags which is a comma-separated list of tags for our post as a string.

With this, when we run our index.js file (from our terminal, at the root of the project, we can run node index.js to run the code), if the posts table doesn't exists in posts.db, SQLite will create it with the specified columns.

/index.js

import sqlite3 from 'sqlite3';
import { LoremIpsum } from 'lorem-ipsum';

const SQLite3 = sqlite3.verbose();
const db = new SQLite3.Database('posts.db');

const query = (command, method = 'all') => { ... };

const createPostsIfEmpty = async () => {
  const existingPosts = await query('SELECT * FROM posts');

  if (existingPosts?.length === 0) {
    const lorem = new LoremIpsum();

    for (let i = 0; i < 1000; i += 1) {
      const tags = [...Array(3)].map(() => lorem.generateWords(1));
      await query(`INSERT INTO posts VALUES ("${new Date().toISOString()}", "${lorem.generateWords(10)}", "Ryan Glover", "${lorem.generateParagraphs(5)}", "${tags}")`, 'run');
    }
  }
};

db.serialize(async () => {
  await query("CREATE TABLE IF NOT EXISTS posts (date text, title text, author text, content text, tags text)", 'run');
  await createPostsIfEmpty();
});

Next, with our table, we want to create some test data. To do it, we're going to add another function above our call to db.serialize() called createPostsIfEmpty().

Like the name implies, our goal will be to check if our posts table is empty, and if it is, insert some test data for us to read.

Just like we saw above, the function we're defining here will need to be prefixed with async so we can safely use the await keyword without triggering a JavaScript error.

Inside of that function, the first thing we want to do is check to see if we have any posts. To do it, we call to await query() passing the SQL statement SELECT * FROM posts which says "select all columns from the posts table." Notice that we do not pass a second argument to query() here, meaning, we want to use the default all method for our driver (this returns all rows matching our query as an array).

If the array we receive back—here, stored in the existingPosts variable—has a length of 0 (meaning the table is empty), we want to insert some data.

To do it, up top we've imported the LoremIpsum class from the lorem-ipsum package we installed earlier. Like the name suggests, this package will help us to generate some fake data on-the-fly.

To use it, first, we need to create an instance by calling new LoremIpsum(), which we've stored in a variable lorem here. Next, to create our data, we're going to use a JavaScript for loop which will create 1000 posts in our posts table.

Inside of that for loop, first, we create a variable tags which will generate an array of 3 strings where each string is the result of calling lorem.generateWords(1). To do that, we use a little trickery with Array(3) saying "create an array of 3 elements" which will be undefined elements and then use the ... spread operator to unpack them into another array (technically unnecessary, but gives us certainty our .map() call is running on an actual array value). Next, we use a .map() to iterate over the array of undefined values and for each one, return a string via lorem.generateWords().

With this, again, we use our query() function to execute a SQL command, this time doing an INSERT into our posts table. As the second argument, we pass run as the method to signify that we just want to run this command and don't expect a return value.

That's it for getting data into the table. Now, for our last step, let's learn how to read back the data we just inserted.

Reading data

Just a one liner to get this done. Back down in our db.serialize() function, now, we should have some data we can query:

/index.js

import sqlite3 from 'sqlite3';
import { LoremIpsum } from 'lorem-ipsum';

const SQLite3 = sqlite3.verbose();
const db = new SQLite3.Database('posts.db');

const query = (command, method = 'all') => {
  return new Promise((resolve, reject) => {
    db[method](command, (error, result) => {
      if (error) {
        reject(error);
      } else {
        resolve(result);
      }
    });
  });
};

const createPostsIfEmpty = async () => {
  const existingPosts = await query('SELECT * FROM posts');

  if (existingPosts?.length === 0) {
    const lorem = new LoremIpsum();

    for (let i = 0; i < 1000; i += 1) {
      const tags = [...Array(3)].map(() => lorem.generateWords(1));
      await query(`INSERT INTO posts VALUES ("${new Date().toISOString()}", "${lorem.generateWords(10)}", "Ryan Glover", "${lorem.generateParagraphs(5)}", "${tags}")`, 'run');
    }
  }
};

db.serialize(async () => {
  await query("CREATE TABLE IF NOT EXISTS posts (date text, title text, author text, content text, tags text)", 'run');
  await createPostsIfEmpty();

  const existingPosts = await query('SELECT rowid as id, date, title, author, content, tags FROM posts');
  console.log(existingPosts);
});

Down at the bottom, we're using query() one last time to perform a SELECT command, this time passing the specific fields we want to retrieve (the standout here is reading back rowid as id where rowid is the default ID that SQLite adds for us but we didn't specify in our table creation). Because we default to the all method, we expect this to return the full 1000 rows we inserted into posts.

If we log out existingPosts, we've got a functioning SQLite database!

Wrapping up

In this tutorial, we learned how to wire up a SQLite database. We learned how to create a database file on-the-fly and how to create a table where we could insert data. Next, we learned how to insert data and then query that data back. To keep our code clean, we also learned how to write a wrapper function that returned a JavaScript Promise, allowing us to write asynchronous calls to our database with ease.