32 — SQLite in TypeScript with better-sqlite3

Level: Intermediate · Time: ~40 min · Prerequisites: Lesson 31 — Databases and SQL Fundamentals

---

Why this matters

Lesson 31 typed SQL into a client. A program needs the same statements, constraints and transactions driven from TypeScript, with rows arriving as domain objects. SQLite is the smallest honest step.

What SQLite is, and what it is not

SQLite is a C library your process links against: no server, no port, no user account, just a file your program opens.

	SQLite	A PostgreSQL server
Concurrency	Many readers, one writer at a time	Many concurrent writers
Types	Storage classes with column affinity; a TEXT column happily holds 123	Strict, rich types: jsonb, numeric, timestamptz, arrays

The single-writer rule is the trade for zero administration; Lesson 33 shows what changes when the tracker becomes a service.

better-sqlite3 is synchronous on purpose

A SQLite read is a local file read measured in microseconds, so wrapping it in a promise buys nothing: the engine runs in-process, blocks deliberately, and statements are prepared once and reused.

// src/db/connection.ts
import Database from "better-sqlite3";

export type Db = InstanceType<typeof Database>;

export function openDatabase(path = "data/solar.db"): Db {
  const db = new Database(path);
  db.pragma("journal_mode = WAL"); // readers stop blocking the writer
  db.pragma("foreign_keys = ON"); // off by default, and per connection
  db.pragma("busy_timeout = 5000"); // wait for the writer instead of throwing SQLITE_BUSY
  return db;
}

.run() returns changes and lastInsertRowid, .get() returns one row or undefined, and .all() returns an array.

Schema and a minimal migration mechanism

PRAGMA user_version is an integer kept in the file header: apply every migration numbered above it inside a transaction and raise it.

// src/db/migrate.ts
import type { Db } from "./connection.js";
const migrations: readonly string[] = [
  `CREATE TABLE sites (site_id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE,
                       capacity_kwp REAL NOT NULL CHECK (capacity_kwp > 0));
   CREATE TABLE daily_production (site_id INTEGER NOT NULL REFERENCES sites(site_id) ON DELETE CASCADE,
                                  day TEXT NOT NULL, kwh REAL NOT NULL CHECK (kwh >= 0),
                                  PRIMARY KEY (site_id, day));`,
];
export function migrate(db: Db): number {
  let version = db.pragma("user_version", { simple: true }) as number;
  for (const sql of migrations.slice(version)) {
    db.transaction(() => {
      db.exec(sql);
      version += 1;
      db.pragma(`user_version = ${version}`); // our own integer literal, never user input
    })();
  }
  return version;
}

Adding a migration means appending to that array; nothing already applied is ever edited, so an old database and a fresh one end in the same state.

A typed repository: rows to domain objects

A prepared statement declares the shape of its rows, so the mapping from snake_case columns to camelCase fields happens once, in the repository.

// src/db/productionRepository.ts
import type { Db } from "./connection.js";

export interface DailyProduction { readonly siteId: number; readonly day: string; readonly kwh: number; }
export interface MonthlyTotal { readonly month: string; readonly kwh: number; readonly days: number; }
interface ProductionRow { readonly site_id: number; readonly day: string; readonly kwh: number; }

export class ProductionRepository {
  constructor(private readonly db: Db) {}

  saveMany(rows: readonly DailyProduction[]): number {
    const statement = this.db.prepare<[number, string, number]>(
      `INSERT INTO daily_production (site_id, day, kwh) VALUES (?, ?, ?)
       ON CONFLICT (site_id, day) DO UPDATE SET kwh = excluded.kwh`,
    );
    const write = this.db.transaction((batch: readonly DailyProduction[]) => {
      for (const row of batch) statement.run(row.siteId, row.day, row.kwh);
      return batch.length;
    });
    return write(rows);
  }

  findBySite(siteId: number, from: string, to: string): DailyProduction[] {
    return this.db.prepare<[number, string, string], ProductionRow>(
      `SELECT site_id, day, kwh FROM daily_production WHERE site_id = ? AND day BETWEEN ? AND ? ORDER BY day`,
    ).all(siteId, from, to).map((row) => ({ siteId: row.site_id, day: row.day, kwh: row.kwh }));
  }

  monthlyTotals(siteId: number): MonthlyTotal[] {
    return this.db.prepare<[number], MonthlyTotal>(
      `SELECT substr(day, 1, 7) AS month, ROUND(SUM(kwh), 1) AS kwh, COUNT(*) AS days
       FROM daily_production WHERE site_id = ? GROUP BY month ORDER BY month`,
    ).all(siteId);
  }
}

