It can be used to write an automated test to ensure that schema created with your migration scripts is the same as the one generated by ORM. Liquibase provides an API for comparing database schemas.
#Compare databases using dbschema code#
H2 and HSQLDB).įiltering code is not particularly beautiful, but usually you write it once and then the test works for years. you may want to ignore column size, as default values may differ if you compare different database engines (e.g. You may want to ignore tables not generated by ORM (e.g.Sometimes it is needed and the reasons include: When you take a look at the code ( MigrationsTest), you may notice that DiffResult generated by Liquibase is filtered. SYS_IDX_FKN5NN4A0LNTWFXOUSQIPEYII8D_10108 on PUBLIC.ORDERS_CUSTOMERS(ORDERS_ID) SYS_IDX_FKHKSFFJAVVQ4MK288GPGCVRI09_10104 on PUBLIC.ORDERS_CUSTOMERS(CUSTOMERS_ID) Reference Database: SA jdbc:hsqldb:mem:testdb (Default Schema: PUBLIC)Ĭomparison Database: SA jdbc:hsqldb:mem:migrationsTestDb (Default Schema: PUBLIC)įKHKSFFJAVVQ4MK288GPGCVRI09(ORDERS_CUSTOMERS -> CUSTOMERS)įKN5NN4A0LNTWFXOUSQIPEYII8D(ORDERS_CUSTOMERS -> ORDERS) You can play with it (by altering entity classes and/or migration scripts) to see different test results. It contains two entities, migration scripts and a working test. I published a working Spring Boot application on Github.
Data consistency must be checked by using other means (and typically a generic solution which would work for all the cases just does not exist). if you moved all records from one table to another or if you properly reformatted values in a column). It will not check if you migrated your records properly (e.g. if all tables exist and have appropriate columns). The presented approach allows you to compare database schema structure only (e.g. Fail if the result contains any differences.Log all the differences so that it is possible to easily find the missing migrations.Use Liquibase to compare two schemas and calculate DiffResult.Provide access to the schema created by migration scripts - in the example, a new in-memory HSQLD Datasource is created and then we manually execute Liquibase scripts against it.Provide access to the schema generated by the ORM framework - in the example, we are using JPA, Spring and HSQL database so we can just use to inject our Datasource to the test.The general flow in our test is going to be as follows: Later in the article I will provide an implementation example with Hibernate/JPA and Liquibase, but please keep in mind that the general idea is framework-agnostic. Therefore you can use any migrations framework (Liquibase, Flyway or something different) and whatever ORM framework you want. The diff mechanism can be used for any schemas, regardless of what has created them. It does not mean that you have to use Liquibase for your migrations scripts, though. We will leverage this functionality in our test. Liquibase, a popular migration scripts tool, provides a diff capability, i.e.
If there are any differences, the test will fail, so the consistency will be ensured. The test will be responsible for generating a diff between ORM-generated schema and schema generated with migration scripts. In this post I will present a way of testing it automatically. It is very error prone to do it manually. This approach imposes a challenge of ensuring that our schema migration scripts are up to date with our ORM entities.
#Compare databases using dbschema update#
In case of JPA/Hibernate, it would mean that to property would be set to update for tests and validate for production. H2) for tests and let ORM generate the schema. A very common approach is to set up an in-memory database (e.g. In automated tests, though, ORM capability of schema auto-generation is very useful - usually it is much faster than running migration scripts. Typically tools such as Liquibase or Flyway are used to manage schema and data migrations. It is especially challenging when you have existing data in the database. In reality you have to write scripts to version your schema and to migrate from one version to another.
While it is very convenient as a proof of concept or for very simple applications, it is not a production ready approach. Object Relational Mapping frameworks, such as Hibernate, very often provide a way to automatically generate the database schema based on persistent classes.
0 kommentar(er)
