# Tusker

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.

### Schema Diff Support

The table below reflects the current implementation state of the Rust rewrite,
not the intended end state. The focus is schema diffing for PostgreSQL 16, 17,
and 18, and the Rust test suite runs against all three versions in CI.

| Feature | Status | Notes |
| --- | --- | --- |
| Schemas | Not yet | Inspection exists, but creating/dropping whole schemas is still `todo!()` in the schema diff layer. |
| Tables | Done | Supports create/drop and many column-level alters for ordinary tables. |
| Columns | Partial | Supports common type/default/nullability/identity changes; generated-column alters are still treated as unsupported and emitted as warnings. |
| Constraints | Partial | Supports table `CHECK`, `PRIMARY KEY`, `UNIQUE`, `EXCLUDE`, and `FOREIGN KEY` ordering. Named `NOT NULL` constraints introduced in PostgreSQL 18 are currently skipped. |
| Indexes | Done | Supports standalone named indexes, including `CREATE INDEX` and `CREATE UNIQUE INDEX`. Constraint-backed indexes are handled through table constraints instead of as standalone indexes. |
| Views | Not yet | Views and materialized views are inspected, but they are not currently diffed or emitted. |
| Materialized views | Not yet | Inspected only; no diff/create/drop support yet. |
| Functions | Partial | Supports normal functions and procedures, with dependency-aware ordering between routines. Aggregates and window functions are not currently diffed. |
| Procedures | Done | Supported through the same routine diff path as functions. |
| Triggers | Done | Supports create/drop and trigger enabled-state restoration. |
| Sequences | Done | Supports create/drop/alter for standalone sequences. |
| Enums | Done | Supports create/drop and safe additive changes. Reordering/removing enum labels intentionally fails with guidance instead of generating unsafe SQL. |
| Domains | Done | Supports create/drop and common alters. Base type changes intentionally stop with a manual-action error instead of generating unsafe SQL. |
| Extensions | Done | Supports create/drop, schema moves, and version updates. |
| Composite types | Not yet | No inspection/diff support yet. |
| Range types | Not yet | No dedicated support for user-defined range or multirange types yet. |
| Collations | Not yet | No inspection/diff support yet. |
| Comments | Not yet | No diff support yet for `COMMENT ON` statements or object descriptions. |
| Privileges / ownership / GRANTs | Not yet | No privilege diff support yet. |
| Row-level security policies | Not yet | No inspection/diff support yet. |
| Foreign tables / FDWs | Not yet | Not currently inspected as diffable relations. |
| Partitioned tables | Not yet | Partitioned tables are currently skipped by relation inspection. |
| Dependencies beyond routines | Partial | Routine dependency ordering exists. General dependency tracking across views, types, tables, and other objects is still incomplete. |

## Development

Inside the devcontainer, run the Rust test suite against PostgreSQL 16, 17, and
18 with:

```shell
./scripts/test-postgres-versions.sh
```

The command runs `cargo test --all-features --workspace` with `PG_URL` pointed
at `postgres16`, `postgres17`, and `postgres18` in sequence. Any extra arguments
are passed through to `cargo test`.

## Installation of the command line tool

```shell
cargo install tusker
```

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

```
tusker --help
```

## Getting started

At the moment Tusker can diff tables, constraints, normal functions, and enums.
Enum handling is intentionally conservative: adding enum values is supported, but
unsafe enum rewrites such as removing or reordering values are emitted as a
failing migration with guidance instead of silently generating runnable SQL.

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

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

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

```shell
mkdir -p db/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 > db/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
[database]
#host = ""
#port = 5432
#user = ""
#password = ""
dbname = "tusker"

[schema]
filename = "db/schema/**/*.sql"

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

[diff]
safe = false
privileges = false
```

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

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

You can also override the configuration using environment variables:

```toml
export TUSKER_DATABASE_URL=postgresql:///some-db-host/some-db
```

## How can I use the generated SQL files?

The resulting SQL files can either be applied to the database by hand
or by using the built-in migration manager of tusker.

In order to apply all the migrations, that haven't been run, in order
just execute the following command:

```shell
tusker migrate
```

## 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 and the output printed to the console.

## Tusker is `unsafe` by default

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 more safety you can either use the
`--safe` argument or set the `diff.safe` configuration option to `True`
in your `tusker.toml` file.

Some changes are unsafe even when they are not simple drops. For example,
unsupported enum rewrites deliberately generate SQL that raises an exception
with a warning instead of trying to apply a dangerous automatic migration.

## FAQ

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

Yes. The `schema.filename` is an actual `glob` pattern and defaults to `db/schema/**/*.sql`.

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

Yes. 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 a temporary databases when diffing against the
`schema` and/or `migrations`. The database is called `{dbname}_diff_schema`.

## FAQ

### How does it differ from the archived Python version of Tusker?

[Tusker was originally written in Python][tusker-pypi] with the only
feature being schema diffing. It relies on [migra] and [schemainspect] to
perform the actual diffing. This version of Tusker implements the diffing from
scratch and also provides a type safe query system.

[tusker-pypi]: https://pypi.org/project/tusker/
[migra]: https://pypi.org/project/migra/
[schemainspect]: https://pypi.org/project/schemainspect/

### How can I upgrade from the Python version of Tusker?

```
$ pipx uninstall tusker
$ cargo install tusker
```

The `migra` configuration directive was renamed to `diff`. Just rename `[migra]`
in your config file to `[diff]`, if you got one.

The default values for `schema.filename` and `migrations.filename` were changed.
If you need the old behavior add this to the `tusker.toml` config file:

```toml

```

## License

Licensed under either of

- Apache License, Version 2.0 ([LICENSE-APACHE](LICENSE-APACHE) or <http://www.apache.org/licenses/LICENSE-2.0>)
- MIT license ([LICENSE-MIT](LICENSE-MIT) or <http://opensource.org/licenses/MIT>)

at your option.
