Intro to Node’s built-in SQLite module

Node 22.5.0 now bundles SQLite, a lightweight, in-process relational database that requires no additional infrastructure but packs considerable capability. Let’s get to know this handy new built-in feature in Node.js.

What is SQLite?

Relational databases are a key component of the software landscape. SQLite is a simple but versatile implementation with a variety of use cases. Node 22.5 introduces a node:sqlite module that ships with the runtime.

SQLite can run using a single storage file, which is a normal file on the local disk. You can use this local version for simple storage when you don’t require the overhead or sophistication of a distributed database like MySQL or Oracle. SQLite also includes an in-memory implementation that is handy for testing and prototyping, with about as close to zero configuration as you can get. 

SQLite is a disarmingly simple database that still delivers ACID guarantees and is crash-tolerant. It’s also quite fast. You can think of it as something like a supercharged, direct filesystem storage approach where everything happens in a simple file, but you still get all the semantics of a relational database. It’s a very popular database.

Node’s new SQLite module

The SQLite module is still considered experimental and you have to use a special flag, --experimental-sqlite, when running programs that use it. As you’ll notice in the coming examples, so far the Node SQLite support is via a synchronous API. There is some discussion about incorporating an async version.

That Node has moved to incorporate SQLite directly into its modules is an indication of how useful this database can be. (There even seems to be a bit of competition between Node and Bun’s zero-dependency SQLite support.)

A working example

Let’s get our feet wet with an example. We’ll create a table and insert a few records into it using the following code:

import { DatabaseSync } from 'node:sqlite';  // 1
const database = new DatabaseSync(':memory:'); // 2

database.exec(`
CREATE TABLE IF NOT EXISTS physics (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, quote TEXT)
`); // 3

const insert = database.prepare('INSERT INTO physics (name, quote) VALUES (?, ?)'); // 4

// 5:
insert.run('Albert Einstein', "I believe in Spinoza's God" );
insert.run('Marie Curie', 'Nothing in life is to be feared, it is only to be understood');
insert.run("Richard Feynman", "Nobody understands quantum mechanics.");

const query = database.prepare('SELECT * FROM physics ORDER BY name'); // 6
console.log(query.all()); // 7

Remember that to run this code, you’ll need the experimental flag node --experimental-sqlite physicists.mjs. I’ve added numbered comments to explain the lines in the example.

In (1), we begin by importing the sqlite module; notice the node namespace is required. In (2), we get a handle to a DatabaseSync instance. The name :memory: is a special one that tells SQLite to save everything in memory instead of on disk. 

In (3), we execute a create table command against the database. This is standard SQL, and lets us make a new table if it doesn’t exist. (In this case, it doesn’t because we’re using an in-memory database.) The table, called physics, holds three columns:

• A primary key called id. This is set to autoincrement, meaning the database will automatically apply the next highest integer value for this column for every new row inserted. A primary key is the unique identifier for a record in a relational database.
• A name column of type text.
• A quote column of type text.

SQLite supports prepared statements, which you use to define an SQL statement that has variables. You then supply specific values to the prepared statement. This allows for more efficiency when running several similar statements or repeating them over time. In (4), we prepare an insert statement with two variables, name and quote.

In (5), we have three insert.run() calls, which apply specific values to the prepared statement. These calls will insert the three quotes into our table.

Finally, in (6), we create a select prepared statement and then run it with query.all(), outputting the results to the console:

$ node --experimental-sqlite physicists.mjs 
[
  {
    id: 1,
    name: 'Albert Einstein',
    quote: "I believe in Spinoza's God"
  },
  {
    id: 2,
    name: 'Marie Curie',
    quote: 'Nothing in life is to be feared, it is only to be understood'
  },
  {
    id: 3,
    name: 'Richard Feynman',
    quote: 'Nobody understands quantum mechanics.'
  }
]

If we want to save the table to disk, we can change the database parameter to a filename where the data will be stored:

const database = new DatabaseSync('myFile.db');

With that simple change, we have a persistent mechanism for sharing data across different processes and programs. If we run our earlier example with this configuration, we will see the myFilename.db file on disk:

