sqldef-on-rails

psqldef on Rails

Running a Rails app's PostgreSQL schema with psqldef instead of ActiveRecord migrations: the desired schema is a set of CREATE statements, and psqldef diffs it against the live database and applies the difference. Project-specific conventions (CLAUDE.md, docs/) always take priority.

For Aurora DSQL, read /sorah-guides:dsql-on-rails as well; it builds on this setup and replaces several tasks.

The model

• The schema is db/schema/*.sql, one file per table, holding its CREATE TABLE, its indexes and its GRANTs. The rake task concatenates the files in sorted order and hands psqldef one desired schema.
• There is no db/migrate/ and no schema.rb/structure.sql. The .sql files are the source of truth, and reviewers read the diff of the desired state, not a migration.
• A schema change ships in the same commit as the code that uses it, never as a preparatory commit.
• rake db:migrate applies the files; rake db:migrate:status shows the plan (--dry-run). Read the plan before applying anything that drops or rewrites.
• psqldef only expresses the end state, plus rename hints. Renaming a table or column without the hint looks like drop-and-add, and a backfill does not exist in the file at all (see below).

-- db/schema/widgets.sql
create table widgets (
    id uuid primary key default gen_random_uuid(),
    owner_id uuid not null references owners (id),
    name varchar(64) not null,
    created_at timestamp(6) with time zone not null default now(),
    updated_at timestamp(6) with time zone not null default now()
);

create index idx_widgets_owner_id on widgets (owner_id);

grant all privileges on widgets to app_admin;
grant select, insert, update, delete on widgets to app_user;

Write a comment on a column whose meaning the name does not carry (an enum's values, an encoded payload). It is the only schema documentation there is.

Rake tasks

references/database.rake is a complete lib/tasks/database.rake for plain PostgreSQL; copy it and adjust. What it does: