Implementing Migrations: Safe Schema Updates for SaaS

Previously in this course, we covered Schema Versioning Basics: Managing Database Evolution for SaaS, where we established the "why" behind tracking changes. Now, we move into the "how": writing and applying actual migrations to your production-bound SaaS database.

Managing schema updates is one of the most high-stakes tasks you'll perform as a developer. A single typo in a DROP COLUMN command can result in catastrophic data loss. To avoid this, we treat database changes as code: version-controlled, testable, and idempotent.

The Anatomy of a Migration File

A migration file is simply a SQL script that performs a specific, atomic change to your database structure. In professional workflows, these files are prefixed with a timestamp or a sequential number to ensure they are applied in the correct order.

A robust migration typically contains two parts:

1. Up: The SQL commands to apply the change (e.g., ALTER TABLE).
2. Down: The SQL commands to revert the change (e.g., ALTER TABLE to drop the column you just added).

Worked Example: Adding a "Phone Number" Column

Let's assume our SaaS needs to capture a phone number for the users table we built in Implementing the User Table: A Practical Guide to SQL DDL.

File: 202310271000_add_phone_to_users.sql

SQL
-- Migration: Add phone column to users
-- UP: Apply the change
ALTER TABLE users ADD COLUMN phone_number VARCHAR(20);

-- DOWN: Revert the change
-- ALTER TABLE users DROP COLUMN phone_number;

When you apply this, you modify the table structure. Because you are using ALTER TABLE rather than deleting or recreating the table, your existing data remains intact.

Best Practices for Safe Schema Updates

To ensure version control remains effective and your data stays safe, follow these principles:

Hands-on Exercise: Implementing a Subscription Status Update

In our project, we previously set up Implementing Subscription Tables: SQL Constraints for SaaS. Let's say we now need to add an updated_at timestamp to the subscriptions table to track when a user last changed their plan.

1. Create a file named 202310271100_add_updated_at_to_subscriptions.sql.
2. Write the SQL to add a column named updated_at with a default value of the current timestamp.
3. Write the corresponding "Down" logic as a comment.

Hint: Use ALTER TABLE subscriptions ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;.

Common Pitfalls

• Renaming Columns: Renaming a column is effectively a "drop" and "add." If you have application code expecting the old name, it will break. Always add the new column first, deploy code that writes to both, migrate the data, then remove the old column.
• Default Values on Huge Tables: Running ALTER TABLE ADD COLUMN ... DEFAULT 'some_value' on a table with millions of rows can lock the database for a long time. In large-scale systems, we add the column as nullable first, then update rows in batches.
• Ignoring Constraints: Adding a NOT NULL constraint to a column that currently has null values will cause the migration to crash. Always clean your data before applying strict constraints.

FAQ

Q: Do I need a specialized tool for migrations? A: While you can run SQL files manually, I highly recommend using tools like Liquibase or Flyway (as discussed in Kubernetes Database Migrations: Automating Schema Updates with Liquibase) to track which migrations have already been applied to your database.

Q: Can I use DROP TABLE in a migration? A: Only if you are absolutely certain you want to destroy that data. In a production environment, prefer renaming or archiving data.

Recap

We’ve learned that migrations are the bridge between your evolving business requirements and your database state. By creating atomic, versioned SQL scripts, you ensure that your schema updates are predictable and reversible. Always test your scripts, keep them small, and treat your database schema with the same care as your application code.

Up next: We will perform a final schema review to ensure our SaaS database architecture is fully normalized and ready for production.