For those who don't use ORMs in large projects:
What's your preferred approach to store table definitions and migrations? Raw SQL queries there too? Doesn't it make them more susceptible to mistakes?
What's your preferred approach to store table definitions and migrations? Raw SQL queries there too? Doesn't it make them more susceptible to mistakes?
I like Flyway over some others I’ve tried (like Liquibase) because it supports straight up, plain ol’ SQL code for migrations and uses very simple file naming conventions to define versions and repeat operations.
The advantage we have being a Java shop is that we can also write our migrations in Java code, or mix and match. I’ve only ever done Java based migrations sparingly, all involved a big migration that involved sorting and sanity checking a lot of existing data.
For PHP/Laravel I’ve really liked Blueprint.
Although I like Flyway better than Liquibase, Liquibase can do a lot that Flyway can’t and is certainly worth looking at.
I like the control of migrating my DDL by hand instead of the code first approach, and then having some ORM randomly changing data on boot.
We use Spring Boot w/ Hibernate and JPA a fair bit. We always turn off the code that would have the code manipulate our DDL, and we do write a lot of queries by hand, but we still get a lot of auto-magical stuff for free.
And there is nothing in Flyway that Liquibase can't do and there is no reason to change back.
Schema versioning and ORM can be quite orthogonal, unless your ORM decides to be as opinionated about stuff as e.g. ActiveRecord. In Go, I use migrate [1] and Gorp [2]. Granted, I have to tell the column names to Gorp manually, but that is a minor annoyance which I'm happy to pay for clean separation of concerns.
> Doesn't [using SQL for table definitions and migrations] make them more susceptible to mistakes?
What mistakes do you have in mind? Typos etc. will be caught by tests that access the database. That leaves actual design errors, which can happen just as easily in ORM code as in SQL code.
We have to think through all schema and data migrations due to the volume of data we have, both being ingested and being stored. And then we have to plan around the schema to be forwards and backwards compatible with the application or applications that use that datastore, allowing apps to update their usage out of band. And we have to do all this while ensuring as close to zero downtime as possible. Using raw SQL allows us to not worry about obfuscated complexity; we know exactly what will be ran. And for anything that might be rolled back, we have that ready to be ran. Sometimes that is not just doing the opposite queries.
This used our home grown query library, which was a pretty thin layer over raw SQL.
This was a huge enabler for testing.
Today, I'd probably use FlywayDB if I needed to do that.