ecto_extract_migrations

Mix task to generate Ecto migrations from a Postgres schema SQL file.

This lets you take an existing project and move it into Elixir with a proper development workflow.

Usage

1. Generate a schema-only dump of the database to SQL:

   pg_dump --schema-only --no-owner postgres://dbuser:dbpassword@localhost/dbname > dbname.schema.sql

2. Generate migrations from the SQL file:

   mix ecto.extract.migrations --sql-file dbname.schema.sql

   or, from outside the target project:

   mix ecto.extract.migrations --sql-file dbname.schema.sql --repo "MyProject.Repo" --migrations-path ../myproject/priv/repo/migrations

3. Create a test database, run migrations to create the schema, then export it and verify that it matches the original database:

   createuser --encrypted --pwprompt dbuser
   dropdb dbname_migrations
   createdb -Odbuser -Eutf8 dbname_migrations
   
   mix ecto.migrate --log-sql
   
   pg_dump --schema-only --no-owner postgres://dbuser@localhost/dbname_migrations > dbname_migrations.sql
   
   cat dbname.schema.sql | grep -v -E '^--|^$' > old.sql
   cat dbname_migrations.sql | grep -v -E '^--|^$' > new.sql
   diff -wu old.sql new.sql

Details

This was written to migrate a legacy database with hundreds of tables and objects.

The parser uses NimbleParsec, and is based on the SQL grammar, so it is precise (unlike regex) and reasonably complete. It doesn't support every esoteric option, just what we needed, but that was quite a lot. Patches are welcome.

Supports:

• ALTER SEQUENCE
• ALTER TABLE
• CREATE EXTENSION
• CREATE FUNCTION
• CREATE INDEX
• CREATE SCHEMA
• CREATE SEQUENCE
• CREATE TABLE
• CREATE TRIGGER
• CREATE TYPE
• CREATE VIEW

Installation

Add ecto_extract_migrations to your list of dependencies in mix.exs:

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

Resources

Here are some useful resources for NimbleParsec:

• https://stefan.lapers.be/posts/elixir-writing-an-expression-parser-with-nimble-parsec/
• https://github.com/slapers/ex_sel

Alternatives

• ecto_generator generates Ecto schemas by querying the database information schema
• ex_abnf generates a parser based on an ABNF grammar.