SQLite & queries

Think of SQLite as a single-file database - an array of objects that survives restarts, with SQL as the query language. Zero Docker required.

Why SQLite for this tutorial

• One file on disk (data.sqlite)
• No separate Postgres process to babysit while learning Express
• Good enough for local apps, prototypes, many small products
• When you outgrow it, the SQL you learned mostly transfers to Postgres

FE map: notes = [] in memory → notes table in SQLite. filter → WHERE. push → INSERT.

Install

npm install better-sqlite3
npm install -D @types/better-sqlite3

better-sqlite3 is sync and fast for local work. That's fine here - keep queries tiny.

Connect + create a table

// src/db.ts
import Database from 'better-sqlite3'
import { config } from './config.js'

export const db = new Database(config.databasePath)

export function initDb() {
  db.exec(`
    CREATE TABLE IF NOT EXISTS notes (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      title TEXT NOT NULL,
      body TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
  `)
}

Call initDb() once at boot from index.ts.

CRUD without an ORM

export function listNotes() {
  return db.prepare('SELECT id, title, body, created_at FROM notes ORDER BY id DESC').all()
}

export function createNote(title: string, body: string) {
  const info = db
    .prepare('INSERT INTO notes (title, body) VALUES (?, ?)')
    .run(title, body)
  return findNote(Number(info.lastInsertRowid))
}

export function findNote(id: number) {
  return db
    .prepare('SELECT id, title, body, created_at FROM notes WHERE id = ?')
    .get(id) as { id: number; title: string; body: string; created_at: string } | undefined
}

Comments for the code above:

• ? placeholders - always use these; never string-concatenate user input into SQL
• .all() → array of rows (like fetch → JSON array)
• .get() → one row or undefined
• .run() → writes; lastInsertRowid is the new id

Wire a route

notesRouter.get('/', (_req, res) => {
  res.json({ notes: listNotes() })
})

notesRouter.post('/', (req, res) => {
  const title = String(req.body?.title ?? '').trim()
  if (!title) return res.status(400).json({ error: 'title required' })
  const note = createNote(title, String(req.body?.body ?? ''))
  res.status(201).json({ note })
})

Mental model vs Prisma / Drizzle

ORMs are optional sugar:

Raw SQL helpers	ORM
You write SQL	Schema → generated client
Tiny + explicit	Migrations, relations helpers
Easy to see the query	Easy to accidentally N+1

We'll stay on SQL helpers for users too - clearer for learning auth. Prisma gets its own chapter later (ORM with Prisma).

File location tip

Put data.sqlite outside src/, gitignore it, keep a clean DB by deleting the file when schema changes during learning.

Next: a real users table with hashed passwords - the foundation for accounts.

Best,

-- ł.