Drizzle Sqlite Scaffold

// {{client_path}} — singleton Drizzle client for better-sqlite3 (sync, Node)
// Generated by drizzle-sqlite-scaffold. Substitute {{placeholders}}.
//
// Enforces: WAL mode, foreign_keys=ON, busy_timeout, HMR-safe singleton.
// See drizzle-sqlite rules: conn-enable-wal, conn-foreign-keys-pragma,
//   conn-set-busy-timeout, conn-singleton-client.

import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/better-sqlite3';
import { migrate } from 'drizzle-orm/better-sqlite3/migrator';
import * as schema from '{{schema_index_import}}';

declare global {
  // eslint-disable-next-line no-var
  var __sqlite__: Database.Database | undefined;
}

function initSqlite(): Database.Database {
  const sqlite = new Database(process.env.{{db_url_env}} ?? './local.db');
  sqlite.pragma('journal_mode = WAL');
  sqlite.pragma('synchronous = NORMAL');
  sqlite.pragma('foreign_keys = ON');
  sqlite.pragma('busy_timeout = 5000');
  return sqlite;
}

const sqlite = globalThis.__sqlite__ ?? initSqlite();
if (process.env.NODE_ENV !== 'production') {
  globalThis.__sqlite__ = sqlite;
}

export const db = drizzle(sqlite, { schema });
export const rawSqlite = sqlite;

// Apply migrations on boot. For multi-instance deploys, prefer a dedicated
// pre-deploy job and remove this line.
migrate(db, { migrationsFolder: '{{migrations_dir}}' });

// {{client_path}} — singleton Drizzle client for bun:sqlite (sync, Bun runtime)
// Generated by drizzle-sqlite-scaffold. Substitute {{placeholders}}.
//
// Enforces: WAL mode, foreign_keys=ON, busy_timeout.
// See drizzle-sqlite rules: conn-enable-wal, conn-foreign-keys-pragma,
//   conn-set-busy-timeout, conn-singleton-client.

import { Database } from 'bun:sqlite';
import { drizzle } from 'drizzle-orm/bun-sqlite';
import { migrate } from 'drizzle-orm/bun-sqlite/migrator';
import * as schema from '{{schema_index_import}}';

declare global {
  // eslint-disable-next-line no-var
  var __sqlite__: Database | undefined;
}

function initSqlite(): Database {
  const sqlite = new Database(process.env.{{db_url_env}} ?? './local.db');
  sqlite.exec('PRAGMA journal_mode = WAL;');
  sqlite.exec('PRAGMA synchronous = NORMAL;');
  sqlite.exec('PRAGMA foreign_keys = ON;');
  sqlite.exec('PRAGMA busy_timeout = 5000;');
  return sqlite;
}

const sqlite = globalThis.__sqlite__ ?? initSqlite();
if (process.env.NODE_ENV !== 'production') {
  globalThis.__sqlite__ = sqlite;
}

export const db = drizzle(sqlite, { schema });
export const rawSqlite = sqlite;

migrate(db, { migrationsFolder: '{{migrations_dir}}' });

// {{client_path}} — singleton Drizzle client for libsql (Turso or local `file:`)
// Generated by drizzle-sqlite-scaffold. Substitute {{placeholders}}.
//
// libsql enables foreign keys by default. For local `file:` URLs, WAL is also
// the default. Remote Turso manages journaling server-side.
//
// See drizzle-sqlite rules: conn-singleton-client, conn-pick-driver-deliberately.

import { createClient, type Client } from '@libsql/client';
import { drizzle } from 'drizzle-orm/libsql';
import { migrate } from 'drizzle-orm/libsql/migrator';
import * as schema from '{{schema_index_import}}';

declare global {
  // eslint-disable-next-line no-var
  var __libsql__: Client | undefined;
}

function initClient(): Client {
  return createClient({
    url: process.env.{{db_url_env}} ?? 'file:local.db',
    authToken: process.env.{{db_url_env}}_AUTH_TOKEN,
  });
}

