PostgreSQL

Store the models in PostgreSQL, sync its tables in development, and change them in production with migration files.

postgres() stores the models in a PostgreSQL database, version 15 or later. In development, each start syncs the tables to the models. For production, migrate create writes each change as an SQL file that you commit; migrate deploy applies those files.

npm install @ultimabase/postgres

src/app.ts

import { graphqlPlugin } from '@ultimabase/graphql'
import { httpPlugin } from '@ultimabase/http'
import { postgres } from '@ultimabase/postgres'
import { defineApp } from 'ultimabase'
import { Project, Task } from './models.ts'

export const app = defineApp({
	// Outside production, a start syncs the tables to the models; in
	// production it checks that the migrations deployed match them.
	database: postgres({ url: process.env['DATABASE_URL'] }),
	// Listens on PORT, or 3000.
	plugins: [httpPlugin(), graphqlPlugin()],
	models: [Project, Task],
	// Accepted in production, where it needs 32 characters at least.
	masterKey: { key: process.env['MASTER_KEY'], production: true },
})

The full example, with its tests: examples/postgres.

url is a connection string such as postgres://user:password@localhost:5432/app. The ultimabase commands run the application that ultimabase.config.ts exports. They look for the migration files in migrations/, beside it:

ultimabase.config.ts

export { app as default } from './src/app.ts'

Development

Outside production, a start syncs the tables to the models:

npx ultimabase dev

dev sets NODE_ENV to development and starts the application. When a file changes, it restarts it, and syncs the tables again. sync does the same without starting the application:

src/app.postgres.test.ts

const synced = await ultimabase('sync', { env: development })
expect(synced.code).toBe(0)
expect(synced.output).toContain("Created the database's tables.")
expect(await ultimabase('sync', { env: development })).toMatchObject({
	code: 0,
	output: 'The database already matches the models.\n',
})

Each change logs a line, such as Synced Task: added priority, 0 in the rows it held. A sync applies a change only when it keeps the rows. It refuses a change that deletes data, prints the SQL it would run, and says what to do:

npx ultimabase sync --force

--force applies the change to this development database. Or write it as a migration, whose SQL you can edit (see Migrations). When a removed field looks renamed, the refusal says so: -- a rename? keep its data: give Locker.title dbName: 'label'. That dbName keeps the old column, with its data, under the new field.

A sync also refuses a change the rows cannot take, such as a required field without a default on a table that holds rows. Make the field optional, give it a default, or rebuild the database with npx ultimabase reset.

The tables

Each model is a table, each field a column, named in snake case: Task is task, createdAt is created_at.

src/models.ts

export const Project = defineModel({
	name: 'Project',
	use: [graphql()],
	fields: (f) => ({
		name: f.string({ unique: true }),
		tasks: f.many(() => Task),
	}),
	// Open to every client, to keep the example on the database.
}).with({ access: allow.public })

export const Task = defineModel({
	name: 'Task',
	use: [graphql()],
	fields: (f) => ({
		title: f.string(),
		done: f.boolean({ default: false }),
		// Added after the first migration: the second one adds its column.
		priority: f.number({ integer: true, default: 0 }),
		// A project's tasks are deleted with it.
		project: f.one(() => Project, { onDelete: 'cascade' }),
	}),
}).with({ access: allow.public })

Field	Column
`id`	`uuid`, the primary key
`createdAt`, `updatedAt`	`timestamptz not null`
`f.string()`	`text collate "C"`
`f.number()`	`double precision`; `integer` with `integer: true`
`f.decimal()`	`numeric(precision, scale)`
`f.boolean()`	`boolean`
`f.dateTime()`	`timestamptz`
`f.date()`	`date`
`f.json()`	`jsonb`
`f.enumOf()`	`text`, with a check of its values
a list (`list: true`)	`text[]`, `double precision[]` or `integer[]`
`f.one()`, the side stored	`<field>_id uuid`, indexed, with a foreign key checked at commit
`f.many()` both ways	a table `<table>_<field>`, with a `<table>_id` column per side
`search: true`	a generated `_search` column, with a GIN index

A required field is not null; an optional one is nullable. A unique field or index is a unique constraint. The foreign keys have no on delete: UltimaBase runs onDelete itself, with the hooks and access rules. Constraint names end in _pkey, _key, _fkey, _check and _idx. A name longer than PostgreSQL's 63 bytes keeps its first 54 bytes plus an 8-character hash. dbName names a table or a column yourself (see Models and fields).

An index with contains: true installs the pg_trgm extension, which needs the right to create it.

Migrations

When the models change, write the change as a migration:

npx ultimabase migrate create add_priority

Here, priority was added to Task. The command wrote migrations/20261029230454_add_priority.sql, named after the time in UTC, and updated migrations/schema.json, the models the migrations lead to. Commit both:

src/migrations.test.ts

// After `priority` was added to Task: `migrate create add_priority`.
expect(await lines('20261029230454_add_priority.sql')).toEqual([
	header,
	'',
	'alter table "task" add column "priority" integer not null default 0;',
	'',
	'alter table "task" alter column "priority" drop default;',
	'',
])

