Database Evolutions - Integrating with Slick 2 Code Generator

[papauschek]

I recently wrote an article about how to integrate Play Framework Evolutions with the new Slick 2.0 code generator, which enables you to write your evolutions by hand and get the necessary slick code autogenerated.

It's basically the reverse of what play-slick is doing right now, as I understand it (code-first via EBean).

It would be nice to have this functionality all in one place, and play-slick seems the perfect place for it, since I saw you already have a branch for upgrading to Slick 2.0

I have a working prototype for the code generator which integrates with evolutions right here:
https://github.com/papauschek/play-slick-evolutions
And here are some explanations:
http://blog.papauschek.com/2013/12/play-framework-evolutions-slick-2-0-code-generator/

My question is: can we somehow integrate this into play-slick? Right now, my prototype uses a separate play module for code generation. How could this be integrated in a Play module?

If someone with more SBT knowledge could point me to the right direction, I would be happy to help with the integration.

Looking forward to some feedback!

[ms-tg]

👍 Clearly, a solution to this will have to end up in play-slick, if we want this to be a/the mainstream approach, right?

[freekh]

👍 Yeah, I think this should go in as well. I do have some SBT skillz, but limited with time these days. If nobody else is willing I can help though

[freekh]

The way the plugin used to generate the DDLs was to create a play plugin.
A play plugin is part of the life cycle of Play so it was easy.

Here you have to generate some code so I guess this has to be done before Play can compile (which is why you are adding to the sourceGenerators).
So that means it has to be a sbt plugin. I guess you should depend on the play sbt-plugin.
A user would then have to add this library as a sbt-plugin (project/plugins.sbt).

I am not sure whether that answers your question?

Since it must be a sbt-plugin I think it would be best if this is released separately from play-slick, but it makes sense to have it living inside of this repo. We would simply have separate 2 projects.

Now, the problem with the code AFAICS, is that you are starting and stopping I am not sure you need to do this if you were to use the actual FakeApplication?http://www.playframework.com/documentation/2.2.1/ScalaFunctionalTest
Is the problem that you do not want to depend on play-test?

Also I am not sure what you mean with: "Right now, my prototype uses a separate play module for code generation." Could you give me the link for the play module?

I guess the plugin would ideally use your actual DB right based on the conf? It should use only H2 right?

[papauschek]

Thanks for the clarification. 👍 Yes you answered all of my questions - it has to be a SBT plugin.

Regarding your questions:

• The prototype generates the code in the "dbgen" module, which is also in the repo. All the code is right there. Sorry for the confusion.
• I copied the implementation of FakeApplication from play-test because I did not want to depend on it for 5 lines of code.
• Yes the database evolutions should run against H2. You can't use the real database because at the time of code generation the evolutions may not have run against it yet.

I see several challenges with implementing a SBT plugin:

• The Play framework right now does not expose the Evolutions Plugin in a way that I could access it directly. So I have to make sure the FakeApplication actually starts and runs those evolutions.
• If the code generator is part of a SBT plugin, the user doesnt have access to the generator code and cannot adapt it. For example, many people want the code generator to use JodaTime instead of the java sql Timestamp for date columns.

If an out-of-the-box plugin doesnt work for most users, I don't think it's worth bothering.

What's your opinion on those points?

[freekh]

Hmm.. Yeah, I can see the challenges that you list.

The EvolutionsPlugins should be callable AFAIK, but you need an app either way. I think if you just find a way to read the configuration file and put it in a FakeApplication, I guess you should be in control (without needing to start the app etc). I think that would be a bit more elegant. In any case this is more a problem for us (authors of the library) than for the users of the library.

If we are smart about the SBT plugin I think it should be possible to extend ( you could have a setting that replaces a code generator). So it would be possible to extend it, but it would perhaps be hard for the ordinary user then again who am I to say what the "ordinary" dev can do :)

This (being hard to extend because it would be a SBT setting) is the only real limitation this plugin would have though. I think I would still say it could be pretty darn cool if you pardon my french :)
Imagine how fast it would be to get running on an existing DB! And you can manage DB schema changes as well would be a breeze especially if you are prototyping Play along another framework.

I do not have the time to actually hack it out though, but I can provide guidance (SBT issues for example) and reviews.

[papauschek]

Alright, I got a basic SBT plugin working. Would really appreciate your feedback & review:

https://github.com/papauschek/play-slick-evolutions-plugin

Some notes:

• It does not need to start the fake Play app anymore, which makes it much faster. You were right, the EvolutionsPlugin can run without the play app. It still depends on the DBPlugin though.
• Exceptions while running the evolutions are actually displayed like all the other Play exceptions (nicely formatted in the browser) - which is pretty cool!
• I think it would be good to include the mapper for JodaTime support by default (https://github.com/tototoshi/slick-joda-mapper), what do you think?

I'm really impressed how easy it was to navigate the play framework code (can't say the same about slick, hehe)

