# Database

> Give a project its own Postgres database with a db/schema.ts, query it with Drizzle through ctx.db(), and change the schema safely on deploy.

A Helix project can have its own managed Postgres database. You describe the tables in `db/schema.ts` with Drizzle ORM, `helix deploy` creates the database and applies your schema changes, and `ctx.db()` gives your functions a typed Drizzle query builder over it. This guide covers setup, schema changes, local development, the SQL you can run, and what to check when something fails.

## When to use it

Use the database for relational data that outlives a request: records, users, tasks, and the relations between them. Use [`ctx.kv()`](/documentation/guides/key-value-store/) for simple key-value state. Both are scoped to one project, and no other project can read either.

## Give a project a database

A project gets a database when its deployment source contains `db/schema.ts`. A project without that file has no database.

1. Make sure `package.json` lists `drizzle-orm` as a dependency and `drizzle-kit` as a dev dependency. `helix init` adds both to new projects. Keep both: a deploy builds the migration with your project's own `drizzle-kit` and fails without it.
2. Create `db/schema.ts`. Use exactly this path, and a real file rather than a symlink.
3. Run `helix deploy`. The first deploy of a project with a schema creates its database and applies the schema before the new code goes live.

```typescript
// db/schema.ts
import { boolean, pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core';

export const notes = pgTable('notes', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  body: text('body').notNull().default(''),
  done: boolean('done').notNull().default(false),
  createdAt: timestamp('created_at').notNull().defaultNow(),
});
```

