Command to diff SQL schemas in git and apply the migrations
Project description
automigrate
Tool to diff SQL schemas in git and apply the migrations.
Use this if you don't like to manage migrations separately from your declarative schema definitions.
- Warning this is beta software
- Features
- Installation & basic use
- What does & doesn't work
- Comparison vs other tools
- Using with ORMs
Beta software
This is beta software and you should be careful with its output.
- Eyeball the migrations before applying them
- Proactively raise github issues for things that seem broken
Features
- Operates on
*.sql
files (i.e. files withcreate table
andcreate index
statements) - Operates on git -- meaning that it tracks the git version of applied migration and can create a SQL migration given two git refs
- No need to write or store migrations for simple cases -- they're defined bidirectionally in terms of your git history
- Ability to specify migrations manually when needed
- Stores the history of applied migrations in sql in
automigrate_meta
table
Philosophy
- Defining your schema in your ORM is nuts because it ties you to one language, reduces clarity, and sometimes limits SQL features you can use
- Existing migration tools don't pull their weight
- SQL is a more general skill than ORMs and other tools should therefore mirror SQL
- Mirroring live databases to get a schema is insane because are you tunneling to prod to run your linter? Live DB shouldn't be available to developers. Source of truth should be git.
- Schema should be versioned using the same git shas as code so the logic is easy to detect if a deploy requires a migration
Installation & basic use
To install, pip install automig
(or use pip3
if that fails). This should install dependencies and register the automig command.
You can fall back to git+https://github.com/abe-winter/automigrate
if you want latest master.
Typical invocations:
# create an initial migration (also create meta tables)
automig 218dd2c 'test/schema/*.sql' --initial | psql -h 172.17.0.2 -U postgres --single-transaction
# migrate a database
LAST_SHA=$(psql -h 172.17.0.2 -U postgres -t -c "select sha from automigrate_meta order by id desc limit 1")
echo migrating from $LAST_SHA
automig $LAST_SHA...b5b40ce 'test/schema/*.sql' | psql -h 172.17.0.2 -U postgres --single-transaction
# I guess you can just migrate to HEAD if you're feeling lucky
automig $LAST_SHA...HEAD 'test/schema/*.sql' | psql -h 172.17.0.2 -U postgres --single-transaction
What does & doesn't work
- Adding tables, indexes and columns should mostly work
- modifying primary keys doesn't work
- modifying column types doesn't work (even something inoccuous like a default)
- For diffs that are erroring, you can override with a .manualmig.yml file
- Be careful with using unescaped keywords as names (i.e. a table named table) -- you'll likely confuse the parser even where your sql engine allows it
- This hasn't been tested on a wide range of syntax (i.e. arrays / json)
- Not sure if capitalized SQL keywords are supported (todo add tests)
- Arbitrary whitespace changes can probably confuse the parser (todo add tests)
- Need a way to check live schema against desired to call out problems
- undo, i.e. what would be 'down' in a typical migration tool. This may work out of the box (pass
HEAD...HEAD~1
instead ofHEAD~1...HEAD
), but needs tests - add sql comments examples to the test suite
- open design question: any reason to support non-DDL statements? what would this be used for?
- Anything that messes with the git history (like a rebase) is deeply confusing to this tool and will result in bad migrations. Workaround:
- warning: this method only works if the rebase doesn't change migrations
- figure out the new sha that corresponds to your last old sha -- most likely you can do a
git show $OLDSHA
and then look for that commit msg ingit log
- and insert that corresponding sha into the
automigrate_meta
table:psql -h 172.17.0.2 -U postgres -c "insert into automigrate (sha) values ('$NEWSHA')"
- you should be good to go
- todo: find a way to automatically detect & recover from rebases
- todo: provide an
--opaque
argument that doesn't try to create granular changes for each commit in the history
Comparison vs other tools
- alembic / other auto migration generators
- they're generally language-specific and ORM-specific, this isn't
- they're not git-aware so you need to manage migration files
- they can usually connect to your DB -- this is designed to be run directly as SQL
- apex sql diff
- they can operate on 'script folders' and integrate with source control
- not sure what this means in practice
- I think this product is GUI-first and only runs on windows?
- sqlite sqldiff.exe
- seems like a really good tool if you're operating on sqlite databases
- liquibase
- supports 4 formats for migration (xml, yml, json, sql) -- this supports 0 because it doesn't need them
- requires you to explictly define migrations (I think) -- this doesn't
Using with ORMs
Your ORM has to be willing to import a schema from create table statements. (I don't know any ORM that does this out of the box, although some can reflect a live DB, like sqlalchemy's automap).
This repo contains a barebones, mostly untested harness to generate sqlalchemy models from create table statements. You can run it with:
python -m automig.lib.sa_harness 'test/schema/*.sql'
Happy to accept PRs to generate ORM defs from create table
stmts (or vice versa).
Development workflow
# enable .envrc with `direnv allow` if necessary, or set up your own virtualenv
# pip install -r requirements.txt
pip install pytest
pytest # in repo root
If you want to pitch in on the project, there are a bunch of @pytest.mark.skip
tests that need to be filled in (most require feature development to get them passing).
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.