Jul 7, 2021

Release: Oh! Krab! A PostgreSQL tool written in Go based on HCL

I am happy to announce that I have finally finished MVP of my tool for PostgreSQL - Oh! Krab! 🦀. It’s far from perfect but I needed to start somewhere. There’s more to come.

Why?

So, the natural question is why I’ve created this in the first place. There are a few reasons.

First, I like experimenting with different technologies and sometimes I would like to compare multiple solutions that use PostgreSQL as a database, but writing migrations more than once in different frameworks or programming languages is a pain. Managing database through other framework migration system is not an option for me.

Secondly, I get hired from time to time to do small jobs in PostgreSQL (queries, schema design, etc.) and very often I don’t have access to the full project so I need to mock everything. In those situations I would write some Bash scripts that mimic this particular scenario - boooooring!

Another use case is when I do workshops or teach about PostgreSQL - I really want to work with pure SQL in a manageable fashion, frameworks and ORMs would only make the image blurry.

Last, but not least, is the old project that I’ve worked on. It was written in Ruby on Rails and used Apartment gem to manage tenants (for non-Rubists: Apartment is a library that allows to have different strategies for multitenancy and integrates with Rails ORM). It perfectly matched our scenario since we had small number of tenants and we knew it wouldn’t grow too much so we went with schema-based approach (single database with 1 schema per each tenant and public schema for other stuff). So, what’s wrong with the Apartment then? Well, it’s not maintained anymore and if I remember correctly you couldn’t upgrade PostgreSQL above version 10 because the way it works is by creating a dump of a database and restoring it to a new schema and in PostgreSQL 11 dump behavior has changed a little and that prevented Apartment to create a valid restore - I think this issue was later addressed by someone in the community with a workaround but I’ve never tested it. In the past I did a presentation on how to replace Apartment with simple SQL using event triggers so feel free to check it out: Schema-based multi-tenancy in PostgreSQL - this should work in simple use cases, but it depends what do you need.

It’s hard to write about other reasons since “Krab” is too small right now compared to what I have planned but I think I will expand on the topic in the future releases as more features are added.

Introduction to the software itself

I’ve tried my best to provide very detailed documentation for Krab so you can start there but if anything is unclear, please let me know so I can make it better.

Currently Krab only supports one feature - migrations - which again is not polished enough to my liking but it’s a starting point.

Let’s start by defining a docker-compose.yml file so we can run database:

version: "3"
services:
  pg:
    image: postgres:12.3-alpine
    restart: always
    ports:
      - 5432:5432
    environment:
      POSTGRES_PASSWORD: secret
      POSTGRES_USER: krab
      POSTGRES_DB: krab

Then start the container:

docker-compose up

Next, install the Krab, possible options of how to do it are here. For local development prefer asdf version. Try in your terminal:

krab -version

It should print the active version, e.g.: 0.2.4.

Then we define the configuration, it can be done in separate files as your project grows but for now let’s create one file default.krab.hcl:

# 1
migration "create_animals" {
  version = "20200707_1703"

  up {
	sql = <<SQL
      CREATE TABLE animals(
        id   BIGINT GENERATED BY DEFAULT AS IDENTITY,
        name VARCHAR
      )
    SQL
  }

  down {
	sql = "DROP TABLE animals"
  }
}

# 2
migration "seed_animals" {
  version = "20200707_1704"

  up {
	sql = "INSERT INTO animals(name) VALUES ('Turtle')"
  }

  down {
    sql = "DELETE FROM animals WHERE name = 'Turtle'"
  }
}

# define the migration set
migration_set "default" {
  migrations = [
    migration.create_animals,
    migration.seed_animals,
  ]
}

The file above contains two migration resources and one migration set resource.

Because we defined default migration set in config files, you should see it as a subcommand:

krab migrate up
# it won't run migrations because <set> argument is needed,
# instead it will print the subcommands

So the output will be the list of available sets to migrate:

This command is accessed by using one of the subcommands below.

Subcommands:
    default    Migrate `default` up

Let’s run the migrations:

DATABASE_URL="postgres://krab:secret@localhost:5432/krab?sslmode=disable" \
  krab migrate up default

DATABASE_URL environment variable is required, read more here.

It’s important to note that migrate up command will execute migrations in the order defined by the migration set and not by their lexicographical scope - which is different than what most alternatives do. Version here only acts as a unique identifier, its “sortability” does not matter.

Now, let’s connect to psql and verify everything:

psql "postgres://krab:secret@localhost:5432/krab?sslmode=disable"

All migrations should be there:

krab=# select * from schema_migrations;
    version    
---------------
 20200707_1703
 20200707_1704
(2 rows)

As well as our turtle 🐢

krab=# select * from animals;
 id |  name
----+--------
  1 | Turtle
(1 row)

Finally, let’s test the migrate down command:

DATABASE_URL="postgres://krab:secret@localhost:5432/krab?sslmode=disable" \
  krab migrate down default 20200707_1704

And verify it:

krab=# select * from schema_migrations;
    version    
---------------
 20200707_1703
(1 row)

And the turtle 🐢 is gone:

krab=# select * from animals;
 id | name 
----+------
(0 rows)

You can stop the docker container now.

Production?

There are still some things that I would like to improve so I would be a bit hesitant to use that in production environment, unless you know what you are doing. One of the reason is that currently all migrations run within transaction which will cause trouble when running concurrent operations like: CREATE INDEX CONCURRENTLY ... - I need to allow to specify option like transaction = false for migration resource so that everything works.

For now treat it as a toy and provide feedback if you wish.

Roadmap for the nearest future

In the upcoming releases I would like to tackle these things:

  • adding more customization around migration execution (e.g. transactions)
  • improving existing CLI (better progress tracking, config generation)
  • building some DSL for migrations (not only raw SQL)
  • providing mini test framework (yes, I need that so much)
  • adding an ability to create custom actions with arguments
comments powered by Disqus