Store data with SQLite

Our notes live in an array. Restart the server and they’re gone. Let’s fix that.

Bun includes a SQLite driver, bun:sqlite, so we can persist data without installing anything. SQLite stores the whole database in one file on disk. For a small API it’s a great choice: no server to run, nothing to configure, and it’s fast.

Create database.ts:

import { Database } from 'bun:sqlite'

export type Note = {
  id: number
  title: string
}

const databasePath = Bun.env.DATABASE_PATH ?? 'notes.sqlite'
const db = new Database(databasePath, { create: true })

db.run(`
  CREATE TABLE IF NOT EXISTS notes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL
  )
`)

const listQuery = db.query<Note, []>(`
  SELECT id, title
  FROM notes
  ORDER BY id DESC
`)

const createQuery = db.query<Note, [string]>(`
  INSERT INTO notes (title)
  VALUES (?)
  RETURNING id, title
`)

export function listNotes() {
  return listQuery.all()
}

export function createNote(title: string) {
  return createQuery.get(title)!
}

Let’s walk through it. new Database() opens the file, and create: true creates it when it doesn’t exist. db.run() executes a statement once, here creating the table if it’s missing, so the file is ready on the first start.

db.query() prepares a statement and caches it, so calling listNotes() a thousand times parses the SQL once. The two type arguments tell TypeScript what each row looks like and what parameters the statement takes.

The ? is a placeholder. The title is passed separately and SQLite treats it as a value, never as SQL. That’s what protects us from SQL injection: a title like '); DROP TABLE notes; -- is stored as a slightly odd string instead of being executed. Never build SQL with string concatenation when a placeholder will do.

RETURNING id, title makes the insert hand back the row it created, so we don’t need a second query to find the new ID.

Now import the functions in index.ts:

import { createNote, listNotes } from './database'

Use them in the notes route, replacing the array:

const notesRoute = {
  GET: () => Response.json(listNotes()),
  POST: async (request: Request) => {
    const json = await request.json().catch(() => null)
    const result = NoteInput.safeParse(json)

    if (!result.success) {
      return Response.json(
        { error: 'Send a title between 1 and 120 characters' },
        { status: 400 },
      )
    }

    return Response.json(
      createNote(result.data.title),
      { status: 201 },
    )
  },
}

Notice that the handler barely changed. Validation stayed where it was, and the array operations became two function calls.

Now test the whole point of this lesson. Start the server, create a note with the curl command from the previous lesson, and stop the server. Start it again and request /api/notes. The note is still there, sitting in notes.sqlite.

Add notes.sqlite to .gitignore. It’s runtime data, it changes every time someone uses the app, and it does not belong in the source repository.