jdkaplan/squill

Manage Postgresql database migrations

Squill

Squill manages Postgresql database migrations.

There's no shortage of tools for running migrations, but this one embodies my particular opinions:

1. Migrations should be written in database-specific SQL.
2. Migrations are not generally idempotent or reversible in production, but reversals (down migrations) are very useful during development.
3. Migration dependencies form a tree structure, not a linear sequence.

What's a squill?

It's the common name for a subfamily of plants that are actually pretty cool looking.

But more importantly, it's a word that has the letters "s", "q", and "l" in that order, is easy to type and pronounce, and not already someone else's crate name 😉

Installation

To install Squill as a command, use cargo install:

cargo install squill-cli

or download a pre-built package from the GitHub Releases.

To use Squill as a library, use cargo add:

cargo add squill

Usage

Run squill --help to get usage information from each subcommand.

First-time setup

Write the configuration file (squill.toml) or set the equivalent environment variables. The environment variables take precedence over the file.

The environment variables are uppercase versions of the ones in the file with SQUILL_ prefixes. For example, database_url is SQUILL_DATABASE_URL.

# The connection string for the database to run migrations on.
#
# You might prefer to set this using an environment variable.
#
# Default: "" (default PostgreSQL server)
database_url = ""

# The directory used to store migration files.
#
# Default: "migrations"
migrations_dir = "migrations"

# The template to use for new migration files.
#
# Default: (unset) (use the embedded default migration templates)
templates_dir = ".squill/templates"

Then, generate the first migration that sets up Squill's requirements:

squill init

That should have written 0-init/{up,down}.sql to your migrations directory. Read through those files and make any changes you want.

Finally, run the up migration:

squill migrate

Writing a new migration

Create a new empty migration file:

squill new --name 'create_users_table'

(You can override the automatic ID generation with --id 123).

Write your migration in the file. Then run it:

squill migrate

Undoing a migration

For a migration that has already been run in production (or some other shared environment), the best option is to write a new migration to undo the old one.

In a development environment, undo the most recently run migration (by application order, not by ID):

squill undo

Edit the up migration, and then use squill migrate as normal to run it.

To make this easier, squill redo will run down.sql and then up.sql for the most recently run migration.

Renumbering migrations

You may have a mix of migrations with different ID lengths, which can make it the directory listing appear out of order. Use the align-ids subcommand to zero-pad shorter IDs:

squill align-ids

That command is just a preview by default. Add --execute to actually execute all of the proposed renames.

Custom migration templates

You can customize the files generated by squill new by setting the templates_dir path. Squill will use the new.up.sql and new.down.sql files in that directory.

.squill/templates
├── new.down.sql
└── new.up.sql

These are interpreted as Tera templates for generating the respective up and down migration files.

The Tera context will be something like this:

id: &i64
name: &str

Named templates

You can keep a named migration template by making a subdirectory within templates_dir and adding new.up.sql and new.down.sql inside it.

To use a named template, add the --template argument to the squill new command. The default (unnamed) template inside templates_dir will be used otherwise.

For example, if you want to ensure that create table migrations follow specific conventions, your templates_dir could look like this:

.squill/templates
├── create_table
│   ├── new.down.sql
│   └── new.up.sql
├── new.down.sql
└── new.up.sql

And this is the command to generate a new migration that uses it:

squill new --template 'create_table' --name 'create_users_table'

License

Licensed under either of

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

at your option.

Contribution

Unless you explicitly state otherwise, any contribution intentionally submitted for inclusion in the work by you, as defined in the Apache-2.0 license, shall be dual licensed as above, without any additional terms or conditions.

Join me!

I welcome contributions from anyone who finds a way to make this better.

In particular, I appreciate these things:

• Pull requests with clear motivation (tests are nice too!)
• Bug reports with reproducible setup instructions
• Ideas about things that work but can be improved
• Comments in issues and PRs to help me write better words and code

Support

This is is hobby project, so I'll only work on it as much as I find it fun to do so. That said, I find software maintenance techniques interesting, so feel free to start a conversation about a stable v1 if you start relying on this for something important.

squill's Issues

Improve missing template error message

The CLI will error when trying to use a migration template file that doesn't exist. An example of the current error message:

$ squill new --template missing --name this
Error: failed to render template: Template 'missing/new.up.sql' not found

This message comes almost directly from Tera. A better error message would include Squill-specific information:

• The full (relative?) path of the missing template(s)
• A special message if the template subdirectory (or the whole templates_dir) doesn't exist
• A special message if the templates_dir is unconfigured/default

A sketch of something better:

Error: failed to render template: `.squill/templates/missing` not found

Error: failed to render template: `.squill/templates/missing/up.sql` not found

Error: failed to render template: `.squill/templates` (templates_dir) not found

Error: failed to render template: no templates_dir configured

Implementation notes

It may be easier to track which template files exist by tracking them in a map explicitly instead of using the one big tera::Tera instance.

Multi-template support was added in #168

Split library and binary packages properly

The True Path: Two Packages
As far as I know there's no winning here, every path leads to sadness for one kind of user or another.

-- https://blog.axo.dev/2024/03/its-a-lib-and-a-bin

That blog post perfectly describes everything I was thinking at every point in it I've seen so far, so I'm going to trust the experts and skip to the end: make two packages.

Add support for `cargo binstall squill-cli`

The cargo-binstall tool needs some extra metadata to find the pre-built binary.

Instructions live here: https://github.com/cargo-bins/cargo-binstall/blob/main/SUPPORT.md

Warn on migration directory ID collisions

When copying around some migration directories, I found myself in a situation where I had two 0000000001-* directories, and nothing complained about it!

Print a warning (or maybe even error?) if there are duplicate migration IDs when scanning the migration directories.

Support multiple template sets in one app

I tend to write one of a few types of migration over the course of an app based on conventions that I've set up within that app. One way to preserve consistency is to copy-paste-edit an existing migration. But then I end up with a "favorite" migration that may drift from current standards.

This could be solved with template sets instead of just a single pair of templates. This is technically already possible with a workaround (below), but it might be interesting to add real support for that kind of thing.

Some examples of conventions:

• create table: Make sure to use the same type for the id column, do/don't use real foreign keys across tables, do/don't manage table metadata automatically, remember to add indexes.
• add column: Do/don't migrate the data in the migration, do/don't set a default for the column.
• (If you have another, post it as a comment so I can include it in this motivation.)

The workaround:

1. Create a directory for each migration type: .squill/templates/{create_table,add_column}.
   a. .squill/templates/{up,down}.sql could still exist to be some "default" templates, but I'd probably put them in .squill/templates/default.
2. With squill new, pass --templates-dir .squill/templates/create_table (or similar) to get the right templates.

I don't have a design for this yet, so feel free to experiment and/or ask questions here if you're interested in thinking about this!

Split into binary and library crates