elements
Manual Migrations

Migrations

elements man migrations Read as markdown

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, in filename order, each file in its own 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 apply the same way on every machine the app runs on. In development you can also edit a migration after it has applied, and Elements re-runs it against the local database. To change a migration a deploy machine has run, add a new one: on a deploy machine each migration runs once, which keeps live data in place.

The elements db migrate command applies pending migrations on demand, for example right after a database comes back online. The build applies migrations for you otherwise.

At a Glance

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

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

app/migrations/20260509120000-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 in its own Postgres transaction, and the record that it applied commits in that same transaction. If migration #7 of 10 fails, #7 rolls back, #1 through #6 stay applied, and #8 through #10 wait. The build reports the error on #7. Fix the file and save it, and the build applies #7 through #10.
  • An unchanged file is skipped.
  • A changed file behaves differently per environment. In development, Elements restores the database from the backup it took before that migration first applied, then replays every migration from there. When one build changes several migrations, edited or deleted, it restores from before the earliest of them. It keeps the 20 most recent pre-migration backups. When no backup is left for the edited migration, it drops the database and rebuilds it from every migration. Either way, local data added since that point is lost, and so are sessions, which live in the database: after a rebuild everyone is signed out, and after a restore anyone who signed in since the backup is. On any other machine, migrations are forward-only and a changed migration is never re-run. The build fails with a stale-migration error. To continue, revert the file to its applied content and put the new changes in a new migration. Or set ignoreStaleMigrations on the environment and Elements ignores the stale file; the file's new content is not applied either way.

One build or deploy migrates a database at a time. Elements holds a Postgres advisory lock on the database for the whole run, so when two processes or machines reach the same database at once, the second waits for the first and then finds its migrations applied.

Statements Outside a Transaction

Some statements run only outside a transaction block: CREATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY, VACUUM, and ALTER TYPE ... ADD VALUE followed by use of the new value. Tag the migration @migration tx=false and it applies in the build like any other, with each statement running on its own:

app/migrations/20260509120000-index-orders-by-customer.migration.sql
/** @migration tx=false */

drop index concurrently if exists ordersCustomerIdIdx;
create index concurrently ordersCustomerIdIdx on orders (customerId);

The tag goes in a /** */ comment before the first statement. Each statement commits as it finishes, and Elements records the migration after the last one succeeds. If a statement fails, the migration stays pending and the next build runs the whole file again, so write each statement to be safe to repeat: if exists, if not exists, or the drop-and-create pair above. The pair also replaces the invalid index Postgres leaves behind when a concurrent build fails. Keep a tx=false migration to the statements that need it, and put ordinary schema changes in a transactional migration of their own.

Creating a Migration

Terminal
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-written DDL works too: give its tables the same columns and trigger, and LiveTable and the realtime triggers work with them unchanged.

camelCase Columns

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

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

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

Elements rolls it back automatically the same way it handles an edit: it restores the backup taken before the migration applied, or rebuilds the database from the remaining migrations when no backup is left. Local data added since is lost.

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

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

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

Env-Scoped Migrations

A migration tagged @migration env=<names> applies only in the environments it names. Demo rows for local work go in a migration tagged @migration env=development, which applies to the development databases and is skipped on a deploy machine:

Seed migration
/** @migration env=development */

insert into users (
  email,
  name
) values (
  'ada@example.com',
  'Ada'
);

A seed file follows the same edit rule as any migration, so write it once and save it when it is done. elements man database/seeds covers the tag, which database gets which file, and reference rows every environment needs.

The @migration Tag

@migration is a build tag, written in a /** */ comment among the file's leading comments, before the first statement. Its options are key=value pairs separated by spaces:

Option Values Default
env environment names, comma-separated with no spaces: env=development,staging every environment
tx true or false true

The options combine:

Example
/** @migration env=production tx=false */

An unknown option, a malformed value, or a @migration in a /* */ or -- comment is a build error, and the migration applies nowhere until it is fixed.

elements db migrate

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

Use it to apply pending migrations on your own schedule, for example right after a database comes back online. The build applies them for you otherwise.

Related

  • elements man database: the SQL surface, transactions, and casing.
  • elements man database/seeds: seed data and @migration env=.
  • 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.