Also, following on which thread each portion of the code gets executed makes my head hurt. Is that normal?

@mbickel

ManagedThreadLocalSession

if for where something external manages transactions, often via

@Transaction

attributes. There's a couple of examples but nothing for Guice. Personally, I wouldn't recommend this path. I'd use

ThreadLocalSession

and use

session.transaction {}

blocks. These blocks are re-entrant so there's no problem nesting code

See https://github.com/andrewoma/kwery/tree/master/core#transactions for a bit more info

@andrewoma yep, I switched to

@Provides fun provideSession(dataSource: DataSource): Session = ThreadLocalSession(dataSource, PostgresDialect(), emptySet())

(instead of the Managed one). Looks good so far - and yeah, this is Dropwizard underneath, so the DS is pooled by default.

Jetbrains Exposed, is it reactive, if not reactive out of the box, can it be made reactive or is it possible to use it with VertX JDBC Client?

Ugh, I hate the word “reactive”. Do you mean async-supporting?

@jkwatson, I'm just using using the word as that is how VertX describes their JDBC component and the same for RxJava. Is Jetbrains Exposed asynchronous and event driven and also, does it come with a connection pool like C3P0 or is a connection pool less useful if you're doing async calls with callbacks?

how stable/full featured/maintained is the vert.x jdbc client at this point? When I last looked, it seemed pretty sketchy.

@jkwatson I can’t comment on the stability as I’ve only been messing around with it for a little bit on Friday, the only thing it gives you is a C3P0 connection pool and pretty much a way to execute SQL queries with a callback. It’s a bit too low level for me (I don’t want to manually extract data from a resultset into objects, that’s what ORM were developed for), hence the reason I’m looking at other options. Hibernate I’ve used before with C3P0 and the connection pool makes a huge difference, Hibernate 3 didn’t have async support as far as I know (not sure about the latest version) Apache Torque seems dead, so not touching it Exposed looks like the nice middle-ground what I’m looking for in this project, just wondering if it has a connection pool out of the box, or if you can get it working with one and if it has async support.

I’m also interested in these questions; I haven’t dug in to the docs yet on Exposed.

The docs are a bit on the thin side, will probably need to dig through the code: https://github.com/JetBrains/Exposed

Our team would love to have a true NIO based async sql solution.

What async options are available at the moment?

none, aside from vert.x jdbc (or the underlying library…don’t recall what it’s called)

@jkwatson Alternatively one could try Quasars and maybe try to asyncify blocking calls. I haven’t had time to experiment with this yet http://docs.paralleluniverse.co/quasar/

don’t know if there would be any benefits if the underlying library is still blocking.

yeah, it’s still blocking down at the database network layer, though. 😞

https://github.com/mauricio/postgresql-async is the only one that I know of (supports mysql & postgres, and it’s not jdbc)

and it seems a bit undermaintained right now

what about rxjava-jdbc?

I have not looked at rxjava-jdbc yet, can it be combined with an existing ORM?

I don't know, I have never use it

The VertX way is certainly doable when using Kotlin since you can write a User constructor and just map the values back via the constructor:

User(fname=it.getString(0), lname=it.getString(1), …)

The RX-Java one I’ll have a look at, maybe VertX is stable enough for use, just feel strange going back a decade and manually mapping results from a database call to an object.

16     override fun getUser(id: String): Future<User> =
17         client.queryOne("SELECT ID, FNAME, LNAME FROM USERS WHERE ID=?", listOf(id)) {
18             it.results.map { User(it.getString(0), it.getString(1), it.getString(2)) }.first()
19         }

At startup all you have to do is:

1 val client = JDBCClient.createShared(vertx, JsonObject()
2         .put("url", "jdbc:hsqldb:mem:test?shutdown=true")
3         .put("driver_class", "org.hsqldb.jdbcDriver")
4         .put("max_pool_size", 30));

http://maballesteros.com/articles/vertx3-kotlin-rest-jdbc-tutorial/

Unfortunately, if you’ve got jdbc in the stack anywhere, then you still need a big thread pool to run your queries, since there is no async jdbc.

Probably why VertX included C3P0 as part of their offering

connection pool != thread pool, though

Isn't a connection pool just a thread pool with each thread keeping open a connection?

nope

not that I’m aware of.