migreat 0.1.5
A flexible SQL migration runner.
License: MIT
Requires Python: >=3.7,<3.10
Keywords: database,postgresql,sql,migrations

# migreat

A flexible SQL migration runner.

Right now, supports only PostgreSQL via `psycopg2`, as it's still an experiment. Support for any database with a DBAPI implementation is planned.

## Install

`migreat` supports Python 3.7, 3.8, and 3.9. Since it currently only supports PostgreSQL via `psycopg2`, it will also be installed.

```
pip install migreat
```

## Mental Model

`migreat` runs SQL migrations with a DB cursor provided by your code via a function, and stores migration metadata in a table with the datetime it was run, the ID of the user who ran it, the name of the migration, and a hash of its contents at the time it was run. The hash of a migration will be checked against if it is ever rolled back, to make sure we are rolling back the same migration that was once run.

## Usage

Migrations are simple SQL files placed inside a directory and following a name pattern. The name pattern is `YEAR-MONTH-DAY-SEQNUM-arbitrary-name.sql`. `YEAR-MONTH-DAY` is the day's date following ISO 8601. The `SEQNUM` is a 2-digit sequential number for migrations released in the same day. Some valid names:

```
2020-01-10-03-create-users-table.sql
2020-03-18-01-remove-foreign-keys-from-audit-tables.sql
```

`migreat` will run the migrations in sequence of date and sequence number.

Create the migrations table:

```
$ migreat create-migrations-table \
    --cursor-factory yourapp.db.atomic
```

Run migrations:

```
$ migreat run \
    --migrations-dir migrations \
    --user-id 42 \
    --last-migration 2020-01-01-01 \
    --cursor-factory yourapp.db.atomic \
    --rollback
```

This will:

- Look for the migrations in directory `migrations` relative to the current working directory;
  - If this is not supplied, the default value will be `migrations`;
- Run the migrations as user with ID 42;
- Run migrations rolling them back, only back up to migration `2020-01-01-01`;
- Run the function `yourapp.db.atomic` with no arguments expecting it to return a DBAPI cursor (see tests for an example).

`migreat` allows you to constrain the user ID to real user IDs you might have in some table in your database. To enable it:

```
$ migreat create-user-id-foreign-key users id \
    --cursor-factory yourapp.db.atomic
```

This will constrain user IDs to values in the column `id` of table `users`.

Finally, you can drop the user ID foreign key constraint:

```
$ migreat drop-user-id-foreign-key \
    --cursor-factory yourapp.db.atomic
```

And drop the migrations table:

```
$ migreat drop-migrations-table \
    --cursor-factory yourapp.db.atomic
```

All above commands also accept:

```
--cursor-factory-args=value1,value2
--cursor-factory-kwargs=key1,value1,key2,value2
```

Values are valid CSV strings and can contain nested commas inside proper delimiters.

## Development

Clone the source code from GitHub, have a Docker Engine reachable, have all supported Python interpreters in your `PATH` (we recommend that you use `pyenv` to manage different Python interpreters and environments) and create a new virtual environment with `poetry install`.

Run the tests with:

```
./run test-db
# Wait for the database to be ready
./run all-tests
```

To run a specific test:

```
./run test-db
# Wait for the database to be ready
./run test <test-address>
```

To make sure your code abides to our quality standards, run:

```
./run quality
```