Automigrate

Auto-generated database schema migrations for Clojure. Define models as plain EDN data and create database schema migrations automatically based on changes to the models.

Features

declaratively define db schema as models in EDN;
create migrations automatically based on model changes;
migrate db schema in forward and backward directions;
manage migrations for: tables, indexes, constraints, enum types;
view actual SQL or human-readable description for a migration;
optionally add a custom SQL migration for specific cases;
use with PostgreSQL ℹ️ [other databases are planned] .

Quick overview

automigrate_demo.mp4

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

Setup database connection

Before running migrations we need to set database URL with DATABASE_URL env var, for example:

export DATABASE_URL="jdbc:postgresql://localhost:5432/mydb?user=myuser&password=secret"

Note: There is an ability to change the name of the environment variable using command argument: :jdbc-url-env-var. Alternatively, instead of env var we can use :jdbc-url argument to setup the database URL directly for commands.

tools.deps -X option

deps.edn

{:deps {org.clojure/clojure {:mvn/version "1.11.2"}
        org.postgresql/postgresql {:mvn/version "42.3.1"}}
 :paths [... "resources"]
 :aliases 
 {...
  :migrations {:extra-deps {net.clojars.abogoyavlensky/automigrate {:mvn/version "<VERSION>"}}
               :ns-default automigrate.core}}}

Now we can create resources/db/models.edn file with a map and run commands:

$ clojure -X:migrations list
$ clojure -X:migrations make
$ clojure -X:migrations migrate
$ clojure -X:migrations explain :number 1
$ clojure -X:migrations help

Leiningen

project.clj

(defproject myprj "0.1.0-SNAPSHOT"
  :dependencies [[org.clojure/clojure "1.11.2"]
                 [org.postgresql/postgresql "42.7.3"]]
  :resource-paths ["resources"]
  :profiles {...
             :migrations
             {:dependencies [[net.clojars.abogoyavlensky/automigrate "<VERSION>"]]
              :main automigrate.core}}
  :aliases {"migrations" ["with-profile" "+migrations" "run"]})

Usage example:

$ lein migrations list
$ lein migrations make
$ lein migrations migrate
$ lein migrations explain --number 1
$ lein migrations help

Note: For lein there is the same CLI-interface with the same commands and options, but instead of keywords (e.g.:number) for option names you should use --... (e.g. --number).

Getting started

After configuration, you need to create models.edn file with first model. Then you will be able to make migration and migrate db schema. By default, the path for models file is resources/db/models.edn.

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 {:primary-key true}]
        [:name [:varchar 255] {: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

A migration file will be created at resources/db/migrations by default. The pattern for migration file name is: <number>_auto_<autogenerated migration name>.edn. The migration can contain multiple actions. Every action will be converted to a SQL query at migration time. The migration at resources/db/migrations/0001_auto_create_table_book.edn looks like:

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

Migrate

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

$ clojure -X:migrations migrate
Applying 0001_auto_create_table_book...
0001_auto_create_table_book successfully applied.

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:
[x] 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 forward migration 0001_auto_create_table_book.edn:

BEGIN;

CREATE TABLE book (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT
);

COMMIT;

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

ℹ️ 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 {:primary-key true}]
        [:name [:varchar 255] {:null false}]
        [:description :text]]}

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

