Broadleaf Commerce Discussion Forums

What are your suggestions for best practices in migrating schema changes between environments while preserving stored data? I did some quick Googling and most of what I saw was at least 5 years old. I wonder if there's been anything recent that's relevant. A post on Stackoverflow suggested LiquiBase might be useful. What do you guys suggest? I think just gaining some visibility into how a schema has evolved between releases would be sufficient to write custom SQL scripts to handle the migration.

We understand that data migration can be a challenging aspect, and we've provided an ant-task to help you in this.

Use the build-sql task we've provided in DemoSite/site/build.xml to generate the current schema. Use the same task on your previous version, and then compare the deltas, which will tell you what is new and what needs to move. You can then build your migration scripts with ease.

Edit: Another way to handle seeing the deltas is starting up your application pointing to a copy of the old database with the hbm2dll set to update with hibernate logging turned to INFO. This will actually generate the necessary update statements for you instead of having you do it yourself!

Alternatively, you could use a database change management tool like http://www.liquibase.org/

Your last option (not recommended) is to leave the Hibernate autoddl setting to "update" for production, which will cause the application to create the necessary new tables. For example, if you added a field to Order that is only necessary for new orders, you'd be ok to do it this way. However, this won't help you if you need to manually move data around.

Another option that you might employ is to leave everything as update while in active development. Once you're ready to release your version 1 (aka, your first stable deployment into production) you then scale back the application to only do 'validate' or 'none'. Then you have a requirement that any entity changes to existing tables require an UPDATE statement to go into a SQL file that you check into source control that you can then apply to your production database along with a version deployment. If you go this route, I would probably create different SQL files for each version you deploy. So assuming that you bump the version with every production deployment, you might have a "schema-updates-1.1.0.sql" and "schema-updates-1.2.0.sql" etc.

That's what I would recommend for normal use cases, which is where once you have a stable production deployment your entity changes are minor. Usually if you're adding new large features then you'll be doing more table creation than table editing.

As Andre said in his edit, if you don't want to have to do both the entity changes and the UPDATE statements in a different sql file, then take a copy of your production database (the schema, at least; you don't really need the data) and point your updated application to that database with the ddl set to "update" and Hibernate logging set to INFO. That will then spit out all the UPDATE statements you need to run against the production database itself.

Cool, yeah these options make a lot of sense. Another way I thought about it would be to have mysql {stage,prod} dump the schema to a file and do a diff on those and hope both mysql instances spit out the schema in the same order.

It will spit out the tables in the same order (assuming you use mysqldump; it will sort them alphabetically). The problem with that is that it will just give you the differences in the table CREATE syntax and won't actually give you the necessary UPDATE statements. You will have to craft those yourself which is kind of a pain (from experience). Which I guess might not be that big of a deal if you have relatively small schema changes.

Yeah I can see that, although I'd almost rather do it by hand and know it's being done right instead of relying on Hibernate to do it for me. And yeah, for small changes it's probably not a big deal any different way you do it.

I think one of the advantages an open source framework like Broadleaf has in this area (especially when paired with something like AWS) is that I can spin up an exact duplicate of the production environment to do the migrations, regression testing, load testing, etc., without incurring licensing penalties. Might cut back on those "well it worked in staging so i dunno why it's broken" headaches.

But those problems are the most fun ones!

Hah, well you know the routine: "Oh man, all that junk I just typed actually compiled? Ship it!"