Configuration:

There is a readme in the repo which covers the basics. But it takes all the configuration from the Play config. The only extra thing it needs to know is which Slick profile to use.

[freekh]

Hey @papauschek, I haven't forgotten this, but I have run out of time to spend on this this week :( I will review next week. Maybe @cvogt has some comments?

[papauschek]

Alright :-)

On Wed, Feb 5, 2014 at 12:08 PM, Fredrik Ekholdt
[email protected]:

Hey @papauschek https://github.com/papauschek, I haven't forgotten
this, but I have run out of time to spend on this this week :( I will
review next week

Reply to this email directly or view it on GitHubhttps://github.com//issues/118#issuecomment-34156590
.

[ScottPierce]

@freekh Conversations on this seems to have died. It'd be great if this happened 👍

[cvogt]

Hej guys, I didn't find the time to review this yet. I agree it's important! @papauschek, I added you on XING. Let's get in touch and GTalk about it or something. Thx

[cvogt]

@papauschek I just reviewed your code read you blog article again: http://blog.papauschek.com/2013/12/play-framework-evolutions-slick-2-0-code-generator/

I think it goes exactly into the right direction and I think we should aim for the following features:

1. Allow to apply evolutions as you write them and re-generate classes on the fly.
2. Allow to add more evolutions and re-generate, even if your app doesn't compile (to potentially recover after a breaking migration).
3. Allow to use same db vendor in dev and production. (as opposed to h2 in dev and mysql in prod).

Looks like you got those covered by https://github.com/papauschek/play-slick-evolutions-plugin/

1. Allow to apply evolutions with your already compiled app in order to upgrade your production db (obviously without running the code generator, because you deploy pre-compiled files).

Optional but nice feature:
5. Use H2 for dev and mysql in staging and prod. Looks like you have that in https://github.com/papauschek/play-slick-evolutions/

As a side-note, the gen-tables sbt command from the slick code gen example projects seem to be broken in that sbt doesn't pick up the generated file. This needs to be fixed (in the example projects and your code, if you include it).

[papauschek]

Hi there,

first of all, just for clarification, there are two different projects and approaches I tried:

1. the "play-slick-evolutions” project, as mentioned in my blogpost, is implemented with a separate play module. It’s nice for people who want full control over the source code generator, but otherwise probably not a good idea to pursue further.

2. the “play-slick-evolutions-plugin” is an SBT plugin which is fully integrated with the play framework source code generators, and as such is a better approach I think. However, the final plugin would have to be good for everyday use "out of the box", because the user cannot modify the code generator.

my comments to your proposed features below refer to the SBT Plugin (2), because I think thats the right way to go.

1.) Allow to apply evolutions as you write them and re-generate classes on the fly.

This was the main motivation for the SBT plugin and already works with the prototype.

2.) Allow to add more evolutions and re-generate, even if your app doesn't compile (to potentially recover after a breaking migration).
The prototype compiles before the Play app compiles, and therefore this already works.

3.)Allow to use same db vendor in dev and production. (as opposed to h2 in dev and mysql in prod)
I think this could be done, but it also means that the source code generator has a dependency on an installed database. Otherwise, the project cannot be compiled.

Let’s say you build your project on a Jenkins or TeamCity type of continuous integration tool. It would mean that a database has to be installed in order for the build server to be able to compile.

What do you think about this issue?

4.) Allow to apply evolutions with your already compiled app in order to upgrade your production db (obviously without running the code generator, because you deploy pre-compiled files).
I’m not sure if understand correctly, but I think this is out of the scope of the SBT plugin. Play framework already takes care of applying evolutions correctly onto a test/prod database.

5.) Optional but nice feature: Use H2 for dev and mysql in staging and prod. Looks like you have that in https://github.com/papauschek/play-slick-evolutions/

This is already implemented, because that’s the way I currently work with slick. However, there is still one issue. For this approach you will sometimes need different evolutions for H2 and for MySQL (despite the compatibility mode), because of the differences in SQL dialect.

Maybe the plugin should also support a way to provide extra migrations for different dialects.

So finally, there are currently 2 open questions for the architecture of the plugin:

• How to deal with the dependency on a database instance, just to compile the project?
• When not having a dependency on a database instance (by using H2), how to deal with the different SQL dialects?

Looking forward to hearing your thoughts,
Cheers,
Chris

[cvogt]

4.) Allow to apply evolutions with your already compiled app in order to upgrade your production db (obviously without running the code generator, because you deploy pre-compiled files).

I’m not sure if understand correctly, but I think this is out of the scope of the SBT plugin. Play framework already takes care of applying evolutions correctly onto a test/prod database.

Yes. I just mentioned this, because we need to make sure the slick-codegen-evolutions combo plugin doesn't get in the way.

