Automated database migrations?

I’m learning about web development and I’m still learning about working with databases. From what I’ve read it seems like a good idea to develop my database schema over time with migrations. However, I’m not sure when to apply the migrations.

Applying migrations manually seems error-prone, so I’m wondering if there’s an automated way to apply migrations. If I’m using something like Integrant do I check for new migrations and apply them during init-key for my database? How does this look different in development vs production?

Migration process should be a part of the deployment process and not the startup process. E.g. I deploy an app to Heroku - there, the migration is done with migratus during Heroku’s release stage, just because there’s a single line in Procfile that Heroku cares about.

1 Like

We have migrations as a first class thing in our git repository. We use mysql, and use the ragtime clojure library to kick off the migrations.

At the current, we do that at JAR startup, ie. just call a fn of the likes of (run-migrations).

Since it’s idempotent and we only have a single deployment, this is fine.

As @p-himik says, it’s probably a bit better to move this to the deployment pipeline, but having said that, there are also benefits if being able to run this from the REPL.

There’s many ways to Rome, as they say :slight_smile:

One way to think about migrations is that they are a way to keep in sync the database with the needs of your code.

If the new version of the application needs a new column, then the database has to be prepared before the new version application start.

That’s why that preparation should happen during the deployment of a new (or old in case of a rollback) version of the application.

Being able to affect change to a database via the REPL in a development environment is very productive and therefore desirable.

Using a migrations library (migratus, ragtime) provides a repeatable way of applying database changes through dev, test, staging and production environments.

Migration scripts can be created from the REPL experiments once the scheme design or change is finalized. Then the migrations can be tested.

In development, migrations could be included in Integrant REPL and driven from the REPL, but not sure if this is useful. I wouldn’t include migrations in Integrant itself, which is responsible for starting the system components in all environments.

As has already been mentions, migrations should not be run during application startup. A failing migration can cause a startup loop to occur, potentially causing degradation of the database.

I’ve seen one migration script seriously degrade a database by multiple startup loops, causing a large number of connections, affecting many other applications that accessed the same database server.

Service startup time in cloud deployment can also be critical, so migrations should be separate, as startup time typically has (should have) short timeouts.

Hope this helps.