How can I use multiple databases without using transaction (I only want to read data, no Create/Upda...

# exposed

[vikrantghadge99]

How can I use multiple databases without using transaction (I only want to read data, no Create/Update/Delete)?

[vikrantghadge99]

For example,

Users.select { Users.id eq id }
    .map { ExposedUser(it[Users.name], it[Users.age]) }
    .singleOrNull()

if this can be wrapped within some function like,

withDatabase(db) {

[spand]

transaction {} takes a db parameter. Why dont you want to use that ?

[Andrew O'Hara]

Tangential: I really don't like how exposed lets you use transaction without a db parameter. It makes it possible to have a static dependency on a database that might not have been initialized.

[Andrew O'Hara]

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

val user1 = transaction(db1) {
  Users.select(filter)
    .singleOrNull()
    .toUser()
}

[vikrantghadge99]

Isn't there a difference between using a transaction vs not using one? I assumed the transaction() will create a database transaction which may not be ideal for simple select queries.

[vikrantghadge99]

I find the approach Ktorm library uses much clearer in this regard; database connection, transactions, etc. are all stated explicitly. I believe there should be at least an option to do similar in Exposed.

[spand]

Well there isnt

[vikrantghadge99]

Sorry, little confused 😄. What isn't exactly, 1. a difference between transaction/no transaction or 2. an option to explicitly specify database?

[spand]

1

[spand]

You already have two examples of 2

[vikrantghadge99]

I see. Thanks a lot!

[Andrew O'Hara]

It's sad there's no option to skip transactions, because Exposed seems to come with huge performance impact compared to raw JDBC. It's really slowed down my adoption of it.

[spand]

@Andrew O'Hara How sure are you that it has a huge overhead? We have also pondered if it was a problem but mostly brushed it off as premature optimization

[Andrew O'Hara]

Well anecdotally, I have a service with 3-4 t4g.smalls, handling 14k requests per minute, with an average response time of 13 ms. This service is already using a mix of exposed and raw JDBC. When I switched some more repositories over, the response time doubled to about 30 ms, and increased average database CPU utilization from 40% to 60%. This isn't the first time I've had performance problems, but it's a recent fairly extreme one.

I don't remember the exact access patterns, but it was mainly one-to-many relationships with a master resource, which could be retrieved individually, or in bulk. I don't know if this was additional overhead from Exposed, or if I just made a dumb error. As I said, this service is already using Exposed, but moving more repositories over from raw JDBC is no longer a no-brainer for me.

[spand]

Thanks for sharing