ON CONFLICT … DO UPDATE makes the import idempotent: re-running the same week updates values instead of failing on the composite primary key.

Transactions, throughput, tests and the report

Batch inserts are the difference between a fast import and a slow one: every COMMIT is a durability write to disk, so wrapping a batch in db.transaction() turns ten thousand inserts into well under a second. Tests need no reset step: :memory: creates a database that dies with the connection.

// test/productionRepository.test.ts
import { test } from "node:test";
import assert from "node:assert/strict";
import { openDatabase } from "../src/db/connection.js";
import { migrate } from "../src/db/migrate.js";
import { ProductionRepository, type DailyProduction } from "../src/db/productionRepository.js";

function fixtureRepository(): ProductionRepository {
  const db = openDatabase(":memory:"); // a fresh database per test
  migrate(db);
  db.prepare("INSERT INTO sites (site_id, name, capacity_kwp) VALUES (?, ?, ?)").run(1, "Sokone", 12.5);
  return new ProductionRepository(db);
}

const march: readonly DailyProduction[] = [
  { siteId: 1, day: "2026-03-01", kwh: 48.2 }, { siteId: 1, day: "2026-03-02", kwh: 51.7 }];

test("monthly totals sum the days that were written", () => {
  const repository = fixtureRepository();
  assert.equal(repository.saveMany(march), 2); // and a second import duplicates nothing
  assert.deepEqual(repository.monthlyTotals(1), [{ month: "2026-03", kwh: 99.9, days: 2 }]);
});

The export is eleven lines: format each row as month,kwh,days, join with newlines, and write the file.

// src/report/monthlyReport.ts
import type { MonthlyTotal } from "../db/productionRepository.js";

export function toCsv(rows: readonly MonthlyTotal[]): string {
  const body = rows.map((row) => `${row.month},${row.kwh.toFixed(1)},${row.days}`);
  return ["month,kwh,days", ...body].join("\n") + "\n";
}

With that, the tracker's loop is closed: fetch the days the Lesson 30 boundary validated, saveMany them, then answer questions from the file.

---

Common mistakes

Mistake	What happens	The fix
Forgetting PRAGMA foreign_keys = ON	REFERENCES is silently ignored and orphan rows appear	Set it in the connection factory
Committing once per row in a loop	An import that should take a second takes a minute	Wrap the batch in db.transaction()
Preparing a statement inside a loop	The same SQL is compiled thousands of times	Prepare once, outside the loop
Treating column types as enforced	A TEXT column holds 123, and the compiler never sees a row	Validate or coerce in the row mapping
Testing against the real database file	Tests depend on leftover rows and on each other	:memory: with a fresh repository per test

---

Try it

1. Install better-sqlite3 and @types/better-sqlite3, then prove the native module loaded:

node -e "const D=require('better-sqlite3');console.log(new D(':memory:').prepare('SELECT sqlite_version() AS v').get())"

Expected output: { v: '3.45.1' }. If the install tries to compile and fails, run sudo apt install build-essential python3.

1. Write the files above plus a src/seed.ts inserting site 1 and 90 days of synthetic production, each value computed from the day of year so the run is repeatable, then run npx tsc && node dist/src/seed.js.

Expected output: a line reporting 90 rows written and a data/solar.db of a few tens of kilobytes.

1. Run npx tsc && node --test dist/test/, then write reports/monthly.csv with toCsv.

Expected output: # pass 1 and # fail 0; the CSV holds a header plus one line per month of site 1.

---

Key takeaways

• SQLite is a library and a file, not a server: zero administration, one writer at a time, ideal for local tools and tests.
• better-sqlite3 is synchronous by design because a local file read is not worth a promise; prepare statements once and keep them short.
• PRAGMA user_version is enough of a migration mechanism for a local tool when each migration runs in a transaction.
• Repositories map rows to domain objects in one place; ON CONFLICT … DO UPDATE makes an import safe to repeat, and db.transaction() with :memory: makes imports fast and tests independent.

Check yourself

1. Why is a blocking, synchronous call right for a local file and wrong for a server across a network?
2. Your first import wrote 90 rows and your second also wrote 90. How many rows are in the table, and which part of the SQL guarantees it?
3. Why does migrate() run each migration in a transaction, and what would a half-applied migration leave behind?

---

Next

Lesson 33 — PostgreSQL with Prisma and Migrations