# 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`:
```bash
cargo install squill-cli
```
or download a pre-built package from the [GitHub Releases].
[GitHub Releases]: https://github.com/jdkaplan/squill/releases?q=squill-cli
To use Squill as a library, use `cargo add`:
```bash
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`.
```toml
# 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:
```bash
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:
```bash
squill migrate
```
### Writing a new migration
Create a new empty migration file:
```bash
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:
```bash
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):
```bash
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:
```bash
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.
[Tera]: https://tera.netlify.app/
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:
```bash
squill new --template 'create_table' --name 'create_users_table'
```
## 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.
## 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.