Persistence with SQLite: the data access layer

Use better-sqlite3 with prepared statements, transactions, and a dedicated repository for fast, safe persistence.

9 min · 3 questions

SQLite runs inside your process: no server, no network, per-query latency on the order of a microsecond. That's what makes better-sqlite3's synchronous API not only acceptable but often faster than an async driver: there's no point paying for a promise and an event-loop turn on a read shorter than a tick. The trade-off is real on writes: a slow query blocks the loop. Keep your queries indexed and short, and SQLite handles tens of thousands of reads per second.

npm install better-sqlite3

When opening the database, two settings make the difference. WAL mode (write-ahead logging) allows reads during a write instead of locking the whole file. And the schema is applied idempotently with CREATE TABLE IF NOT EXISTS - plenty good enough before you introduce a real migration tool.

import Database from 'better-sqlite3';

export function createDb(path = 'app.db') {
  const db = new Database(path);
  db.pragma('journal_mode = WAL');
  db.exec(`CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL
  )`);
  return db;
}

The data access layer - the repository - is the only module that speaks SQL. Queries are prepared once at construction and then reused on every call: the engine doesn't re-parse the SQL text, and values pass through placeholders ?, never through concatenation. It's exactly the findAll, findByEmail, insert interface that the service from the previous lesson expects.

export function createUserRepository(db) {
  const insertStmt = db.prepare(
    'INSERT INTO users (email, name) VALUES (?, ?)',
  );
  const byEmailStmt = db.prepare(
    'SELECT * FROM users WHERE email = ?',
  );
  const allStmt = db.prepare(
    'SELECT * FROM users ORDER BY id',
  );
  return {
    insert(user) {
      const info = insertStmt.run(user.email, user.name);
      return { id: info.lastInsertRowid, ...user };
    },
    findByEmail(email) {
      return byEmailStmt.get(email);
    },
    findAll() {
      return allStmt.all();
    },
  };
}

import { createDb } from './connection.js';

const db = createDb();
const insertStmt = db.prepare(
  'INSERT INTO users (email, name) VALUES (?, ?)',
);
const insertMany = db.transaction((users) => {
  for (const user of users) {
    insertStmt.run(user.email, user.name);
  }
});
insertMany([
  { email: 'ada@example.com', name: 'Ada' },
  { email: 'linus@example.com', name: 'Linus' },
]);

Knowledge check

Make sure you remember the key points of this lesson.

1. Why is better-sqlite3's synchronous API not a contradiction in Node?
   • Because Node runs native modules in a separate thread
   • Because SQLite is in-process: the query costs less than the async orchestration it would otherwise avoid
   • Because writes are automatically queued
   • Because WAL mode makes every query non-blocking
2. What do the ? placeholders in a prepared statement guarantee?
   • Values are passed separately from the SQL and can never be executed as code
   • Values are encrypted before being stored
   • The query is automatically cached to disk
3. What twofold benefit does db.transaction bring for a thousand inserts?
   • Data compression and automatic deduplication
   • Atomicity (all or nothing) and a single disk sync instead of a thousand
   • Schema validation and creation of missing indexes