Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Integrate jOOQ into Play! Framework #768

Closed
lukaseder opened this issue Jul 22, 2012 · 22 comments

Comments

@lukaseder
Copy link
Member

commented Jul 22, 2012

None

@ghost ghost assigned lukaseder Jul 22, 2012

@noguespi

This comment has been minimized.

Copy link

commented Feb 7, 2013

Could be a nice plugin

@lukaseder

This comment has been minimized.

Copy link
Member Author

commented Feb 7, 2013

What's needed to make jOOQ a Play plugin? Would you be willing to contribute the necessary changes?

@hbarney

This comment has been minimized.

Copy link

commented Apr 22, 2013

We're using JOOQ with the play framework and we're very happy with it. We're not using it as a plugin though.

@smola

This comment has been minimized.

Copy link

commented Apr 22, 2013

@hbarney how do you handle migrations/evolutions? One of the reasons stopping me from migrating to jOOQ on an existing Play project is adapting it to support Play evolutions.

The other missing piece is connection handling, but that's easier to write a plugin for.

@hbarney

This comment has been minimized.

Copy link

commented Apr 22, 2013

We wrote our own (very basic) framework to do migrations. It just checks to see what was the last migration that was applied to the database and runs any new outstanding migrations.

We use BoneCP for connection pooling and just a have a static instance that we request it from. Now that controller methods don't have to be static, there are some much more attractive ways to do this like using Guice for this kind of dependency injection.

@jaiew

This comment has been minimized.

Copy link

commented Apr 22, 2013

After I spoke with @hbarney my team and I are looking at using JOOQ with Play and would also be happy to look into this as well. I'm going to try a small spike this week and will report back with details.

@lukaseder

This comment has been minimized.

Copy link
Member Author

commented Apr 28, 2013

Thanks for the feedback guys! Nice to know that these things already work.

@jaiew

This comment has been minimized.

Copy link

commented Apr 30, 2013

I've been playing around with this for the last few days and I've written a simple play plugin as a proof of concept that lets JOOQ generate the classes. It uses all the play internals. Evolutions and db connection handling which uses BoneCP under the covers.

There a few issues to think about.
When to generate the classes.

  • You really only want to do this on first run and not every time play starts up. Similar to how the evolutions plugin works. It tracks what evolution it is up to and if it needs to run it will throw an exception which is caught by play and presents a screen letting the user know they need to run the evolutions. This can be replicated for JOOQ which can then, after evolutions have run, detect if it needs to run or not and then throw a similar exception which the user can then trigger the generation and the app will be reloaded automatically and the new generated files will be compiled.

Handling different generated files for different databases.

  • I was trying to see if it was possible to generate one set of classes that could be reused by multiple databases. eg use Mysql in production and use h2 in dev mode and only have to generate a single set of files for this. I couldn't get this to work due to h2 requiring me to reference table names as uppercase etc. This would mean tracking multiple sets of generated classes if you wished to use an in memory db in dev and then having another set for use with mysql in prod. Not such a major issue but still would be nice if you could just have one set.

Use the application.conf or use the configuration file that JOOQ uses in the GenerationTool.

  • In mine I used the application config for setting some simple values but if you wanted to use a full featured set of the configuration file that JOOQ uses you could define the path to the config file.
    eg. db.default.jooq.includes=".*"
    db.default.jooq.excludes=""
    db.default.jooq.inputSchema="database_name"
    or
    have a jooq.default file in the conf dir of the play app which jooq could automatically pick up similar to how the evolutions plugin looks in evolutions.default

Let me know what you think. When I get some more time I'll clean up what I've got and put it up so you can take a look.

@lukaseder

This comment has been minimized.

Copy link
Member Author

commented Apr 30, 2013

Handling different generated files for different databases.

If you're doing this, you're probably best off quoting your table / column names to get rid of case sensitivity issues. I.e. name your tables:

  • MySQL: CREATE TABLE Table (Column INT)
  • H2: CREATE TABLE "Table" ("Column" INT)

All of jOOQ's supported databases support case-sensitive identifiers in some way (except for SQLite). But they're different in how they interpret the default (unquoted) behaviour.

@smola

This comment has been minimized.

Copy link

commented Jun 9, 2013

Let me know what you think. When I get some more time I'll clean up what I've got and put it up so you can take a look.

I'd be interested in trying your code, even if it's not clean or has rough edges. For me, supporting different databases and having nice conf extras are secondary. Only having evolution support as you described in your first point would already be really useful.

@ben-manes

This comment has been minimized.

Copy link
Contributor

commented Jun 10, 2013

While I don't use play! (jersey & scalatra), my solution may be worth emulating.

how do you handle migrations/evolutions

I use Flyway, which has a Play! plugin, and wrote a Gradle plugin for my build. For application usage, I invoke a flyway instance per service (schema per service) by collecting all of the installed services using a Guice multibinder. Every service has its own set of migration scripts.

When to generate the classes.