How to deal with the dependency on a database instance, just to compile the project?

In my opinion devs should add the generated code to version control. The CI then just needs to checkout the code and compile it. We just need to make it possible not to optionally invoke the code generator when compiling releases and take the code from version control instead. How does that sound?

When not having a dependency on a database instance (by using H2), how to deal with the different SQL dialects?

Yeah, that's a problem. As I said, I consider it nice to have but optional. One way to solve it: Have MySQL installed on your system (or whatever dbms you use in production). Always write MySQL migrations. Apply migrations to MySql and use it as the source for code generation. It it wise to have MySQL anyways to run tests against the real db you are using in production. Nothing stops you in this setup to alternatively run tests against H2 for speed.

In the intermediate term, @nafg's slick-migration-api can solve this: https://github.com/nafg/slick-migration-api . I am meaning to flesh out the migrations management tool/api out of my prototype: https://github.com/cvogt/migrations some time in the next half year. Put the two together and we can replace play evolutions, by db independent migrations (at lest for schemas).

WDYT?

[papauschek]

The reason I stopped working on this is

a) because of time constraints (unfortunately)
b) and our own requirements changing in that we possibly have to move away from MySQL to a NoSQL solution, where we can't use Slick anymore.

However, the code base is very small and however wants to continue to work on this, be my guest. The code is in public domain now.

If Slick happens to continue to improve in the right direction (for us), it's possible I continue working on Play integration in November.

Cheers,

[pathikrit]

Awesome work. This removes duplicate work from storing schema in evolutions/x.sql files and in the Scala layer. +1 for this to be part of play-slick itself.

[nafg]

@cvogt @jroper Can't play evolutions be made to run scala (or java) code?

[cvogt]

no idea about Play evolutions but what you are asking for is basically what I prototyped here: https://github.com/cvogt/migrations

Never had the time to flesh it out. Maybe you want to take a stab at it :)?

[pathikrit]

Yes, take a a look at this:
https://github.com/pathikrit/garfield/blob/master/play-slick-evolutions-codegen-plugin/app/PlaySlickCodeGenerator.scala

I am trying to create an sbt plugin for this myself:
https://github.com/pathikrit/garfield/tree/master/play-slick-evolutions-codegen-plugin

[He-Pin]

should provide play-slick-codegen

[krivachy]

@cvogt What is the status with this? Has someone created a solution that works? I think this would be an awesome and much needed feature and could help implement it.

[cvogt]

no progress on this from my side particular to this ticket. However, since then "Scala Forklift" was developed https://github.com/lastland/scala-forklift . @lastland maybe you want to take a look how it covers this use case and how it would be integrated with play-slick to replace the built-in evolutions including slick support.

[lastland]

@cvogt thanks for letting me know! I will take a look at it!

[lastland]

Hi everyone,

I have just made an example of using Play, Play-Slick with Scala Forklift. You can find the example here: https://github.com/lastland/play-slick-forklift-example

Scala Forklift supports migration files written in plain SQL queries or type-safe Slick queries. It applies all the migrations one by one, and generates the corresponding schema code for each version automatically. It also supports recovering from previous break migration (for example, when the generated code cannot compile).

There is also an experimental feature to support developing with git branches. It is not yet demonstrated in the example, but you can find more information here: https://github.com/lastland/scala-forklift/tree/develop/example/tools/git

Basically you don't need much extra work to start a web application with Play, Play-Slick, and Forklift. Just clone the Forklift start template from https://github.com/lastland/scala-forklift-start-template, put the web application written with Play under the app directory, and edit some files accordingly. You can find all changes to the original start template here: lastland/play-slick-forklift-example@c971369 (note that most of the additions here are created by activator new).

I wonder if this project fits your requirements? Please let me know if you have any questions.

[krivachy]

@lastland thanks for the effort, I checked out the example project and it works great 👍 It would be something I could start using in a project in the coming weeks and give you more feedback.

Where can I find out more info about when I would use datamodel.v1.schema? I don't quite see the point in ever referencing an older schema from my code.

Thanks again for the clear example and Scala Forklift is very cool!

[cvogt]

migrations change the schema. if they are type-safe, they need to type-check against the version they upgrade. If you upgrade with multiple migrations, each one needs a different schema that corresponds to the database at that point in time. That's why you need to keep the older versions around, to upgrade older database versions. If that is never the case they could be thrown away after the upgrade.

[cvogt]

And I agree, Forklift is very cool ;)

[lastland]

@krivachy Glad you find it useful! 😄

The reason to keep datamodel.v1.schema is exactly as @cvogt has explained.

Please also note that I have just published Forklift version 0.2.1 (which supports Slick 3.1.1) and 0.2.0 (which supports Slick 3.0). I have also updated the starting template to show how to use Forklift 0.2.1 with slick 3.1.1. See: https://github.com/lastland/scala-forklift-start-template