I have been looking through the ecto docs to see how to generate the sql between two migrations and haven’t found anything. Is this really something that doesn’t exist?
1 Like
The migration API handles that itself, no SQL is really generated except the individual commands to perform the diff to the server. Are you wanting a dump of those SQL commands that actually ‘perform’ the diff?
1 Like
Yes I was looking for this. For example with entity framework from the .net world you can specify you want the script to upgrade a database that is at migration b up to migration c.
In other words without running the mix task on a production server how do people upgrade their databases?
1 Like
This has been a long debated topic. There are hacks to do it but there is no really well documented and ‘accepted’ method that I’ve seen yet, at least not one that has become a well approved library (which we badly need!).
However, the usual way is to manually call the migration stuff in Ecto on program load on production to upgrade based on the scripts, it is a bit messy currently and I’m hoping someone has made a good library to do this and will introduce it in this thread. ^.^
What ‘i’ do is run the mix task via having it remotely connect to the prod server over ssh, also very messy, and not quite as safe as I’d prefer (the program and database are desynched until the few seconds later that I can upgrade the server).
1 Like
OK I feared this might be case. I only have one small app in production at the moment so for now I might just get my local prod database at the same migration as my production server and then use a pgdiff tool to generate a diff script against my dev database.
Would be really cool to have a mix task for this, if I get some spare time at some point I will try and look into this.
Any pointers as to where I might start? Maybe with the hack you mentioned?
1 Like
Distillery has a great documentation on how you can easily run migrations without mix in a release.
https://hexdocs.pm/distillery/running-migrations.html#content
Adding support for dumping generated SQL to a file, shouldn’t be that difficult, If somebody wants to contribute this feature, it will probably be accepted. I’m also pretty sure it would be possible to have this as an external package, but I haven’t investigated this in depth.
4 Likes
I hadn’t seen this before and on a quick scan of the article it looks like it will achieve what I needed to do with raw sql. I was struggling because everywhere I had been reading they were all pushing their source code up to production so they could manually run the mix task.
I am about to move from exrm to distillery so will give it a try, thanks.
1 Like
This technique should work with exrm as well. The only thing lacking would be the custom command, created at the end, so instead of bin/app migrate it would be the full bin/app command Elixir.MyApp.ReleaseTasks seed.
3 Likes
Ah cool thanks for the heads up!
1 Like
Ecto supports mix ecto.load and mix ecto.dump that loads and dumps the database structure to a SQL file.
Furthermore, In Ecto master, and possibly on 2.1, there is a --log-sql file that logs the SQL used in migrations.
5 Likes