const client = globalThis.__libsql__ ?? initClient();
if (process.env.NODE_ENV !== 'production') {
  globalThis.__libsql__ = client;
}

export const db = drizzle(client, { schema });
export const rawClient = client;

// Apply migrations on boot. For multi-instance deploys, prefer a dedicated
// pre-deploy job and remove this top-level await.
await migrate(db, { migrationsFolder: '{{migrations_dir}}' });

// drizzle.config.ts — local SQLite (better-sqlite3, bun:sqlite, libsql `file:` URL)
// Generated by drizzle-sqlite-scaffold. Substitute {{placeholders}}.

import 'dotenv/config';
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  dialect: 'sqlite',
  schema: '{{schema_dir}}/*.ts',
  out: '{{migrations_dir}}',
  dbCredentials: {
    url: process.env.{{db_url_env}} ?? 'file:local.db',
  },
  casing: 'snake_case',
  verbose: true,
  strict: true,
});

// drizzle.config.ts — libsql / Turso (remote)
// Generated by drizzle-sqlite-scaffold. Substitute {{placeholders}}.

import 'dotenv/config';
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  dialect: 'turso',
  schema: '{{schema_dir}}/*.ts',
  out: '{{migrations_dir}}',
  dbCredentials: {
    url: process.env.{{db_url_env}}!,
    authToken: process.env.{{db_url_env}}_AUTH_TOKEN!,
  },
  casing: 'snake_case',
  verbose: true,
  strict: true,
});

# Drizzle SQLite — gitignore patch
# Append these lines to your project's .gitignore.
# Generated by drizzle-sqlite-scaffold.
#
# DO commit:
#   - {{migrations_dir}}/*.sql        (the migrations themselves)
#   - {{migrations_dir}}/meta/        (snapshots; required for the next generate)
#   - drizzle.config.ts               (so generate/migrate work without flags)
#
# See drizzle-sqlite rule migrate-commit-migrations-to-git for why.

# Local SQLite database files — never commit
*.db
*.db-journal
*.db-wal
*.db-shm
local.db*

# Drizzle Studio cache
.drizzle-studio/

// {{repository_dir}}/{{name}}.ts — CRUD repository for `{{table_name}}`
// Generated by drizzle-sqlite-scaffold. Substitute {{placeholders}}.
//
// Conventions baked in (see references/conventions.md):
//   - `.returning()` on every write — no second SELECT round trip
//   - `inArray()` for batch reads — no N+1
//   - `.onConflictDoUpdate()` for upserts — atomic, no select-then-write race
//   - Prepared statement for the by-id read (compile once, reuse)
//   - Pagination is keyset, not offset

import { desc, eq, inArray, lt } from 'drizzle-orm';
import type { SQLiteColumn } from 'drizzle-orm/sqlite-core';
import { db } from '{{client_import}}';
import {
  {{name_camel}},
  type {{name_pascal}},
  type New{{name_pascal}},
} from '{{schema_import}}';

// ---------------------------------------------------------------------------
// Reads
// ---------------------------------------------------------------------------
//
// For hot-path reads called on every request, promote `findById` to a
// prepared statement following drizzle-sqlite rule perf-prepare-hot-paths.
// Prepared statements are driver-specific (sync .get() on better-sqlite3 /
// bun:sqlite; async on libsql), so they're not in the default template.

export async function findById(id: {{pk_ts_type}}): Promise<{{name_pascal}} | undefined> {
  const rows = await db
    .select()
    .from({{name_camel}})
    .where(eq({{name_camel}}.{{pk_field}}, id))
    .limit(1);
  return rows[0];
}

export async function findManyByIds(ids: {{pk_ts_type}}[]): Promise<{{name_pascal}}[]> {
  if (ids.length === 0) return [];
  // Single round trip; see drizzle-sqlite rule query-avoid-n-plus-one-with-inarray.
  return db.select().from({{name_camel}}).where(inArray({{name_camel}}.{{pk_field}}, ids));
}

export interface ListParams {
  cursor?: {{pk_ts_type}};
  pageSize?: number;
}

