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
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
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
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.
You could describe the target schema with a CREATE TABLE command and have a tool generate the migration by comparing that against the current schema. I’m not aware of such tool, however. ↩︎
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
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
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:
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:
First, create a unique index using CREATE INDEX CONCURRENTLY.
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.
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 Python1. 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.2 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?3
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?
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.
have improved immensely over the last decade.4
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
and it’s enabling powerful static analysis. I’m not seeing an easy way for
ClojureScript to benefit from that.
ClojureScript. Time will tell if I’m wrong once again.
Python REPL leaves a lot to be desired if you’re used to Clojure
REPLs, but to give it credit, Python has popularized another innovative way of interacting with a
live system: notebooks. ↩︎
As a teenager, I fixed my websites by editing the PHP files directely on
the server over SFTP. Now that was continuous delivery. ↩︎
Another thing I wonder about is what Erlang and Elixir are doing here.
Their support for distributed systems is on the next level. If you have seen a
good article on how Erlang and Elixir people deploy and interact with their
production systems, please send it my way! ↩︎
Admitedly you may still need to use webpack, and it’s still difficult to configure. ↩︎