search

djrobstep / migra

Like diff but for PostgreSQL schemas
https://databaseci.com/docs/migra
The Unlicense
2.9k stars 121 forks source link

Support a *.sql file as the source schema #131

Open ryanberckmans opened 4 years ago

ryanberckmans commented 4 years ago

Could migra support a *.sql file as the source schema?

My thought is that I'd like to maintain a schema.sql, which is the authoritative schema for my DB. Then, I'd like to run migra postgresql:///prod-db schema.sql to generate the statements which must be run for DB prod-db to match schema.sql.

ryanberckmans commented 4 years ago

A way to implement this might be a wrapper that takes schema.sql as input, creates an ephermal postgres instance via docker, applies schema.sql to it, diffs that ephemeral instance against postgresql:///prod-db, and then destroys the ephemeral instance.

Perhaps this wrapper shell script could be added to this repo for reference.

djrobstep commented 4 years ago

This is definitely the roadmap. In the short term, if you're comfortable with python you can script this up fairly straightforwardly yourself (there's some relevant code in the dev usage section of the docs). These temporary databases use a locally running postgres instance but it would be great to support docker too.

And I agree if this was out of the box it would be great as the use case you describe is very common. There's some complexity to work through with making sure the user has the right container image downloaded and available to use etc, but worth it for the convenience and easy cleanup that docker containers provide.

ioistired commented 4 years ago

Related: https://github.com/djrobstep/migra/issues/100

dkourilov commented 4 years ago

A good example of using static schema.sql as a migra input is https://github.com/bikeshedder/tusker

bikeshedder commented 4 years ago

I just released Tusker 0.2.0 with the help of @Swizz that supports diffing a schema file, migration files and an actual database. It uses migra for all diffing purposes but provides a different user interface.

tzaffi commented 3 years ago

Could migra support a *.sql file as the source schema?

My thought is that I'd like to maintain a schema.sql, which is the authoritative schema for my DB. Then, I'd like to run migra postgresql:///prod-db schema.sql to generate the statements which must be run for DB prod-db to match schema.sql.

Hi @ryanberckmans. Glad to see we're touring around in the same repos. Great issue!

mariusa commented 3 years ago

Instead of only 1 schema.sql file, could this also work with *.sql ? We have schema split into multiple sql files. When run in alphabetical order, dependencies are also met (eg 10-tables.sql, 12-more-tables.sql, 20-views.sql ...)

ryanberckmans commented 3 years ago

Social comment:

@tzaffi hey, Zeph! Great to see you out in the wild, haha. I hope things are well!

It looks like I missed the ping in December and saw it now, thanks to mariusa.

bikeshedder commented 3 years ago

Tusker supports globbing patterns as schema input since version 0.3: https://github.com/bikeshedder/tusker/blob/master/CHANGELOG.md

It's a very thin wrapper over migra and provides some convenience features.

maximsmol commented 2 years ago

Also mentioned here: https://github.com/djrobstep/migra/issues/25#issuecomment-449711551