Skip to content
Tutorials Center
My learning

Topics

Lesson 66 of 133 ยท Data and Storage

SQLite with node:sqlite

Use the built-in node:sqlite module in Node.js 24 - DatabaseSync, prepared statements, parameters, transactions, custom functions, migrations and in-memory vs file databases.

Most programs need to keep data somewhere that is better than a JSON file: data you can search, update piece by piece, and trust when two things change at once. A database gives you that, and SQLite is the easiest one to start with. It is a complete SQL database that lives in a single file (or in memory), with no server to install, no password and no network. Phones, browsers and countless apps use it internally.

Since Node.js 22.5 the runtime ships with a SQLite module: node:sqlite. You import it like node:fs, and there is nothing to install and no native add-on to compile. In Node.js 24 the module is usable without any flag, but it is still marked experimental (stability 1.1, "active development"): the API may change in small ways between releases, and Node may print an ExperimentalWarning on the error output when you load it. Everything you see in this lesson ran on Node.js 24. If you need a long-term stable choice today, third-party drivers such as better-sqlite3 offer a similar API (we show it read-only in Databases from Node.js).

This lesson assumes you know basic SQL. If not, the SQL tutorial on this site teaches it from scratch, and every example here is plain SQL.

node:sqlite is synchronous: the main class is called DatabaseSync, and every call blocks the event loop until SQLite is finished. For a local file that is usually microseconds to milliseconds, and it keeps your code simple. For slow queries on big tables, move the work to a worker thread.

Opening a database

new DatabaseSync(path) opens (and creates, if needed) a database file. The special name ':memory:' creates a database that exists only in RAM and vanishes when the process ends. It is perfect for demos, tests and caches, and it is what we use in this lesson. db.exec(sql) runs one or more SQL statements and returns nothing, which makes it the tool for schema changes.

Node.js Create a table and insert a row

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The program prints { changes: 1, lastInsertRowid: 1 }. changes is how many rows the statement modified and lastInsertRowid is the primary key SQLite gave the new row.

Prepared statements: run, get, all, iterate

The central idea is the prepared statement. db.prepare(sql) compiles the SQL once and returns a StatementSync object that you can run many times with different values. It has four ways to run:

Method Returns
run(...params) { changes, lastInsertRowid } (for INSERT, UPDATE, DELETE)
get(...params) the first row as an object, or undefined if there is none
all(...params) an array of every row
iterate(...params) an iterator that produces one row at a time

Rows are plain JavaScript objects (with a null prototype, so console.log shows [Object: null prototype]) with the column names as keys. SQLite types map to JavaScript like this: INTEGER to number (or bigint, see below), REAL to number, TEXT to string, BLOB to Uint8Array, and NULL to null.

Node.js run, get, all and iterate

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Use get when you expect at most one row, all for small results, and iterate when a result may be large and you want to handle it row by row without building an array. A prepared statement is cheap to keep: create it once (at module level or in a class) and reuse it inside loops instead of calling prepare every time.

Parameters: never build SQL from strings

User input must never be glued into an SQL string. Doing so gives an SQL injection hole, where input such as '; DROP TABLE books; -- becomes part of your command. Always pass values as parameters, so SQLite treats them as data, never as code.

There are two styles. Positional placeholders use ? and take values in order. Named placeholders use :name, $name or @name, and take an object.

Node.js Positional and named parameters

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

You can only parameterize values, not identifiers. A table name or a column name in ORDER BY cannot be a ?. If you need a dynamic column, check it against an allow-list in JavaScript (['title', 'year'].includes(column)) and then put it into the string.

Treat every string from outside your program as hostile: query strings, form fields, JSON bodies and file names. The rule is always the same, parameters for values and allow-lists for identifiers. See Web Application Security.

Integers, BigInt and other types

SQLite integers are 64 bits wide, but a JavaScript number can only hold integers exactly up to 2^53 - 1 (about 9 quadrillion). By default node:sqlite returns numbers and throws an error if a value does not fit. Call statement.setReadBigInts(true) to get bigint values for every integer in that statement's results. Booleans are stored as 0 and 1, and dates are stored by you as ISO-8601 text or as epoch numbers, because SQLite has no date type.

Node.js Large integers, BigInt and a failing bind

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Transactions

A transaction groups several statements into one all-or-nothing unit. If something fails halfway, you roll back and the database looks as if nothing happened. This is the classic bank transfer: subtract from one account and add to the other, both or neither.

node:sqlite has no db.transaction() helper (the third-party better-sqlite3 does), so you write the pattern yourself with BEGIN, COMMIT and ROLLBACK. Wrapping it in a small function keeps call sites tidy and guarantees that you never forget the rollback.

