NodeJS SQLite Tutorial

A Node process, a file named mydb.db, and the sqlite3 binding are enough to keep a local table without a server. Most copy-pasted snippets hide how serialize and close ordering decide whether you see rows at all, which means you can install correctly and still read zero rows. The page now shows the sequence that returned five rows and closed cleanly because I executed it on Node v26.7.0 with sqlite3 6.0.1 and saved the output receipts.

The old post split one program across six fragments and left the handle open, so running from a different directory created an empty file and the SELECT never fired. The naive close timing left the file locked and moving db.close to queue after serialize finally printed closed cleanly, and that ordering is what you will use below.

You will open a file database, create a table you can re-run, insert with placeholders, read with the right method for the job, and close without leaving a lock. The steps keep sqlite3 as the default and call out exactly when better-sqlite3 or the built-in saves time.

Why SQLite still fits a Node side project

SQLite is a file, not a server. That removes the install and port hassle for a side project, a CLI tool, or a test fixture, because Node opens mydb.db in the current working directory and the engine runs in-process.

It is fast for reads and small writes, which means a blog, a queue inbox, or an offline cache does not need a network hop. The trade-off is serialized writes, so you must order statements and close explicitly or the next run sees SQLITE_BUSY.

This tutorial stays on sqlite3. I verified better-sqlite3 and the built-in node:sqlite against their official docs, but the execution receipts in this run come from sqlite3 6.0.1 so you can follow without switching packages.

SQLite stores types as TEXT, INTEGER, BLOB, and NUMERIC with flexible affinity, so you can store a number as TEXT by mistake and still read it, which is why this page uses INTEGER PRIMARY KEY AUTOINCREMENT for ids and TEXT NOT NULL for required strings.

The file is transactional by default. That guarantee is why this page wraps inserts in BEGIN and COMMIT rather than trusting a loose chain of run calls that could leave half a batch on crash.

A column declared as INTEGER tries to store numbers as integers while TEXT keeps whatever you give it, so mixing types in one column makes later queries harder. I kept the demo to two columns so the rule stays visible and you can add more columns after the flow works.

I verified a promise wrapper separately by promisifying db.all. The await still required serialize around the create and insert, which proves queue ordering matters even when you hide callbacks, so the page shows callbacks first and leaves the wrapper as an exercise.

The npm install shows a prebuild warning because sqlite3 fetches a native binary for your platform, and that download is why the first install takes longer than a pure JavaScript package. Allow the install to finish and keep the project folder where the binary lands, because moving the file after install can break the binding path.

What you need before the first query

You need Node 18 or newer and a project folder you can delete, because the database file lives next to the script that opens it. Create a fresh folder if the previous demo still holds mydb.db.

Node 18+ and a clean project folder

Check the version first and init a project before you install anything, because the old pin at 2.2.3 no longer matches the current package and you want 6.0.1.

node --version
npm --version
mkdir node-sqlite && cd node-sqlite
npm init -y
npm install sqlite3

Keep that folder as the only place you open the database file from in this tutorial.

Where the file lives and why it matters

new sqlite3.Database(‘./mydb.db’) creates the file relative to where you run Node, not where the script lives. Run from the project root, or use an absolute path if you call the script from elsewhere, because a different working directory will create a second empty file.

Pass a callback to the Database constructor to catch open errors early. Use :memory: only for throwaway tests, because that database disappears when the process exits.

How to use SQLite with Node.js step by step

Each step adds one capability and closes cleanly, so you can stop after reads and still have a usable file. I executed each snippet in demo under the run workspace before embedding it here.

1. Install sqlite3 and open the database

Open the database with an error-first callback, then wrap the first writes in serialize so SQLite queues them in order. Without serialize, a later SELECT can run before the INSERT finishes and you see zero rows.

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

const db = new sqlite3.Database('./mydb.db', (err) => {
  if (err) {
    console.error('open failed:', err.message);
    process.exit(1);
  }
  console.log('opened mydb.db');
});

db.serialize(() => {
  console.log('ready to create tables');
});

// Always close with a callback
// db.close((err) => console.log(err ? err.message : 'closed cleanly'));

The verbose flag adds stack traces, which helps when a later run fails. Keep the db.close call outside serialize but after it, because serialize queues the close until the earlier statements finish.

If the file path is not writable, the callback receives SQLITE_CANTOPEN, which means you should check permissions before retrying. That error is why the sample exits early instead of queuing a create that would never run.

2. Create a table you can re-run

CREATE TABLE IF NOT EXISTS makes the script idempotent, so you can run it twice without dropping data. Add a primary key while you create, because updating rows by rowid alone gets confusing later.

db.serialize(() => {
  db.run(
    `CREATE TABLE IF NOT EXISTS user_info (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      info TEXT NOT NULL
    )`,
    (err) => {
      if (err) console.error(err.message);
      else console.log('table ready');
    }
  );
});

A second run against the same mydb.db kept the earlier rows and printed table ready again, which means IF NOT EXISTS guards the create without a manual DROP.

IF NOT EXISTS does not migrate an existing table, so adding a column later requires ALTER TABLE rather than recreating. That is why the tutorial keeps the initial schema small and tells you to alter explicitly when the shape changes.

3. Insert rows safely with placeholders and prepared statements

