sqlite-dev

SQLite for Local Dev — Postgres-Portable, Bun + Drizzle

🎯 Why: Design for Change

The goal of writing software is to be able to change it safely — including changing the database. Every rule here (snake_case, plural tables, NOT NULL FKs, JSON document store) exists so the schema ports to Postgres with a dialect swap, not a rewrite. Portability is change-safety: when SQLite stops fitting, you move with a Drizzle config edit instead of a month of forensics.

SQLite here is the development database for a TypeScript/Bun app whose production system of record will almost certainly be Postgres. That single fact drives every rule: write the SQLite schema so that switching to Postgres is a Drizzle dialect change and a data copy, not a redesign. Use the same names, the same modeling discipline, and the same integrity rules as postgres-dba — SQLite is just a smaller engine running them.

This skill is about the SQLite schema, the Drizzle layer, and operating SQLite. For schema-design philosophy (why NOT NULL FKs, why a surrogate key) the authority is postgres-dba; this skill does not re-argue it, it ports it. For app-layer TypeScript use the language skills.

How to use this skill

1. Match the task to a rule below or in the decision guide.
2. Open the matching references/*.md for the rationale, the wrong way, the right way, and the explicit-override escape hatch.
3. Copy the closest file from templates/ and adapt it — the templates already encode every convention, so you start compliant and portable.
4. Apply the rule unless you can state, in a code comment, the specific reason it does not apply. "SQLite let me" is not a reason — SQLite lets you do almost anything; Postgres will not.

The hard rules (non-negotiable defaults)

1. Naming is identical to postgres-dba. snake_case, lowercase, unquoted. Tables plural (orders), columns singular (shipped_at), primary key literally id, FKs <table_singular>_id, booleans is_/has_, timestamps past-tense _at. In Drizzle the TypeScript property may be camelCase, but the column name argument is always the snake_case name — createdAt: integer('created_at', …). The database never sees a quoted mixed-case identifier. See references/naming.md.