Node.js A transaction helper with rollback

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Notice that the credit ran first, and the failure came afterwards, yet the balances are unchanged. That is what the rollback bought us. Since the module is synchronous, nothing else in your process can run between BEGIN and COMMIT, so never await inside such a function. If you did, other code could run in the middle and see (or write into) your half-finished transaction.

Constraints and errors

SQLite enforces NOT NULL, UNIQUE, CHECK and foreign keys. Violations throw an Error with code: 'ERR_SQLITE_ERROR' and extra fields (errcode, errstr) that say which kind of failure it was. Plain SQLite has foreign-key checks off for historical reasons, but node:sqlite turns them on by default (the constructor option enableForeignKeyConstraints is true); the last line of the run below shows the pragma set to 1. Rows come back as objects with a null prototype, which is why console.log prints [Object: null prototype] in front of them. Property access works as usual.

Node.js Constraint errors (fails on purpose)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

In production code, catch the error where it is meaningful (for example a duplicate sign-up) and look at err.errcode or the message to decide what to tell the user. Do not leak raw SQL errors to API clients.

Custom SQL functions

SQLite lets you register JavaScript functions and call them from SQL. db.function(name, fn) adds a scalar function, and db.aggregate(name, options) adds an aggregate such as SUM. This is handy for things SQLite has no built-in for, such as a slug or a regular-expression test.

Node.js User-defined and aggregate functions

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Files, durability and tuning

A file-backed database is created with new DatabaseSync('app.db'). Data written in a committed transaction survives a crash. Our editor lets a program create files, but they live for one run only, so we use :memory: and a temporary file in the examples. For real applications, two settings matter:

  • PRAGMA journal_mode = WAL; switches to write-ahead logging, which lets readers and a writer work at the same time and is faster for most apps.
  • PRAGMA busy_timeout = 5000; makes SQLite wait up to five seconds for a lock instead of failing at once with "database is locked".

Also wrap bulk inserts in a transaction. Each statement outside one is its own transaction, which means a disk sync per row. Putting 10,000 inserts in one BEGIN ... COMMIT is typically many times faster.

Node.js A file database and a backup

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

backup(db, path) (added in Node.js 23.8) copies a database, even while it is in use, and returns a promise. Remember to close() connections when you are done; an open connection keeps a file handle.

Migrations by hand

Your schema changes over time. A migration is a numbered step that moves the database from one version to the next, and the database remembers which version it is at. SQLite has a free slot for this number: PRAGMA user_version. A runner then applies every migration newer than the stored version, each inside a transaction.

Node.js A tiny migration runner

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Never edit a migration after it has run on a real database; add a new one instead. Write them so they only ever move forward.

When to use SQLite, and when not

SQLite is an excellent choice for tools, CLIs, desktop apps, tests, caches, small and medium websites and anything that is read-heavy on one machine. It struggles when many separate machines must write to the same data at once, because the database is a file on one disk. At that point look at a client-server database (Databases from Node.js).

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does DatabaseSync's synchronous design mean for your program?

    Every call runs on the main thread and returns the result directly. That is simple and quick for small queries, but slow queries should move to a worker thread.
  2. 2. Which is the correct way to include a user's name in a query?

    Parameters keep values separate from the SQL, which prevents SQL injection. String building lets input become part of the command.
  3. 3. What does statement.get() return when no row matches?

    get returns the first row, or undefined when there is none. all returns an empty array in that case.
  4. 4. Why should you not await inside a BEGIN ... COMMIT block with node:sqlite?

    Awaiting yields to the event loop while the transaction is still open. Keep the transaction body synchronous so nothing else can interleave.
  5. 5. How do you read SQLite integers larger than 2^53 without an error?

    setReadBigInts(true) makes the statement return bigint values for integers, so no precision is lost.

Summary

  • node:sqlite is built into Node.js 22.5+ and works without flags in Node.js 24, but it is still marked experimental and prints a warning.
  • new DatabaseSync(':memory:') or a file path opens a database; exec runs schema SQL, prepare returns a reusable statement.
  • Statements offer run, get, all and iterate. Rows are plain objects.
  • Always pass values as ? or :name parameters. Identifiers need an allow-list.
  • Integers become number; use setReadBigInts(true) for huge values. Dates are your responsibility.
  • Write transactions with BEGIN/COMMIT/ROLLBACK, and never await inside them.
  • db.function and db.aggregate add JavaScript logic to SQL; backup copies a live database.
  • Use WAL mode and a busy timeout for real apps, and PRAGMA user_version for migrations.

Next: CSV, Logs and Text Processing