Exposed: Multiple databases

[dakuenjery]

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?

[DrFriendless]

Yes, the basic issue is that connecting to a database sets a static, then transaction uses that static. If you want you can catch the thing from the static and use it explicitly. Ilya did write an example of this for me but I can't find it at the moment. If you haven't seen this, it might help:

https://github.com/JetBrains/Exposed/blob/master/src/test/kotlin/org/jetbrains/exposed/sql/tests/shared/ThreadLocalManagerTest.kt

[Tapac]

At the moment there is no a easy-to-use way to manage multiple connections within one thread (concurrent transactions), but not so long time ago I made a prototype of nestedTransaction function for someone was asking for same thing at Slack channel.

val dbToTM = hashMapOf<Database, TransactionManager>()

fun <T> Database.nestedTransaction(statement: Transaction.() -> T): T {
    val requiredManager = dbToTM[this]
    val existingManager = TransactionManager.manager

    return if (requiredManager == existingManager) {
        transaction {
            statement()
        }
    } else {
        try {
            TransactionManager.manager = requiredManager!!
            transaction {
                dialect.resetCaches()
                statement()
            }
        } finally {
            TransactionManager.manager = existingManager
            dialect.resetCaches()
        }
    }
}

fun registerDatabase(datasource: DataSource): Database {
    return Database.connect(datasource).apply {
        dbToTM[this] = TransactionManager.manager
    }
}

fun registerDatabase(url: String, driver: String, user: String = "", password: String = ""): Database {
    return Database.connect(url, driver, user, password).apply {
        dbToTM[this] = TransactionManager.manager
    }
}

object FooTable : Table("Foo") {
    val name = text("name")
}

// Test Data
object BarTable : Table("Bar") {
    val value = integer("value")
}

fun main(args: Array<String>) {
    val fooDB = registerDatabase("jdbc:h2:~/foo", "org.h2.Driver", "root")
    val barDB = registerDatabase("jdbc:h2:~/bar", "org.h2.Driver", "root")

    fooDB.nestedTransaction {
        SchemaUtils.create(FooTable)
        barDB.nestedTransaction {
            SchemaUtils.create(BarTable)
            assertTrue(BarTable.exists())
            assertFalse(FooTable.exists())
        }
        assertFalse(BarTable.exists())
        assertTrue(FooTable.exists())
        barDB.nestedTransaction {
            assertTrue(BarTable.exists())
            assertFalse(FooTable.exists())
            SchemaUtils.drop(BarTable)
            assertFalse(BarTable.exists())
        }
        SchemaUtils.drop(FooTable)
    }
}

You can try it out and leave your feedback and we will think about including it to Exposed.

[dakuenjery]

I tested your code and it works. Thanks!
But I think it would be better if database would have its own TransactionManager and wouldn't require switching. If possible I try to do it and send pull request. Did this feature is required? Maybe optional?

[Tapac]

Sounds good, but IMO, we should not to force users to remember witch Database's manager to ask for current transaction.
I.e. that code should to work as before:

transaction {
    ... 
    TransactionManager.current().commit() // or some analog like currentTransaction()
}

What means that Database instance (among with it's TransactionManager) should be bound to a thread/execution scope.
If you have any idea how to implement it, your PR is more then welcome!

[PapaCharlie]

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.

[mathiasbn]

dialect is internal. Is there a workaround? And any plans to include a solution to the project?

[Miha-x64]

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.

[wfxr]

I totally agree with @Miha-x64 's opinion. Bad idea to design db connection as a singleton. Why there must only be one connection? Just think of the bad design of Java DateTimeFormatter.

[Anti-Alias]

If I wanted a global db value, I'd make it myself.

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

// Code that uses Databases

It should not be the default.

[Tapac]

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.

[oshai]

how about doing something like this:

  withConnection(datasource) {
    transaction {
      ...
    }
  }

and withConnection will be something that keeps the previous TransactionManager and then set it back in finally?

[oshai]

@Tapac any estimation when this will be addressed?

[Tapac]

Hopefully until end of the year, but this is not a promise because I have a lot of work on the main project at JetBrains.

[oshai]

Is there any way I can help? Do you have a plan?

[Tapac]

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

[Tapac]

Released at 0.10.1

[raderio]

Is there an example how it should be? Maybe add an example in readme?

[Tapac]

https://github.com/JetBrains/Exposed/wiki/Transactions#working-with-a-multiple-databases

[raderio]

Thanks.
But why did you choose this approach? Why not

db.transaction {
    // ...
}

It is not clear from API that a transaction can receive a database.
If someone need a GLOBAL database then just store it in a object with constants

Also, what if I have 2 instances of Database and use like

transaction {
}

it should throw an exception, or why one is used instead of other? It can lead to errors.

[Tapac]

If we provide db.transaction someone will ask: "Which one of transaction I should use with or without DataBase receiver?" But I think that we might re-review that place in the future.

By default transaction uses the last connected Database as a target. A lot of users (afaik) call Database.connect just before call transaction and in that case, their code will start to fail as there will be multiple Database instances.

I think that we will rewrite that part of the code while working on a coroutine support.

[raderio]

"Which one of transaction I should use with or without DataBase receiver?"

You can deprecate the old one and after delete it.

I think that we will rewrite that part of the code

+1

[raderio]

Can you please reopen the issue or will create a new one?