SolvedExposed Multiple databases

Can I use multiple connection? Can I use something like this?

val db1 = Database.connect("jdbc:h2:dbfile1", "org.h2.Driver")
val db2 = Database.connect("jdbc:h2:dbfile2", "org.h2.Driver")

db1.transaction {
    // working with the first database
}

db2.transaction {
    // working with the second database
}

I have read the source and if I understand I can use separate bases in separate threads. Like this.

// thread 1
Database.connect("jdbc:h2:dbfile1", "org.h2.Driver")
transaction { // first db }

// thread 2
Database.connect("jdbc:h2:dbfile2", "org.h2.Driver")
transaction { // second db }

That's right?

23 Answers

✔️Accepted Answer

Syntactically, I would find it more pleasing to have something like this:

val db1 = Database.connect(...)
val db2 = Database.connect(...)

transaction(db1) {
}

transaction(db2) {
}

And when it's called without a specified Database, it defaults to the current local one, so that if only one database is in use in a project, it doesn't need to be specified everywhere.

Other Answers:

the basic issue is that connecting to a database sets a static, then transaction uses that static

Having a global state is very bad, procedural practice. Should be avoided at all costs.

We aware of that bad design decision and going to fix it in the upcoming release.
We don't want to break existing API and to force users to provide DB instance when you just work with a single database.
Also there is "a problem" with entities and their DB relationship, but we have some ideas how to ensure that you don't link entities from different databases.

I have a prototype (which works) for DSL part, but there is a lot of nuances at DAO part (tracking from which transaction entity was loaded, when it should be updated, etc).
So I need to write a lot of tests to cover most of the cases or it can cause problems for users :D

More Issues: