# Database & Migrations
URL: /docs/building-apps/capabilities/database
Markdown: /docs/building-apps/capabilities/database.md
Description: Save app data in SQL tables with Drizzle ORM, kept apart by table prefix.

A Rome app can save data in SQL tables managed with [Drizzle ORM](https://orm.drizzle.team/). The database layer is optional — delete it all if your app doesn't need to store anything — but when it's there it follows a set storage model, a migration flow run by `drizzle-kit`, and a repository pattern that keeps SQL out of your actions and API handlers.

## Storage model [#storage-model]

### One shared SQLite file [#one-shared-sqlite-file]

App data is **not** written to its own SQLite database. Every app table lives in the same system SQLite file:

```
~/.rome/<profile>/rome.db
```

Apps never open this file themselves. The runtime hands each app its own Drizzle connection through `appContext.db`, typed as `AppDbContext`:

```ts
export type DrizzleDb = BetterSQLite3Database<Record<string, never>>;

export interface AppDbContext {
  connection: DrizzleDb;
  tablePrefix: string;
  tableName(name: string): string;
}
```

`connection` is the Drizzle handle you run queries against. `tablePrefix` is your app's name space. `tableName(name)` joins the two: it turns a logical name into its real, prefixed table name.

### Keeping tables apart with `tablePrefix` [#keeping-tables-apart-with-tableprefix]

Because all apps share one file, table names must not clash. Each app sets a `tablePrefix` (usually derived from its `appId`) in `app.yaml`:

```yaml
db:
  migrations: db/migrations
  tablePrefix: myapp           # typically equals the appId
```

The `db.migrations` path is resolved under the app's `appRoot` (typically `dist`), so the build copies `src/db/migrations/` into the packaged output; the installer rejects the app if `<appRoot>/db/migrations/meta/_journal.json` is missing.

Real table names are built as `<tablePrefix>__<logical_name>` — the prefix and the logical name joined by **two underscores**. So a logical `notes` table for the `myapp` app is really named `myapp__notes`.

| Concept                 | Example                          |
| ----------------------- | -------------------------------- |
| `tablePrefix`           | `myapp`                          |
| Logical name            | `notes`                          |
| Physical table          | `myapp__notes`                   |
| Migration history table | `__drizzle_migrations_app_myapp` |

The migration history table (`__drizzle_migrations_app_<tablePrefix>`) is named the same way, so your migration records stay apart from other apps' and from the system's.

### DB tables vs. the app's data folder [#db-tables-vs-the-apps-data-folder]

The table prefix only covers SQL tables. File data that isn't in tables — caches, uploads, generated files — belongs in the app's data folder, a separate path:

```
~/.rome/<profile>/apps/data/<appId>/
```

This folder has nothing to do with the table prefix. When the app is removed with `system:app_management { op: "uninstall", purge: true }`, the daemon drops the `<tablePrefix>__*` tables and the migration table **and** deletes `apps/data/<appId>/`.

## Schema and migrations [#schema-and-migrations]

### Define the schema with Drizzle [#define-the-schema-with-drizzle]

Tables are declared in `src/db/schema.ts` using Drizzle's `sqlite-core` builders. The example below is the run-history table from the `morning-brief` example app. It wraps the definition in a factory so the `tablePrefix` is added when the schema is built:

```ts
// src/db/schema.ts
import { integer, sqliteTable, text } from "drizzle-orm/sqlite-core";

export function createAppDbSchema(tablePrefix: string = "morning_brief") {
  const runs = sqliteTable(`${tablePrefix}__runs`, {
    id: text("id").primaryKey(),
    status: text("status", { enum: ["running", "success", "error"] }).notNull(),
    dryRun: integer("dry_run", { mode: "boolean" }).notNull(),
    input: text("input", { mode: "json" }),
    result: text("result", { mode: "json" }),
    error: text("error"),
    startedAt: integer("started_at", { mode: "timestamp" }).notNull(),
    finishedAt: integer("finished_at", { mode: "timestamp" }),
    durationMs: integer("duration_ms"),
  });

  return { runs };
}

const defaultSchema = createAppDbSchema();
export const runs = defaultSchema.runs;
```

Notes on the column patterns above:

* `text(..., { mode: "json" })` stores any JSON value (the column is read and written as a parsed object).
* `integer(..., { mode: "boolean" })` and `integer(..., { mode: "timestamp" })` store booleans and `Date`s in integer columns.
* `text(..., { enum: [...] })` limits a column to a fixed set of string values at the type level.

The daemon never rewrites your migration SQL — migrations run verbatim, so the schema itself must produce the prefixed name. The factory pattern above, writing `${tablePrefix}__<name>` right into the table name string, is the standard way: the generated SQL then matches the real name directly. A schema with bare logical names would create unprefixed tables the daemon doesn't own — purge only drops `<tablePrefix>__*` tables, so they'd be left behind. For raw SQL at runtime, `appContext.db.tableName(name)` joins a logical name into its prefixed form.

### `drizzle.config.ts` [#drizzleconfigts]

The generator reads a `drizzle.config.ts` at the app root. Point it at your schema, the folder migrations are written to, the app's own migration table, and a `tablesFilter` set to your prefix so `drizzle-kit` ignores every other app's tables:

```ts
// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./src/db/schema.ts",
  out: "./src/db/migrations",
  dialect: "sqlite",
  migrations: { table: "__drizzle_migrations_app_myapp" },
  tablesFilter: ["myapp__*"],
});
```

### Generate migrations with `pnpm db:generate` [#generate-migrations-with-pnpm-dbgenerate]

The app template's `package.json` ships a `db:generate` script:

```jsonc
{
  "scripts": {
    "db:generate": "drizzle-kit generate"
  }
}
```

Run it from the app folder whenever you change `schema.ts`:

```sh
pnpm db:generate
```

This writes a new migration to `src/db/migrations/` — a `.sql` file plus an updated `meta/` snapshot. Two rules:

* **Rerun `pnpm db:generate` after every schema change.** A schema edit without a fresh migration will not reach the database.
* **Never hand-edit existing migration SQL.** Treat generated migrations as history you only add to; just add new files.

### Auto-migrate on install [#auto-migrate-on-install]

There is no manual migrate step. When the app is installed (or upgraded) via `system:app_management`, the daemon runs any pending migrations for the app on its own, tracked in the app's own `__drizzle_migrations_app_<tablePrefix>` table.

If the app doesn't need to store anything, remove the whole layer together: delete `src/db/`, `drizzle.config.ts`, the `db:` block in `app.yaml`, the `drizzle-*` dependencies, and the `db:generate` script from `package.json`.

## Repository pattern [#repository-pattern]

Keep all data access in one place, behind repositories in `src/db/repositories/`. Pull the Drizzle connection from `appContext.db` and wrap each query in a named method. **Don't write raw SQL inside actions or API handlers** — they call repository methods only.

```ts
// src/db/repositories/notes.ts
import type { DrizzleDb } from "@rome-os/app-runtime";
import { eq } from "drizzle-orm";
import { notes } from "../schema.js";

export function createNotesRepository(db: DrizzleDb) {
  return {
    list: () => db.select().from(notes).all(),
    byId: (id: string) => db.select().from(notes).where(eq(notes.id, id)).get(),
    insert: (row: typeof notes.$inferInsert) => db.insert(notes).values(row).run(),
  };
}
```

Here `db` is the Drizzle connection (`appContext.db.connection`). Build the repository once with that connection, then call its methods from your action `execute` bodies and API routes:

```ts
async execute(args): Promise<ActionResult> {
  const notesRepo = createNotesRepository(appContext.db.connection);
  const all = notesRepo.list();
  return { status: "ok", data: all };
}
```

### Why repositories [#why-repositories]

* **No SQL at the call sites.** Actions and handlers say what they want (`notesRepo.byId(id)`), not how the query works. Schema changes touch one file.
* **Types come from the schema.** `typeof notes.$inferInsert` and `$inferSelect` keep row types in step with the table definition.
* **The same everywhere.** API handlers get the same context shape (`ctx.db`, `ctx.repositories`) as actions, so the same repositories serve both.

The platform also offers a small set of built-in repositories on `appContext.repositories` (for example, `settings` for key/value app settings). Use those for what they cover, and add your own under `src/db/repositories/` for your app's tables.