Renaming database fields is ureasonably hard

Renaming a field in your database is perhaps the most fundamental schema change, yet doing so safely is really hard with most databases. Take MongoDB for example. Its schemaless nature means that renaming a field must be done with an update for each record in the database. During this migration, some of your records will have the new name and some the old, and your application will need to be updated to handle them both, unless you simply take your application down for maintenance until all records have been updated.

Databases with stricter schemas, like Postgres, technically makes this easier. This simple SQL operation does the job:

ALTER TABLE user
RENAME COLUMN full_name TO name;

Even though your database will make the change almost immediately without having to update any rows, it’s not that easy. The old version of your application expects the old name and will start to error the moment the change has been made. The new version of your application expects the new name, and hence will error until the change has been made. This means a gradual rollout is not possible, you will have to take your application entirely offline, make the change and then rollout the new version, resulting in some downtime.

Achieving a zero-downtime rename requires a much more complicated procedure:

1. Add a new column with the new name
2. Update your application to write to both the new and old column for every insert and update
3. Write, run and wait on a backfill script which copies all data from the old to the new column
4. Update your application to read from the new field and stop writing to the old one
5. Finally remove the field with the old name

This means several separate deploys, a ton of manual work and leaves plenty of room for human errors, just to rename a field! If you are using Postgres there is a nice trick around this though by using views, which I’ve written about previously. I even built Reshape on this technique which although powerful, is still greatly limited by the design of Postgres.

After building Reshape and going deep on the subject, I’ve become convinced that our databases can and should help us make any kind of change–from a simple rename to complex data migrations–simple, safe and zero-downtime. Renaming a field in your database shouldn’t be any harder than renaming a variable in your code! To try and make this a reality, I’m building ReshapeDB. What does a rename look like in ReshapeDB? It’s as simple as updating the declarative schema file.

model "User" {
-   field "full_name" {
+   field "name" {
        type = "string"
    }
}

Once you apply this change to your database, ReshapeDB will not immediately rename the field but will instead support both the old and the new name for the field. This works because each client tells ReshapeDB what schema it expects, so for the existing deployed version of your application, it will keep accepting and using full_name, and for the new version you are rolling out, it will expect name. This way, you can gradually roll out your application changes without any downtime, and if something breaks, you can roll back in seconds.

Of course, I’m not just aiming to solve the renaming problem with ReshapeDB. By building a database from the ground up with change in mind, we can greatly simplify all kinds of schema and data migrations that would normally require a ton of manual, error-prone work. I’ll be writing more about those capabilities soon but if you’d like to know more or just chat about the subject, please reach out!