Column helpers come from `drizzle-orm/pg-core`, including `uuid`, `text`, `integer`, `bigint`, `boolean`, `timestamp`, `date`, `jsonb`, and `vector`, along with `index`, `uniqueIndex`, and `primaryKey`. The database includes the `pgvector` extension, so `vector` columns and vector indexes work as they do in Drizzle, except that a `vector` value you write or compare needs a cast (see [Columns that need a cast](#columns-that-need-a-cast)). Enum, `numeric` and array columns need a cast too, and `@trayai/helix-sdk/db` has ready-made column types for them. Relative imports inside `db/` are fine. Packages stay external.

## Schema changes and migrations

You never write or commit migration files. The source holds only `db/schema.ts`. On each deploy the platform compares your schema with the one the database already has, generates the migration, and applies it before the new code goes live.

- **A failed migration fails the deploy.** The previous version keeps running. Run `helix deployment get <deploymentId>` to read the error.
- **Each migration is additive or destructive.** Adding tables and nullable or defaulted columns is additive and always safe: edit `db/schema.ts` and deploy. Dropping a table or column, or narrowing a type, is destructive, and a deploy that contains one is refused.
- **Your functions cannot change the schema.** The role your functions use can read and write data but cannot run `CREATE`, `ALTER`, or `DROP`. Tables come from `db/schema.ts` and a deploy, never from handler code.
- **Nothing runs from your machine.** There is no command to push a schema to a deployed database. `helix deploy` is the only way schema changes reach it.

To see what has been applied, run [`helix db migrations`](/documentation/reference/cli/db/).

### Remove or rename in steps

Make removals and renames across separate deploys, so the live code never depends on something that is gone:

1. **Expand.** Add the new column or table, keep the old one, and write to both.
2. **Backfill.** Copy the existing data with a one-off function that reads the old column and writes the new one through `ctx.db()`. Migrations carry schema changes, not data.
3. **Contract.** Stop using the old column or table, then remove it from `db/schema.ts` in a later deploy.

A change that drops one column and adds another in the same deploy looks like a rename. Drizzle Kit would ask you which it is, and a build has no terminal to answer on, so the build fails with a message telling you to make the change in steps.

## Local development

`helix dev` runs `ctx.db()` on an in-memory [PGlite](https://pglite.dev/) database, which is Postgres compiled to WebAssembly with `pgvector`. Its tables are created from `db/schema.ts` using your project's `drizzle-kit`. The startup output says `database: in-memory PGlite, reset on every save/restart` when it is active.

What differs from a deployed project:

- **It is reset often.** Every file save and every restart of `helix dev` starts from an empty database. Seed what you need at the start of a request or in a dev-only function. Saving `db/schema.ts` rebuilds it too. A `db/schema.ts` you add while `helix dev` is running is picked up on the next function save or restart.
- **It never touches a deployed database.** Nothing you do locally changes deployed data.
- **There is no migration history.** The schema is applied fresh each time, so local development does not exercise additive and destructive checks.
- **Limits and isolation are not applied.** Statement limits, timeouts, and per-project isolation exist only on a deployed project, so test them after you deploy.
- **The Postgres version can differ.** A feature that exists only in a newer Postgres version can work locally and fail when deployed.
- **Parameters are bound as they are when deployed.** The deployed database receives string parameters untyped, and `helix dev` sends them the same way, so a query that needs a cast fails locally with the same Postgres error instead of only after you deploy. See [Columns that need a cast](#columns-that-need-a-cast).
- **Raw SQL results can differ.** The value `execute()` returns can have a different shape locally and when deployed, so check code that reads raw results against a deployed project. Queries built with the Drizzle query builder behave the same in both.

## Runtime behavior

- **The first call after a quiet period can be slower.** An idle database wakes up when it is next used. A call made while it wakes can answer with a retryable 503 and the code `DATABASE_RESUMING`. The SDK retries that call automatically, up to three attempts and about 30 seconds in all, before it fails.
- **A failed statement is not retried.** A retry could run a write twice, so only the wake-up case above is retried. Retry only logic you know is safe to repeat.
- **Results and requests have size limits.** A single result of about 1 MiB and a single request of about 4 MB are rejected. Page large reads with `limit` and `offset`.

## Run SQL

`ctx.db()` takes no argument and returns a Drizzle query builder typed from your schema. Import operators from `drizzle-orm` and tables from `db/schema.ts`.

Always pass values through Drizzle operators or the `sql` tag, which bind them as parameters. Never build SQL by joining strings.

### Select, insert, update, and delete

```typescript
// functions/api/notes.post.ts
import { defineFunction } from '@trayai/helix-sdk';
import { desc, eq } from 'drizzle-orm';
import { z } from 'zod';
import { notes } from '../../db/schema';

export const input = z.object({ title: z.string().min(1) });

export default defineFunction<typeof input>(async (ctx) => {
  const db = ctx.db();

  // Insert. returning() gives back the row with defaults filled in.
  const [created] = await db.insert(notes).values({ title: ctx.input.title }).returning();

  // Select, newest first.
  const recent = await db.select().from(notes).orderBy(desc(notes.createdAt)).limit(20);

  // Update.
  const [updated] = await db
    .update(notes)
    .set({ done: true })
    .where(eq(notes.id, created.id))
    .returning();

  ctx.status(201);
  return { created, updated, recent };
});
```

Delete a row the same way:

```typescript
import { eq } from 'drizzle-orm';
import { notes } from '../../db/schema';

await ctx.db().delete(notes).where(eq(notes.id, 1));
```

Upserts, joins, and `returning()` work as they do in Drizzle. Row types come from the schema: `typeof notes.$inferSelect` for rows and `typeof notes.$inferInsert` for inserts.

### Raw SQL

For a query the builder cannot express, use `execute` with the `sql` tag. Interpolated values are bound as parameters:

```typescript
import { sql } from 'drizzle-orm';

const open = await ctx.db().execute(
  sql`SELECT count(*)::int AS open FROM notes WHERE done = ${false}`,
);
```

### List your tables

Query `information_schema` to see which tables exist in the database a function is talking to:

```typescript
import { sql } from 'drizzle-orm';

const tables = await ctx.db().execute(
  sql`SELECT table_name FROM information_schema.tables
      WHERE table_schema = current_schema()
      ORDER BY table_name`,
);
```

### Transactions

Everything inside the callback commits together or rolls back together. A thrown error rolls the transaction back:

```typescript
import { notes } from '../../db/schema';

const result = await ctx.db().transaction(async (tx) => {
  const [first] = await tx.insert(notes).values({ title: 'First' }).returning();
  const [second] = await tx.insert(notes).values({ title: 'Second' }).returning();
  return { first, second };
});
```

## Columns that need a cast

The deployed database receives your query's string parameters untyped, and Postgres refuses to assign or compare untyped text with some column types. These fail with errors like `column "status" is of type status but expression is of type text` or `operator does not exist: status = text`:

- an enum column,
- a `numeric` column in `number` mode (`numeric('price', { mode: 'number' })`),
- an array column, such as `text[]`,
- a `vector` value (pgvector),
- a bare `Date` inside a `sql` template, or a bare string compared there with an enum, numeric or array column. `gte(table.createdAt, date)` is fine, because the column supplies the type.

Numbers and booleans are typed when they are sent and need no cast. `uuid`, `text`, `integer`, `boolean`, `timestamp`, `date`, `jsonb` and string-mode `numeric` columns need none either.

`@trayai/helix-sdk/db` exports replacements that cast for you. They need no migration, and reads return the same values as before:

```typescript
// db/schema.ts
import { integer, pgEnum, pgTable } from 'drizzle-orm/pg-core';
import { enumColumn, numericNumber, textArray } from '@trayai/helix-sdk/db';

export const statusEnum = pgEnum('status', ['open', 'closed']);

export const tickets = pgTable('tickets', {
  id: integer('id').primaryKey(),
  status: enumColumn('status', statusEnum).notNull().default('open'),
  price: numericNumber('price', { precision: 10, scale: 2 }),
  tags: textArray('tags'),
});
```

```typescript
import { sql } from 'drizzle-orm';
import { asTimestamptz } from '@trayai/helix-sdk/db';

await ctx.db().execute(sql`SELECT * FROM events WHERE created_at >= ${asTimestamptz(since)}`);
```

| Helper | Replaces | Notes |
|---|---|---|
| `enumColumn(name, enumType)` | `enumType(name)` | `enumType` comes from `pgEnum`, which still creates the type in the migration. An enum declared inside a Postgres schema is not supported. |
| `numericNumber(name, { precision, scale })` | `numeric(name, { mode: 'number' })` | Reads and writes a JavaScript `number`. |
| `textArray(name)` | `text(name).array()` | Reads and writes a `string[]`. A NULL element is not supported. |
| `asTimestamptz(date)` | a bare `${date}` in `sql` | Takes a `Date`, or an ISO 8601 string that carries a timezone (`Z` or an offset such as `+02:00`). A string with a time but no timezone is rejected, because it would be read in the local timezone. |

For anything without a helper, cast in the SQL yourself: a `vector` value, an `integer[]` column, or a bare string compared with an enum or numeric:

```typescript
await ctx.db().execute(
  sql`INSERT INTO docs (id, embedding) VALUES (${id}, cast(${JSON.stringify(embedding)} as vector))`,
);
```

The helpers are columns, so they do not work with `sql.placeholder()` or prepared statements: Drizzle encodes a placeholder's value with the column, and the cast is lost. Cast the placeholder in the SQL instead.

## Troubleshooting

### A call fails at runtime

Database failures reach your code as `SdkError`s with one of these kinds:

| Kind | Meaning |
|---|---|
| `db_call_failed` | The statement failed. The message is `CODE: message` and the original error is `cause`, so read `cause`, because Drizzle wraps driver errors. |
| `db_managed_requires_provisioning` | The project is not deployed yet. Run `helix deploy` first. |
| `db_managed_not_available` | In `helix dev`, the project has no `db/schema.ts`. |
| `db_dev_setup_failed` | `helix dev` could not create the local database. Either `drizzle-kit` is missing or `db/schema.ts` does not compile. The message says which. |

A `db_call_failed` whose cause says `expression is of type text` or `operator does not exist: ... = text` means a column needs a cast, as in [Columns that need a cast](#columns-that-need-a-cast).

For a deployed function, read the execution in the terminal. `helix logs list --errors` finds the failing execution, and `helix logs get <executionId>` shows who called it, which deployment ran it, and each database call with its SQL and any error. See [Logs commands](/documentation/reference/cli/logs/).

### A deploy fails

Run `helix deployment get <deploymentId>` with the ID that `helix deploy` printed, and read every string under the errors. The usual causes:

- `drizzle-kit` or `drizzle-orm` was removed from `package.json`.
- The schema is somewhere other than `db/schema.ts`.
- The change drops or narrows something, or drops one column and adds another in the same deploy. Split it into steps, as in [Remove or rename in steps](#remove-or-rename-in-steps).

### The old aliased client

`ctx.db(alias)`, the older aliased client that talks to Redshift, is deprecated. It still works, logs one warning per alias, and will be removed in a future major version. No replacement is documented yet, and new code should not use it. `ctx.db()` with no argument, the managed database, is not affected.

---

Canonical: https://helix.tray.ai/documentation/guides/database/
Any link on this page is available as markdown by appending .md to its URL.
Full corpus: https://helix.tray.ai/documentation/llms-full.txt