Database Migrations & Schema Management

What happens when your application changes? New features often need new places to store data, or changes to existing data structures. This is where managing your database schema comes in.

A database schema is like a blueprint for your database. It defines the tables, columns, relationships, and constraints that structure your data.

As your application evolves, so must its schema. Manual changes can be risky and inconsistent across environments.

Database migrations are version control for your database schema. Think of them like code commits, but for your database structure.

Each migration is a set of instructions (usually SQL) that describes how to change the database from one version to the next. They are typically timestamped and applied in order.

• `up` script: Applies a change (e.g., add a table).
• `down` script: Reverts a change (e.g., drop a table).

Using database migrations offers several key advantages for development teams:

• Consistency: Ensures all developers and deployment environments have the same database schema.
• Collaboration: Makes it easy for multiple developers to contribute schema changes without conflicts.
• Rollback Capability: Allows you to easily revert to a previous schema state if something goes wrong.
• Automation: Integrates smoothly into your CI/CD pipeline for automated deployments.

In the Clojure ecosystem, a popular and robust library for managing database migrations is Migratus.

Migratus is flexible, supporting SQL and even Clojure-based migrations. It keeps track of applied migrations in your database, ensuring they are only run once and in the correct order.

It's designed to be simple to configure and use, fitting well with Clojure's functional approach.

To use Migratus, you first add it as a dependency and configure it. Here's a typical setup using deps.edn and a configuration map:

First, add the dependency to your deps.edn:

{:deps {org.clojure/clojure {:mvn/version "1.11.1"}
        migratus/migratus {:mvn/version "1.0.10"}
        org.postgresql/postgresql {:mvn/version "42.6.0"}}}

Then, create a migratus-config.edn file (or similar) to define your database connection:

{:store :database
 :migration-dir "resources/migrations"
 :db {:classname   "org.postgresql.Driver"
      :subprotocol "postgresql"
      :subname     "//localhost:5432/myapp_db"
      :user        "dbuser"
      :password    "dbpass"}}

Migrations are usually SQL files placed in a designated directory (e.g., resources/migrations as configured earlier). Each migration consists of an "up" and a "down" script.

Migratus expects files named like YYYYMMDDHHMMSS-migration-name.up.sql and YYYYMMDDHHMMSS-migration-name.down.sql.

The timestamp ensures a unique order. You can generate these using a command-line tool, typically migratus create <name>.

The .up.sql file contains SQL commands to apply the schema change. This example creates a new users table.

-- resources/migrations/20231027100000-create-users-table.up.sql

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(255) NOT NULL UNIQUE,
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

This script defines the initial structure for our users.

The .down.sql file contains SQL commands to revert the schema change made by its corresponding .up.sql. This example drops the users table.

-- resources/migrations/20231027100000-create-users-table.down.sql

DROP TABLE IF EXISTS users;

It's crucial that the `down` script precisely undoes the `up` script, allowing for safe rollbacks.

Once your migration files are ready and `migratus` is configured, you can apply your migrations.

Typically, you'd use a command-line tool like Leiningen or clj with an alias configured for `migratus`.

To apply all pending migrations:

lein migratus migrate

or if using clj with a migratus alias:

clj -M:migratus migrate

Migratus will run all `up` scripts that haven't been applied yet, in order.

Sometimes you need to undo a migration, perhaps due to an error or a change in requirements. Migratus makes this easy.

To roll back the last applied migration:

lein migratus rollback

This command will execute the `down` script of the most recently applied migration. You can call it multiple times to roll back further.

Always ensure your `down` scripts are safe and idempotent!

Understanding the purpose of up and down scripts is key to effective schema management.

We've explored the critical role of database migrations in managing schema changes in Clojure projects. You learned:

• What database schemas and migrations are.
• The benefits of version-controlled schema changes.
• How to use `Migratus` to define and apply migrations using `up` and `down` SQL scripts.
• Commands to apply and roll back migrations.

With migrations, your database schema can evolve alongside your application code in a controlled and reliable way!