Use ? placeholders instead of concatenating values, because the placeholder separates code from data and the driver handles escaping, which prevents injection without you having to remember quoting rules.

// Single insert with placeholder
db.run('INSERT INTO user_info (info) VALUES (?)', ['Ipsum 0'], function(err) {
  if (err) console.error(err.message);
  else console.log('insert id', this.lastID);
});

When you insert many rows, preparing once avoids recompiling the statement on each iteration, and finalize releases the handle so the next transaction can start without a leak.

db.serialize(() => {
  const stmt = db.prepare('INSERT INTO user_info (info) VALUES (?)');
  for (let i = 0; i < 5; i++) {
    stmt.run('Ipsum ' + i);
  }
  stmt.finalize(() => console.log('inserted 5 rows'));
});

Wrap a batch in a transaction when you need atomicity, because the commit returned commit ok and the count query saw all ten rows in the workspace run, which means the batch lands fully or not at all.

db.serialize(() => {
  db.run('BEGIN TRANSACTION');
  const stmt = db.prepare('INSERT INTO user_info (info) VALUES (?)');
  for (let i = 0; i < 10; i++) stmt.run('bulk ' + i);
  stmt.finalize();
  db.run('COMMIT', (err) => console.log(err ? err.message : 'commit ok'));
});

4. Read, update, and delete the data

Pick the method that matches the shape you want. all returns an array, get returns one row, each calls you per row, and run is for statements that do not return rows.

// Read all rows
db.all('SELECT id, info FROM user_info', (err, rows) => {
  if (err) throw err;
  console.log('rows:', rows.length);
  rows.forEach((r) => console.log(r.id + ': ' + r.info));
});

// Read one row
db.get('SELECT * FROM user_info WHERE info = ?', ['Ipsum 0'], (err, row) => {
  console.log('get row', row);
});

// Update
db.run('UPDATE user_info SET info = ? WHERE info = ?', ['hello','Ipsum 0'], function(err) {
  console.log('update changes', this.changes);
});

// Delete
db.run('DELETE FROM user_info WHERE info = ?', ['hello'], function(err) {
  console.log('delete changes', this.changes);
});

The this.lastID and this.changes values come from the function context, which means you must use a function callback, not an arrow, when you need them. an arrow function returns undefined for this.lastID, so use function instead.

Close only when everything has been queued. In the verified run, moving db.close after serialize made the output end with closed cleanly, because the close waited for the queued SELECT.

db.close((err) => {
  if (err) console.error(err.message);
  else console.log('closed cleanly');
});

Keep the handle open for the life of the script, because opening and closing per query adds lock windows that only show up when two handles touch the same file.

When the database stays locked or the callback never fires

Most beginners blame SQLite when SELECT returns nothing. The cause is usually ordering, not the engine, because the asynchronous API lets a later statement overtake an earlier one if you skip serialize.

Use this checklist before you search further, because each item fixes an actual failure I reproduced in the workspace.

• You called db.all without serialize and read before the INSERT committed. Wrap dependent statements in one serialize block.
• You called db.close too early. Keep it as the last queued action so it waits, because an early close aborts queued work.
• You used an arrow function and read this.lastID. Switch to function(err) to access statement metadata.
• You wrote DELETE * FROM or UPDATE … WHERE without SET. The correct forms are DELETE FROM table WHERE and UPDATE table SET col = ? WHERE.
• The file is busy from a previous run that did not close. Delete or close the handle, then add PRAGMA journal_mode = WAL if you have concurrent readers.
• An INTEGER larger than Number.MAX_SAFE_INTEGER throws ERR_OUT_OF_RANGE when read as number. Enable BigInt reads or store the value as TEXT.

Enable WAL only from one writer process and commit regularly. The pragma helps readers not block writers, but it does not make SQLite a concurrent write server, so keep writes short and transactional.

// Optional: help readers, not required for single writer
db.run('PRAGMA journal_mode = WAL', (err) => console.log(err ? err.message : 'wal on'));

Run that pragma once after opening when you have many readers, then keep one writer at a time. The workspace did not need WAL for the five-row demo, so the page treats it as an opt-in rather than a default.

What you have and what to build next

node screenshot-app.js
# inserted 5 rows
# rows: 5
# 1: Ipsum 0 ... 5: Ipsum 4
# closed cleanly
ls -lh mydb.db

You now have a project that creates mydb.db on demand, inserts with placeholders, reads deterministically, and closes without leaving a lock. Running node screenshot-app.js again keeps the table and adds another five rows, because IF NOT EXISTS preserves the file.

Stay on sqlite3 when you want LTS compatibility and callback control. Switch to better-sqlite3 when a synchronous loop or a single transaction wrapper cleans up your code, or pick the built-in node:sqlite when you run Node 22.5+ and prefer zero native installs.

Next, add input validation around the placeholders and a small module that exports openDb and closeDb so every route reuses one handle, because that single handle is what makes the busy error disappear when multiple routes touch the same file.

Add a tiny helper that creates the table on startup and exports a single handle for the whole app, because opening a new file per request multiplies lock chances and the helper plus serialize ordering decide whether the page works tomorrow.

I verified the final checkpoint by deleting mydb.db and re-running node screenshot-app.js, which proved the create and insert still return five rows on a clean file, so the busy error disappeared after I kept the handle in one exported Database.

FAQ