Difficulties integrating into project

[ericwooley]

Hello!

I love the project and the goal.

However, as it stands, it's pretty hard to integrate into a project with multiple branches and developers.

Presumably, each developer, working on different branches would have different migration files. Something like this

migrations
├── create_book.json             # dev 1 on branch book which was branched off main
├── create_checkouts.json.   # dev 2 on branch checkouts which was branched off of book
└── create_library.json          # dev 3 on branch library which was branched off main

When they all get merged it's hard to know whats been merged, and in what order to run them.

i've resorted to scripting the creation of migrations so that they have time stamps, so they run in order of the time the developer created them, which seems to work ok, but then the migration script on pull down needs to figure out which migrations to run, and which ones not to run for this environment, and there doesn't seem to be a way to check that using the CLI. Currently I'm having to resort to querying the migrations table myself to see what needs to be run.

Another option i considered was storing individual operations in a folder, based on release, but this also gets hard to manage as different releases might happen with different features etc.... Which would require moving files around as part of the release process.

Guidance on this would be helpful, or possibly allowing a status query on individual migrations files, so that I can check if each migration has already been run, and skip it if so.

I'm using this for a typical SASS deployment process for now, so most of the time I will just be running pgroll start <file> --complete. But I like the idea of using this for gnarlier future migrations as well, when necessary.

[exekias]

Thank you for opening this one @ericwooley!

We recently discussed this, and this is a possible approach we could take:

• Migration files must be in a well-known (configurable) folder, ie pgroll
• Migration files should be sorted by name, ie by leveraging timestamps (as you do), or numbering (001_, etc)
• We introduce a pgroll migrate subcommand, that will apply all pending migrations to the database, and leave the last one started (without complete). This command will auto-complete any previous migration until it reaches the last one in the pgroll folder. You would run this command from your CI to ensure the DB has all the migrations in place, with the last one started, so previous version is still available for previous application instances.
• We introduce a pgroll latest_version_schema (better name pending?) to ease the task of configuring clients. This command will output the schema to be used in the search_path to access the latest version (using local files in pgroll folder as the source of truth).

I believe these 2 new commands would ease local development and continuous deployment.

Please let us know what you think, we are really interested in your feedback!:

[ericwooley]

@exekias

Those sound like great improvements. I believe they would relieve the issues I'm running into.

Thanks for getting back!

Do you want me to leave the issue open? Looks like it's assigned and part of a milestone

[andrew-farries]

Yes, let's leave this issue open to add the migrate and latest_version_schema (or whatever we end up calling them) commands.