automigrate

Database auto-migration tool for Clojure. Define models as plain EDN data structures and create database schema migrations automatically based on changes to the models.

Features

• define db schema as models in EDN declaratively;
• automatically migrate db schema based on model changes;
• view raw SQL for any migration;
• ability to add raw SQL migration for specific cases or data migrations;
• migrate to any migration in forward and backward [:construction: under development] directions;
• use with PostgreSQL [:construction: others are under development];

State

Project is in alpha state till the 1.0.0 version and is not yet ready for production use. Breaking changes are possible.

Usage

Installation

tools.deps -X option

A config for development environment could look like the following:

deps.edn

{...
 :aliases {...
           :migrations {:extra-deps {net.clojars.abogoyavlensky/automigrate {:mvn/version "<LATEST VERSION>"}
                                     org.postgresql/postgresql {:mvn/version "42.3.1"}}
                        :ns-default automigrate.core
                        :exec-args {:models-file "resources/db/models.edn"
                                    :migrations-dir "resources/db/migrations"
                                    :jdbc-url "jdbc:postgresql://localhost:5432/mydb?user=myuser&password=secret"}}}}

:information_source: You can move postgres driver to project's :deps section. You can choose any paths you want for :models-file and :migrations-dir.

Then you could use it as:

clojure -X:migrations make

tools.deps -T option

Alternatively you can use the following alias with -T option (from clojure tools cli version >= 1.10.3.933). The difference is that the project's :deps is not included for running migrations.

deps.edn

{...
 :aliases {...
           :migrations {:deps {net.clojars.abogoyavlensky/automigrate {:mvn/version "<LATEST VERSION>"}
                               org.postgresql/postgresql {:mvn/version "42.3.1"}}
                        :ns-default automigrate.core
                        :exec-args {:models-file "resources/db/models.edn"
                                    :migrations-dir "resources/db/migrations"
                                    :jdbc-url "jdbc:postgresql://localhost:5432/mydb?user=myuser&password=secret"}}}}

You can then use it as:

clojure -T:migrations make

Leiningen

[:construction: Leiningen support is under development.]

Getting started

After configuration, you are able to create models.edn file with first model, make migration for it and migrate db schema. Paths for migrations and models can be chosen as you want. A model is the representation of a database table which is described in EDN structure. Let's do it step by step.

Add model

resources/db/models.edn

{:book [[:id :serial {:unique true
                      :primary-key true}]
        [:name [:varchar 256] {:null false}]
        [:description :text]]}

Make migration

$ clojure -X:migrations make
Created migration: resources/db/migrations/0001_auto_create_table_book.edn
Actions:
  - create table book

Migration can contain multiple actions. Every action is converted to a SQL query. The migration at resources/db/migrations/0001_auto_create_table_book.edn will look like:

({:action :create-table,
  :model-name :book,
  :fields
  {:id {:unique true, :primary-key true, :type :serial},
   :name {:null false, :type [:varchar 256]},
   :description {:type :text}}})

Migrate

Existing migrations will be applied one by one in order of migration number:

$ clojure -X:migrations migrate
Migrating: 0001_auto_create_table_book...
Successfully migrated: 0001_auto_create_table_book

That's it. In the database you can see a newly created table called book with defined columns and one entry in table automigrate_migrations with new migration 0001_auto_create_table_book.

List and explain migrations

To view status of existing migrations you can run:

$ clojure -X:migrations list
Existing migrations:

[✓] 0001_auto_create_table_book.edn

To view raw SQL for existing migration you can run command explain with appropriate number:

$ clojure -X:migrations explain :number 1
SQL for migration 0001_auto_create_table_book.edn:

BEGIN;
CREATE TABLE book (id SERIAL UNIQUE PRIMARY KEY, name VARCHAR(256) NOT NULL, description TEXT);
COMMIT;

All SQL queries of the migration are wrapped by a transaction.

:information_source: For a slightly more complex example please check models.edn and README.md from the examples dir of this repo.

Documentation

Model definition

Models are represented as a map with the model name as a keyword key and the value describing the model itself. A model's definition could be a vector of vectors in the simple case of just defining fields. As we saw in the previous example:

{:book [[:id :serial {:unique true
                      :primary-key true}]
        [:name [:varchar 256] {:null false}]
        [:description :text]]}

