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

Placeholder Reference

Every {{placeholder}} the templates use, with its derivation rule. Items marked simple sub are find-and-replace; items marked block require the agent to expand per the rules in the next section.

If a template emits a {{placeholder}} not in this table, that's a bug — file it under gotchas.md.

Block Expansions

The table.ts.template uses placeholder blocks for variable-shaped sections — the agent must expand them per parameters, not just text-substitute.

{{timestamps_block}} (when timestamps=true)

createdAt: integer({ mode: 'timestamp_ms' })
  .notNull()
  .$defaultFn(() => new Date()),
updatedAt: integer({ mode: 'timestamp_ms' })
  .notNull()
  .$defaultFn(() => new Date())
  .$onUpdateFn(() => new Date()),

When timestamps=false, remove the line entirely (don't leave the comment marker).

{{soft_delete_block}} (when soft_delete=true)

In the columns block:

deletedAt: integer({ mode: 'timestamp_ms' }),

In the indexes block:

index('{{table_name}}_active_idx').on(table.deletedAt).where(sql`deleted_at IS NULL`),

(Adjust the sql import accordingly.)

{{relations_body}} (when relations is non-empty)

For each related table in relations[], the agent decides whether it's a one or many based on whether the FK lives on the current table (then it's one) or on the related table (then it's many):

// FK on current table — `one`:
parent: one(parents, { fields: [users.parentId], references: [parents.id] }),

// FK on related table — `many`:
posts: many(posts),

If the user can't easily tell, default to one example of each and leave a comment.

{{indexes}} block

For each FK column, emit:

index('{{table_name}}_{{column}}_idx').on(table.{{columnCamel}}),

Cloudflare D1 note

D1 has a different lifecycle: the client is constructed per request from env.DB (the binding), not as a module-level singleton. This skill doesn't ship a D1 client template — follow the official D1 + Drizzle guide for the wiring. The table.ts.template, repository.ts.template, and validators.ts.template are still usable with D1 — only the client module differs.

Reference Files

Related Skills

• drizzle-sqlite — The library-reference rules these templates encode. The conventions doc cites specific rule filenames from it. Read it when you need to make an informed exception, debug a generated file, or scaffold something outside the templates' scope (custom migrations, complex queries, performance work).
• better-auth-scaffold — Scaffolds Better Auth on top of a Drizzle DB; can be run after this skill provides the client.