Whenever we do a major release we delete all the migration files and dump the database structure to a file (e.g. structure.sql) and use mix ecto.load to load the structure into the database. That way we have a history of the migrations, albeit through git, and a clean migrations directory. 
timothy
0
fireproofsocks
I like that idea.
For the record, how do you reference the path to that file? In the past I’ve gotten tripped up referencing paths in a way that’s compatible with releases…
0
timothy
We only keep one file, which is sufficient enough for us, in the default directory (priv/YOUR_REPO/structure.sql).
0
thbar
I was researching this topic again today, because we have 166 migration files today, and sometimes it leads to debugging sessions which are a bit more complicated than necessary (e.g. a search vector updated in database via some SQL function, called via a trigger, etc).
I stumbled upon this which seems nice:
https://github.com/prosapient/ecto_squash
Curious to know if anyone tried it already!
2
fireproofsocks
We have come up with the following strategy to deal with squashing migrations on a periodic basis.
- Run
mix ecto.dumpor the equivalent database command - Rename the generated
structure.sqlfile to something the better communicates that this represents a snapshot of the database structure at a certain point in time, e.g.snapshot-2023-11-10.sql - Remove the old migrations (celebrate if you wish)
- Create a new migration that references the dump file from above. This helps avoid frustration when developers unfamiliar with the process have to run yet-another-mix-command to bootstrap the database. They may run into problems getting the
psqlCLI installed, for example. You can write a simple migration that parses the dump file like this:
defmodule MyApp.LoadStructureSqlMigration do
use Ecto.Migration
require Logger
def change do
:core
|> :code.priv_dir()
|> Path.join("snapshot-2023-11-10.sql")
|> File.read!()
|> String.split(";\n")
|> Enum.each(fn statement ->
execute(statement)
end)
end
end
8
fireproofsocks
IIRC there are errors if you try to execute more than one statement at a time – that’s why we ended up with the split on ; thing.
Good reminder though: some dump files may not be so easily parsed.
1
