Drizzle Sqlite Scaffold

Drizzle SQLite Scaffold

Parameterized templates for bootstrapping Drizzle + SQLite in a fresh project, or adding a new table/repository to an existing one. Every output bakes in the conventions documented in references/conventions.md — explicit primary keys, indexed foreign keys, relations() declarations, $inferSelect/$inferInsert exports, timestamp_ms dates, boolean-mode bools, WAL + foreign_keys=ON + busy_timeout pragmas, singleton client with HMR guard, and CRUD helpers using .returning() + inArray() + .onConflictDoUpdate().

When to Apply

Reach for these templates when:

• Starting a new project that will use Drizzle with SQLite (any driver)
• Adding a new table to an existing Drizzle project — the table file should match the existing patterns
• Adding a CRUD repository module for an existing table
• Refactoring a hand-rolled Drizzle setup that's missing pragmas, has no relations(), or has hand-written User types that drift from the schema
• Migrating from another ORM (Prisma, Kysely) to Drizzle and wanting consistent shapes from the start

Setup

Required parameters (asked on first use, saved to config.json)

If config.json already exists with values, this skill uses them; otherwise it asks via AskUserQuestion.

Per-table parameters (asked each time a new table is scaffolded)

Available Templates

Project-init templates (emit once)

Per-table templates (emit once per table)

How to Use

Flow A — Initialize a new project (run once)

1. Resolve project parameters. Read config.json. For any required field that's empty, ask the user via AskUserQuestion (driver is the only strictly required one; the rest have sensible defaults).

2. Install runtime + tooling first so the rendered files type-check immediately:

   # Pick the driver-specific runtime package:
   npm install drizzle-orm @libsql/client      # for libsql
   npm install drizzle-orm better-sqlite3      # for better-sqlite3
   npm install drizzle-orm                     # bun:sqlite is built into Bun
   
   # Dev tools (all drivers):
   npm install -D drizzle-kit
   npm install -D @types/better-sqlite3        # better-sqlite3 only
   npm install -D drizzle-zod zod              # if with_zod=true

3. Pick the config and client variants for the resolved driver:

   • better-sqlite3 → drizzle.config.local.ts.template + client.better-sqlite3.ts.template
   • libsql with file: URL → drizzle.config.local.ts.template + client.libsql.ts.template
   • libsql with remote Turso URL → drizzle.config.turso.ts.template + client.libsql.ts.template
   • bun-sqlite → drizzle.config.local.ts.template + client.bun-sqlite.ts.template

4. Render and write the project-init files:

   • drizzle.config.ts
   • {{client_path}} (typically src/db/client.ts)
   • {{schema_dir}}/index.ts (empty barrel)
   • Append the gitignore.template block to the project's .gitignore

5. For libsql: the client template uses top-level await migrate(...). Verify tsconfig.json has "module": "ESNext" (or "NodeNext") and "target": "ES2022"+ for top-level await support. If the runtime is CommonJS, replace the top-level await with an exported async function init() the app calls during startup.

6. Save resolved values to config.json so subsequent table runs don't re-prompt.

Flow B — Add a new table (run per table)

1. Resolve per-table parameters. Ask the user for name, then offer defaults for table_name (snake_case plural), pk, timestamps, soft_delete, relations, with_zod. Use AskUserQuestion for any non-default the user wants.

2. Compute derived identifiers:

   • name_camel — camelCase of name (user, orderItem)
   • name_pascal — PascalCase of name (User, OrderItem)
   • pk_field — the PK column name (id for all 4 pk modes)
   • pk_ts_type — TS type for the PK (number for serial-int, string for uuid/cuid2/text)
   • pk_definition — the actual line, e.g., id: integer().primaryKey({ autoIncrement: true }), (see PK Variants table below)

3. Render the table template: Read table.ts.template, substitute {{name}}, {{name_camel}}, {{name_pascal}}, {{table_name}}, {{pk_definition}}, {{pk_extra_imports}}, etc. Expand {{timestamps_block}} and {{soft_delete_block}} per the parameters (see "Block Expansions" below). Write to {{schema_dir}}/{{name}}.ts.

4. Render the repository template with the same parameters. Write to {{repository_dir}}/{{name}}.ts.

5. If with_zod=true, render the validators template. Write to {{validators_dir}}/{{name}}.ts.

6. Append to the schema barrel: Add export * from './{{name}}'; to {{schema_dir}}/index.ts.

7. Generate the migration: Tell the user to run npx drizzle-kit generate to produce the SQL file. Remind them to answer rename prompts explicitly if this scaffold replaces an existing differently-named table.

8. Apply the migration: Run npx drizzle-kit migrate against the dev database. The client templates also call migrate(...) on boot, but applying once in the dev loop confirms the SQL works before the next process restart.

Flow C — Add a CRUD module for an existing table (no schema change)

Same as Flow B steps 1-2, but skip the table.ts.template render and just emit the repository (and optionally validators) modules.

PK Variants