> ## Documentation Index
> Fetch the complete documentation index at: https://docs.vertz.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Overview

> @vertz/db — schema-first database layer with typed queries and automatic migrations

`@vertz/db` is the database layer of the Vertz stack. You define schemas with the `d` builder, query with typed operations, and run migrations with a single command. Types are inferred from your schema — no code generation step, no separate type files.

## How it works

<Steps>
  <Step title="Define your schema">
    Use the `d` builder to declare tables, columns, and relations. Column types, constraints, and
    annotations are all defined in TypeScript.
  </Step>

  <Step title="Query with types">
    The database client provides typed CRUD operations. Filters, selects, and includes are all
    type-checked at compile time.
  </Step>

  <Step title="Migrate automatically">
    The CLI diffs your schema against the database and generates migrations. No manual SQL, no
    migration files to maintain during development.
  </Step>
</Steps>

## Quick example

```ts theme={null}
import { createDb, d } from '@vertz/db';

// Define schema
const usersTable = d.table('users', {
  id: d.uuid().primary({ generate: 'cuid' }),
  email: d.email().unique(),
  name: d.text(),
  role: d.enum('user_role', ['admin', 'user']).default('user'),
  createdAt: d.timestamp().default('now').readOnly(),
});

const usersModel = d.model(usersTable);

// Create client — PostgreSQL
const db = createDb({
  url: process.env.DATABASE_URL!,
  models: { users: usersModel },
});

// Typed queries
const user = await db.users.create({
  data: { email: 'alice@example.com', name: 'Alice' },
});

const admins = await db.users.list({
  where: { role: 'admin' },
  orderBy: { createdAt: 'desc' },
});
```

Types flow from the schema definition. `db.users.create()` only accepts fields from the table definition, and the return type includes all non-hidden columns.

## Database support

All databases use the same `createDb()` API — the only difference is the options you pass:

<CodeGroup>
  ```ts PostgreSQL theme={null}
  import { createDb } from '@vertz/db';

  const db = createDb({
  url: process.env.DATABASE_URL!,
  models: { users: usersModel, tasks: tasksModel },
  });

  ```

  ```ts SQLite (local file) theme={null}
  import { createDb } from '@vertz/db';

  const db = createDb({
    dialect: 'sqlite',
    path: '.vertz/data/app.db', // or ':memory:' for in-memory
    models: { users: usersModel, tasks: tasksModel },
    migrations: { autoApply: true },
  });
  ```

  ```ts Cloudflare D1 theme={null}
  import { createDb, type D1Database } from '@vertz/db';

  export function createD1Db(d1: D1Database) {
    return createDb({
      dialect: 'sqlite',
      d1,
      models: { users: usersModel, tasks: tasksModel },
    });
  }
  ```
</CodeGroup>

| Option       | PostgreSQL               | SQLite (local)                    | Cloudflare D1                                                              |
| ------------ | ------------------------ | --------------------------------- | -------------------------------------------------------------------------- |
| `dialect`    | `'postgres'` (default)   | `'sqlite'`                        | `'sqlite'`                                                                 |
| Connection   | `url: string`            | `path: string`                    | `d1: D1Database`                                                           |
| Migrations   | CLI (`vertz db migrate`) | `migrations: { autoApply: true }` | Not available (`autoApply` is disallowed for D1 — use wrangler migrations) |
| Transactions | `db.transaction()`       | `db.transaction()`                | Not supported (use `D1Database.batch()`)                                   |

<Note>
  SQLite with `autoApply: true` automatically creates tables on first query — no migration commands
  needed during development. Boolean values are stored as `0`/`1` and timestamps as ISO strings, but
  the query layer converts them to native JS types automatically.
</Note>

<Warning>
  **SQLite requires the `vtz` runtime.** The `vtz` runtime includes a built-in SQLite driver
  powered by Rust — no extra dependencies needed. If you run scripts with plain `node` instead of
  `vtz`, SQLite will fail to initialize. Always use `vtz dev`, `vtz test`, or `vtz run <script>` to
  run code that uses SQLite. For Node.js-only environments (e.g. production without `vtz`), use
  PostgreSQL instead.
</Warning>

## What's included

| Feature                  | Description                                                |
| ------------------------ | ---------------------------------------------------------- |
| **Schema builder**       | Declarative `d` API for tables, columns, relations         |
| **Typed queries**        | CRUD operations with compile-time type checking            |
| **Automatic migrations** | Schema diffing with CLI commands                           |
| **Multi-dialect**        | PostgreSQL and SQLite with a unified API                   |
| **Relations**            | One-to-many, many-to-one, many-to-many through join tables |
| **Transactions**         | Atomic multi-operation writes with `db.transaction()`      |
| **Error handling**       | Result-based errors — never throws                         |
| **Multi-tenancy**        | Built-in tenant scoping at the model level                 |

## Core principles

### Schema is the source of truth

Column types, constraints, defaults, and annotations are all declared in the schema. Input/output types, validation schemas, and migration diffs are derived from it automatically. No duplication.

### Result-based errors

Query operations return `Result<T, Error>` — they never throw. You handle errors explicitly with pattern matching, not try/catch:

```ts theme={null}
const result = await db.users.create({ data: { email: 'alice@example.com', name: 'Alice' } });

if (result.ok) {
  console.log(result.data.name);
} else {
  console.error(result.error.code); // 'UNIQUE_VIOLATION', 'NOT_NULL_VIOLATION', etc.
}
```

### Compile-time safety

If your schema says `role` is an enum of `'admin' | 'user'`, the query builder rejects any other value at compile time. If a column is `.readOnly()`, it won't appear in create/update input types. The TypeScript compiler catches data issues before they reach the database.

## Guides

<CardGroup cols={2}>
  <Card title="Schema" icon="table" href="/guides/db/schema">
    Tables, columns, types, relations, and annotations.
  </Card>

  <Card title="Queries" icon="magnifying-glass" href="/guides/db/queries">
    CRUD operations, filters, pagination, and error handling.
  </Card>

  <Card title="Migrations" icon="arrows-rotate" href="/guides/db/migrations">
    Auto-migration CLI and deployment workflow.
  </Card>

  <Card title="Seeding" icon="seedling" href="/guides/db/seeding">
    Populate your database with dev, test, and production data.
  </Card>
</CardGroup>