Add a new command to get latest schema from migration files instead of the db

[ludat]

Hello, I'm integrating pgroll with my app (it's open source so it's here and I found something that seems odd.

So with normal migrations I'd like to make sure that my app doesn't start unless the migrations for it has run (at least started or possibly completed). But I don't see that with pgroll.

For example rails or phoenix both refuse to start if the migrations for the project have not run, so I had to implement a couple of things by hand:

1. Find the current last migration from the filesystem (which is something pgroll already does internally)
2. Ensure that at least that migration has started to run or has been completed successfully

The docs here suggest hardcoding the current schema but I'd like to automate that since the app has that information in the fs.

My proposal are two commands (maybe one with a flag):

• pgroll local current-migration --migrations-dir ./migrations: kind of like pgroll latest schema to get the schema that the app expects (not necessarily the one that shows up on the db)
• pgroll up-to-date --url postgresql://... --migrations-dir ./migrations: I'd like this command to read from the fs the migrations that exist, read from the db the migrations that have been applied and check if the db has at least all the migrations from the fs run (or at least started)

I could give it a go at implementing those features since it looks like it's something that almost happening but not exposed

[gulcin]

Hello @ludat thank you for sharing your use case with us, we really appreciate the feedback!

As you mentioned, the building blocks for this are already available:

• You can use pgroll latest schema --local your-migration-directory/ to get the latest schema version from a local migration directory. You can also run pgroll latest migration --local to print the latest migration name from a local directory. See the docs here and here. If you want to get the latest schema/migration from the database, just omit the --local flag.
• Then we have the pgroll status command that prints the latest applied migration and status (Complete/In Progress/No migrations) for a given schema.

There isn't a single command for comparing local migrations to database applied migrations but for now you can script and compare the outputs of pgroll latest schema --local and pgroll status commands.

We will consider your suggestion to have a single command like up-to-date and keep you posted in case we'd prioritize this.

Thanks!

[ludat]

Thanks! I didn't notice the --local flag.

I've given this a bit more thought and I think the new command should be something like "is my schema available?"

So I'd imagine that:

schema in fs	schema in db	status	result	explanation
2	3	running	ok	next migration is running but my schema still exists
2	3	complete	fail	next migration has already run so my schema is gone
2	2	running	ok	current migration is running so my schema exists
2	2	complete	ok	current migration is running so my schema exists
2	1	running	fail	previous migration is running so my schema doesn't exist yet
2	1	complete	fail	previous migration is complete so my schema doesn't exist yet

[gulcin]

Thanks @ludat for putting the details together in a table, really helpful.

We'll keep this issue updated when we can work on this.