Skip to content

Ensure lex ordering of inferred migration names with respect to pgroll migrations #899

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 2 commits into from
Jun 13, 2025

Conversation

andrew-farries
Copy link
Collaborator

@andrew-farries andrew-farries commented Jun 13, 2025

Ensure that inferred migrations are named appropriately with respect to user created migrations to ensure correct lexicographical ordering of migration files on disk.

Reproduction

Before this PR, the problem with inferred migration naming was reproducible as follows:

Create the following migration as 01_first_migration.yaml:

operations:
  - sql:
      up: SELECT 1

Apply the migration with pgroll start --complete .

On the target database, run some DDL:

CREATE TABLE foo(a int PRIMARY KEY);

Create another migration, 02_second_migration.yaml:

operations:
  - sql:
      up: SELECT 1

Apply the migration with pgroll start --complete .

Now pull the migration history into a temporary directory:

$ pgroll pull tmp/

The migration history is not in the correct lexicographical order; the inferred migration is ordered after the user-created migrations:

├──  01_first_migration.yaml
├──  02_second_migration.yaml
└──  sql_1fb6e25eba9678.yaml

New behaviour

This PR fixes the problem by naming inferred migrations so that they appear in the correct lexicographical order when pulled to disk with pgroll pull:

The same steps as in the reproduction now result in these files on disk:

├──  01_first_migration.yaml
├──  01_first_migration_20250613120930931268.yaml
└──  02_second_migration.yaml

The inferred migration is now lex-ordered correctly between the two user migrations by appending a fixed width timestamp suffix to the previous migration name.

The inferred migration has its version_schema field set:

cat 01_first_migration_20250613120930931268.yaml:

version_schema: sql_e842123c
operations:
- sql:
    up: create table foo(id serial primary key)

This is to ensure that the version schema for the inferred migration does not risk exceeding the Postgres 63 character limit for schema names.


Fixes #882

Name inferred migrations according to the pattern:

* For an initial inferred migration (ie the inferred migration is the
  first in the history), use:
  `000_initial_<timestamp>`
* For inferred migrations following a non-inferred migration, use:
  `<previous_migration>_<timestamp>`

Regardless of how the migration is named, it will have its
`versionSchema` field set to `sql_<random-suffix`>.
@github-actions github-actions bot temporarily deployed to Docs Preview June 13, 2025 12:14 Inactive
Copy link

Merging this branch will increase overall coverage

Impacted Packages Coverage Δ 🤖
github.com/xataio/pgroll/pkg/state 63.81% (+10.07%) 🎉

Coverage by file

Changed unit test files

  • github.com/xataio/pgroll/pkg/state/state_test.go

@andrew-farries andrew-farries marked this pull request as ready for review June 13, 2025 12:27
@andrew-farries andrew-farries requested a review from kvch June 13, 2025 12:27
Copy link
Contributor

@kvch kvch left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Are we going to provide a migration or upgrade path for people to update their pgroll.migrations table?

@andrew-farries
Copy link
Collaborator Author

Are we going to provide a migration or upgrade path for people to update their pgroll.migrations table?

No, I wasn't planning to do that - or at least not as part of #882 anyway.

@andrew-farries andrew-farries merged commit 07d8d08 into main Jun 13, 2025
30 checks passed
@andrew-farries andrew-farries deleted the inferred-migration-naming branch June 13, 2025 13:07
andrew-farries added a commit that referenced this pull request Aug 12, 2025
Remove the `--with-prefixes` flag from the `pull` command.

The `--with-prefixes` flag was a workaround for inferred migrations not
being named in such a way that they would appear in the correct order
when pulled. As of #899, inferred
migrations are named correctly so that `pgroll pull` will put all
migrations, including inferred migrations, in the correct order on disk.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging this pull request may close these issues.

Fix ordering of inferred migrations with respect to pgroll migrations
2 participants