title: database independent data migration
body: <p>My goal is to enable schema and data migration for an existing application.</p>
  
  <p>This kind of question seems to have been asked many times, however with different requirements and circumstances as mine, I think.</p>
  
  <p>Since I am inexperienced in this domain, allow me to lay out the architecture of the app and my assumptions first.</p>
  
  <h2>Architecture</h2>
  
  <p>The app is a multi-user, enterprise desktop application with a backend server that can persist to any major DB (MySql, Postgresql, SQL Server, Oracle DB, etc). It is assumed the DB is on-premise and maintained by our clients.</p>
  
  <p>The tech stack used is a fairly common Hibernate+Spring+RMI/JMS-Combo.</p>
  
  <p>Currently, migrations are done by the server in the following way:</p>
  
  <ul>
  <li>On server start it checks for the latest expected schema version</li>
  <li>If larger than the current version, start migration to next version until current==latest:
  
  <ol>
  <li>Create new database</li>
  <li>Load (whole) latest schema (SQL script with a lot of <code>CREATE TABLE ...</code>)</li>
  <li>Migrate data (in Java classes using 2 JDBC-Connections to old and new schema)</li>
  <li>Load (all) latest constraints (SQL script with a lot of <code>ALTER TABLE ...</code>)</li>
  </ol></li>
  </ul>
  
  <p>This migration is slow and forward-only. But it is simple. The problem is, that until now the schema scripts and the queries in the data migrations have been using MySQL-syntax and features.</p>
  
  <p>Note that by <strong>migrate data</strong> I mean: the backend server copies the data from the old schema to the new one, transforming it if necessary.
  Also, the migration process starts automatically on-premise of our clients. Meaning, we only have control over the JDBC connection, but no direct access to the database nor knowledge about the specific database being used (MySQL, SQL Server,...).</p>
  
  <p>The goal is to either replace or augment this migration scheme with a database independent one.</p>
  
  <h2>Assumptions and research</h2>
  
  <p>StackOverflow <a href="https://stackoverflow.com/questions/24229529/managing-database-schemas-during-the-lifecycle-of-an-application-using-flyway-to">1</a> <a href="https://stackoverflow.com/questions/131020/migrations-for-java">2</a> <a href="https://stackoverflow.com/questions/10664553/flyway-liquibase-for-database-structure-and-dbunit-for-database-inserts">3</a> <a href="https://stackoverflow.com/questions/1142447/db-migrate-hibernate-jpa-hbm2ddl-diff-tools?rq=1">4</a> <a href="https://stackoverflow.com/questions/14482644/can-flyway-or-liquibase-generate-an-update-script-instead-of-updating-the-databa?rq=1">5</a> <a href="https://stackoverflow.com/questions/37385823/liquibase-vs-flyway-which-one-to-use?rq=1">6</a> <a href="https://stackoverflow.com/questions/16721166/how-does-flyway-liquibase-handle-destructive-upgrades-in-service?rq=1">7</a>: Answers state to use Hibernate's inbuilt feature. However, the docs state that this is not <a href="https://stackoverflow.com/questions/221379/hibernate-hbm2ddl-auto-update-in-production">production ready</a>. Also, AFAICT, all answers are concerned with <strong>schema migration only</strong>.</p>
  
  <p><a href="http://www.liquibase.org/index.html" rel="nofollow noreferrer">Liquibase</a>: Uses a custom DSL (in XML/JSON/YAML/etc) to allow for database independent <strong>schema migration only</strong>.</p>
  
  <p><a href="http://dbunit.sourceforge.net/" rel="nofollow noreferrer">DBUnit</a>: Uses custom XML-DSL to capture snapshots of states of databases. <strong>Can not recreate a snapshot of schema version 1 to version 2</strong>.</p>
  
  <p><a href="https://flywaydb.org/documentation/" rel="nofollow noreferrer">flyway</a>: In principle same as Liquibase. But <strong>is not database independent</strong> because SQL-Scripts are used for migrations.</p>
  
  <p><a href="http://www.jooq.org/" rel="nofollow noreferrer">JOOQ</a>: A database independent Query-DSL in Java on top of JDBC. Comparable to Criteria API but without the drawbacks of JPA. Should in principle allow for database independent data migration, however, does <strong>not</strong> help with <strong>schema migration</strong>.</p>
  
  <p>JPA-Query languages like HQL, JPQL, Criteria API are not sufficient because</p>
  
  <ol>
  <li>One cannot reference tables not mapped by the entity manager. E.g. join tables, metadata and audit tables.</li>
  <li>A copy of all versions of the Entity classes needs to be kept around for the mapping.</li>
  </ol>
  
  <h2>Question</h2>
  
  <p>I realize, that as this question stands now, it will be dismissed as opinion-based.</p>
  
  <p>However, I am not necessarily looking for specific solutions to this problem ( I doubt there exists a clear solution for such a complex problem space ) but rather to validate my assumptions.</p>
  
  <p>Namely, is it true, that</p>
  
  <ul>
  <li>Liquibase and Flyway are mainly concerned with schema migration and data migration is left as an exercise for the reader?</li>
  <li>in order for Flyway to support multiple, different databases, one needs to duplicate the migrations scripts per database?</li>
  <li>by and large, the problem of <strong>database independent data migration</strong> remains unresolved in enterprise Java?</li>
  </ul>
  
  <p>Even if I was to combine Liquibase/Flyway with JOOQ, I do not see how to perform a data migration, because Liquibase/Flyway migrate databases <strong>in place</strong>. The old database gets destroyed and with it the opportunity to transform the old data to the new schema.</p>
  
  <p>Thanks for your attention!</p>