Squashing Schema Migrations

Questions & Help Questions
26

We have come up with the following strategy to deal with squashing migrations on a periodic basis.

  1. Run mix ecto.dump or the equivalent database command
  2. Rename the generated structure.sql file 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
  3. Remove the old migrations (celebrate if you wish)
  4. 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 psql CLI 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