Today’s topic is database migrations, something I’m learning right now.
No prior knowledge needed. Let’s start with a story.
THE DAY THE APP CRASHED
Meet a small development team. There’s a frontend developer, a backend developer, a QA engineer, and a team lead who works on both frontend and backend.
One morning, the team lead needs to add a new column to the Student table in their MySQL database. He opens MySQL on his own computer, runs a command, and the column appears. Then he updates the backend code (the model, the service, and the controller) to use the new column. Everything works on his machine. He commits the code and pushes it to GitHub.
After lunch, the rest of the team pulls the latest code and starts the application.
It crashes 😒. ohhhh!
Why? The new backend code expects the Student table to have the new column. But that column exists only in the team lead’s database. Everyone else’s database still looks the way it did yesterday.
The code travelled through GitHub. The database change didn’t. It stayed on one laptop.
Git solved this problem for code. Migrations solve it for the database.
WHAT IS A DATABASE MIGRATION?
A migration is a small file that describes one change to the database, such as “create the Student table” or “add an email column to Student.”
Instead of typing changes directly into the database, you write them in a file, give it a number, and save it in the project, right next to your code. So when your teammates pull from GitHub, they get the code change and the database change together.
A migration tool then reads these files and applies them to each database in the right order, exactly once.
A LITTLE HIST.
These ideas aren’t new. Around 2000, Pramod Sadalage developed them while working on a large project at ThoughtWorks. He and Martin Fowler described them in the article “Evolutionary Database Design” (first published in 2003, updated in 2016). One of its key rules is that every database change should be a migration.
Ruby on Rails helped make the word “migration” popular in the mid-2000s. Many tools followed, including Liquibase (2006), Flyway (2010), and built-in migration systems in frameworks like Django, Laravel, and Entity Framework, and in ORMs like Prisma.
A QUICK CLARIFICATION
You’ll see two terms.
Schema migration means changing the structure of a database, such as adding a table, a column, or an index. This is today’s topic.
Data migration is used in two ways. Sometimes it means updating existing data as part of a change, for example filling a new column for rows that already exist. That is also done with migration files. Other times it means moving data from one system to another, such as from MySQL to PostgreSQL or from on-premises servers to the cloud. That’s a different job, with different tools.
LET’S TRY IT WITH FLYWAY
I practised with Flyway (the free Community edition) and MySQL/MariaDB. Here’s the process in simple steps, without code.
Step 1. Prepare the database Start a database server and create an empty database for the project, plus a user account Flyway can log in with. Flyway manages what goes inside the database. The server, the user, and the password are set up separately. Usually, each developer has their own local database.
Step 2. Install Flyway Download Flyway Community edition, then check that it works by asking for its version in the terminal.
Step 3. Create a project folder Make a folder for your migration files. A separate folder just for migrations keeps things tidy.
Step 4. Tell Flyway how to connect Create a config file called flyway.toml. It tells Flyway which database to connect to, which username and password to use, and where the migration files are. Be careful with that last one. If the location is wrong, Flyway connects fine but finds no migrations. It’s a very common beginner mistake, and I made it myself.
[environments.default]
url = "jdbc:mysql://localhost:3306/"
user = "your username"
password = "user password"
schemas = ["shop"]
[flyway]
locations = ["filesystem:migration_folder_location"]
Step 5. Write your first migration Create a file with one change in it, such as creating a table. The name follows a fixed pattern. It starts with a V, then a version number, two underscores, a short description, and finally .sql. For example, V1__create_student_table.sql The version number decides the order.
Step 6. Run it Run “flyway migrate.” Flyway applies your change and also creates its own table called flyway_schema_history.
flyway migrate
Step 7. Check the status Run “flyway info.” It lists every migration and shows whether it has been applied (Success) or is still waiting (Pending).
flyway info
Step 8. Next change, new file Need a new column? Don’t edit the old file. Create a new one with the next number, like V2__add_email_to_student.sql, and run migrate again. Flyway sees that V1 is already done, skips it, and applies only V2.
Step 9. The golden rule Never change a migration file after it has been applied. Flyway saves a fingerprint (called a checksum) of every file it runs. If an old file changes, Flyway notices and stops with an error. Need a fix? Write a new migration.
Step 10. Data changes go in migrations too If a change needs existing data updated, such as filling a new column for old rows, adding fixed lists like countries, or fixing bad data, put that in a migration file as well. Then every database gets the same update in the same order.
Step 11. Start fresh while practising On a practice database, the clean command wipes everything so you can rebuild from V1. It’s turned off by default because it deletes all data. Never use it on a real database.
If you use Spring Boot, it’s even easier. Add the Flyway dependencies and put your migration files in src/main/resources/db/migration. Flyway then runs automatically every time the app starts.
THE SECRET IS THE HISTORY TABLE
The flyway_schema_history table is the heart of it all. Every database keeps its own copy, so every database “remembers” which changes it already has. That’s how each migration runs exactly once, on your laptop, on your teammate’s laptop, on the test server, and in production.
BACK TO OUR TEAM
Let’s replay the story, this time with migrations.
The team lead writes a migration file that adds the new column, and he commits it together with his backend changes. The team pulls from GitHub, runs Flyway (or just starts the Spring Boot app), and their databases get the new column automatically. The app starts. No crash.
Same team, same story. No more “but it works on my machine. 😊”
Want to go deeper? Read the original article by Martin Fowler and Pramod Sadalage here https://martinfowler.com/articles/evodb.html
Top comments (0)