{:book {:fields [[:id :serial {:primary-key true}]
                [:name [:varchar 255] {: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 params. Available field types are matched with PostgreSQL built-in data types and presented in the following table:

Field type	Description
`:integer`
`:smallint`
`:bigint`
`:float`
`:real`
`:serial`	Auto-incremented pg integer field.
`:bigserial`	Auto-incremented pg bigint field.
`:smallserial`	Auto-incremented pg serial2 field.
`:numeric` or `[:numeric <pos-int>? <int>]`	Numeric type with optional precision and scale params. Default value could be set as numeric string, bigdec, float, int and nil: `"10.22"`, `10.22M`, `10`, `10.22`, `nil`.
`:decimal` or `[:decimal <pos-int>? <int>]`	Numeric type with optional precision and scale params. Same as `:numeric`.
`:uuid`
`:boolean`
`:text`
`:time` or `[:time <int>]`
`:timetz` or `[:timetz <int>]`
`:timestamp` or `[:timestamp <int>]`
`:timestamptz` or `[:timestamptz <int>]`
`:interval` or `[:interval <int>]`
`:date`
`:point`
`:json`
`:jsonb`
`:varchar` or `[:varchar <pos-int>]`	Second element is the length of value.
`:char` or `[:char <pos-int>]`	Second element is the length of value.
`:float` or `[:float <pos-int>]`	Second element is the minimum acceptable precision in binary digits.
`[:enum <enum-type-name>]`	To use enum type you should define it in `:types` section in model.
`:box`
`:bytea`
`:cidr`
`:circle`
`:double-precision`
`:inet`
`:line`
`:lseg`
`:macaddr`
`:macaddr8`
`:money`
`:path`
`:pg_lsn`
`:pg_snapshot`
`:polygon`
`:tsquery`
`:tsvector`
`:txid_snapshot`
`:xml`
`:bit` or `[:bit <pos-int>]`
`:varbit` or `[:varbit <pos-int>]`

Doc reference to the PostgreSQL built-in general-purpose data types: https://www.postgresql.org/docs/current/datatype.html#DATATYPE-TABLE

Notes

<...>? - param is optional.
or - an alternative definition of type.

ℹ️ 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:

Field option	Description	Required?	Value
`:null`	Set to `false` for non-nullable field. Field is nullable by default if the option is not set.	`false`	`boolean?`
`:primary-key`	Set to `true` for making primary key field.	`false`	`true?`
`:unique`	Set to `true` to add unique constraint for a field.	`false`	`true?`
`:default`	Default value for a field.	`false`	`boolean?`, `integer?`, `float?`, `decimal?`, `string?`, `nil?`, or fn defined as `[:keyword <integer? or float? or string?>]`
`:foreign-key`	Set to namespaced keyword to point to a primary key field from another model.	`false`	`:another-model/field-name`
`:on-delete`	Specify delete action for `:foreign-key`.	`false`	`:cascade`, `:set-null`, `:set-default`, `:restrict`, `:no-action`
`:on-update`	Specify update action for `:foreign-key`.	`false`	`:cascade`, `:set-null`, `:set-default`, `:restrict`, `:no-action`
`:check`	Set condition in Honeysql format to create custom CHECK for a column.	`false`	Example: `[:and [:> :month 0] [:<= :month 12]]`
`:array`	Can be added to any field type to make it array.	`false`	`string?`, examples: `"[]"`, `"[][]"`, `[][10][3]`
`:comment`	Add a comment on the field.	`false`	`string?`

Indexes

Each index is a vector of three elements: [:name-of-index :type-of-index {:fields [:field-from-model-to-index] :unique boolean? :where [...]}] 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:

Field type
`:btree`
`:gin`
`:gist`
`:spgist`
`:brin`
`:hash`

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.
Available options are presented in the table below:

Field option	Description	Required?	Value
`:fields`	Vector of fields as keywords. Index will be created for those fields.	`true`	[`:field-name` ...]
`:unique`	Set to `true` if index should be unique.	`false`	`true?`
`:where`	Set condition in Honeysql format to create partial index.	`false`	Example: `[:> amount 10]`

Types

ℹ️ At the moment only Enum type is supported.

Each type is a vector of three elements: [:name-of-type :type-of-type {...}] Name, type-of-type and options are required.

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

Type of type

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

Field type
`:enum`

Enum type

Each enum type is a vector of three elements: [:name-of-type :enum {:choices [<str>]}]

Options for enum type must contain the :choices value with vector of strings. :choices represent enum values for the type.

An example of model definition with enum type:

{:account {:fields [[:id :serial]
                    [:role [:enum :account-role]]]
           :types [[:account-role :enum {:choices ["admin" "customer"]}]]}}

Limitations:

:choices can't be empty;
values in :choices must be unique for the particular type;
removing a value from :choices of existing type is not supported;
re-ordering values in :choices of existing type is not supported;

CLI interface

Available commands are:

Command	Description
`make`	Create migration for new changes in models file.
`migrate`	Apply a change described in the migration to database.
`list`	Show list of existing migrations with status.
`explain`	Show a migration in SQL or human-readable format.
`help`	Show short documentation for Automigrate or a particular command.

Common args for all commands:

Argument	Description	Required?	Possible values	Default value
`:jdbc-url`	Database connection defined as JDBC-url.	`false`	string jdbc url (example: `"jdbc:postgresql://localhost:5432/mydb?user=myuser&password=secret"`)	Read env var (`DATABASE_URL` or set as `:jdbc-url-env-var`)
`:jdbc-url-env-var`	Name of environment variable for jdbc-url.	`false`	string jdbc url (example: `DB_URL`)	`DATABASE_URL`
`:models-file`	Path to models file, relative to the `resources` dir.	`false`	string path (example: `"path/to/models.edn"`)	`"db/models.edn"`
`:migrations-dir`	Path to store migrations dir, relative to the `resources` dir.	`false`	string path (example: `"path/to/migrations"`)	`"db/migrations"`
`:resources-dir`	Path to resources dir to create migrations dir when it doesn't exist yet.	`false`	string path (example: `"path/to/resources"`)	`"resources"`
`:migrations-table`	Model name for storing applied migrations.	`false`	string (example: `"migrations"`)	`"automigrate_migrations"`

`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:

Argument	Description	Required?	Possible values	Default value
`:type`	Type of migration file.	`false`	`:empty-sql`	not provided, migration will be created automatically
`:name`	Custom name for migration file separated by underscores.	`false` (:warning: required for `:empty-sql` type)	string (example: `"add_custom_trigger"`)	generated automatically by first migration action

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 change 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.

Backward migration is fully implemented. For auto-generated and SQL migrations, it is possible to revert migration and to delete appropriate entry from migrations table. Database changes will be reverted.

In forward direction if specified migration :number is included, meaning if, for example, :number 3 the migration with number 3 will be applied. In backward migration the :number is excluded, so all migrations until the specified number will be reverted but not the target one. For instance if we have 3 migrations as applied, and want to revert just the 3d and 2d ones, we can run migrate command with :number 1. 3d and 3d migrations will be reverted, but the first one will stay applied.

Specific args:

Argument	Description	Required?	Possible values	Default value
`:number`	Number of migration which should be a target point. In forward direction, migration by number will by applied. In backward direction, migration by number will be reverted.	`false`	integer (example: `1` for migration `0001_...`)	not provided, last migration number by default

Examples

Migrate forward all unapplied migrations:

$ clojure -X:migrations migrate
Appyling 0001_auto_create_table_book...
0001_auto_create_table_book successfully applied.
Appyling 0002_create_table_author...
0002_create_table_author successfully applied.
Appyling 0003_add_custom_trigger...
0003_add_custom_trigger successfully applied.

Migrate forward up to particular migration number (included):

$ clojure -X:migrations migrate :number 2
Appyling 0001_auto_create_table_book...
0001_auto_create_table_book successfully applied.
Appyling 0002_create_table_author...
0002_create_table_author successfully applied.

Migrate backward down to particular migration number (excluded):

$ clojure -X:migrations migrate :number 1
Reverting 0002_create_table_author...
0002_create_table_author successfully reverted.

Migrate backward to initial state of database:

$ clojure -X:migrations migrate :number 0
Reverting 0003_add_custom_trigger...
0003_add_custom_trigger successfully reverted.
Reverting 0002_create_table_author...
0002_create_table_author successfully reverted.
Reverting 0001_auto_create_table_book...
0001_auto_create_table_book successfully reverted.

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:

[x] - applied;
[ ] - not applied.

No specific args.

Examples:

View list of partially applied migrations:

$ clojure -X:migrations list
Existing migrations:
[x] 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:

Argument	Description	Required?	Possible values	Default value
`:number`	Number of migration which should be explained.	`true`	integer (example: `1` for migration `0001_...`)	not provided
`:direction`	Direction in which migration should be explained.	`false`	`:forward`, `:backward`	`:forward`
`:format`	Format of explanation.	`false`	`:sql`, `:human`	`:sql`

Examples:

View raw SQL for migration in forward direction:

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

BEGIN;

CREATE TABLE book (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT
);

COMMIT;

View raw SQL for migration in backward direction:

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

BEGIN;

DROP TABLE IF EXISTS book;

COMMIT;

`help`

You can print short doc info for a particular command or the tool itself by running help command.

Args:

Argument	Description	Required?	Possible values	Default value
`:cmd`	Command name.	`false`	`make`, `migrate`, `list`, `explain`, `help`	not provided, by default prints doc for all commands

Examples

Print doc for all available commands:

$ clojure -X:migrations help
Auto-generated database migrations for Clojure.

Available commands:
...

Print doc for a particular command:

$ clojure -X:migrations help :cmd make
Create a new migration based on changes to the models.

Available options:
...

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. Backward migration block is not mandatory and can be empty. 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
Appyling 0003_make_all_accounts_active...
0003_make_all_accounts_active successfully applied.

Use in production

⚠️ The library is not yet ready for production use. But it is really appreciated if you try it out! 😉

In production build you can use DATABASE_URL env variable to set up database connection for migrations. There are some options we have to run migrations.

Inside application as a part of the system start

An example for Integrant database component:

(ns myprj.main
  (:require [automigrate.core :as automigrate]
            [integrant.core :as ig]
            [hikari-cp.core :as cp])

  (defmethod ig/init-key ::db
     [_ options]
     (automigrate/migrate)
     (cp/make-datasource options)))

Without uberjar

If you do not build a jar-file and use clojure cli tool or lein to run the app then you can use the same alias as it is described in the installation section of this doc.

$ clojure -X:migrations migrate

With uberjar

If you build jar-file then you can implement additional option to run migration via main, for instance:

(ns myprj.main
  (:gen-class)
  (:require [automigrate.core :as automigrate]))

(defn- run-system
  []
  ...)

(defn -main
  "Run application system in production."
  [& [command]]
  (case command
    "migrations" (automigrate/migrate)
    (run-system)))

Then build jar-file and run migrations

$ java -jar target/standalone.jar migrations 
Appyling ...

or run the app:

$ java -jar target/standalone.jar

Roadmap

Things still in design

How to handle common configuration conveniently (separated edn file?)?
More consistent and helpful messages for users, maybe using fipp library.
Ability to separate models by multiple files.
Move transformations out of clojure spec conformers or replace spec with malli.
Simplify model definition just as map with key :type instead of vector of 3 items.
Disable field types validation at all, or add ability to set arbitrary custom type.
Handle of model/field renaming.

Inspired by

Thanks to projects

Resources

Announcing automigrate (blog post)
Designing a database schema for a budget tracker with Automigrate (blog post)

Projects

clojure-kamal-example

Development

Install system deps

Install mise-en-place and run:

mise install

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 outdated  # 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

Distributed under the MIT License.

Name		Name	Last commit message	Last commit date
Latest commit History 110 Commits
.clj-kondo		.clj-kondo
.github		.github
dev		dev
examples		examples
src/automigrate		src/automigrate
templates		templates
test		test
.cljstyle		.cljstyle
.gitignore		.gitignore
.mise.toml		.mise.toml
CHANGELOG.md		CHANGELOG.md
LICENSE		LICENSE
Makefile		Makefile
README.md		README.md
build.clj		build.clj
deps.edn		deps.edn
docker-compose.yaml		docker-compose.yaml
lefthook.yml		lefthook.yml

License

abogoyavlensky/automigrate

Folders and files

Latest commit

History

Repository files navigation

Automigrate

Features

Quick overview

State

Usage

Installation

Setup database connection

tools.deps -X option

Leiningen

Getting started

Add model

Make migration

Migrate

List and explain migrations

Documentation

Model definition

Fields

Field types

Notes

Field options

Indexes

Index types

Index options

Types

Type of type

Enum type

CLI interface

make

Examples

migrate

Examples

list

Examples:

explain

Examples:

help

Examples

Custom SQL migration

Use in production

Inside application as a part of the system start

Without uberjar

With uberjar

Roadmap

Things still in design

Inspired by

Thanks to projects

Resources

Projects

Development

Install system deps

Run locally

Release new version

License

About

Topics

Resources

License

Stars

Watchers

Forks

Releases 10

Languages

`make`

`migrate`

`list`

`explain`

`help`