Hi, we are long time PostgreSql users and in our apps we have an “update script” in charge of upgrading the database structure.
The script is a big “Do…begin…end” containing queries to check if a table, column, constraint, etc exist. If not it creates or remove the needed object. This approach works perfectly since many years.
Now with Ecto there are migrations, quite similar to what we’ve been doing with the update script, but using a different syntax.
What do you recommend for us, use Ecto without migrations (keep updating the DDL by hand) or dedicate some time to learn Ecto Migrations?.
Interesting question! The advantage of Ecto migrations is that they can automatically generate rollback scripts most of the time. So you write your migration as:
def change do
alter table(:foobar) do
add :new_column, :string
end
end
and then you can migrate the DB up with mix ecto.migrate and migrate it down (rollback) with mix ecto.rollback. If you’re using down migrations, it should probably cut the amount of code in half. To be fair, sometimes you need to write both up and down functions - especially when changing NOT NULL constraints. Plus you get schema version tracking for free. Phoenix will take advantage of that and warn you if you have pending migrations.
The nice part is that you can always drop down to SQL with execute("<up statements>", "<down statements>"). So if you would decide to move to Ecto migrations, you can create the initial migration and paste your whole update script into an execute() call and then use the Ecto’s DSL for newer migrations.
I would say that the biggest value of migrations has little to do with the syntax, in fact even if you just made your migrations with big execute blocks that had SQL in them it would be an improvement.
Suppose you have two developers working on two different branches, and they each need to make changes to the database. With a single database update script, you have a merge conflict that needs to be sorted out, even if they are doing totally unrelated things. With migrations, each developer will have generated a different migration file, and merging will basically work just fine.
Then you have some third developer who has been on vacation for 3 weeks, and is now coming back and needs to get their database up to date. With migrations, they just git pull, run all the migrations and voila, their database was deterministically updated in the right sequence. With a changing update script, they’d need to check out the code at different stages to run the updates that were written at different points in time.
The syntax of Ecto migrations has certain benefits, but honestly it’s the migration pattern itself that to me holds the most value.
Agreed but I suppose people periodically reevaluate and are wondering whether external dedicated tools might be a better fit (since they too use the up/down and version-control-friendly idioms). I know I did ask myself that on occasion; and to be fair to the OSS community, there have been a few interesting projects in the area posted on HN during the last year.
I think there are absolutely use case for db migration tools out side of ecto. I don’t get the impression though that @leonardorame is using one of those tools, rather it sounds like they have a bespoke upgrade script that is changed over time.
