> ## 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.

# Introspection

> Generate a Vertz schema from an existing database with vertz db pull

`vertz db pull` connects to an existing database, reads its tables, columns, indexes, and foreign keys, and generates a TypeScript schema file using the `d` builder. This is the fastest way to bring an existing database into Vertz.

## Getting started

<Steps>
  <Step title="Configure your database connection">
    Add a `db` export to your `vertz.config.ts` with the connection URL and dialect:

    ```ts vertz.config.ts theme={null}
    export const db = {
      dialect: 'postgres',
      url: 'postgres://user:pass@localhost:5432/mydb',
      schema: './src/schema.ts',
    };
    ```
  </Step>

  <Step title="Pull the schema">
    Run `vertz db pull` to introspect the database and generate a schema file:

    ```bash theme={null}
    vertz db pull --output src/schema.ts
    ```
  </Step>

  <Step title="Review and customize">
    The generated schema is a starting point. Review it, add [annotations](/guides/db/schema#column-modifiers) like `.readOnly()`, `.autoUpdate()`, and `.hidden()`, then use it like any hand-written schema.
  </Step>
</Steps>

## CLI options

```bash theme={null}
vertz db pull [options]
```

| Option                | Description                                                                        |
| --------------------- | ---------------------------------------------------------------------------------- |
| `-o, --output <path>` | Output file or directory. If the path ends with `/`, generates one file per table. |
| `--dry-run`           | Preview the generated code without writing files.                                  |
| `-f, --force`         | Overwrite existing files.                                                          |
| `--url <url>`         | Database URL (overrides `vertz.config.ts`).                                        |
| `--dialect <dialect>` | Database dialect: `postgres` or `sqlite`.                                          |

## Output modes

### Single file (default)

Generates one `schema.ts` file with all tables:

```bash theme={null}
vertz db pull --output src/schema.ts
```

```ts src/schema.ts theme={null}
import { d } from '@vertz/db';

// ---------- users ----------

export const usersTable = d.table('users', {
  id: d.uuid().primary(),
  email: d.text().unique(),
  name: d.text(),
  createdAt: d.timestamp().default('now'),
});

// ---------- posts ----------

export const postsTable = d.table(
  'posts',
  {
    id: d.uuid().primary(),
    title: d.text(),
    body: d.text().nullable(),
    authorId: d.uuid(),
  },
  {
    indexes: [d.index('authorId', { name: 'idx_posts_author_id' })],
  },
);

export const postsModel = d.model(postsTable, {
  author: d.ref.one(() => usersTable, 'authorId'),
});
```

### Per-table mode

When the output path ends with `/`, each table gets its own file plus a barrel `index.ts`:

```bash theme={null}
vertz db pull --output src/schema/
```

```
src/schema/
  users.ts
  posts.ts
  index.ts
```

Each file imports its FK dependencies from sibling files:

```ts src/schema/posts.ts theme={null}
import { d } from '@vertz/db';
import { usersTable } from './users';

export const postsTable = d.table('posts', {
  id: d.uuid().primary(),
  title: d.text(),
  body: d.text().nullable(),
  authorId: d.uuid(),
});

export const postsModel = d.model(postsTable, {
  author: d.ref.one(() => usersTable, 'authorId'),
});
```

## Zero-config mode

You can skip `vertz.config.ts` entirely by providing `--url` and `--dialect` directly:

```bash theme={null}
vertz db pull --url postgres://localhost:5432/mydb --dialect postgres --dry-run
```

This is useful for one-off introspection of databases you don't have a config for.

## Type mapping

The code generator maps database types to `d` builder calls:

### Postgres

| SQL type                | Generated code         | Notes                                                   |
| ----------------------- | ---------------------- | ------------------------------------------------------- |
| `uuid`                  | `d.uuid()`             |                                                         |
| `text`                  | `d.text()`             |                                                         |
| `character varying(n)`  | `d.varchar(n)`         | Falls back to `d.text()` without length                 |
| `boolean`               | `d.boolean()`          |                                                         |
| `integer`               | `d.integer()`          |                                                         |
| `integer` + `nextval()` | `d.serial()`           | Auto-increment detection                                |
| `bigint`                | `d.bigint()`           |                                                         |
| `numeric(p,s)`          | `d.decimal(p, s)`      |                                                         |
| `real`                  | `d.real()`             |                                                         |
| `double precision`      | `d.doublePrecision()`  |                                                         |
| `timestamptz`           | `d.timestamp()`        |                                                         |
| `timestamp`             | `d.timestamp()`        | Annotated with `// Source: timestamp without time zone` |
| `date`                  | `d.date()`             |                                                         |
| `time`                  | `d.time()`             |                                                         |
| `jsonb`                 | `d.jsonb()`            |                                                         |
| `json`                  | `d.jsonb()`            | Annotated with `// Source: json`                        |
| `text[]`                | `d.textArray()`        |                                                         |
| `integer[]`             | `d.integerArray()`     |                                                         |
| `USER-DEFINED`          | `d.enum(name, values)` | Reads enum values from `pg_enum`                        |
| `smallint`              | `d.integer()`          | Annotated with `// Source: smallint`                    |
| `vector(n)`             | `d.vector(n)`          | pgvector — dimensions preserved                         |
| `vector`                | `d.text()`             | Falls back without dimensions                           |
| `citext`                | `d.text()`             | Case-insensitive text extension                         |
| `bytea`                 | `d.text()`             | Annotated with `// TODO: binary type`                   |

### SQLite

| SQL type         | Generated code | Notes                                 |
| ---------------- | -------------- | ------------------------------------- |
| `integer`        | `d.integer()`  |                                       |
| `text`           | `d.text()`     |                                       |
| `real` / `float` | `d.real()`     |                                       |
| `blob`           | `d.text()`     | Annotated with `// TODO: binary type` |

Types that don't map directly get `d.text()` with a `// TODO: unmapped type` comment so you can fix them manually.

## What gets generated

### Columns

* **Names**: Snake-case column names are converted to camelCase (`created_at` becomes `createdAt`)
* **Constraints**: `.primary()`, `.unique()`, `.nullable()` are applied based on the database schema
* **Defaults**: Simple defaults (`now()`, `true`, `false`, numeric values, string literals) are preserved. Complex expressions (function calls, casts) are skipped.

### Indexes

Indexes are generated with their original name, uniqueness, type, and WHERE clause:

```ts theme={null}
d.index('createdAt', { name: 'idx_users_created_at' }),
d.index(['tenantId', 'email'], { name: 'idx_users_tenant_email', unique: true }),
d.index('status', { name: 'idx_active', where: "status = 'active'" }),
```

### Relations

Foreign keys are detected and generate `d.model()` with `d.ref.one()` relations:

```ts theme={null}
export const postsModel = d.model(postsTable, {
  author: d.ref.one(() => usersTable, 'authorId'),
});
```

* The relation name is derived from the FK column by stripping `Id` or `Fk` suffixes (`authorId` becomes `author`)
* Self-referential FKs work (`managerId` on `employees` references `employees`)
* Multiple FKs to the same table are disambiguated (`sender`, `usersByReceiverId`)

<Note>
  Only `d.ref.one()` (many-to-one) relations are generated. Inverse `d.ref.many()` relations are not
  inferred — add them manually if needed.
</Note>

### Table ordering

Tables are topologically sorted so FK targets are defined before the tables that reference them. Circular references are detected, placed at the end, and annotated:

```ts theme={null}
// Note: circular FK reference with table_b
```

### Composite primary keys

Tables with multiple primary key columns use the `primaryKey` option instead of `.primary()` on individual columns:

```ts theme={null}
export const postTagsTable = d.table(
  'post_tags',
  {
    postId: d.uuid(),
    tagId: d.uuid(),
    createdAt: d.timestamp().default('now'),
  },
  {
    primaryKey: ['postId', 'tagId'],
  },
);
```

## What is NOT generated

The code generator produces a **database-level schema** — the structural representation of what exists in the database. App-level annotations that carry semantic meaning are left for you to add:

| Annotation           | Why it's not generated                                               |
| -------------------- | -------------------------------------------------------------------- |
| `.readOnly()`        | Database can't tell which columns are read-only in your app          |
| `.autoUpdate()`      | Database triggers exist but don't map cleanly to Vertz's auto-update |
| `.hidden()`          | Field visibility is an app concern                                   |
| `.tenant()`          | Tenant scoping is a framework convention, not a DB concept           |
| `.email()`, `.url()` | Format validation is application logic                               |
| `d.ref.many()`       | Inverse relations require knowing which side is "primary"            |

After pulling, review the generated schema and add these annotations where appropriate.

## Preview before writing

Use `--dry-run` to see what would be generated without writing any files:

```bash theme={null}
vertz db pull --dry-run
```

The generated code is printed to stdout, so you can pipe it:

```bash theme={null}
vertz db pull --dry-run > schema-preview.ts
```

## Workflow: adopting Vertz on an existing database

<Steps>
  <Step title="Pull the schema">`bash vertz db pull --output src/schema.ts `</Step>

  <Step title="Review and annotate">
    Add `.readOnly()`, `.autoUpdate()`, `.hidden()`, `.tenant()`, and other annotations. Fix any `//
            TODO` comments for unmapped types.
  </Step>

  <Step title="Baseline the migration history">
    `bash vertz db baseline ` This marks the current database state as the starting point — no
    SQL is applied.
  </Step>

  <Step title="Start developing">
    From here, use `vertz dev` for automatic migrations or `vertz db migrate` for explicit migration
    files. See the [migrations guide](/guides/db/migrations) for details.
  </Step>
</Steps>

<CardGroup cols={2}>
  <Card title="Schema" icon="table" href="/guides/db/schema">
    Full reference for the `d` builder — all column types, modifiers, and annotations.
  </Card>

  <Card title="Migrations" icon="code-branch" href="/guides/db/migrations">
    How migrations work in development and production.
  </Card>
</CardGroup>