JetBrains / JetBrains/Exposed

Looking for advice on schema-based (Postgres) multi-tenancy using Exposed and Ktor

Open
#1,493 3 comments 0 reactions 0 assignees View on GitHub
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.