Or it could be a map with two keys :fields and (optional) :indexes. Each of these is also a vector of vectors. The same model from above could be described as a map:

{:book {:fields [[:id :serial {:unique true
                               :primary-key true}]
                [:name [:varchar 256] {:null false}]
                [:description :text]]}}

Fields

Each field is a vector of three elements: [:field-name :field-type {:some-option :option-value}]. The third element is optional, but name and type are required.

The first element is the name of a field and must be a keyword.

Field types

The second element could be a keyword or a vector of keyword and integer. Available field types are presented in the following table:

:information_source: There are fixed field types because automigrate validates type of field and default value to have errors as early as possible before running migration against database.

Field options

Options value is a map where key is the name of the option and value is the available option value. Available options are presented in the table below:

Indexes

Each index is a vector of three elements: [:name-of-index :type-of-index {:fields [:field-from-model-to-index] :unique boolean?}] Name, type and :fields in options are required.

The first element is the name of an index and must be a keyword.

Index types

The second element is an index type and must be a keyword of available index types:

Index options

The options value is a map where key is the name of the option and value is the available option value. The option :fields is required, others are optional (for now there is just :unique is optional).
Available options are presented in the table below:

CLI interface

Available commands are: make, migrate, list, explain. Let's see them in detail by section.

:information_source: Assume that args :models-file, :migrations-dir and :jdbc-url are supposed to be set in deps.edn alias.

Common args for all commands:

make

Create migration for new changes in models file. It detects the creating, updating and deleting of tables, columns and indexes. Each migration is wrapped by transaction by default.

Specific args:

Examples

Create migration automatically with auto-generated name:

$ clojure -X:migrations :make
Created migration: resources/db/migrations/0001_auto_create_table_book.edn
Actions:
  ...

Create migration automatically with custom name:

$ clojure -X:migrations make :name create_table_author
Created migration: resources/db/migrations/0002_create_table_author.edn
Actions:
  ...

Create empty sql migration with custom name:

$ clojure -X:migrations make :type :empty-sql :name add_custom_trigger
Created migration: resources/db/migrations/0003_add_custom_trigger.sql

Try to create migration without new changes in models:

$ clojure -X:migrations make
There are no changes in models.

migrate

Applies changes described in migration to database. Applies all unapplied migrations by number order if arg :number is not presented in command. Throws error for same migration number.

:warning: Backward migration is not yet fully implemented for auto-migrations, but already works for custom SQL migrations. For auto-migrations, it is possible to unapply migration and to delete appropriate entry from migrations table. But database changes will not be unapplied for now.

Specific args:

Examples

Migrate forward all unapplied migrations:

$ clojure -X:migrations migrate
Migrating: 0001_auto_create_table_book...
Successfully migrated: 0001_auto_create_table_book
Migrating: 0002_create_table_author...
Successfully migrated: 0002_create_table_author
Migrating: 0003_add_custom_trigger...
Successfully migrated: 0003_add_custom_trigger

Migrate forward up to particular migration number (included):

$ clojure -X:migrations migrate :number 2
Migrating: 0001_auto_create_table_book...
Successfully migrated: 0001_auto_create_table_book
Migrating: 0002_create_table_author...
Successfully migrated: 0002_create_table_author

Migrate backward up to particular migration number (excluded):

$ clojure -X:migrations migrate :number 1
Unapplying: 0002_create_table_author...
WARNING: backward migration isn't fully implemented yet. Database schema has not been changed!
Successfully unapplied: 0002_create_table_author

Migrate backward to initial state of database:

$ clojure -X:migrations migrate :number 0
Unapplying: 0003_add_custom_trigger...
Successfully unapplied: 0003_add_custom_trigger
Unapplying: 0002_create_table_author...
WARNING: backward migration isn't fully implemented yet. Database schema has not been changed!
Successfully unapplied: 0002_create_table_author
Unapplying: 0001_auto_create_table_book...
WARNING: backward migration isn't fully implemented yet. Database schema has not been changed!
Successfully unapplied: 0001_auto_create_table_book

Try to migrate already migrated migrations:

$ clojure -X:migrations migrate
Nothing to migrate.

Try to migrate up to not existing migration:

$ clojure -X:migrations migrate :number 10
-- ERROR -------------------------------------

Invalid target migration number.

list

Print out list of existing migrations with statuses displayed as boxes before migration name:

• [✓] - applied;
• [ ] - not applied.

No specific args.

