The basics of managing the database schema changes

Source: Dev.to

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 such as Flyway, Liquibase, MyBatis Migrations 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 migration files in a dedicated folder (e.g., migrations/).
• Use sequential filenames: 1.sql, 2.sql, 3.sql, …
  To modify the schema, simply add the next file in the sequence (e.g., 4.sql).

Each migration script contains two sections:

Note: Production environments should prohibit Down scripts to prevent data loss, but executing them in development is essential for agility.

Tools such as JMigrate, Flyway, Liquibase, and MyBatis Migrations automate this process. With JMigrate, a single call to JMigrate.migrate() at application startup handles all pending migrations.

How it works in practice

Scenario 1 – Iterating on a migration script

You want to add a last_login column using BIGINT (epoch milliseconds). Create 5.sql:

# --- !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. 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 previous Down script (DROP COLUMN) and then the revised Up script (ADD COLUMN … TIMESTAMP), keeping your local database synchronized with the code.

Scenario 2 – Simultaneous migrations by two developers

• You add 5.sql because the last migration applied was 4.sql.
• Your coworker also adds a 5.sql and merges it first.

Git will report a conflict. Resolve it by renaming your file to 6.sql.

In each developer’s local environment, JMigrate will run 5.sql (your coworker’s) followed by 6.sql (yours) automatically, ensuring the database stays in sync without manual intervention.

Scenario 3 – Accidentally modifying a past migration

You edit 3.sql, which has already been deployed. Because “execute down scripts in production” is disabled, JMigrate throws an exception and aborts the deployment. This is the safest outcome—you receive an alert, revert the erroneous change, and create a proper fix before redeploying.

Making migrations more reliable

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

Renaming a column is a classic example. If you rename name to full_name, existing application instances will continue to query name until they are redeployed, causing runtime exceptions.

Many engineers (e.g., at Stripe, Google) avoid renames altogether, preferring to keep “bad” names. When a rename is unavoidable, use a multi‑step deployment to maintain availability:

1. Add the new column and deploy.
2. Dual‑write to both old and new columns and deploy.
3. Backfill data from the old column to the new one (may take days for large tables).
4. Read from the new column only and deploy.
5. Remove the old column and deploy.

JMigrate: a simple database‑schema migration library for Java

I created JMigrate to provide a lightweight alternative to the larger tools.

(The table can be expanded with additional rows as needed.)

TL;DR

• Store every schema change as a numbered SQL file in version control.
• Keep up and down sections in each file.
• Run migrations automatically at startup (e.g., JMigrate.migrate()).
• Use multi‑step deployments for non‑backward‑compatible changes.

Following this pattern gives you a reliable, auditable, and collaborative way to evolve your database schema—no matter the language or framework you’re using.

Migration Tools Overview

Use‑case guidance

• JMigrate is ideal for desktop and self‑hosted applications where minimal file size and architectural simplicity are paramount.
• Large‑scale server‑side applications—where teams manage their own deployments—typically prioritize extensive feature sets, making Flyway or Liquibase a better fit.

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 JMigrate, Flyway, Liquibase, or MyBatis, you are now equipped to manage schema changes with confidence.