Managing Database Migrations: Versioning Your SQL Schema

Previously in this course, we explored debugging PHP applications to resolve runtime issues. Now that your application logic is stable, we need to address a common "hidden" bug: database drift. If you manually run ALTER TABLE commands on your production database, you will eventually lose track of your schema state.

Database migrations turn your schema evolution into a series of repeatable, versioned scripts. Instead of "fixing" the database, you are "evolving" it through a history of changes that any team member can replay.

The First Principles of Schema Management

In a professional environment, you never touch the database schema by hand after the initial setup. Every change—adding a column, creating a table, or changing a data type—should be represented by a migration file.

A migration file usually contains two parts:

1. Up: The SQL needed to apply the change.
2. Down: The SQL needed to revert the change (if something goes wrong).

This approach ensures that your local environment, your teammate's environment, and the production server are always running the exact same schema.

Writing Your First Migration Script

Let's assume our project currently has a posts table, but we forgot to add a created_at timestamp. Instead of running a raw SQL command in a terminal, we create a file named 202310271000_add_created_at_to_posts.sql.

The filename prefix (a timestamp) is crucial. It dictates the order in which migrations are executed.

SQL
-- 202310271000_add_created_at_to_posts.sql

-- UP: Apply the change
ALTER TABLE posts ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- DOWN: Revert the change
ALTER TABLE posts DROP COLUMN created_at;

Tracking Schema Changes

To automate this, you need a way to track which migrations have already been applied. Most developers create a simple table in their database called migrations:

When your application starts or when you run a deployment script, it checks the migrations table, compares it against the files in your migrations/ folder, and executes only the files that haven't been recorded yet.

For those interested in how this scales, reading about implementing migrations: safe schema updates for SaaS provides a deeper look at handling data loss prevention.

Hands-on Exercise: Implementing a Migration Log

1. Create a migrations/ folder in your project root.
2. Create a migrations_log table in your MySQL database with two columns: id and migration_name.
3. Write a small PHP script that scans the migrations/ folder for .sql files.
4. For each file, check if its name exists in the migrations_log table.
5. If it doesn't exist, execute the SQL within that file using PDO->exec() and insert the filename into the log.

Common Pitfalls

• Editing Past Migrations: Never modify a migration file after it has been pushed to production. If you made a mistake, create a new migration to fix it. Changing history breaks other people's local setups.
• The "One-Way" Migration: If you write an UP script but fail to write a corresponding DOWN script, you make it impossible to roll back a bad deployment. Always define the revert logic.
• Mixing Logic and Data: Keep migrations focused on schema (tables, indexes, columns). Avoid using them to insert thousands of rows of seed data, as this slows down deployments significantly.

For more advanced strategies on organizing these files, refer to implementing schema migrations: a guide for scalable databases to understand how to handle large-scale database evolutions.

FAQ

Q: Should I use a migration library? A: Eventually, yes. Libraries like Phinx or Eloquent Migrations handle the heavy lifting. However, learning to build your own simple migration runner is the best way to understand how they work under the hood.

Q: What if a migration fails halfway? A: This is why we use database transactions. By wrapping your migration in a START TRANSACTION and COMMIT, you ensure that if an error occurs, the database rolls back to its previous state.

Recap

Database migrations allow you to version your schema as code. By using timestamped files and a migration tracking table, you ensure your database remains consistent across all environments. Remember: create a new file for every change, keep them atomic, and always provide a rollback path.

Up next: Protecting Against CSRF — we'll secure our forms to ensure that data submissions are coming from your actual users, not malicious actors.