The first line is the hash of the models after the file. A deploy uses it to check that the files match the models. A statement that deletes data or may fail on the rows gets a comment above it, such as -- removing "Locker.label" deletes data.. Read the file, and edit it before you commit it.

Your own SQL

When no model changed, migrate create writes a file for your SQL: a data change, a view, an index of your own. It prints Wrote migrations/20261029230505_first_tasks_urgent.sql: no model changed, write your SQL in it.

src/migrations.test.ts

// `migrate create first_tasks_urgent`, then the update written in it.
expect(await lines('20261029230505_first_tasks_urgent.sql')).toEqual([
	header,
	'',
	'-- The tasks written before priorities came first.',
	'update "task" set "priority" = 1 where not "done";',
	'',
])

Each file runs in a transaction: if a statement fails, the file is rolled back. Some statements cannot run in a transaction, such as create index concurrently. Put such a statement alone in its file, with -- ultimabase no transaction as the second line:

src/migrations.test.ts

// A statement PostgreSQL runs outside a transaction, alone in its file.
expect(await lines('20261029230507_open_tasks_index.sql')).toEqual([
	header,
	'-- ultimabase no transaction',
	'',
	'-- Built without locking the table against writes.',
	'create index concurrently "task_open_idx" on "task" ("project_id") where not "done";',
	'',
])

Deploy

migrate status says what a deploy would do, and exits 1 while it has something to do. migrate deploy applies the files the database lacks, in order:

npx ultimabase migrate deploy

src/app.postgres.test.ts

// Exits 1 while a deploy has something to do.
expect(await ultimabase('migrate status')).toMatchObject({
	code: 1,
	output: [
		'20261026200257_init.sql is pending.',
		'20261029230454_add_priority.sql is pending.',
		'20261029230505_first_tasks_urgent.sql is pending.',
		'20261029230507_open_tasks_index.sql is pending.',
		'',
	].join('\n'),
})

src/app.postgres.test.ts

expect(await ultimabase('migrate deploy')).toMatchObject({
	code: 0,
	output: [
		'Applied 20261026200257_init.sql.',
		'Applied 20261029230454_add_priority.sql.',
		'Applied 20261029230505_first_tasks_urgent.sql.',
		'Applied 20261029230507_open_tasks_index.sql.',
		'',
	].join('\n'),
})
expect(await ultimabase('migrate status')).toMatchObject({
	code: 0,
	output: 'The database is up to date.\n',
})

Running it, its stops and its refusals in production are on Deploy.

Test a migration

Deploy applies migrations only to a database they built. After a sync, it is refused. reset drops every table, then applies the migrations. It gives you the database production will have:

npx ultimabase reset

src/app.postgres.test.ts

// A deploy runs only on a database the migrations built.
expect(
	await ultimabase('migrate deploy', { env: development }),
).toMatchObject({
	code: 1,
	output: [
		'Development sync changed this database since its last deploy: deploy applies migrations only to a database they built.',
		'Restore the dump you test on, or rebuild this database from the migrations with `npx ultimabase reset`.',
		'',
	].join('\n'),
})

src/app.postgres.test.ts

expect(await ultimabase('reset', { env: development })).toMatchObject({
	code: 0,
	output: [
		'Dropped project, task.',
		'Applied 20261026200257_init.sql.',
		'Applied 20261029230454_add_priority.sql.',
		'Applied 20261029230505_first_tasks_urgent.sql.',
		'Applied 20261029230507_open_tasks_index.sql.',
		'',
	].join('\n'),
})

To test a data migration on real rows, restore a dump of production in development, then run npx ultimabase migrate deploy.

Tests

testApplication(app, { database: 'declared' }) runs a test file on the application's own PostgreSQL, in a schema of the file's own: its tables are emptied before each test, and the schema is dropped after the last one.

src/tasks.postgres.test.ts

// On the database DATABASE_URL names: the file's own schema, its tables
// emptied before each test, dropped after the last.
testApplication(app, { database: 'declared' })

it('deletes the tasks with their project', async () => {
	const project = await Project.create(
		{ data: { name: 'Launch', tasks: { create: [{ title: 'Write' }] } } },
		{ as: master },
	)
	await Project.delete({ where: { id: project.id } }, { as: master })
	expect(await Task.count({}, { as: master })).toBe(0)
})

it('starts with empty tables', async () => {
	expect(await Project.count({}, { as: master })).toBe(0)
})

Name these files *.postgres.test.ts, and keep the other tests on memory() (see Testing).

Options

src/search.postgres.test.ts

const app = defineApp({
	database: postgres({
		url: process.env['DATABASE_URL'],
		// At most 20 queries at once; the next waits up to 10 s for one.
		connections: 20,
		// Words stemmed as English: "running" finds "runs".
		searchLanguage: 'english',
	}),
	models: [Article],
})

searchLanguage names a text search configuration of the server, such as 'english' or 'french'. It stems the words that _search reads:

