Embedded SQLite — Remembering Things Between Runs

Lesson 0006 · Glossary · 1 · 2 · 3 · 4 · 5 · Next: globs & scheduling

The win, in one line: by the end of this lesson you'll have built a script that fetches an API, remembers what it's already seen in SQLite, and reports only what's new — the stateful backbone of daily chores.

Until now every script forgot everything when it exited. bun:sqlite fixes that: a full SQL database embedded in the runtime — one file, no server, no driver, synchronous and fast. The natural storage layer under your scheduled reports.

1. The embedded model

bun:sqlite gives you a real SQL database that lives in one file on disk. No server to start, nothing to install, no native driver — and unlike Node’s sqlite or a remote DB, calls are synchronous. You open a database, run SQL, close it. (Bun docs: SQLite)

import { Database } from "bun:sqlite";

const db = new Database("tracker.db");   // creates the file if missing
// later: const db = new Database(":memory:");  // throwaway, in RAM

2. Tables and statements

db.exec() runs whole SQL strings (DDL, bulk). To query, you prepare a statement with db.query(), then run it once or many times with parameters:

db.exec(`
  CREATE TABLE IF NOT EXISTS releases (
    repo TEXT PRIMARY KEY,
    tag  TEXT,
    seen_at TEXT
  )`);

const insert = db.query("INSERT OR REPLACE INTO releases (repo, tag, seen_at) VALUES ($repo, $tag, $seen_at)");
insert.run({ $repo: "oven-sh/bun", $tag: "v1.4.1", $seen_at: new Date().toISOString() });

.run(params) executes without returning rows — it resolves to { changes, lastInsertRowid }. For data back, use .all() (array of rows) or .get() (first row, or null). (Bun docs: SQLite — prepared statements)

const rows = db.query("SELECT repo, tag FROM releases").all();
const latest = db.query("SELECT tag FROM releases WHERE repo = ?").get("oven-sh/bun");

// .get() with no match returns null — perfect for "is it new?" checks:
const known = db.query("SELECT tag FROM releases WHERE repo = $repo").get({ $repo: "oven-sh/bun" });
if (known) console.log("already seen", known.tag);

3. Parameters are your defense

Never build SQL strings by concatenating values. Use named ($name) or positional (?) placeholders and pass the values in — SQLite treats them as data, so a stray quote in an API response can’t break your query or inject SQL:

const tag = "v1.4.1')); DROP TABLE releases;--";  // hostile input
db.query("INSERT INTO releases (tag) VALUES (?)").run(tag);  // safe: stored as text

4. Typed rows

Statements are generic — annotate the row shape and get autocompletion and type-checking at the database boundary, exactly the “typing” your mission is about:

type Release = { repo: string; tag: string; seen_at: string };

const latest = db.query<Release>("SELECT * FROM releases WHERE repo = ?").get("oven-sh/bun");
console.log(latest.tag);   // TS knows this is a string

5. Transactions: make several writes one

Synchronous and atomic. Either every statement applies, or none — the pattern for multi-repo updates staying consistent:

const record = db.transaction((repo: string, tag: string) => {
  const stmt = db.query("INSERT OR REPLACE INTO releases (repo, tag, seen_at) VALUES (?, ?, ?)");
  stmt.run(repo, tag, new Date().toISOString());
});

record("oven-sh/bun", "v1.4.1");
record("denoland/deno",  "v2.0.0");   // atomic together

6. Close when done

db.close() flushes and releases the file handle. Simple scripts can skip it — the OS reclaims everything on exit — but close explicitly when you open many.

The bash → Bun map (this layer)

bash (stateful-ish)	Bun + sqlite
append to a log file, `grep` it next run	`INSERT` now, `SELECT ... WHERE` next run
keep “last seen tag” in a temp file	a `releases` table + `.get()`
awk summing a CSV each day	`SELECT COUNT(*), SUM(...) GROUP BY ...`
dedupe by grepping history	`PRIMARY KEY` + `INSERT OR REPLACE`

Practice

Recall check (from memory, no peeking)

1. Where does the database live?

new Database("tracker.db") uses one file on disk, created on first run. No server, no driver — :memory: is the explicit opt-in for throwaway data.

2. Which returns exactly one row or null?

stmt.get(params). .all() returns an array; .run() returns { changes, lastInsertRowid } and executes without returning rows.

3. Which symbol starts a named parameter?

Named params use $name (run({ $name: value })); positional use ?. Either way, values are bound as data — never concatenate SQL strings.

Hands-on: a release tracker

Combine Lesson 5’s fetch with SQLite to build track-releases.ts — the “new since last check” backbone of a daily chore.

Open releases.db and CREATE TABLE IF NOT EXISTS releases (repo TEXT PRIMARY KEY, tag TEXT, seen_at TEXT).
Fetch the latest Bun release (reuse the pattern from Lesson 5: res.ok guard, res.json(), read tag_name).
Look up the stored tag for oven-sh/bun with .get(). If there’s no row, print "first check: vX". If the fetched tag differs from the stored one, print "NEW: vX (was vY)". Then upsert INSERT OR REPLACE. If it matches, print "no new release".
Wrap the write in db.transaction so state stays consistent.
Extend: read a space-separated list of repos from process.env.REPOS (default oven-sh/bun) and loop.

Check your work (immediate feedback):

bun run track-releases.ts        # first run: "first check: v1.4.1" (or NEW)
bun run track-releases.ts        # second run: "no new release"  ← the win!
sqlite3 releases.db 'SELECT * FROM releases;'   # your data survived, typed

If the second run says “no new release,” your script now remembers — that’s the entire stateful backbone of daily scheduling, and it required no server and no npm install.

Read this next

Primary source for this lesson: Bun Docs: SQLite — well-structured into A transactions, prepared statements, and performance toggles. Read the “Optional parameters” section for the two other families of placeholders (? and bare :).

After this, your remaining mission slice is scheduling — and with SQLite in hand, a scheduled job that tracks something daily is closer than ever. That (plus Bun.Glob) is the next pair.