node:sqlite: SQLite built into Node.js

Node now ships a built-in SQLite module. You import node:sqlite and start querying. There is no database driver to install or native add-on to compile.

Use a current Node 24 release for these examples. node:sqlite is a release candidate in Node 24.15 and later. The core API is practical today, but check its stability before committing a long-lived library to newer parts of the module.

If SQLite itself is new to you, the free SQLite course explains tables, transactions, indexes, and database files first.

Open a database

The main class is DatabaseSync. It opens one connection to a file:

import { DatabaseSync } from 'node:sqlite'

const db = new DatabaseSync('notes.db')

The constructor creates notes.db when the file does not exist.

These examples use ES modules. Add "type": "module" to package.json, or use the .mjs extension.

Use ':memory:' for a temporary database:

const db = new DatabaseSync(':memory:')

That database exists only for this connection. Closing the connection destroys it.

Every DatabaseSync operation runs synchronously. This makes scripts and tests easy to read. It also means a slow query blocks the JavaScript thread until SQLite returns.

Create the schema

Use exec() for SQL that does not need to return rows:

db.exec(`
  CREATE TABLE IF NOT EXISTS notes (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
  ) STRICT
`)

exec() can execute several SQL statements from one string. It is useful for schema setup and migration files.

The STRICT suffix asks SQLite to enforce the declared column types more closely. It is optional, but I like it for new application tables.

Current node:sqlite connections enable foreign key constraints by default. Keep the database schema responsible for relationships instead of relying only on application checks.

Insert data with a prepared statement

Prepare SQL once, then run it with different values:

const insert = db.prepare(`
  INSERT INTO notes (title, body)
  VALUES (?, ?)
`)

insert.run('Buy coffee', 'Get espresso beans')
insert.run('Write article', 'Explain node:sqlite')

The question marks are positional parameters. SQLite treats the supplied values as data, not as SQL syntax.

This prevents the classic mistake of joining untrusted text into a query:

const title = requestData.title
insert.run(title, requestData.body)

Never build that statement with string interpolation.

Parameters represent values. They cannot replace a table name, column name, or SQL keyword. Choose identifiers from a fixed allowlist when those parts must be dynamic.

run() returns information about the write:

const result = insert.run('Plan trip', 'Book the train')

console.log(result.lastInsertRowid)
console.log(result.changes)

lastInsertRowid identifies the inserted row. changes reports how many rows the statement changed.

Use named parameters

Named parameters make larger statements easier to read:

const insert = db.prepare(`
  INSERT INTO notes (title, body)
  VALUES ($title, $body)
`)

insert.run({
  $title: 'Buy coffee',
  $body: 'Get espresso beans'
})

Keep one parameter style within a statement. Mixing positional and named values makes call sites harder to review.

Query one row

Use get() when the query should return at most one row:

const findById = db.prepare(`
  SELECT id, title, body, created_at
  FROM notes
  WHERE id = ?
`)

const note = findById.get(1)

get() returns an object or undefined:

if (!note) {
  console.log('Note not found')
} else {
  console.log(note.title)
}

Use an explicit column list in application queries. SELECT * silently changes shape when the table gains a column.

Query several rows

Use all() when the result fits comfortably in memory:

const list = db.prepare(`
  SELECT id, title, created_at
  FROM notes
  ORDER BY created_at DESC
`)

const notes = list.all()

console.log(notes)

all() returns an array.

Use iterate() for a larger result so we handle one row at a time:

for (const note of list.iterate()) {
  console.log(note.title)
}

Iteration reduces the amount of result data held in a JavaScript array. The query is still synchronous, so a long loop still occupies the JavaScript thread.

Update and delete rows

An update uses the same prepared statement pattern:

const update = db.prepare(`
  UPDATE notes
  SET title = ?, body = ?
  WHERE id = ?
`)

const result = update.run(
  'Buy great coffee',
  'Get fresh espresso beans',
  1
)

if (result.changes === 0) {
  console.log('Note not found')
}

Delete by primary key:

const remove = db.prepare('DELETE FROM notes WHERE id = ?')

remove.run(1)

