zopu-code/.agents/skills/per-tenant-database/reference/actors/sqlite-drizzle.md

# SQLite + Drizzle

> Source: `src/content/docs/actors/sqlite-drizzle.mdx`
> Canonical URL: https://rivet.dev/docs/actors/sqlite-drizzle
> Description: Use Drizzle ORM with embedded SQLite in Rivet Actors.

---
Use Drizzle when you want typed schema, typed queries, and generated migrations on top of actor-local SQLite.

For a high-level overview of where to store actor data, see [State & Storage](/docs/actors/state).

## What is Drizzle good for?

- **Typed schema**: define tables in TypeScript and get typed query results.
- **Typed query builder**: write SQL-like queries with autocompletion.
- **Migration workflow**: generate SQL migration files from schema changes.
- **Raw SQL escape hatch**: use `c.db.execute(...)` for direct SQLite when needed.

## Project structure

Use one folder per actor database:

```txt
src/
  actors/
    todo-list/
      index.ts
      schema.ts
      drizzle.config.ts
      drizzle/
        0000_init.sql
        migrations.js
        migrations.d.ts
        meta/
          _journal.json
```

- `index.ts` is the actor implementation.
- `drizzle/` holds the SQL migrations (`*.sql`) and `meta/_journal.json` generated by `drizzle-kit`.
- `migrations.js` is a small RivetKit glue file you maintain by hand. It imports the journal and each `*.sql` file and exports a `{ journal, migrations }` object keyed by migration (for example `m0000`). Add a new entry here whenever `db:generate` produces a new migration.
- Commit the generated migration files and `migrations.js` to source control.

## Basic setup

```json package.json
{
  "scripts": {
    "db:generate": "find src/actors -name drizzle.config.ts -exec drizzle-kit generate --config {} \\;"
  },
  "dependencies": {
    "rivetkit": "*",
    "drizzle-orm": "^0.44.2"
  },
  "devDependencies": {
    "drizzle-kit": "^0.31.2"
  }
}
```

```ts vite.config.ts @nocheck
import { defineConfig, type Plugin } from "vite";
import { readFileSync } from "node:fs";

function sqlRawPlugin(): Plugin {
  return {
    name: "sql-raw",
    transform(_code, id) {
      if (id.endsWith(".sql")) {
        const content = readFileSync(id, "utf-8");
        return { code: `export default ${JSON.stringify(content)};` };
      }
    },
  };
}

export default defineConfig({
  plugins: [sqlRawPlugin()],
});
```

```ts drizzle.config.ts @nocheck
import { defineConfig } from "rivetkit/db/drizzle";

export default defineConfig({
  schema: "./src/actors/todo-list/schema.ts",
  out: "./src/actors/todo-list/drizzle",
});
```

```sql 0000_init.sql
CREATE TABLE `todos` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `title` text NOT NULL,
  `created_at` integer NOT NULL
);
```

```json _journal.json
{
  "version": "7",
  "dialect": "sqlite",
  "entries": [
    {
      "idx": 0,
      "version": "7",
      "when": 1735689600000,
      "tag": "0000_init",
      "breakpoints": true
    }
  ]
}
```

```ts migrations.js @nocheck
import journal from "./meta/_journal.json";
import m0000 from "./0000_init.sql";

export default {
  journal,
  migrations: {
    m0000,
  },
};
```

```ts index.ts @nocheck
import { actor } from "rivetkit";
import { db } from "rivetkit/db/drizzle";
import migrations from "./drizzle/migrations.js";
import { schema, todos } from "./schema.ts";

export const todoList = actor({
  db: db({ schema, migrations }),
  actions: {
    addTodo: async (c, title: string) => {
      const rows = await c.db
        .insert(todos)
        .values({ title, createdAt: Date.now() })
        .returning();
      return rows[0];
    },
    getTodos: async (c) => {
      return await c.db.select().from(todos).orderBy(todos.id);
    },
    getTodoCount: async (c) => {
      const rows = (await c.db.execute(
        "SELECT COUNT(*) AS count FROM todos",
      )) as { count: number }[];
      return rows[0]?.count ?? 0;
    },
  },
});
```

```ts index.ts @nocheck
import { setup } from "rivetkit";
import { todoList } from "./todo-list/index.ts";

export const registry = setup({ use: { todoList } });
registry.start();
```

```ts client.ts @nocheck
import { createClient } from "rivetkit/client";
import type { registry } from "./index";

const client = createClient<typeof registry>("http://localhost:6420");
const todoList = client.todoList.getOrCreate(["main"]);

await todoList.addTodo("Write Drizzle docs");

const todos = await todoList.getTodos();
const count = await todoList.getTodoCount();

console.log(todos, count);
```

## Queries

### Query builder

Use Drizzle's typed query APIs for most reads and writes.

```ts @nocheck
import { eq } from "drizzle-orm";

await c.db.insert(todos).values({ title, createdAt: Date.now() });

const rows = await c.db
  .select()
  .from(todos)
  .where(eq(todos.title, title));
```

### Raw SQL

`rivetkit/db/drizzle` also exposes raw SQLite access through `c.db.execute(...)`.

```ts @nocheck
await c.db.execute(
  "CREATE INDEX IF NOT EXISTS idx_todos_created_at ON todos(created_at)",
);
```

## Queues

Use queues for ordered mutations and keep actions read-only. Import `queue` alongside `actor` from `rivetkit`.

```ts @nocheck
import { actor, queue } from "rivetkit";

// ...

queues: {
  addTodo: queue<{ title: string }>(),
},
run: async (c) => {
  for await (const message of c.queue.iter()) {
    if (message.name === "addTodo") {
      await c.db.insert(todos).values({
        title: message.body.title,
        createdAt: Date.now(),
      });
    }
  }
},
actions: {
  getTodos: async (c) => await c.db.select().from(todos),
},
```

## Recommendations

- Prefer Drizzle query APIs for app code and use raw SQL for advanced SQLite features.
- Keep one `drizzle.config.ts` per actor folder.
- Re-run `db:generate` after schema changes and commit generated migration files.
- Use queues for writes and actions for reads.
- Keep related writes in one action or queue message to reduce interleaved query risk.

## Read more

- [Drizzle SQLite quickstart](https://orm.drizzle.team/docs/get-started-sqlite)
- [Drizzle `drizzle-kit generate`](https://orm.drizzle.team/docs/drizzle-kit-generate)
- [Drizzle + Cloudflare D1](https://orm.drizzle.team/docs/deploy-cloudflare-d1)
- [Drizzle + Cloudflare Durable Objects](https://orm.drizzle.team/docs/deploy-cloudflare-do)
- [Cloudflare Durable Objects SQLite storage](https://developers.cloudflare.com/durable-objects/api/sqlite-storage-api/)

_Source doc path: /docs/actors/sqlite-drizzle_