We use a more complex setup for our postgres database in production with read/write and read-only nodes (I think there is also a loadbalancer involved, I am not the database infrastructure person). We tried to express this using Exposed by having 2 DatabaseConnections and then, depending on the need to read or write in a give context, use

transaction(db = readDB)

or

transaction(db = writeDB)

. But when I have an entity that was once retrieved via a readDB connection and later use that entity to write it using the writeDB connection, I get:

Can't link entities from different databases

Is there a workaround for this? Or does anyone else have experience with / a different approach for multiple DB nodes and using them with Exposed?

I havent used exposed dao but maybe you can create a generic method that can copy Entities into the new context ? Seems a bit unavoidable to do something explicitly to the entity objects

I'm not sure, but do you really need to reuse the same entity from a read-only transaction in the new write transaction? It may possibly contain some stale data, have a different transaction isolation level and so on

I think you are correct, we probably need to switch our approach to decide which DB connection to use on a broader level, like if anything for a request call possibly write to the database, use the write connection for all transactions. Because I looked into copying entities to the new context, that seems like way more manual work than just being fine-grained with the chosen db connection

Yeah. I am not sure how you resolve data races but that also gives you access to select for update