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`](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`) | Parameter | Required | Default | Values | |-----------|----------|---------|--------| | `driver` | yes | — | `better-sqlite3` \| `libsql` \| `bun-sqlite` (D1 has a different lifecycle — see "Cloudflare D1" below) | | `db_url_env` | no | `DATABASE_URL` | env var name | | `schema_dir` | no | `./src/db/schema` | per-table schema files | | `repository_dir` | no | `./src/db/repository` | per-table CRUD modules | | `validators_dir` | no | `./src/db/validators` | drizzle-zod schemas (when `with_zod=true`) | | `client_path` | no | `./src/db/client.ts` | singleton client module | | `migrations_dir` | no | `./drizzle` | drizzle-kit output | 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) | Parameter | Required | Default | Description | |-----------|----------|---------|-------------| | `name` | yes | — | Kebab-case singular: `user`, `order-item`. Used for filenames and TS identifiers (`name_camel`, `name_pascal` derived). | | `table_name` | no | snake_case plural of `name` | SQL table name: `users`, `order_items` | | `pk` | no | `serial-int` | `serial-int` \| `uuid` \| `cuid2` \| `text` | | `timestamps` | no | `true` | adds `createdAt`/`updatedAt` columns | | `soft_delete` | no | `false` | adds nullable `deletedAt` + partial index | | `relations` | no | `[]` | list of related table names — expands `relations()` body | | `with_zod` | no | `true` | emits a drizzle-zod validators file | ## Available Templates ### Project-init templates (emit once) | Template | Output File | When | |----------|-------------|------| | [`drizzle.config.local.ts.template`](assets/templates/drizzle.config.local.ts.template) | `drizzle.config.ts` | `driver` is `better-sqlite3`, `bun-sqlite`, or `libsql` with a `file:` URL | | [`drizzle.config.turso.ts.template`](assets/templates/drizzle.config.turso.ts.template) | `drizzle.config.ts` | `driver` is `libsql` against Turso (remote `libsql:` URL) | | [`client.better-sqlite3.ts.template`](assets/templates/client.better-sqlite3.ts.template) | `{{client_path}}` | `driver` is `better-sqlite3` | | [`client.libsql.ts.template`](assets/templates/client.libsql.ts.template) | `{{client_path}}` | `driver` is `libsql` | | [`client.bun-sqlite.ts.template`](assets/templates/client.bun-sqlite.ts.template) | `{{client_path}}` | `driver` is `bun-sqlite` | | [`schema-index.ts.template`](assets/templates/schema-index.ts.template) | `{{schema_dir}}/index.ts` | Always (initially empty; append exports as tables are added) | | [`gitignore.template`](assets/templates/gitignore.template) | `.gitignore` (append) | Always | ### Per-table templates (emit once per table) | Template | Output File | When | |----------|-------------|------| | [`table.ts.template`](assets/templates/table.ts.template) | `{{schema_dir}}/{{name}}.ts` | Per table | | [`repository.ts.template`](assets/templates/repository.ts.template) | `{{repository_dir}}/{{name}}.ts` | Per table | | [`validators.ts.template`](assets/templates/validators.ts.template) | `{{validators_dir}}/{{name}}.ts` | Per table when `with_zod=true` | ## 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: ```bash # 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 | `pk` value | `pk_definition` | `pk_ts_type` | Extra imports | |---|---|---|---| | `serial-int` (default) | `id: integer().primaryKey({ autoIncrement: true }),` | `number` | — | | `uuid` | `id: text().primaryKey().$defaultFn(() => crypto.randomUUID()),` | `string` | — (uses Web Crypto) | | `cuid2` | `id: text().primaryKey().$defaultFn(() => createId()),` | `string` | `import { createId } from '@paralleldrive/cuid2';` | | `text` | `id: text().primaryKey(),` | `string` | — (caller supplies the ID) | ## 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. | Placeholder | Type | Source / derivation | |---|---|---| | `{{driver}}` | simple sub | `config.json:driver` | | `{{db_url_env}}` | simple sub | `config.json:db_url_env` | | `{{schema_dir}}` | simple sub | `config.json:schema_dir` | | `{{repository_dir}}` | simple sub | `config.json:repository_dir` | | `{{validators_dir}}` | simple sub | `config.json:validators_dir` | | `{{client_path}}` | simple sub | `config.json:client_path` | | `{{migrations_dir}}` | simple sub | `config.json:migrations_dir` | | `{{schema_index_import}}` | simple sub | derived: `client_path` → relative path to `{{schema_dir}}/index.ts` (typically `'./schema'`) | | `{{client_import}}` | simple sub | derived: from a repository file, relative path back to `client_path` (typically `'../client'`) | | `{{schema_import}}` | simple sub | derived: from a repository or validators file, relative path to the matching table file (typically `'../schema/{{name}}'`) | | `{{name}}` | simple sub | per-table param — kebab-case singular (`user`) |