v0.8.0

⚡ Highlights

• 🔑 Support for multi-column primary keys (#413, #426)
• 🤸‍♂️ Support for multi-column UNIQUE, CHECK and FOREIGN KEY constraints (#487, #464, #471, #466, #459)
• 📇 Support for more index types (#417, #442)
• 💧 Support dropping DEFAULT constraints on columns (#478)
• ⚡ New CLI commands:
  • pgroll migrate: apply outstanding migrations to the target database (#465)
  • pgroll latest: display the most recent schema version in the target database or local migrations directory (#469)
  • pgroll pull: pull the migration history from a target database and dump it to disk. (#463)
• 🚤 Performance benchmarks, see them here (#472, #412)

Read the full release notes on the pgroll.com blog

🔴 Breaking changes

The format of the drop_constraint operation has changed to remove the column field. Column names should no longer be specified when dropping a constraint. This means that migrations like this must be updated to remove the column field:

{
  "name": "23_drop_check_constraint",
  "operations": [
    {
      "drop_constraint": {
        "table": "posts",
        "column": "title",     <-- should be removed
        "name": "title_length",
        "column": "title",
        "up": "title",
        "down": "(SELECT CASE WHEN length(title) <= 3 THEN LPAD(title, 4, '-') ELSE title END)"
      }
    }
  ]
}

Changelog

• 0992c8d Add --skip-validation flag to start command (#448)
• 85e917c Add pgroll migrate subcommand (#465)
• b6fff69 Add a .ledger file which is built by make examples (#445)
• 9c9518b Add a drop_multicolumn_constraint operation (#487)
• 5a49929 Add a healthcheck to the db service in docker-compose.yml (#452)
• eaaabf9 Add a pgroll latest command (#469)
• 30d131e Add a pgroll pull subcommand (#463)
• 1f6f49b Add a sql2pgroll package to convert SQL to pgroll migrations (#502)
• 481f1c6 Add a benchmark for read_schema (#472)
• fd94011 Add a config.json file for docs website (#500)
• 86e02c5 Add and use a table cleanup assertion function (#493)
• b6f76c7 Add backfill benchmarks (#412)
• 898bd84 Add enum column examples (#444)
• 68578f2 Add missing group by (#456)
• 12ae369 Add support for composite keys in create table statements (#413)
• d231ee0 Add support for creating CHECK constraints with create_constraint (#464)
• a9b2048 Add support for creating foreign key constraints using create_constraint (#471)
• ac1dd85 Add support for creating unique indexes (#442)
• 171f5a2 Allow adding a column to a table created in the same migration (#449)
• c677bbe Allow adding an index to a table created in the same migration (#451)
• 1650210 Allow adding indexes to columns created in the same migration (#454)
• 929ea27 Always surround up and down clauses in parentheses (#481)
• 3c7b74b Avoid running all CI steps twice (#433)
• 860f5a9 Build static HTML page using the latest benchmark results (#467)
• 06d651b Bump github.com/pterm/pterm from 0.12.69 to 0.12.79 (#424)
• 43a6d97 Bump github.com/pterm/pterm from 0.12.79 to 0.12.80 (#501)
• 74a6fb5 Bump github.com/stretchr/testify from 1.9.0 to 1.10.0 (#486)
• 388d0b5 Bump github.com/testcontainers/testcontainers-go/modules/postgres from 0.33.0 to 0.34.0 (#422)
• 4468211 Bump golang.org/x/tools from 0.21.1-0.20240508182429-e35e4ccd0d2d to 0.26.0 (#425)
• 0e34f78 Bump golang.org/x/tools from 0.26.0 to 0.27.0 (#460)
• 0a282a2 Change SQL fomatting tool to pgformatter (#477)
• e02c4a2 Change type of Column.Nullable from *bool to bool (#496)
• 491927c Change type of Column.Pk from *bool to bool (#495)
• d1daf8f Change type of Column.Unique from *bool to bool (#497)
• 9626c20 Configure dependabot (#421)
• 06bfebd Convert ALTER TABLE foo ADD CONSTRAINT bar UNIQUE (a) SQL to pgroll operation (#507)
• 3512f76 Convert ALTER TABLE foo RENAME TO bar SQL to pgroll operation (#513)
• 84661eb Convert DROP NOT NULL SQL to pgroll operation (#505)
• d94d3b4 Convert RENAME COLUMN to pgroll operation (#511)
• 34b623f Convert RENAME CONSTRAINT SQL to pgroll operation (#514)
• c756988 Convert SET DATA TYPE SQL to pgroll operation (#506)
• 7849864 Defer close of migration file (#430)
• 96e50f9 Don't add series for missing row count (#474)
• 5d63a7f Duplicate unique and check constraints correctly (#466)
• 6ff198b Expand operation reference docs (#516)
• f628060 Explicitly add Postgres 17 checks before merge into main (#415)
• 88cc975 Extract initialisation SQL into separate file (#435)
• 0b08a1d Fix read_schema function for multi-column foreign key constaints (#476)
• eef4a9b Fix ledger file in examples folder (#447)
• 8469bad Fix some command short descriptions (#470)
• 6d48386 Handle scan and close errors in SchemaHistory (#482)
• db74779 List enum types when reading schema (#443)
• 5b7980d Make ALTER TABLE foo ALTER COLUMN a SET DATA TYPE text options unrepresentable (#508)
• 2f637c3 Make check-ledger job required for release (#517)
• 01ff6ac Move pkg/testutils and pkg/jsonschema into internal (#418)
• b03cef2 Move validation testcases for op_create_constraint to a separate test (#494)
• 7130506 New command: bootstrap (#414)
• a5fb72e New operation: create_constraint and support unique constraints with multiple columns (#459)
• e044c56 Persist benchmark results in CI (#461)
• 7c01085 Quote schema when running ReadSchema (#419)
• 06e69c2 Refactor docs (#510)
• 999a299 Remove Column field from DropConstraint (#431)
• 358c6c6 Remove pgroll sql subcommand (#515)
• f85bca4 Remove unused operation names (#503)
• a7f3e3c Remove unused parameter (#483)
• 3b8c56e Rename and move test variables relating to sql2pgroll (#512)
• 813b0a1 Respect context cancellation when backing off (#437)
• ddd44bb Respect context deadline during batch backfill (#438)
• 12acbde Run the build workflow on tag push (#520)
• 7bc6eb8 Small cleanups (#484)
• 1279b7d Small docs change to thecreate_constraint operation (#488)
• fdebf23 Split docs/README.md into multiple files (#499)
• e2f1740 Support creating different index types (#417)
• 81dd0a7 Support dropping default for a column (#478)
• b02c324 Support multiple PKs during backfilling (#426)
• c35070a Tweak Makefile (#436)
• 2d2b4a0 Up not required when adding serial columns (#432)
• 434cf36 Update docs with deprecation notice (#498)
• 416fb7a Update schema during add column validation (#457)
• d656387 Update schema during create table validation (#455)
• 0d3e17d Update tutorial with new sections (#509)
• 7655f53 Use ubuntu 24.04 as the runner in all workflows (#518)
• 515dd54 Use filepath.Glob in bootstrap (#416)
• 0b5646d Validate check name is unique (#429)
• 37a7232 Validate maximum identifier length (#439)
• 9922e5f Validate that FK constraint name is unique (#428)
• d00dd1e Validate that unique constraint name is unique (#427)
• 4f484fc deps: Upgrade cobra and viper (#420)

v0.7.0

⚡ Highlights

💻 Add support for Postgres 17 (#387)
🚤 Improve backfill performance by up to 80% (#389)
📦 Allow configuration of the batch size during backfills with the --backfill-batch-size flag (#406)
🐢 Allow configuration of a delay between each batch during backfills with the --backfill-batch-delay flag (#410)
🧩 Add support for creating partial indexes in create_index operations (#404)
💭 Capture CREATE / DROP schema statements as inferred migrations (#359)
⏫ Use of pgroll as a Go module now requires Go 1.23 (#397)
🔍 Distinguish inferred migrations by timestamp for statements within the same transaction (#362)
❓ Add a WithNoVersionSchemaForRawSQL Go module option to control creating version schemas for raw SQL migrations (#365)
🔎 Add a WithSearchPath Go module option to allow widening the Postgres search path during migration execution (#380)

Thanks to first time contributors 🙏 ❤️ :

• @dolezvo1
• @t-ham752
• @inelpandzic
• @songlim327
• @ryanslade
• @kvch

Changelog

• 3d6ac6d Add WithNoVersionSchemaForRawSQL option (#365)
• bccdf48 Add examples and test Makefile rules (#400)
• 092313a Add roll.WithSearchPath option (#380)
• 27d09ec Add missing doc comments to exported symbols (#392)
• b72ce2a Add pgroll binary to .gitignore (#391)
• 0cab124 Add support for creating partial indexes on tables (#404)
• f994a42 Add support to list views (#358)
• ced761b Allow configuration of backfill batch size (#406)
• 7d88c25 Allow configuration of batch delay (#410)
• 3f792b7 Bump testcontainers package dependencies (#381)
• 080ca66 Change docker-compose port to 5432 (#402)
• bec15d8 Chore: Add migration start message with schema name (#384)
• a7078ad Clarify usage of down field when dropping a column. (#405)
• 7cef8b1 Distinguish inferred migrations by timestamp for statements within the same transaction (#362)
• fca5abd Export OpAlterColumn.IsRenameOnly method (#357)
• 2c895e2 Fix CLI progress updates (#390)
• 718ecc7 Fix crash when connecting to PostgreSQL rc version (#393)
• c4e4ee3 Fix performance regression in previous_version function (#366)
• aa0c6a4 Ignore duplicate inferred migrations having the same timestamp (#369)
• d7f8ec7 Improve backfill performance (#389)
• 84ef318 Infer CREATE/DROP SCHEMA migrations (#359)
• c2635b3 Minor attempt to clear up the behaviour in the documentation (#372)
• edaa25b Remove dependency on go-cmp (#394)
• 681a3eb Remove some lint deprecation warnings (#407)
• 8234b9e Revert "Add support to list views (#358)" (#360)
• 6466b99 Run examples against Postgres 17 (#401)
• eba0332 Run tests against Postgres 17.0 (#387)
• aeb11fd Speed up make examples (#403)
• 00d492a Update docker-compose.yml (#398)
• d2b62f0 Upgrade some dependencies (#395)
• 7561de0 Upgrade to Go 1.23 (#397)

v0.6.0

⚡ Highlights

🪝 Add support for 'hooks' so that users using pgroll as a Go module can customize its behaviour (#290, #335)
🚥 Backoff and retry DDL and DML operations on lock_timeout errors (#353)
🔗 Run all DDL operations before running any DML (data backfills) during migration start (#289)
✖️ Support changing multiple column properties in one alter_column operation (#338)
🖊️ Support rewriting or rejecting user-defined SQL using transformers when using pgroll as a Go module (#329, #332).
🫱 Allow changing column default values (#346)
📛 Support for renaming constraints. (#293)
✅ Allow raw SQL migrations to be run on migration completion instead of start (#280).
🔑 Allow setting the ON DELETE behaviour of foreign key constraints (#297)

Changelog

• 63d5a7f Add CI step to validate examples against the JSON schema (#301)
• 58fa7ae Add SQL onComplete flag, allow sql migration to run with others (#280)
• 0f75224 Add Roll.Schema method (#299)
• 8ed5799 Add WithRawSQLURL to configure an optional URl for raw sql operations (#315)
• 577870c Add rename_constraint operation (#293)
• eda936f Add rename_constraint operation to PgRollOperation JSON schema definition (#300)
• 4fbfdf7 Add a 'set comment' sub-operation to 'alter column' (#344)
• afad3d8 Add a 'set default' sub-operation to 'alter column' (#346)
• cc8c2d3 Add a WithSQLTransformer option to rewrite user-defined SQL in up and down triggers (#329)
• d5d4bea Add complete phase hooks (#335)
• fb05aec Add extra validation for the rename_constraint operation (#303)
• 431b951 Add testcase to ensure new columns can't be used as the the identity column in a backfill. (#298)
• c08ef70 Add two new options to help with replication control (#290)
• 7d8460f Allow rename-only 'alter-column' operations on unbackfillable columns (#341)
• def08e2 Allow setting the ON DELETE behaviour of foreign key constraints (#297)
• 51de6d4 Bump golangci-lint action to v4 (#287)
• 3e49151 Bump github.com/docker/docker from 24.0.7+incompatible to 24.0.9+incompatible (#324)
• 37d2c28 Bump golang.org/x/net from 0.20.0 to 0.23.0 (#343)
• 14a2217 Bump google.golang.org/protobuf from 1.31.0 to 1.33.0 (#319)
• 2932ea3 Disallow unknown fields in migration JSON (#334)
• 4c1bc6a Dont duplicate CHECK constraints and DEFAULTs when altering column type (#349)
• 162bd06 Fix duplicate inferred migrations when dropping columns outside of a migration (#305)
• 0aebb50 Fix error message of column rename (#292)
• 161fde6 Fix hardcoded pgroll schema in state initialization (#284)
• 52fb532 Format schema.json on generate (#294)
• 4d3faeb Make down SQL in rename column operations use the new name of the column (#354)
• 6e4ee68 Make name not required in PgRollMigration (#282)
• b4e3044 Make state initialization concurrency safe (#285)
• f5d09d5 Move example schema validation to its own job (#318)
• 79566b0 Preserve comments on column duplication (#323)
• 547ac3e Preserve foreign key ON DELETE attributes when FK columns are duplicated (#314)
• d4445bb Refactor alter column suboperations (#337)
• 16377ca Relax add_column requirements on pk (#286)
• 937f65c Relax backfill requirements so it works with unique columns (#288)
• 297dd38 Remove BeforeBackfill hook (#312)
• f6cacf7 Respect foreign key ON DELETE in add column and create table operations (#308)
• a444723 Restrict state connection search path (#342)
• 5c1aef2 Retry on lock_timeout errors (#353)
• 944ebed Revert: Add WithRawSQLURL to configure an optional URl for raw sql operations (#333)
• 6334cb4 Rewrite column DEFAULTs using the SQL transformer (#332)
• 0673b1b Rewrite raw SQL operations using a SQL transformer (#330)
• 631d642 Rollback on backfill failure (#317)
• 0746ba6 Separate DDL operations from DML (backfills) on migration start (#289)
• 130f451 Strengthen SQL transformation tests (#331)
• a87fa36 Support create/drop index with uppercase names (#356)
• a4222d8 Support multiple 'alter column' sub operations (#338)
• 4f0a715 Support setting table and column comments to NULL (#345)
• c88c060 Track foreign key ON DELETE setting in the internal schema representation (#311)
• a7d5095 Update banner images and logo colors (#307)
• 099a443 Update documentation for foreign key options (#310)
• cf66d1f Update operation types to set a default of "" for up and down SQL (#325)

v0.5.0

• ❌ Extend the alter_column operation to allow dropping NOT NULL constraints from columns [docs]
• 💬 Add support for adding comments to tables and columns at creation time [docs]
• 🔑 Correctly preserve column constraints when duplicating columns for backfilling [issue]
• 🐛 Ensure that the drop_column operation works for tables and columns with reserved words as names
• 🦺 Update the JSON schema for migration operations to more accurately reflect required and optional fields

Changelog

• f1757f3 Add CI check for up-to-date type generation (#225)
• d00d804 Add JSON schema tests (#263)
• eed4e4e Add JSON schema validation tests for 'sql' operations (#266)
• 70a7c8e Add CHECK constraints to pgroll's internal schema representation (#241)
• 994a1aa Add UNIQUE constraints to pgroll's internal schema representation (#242)
• f798ba5 Add a CI check for unreachable code using deadcode (#251)
• 73c2016 Add a way to set postgres role when executing migrations (#226)
• b9e7819 Add drop NOT NULL operation to remove NOT NULL from a column (#258)
• 9d25159 Add fields to GROUP BY clauses in read_schema function (#255)
• 025a38f Add missing comment to field (#229)
• 61cc53a Add support for creating tables and columns with comments (#224)
• dde27eb Add test: check column DEFAULTs preserved on set NOT NULL (#235)
• 66ccf91 Add unique & FK constraints info to the schema (#218)
• 83b5d76 Allow to disable version schemas (#233)
• 4450862 Bump actions/checkout to v4 (#272)
• 72cc155 Bump github.com/containerd/containerd from 1.7.3 to 1.7.11 (#217)
• c5a3dbd Bump github.com/opencontainers/runc from 1.1.5 to 1.1.12 (#260)
• 4b124b0 Change Complete method signature (#231)
• 355ac20 Correctly quote table and column names in the drop column operation (#254)
• 14e3d1b Ensure constraint existence as part of validation for the drop constraint operation (#252)
• 7e65cda Fix inferred migrations format (#259)
• c10dabf Fix pgroll migration definition (#216)
• bb79eec Fix raw SQL migrations spec in schema.json (#264)
• 7a38e1f Identify duplicated foreign key constraints by prefix (#245)
• b2e93a9 Improve MustNotInsert test assertions (#243)
• 7b5fafd Improve constraint assertions (#240)
• 908485f Include key columns for an index in the internal schema representation (#248)
• c95bc78 Parameterize schema in which migration tests run via PGROLL_TEST_SCHEMA env var (#276)
• 68c5fbf Preserve CHECK constraints on duplication (#244)
• 7c1167a Preserve NOT NULL attributes on column duplication (#246)
• 9e9eeaf Preserve UNIQUE constraints on column duplication (#250)
• ddb91d1 Preserve column properties on add CHECK constraint operation (#236)
• 4928cf1 Preserve column properties on add FOREIGN KEY constraint operation (#238)
• 7b7caa0 Preserve column properties on add UNIQUE constraint operation (#237)
• 04de471 Preserve column properties on drop constraint operation (#253)
• d803689 Preserve default on column duplication when changing column type (#234)
• b81c42c Preserve foreign key constraints on columns duplicated for backfilling (#230)
• fc7370f Preserve foreign key constraints when changing a column's type (#232)
• 4bee7ac Record index uniqueness in pgroll's internal schema representation (#247)
• 7fd8c7c Remove RAISE NOTICE statements (#256)
• 82b7937 Remove unused function (#228)
• 7404ddf Run go-jsonschema container with --rm (#267)
• 889946b Run migration tests in a non-public schema as part of CI (#279)
• 5686ddc Strengthen assertions for tests that check preservation of UNIQUE constraints (#277)
• e906da7 Strip schema from index names in internal schema representation (#278)
• a5cf473 Take go version from go mod version in buid workflow (#271)
• d06a0af Update JSON schema to specify that not all Column fields are required (#265)
• 5a6a83a Update schema.json to correctly describe the 'alter column' operation (#261)
• 9586b44 Update state command to avoid returning nulls (#274)
• 0cc62c1 docs/readme: broken code syntax (#270)

v0.4.4

• 🐳 pgroll docker images are now available at https://github.com/xataio/pgroll/pkgs/container/pgroll
• 🐛 Fix backfilling for tables with character-type primary keys

Changelog

• 3924fd0 Fix backfills for tables with character-type valued primary keys (#213)
• 788bac6 Add JSON schema struct generation (#210)
• 5812acc Support build docker image (#209)

v0.4.1

🐛 The pgroll status command now correctly defaults to the public schema and respects the --schema flag.

Changelog

• 763dabc Move logic for the status command into the state and migrations packages (#205)
• 323fc5f Quote table name in OpSetReplicaIdentity (#206)
• 34bbb24 Show the status for the correct schema (#204)

v0.4.0

• 🔒 Versioned views are now created with security_invoker=true on Postgres >=15 in order to respect row level security policies on the underlying tables. See the README section for more details.
• 🔁 Added a new SET REPLICA IDENTITY operation. See the docs for details.
• 🐛 Old version schemas are now removed correctly when working in schemas other than public.
• 🐛 Old version schemas are now removed correctly when a DDL change was captured in between migrations.
• 🔒 Made some internal functions in the pgroll schema run as security_definer.
• 🟢 Ping after establishing the postgres connection for cleaner error messages on connection failures.
• 🚤 Made some performance improvements in the read_schema internal function to better handle databases with many schemas.

Changelog

• 536295b Add SECURITY DEFINER to internal functions (#191)
• 4f4d549 Add a 'set replica identity' operation (#201)
• 7f20c15 Add section to README about RLS in Postgres 14 (#202)
• bd53d10 Create versioned views WITH (security_invoker = true) (#189)
• d486aab Fix previous_version function to work with non-public schema (#190)
• c029d5e Fix previous version detection in the presence of inferred DDL migrations (#197)
• 954746b Fix primary keys retrieval query (#198)
• 2429a6b Ping after creating connection (#195)
• edc78ac Stop quoting column default values (#200)

v0.3.0

• 🍺 pgroll can now be installed using brew. See the install section in README.md.
• 🔴 We've made a breaking change to the format of "alter_column" migrations that set NOT NULL constraints: the not_null field has been renamed to nullable. A migration that used to look like this:

{
  "name": "16_set_nullable",
  "operations": [
    {
      "alter_column": {
        "table": "reviews",
        "column": "review",
        "not_null": true,
        "up": "(SELECT CASE WHEN review IS NULL THEN product || ' is good' ELSE review END)",
        "down": "review"
      }
    }
  ]
}

should now be written as:

{
  "name": "16_set_nullable",
  "operations": [
    {
      "alter_column": {
        "table": "reviews",
        "column": "review",
        "nullable": false,  <-- field name has changed and logic inverted
        "up": "(SELECT CASE WHEN review IS NULL THEN product || ' is good' ELSE review END)",
        "down": "review"
      }
    }
  ]
}

❤️ Thanks to first-time contributors @sFritsch09 and @pucke-uninow for their work on this release.

Changelog

• f15931e Add.env to .gitignore (#178)
• 0792d6b Bump golang.org/x/net from 0.10.0 to 0.17.0 (#185)
• 0fb46f2 Change signature of migrations.ReadMigrationFile (#177)
• e87c639 Feat/#175 homebrew release (#180)
• 57ea814 Mark releases as draft (#186)
• 8e49d69 Remove unnecessary env section from release job (#188)
• a3a40f7 Update goreleaser release job (#187)
• 80adbae fix(#132): consistent naming for nullable columns in migration files (#181)

v0.2.1

• 🔗 Disable CGO: pgroll binaries are now statically linked (#176)

v0.2.0

New features:

• 🔓 Backfill in batches to avoid locking out updates to modified tables during the start phase. (#137)
• ⌛ Set Postgres' lock_timeout for pgroll DDL operations. (#155)
• 🛼 A --version flag to report the version of the pgroll CLI (#148)
• 🐍 Allow pgroll 's top-level CLI flags to be set via environment variables (#152)