4 ms·

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

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?

manyxcxi 9y ago
Since we use a lot of Java we use Flyway. It’s got different ways to use it, one of them being straight command line- so you don’t have to be a Java shop to use it. 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.
- sancha_ 9y ago
  Having used both Liquibase and Flyway, I prefer Liquibase. For what it's worth, Liquibase does support raw sql migration too: http://www.liquibase.org/documentation/index.html http://www.liquibase.org/documentation/index.html
  - schalla 8y ago
    Yes Liquibase is very flexible in how you can author changesets; it's certainly nice too that you can format it in XML, YAML, JSON, or SQL. All that said, what makes you say you prefer Liquibase to Flyway?
    - sancha_ 8y ago
      The main reason I prefer Liquibase over Flyway does not exist anymore, that was being able to downgrade my DB schema. It really is a new feature in Flyway and they were against it for a godly long time: https://github.com/flyway/flyway/issues/109 https://github.com/flyway/flyway/issues/109 And there is nothing in Flyway that Liquibase can't do and there is no reason to change back.
jpitz 9y ago
I used a home-grown system that essentially 'versioned' the database schema and initial configuration data. The program queried the database on startup, creating and/or altering as needed to get to the current version the program expected. Sometimes, certain upgrades required manual/offline steps to be taken - the program noted this in the log and shut down until that version upgrade was completed. 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.
sethammons 9y ago
We have an in-house migrations tool. It has a base schema and then plays migration files of raw SQL queries. When the playlist gets to taking too long, we reform the base schema and reset the playlist. 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.
majewsky 9y ago
> What's your preferred approach to store table definitions and migrations? 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. [1] https://github.com/mattes/migrate https://github.com/mattes/migrate [2] https://github.com/go-gorp/gorp https://github.com/go-gorp/gorp

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?

manyxcxi 9y ago

Since we use a lot of Java we use Flyway. It’s got different ways to use it, one of them being straight command line- so you don’t have to be a Java shop to use it. 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.

sancha_ 9y ago
Having used both Liquibase and Flyway, I prefer Liquibase. For what it's worth, Liquibase does support raw sql migration too: http://www.liquibase.org/documentation/index.html http://www.liquibase.org/documentation/index.html
- schalla 8y ago
  Yes Liquibase is very flexible in how you can author changesets; it's certainly nice too that you can format it in XML, YAML, JSON, or SQL. All that said, what makes you say you prefer Liquibase to Flyway?
  - sancha_ 8y ago
    The main reason I prefer Liquibase over Flyway does not exist anymore, that was being able to downgrade my DB schema. It really is a new feature in Flyway and they were against it for a godly long time: https://github.com/flyway/flyway/issues/109 https://github.com/flyway/flyway/issues/109 And there is nothing in Flyway that Liquibase can't do and there is no reason to change back.

jpitz 9y ago

I used a home-grown system that essentially 'versioned' the database schema and initial configuration data. The program queried the database on startup, creating and/or altering as needed to get to the current version the program expected. Sometimes, certain upgrades required manual/offline steps to be taken - the program noted this in the log and shut down until that version upgrade was completed. 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.

sethammons 9y ago

We have an in-house migrations tool. It has a base schema and then plays migration files of raw SQL queries. When the playlist gets to taking too long, we reform the base schema and reset the playlist. 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.

majewsky 9y ago

> What's your preferred approach to store table definitions and migrations? 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. [1] https://github.com/mattes/migrate https://github.com/mattes/migrate [2] https://github.com/go-gorp/gorp https://github.com/go-gorp/gorp