The basics of managing database schema changes

Building an application requires data storage and, consequently, database schemas. When you update a released application, you must often modify these schemas. Managing these changes safely and efficiently is a fundamental engineering challenge.

This article outlines my approach to schema management, its trade-offs, and strategies for scaling to higher reliability. As a solopreneur, I prioritize simplicity and productivity. While I use Java and JMigrate, these concepts apply to any library like Flyway, Liquibase, MyBatis and across languages and frameworks like Rails.

The problem of changing database schemas

Suppose you create a user table:

CREATE TABLE "jmigrate_test_user" (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    hashed_password TEXT NOT NULL,
    password_expired_at TIMESTAMP
);

If you need to add a last_login column a week later, you could execute the SQL manually—a common practice in 1999. However, manual updates create two critical problems:

1. No Version Control: Changes are untracked. You cannot identify when the source code began supporting the new column, nor can you easily revert.
2. Team Desynchronization: You must coordinate manual updates across every teammate's local environment, which is error-prone and inefficient.

If a colleague simultaneously attempts to add an age column, the lack of automation results in a "schema collision" that is difficult to resolve.

How I solve it

Every database change should be committed to Git. Store schema changes in a dedicated folder using sequential filenames, such as 1.sql, 2.sql, and 3.sql. To modify the schema, simply add the next file in the sequence (e.g., 4.sql).

Each migration script contains two sections:

• Up: SQL code to advance the database schema.
• Down: SQL code to revert the changes made by the "Up" section.

While production environments should prohibit "Down" scripts to prevent data loss, executing them in development is essential for agility. Tools like JMigrate, Flyway, Liquibase, and MyBatis automate this process.

With JMigrate, a single call to JMigrate.migrate() at application startup handles all pending migrations.

How it works in practice

Let's walkthrough 3 real-world scenarios:

Scenario 1: You want to make a database schema change and need to iterate on the migration script

Suppose you add a last_login column using bigint (for epoch milliseconds). You create 5.sql with the following content:

# --- !Ups
ALTER TABLE "user" ADD COLUMN "last_login" BIGINT;

# --- !Downs
ALTER TABLE "user" DROP COLUMN "last_login";

After running the script, you realize a timestamp type is more appropriate. You then modify 5.sql:

# --- !Ups
ALTER TABLE "user" ADD COLUMN "last_login" TIMESTAMP;

# --- !Downs
ALTER TABLE "user" DROP COLUMN "last_login";

In development, JMigrate detects the modification. It automatically executes the Down script of the previous version (DROP COLUMN) and the Up script of the revised version (ADD COLUMN ... TIMESTAMP), ensuring your local database remains synchronized with your code.

Scenario 2: You are adding a migration script, and your co-worker is also adding one at the same time.

You are adding a new migration script 5.sql because the last previously run migration script is 4.sql.

Your co-worker is adding 5.sql at the same time and has merged his change before you do.

What happens is that there will be a git conflict, and you will have to resolve that before merging by moving your migration script to 6.sql.

In your local development environment, JMigrate will run 5.sql (from your co-worker) and 6.sql (from you) automatically. This automation ensures your local environment remains synchronized, allowing you to continue working without manual database intervention.

Scenario 3: You accidentally modify a past migration script

You accidentally modified 3.sql, which had been previously deployed.

Since forbidding executing the down scripts is set to true, JMigrate will throw an exception and fail the deployment. This is the best possible outcome because you wouldn't want to deploy a mistake.

You will get an alert and are able to revert 3.sql and make a proper fix before attempting another deployment.

Make it more reliable

At scale, the automated migration process reveals a flaw: non-backward-compatible changes cause downtime.

Renaming a column illustrates this risk. If you rename name to full_name, the existing application instances will continue to query the old column name until they are replaced. During this deployment window, the database will throw exceptions, crashing the application.

To avoid this, engineers at companies like Stripe and Google often tolerate "bad" names rather than renaming columns. When a change is unavoidable, use a multi-step deployment to maintain availability:

1. Add the new column and deploy.
2. Dual-write to both the old and new columns and deploy.
3. Backfill data from the old column to the new one.
4. Read from the new column only and deploy.
5. Remove the old column and deploy.

Note that for massive datasets, step three may take days and require specialized data-migration tooling.

JMigrate: a simple database schema migration for Java

I developed JMigrate to provide a simpler, lightweight alternative.

Feature	JMigrate	Alternatives
Simplicity	A single function call handles all migrations.	Often require complex configuration.
Integration	Pure Java; runs within the application.	Frequently require a separate CLI, which platforms like Heroku and Render.com may restrict.
Size	14 KB	800 KB (Flyway) to 3 MB (Liquibase).

JMigrate is ideal for desktop and self-hosted applications where minimal file size and architectural simplicity are paramount. Conversely, large-scale server-side applications—where teams manage their own deployments—typically prioritize extensive feature sets.

Summary

Managing database schema migrations is a fundamental engineering responsibility. While modern libraries automate best practices, engineers must understand the underlying mechanics to resolve exceptional cases, such as migration failures.

Current standards facilitate simultaneous development, rigorous testing, and seamless deployment. Whether you choose either JMigrate, Flyway, Liquibase, or MyBatis, you are now equipped to manage schema changes with confidence.