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)

v0.1.0

Initial Release

pgroll is an open source command-line tool that offers safe and un-doable schema migrations for PostgreSQL by serving multiple schema versions simultaneously. It takes care of the complex migration operations to ensure that client applications continue working while the database schema is being updated. This includes ensuring changes are applied without locking the database, and that both old and new schema versions work simultaneously (even when breaking changes are being made!). This removes risks related to schema migrations, and greatly simplifies client application rollout, also allowing for instant rollbacks.

See the README for details and usage instructions.