Tasks of a schema migration tool

The last time I wrote about how database schema migration tools could do more to help us. The way I see it, any schema migration solution has to cover three tasks: creating, managing, and executing migrations.

Creating migrations. The first task is to create a migration script. If your scripts consist of DDL commands in SQL files, you’ll probably write them by hand. A linter like Squawk can help you to ensure the migrations do not cause unnecessary downtime.

Things get more interesting if you can describe your target schema in another programming language or as data. For example, when using Django’s ORM or automigrate for Clojure, the tool generates the migration automatically by comparing the target schema to the current schema. Sometimes you may need to edit the generated migration by hand, but mostly the tool does the job for you.¹

Managing migrations. You need to keep track of which migrations have been applied to each of your databases (local, staging, production). When you want to migrate a database, the tool is able to give you a list of required migrations and the order in which they should be performed. The tools tackling this problem end up having essentially same features.

One problem that migration managers need to solve is how to support multiple developers creating migrations at once. If you number your migrations incrementally like Flyway does, migrations in concurrenty-developed branches may end up using the same number.

To avoid this, some tools such as dbmate and Migratus use timestamps in migration names. Ben Kuhn’s sketch of a migration tool has an alternative solution: it uses incrementing numbers, but it provides a rebase command to automatically renumber the migrations based on Git history. That’s a nice touch!

Executing migrations. At the basic level this means running the DDL commands from your SQL files. However, as we learned the last time, you need to be careful to avoid downtime and do things like set lock_timeout in PostgreSQL.

While every tool that manages migrations can also execute them, there are specialist tools such as gh-ost for MySQL which focus only on executing migrations in a robust way without touching any of the other tasks.

Again, not using DDL for migrations seems to open the door for innovation. For example, Reshape for PostgreSQL has a novel way of executing migrations: by using schemas, views, and triggers, it allows you to use both the old and the new schema at the same time. This means that your application does not have to be backwards-and-forwards compatible with database changes.

As seen, there are plenty of tools out there for dealing with these tasks. There’s no one-size-fits-all solution. If your needs are simple, a simple tool will suffice. However, if you’re looking to ensure safety and efficiency, you should look further.

Schema migrations and avoiding downtime

If you’re developing an application that is backed by a SQL database, sooner or later you will need to do a schema migration. Maybe you’ll need to add a new table or a new column, create a new index, or change some constraint.

Luckily there are plenty of tools to help you! Migratus for Clojure, Flyway for Java, and dbmate as a more language-independent option are examples of the most common design pattern: you write migrations as DDL commands in SQL files and the tool keeps track of which of the migrations have been applied to the database.

Let’s say you forgot to make the e-mail field of your user table unique and now you want to fix it. With these tools, the migration could look something like this:

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

Many object-relational mappers such as Django’s ORM and ActiveRecord from Ruby on Rails exhibit another pattern: the migrations are written using a DSL in the ORM’s programming language (Python and Ruby, respectively). Here’s what the migration above could look if it was created with Django:

from django.db import migrations, models

class Migration(migrations.Migration):
    operations = [
        migrations.AlterField(
            model_name='user',
            name='email',
            field=models.TextField(unique=True),
        ),
    ]

One tricky thing about migrations is that to alter a table, you have to lock it potentially for a long time. This means that migrations can cause downtime. Braintree has published a great guide for avoiding downtime with PostgreSQL migrations but even then you have to be careful.

For example, when you add a uniqueness constraint in PostgreSQL, an index gets created. The guide advises that the migration above should be done in two steps to avoid grabbing an exclusive lock while the index is being created:

1. First, create a unique index using CREATE INDEX CONCURRENTLY.
2. Then, add the constraint with USING INDEX which only requires a short-lived exclusive lock to alter the table’s metadata.

If you’re like me, you might think that it’d be a great idea to encode all this knowledge into a tool. And it turns out that people have done it!

There are linters such as Squawk that check your SQL files. Here’s what Squawk has to say about our migration:

example.sql:1:0: warning: disallowed-unique-constraint

   1 | ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

  note: Adding a UNIQUE constraint requires an ACCESS EXCLUSIVE lock which blocks reads.
  help: Create an index CONCURRENTLY and create the constraint using the index.

Seems good – this is exactly what I was looking for.