export async function list({ cursor, pageSize = 20 }: ListParams = {}): Promise<{
  rows: {{name_pascal}}[];
  nextCursor: {{pk_ts_type}} | null;
}> {
  // Keyset pagination — constant cost regardless of page depth.
  // See drizzle-sqlite rule perf-keyset-not-offset-for-deep-pages.
  const rows = await db
    .select()
    .from({{name_camel}})
    .where(cursor ? lt({{name_camel}}.{{pk_field}}, cursor) : undefined)
    .orderBy(desc({{name_camel}}.{{pk_field}}))
    .limit(pageSize + 1);

  const hasMore = rows.length > pageSize;
  const trimmed = hasMore ? rows.slice(0, pageSize) : rows;
  const nextCursor = hasMore ? trimmed[trimmed.length - 1].{{pk_field}} : null;
  return { rows: trimmed, nextCursor };
}

// ---------------------------------------------------------------------------
// Writes
// ---------------------------------------------------------------------------
// Every write uses `.returning()` so the caller gets the inserted/updated row
// without a second round trip. See drizzle-sqlite rule
// query-returning-instead-of-reselect.

export async function create(input: New{{name_pascal}}): Promise<{{name_pascal}}> {
  const [row] = await db.insert({{name_camel}}).values(input).returning();
  return row;
}

export async function createMany(inputs: New{{name_pascal}}[]): Promise<{{name_pascal}}[]> {
  if (inputs.length === 0) return [];
  // Multi-row VALUES — one statement, one plan, one commit.
  // See drizzle-sqlite rule perf-bulk-insert-multi-row-values.
  return db.insert({{name_camel}}).values(inputs).returning();
}

export interface UpsertParams {
  /** Drizzle column reference(s) — pass `{{name_camel}}.email` for a single
   *  unique column, or `[{{name_camel}}.a, {{name_camel}}.b]` for a composite key. */
  target: SQLiteColumn | SQLiteColumn[];
  /** Columns to update when a conflict occurs. */
  set: Partial<New{{name_pascal}}>;
}

export async function upsert(
  input: New{{name_pascal}},
  { target, set }: UpsertParams,
): Promise<{{name_pascal}}> {
  // Atomic — no select-then-write race. See drizzle-sqlite rule query-upsert-with-onconflict.
  // Callers pass the column reference directly:
  //   upsert({ email, name }, { target: {{name_camel}}.email, set: { name } });
  const [row] = await db
    .insert({{name_camel}})
    .values(input)
    .onConflictDoUpdate({ target, set })
    .returning();
  return row;
}

export async function update(
  id: {{pk_ts_type}},
  changes: Partial<New{{name_pascal}}>,
): Promise<{{name_pascal}} | undefined> {
  const [row] = await db
    .update({{name_camel}})
    .set(changes)
    .where(eq({{name_camel}}.{{pk_field}}, id))
    .returning();
  return row;
}

export async function remove(id: {{pk_ts_type}}): Promise<{{name_pascal}} | undefined> {
  // Hard delete. For soft delete (deletedAt column), call update({ deletedAt: new Date() }).
  const [row] = await db
    .delete({{name_camel}})
    .where(eq({{name_camel}}.{{pk_field}}, id))
    .returning();
  return row;
}

// ---------------------------------------------------------------------------
// Transactional helpers
// ---------------------------------------------------------------------------
// Wrap multi-statement writes in db.transaction() — see drizzle-sqlite rule
// tx-wrap-multi-statement-writes. Use behavior: 'immediate' for read-then-write
// flows so contention surfaces at BEGIN, not mid-tx.

export function withTransaction<T>(
  fn: (tx: Parameters<Parameters<typeof db.transaction>[0]>[0]) => Promise<T>,
): Promise<T> {
  return db.transaction(fn, { behavior: 'immediate' });
}

// {{schema_dir}}/index.ts — barrel re-export so drizzle({ schema }) sees every table
// Generated by drizzle-sqlite-scaffold.
//
// Append a new line for each table you add via the `table.ts.template` flow:
//   export * from './<table-name>';
//
// The barrel lets the client construct with `drizzle(driver, { schema })` and
// pick up every table + relations automatically. See drizzle-sqlite rule
// rel-declare-relations-for-rqb for why this matters for `db.query.*`.

