Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 39 ยท File I/O, SQLite and Data

SQLite with bun:sqlite

Use Bun's built-in SQLite driver: Database, query vs prepare, all/get/run, named parameters, transactions, WAL mode, migrations and safe SQL, with a runnable node:sqlite stand-in.

SQLite is a complete SQL database stored in a single file (or in memory), with no server to install. It is a great fit for small and medium apps, internal tools, caches, CLIs and tests. Bun ships a fast built-in driver, bun:sqlite, so you need no npm package and no native build step. Its API is synchronous: calls return results directly, with no await, which is fine because SQLite runs in-process and queries take microseconds.

Bun is not available on this site. The bun:sqlite snippets are read-only. The runnable blocks use Node 24's built-in node:sqlite, which has a very similar shape (prepare, all, get, run). Names and a few details differ and we point them out. It is a stand-in, not Bun's driver.

Opening a database

TypeScript
import { Database } from "bun:sqlite";

const db = new Database("app.db");          // file; created if missing
const mem = new Database(":memory:");       // lives only as long as the process
const ro = new Database("app.db", { readonly: true });
const strict = new Database("app.db", { strict: true }); // missing params throw

Close it with db.close() when your program ends (for servers, usually never, or in a shutdown handler). Enable the faster, concurrent-reader-friendly journal mode once per database file, and foreign keys on every connection:

TypeScript
db.run("PRAGMA journal_mode = WAL;");
db.run("PRAGMA foreign_keys = ON;");

Creating tables and inserting

db.run(sql, params?) runs one statement without returning rows. db.exec(sql) is similar; use it for multi-statement scripts.

TypeScript
db.run(`
  CREATE TABLE IF NOT EXISTS todos (
    id    INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    done  INTEGER NOT NULL DEFAULT 0
  )
`);

const result = db.run("INSERT INTO todos (title) VALUES (?)", ["Learn bun:sqlite"]);
console.log(result.lastInsertRowid, result.changes); // 1, 1

query() and prepare(): reading rows

db.query(sql) compiles a statement and caches it by SQL text, so calling db.query with the same string again is cheap. db.prepare(sql) returns a fresh, uncached statement. Both return a Statement with these methods:

Method Returns
.all(...params) an array of row objects
.get(...params) the first row, or null
.run(...params) { lastInsertRowid, changes } (for INSERT/UPDATE/DELETE)
.values(...params) an array of arrays (no column names)
.iterate(...params) an iterator that yields one row at a time (huge results)
TypeScript
const insert = db.query("INSERT INTO todos (title) VALUES ($title)");
insert.run({ $title: "Write tests" });
insert.run({ $title: "Deploy" });

const all = db.query("SELECT * FROM todos ORDER BY id").all();
// [{ id: 1, title: "Learn bun:sqlite", done: 0 }, ...]

const one = db.query("SELECT * FROM todos WHERE id = ?1").get(2);
const count = db.query("SELECT COUNT(*) AS n FROM todos").get() as { n: number };

Parameters can be positional (?, ?1) or named ($title, :title, @title). Always use parameters, never string concatenation: SELECT * FROM users WHERE name = '${name}' is the classic SQL injection hole. With parameters, the database treats the value strictly as data.

Rows can be mapped onto a class with .as(User), which sets the prototype (and so methods and getters) but does not call the constructor.

Node.js Create, insert, query with parameters (node:sqlite stand-in)

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

By default each statement commits on its own, which is slow for bulk work and unsafe for multi-step changes. A transaction groups statements: either all succeed or none do. db.transaction(fn) returns a function that wraps fn in BEGIN/COMMIT and rolls back if it throws.

TypeScript
const insertTodo = db.prepare("INSERT INTO todos (title) VALUES (?)");

const insertMany = db.transaction((titles: string[]) => {
  for (const title of titles) insertTodo.run(title);
  return titles.length;
});

insertMany(["a", "b", "c"]); // one commit, very fast

Inserting 100,000 rows inside one transaction can be hundreds of times faster than 100,000 separate commits, because each commit forces a disk sync. Transactions also fix correctness problems like moving money: debit and credit must succeed together.

Node.js A transaction rolls back on error

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.

Migrations

A schema changes as the app grows. A minimal migration system stores a version number in SQLite's user_version pragma and applies the missing steps in order, inside a transaction:

TypeScript
const migrations = [
  `CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT UNIQUE NOT NULL)`,
  `ALTER TABLE users ADD COLUMN name TEXT`,
  `CREATE INDEX idx_users_name ON users (name)`,
];

function migrate(db: Database) {
  const { user_version } = db.query("PRAGMA user_version").get() as { user_version: number };
  const apply = db.transaction(() => {
    for (let v = user_version; v < migrations.length; v++) {
      db.run(migrations[v]);
    }
    db.run(`PRAGMA user_version = ${migrations.length}`);
  });
  apply();
}

Never edit an applied migration; add a new one. For larger projects an ORM such as Drizzle works with bun:sqlite (bun add drizzle-orm).

Handy details

  • Types: SQLite integers come back as JS numbers; for values above 2^53 construct the database with { safeIntegers: true } to get bigint. BLOB maps to Uint8Array. SQLite has no real boolean or date type: store 0/1 and ISO-8601 text or a Unix timestamp.
  • Serialization: db.serialize() returns the database as a Uint8Array and Database.deserialize(bytes) loads one, useful for tests and snapshots.
  • In a server: open one Database at startup and share it. Reuse prepared statements (db.query caches them). With WAL mode, many readers and one writer work together.
  • In tests: use :memory: so each test starts clean (see hooks).
  • Where to learn SQL itself: this lesson focuses on the driver; the site's SQL tutorial covers the language.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which bun:sqlite statement method returns an array of all matching rows?

    .all() returns every row; .get() returns the first row; .run() is for writes.
  2. 2. Why should you pass user input as parameters instead of building SQL strings?

    Concatenated SQL lets input change the query. Parameters never do.
  3. 3. What does db.transaction(fn) do if fn throws?

    The wrapper issues ROLLBACK on error and rethrows it.
  4. 4. What is the main difference between db.query() and db.prepare()?

    query reuses compiled statements for identical SQL; prepare compiles every time you call it.
  5. 5. Why wrap 100,000 inserts in one transaction?

    Per-statement commits dominate the cost of bulk inserts.

Summary

  • new Database(path | ":memory:"); enable PRAGMA journal_mode = WAL and foreign_keys = ON.
  • run for writes; query(...).all/get/values/iterate for reads; prepare for uncached statements.
  • Always use ? or $name parameters; never concatenate input.
  • db.transaction makes multi-statement changes atomic and bulk inserts fast.
  • Track schema versions with PRAGMA user_version or an ORM.

Next: Bun Shell and Child Processes