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

pipx install tusker

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

tusker --help

Getting started

Once tusker is installed create a new file called schema.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:

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:

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:

[schema]
filename = "schema.sql"

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

[database]
#host = ""
#port = 5432
#user = ""
#password = ""
dbname = "my_awesome_db"
#schema = "public"

[migra]
safe = false
privileges = false

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

[schema]
filename = "schema.sql"

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

[database]
url = "postgresql:///my_awesome_db_connection"

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

[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
• Rust: 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 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.:

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

As of 0.4.5 recursive glob patterns are supported as well:

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

How can I export initial schema from an existing database?

For exporting the initial schema you can use the native Postgres pg_dump command with the --schema-only option.

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?

The dbname setting in tusker.toml specifies database name to be used when diffing against your database. This command will print out the difference between the current database schema and the target schema:

tusker diff database

Note that this command is meant to be run after you have migrated your 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.

The dbname setting overrides the database name in connection url (if specified). If neither a dbname nor a url is specified it will default to tusker. Calling tusker diff database only makes sense if you specify a dbname or your application does indeed use a database called tusker.