Skip to content
Helix Docs

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.

For developers Updated Nov 16, 2026
View as Markdown

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() 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.
// 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). 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.

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

// 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:

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:

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:

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:

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:

// 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'),
});
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)}`);
HelperReplacesNotes
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 sqlTakes 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:

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 SdkErrors with one of these kinds:

KindMeaning
db_call_failedThe statement failed. The message is CODE: message and the original error is cause, so read cause, because Drizzle wraps driver errors.
db_managed_requires_provisioningThe project is not deployed yet. Run helix deploy first.
db_managed_not_availableIn helix dev, the project has no db/schema.ts.
db_dev_setup_failedhelix 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.

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.

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.

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.

Loading search…

Jump to a section

tab to move · esc to close