Examples:

View list of partially applied migrations:

$ clojure -X:migrations list
Existing migrations:

[✓] 0001_auto_create_table_book.edn
[ ] 0002_create_table_author.edn
[ ] 0003_add_custom_trigger.sql

explain

Print out actual raw SQL for particular migration by number.

Specific args:

Examples:

View raw SQL for migration in forward direction:

$ clojure -X:migrations explain :number 1
SQL for migration 0001_auto_create_table_book.edn:

BEGIN;
CREATE TABLE book (id SERIAL UNIQUE PRIMARY KEY, name VARCHAR(256) NOT NULL, description TEXT);
COMMIT;

View raw SQL for migration in backward direction:

$ clojure -X:migrations explain :number 1 :direction :backward
SQL for migration 0001_auto_create_table_book.edn:

WARNING: backward migration isn't fully implemented yet.

help/doc

You can print docstring for function in the default namespace of the lib by running clojure cli help/doc function.

Print doc for all available functions:

$ clojure -X:deps:migrations help/doc :ns automigrate.core
Public interface for lib's users.

-------------------------
...

Print doc for particular function:

$ clojure -X:deps:migrations help/doc :ns automigrate.core :fn make
-------------------------
automigrate.core/make
...

Custom SQL migration

There are some specific cases which are not yet supported by auto-migrations. There are cases when you need to add simple data migration. You can add a custom SQL migration which contains raw SQL for forward and backward directions separately in single SQL-file. For that you can run the following command for making empty SQL migration with custom name:

$ clojure -X:migrations make :type :empty-sql :name make_all_accounts_active
Created migration: resources/db/migrations/0003_make_all_accounts_active.sql

The newly created file will look like:

-- FORWARD


-- BACKWARD

You can fill it with two block of queries for forward and backward migration. For example:

-- FORWARD

UPDATE account
SET is_active = true;

-- BACKWARD

UPDATE account
SET is_active = false;

Then migrate it as usual:

$ clojure -X:migrations migrate
Migrating: 0003_make_all_accounts_active...
Successfully migrated: 0003_make_all_accounts_active

Use in production

:warning: The library is not yet ready for production use. But it is really appreciated if you try it out! :wink:

For now, there is just a single way to configure db connection url as a :jdbc-url arg for a command. The idea here is that you could override default dev :jdbc-url value from deps.edn by running migrate command with env var using bash-script, makefile or whatever you want:

$ clojure -X:migrations migrate :jdbc-url $DATABASE_URL 
Migrating: ...

The downside of that approach could be a lack of ability to use a common config for a project. In the future there could be more convenient options for configuration if needed.

Roadmap draft

• [ ] Support backward auto-migration.
• [ ] Support custom migration using Clojure.
• [ ] Support running with Leiningen.
• [ ] Support array, enum and other fields in PostgreSQL.
• [ ] Support comment for field.
• [ ] Support autogenerated custom constraints and checks.
• [ ] Support for migrating views.
• [ ] Support for SQLite and MySQL.
• [ ] Optimize autogenerated sql queries.
• [ ] Test against different versions of db and Clojure.
• [ ] Add visual representation of db schema by models.
• [ ] Handle of model/field renaming.

Things still in design

• Should args :models-file and :migrations-dir are set by default?
• Should it be possible to set arg :jdbc-url as an env var?
• How to handle common configuration conveniently?
• More consistent and helpful messages for users.
• Ability to separate models by multiple files.
• Move transformations out of conformers.
• Most likely add option to disable field types validation in order to be able to use the tool while not every type of database column is supported.

Inspired by

• Django migrations

Thanks to projects

• Honey SQL
• Dependency
• Differ

Materials

• Blog post: Announcing automigrate.

Development

Run locally

make up  # run docker-compose with databases for development
make repl  # run builtin repl with dev aliases; also you could use any repl you want
make test  # run whole tests locally against testing database started by docker-compose
make fmt  # run formatting in action mode
make lint  # run linting
make check-deps  # run checking new versions of deps in force mode

Release new version

make install-snapshot :patch  # build and install locally a new version of lib based on latest git tag and using semver
make deploy-snapshot :patch  # build and deploy to Clojars next snapshot version from local machine
make release :patch  # bump git tag version by semver rules and push to remote repo

License

Copyright © 2021 Andrey Bogoyavlensky

Distributed under the MIT License.