tusker 0.5.1
A PostgreSQL specific migration tool
License: Unlicense
Requires Python: >=3.7,<4.0

# Tusker

[![GitHub](https://img.shields.io/github/license/bikeshedder/tusker?label=License&logoColor=white&style=for-the-badge)](https://github.com/bikeshedder/tusker/blob/master/LICENSE)
&nbsp;
[![PyPI](https://img.shields.io/pypi/v/tusker?label=PyPI&logo=pypi&logoColor=white&style=for-the-badge)](https://pypi.org/project/tusker)

A PostgreSQL specific migration tool

## Elevator pitch

Do you want to write your database schema directly as SQL
which is understood by PostgreSQL?

Do you want to be able to make changes to this schema and
generate the SQL which is required to migrate between the
old and new schema version?

Tusker does exactly this.

## Installation

```shell
pipx install tusker
```

Now you should be able to run tusker. Give it a try:

```shell
tusker --help
```

## Getting started

Once tusker is installed create a new file called `schema.sql`:

```sql
CREATE TABLE fruit (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY,
    name TEXT NOT NULL UNIQUE
);
```

You probably want to create an empty `migrations` directory, too:

```shell
mkdir migrations
```

Now you should be able to create your first migration:

```
tusker diff
```

The migration is printed to the console and all you need to do is
copy and paste the output into a new file in the migrations directory.
Alternatively you can also pipe the output of `tusker diff` into the
target file:

```
tusker diff > migrations/0001_initial.sql
```

After that check that your `schema.sql` and your `migrations` are in sync:

```
tusker diff
```

This should give you an empty output. This means that there is no difference
between applying the migrations in order and the target schema.

Alternatively you can run the check command:

```
tusker check
```

If you want to change the schema in the future simply change the `schema.sql`
and run `tusker diff` to create the migration for you.

Give it a try and change the `schema.sql`:

```sql
CREATE TABLE fruit (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY,
    name TEXT NOT NULL UNIQUE,
    color TEXT NOT NULL DEFAULT ''
);
```

Create a new migration:

```
tusker diff > migrations/0002_fruit_color.sql
```

**Congratulations! You are now using SQL to write your migrations. You are no longer limited by a 3rd party data definition language or an object relational wrapper.**

## Configuration

In order to run tusker you do not need a configuration file. The following
defaults are assumed:

- The file containing your database schema is called `schema.sql`
- The directory containing the migrations is called `migrations`
- Your current user can connect to the database using a unix
  domain socket without a password.

You can also create a configuration file called `tusker.toml`. The default
configuration looks like that:

```toml
[schema]
filename = "schema.sql"

[migrations]
filename = "migrations/*.sql"

[database]
#host = ""
#port = 5432
#user = ""
#password = ""
dbname = "tusker"

[migra]
safe = false
privileges = false
```

Instead of the exploded form of `host`, `port`, etc. it
is also possible to pass a connection URL:

```toml
[schema]
filename = "schema.sql"

[migrations]
filename = "migrations/*.sql"

[database]
url = "postgresql:///my_awesome_db"
```

You can also use an environment variable in place of a hard-coded value:

```toml
[database]
url = "${DATABASE_URL}"
```

## How can I use the generated SQL files?

The resulting SQL files can either be applied to the database by hand
or by using one of the many great tools and libraries which support
applying SQL files in order.

Some recommendations are:

- NodeJS: [marv](https://www.npmjs.com/package/marv)
- Rust: [refinery](https://crates.io/crates/refinery)

## How does it work?

Upon startup `tusker` reads all files from the `migrations` directory
and runs them on an empty database. Another empty database is created
and the target schema is created. Then those two schemas are
diffed using the excellent [migra](https://pypi.org/project/migra/)
tool and the output printed to the console.

## Tusker is `unsafe` by default

Unlike `migra` the `tusker` command by default does not throw an
exception when a `drop`-statement is generated. Always check your
generated migrations prior to running them. If you want the same
behavior as migra you can either use the `--safe` argument or set
the `migra.safe` configuration option to `True` in your `tusker.toml`
file.

## FAQ

### Is it possible to split the schema into multiple files?

Yes. This feature has been added in 0.3. You can now use `glob` patterns as
part of the `schema.filename` setting. e.g.:

```toml
[schema]
filename = "schema/*.sql"
```

As of 0.4.5 recursive glob patterns are supported as well:

```toml
[schema]
filename = "schema/**/*.sql"
```

### Is it possible to diff the schema and/or migrations against an existing database?

Yes. This feature has been added in 0.2. You can pass a `from` and `to`
argument to the `tusker diff` command. Check the output of `tusker diff --help` for
more details.

### Tusker printed an error and left the temporary databases behind. How can I remove them?

Run `tusker clean`. This will remove all databases which were created
by previous runs of tusker. Tusker only removes databases which are
marked with a `CREATED BY TUSKER` comment.

### What does the `dbname` setting in `tusker.toml` mean?

When diffing against a ready migrated database this database name is used. This
command will print out the difference between the current database schema and
the target schema:

```shell
tusker diff database
```

Tusker also needs to create temporary databases when diffing against the `schema`
and/or `migrations`. The two databases are called `{dbname}_{timestamp}_schema`
and `{dbname}_{timestamp}_migrations`.