Skip to main content

Database and Drizzle

W7S uses repo-scoped SQLite databases. The recommended development style is:

  • SQL only for migrations
  • Drizzle schema for the app layer
  • Drizzle query builder in handlers
  • createW7SDbClient as the common runtime and local bridge

Database scope​

Every database is isolated by:

  • org
  • repo
  • database name

The default application database usually lives at:

db/app/migrations

Migrations​

Create migrations as raw SQL files:

db/app/migrations/0001_init.sql
db/app/migrations/0002_add_visits.sql

Example:

create table if not exists notes (
  id integer primary key autoincrement,
  title text not null,
  body text,
  created_at_ms integer not null
);

Deploy applies migrations before finalizing the runtime snapshot.

Drizzle schema​

Create a schema module:

import { integer, sqliteTable, text } from "drizzle-orm/sqlite-core";

export const notes = sqliteTable("notes", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  title: text("title").notNull(),
  body: text("body"),
  createdAtMs: integer("created_at_ms").notNull(),
});

Shared DB client​

Use the SDK helper instead of a custom transport layer:

import { createW7SDbClient } from "@w7s-io/sdk";
import * as schema from "./schema";

export const db = createW7SDbClient({
  repo: "owner/repo",
  databaseName: "app",
  schema,
});

export { schema };

Why this is the preferred path:

  • in runtime, it automatically uses globalThis.db.query/execute
  • in local Node, it uses SDK calls to your org runtime
  • you keep one DB API for both environments

Typed handler usage​

import { desc } from "drizzle-orm";
import { db, schema } from "./db/client";

const rows = await db
  .select()
  .from(schema.notes)
  .orderBy(desc(schema.notes.createdAtMs))
  .limit(20);

await db.insert(schema.notes).values({
  title: "Hello",
  body: "World",
  createdAtMs: Date.now(),
});

Drizzle Kit​

Drizzle Kit is optional, but useful for generating migrations.

Example drizzle.config.ts:

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "sqlite",
  schema: "./backend/src/db/schema.ts",
  out: "./db/app/migrations",
});

That keeps:

  • schema in app code
  • generated migrations in the W7S migration path

Use this philosophy:

  • SQL is allowed in migrations
  • app and handler code should use Drizzle, not handwritten SQL strings

That gives you a much cleaner surface for agents and future refactors.