// {{exports}}

// {{schema_dir}}/{{name}}.ts — schema for the `{{table_name}}` table
// Generated by drizzle-sqlite-scaffold. Substitute {{placeholders}}.
//
// Conventions baked in (see references/conventions.md):
//   - Explicit primary key — never relies on rowid
//   - Timestamps as integer({ mode: 'timestamp_ms' }) with $defaultFn
//   - Booleans as integer({ mode: 'boolean' })
//   - Every foreign key has explicit onDelete/onUpdate
//   - Every foreign key column is indexed
//   - relations() declared when foreign keys exist
//   - $inferSelect / $inferInsert types exported

import { relations } from 'drizzle-orm';
import {
  index,
  integer,
  sqliteTable,
  text,
  // Uncomment when you need them — leaving unused imports breaks strict TS:
  // primaryKey,    // for composite-PK join tables — primaryKey({ columns: [a, b] })
  // uniqueIndex,   // for case-insensitive unique indexes, expression unique indexes
  // blob,          // for binary columns
} from 'drizzle-orm/sqlite-core';
// {{pk_extra_imports}}                  // e.g., `import { createId } from '@paralleldrive/cuid2';`
// {{relation_imports}}                  // e.g., `import { posts } from './post';`

// ---------------------------------------------------------------------------
// Table
// ---------------------------------------------------------------------------

export const {{name_camel}} = sqliteTable(
  '{{table_name}}',
  {
    // Primary key — {{pk}}
    {{pk_definition}}

    // Domain columns — replace these with your own; the boilerplate above and
    // below stays the same.
    // Example shapes that follow the conventions:
    //
    //   name: text().notNull(),
    //   email: text().notNull().unique(),
    //   active: integer({ mode: 'boolean' }).notNull().default(true),
    //   bio: text(),
    //   settings: text({ mode: 'json' }).$type<Settings>(),
    //
    // For a foreign key, use:
    //
    //   parentId: integer()
    //     .notNull()
    //     .references(() => parents.id, { onDelete: 'cascade', onUpdate: 'cascade' }),
    {{domain_columns}}

    // {{timestamps_block}}
    // ↑ When timestamps=true, the agent expands this to:
    //     createdAt: integer({ mode: 'timestamp_ms' }).notNull().$defaultFn(() => new Date()),
    //     updatedAt: integer({ mode: 'timestamp_ms' }).notNull().$defaultFn(() => new Date()).$onUpdateFn(() => new Date()),

    // {{soft_delete_block}}
    // ↑ When soft_delete=true, the agent expands this to:
    //     deletedAt: integer({ mode: 'timestamp_ms' }),
  },
  (table) => [
    // Index every foreign key column. Add composite indexes for hot WHERE+ORDER BY
    // patterns. See drizzle-sqlite rules schema-index-foreign-keys-and-lookups
    // and perf-covering-index-for-hot-queries.
    {{indexes}}
    // {{soft_delete_index}}
    // ↑ When soft_delete=true, the agent appends:
    //     index('{{table_name}}_active_idx').on(table.deletedAt).where(sql`deleted_at IS NULL`),
  ],
);

// ---------------------------------------------------------------------------
// Relations
// ---------------------------------------------------------------------------
// Declared in this file (not the related table's file) so the relations object
// stays next to its column definitions. See drizzle-sqlite rule
// rel-declare-relations-for-rqb.

export const {{name_camel}}Relations = relations({{name_camel}}, ({ one, many }) => ({
  {{relations_body}}
}));

// ---------------------------------------------------------------------------
// Inferred types
// ---------------------------------------------------------------------------
// Single source of truth — never hand-write parallel types. See
// drizzle-sqlite rule types-infer-select-insert.

export type {{name_pascal}} = typeof {{name_camel}}.$inferSelect;
export type New{{name_pascal}} = typeof {{name_camel}}.$inferInsert;