src/search.postgres.test.ts

await Article.create(
	{ data: { body: 'She runs every morning' } },
	{ as: master },
)
const found = await Article.findMany(
	{ where: { _search: 'running' } },
	{ as: master },
)
expect(found.map((article) => article.body)).toEqual([
	'She runs every morning',
])

Reference

`postgres(options)`

Option	Type	Default	Effect
`url`	`string \| undefined`	required	The connection string. Unset or empty, the start is refused.
`connections`	whole number, 1 or more	`10`	The connections opened at most; a query waits up to 10 s for a free one.
`searchLanguage`	`string`	`'simple'`	The text search configuration `_search` reads words in.

Commands

Command	Effect
`ultimabase dev`	Starts the application in development, syncs the tables, and restarts it when a file changes.
`ultimabase sync [--force]`	Syncs a development database to the models; `--force` applies a change that deletes data.
`ultimabase reset [--force]`	Drops every table of a development database, then applies the migrations; `--force` allows a database on another machine.
`ultimabase migrate create <name>`	Writes the models' changes as a migration, or an empty one for your SQL. `name` holds lowercase letters, digits and `_`.
`ultimabase migrate deploy`	Applies the pending migrations.
`ultimabase migrate status`	Says what a deploy would do; exits 1 if anything.
`ultimabase start`	Starts the application in production (see Deploy).

Each takes --config <path> for a config other than ultimabase.config.ts, and exits 1 when it fails.

Messages

Message	When
`Created the database's tables.`	A sync on a database without the tables.
`Synced Task: added priority, 0 in the rows it held.`	A sync changed a table.
`The database already matches the models.`	A sync with nothing to do.
`UltimaBase did not sync Locker: removing "Locker.label" deletes data.`	A change that deletes data, without `--force`.
`UltimaBase did not sync Locker: another session held its table.`	Another connection held the table for 5 s: end its transaction.
sync changes only a development database: run `npx ultimabase migrate deploy` in production.	`sync` in production.
`Wrote migrations/<file>.`	`migrate create` wrote the models' changes.
`Wrote migrations/<file>: no model changed, write your SQL in it.`	`migrate create` without a model change.
`"<name>" is not a migration name: use lowercase letters, digits and _.`	A name with other characters.
`migrations/ holds SQL files but no schema.json: restore it from version control.`	`schema.json` is missing.
`<file> is pending.`	`migrate status`, for each file to apply.
`Applied <file>.`	`migrate deploy` or `reset` applied a file.
`The database is up to date.`	Nothing to deploy.
migrations/ holds no migration: run `npx ultimabase migrate create <name>` first.	A deploy without migrations.
The models changed after <file> was written: run `npx ultimabase migrate create <name>`, then deploy again.	The models differ from the last migration.
`<file> was edited after it was applied to the database: restore it, …`	An applied file changed.
`<file> was applied to the database, and migrations/ no longer holds it: restore it from version control.`	An applied file is missing.
`Development sync changed this database since its last deploy: deploy applies migrations only to a database they built.`	A deploy after a sync: run `reset`.
`<file> was not applied: <reason>`, then `The database rolled it back: fix it, then deploy again.`	A statement failed.
`Dropped project, task.`	`reset` dropped those tables.
`reset rebuilds only a development database: it is refused in production.`	`reset` in production.
`reset drops every table, and db.example.com is not this machine: pass --force if it holds a development database.`	`reset` on a database on another machine.
`postgres() was given no url: is the variable it reads set?`	`url` is unset or empty.
`Cannot reach PostgreSQL at localhost:5432 (ECONNREFUSED): is it running, and is its url right?`	The server does not answer.
`UltimaBase needs PostgreSQL 15 or later.`	An older server.
`postgres() searches in 'klingon', a text search configuration this server does not have.`	An unknown `searchLanguage`.
`Every one of the 10 PostgreSQL connections stayed busy for 10s: a transaction may wait on something slow or never end.`	No connection came free in 10 s.

A warning at start says when the database's locale folds case for ASCII only: mode: 'insensitive' then misses É and é. Create the database with a UTF-8 locale, such as en_US.UTF-8.

Limits

PostgreSQL 15 or later.
A migration waits 5 s at most for a table another session holds.
A file marked -- ultimabase no transaction holds one statement; if it fails, nothing is rolled back.
sync and reset refuse production; reset refuses another machine without --force.

DevelopmentCopy Anchor Link

The tablesCopy Anchor Link

MigrationsCopy Anchor Link

Your own SQLCopy Anchor Link

DeployCopy Anchor Link

Test a migrationCopy Anchor Link

TestsCopy Anchor Link

OptionsCopy Anchor Link

ReferenceCopy Anchor Link

postgres(options)Copy Anchor Link

CommandsCopy Anchor Link

MessagesCopy Anchor Link

LimitsCopy Anchor Link

On this page

Development

The tables

Migrations

Your own SQL

Deploy

Test a migration

Tests

Options

Reference

`postgres(options)`

Commands

Messages

Limits