Migrations

Your database stays in sync with your code in real time. Save a migration file and Elements applies it instantly. Edit the migration in development and Elements updates the database immediately. Working with migrations is the same development experience as working with any other source file.

Migrations are SQL files under app/migrations/, and applying them is part of the build loop. The build server runs any pending migrations against the database automatically, inside a single Postgres transaction that either succeeds in full or rolls back in full. There are no up and down pairs to keep in sync, and no orchestration to wire up.

Migrations work the same way on every machine the app runs on, with one exception. In development you can edit a migration after it has applied and Elements re-runs it against the local database. On a deploy machine, once a migration has run it is never run again, even if the file changes.

The elements db migrate command applies pending migrations on demand. Use it when a migration needs to run out of band, for example if the database was unreachable or in a bad state when the build server tried to apply it. Usually the build takes care of migrations and there is nothing for you to do.

At a Glance

elements create migration "add comments table" -tables=comments

Generates app/migrations/<timestamp>-add-comments-table.migration.sql:

create or replace function touchUpdatedAt()
returns trigger
language plpgsql
as $$
begin
  new.updatedAt = now();
  return new;
end;
$$;

create table comments (
  id uuid primary key default uuidGenerateV7(),
  createdAt timestamptz not null default now(),
  updatedAt timestamptz not null default now()
  -- add columns here
);

create trigger commentsTouchUpdatedAt
  before update on comments
  for each row execute function touchUpdatedAt();

Save the file. Elements applies it to the database. In development, you can edit it (add a column, a constraint, an index) and save again. Elements rolls back this migration and replays the new version.

How Migrations Apply

Elements tracks the content hash of every migration file on disk against the hash that was recorded when the migration was applied to the database.

Migrations always run against the test database first, then against the app database. If applying to the test database fails, the build fails before the migrations are applied to the app database.

On every build:

• A new migration file runs against the database, inside a single Postgres transaction with every other pending migration. If migration #7 of 10 fails, all 10 roll back, and the database is left exactly as it was before the batch.
• An unchanged file is skipped.
• A changed file behaves differently per environment. In development, Elements reapplies the migration so long as it is within the reapply window. Elements keeps a fixed number of pre-migration backups, and once a migration falls outside that sliding window it can no longer be re-edited. On any other machine, a changed migration is never re-run. The build fails with a stale-migration error. To unblock, revert the file to its applied content and put the new changes in a new migration. As an escape hatch, set ignoreStaleMigrations on the environment and Elements ignores the stale file; the file's new content is not applied either way.

Some Postgres DDL cannot run inside a transaction, including CREATE INDEX CONCURRENTLY, VACUUM, REINDEX CONCURRENTLY, ALTER TYPE ... ADD VALUE (pre-Postgres 12), and anything else the Postgres docs list as "cannot be executed within a transaction block". Apply those out of band by connecting to the database directly, then add a comment-only migration so the on-disk state matches reality.

Creating a Migration

elements create migration "describe what it does"
elements create migration "add users table" -tables=users
elements create migration "add users and roles" -tables=users,roles

-tables=... scaffolds the canonical baseline for each named table:

• id uuid primary key default uuidGenerateV7(), a UUIDv7 primary key that is time-sortable.
• createdAt timestamptz not null default now().
• updatedAt timestamptz not null default now().
• A trigger that updates updatedAt on every row update via touchUpdatedAt().

The baseline matches what LiveTable expects and what the realtime pub/sub triggers assume. Hand-rolled DDL works when you need it, but you take on the responsibility for matching that contract.

camelCase Columns

Write camelCase in your migration files. Elements converts to snake_case at the database boundary and back to camelCase on results.

create table products (
  id uuid primary key default uuidGenerateV7(),
  sku text not null unique,
  displayName text not null,
  listPrice integer not null,
  isArchived boolean not null default false,
  createdAt timestamptz not null default now(),
  updatedAt timestamptz not null default now()
);

The same rule applies in indexes, foreign keys, and any other DDL that names columns.

Reverting

In development, delete the migration file:

rm app/migrations/<timestamp>-bad-idea.migration.sql

Elements rolls it back automatically, provided the migration is still within the reapply window.

In production, migrations are forward-only. To undo a deployed migration, write a new migration that corrects the change:

elements create migration "fix bad column type from previous migration"

The corrective migration applies on top of the bad one on the next deploy.

elements db migrate

elements db migrate               # apply pending migrations on demand
elements db migrate -json         # machine-readable output

Use this when a migration needs to run out of band, for example after a database was unreachable when the build server tried to apply it. Normal development does not need to run this command.

Related

• elements man database: the SQL surface, transactions, and casing.
• elements man livetable: LiveTable expects the standard id, createdAt, updatedAt columns the migration generator creates.
• elements man deploy: the test-database-first / app-database-second deploy sequence.