Prepared statements are small reusable database operations. Keep policy and validation in normal JavaScript around them.

Group writes in a transaction

A transaction makes several writes succeed or fail together.

Here we insert several notes:

const insert = db.prepare(`
  INSERT INTO notes (title, body)
  VALUES (?, ?)
`)

const notes = [
  ['Buy coffee', 'Get espresso beans'],
  ['Write article', 'Explain transactions']
]

db.exec('BEGIN')

try {
  for (const note of notes) {
    insert.run(note[0], note[1])
  }

  db.exec('COMMIT')
} catch (error) {
  db.exec('ROLLBACK')
  throw error
}

Without the transaction, an error after the first insert leaves half the operation stored. The transaction also makes large batches much faster because SQLite does not have to commit every row separately.

Keep transactions short. A long transaction holds locks and delays other writers.

Handle database locks

SQLite allows many readers but coordinates writes through file locks. A second connection may have to wait while another write transaction finishes.

Set a busy timeout when opening the database:

const db = new DatabaseSync('notes.db', {
  timeout: 5000
})

This connection waits up to five seconds for a lock before throwing an error.

A timeout helps with short overlaps. It does not fix a design that keeps transactions open for a long time.

For a web application, Write-Ahead Logging can improve the relationship between readers and one writer:

db.exec('PRAGMA journal_mode = WAL')

WAL is persistent database configuration. Understand its extra -wal and -shm files before copying or backing up the database.

JavaScript and SQLite value types

The useful mapping is small:

JavaScript numbers cannot safely represent every 64-bit SQLite integer. Open the connection with readBigInts: true when the database stores values outside the safe integer range:

const db = new DatabaseSync('events.db', {
  readBigInts: true
})

Now integer columns come back as bigint values. JSON cannot stringify a bigint directly, so convert it deliberately at an API boundary.

Use an in-memory database in tests

An in-memory database gives each test a clean schema and no temporary file:

import assert from 'node:assert/strict'
import { test } from 'node:test'
import { DatabaseSync } from 'node:sqlite'

test('stores a note', (t) => {
  const db = new DatabaseSync(':memory:')
  t.after(() => db.close())

  db.exec(`
    CREATE TABLE notes (
      id INTEGER PRIMARY KEY,
      title TEXT NOT NULL
    ) STRICT
  `)

  db.prepare('INSERT INTO notes (title) VALUES (?)')
    .run('Test note')

  const row = db.prepare(
    'SELECT title FROM notes WHERE id = ?'
  ).get(1)

  assert.equal(row.title, 'Test note')
})

Pair this with the Node.js built-in test runner and the test stack has no external database process.

An in-memory database is fast, but it does not test file permissions, WAL files, or multi-process locking. Add a file-backed integration test when those behaviors matter.

Close the connection

Close a database when the process continues after its work:

db.close()

Short command-line programs also release the file when the process exits. I still close explicitly in libraries and tests because ownership stays clear.

Do not close a connection while prepared statements or iterators are still in use.

Where synchronous SQLite fits

node:sqlite is a strong fit for:

• command-line tools
• local desktop applications
• caches and indexes
• build tools
• tests and prototypes
• small services with fast, bounded queries

Be careful in a busy HTTP server. Every synchronous query runs on the JavaScript thread handling other requests. A large migration, report, or unindexed scan can pause the whole process.

Options include keeping queries small, moving database work to a worker thread, or using a database client with an asynchronous API. Measure before adding complexity.

SQLite is also not the right database for many concurrent writers across several machines. Use a server database when the workload needs that architecture.

How I use node:sqlite

I would use node:sqlite first for a local tool, an event index, or a small application that owns one database file. Prepared statements, transactions, and the built-in test runner cover the complete basic workflow.

I would keep SQL in small named operations and version schema changes as migrations. I would also measure slow queries before blaming the synchronous API. A missing index hurts in every SQLite driver.

I would not put unbounded reporting queries on the request path. The simple synchronous model is the main advantage, so I would keep it only while the workload stays simple enough to match.

Continue with the free SQL course for query design and the SQLite course for transactions, indexes, and operational details.

Want me to talk about your product? You can sponsor this site.