Drogon doesn’t have database migrations, so I built one. Before I show you how it works, though, let’s address the two elephants in the room:
- Why doesn’t Drogon have database migrations? It actually makes sense once you understand how its ORM is designed
- If it makes sense… then why did I build one, anyway?
Hint: it’s all about running Drogon across multiple servers and databases…
Why Doesn’t Drogon Have Database Migrations Built-In?
Why doesn’t Drogon have migrations in the first place? Because nobody has written it yet… Just kidding. There’s more to it
Drogon’s ORM works differently to other ORMs. Other ORMs that I’ve encountered have tools to generate both database and code from a description (e.g., Ruby on Rails). Or, you add annotations directly to your source-code that get turned into database tables.
Drogon does the opposite. You write the database schema in SQL, create an empty database from that SQL code, and then run drogon_ctl create model to generate the C++ class code for each table in that database.
Updating the database works the same way: You write SQL code to modify the database’s schema, execute it on the database, and run drogon_ctl again
Congratulations, you’ve already got your database migration in SQL form! All you need to do now, is execute it on the production database directly, and you’re done! No fancy migrations system necessary..
Makes sense, right?
Why I Still Want Automated Database Migrations
That works fine for a single server. But what if you’re running many servers? Or a SaaS product with per-tenant databases. Every database needs the updates applied, in the right order. One mistake and you’ve broken the database.
This is why we need automated migrations. Or, at least, why I need it. It simply doesn’t scale well.
So, I’ve come up with a simple solution:
- Put all your SQL migrations into files with version numbers (e.g., v1_create_users_table.sql, v2_add_admin_flag.sql, etc.)
- Add a version number to the database. This could be sqlite’s user_version, or a special table
- When a server opens a database, it reads the version, scans the migrations folder, and executes all the missing SQL files
Each of those migrations is done in a transaction, so that it either succeeds or rolls back. No half-done migrations. No corruption.
The database is now always in sync with the code.
Nothing fancy, or ground-breaking. Just simple and reliable. It works great, provided that all your database migrations can be expressed in SQL. If a migration needs data manipulation that can’t be done in SQL, then you’re going to need something else.
For my purposes, though, this is fine. At least, I don’t expect that I’ll ever have migrations that SQL can’t do…
My code is also currently tied to SQLite. This works for me, because I’m planning to use SQLite in production. I’ll say more about that in a future video.
For now, though, I’m happy with my a simple database migrations system for Drogon. It’s going to come in very handy for my future Drogon based SaaS product, which again, I’ll say more about later, when I start building it.
Make sure you’re subscribed, and I’ll see you next time.