Store data with SQLite

Our notes currently disappear when the process restarts. Bun includes a SQLite driver, so we can persist them without installing another package.

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)!
}

db.query() prepares and caches each SQL statement. The ? placeholder keeps the title separate from the SQL syntax.

Now import the functions in index.ts:

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

Use them in the notes route:

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 },
    )
  },
}

Restart the server, create a note, and restart again. The note remains in notes.sqlite.

Add notes.sqlite to .gitignore. Runtime data does not belong in the source repository.