Embedded Databases

Your route handlers need somewhere durable to keep data — todos, notes, whatever your app is about — that survives a server restart. This chapter covers relational database basics, why SQLite is a great fit for a project like this, and how to evolve its schema safely over time.

Tables, rows, and columns #

A relational database organizes data into tables. Each table has a fixed set of columns (with types), and each row is one record. A small notes app might have two tables:

Every row needs a way to be referenced uniquely — that's the primary key, usually an auto-incrementing id. When one table needs to point at a row in another table — a note belonging to a tag — it stores that row's primary key in a column called a foreign key. That's the entire relationship: no duplication of the tag's name in every note, just a number pointing at where the real data lives.

SQL basics #

SQL is the language you use to read and write rows. Three statements cover most of what a typical app needs:

-- read all notes with a given tag
SELECT * FROM notes WHERE tag_id = 3;

-- create a new note
INSERT INTO notes (title, body, tag_id) VALUES ('Groceries', 'Milk, eggs', 3);

-- update one note by id
UPDATE notes SET body = 'Milk, eggs, bread' WHERE id = 17;

Notice the WHERE clause on the update. Without it, that statement would overwrite the body column on every row in the table — one of the most common and most destructive SQL mistakes. Always know what a statement's WHERE clause is limiting before you run it.

Prepared statements, and why string concatenation is dangerous #

It's tempting to build a query by gluing a variable straight into the SQL string:

const title = req.body.title;
db.exec(`INSERT INTO notes (title) VALUES ('${title}')`);

If title is ever something an untrusted user typed — and in a web app, it always is — this is dangerous. A value like '); DROP TABLE notes; -- would close the intended string early and splice in a second statement that the database would happily run. This class of bug is called SQL injection: not because of any exotic technique, but simply because the database can't tell the difference between "data" and "code" once they've been concatenated into the same string.

The fix is a prepared statement: you write the SQL with ? placeholders, and pass the actual values separately. The database library sends them as data, never as SQL text, so there's nothing for an attacker to break out of.

const insert = db.prepare('INSERT INTO notes (title) VALUES (?)');
insert.run(req.body.title);

Embedded databases and WAL mode #

Some databases (Postgres, MySQL) run as their own long-lived process that your server connects to over the network. SQLite takes a different approach: there's no separate server, no network hop, no process to install and keep running. The entire database is one file on disk, and your application process reads and writes it directly through a library like better-sqlite3. For a single-server app, that's simpler to run and reason about. Backing it up is simple too, though not quite as simple as "copy the file" once WAL mode is on, below — reads and writes are also synchronous and run on the same thread as the rest of your code, a trade-off worth knowing about under heavy load.

The one thing you have to think about with an embedded database is concurrent access: what happens when a background job is writing while a request handler is reading? SQLite's answer is WAL mode (write-ahead logging, see SQLite's WAL documentation). Writes go to a separate log file first and get merged into the main database later, which means readers never block on a writer and generally see a consistent snapshot. You turn it on once, right after opening the database:

import Database from 'better-sqlite3';

const db = new Database('app.db');
db.pragma('journal_mode = WAL');

A pragma is SQLite's mechanism for configuring the connection itself, as opposed to reading or writing data.

Evolving the schema: numbered migrations #

Your schema will change as the app grows — a new column, a new table, a new index. You can't just edit the CREATE TABLE statement and rerun it against a database that already has data in it. The standard pattern is a migration: a small function that applies exactly one change, numbered in the order it should run, tracked in a table so the database itself remembers which migrations it has already seen.

const migrations = [
  {
    id: 1,
    up: (db) => {
      db.exec(`CREATE TABLE notes (
        id INTEGER PRIMARY KEY,
        title TEXT NOT NULL,
        body TEXT
      )`);
    },
  },
  {
    id: 2,
    up: (db) => {
      db.exec('CREATE TABLE tags (id INTEGER PRIMARY KEY, name TEXT NOT NULL)');
      db.exec('ALTER TABLE notes ADD COLUMN tag_id INTEGER REFERENCES tags(id)');
    },
  },
];

Every statement above uses db.exec(), the same method flagged earlier as unsafe for building queries out of user input. It's safe here for a different reason than prepared statements are safe: there is no user input involved at all — every string is a fixed literal you wrote, checked into source control. The danger was never db.exec() itself, only using it with data that didn't come from your own code.

That REFERENCES tags(id) in migration 2 declares a foreign key, but SQLite doesn't actually enforce foreign keys unless you turn that behavior on — the constraint above is currently decorative:

db.pragma('foreign_keys = ON');

Run that once per connection, typically right after opening the database. Without it, deleting a row from tags that a note still references silently leaves a dangling tag_id behind instead of being blocked or cascading, and the diagram's promised relationship isn't actually guaranteed by anything.

function migrate(db) {
  db.exec(`CREATE TABLE IF NOT EXISTS schema_migrations (
    id INTEGER PRIMARY KEY
  )`);

  const applied = new Set(
    db.prepare('SELECT id FROM schema_migrations').all().map((row) => row.id)
  );

  for (const migration of migrations) {
    if (applied.has(migration.id)) continue;
    const runMigration = db.transaction(() => {
      migration.up(db);
      db.prepare('INSERT INTO schema_migrations (id) VALUES (?)').run(migration.id);
    });
    runMigration();
  }
}

db.transaction() wraps the schema change and the bookkeeping insert together: if either one throws partway through, better-sqlite3 rolls back everything the transaction did, so a failed migration never leaves the schema half-changed with no record of what happened. Without it, a crash between the CREATE TABLE and the INSERT INTO schema_migrations line leaves a database that's already been changed but doesn't know it — the next run tries the same migration again and fails on a table that already exists.

This is deliberately lower-tech than a full ORM migration tool. It's a list, a loop, and a table — but it gives you the property that actually matters: every environment (your laptop, a teammate's laptop, production) converges on the same schema by running the same numbered steps in the same order, no matter when it last started up.

With storage and schema evolution covered, the next chapter tackles a different problem: how do you get an update from the server to the browser without the browser having to ask for it first?