I generate during the build to avoid checking in code-generates sources. This is done using my jOOQ plugin which runs iff a Flyway migration was performed. Gradle's incremental build (up-to-date checks) and parallel builds work great here. For Eclipse I have an external builder generated if the project uses codegen, invoking the Gradle task in the background on a change. This lets schema changes immediately trigger codegen and update the classes. I do this for other codegen usages (jsonschema2pojo, thirft / finagle). I couldn't figure out how to do this in IntelliJ, though.

As a safety measure, I also run a naive schema validator at startup to check that the jOOQ generated code matches the database.

Handling different generated files for different databases.

I haven't found this necessary, but then I follow Lucas' advise by quoting everything. I also have different migration scripts per dialect to avoid incompatibilities. It is a little too easy to write code that works for H2 and realize its incompatible with MySQL, but that's what integration tests are for.

Use the application.conf or use the configuration file that JOOQ uses in the GenerationTool.

I don't see why this is necessary. I use conf files to configure within Guice modules, e.g. my custom JooqModule. The GenerationTool only needs to be configured within the build, which is done with Groovy to auto-customize the configuration for that project (by applying a shared build script). Similar should be possible with sbt.

The other missing piece is connection handling, but that's easier to write a plugin for.

I use BoneCP within a Guice module. This is only configured if using MySQL, as H2 doesn't need pooling for tests.

I'm not sure how Play! handles transactions, but I use Spring's excellent transaction library. It took a little extra effort to integrate that with Guice and jOOQ, but it came out very clean.

@poornerd

This comment has been minimized.

Copy link

commented Jul 3, 2013

@jaiew I would love to try out your plugin. Is it on github? Or is the source code anywhere where I can try it out?

@jaiew

This comment has been minimized.

Copy link

commented Jul 26, 2013

@poornerd, @smola @lukaseder

Sorry been really busy lately and haven't had any time to work on this. I finally got some time today and pushed up a jooq play plugin here: https://github.com/jaiew/play-jooq

It's not doing anything too fancy but just generating the jooq code based on some config. There's a sample app in the repo which I set up to demo it.

Let me know if you have any issues.

@lukaseder

This comment has been minimized.

Copy link
Member Author

commented Jul 28, 2013

@jaiew I have advertised your plugin to the user group to get broader feedback:
https://groups.google.com/forum/#!topic/jooq-user/GZDFohnxCI4

@jaiew

This comment has been minimized.

Copy link

commented Jul 28, 2013

Great. It's just a first draft so I'm sure there's plenty of room for improvement.

@smola

This comment has been minimized.

Copy link

commented Oct 13, 2013

@jaiew I have tried your plugin with a basic Play 2.2 app (see PR jaiew/play-jooq#3) and it's working correctly so far!

It seems PlayConnectionProvider is applicable as is to any project using the plugin. Shouldn't it be moved from the sample app to the plugin?

@jaiew

This comment has been minimized.

Copy link

commented Oct 15, 2013

Thanks @smola I've merged your PR and I'l move the PlayConnectionProvider to the Plugin.

@smola

This comment has been minimized.

Copy link

commented Dec 3, 2013

Finally, I'm moving a Play 2.2 production app to jOOQ. @jaiew's plugin is working correctly so far. However, running codegen at run-time leads to really painful situations. Most of the time this is not a problem (some git checkout hackery will do the job), but in some other cases you really want to run codegen again without requiring a successful compilation. In these cases, https://github.com/sean8223/jooq-sbt-plugin will do the job.

In short: I think @jaiew just works, but an sbt integration will be required for a fully satisfactory Play+jOOQ experience.

@smola

This comment has been minimized.

Copy link

commented Jan 13, 2014

An update on @jaiew's https://github.com/jaiew/play-jooq

I'm using it on two production apps with great results. I use the following helper clase for increased convenience:

package net.bitsnbrains.jooq;

import org.jooq.DSLContext;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;

public abstract class Jooq {
    public static DSLContext create() {
        return DSL.using(new PlayConnectionProvider(), SQLDialect.POSTGRES);
    }
}

I wonder if something like this can be made part of the plugin (dialect would need to be auto-discovered) or if it's better to let the user handle this.

Other than that, I stil miss a sbt integration from time to time in order to force codegen before compiling the project. Transaction support would be great too.

@ankurb0308

This comment has been minimized.

Copy link

commented Jun 9, 2014

@smola , @poornerd , @jaiew

If anyone made it work with Transaction Support than it would save my life.... please share your updates

Thanks & Regards,
Ankur

@mkurz

This comment has been minimized.

Copy link

commented Jan 12, 2016

@jaiew Will you make updates to your Play plugin/module?

@lukaseder lukaseder modified the milestones: Version 3.10.0, Version X: User contribution candidates Apr 11, 2017

@lukaseder

This comment has been minimized.

Copy link
Member Author

commented Apr 11, 2017

Closing this as we won't be doing anything from the jOOQ side. Thanks for all your comments in this thread!

@lukaseder lukaseder closed this Apr 11, 2017

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.