Looking for advice on schema-based (Postgres) multi-tenancy using Exposed and Ktor
- Dominant language
- Kotlin
- Stars
- 9.3k
- Forks
- 798
- Avg merge
- 4d 2h
- Merged PRs (30d)
- 26
Description
I'm looking for advice on a good way to handle schema-based multi-tenancy with Exposed. I'm getting tripped up because of Ktor and Coroutines and I'm coming from a Spring background where I would normally just use `MultiTenantConnectionProvider` from Hibernate.
Context:
- Ktor
- Postgres
- Identical Schema per Tenant (managed by Flyway)
- Schema name is derived from Auth
- Connection pooling using Hikari
Here is my current solution:
```kotlin
import org.jetbrains.exposed.sql.transactions.experimental.newSuspendedTransaction
suspend fun suspendedTransaction(schema: String, block: () -> T): T = newSuspendedTransaction(Dispatchers.IO) {
SchemaUtils.setSchema(schema = Schema(schema))
val result = block()
SchemaUtils.setSchema(schema = publicSchema)
result
}
```
Theoretically, this could work, too:
```kotlin
import org.jetbrains.exposed.sql.transactions.experimental.newSuspendedTransaction
suspend fun suspendedTransaction(schema: String, block: () -> T): T = newSuspendedTransaction(Dispatchers.IO) {
connection.schema = schema
val result = block()
connection.schema = publicSchema.identifier
result
}
```
Questions:
1. Is there a difference between the above?
2. Is there a better way of doing what I'm trying to do?
3. Is what I'm currently doing "safe" in a Ktor server context?
Thanks!
Contributor guide
Assessment
This issue has not been assessed yet.