Introduction to eugene

Eugene helps you write zero downtime schema migration scripts for PostgreSQL databases. Sometimes, the most straightforward way to make a change to your database schema is also quite risky, due to locking issues and lock queues. Eugene has two modes that can help you spot dangerous patterns and can suggest a safer way to achieve the same effect in many cases, and it is easy to get started with both:

  • eugene lint will perform syntax tree analysis of your SQL script using the PostgreSQL parser.
  • eugene trace run your scripts in a temporary PostgreSQL server, and inspect locks.
  • Both understand git and can easily check only what's new on your branch, commit or tag.
  • Both handle folders with version named scripts, and run scripts in the right order.
  • Easy to run in CI, to post markdown reports to your PRs.

Feel free to try out Eugene by playing around with the SQL script in the text area below. When you click the "Check" button, Eugene will analyze the scripts and let you know if it found any issues.

-- You can use file markers like this to break migrations -- into steps and run them in order. -- file: create_table.sql create table books ( id serial primary key, title text, author text, published date ); -- file: alter_table.sql alter table books alter column title set not null; alter table books alter column author set not null; -- file: set_unique.sql set local lock_timeout = '2s'; alter table books add constraint unique_title_author unique (title, author);

The demo corresponds to using eugene lint on a folder of SQL scripts on your local machine. You can also use eugene trace to run the scripts, which can pick up more issues, some of which eugene lint can't detect.

Installing eugene

You can install eugene using cargo, but this requires you to have rust and some other build tools installed. To install rust, you can use rustup.

In addition to rust, you need:

  • gcc and g++ or clang and clang++
    • on macos, you get these with xcode-select --install
    • on ubuntu, install with sudo apt install clang
  • cmake
    • on macos, you can get this with brew install cmake
    • on ubuntu, you can get this with sudo apt install cmake

After you have rust and the other build tools installed, you can install eugene with:

cargo install eugene

It is also available as a Docker image:

docker run --rm -v $(pwd):/workdir \
  ghcr.io/kaaveland/eugene:latest \
  lint /workdir

Eugene is available as a binary for Linux and macOS. You can download the latest release from the releases page. Note that the binaries are not notarized and signed for macOS, so you may need to allow the binary to run by removing its quarantine attribute:

xattr -d com.apple.quarantine eugene

Source code and issue tracker

The source code is available on GitHub, where it is also possible to report issues and suggest improvements.

eugene is licensed under the MIT license.

Usage

Eugene has a number of subcommands, and can tell you about them:

$ eugene help
eugene is a tool for writing safer schema changes for PostgreSQL

eugene can run your migration scripts and detect which locks that is taken by each
individual SQL statement and summarize which operations that conflict with those
locks, in other words what the script must wait for and what
concurrent transactions that would be blocked.


Usage: eugene [COMMAND]

Commands:
  lint         Lint SQL migration script by analyzing syntax tree
  trace        Trace effects by running statements from SQL migration script
  modes        List postgres lock modes
  explain      Explain what operations a lock mode allows and conflicts with
  hints        Show migration hints that eugene can detect in traces
  completions  Generate shell completions for eugene
  help         Print this message or the help of the given subcommand(s)

Options:
  -h, --help
          Print help (see a summary with '-h')

  -V, --version
          Print version

The two main subcommands are eugene lint and eugene trace, which both have their own page. eugene lint will perform syntax tree analysis of your SQL script using the PostgreSQL parser, while eugene trace will actually run it in a transaction and inspect the effects of the script on the database. It will be easier to get started with eugene lint and it can catch many dangerous patterns, but it may also report some false positives and might not pick up everything that eugene trace can catch.

Hints provided by eugene

See hints for a list of hints that Eugene can give you.

Blog

I frequently blog about software development and other topics, here's blog posts about egene.

Release notes

The releases page is the best place to find release notes for eugene.