Excellent Migrations

Detect potentially dangerous or destructive operations in your database migrations.

Installation

The package can be installed by adding excellent_migrations to your list of dependencies in mix.exs:

def deps do
  [
    {:excellent_migrations, "~> 0.1.0"}
  ]
end

How It Works

This tool analyzes code (AST) of migration files. You don't have to edit or include anything in your migration files, except for ocassionally adding module attribute @safety_assured.

How to use it

There are multiple ways to integrate with Excellent Migrations.

mix task

mix excellent_migrations.check_safety

This mix task analyzes migrations and logs a warning for each danger detected.

migration task

mix excellent_migrations.migrate

Running this task will first analyze migrations. If no dangers are detected it will proceed and run mix ecto.migrate. If there are any, it will log errors and stop.

Credo check

Excellent Migrations provide custom check for Credo. Add ExcellentMigrations.CredoCheck.MigrationsSafety to your .credo file. Example warnings:

  Warnings - please take a look                                                                                                                                             
┃ 
┃ [W] ↗ Raw SQL used
┃       apps/cookbook/priv/repo/migrations/20211024133700_create_recipes.exs:13 #(Cookbook.Repo.Migrations.CreateRecipes.up)
┃ [W] ↗ Index added not concurrently
┃       apps/cookbook/priv/repo/migrations/20211024133705_create_index_on_veggies.exs:37 #(Cookbook.Repo.Migrations.CreateIndexOnVeggies.up)

Code

You can also use it in code. To do so, you need to get AST of your migration, e.g. via Code.string_to_quoted/2 and pass it to ExcellentMigrations.DangersDetector.detect_dangers(ast). It will return keyword list containing danger types and lines where they were detected.

Checks

Potentially dangerous operations:

Postgres-specific checks:

Best practices:

You can also disable specific checks.

Removing a column

Example

defmodule Cookbook.RemoveSizeFromDumplings do
  def change do
    alter table(:dumplings) do
      remove :size, :string
    end
  end
end

Adding a column with a default value

Example

defmodule Cookbook.AddTasteToDumplingsWithDefault do
  def change do
    alter table(:dumplings) do
      add(:taste, :string, default: "sweet")
    end
  end
end

Backfilling data

Example

defmodule Cookbook.BackfillRecords do
  def change do
    Repo.insert!(%Dumpling{taste: "umami"})
  end
end

Changing the type of a column

Example

defmodule Cookbook.ChangeColumnSizeTypeToInteger do
  def change do
    alter table(:dumplings) do
      modify(:size, :integer)
    end
  end
end

Renaming a column

Example

defmodule Cookbook.RenameFillingToStuffing do
  def change do
    rename table(:dumplings), :filling, to: :stuffing
  end
end

Renaming a table

Example

defmodule Cookbook.RenameDumplingsToNoodles do
  def change do
    rename(table(:dumplings), to: table("noodles"))
  end
end

Adding a check constraint

Example

defmodule Cookbook.CreatePriceConstraint do
  def change do
    create constraint("dumplings", :price_must_be_positive, check: "price > 0")
  end
end

Setting NOT NULL on an existing column

Example

defmodule Cookbook.AddNotNullOnShape do
  def change do
    alter table(:dumplings) do
      modify :shape, :integer, null: true
    end
  end
end

Executing SQL directly

Example

defmodule Cookbook.CreateIndexOnDumplings do
  def up do
    execute("CREATE INDEX dumplings_geog ON dumplings using GIST(Geography(geom));")
  end

  def down do
    execute("DROP INDEX dumplings_geog;")
  end
end

Adding an index non-concurrently

Example

defmodule Cookbook.AddIndex do
  def change do
    create index(:dumplings, [:recipe_id, :flour_id])
  end
end

Adding a reference

Example

defmodule Cookbook.AddReferenceToIngredient do
  def change do
    alter table(:recipes) do
      modify :ingredient_id, references(:ingredients)
    end
  end
end

Adding a json column

defmodule Cookbook.AddDetailsJson do
  def change do
    add :details, :json, default: "{}"
  end
end

Keeping non-unique indexes to three columns or less

defmodule Cookbook.AddIndexOnIngredients do
  def change do
    alter table(:dumplings) do
      create index(:ingredients, [:a, :b, :c, :d], concurrently: true)
    end
  end
end

Assuring safety

To mark an operation in a migration as safe list it in @safety_assured attribute. It will be ignored during analysis.

defmodule Cookbook.AddTasteToDumplingsWithDefault do
  @safety_assured [:column_added_with_default]

  def change do
    alter table(:dumplings) do
      add(:taste, :string, default: "sweet")
    end

    create index(:dumplings, [:recipe_id, :flour_id])
  end
end

You can also mark all operations as safe in a given migration by adding @safety_assured :all

defmodule Cookbook.BackfillRecords do
  @safety_assured :all

  def change do
    Repo.insert!(%Dumpling{taste: "umami"})
  end
end

Possible operation types are:

Disable checks

Ignore specific dangers for all migraion checks with:

config :excellent_migrations, skip_checks: [:raw_sql_executed, :not_null_added]

Existing migrations

To skip analyzing migrations that were created before adding this package, set timestamp from the last migration in start_after in config:

config :excellent_migrations, start_after: "20191026080101"

Similar tools

Contributing

Everyone is encouraged to help improve this project. Here are a few ways you can help: