HAPI JPA Server - Database migrations

[joshu...@tempus.com]

I'm creating a project that is just like the example jpa server, using the built in structure. I noticed that the hibernate mode is set to `update`. This is great and all, but I'd rather not run that in production and have a proper database migration tool running in its place. Is there a standard liquibase structure or other official means for generating initial schema and keeping up with changes to the base structures implemented anywhere? It seems unreliable for me to maintain the liquibase changelogs when the structure is generated off of a dependency rather than my own entities, but not sure if there is any other way to do it unless there is a migration scheme built out for the entities.

[wcut...@gmail.com]

I think it's an excellent suggestion to allow a database migration path other than relying on the "update" mode.  Liquibase, Flyway, or simple DDL/DML scripts are good options.  

[James Agnew]

I've never used these tools, but they certainly seem cool.

Do any of these tools support automated generation based on JPA/Hibernate entity classes, or is this a purely manual process? If anyone wanted to try and get this working for HAPI we would definitely be supportive of including it.

Cheers,

James

--
You received this message because you are subscribed to the Google Groups "HAPI FHIR" group.
To unsubscribe from this group and stop receiving emails from it, send an email to hapi-fhir+unsubscribe@googlegroups.com.
To post to this group, send email to hapi...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/hapi-fhir/44925e54-1d93-4f2a-bea6-50e239191f5b%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[joshu...@tempus.com]

This is a requirement for my use case of HAPI and I have had less than stellar results trying to get it to work with liquibase. On the live chat someone else said that they use flyaway with validate, but they are not using the base jpa server - so basically means nothing if they have their own entities and schema.

I was successful in getting liquibase to generate my initial schema for HAPI, but as I said above the validate mode which is very important in this case always fails. This leads me to believe there is something very illy configured in HAPI for hibernate to behave this way, but I haven't had the time to fork and investigate directly. I was also not able to generate a change log using liquibase, I had to get an sql dump and just have liquibase run the sql file for the initial schema. This paired with hibernate set to `none` mode does work, but means whenever I upgrade I will have to take extra care to write migrations perfectly.

Things I tried:

1) Standard method to getting liquiase setup on an existing project

• startup app with hibernate create mode
  
• stop the app
• run liquibase generateChangelog command to create initial database state xml changelogs
• set hibernate to validate and add in liquibase to project startup (this includes adding dependency, setting hibernate to wait on liquibase on startup, etc etc)
• startup runs, liquibase runs, validate fails
• tried again with no validation, server starts up, but it fails to accept any new resources (some indexing problems with the database)

2) Make an sql dump for liquibase to simply load

• startup hapi with create mode
• stop the app
• dump the database schema using mysqldump
• save the file and have the first changset of liquibase run the sql dump
• setup liquibase, bla bla bla
• run the app with validate mode
• app fails to validate, schema is created
• run the app with no validation, app starts and works as intended

So method 2 worked, which is ok. But validation still can't be turned on. This is what I have continued with for the time being, but when I update HAPI i'll have to revisit and or spend a long time making sure I manually update anything that changed from version to version in the database.

My recommendation just in general for you @James is to be able to follow these steps without failure, as this is standard hibernate and should work in any project that uses it. Eventually I'll probably investigate this myself when I have time to, but would be great if anyone else gets the ball rolling;

Steps to test that hibernate is working properly:

1. Standup a HAPI server using the jpa base (Basically run the example JPA server) under create or update mode
• Ensure that hibernate ddl2auto mode is configured to `create` (or update if you really want) on the first run - to generate the schema
  
2. Stop your server
3. Set the ddl auto mode to `validate`
4. Start the server

You'll notice that right now step 4 will fail on validation. Which is super bizzare, as any hibernate application should be able to be run with create mode and then immediately in validate mode successfully. So something weird is happening. Additionally, if you keep it on `update` mode (which is the default set in the example project) you'll notice that every single time you start the app it attempts to do database changes, even if you run, stop, and re run without doing anything. So both update and validate are not reading the state of the database correctly. This is inheritly super dangerous as hibernate has the power to simply wipe clean data on startup, and you can google why `hibernate create/update in production is bad`.

On Wednesday, September 6, 2017 at 3:37:37 PM UTC-5, James Agnew wrote:

I've never used these tools, but they certainly seem cool.

Do any of these tools support automated generation based on JPA/Hibernate entity classes, or is this a purely manual process? If anyone wanted to try and get this working for HAPI we would definitely be supportive of including it.

Cheers,

James

On Wed, Sep 6, 2017 at 10:44 AM, <wcut...@gmail.com> wrote:

I think it's an excellent suggestion to allow a database migration path other than relying on the "update" mode.  Liquibase, Flyway, or simple DDL/DML scripts are good options.  

On Tuesday, August 29, 2017 at 1:13:47 PM UTC-4, joshu...@tempus.com wrote:

I'm creating a project that is just like the example jpa server, using the built in structure. I noticed that the hibernate mode is set to `update`. This is great and all, but I'd rather not run that in production and have a proper database migration tool running in its place. Is there a standard liquibase structure or other official means for generating initial schema and keeping up with changes to the base structures implemented anywhere? It seems unreliable for me to maintain the liquibase changelogs when the structure is generated off of a dependency rather than my own entities, but not sure if there is any other way to do it unless there is a migration scheme built out for the entities.

--

You received this message because you are subscribed to the Google Groups "HAPI FHIR" group.

To unsubscribe from this group and stop receiving emails from it, send an email to hapi-fhir+...@googlegroups.com.

To post to this group, send email to hapi...@googlegroups.com.