// {{validators_dir}}/{{name}}.ts — drizzle-zod validators for `{{table_name}}`
// Generated by drizzle-sqlite-scaffold. Substitute {{placeholders}}.
//
// Conventions baked in (see references/conventions.md):
//   - Validators derived from the Drizzle schema — no hand-written parallel types
//   - createInsertSchema / createUpdateSchema / createSelectSchema covered
//   - Tighten constraints (email format, length, refinement) inside the
//     refinement callback so the validator can be both stricter and aligned.
//
// See drizzle-sqlite rule types-drizzle-zod-for-runtime-validation.

import {
  createInsertSchema,
  createSelectSchema,
  createUpdateSchema,
} from 'drizzle-zod';
import { z } from 'zod';
import { {{name_camel}} } from '{{schema_import}}';

// INSERT — used for POST endpoints, server actions, queue payloads.
export const new{{name_pascal}}Schema = createInsertSchema({{name_camel}}, {
  // Replace these example refinements with the real constraints for this table:
  //
  //   email: (s) => s.email().toLowerCase(),
  //   name: (s) => s.min(1).max(120),
  //   bio: (s) => s.max(2000).nullish(),
  //
  {{insert_refinements}}
});
export type New{{name_pascal}}Input = z.infer<typeof new{{name_pascal}}Schema>;

// PARTIAL UPDATE — used for PATCH endpoints.
export const update{{name_pascal}}Schema = createUpdateSchema({{name_camel}}, {
  {{update_refinements}}
});
export type Update{{name_pascal}}Input = z.infer<typeof update{{name_pascal}}Schema>;

// SELECT — useful when consuming an API response that should match the row shape.
export const {{name_camel}}Schema = createSelectSchema({{name_camel}});
export type {{name_pascal}}Output = z.infer<typeof {{name_camel}}Schema>;

{
  "driver": "",
  "db_url_env": "DATABASE_URL",
  "schema_dir": "./src/db/schema",
  "repository_dir": "./src/db/repository",
  "validators_dir": "./src/db/validators",
  "client_path": "./src/db/client.ts",
  "migrations_dir": "./drizzle",
  "_setup_instructions": {
    "driver": "Which SQLite driver this project uses. One of: 'better-sqlite3' (sync, Node), 'libsql' (async, Turso or local file), 'bun-sqlite' (sync, Bun runtime), 'd1' (async, Cloudflare Workers).",
    "db_url_env": "Environment variable holding the database URL. Defaults to DATABASE_URL.",
    "schema_dir": "Where per-table schema files are emitted. Each table becomes {schema_dir}/{name}.ts.",
    "repository_dir": "Where per-table repository (CRUD) modules are emitted.",
    "validators_dir": "Where drizzle-zod validators are emitted (only used when with_zod=true).",
    "client_path": "Path to the singleton Drizzle client module.",
    "migrations_dir": "drizzle-kit output folder. Both SQL files and meta/ snapshots must be committed."
  }
}

{
  "version": "1.0.3",
  "organization": "dot-skills",
  "technology": "Drizzle ORM + SQLite",
  "discipline": "extraction",
  "type": "scaffolding",
  "date": "May 2026",
  "abstract": "Parameterized templates for scaffolding Drizzle ORM + SQLite boilerplate that bakes in the 45 rules from the `drizzle-sqlite` skill. Produces a drizzle.config.ts + singleton client with pragmas + per-table schema/repository/validator modules — every output has explicit primary keys, indexed foreign keys, declared relations(), boolean/timestamp_ms column modes, and CRUD helpers that use `.returning()`, `inArray()`, and `.onConflictDoUpdate()` by default.",
  "references": [
    "https://orm.drizzle.team/docs/get-started-sqlite",
    "https://orm.drizzle.team/docs/sql-schema-declaration",
    "https://orm.drizzle.team/docs/relations",
    "https://orm.drizzle.team/docs/drizzle-config-file",
    "https://orm.drizzle.team/docs/zod"
  ]
}