Dropwizard + Liquibase Migrations

[Ari King]

Hi,

I've reviewed both Dropwizard's and Liquibase's documentations and I have the following questions, which I'd appreciate help with:

1. From what I understand db commands are executed against (dropwizard) application jars, i.e.

   java -jar hello-world.jar db migrate helloworld.yml

   Is there a way to execute db commands without first creating an app jar?
2.  How can I configure foreign keys through column tag attributes foreignKeyName and references? The only example I've found demonstrates how to add foreign keys after the fact.

Thanks.

Best,

Ari

[Jochen Schalanda]

Hi Ari,

On 14.07.2014 23:19, Ari King wrote:
> Is there a way to execute db commands without first creating an app jar?

If you want to strictly keep using dropwizard-migrations [1], then no
(except for running the respective command with your build system, e. g.
with the Exec Maven Plugin [2]).

On the other hand you can always use one of the many integration points
for Liquibase and run your database migrations from your build system,
e. g. Ant [3], Maven [4], Gradle [5], or completely stand-alone [6].


> 2. How can I configure foreign keys through column tag attributes
> |foreignKeyName| and |references|? The only example I've found

> demonstrates how to add foreign keys after the fact

> <http://www.liquibase.org/documentation/changes/add_foreign_key_constraint.html>.

Adding a FK with 'addForeignKeyConstraint' usually works well because
the DDL statements are being executed inside a transaction. Just add the
FK directly after creating the table or adding a column and you should
be fine.


[1]:
https://dropwizard.github.io/dropwizard/0.7.1/docs/manual/migrations.html
[2]: http://mojo.codehaus.org/exec-maven-plugin/
[3]: http://www.liquibase.org/documentation/ant/index.html
[4]: http://www.liquibase.org/documentation/maven/index.html
[5]: https://github.com/tlberglund/gradle-liquibase-plugin
[6]: http://www.liquibase.org/documentation/command_line.html


Cheers,
Jochen

[Dimas Guardado]

Hi Ari,

In answer to your first question, I don't think I can add much more to Jochen's response. Why do you need to run liquibase outside of the app jar?

In answer to your second question, the Liquibase 'column' tag can have 'constraints' child tag which describes foreign key constraint name and references.

http://www.liquibase.org/documentation/column.html
https://gist.github.com/dguardado/2ed37e0062411c8530b2

Cheers,
Dimas

[Ari King]

Hi,

@Jochen:

Thanks for the clarification and links regarding running migrations outside of an app jar. Have you used any of those plugins? If yes, how was your experience with them/it? I use gradle more so these days, so I'd be interested in reviews of that plugin in particular.

Seems like 'addForeignKeyConstraint' is the way to go, since not only are the DDL statements executed in a transaction, but the tag offers more configuration options like, "onDelete" and "onUpdate".

@Dimas:

For ad hoc trial/error tests and integration tests (usually within an IDE), I need to have the db schema setup, hence the need to run liquibase outside of an app jar.

Thanks for the foreign key 'constraint' child tag example. Is it possible to include cascading attributes within this tag?

Best,
Ari

[Dimas Guardado]

Hi Ari,

I see. For my purposes, the maven liquibase plugin has served me well in those scenarios. I have not tried to include it in a dropwizard context as I've tended to just execute commands against a pre-built dropwizard app jar for integration testing.

The 'constraints' tag allows you to specify cascading on delete, but not on update. Supported attributes of the 'constraints' tag are on the bottom half of the documentation for 'column'. I've updated the gist to illustrate.

https://gist.github.com/dguardado/2ed37e0062411c8530b2

Keep in mind that some databases, like MySQL, do not support transactional DDL, so you'll want to check with your database documentation before relying on transactional semantics for DDL.

http://dev.mysql.com/doc/refman/5.5/en/cannot-roll-back.html


Best,
Dimas

[Jochen Schalanda]

On 15.07.2014 00:41, Ari King wrote:
> Thanks for the clarification and links regarding running migrations
> outside of an app jar. Have you used any of those plugins? If yes, how
> was your experience with them/it? I use gradle more so these days, so
> I'd be interested in reviews of that plugin in particular.

I've been using the Maven and Gradle plugins before and they work as
advertised.

In the context of Dropwizard I didn't like the duplication of the
database settings and credentials in the build system as neither Maven
nor Gradle support YAML for external property files. In my opinion
database migrations should always happen from within the application
itself and not at build time.

> For ad hoc trial/error tests and integration tests (usually within an
> IDE), I need to have the db schema setup, hence the need to run
> liquibase outside of an app jar.

It's relatively easy to run Liquibase migrations before a running JUnit
test in a method annotated with @BeforeClass, see [1] for an example.

If there's the need for it, you could probably also extract a JUnit Rule
[2] from it.


[1]:
http://blog.javaforge.net/post/35193958835/database-driven-unit-tests-with-liquibase
[2]: https://github.com/junit-team/junit/wiki/Rules


Cheers,
Jochen