$ cat myFile.db 
??P++Ytablesqlite_sequencesqlite_sequenceCREATE TABLE sqlite_sequence(name,seq)mtablephysicsphysicsCREATE TABLEII8+WRichard FeynmanNobody understands quantum mechanics.L#Marie CurieNothing in life is to be feared, it is only to be understood-+AAlbert EinsteinI believe in Spinoza's God

This example lets you see SQLite’s on-disk file format.

Simple commands for using SQLite in Node

Now, let’s create a new file to count the number of physics quotes in the database:

// count.mjs
import { DatabaseSync } from 'node:sqlite';

const database = new DatabaseSync('myFile.db'); 

const query = database.prepare('SELECT COUNT(*) AS count FROM physics');
const result = query.get();

console.log(`There are ${result.count} quotes in the physics table.`);

You can run this new program like so:

$ node --experimental-sqlite count.mjs

There are three quotes in the physics table.

To add a new quote, we can use SQLite’s command-line interface. Let’s say we wanted to accept a couple of arguments, the name and quote, and insert them using addQuote.mjs:

// addQuote.mjs 
import { DatabaseSync } from 'node:sqlite';

const [, , name, quote] = process.argv;

// Check for missing args
if (!name || !quote) {
  console.error('Please provide both name and quote arguments!');
  process.exit(1);
}

const database = new DatabaseSync('myFile.db');

const insert = database.prepare('INSERT INTO physics (name, quote) VALUES (?, ?)');
insert.run(name, quote);

console.log(`Quote added successfully for ${name}`);

database.close();

The CLI app accepts two arguments and uses them to add a new record. Note that the arguments must be double-quoted to be accepted. Here’s the code:

$ node --experimental-sqlite addUser.mjs "Werner Heisenberg" "Now, this is a very strange result, since it seems to indicate that the observation plays a decisive role in the event"

Quote added successfully for Werner Heisenberg

To verify the new quote is there, run getQuotes.mjs:

// getQuotes.mjs
import { DatabaseSync } from 'node:sqlite';
const database = new DatabaseSync('myFile.db');

const query = database.prepare('SELECT * FROM physics ORDER BY name');
console.log(query.all());

Transactions in SQLite

Similar commands let us access the full range of SQL capabilities, including rich relationships and complex joins. SQLite also supports transactions, which you can use to bundle multiple operations into an atomic grouping. That means if any operation fails, they’ll all be rolled back.

As a final example, say we want to add experiments to a database, but only if both experiments succeed—meaning, in this case, that each experiment is inserted. We could wrap the two inserts into a transaction so that if either insert fails, the entire transaction is rolled back:

// experiments.mjs
import { DatabaseSync } from 'node:sqlite';

const database = new DatabaseSync('experiments.db');

database.exec(`
  CREATE TABLE IF NOT EXISTS experiments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    description TEXT
  )
`);

const addExperiments = (experiment1, experiment2) => {
  database.exec('BEGIN TRANSACTION');

  try {
    database.prepare('INSERT INTO experiments (name, description) VALUES (?, ?)').run(experiment1.name, experiment1.description);
    database.prepare('INSERT INTO experiments (name, description) VALUES (?, ?)').run(experiment2.name, experiment2.description);
    database.exec('COMMIT');
    console.log('Experiments added successfully');
  } catch (error) {
    database.exec('ROLLBACK');
    console.error('Error adding experiments:', error);
  }
};

const experiment1 = {
  name: "Newton's Law of Gravity",
  description: "An experiment to demonstrate the gravitational force between two objects."
};

const experiment2 = {
  name: "Ohm's Law",
  description: "An experiment to demonstrate the relationship between voltage, current, and resistance."
};

addExperiments(experiment1, experiment2);

Conclusion

SQLite is a versatile tool and a welcome addition to Node’s built-in profile. When you need a lightweight mechanism for persisting data, definitely consider SQLite.

We looked at simple examples, but SQLite is capable of handling more demanding requirements; just make sure you don’t need a distributed datastore or massive throughput. Many smaller applications and websites meet this requirement, so you can avoid the additional logistics of a more involved datastore by using SQLite. As of Node 22.5.0, you don’t even need to include a library. We were able to set up and run our examples without using NPM or any other package manager.