Since Django migrations are specified on a higher level, could it do this kind of tricks automatically? Possibly, but as far as I know, currently it doesn’t. For ActiveRecord, there’s pg_ha_migrations which implements these ideas.

Tools like gh-ost and pt-online-schema-change for MySQL are another stab at this problem. They’re only concerned with running ALTER TABLE statements in a safe, robust way. The problem of managing migrations is left to you.

My point is that be careful with your migrations but my meta-point is this: migrations of heavily-loaded databases take skill and your tools can help you. I’ve heard it said that all you need for migrations is a bunch of SQL files in a directory. It goes a long way, yeah, but you can go further.

Clojure and what could've been

Epistemic status: Anecdotes and opinions.

I’ve used Clojure for over a decade now: I first learned it in 2012 and started using it professionally in 2013. I’ve been reflecting on what has happened and what the future looks like. In this post, I want to share a few musings about it.

Missed opportunity: production REPLs

REPL is so central to Clojure that people even talk about REPL-driven development. It’s not just about having a prompt for typing commands like in Python¹. Instead, it’s a way of connecting your editor to a running Clojure system.

Usually this is done only during the development. You could connect to the REPL of production system to debug or maintain it, if you dare. There are stories about adventurous Clojure developers doing to hotfix a running system.² Usually these stories end with a word of caution: the devs will tell you about a time when they forgot to commit the fix to the version control system and the changes were lost the next time the system was restarted.

You used to be able to do it, anyway. Nowadays when the standard model of deploying software is ephemeral containers in a Kubernetes cluster, the production REPL is less meaningful. If you need to apply the same hotfix to eight different containers and Kubernetes can re-schedule them at any moment, there isn’t much point to this. Your services are now cattle, not pets.

At Metosin, we had this belief that a small but skilled team using sharp tools can deliver better software faster than an ordinary team relying on standard tools. The recent blog post by Tailscale about their database choices seems like an example of this: SQLite is an unusual choice for their setup, but they know what they’re doing and it’s working great.

I wonder if we’ve missed a similar opportunity here. Ephemeral containers have their benefits, especially for scaling, but do they always outweight the productivity benefits of using production REPLs for debugging?³

FaaS did not kill Clojure

A few years ago, Function-as-a-Service (FaaS) platforms such as AWS Lambda were surging in popularity. I thought it could be the end of Clojure for web services. Replacing all the glue code of web services with an API gateway and some lambdas seemed like a big win: all your code is about the business logic; the infrastructure has been abstracted away. I didn’t see what Clojure could offer here: it’s startup time was slow and REPL-driven development didn’t fit the FaaS platforms.

I was wrong. People still develop long-running server software just as they did in 2015. AWS Lambda became a popular tool scripting AWS services and for connecting them to each other, but it didn’t replace traditional web backends. As far as I understand, the startup time problems have been mitigated, too, and Clojure lambdas are a feasible choice nowadays.

Thinking back, I expected AWS Lambda developer experience to be rapidly improved. This didn’t happen and it still clunky if you have a lot of lambdas. Frameworks such as Serverless tried to smooth it out, but they never hit the big time. Is this another missed opportunity?

JavaScript is taking over ClojureScript

There’s a lot to like about developing browser applications with ClojureScript. Tools such as shadow-cljs and Google Closure Compiler are great – I’ll take them any day over configuring webpack. There are a bunch of good libraries such as Reagent, and of course the language itself is nice.

Despite the rough spots such as JavaScript interop and awkward testing tools, for a long time ClojureScript was a clear winner over JavaScript for me. Today, I’m not so sure about it. JavaScript language, tooling, and ecosystem have improved immensely over the last decade.⁴

In some ways, the JavaScript experience is superior now. One essential feature is async/await syntax for asynchronous programming. It’s much smoother than using core.async or promises and looks like we’re not going to have it in ClojureScript.

Another one is TypeScript. JavaScript ecosystem is strongly embracing TypeScript and it’s enabling powerful static analysis. I’m not seeing an easy way for ClojureScript to benefit from that.

JavaScript has improved so much that it’s harder for me to look over ClojureScript’s rough spots now. I certainly feel that JavaScript is overtaking ClojureScript. Time will tell if I’m wrong once again.