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

[nhalase]

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:

import org.jetbrains.exposed.sql.transactions.experimental.newSuspendedTransaction

suspend fun <T> 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:

import org.jetbrains.exposed.sql.transactions.experimental.newSuspendedTransaction

suspend fun <T> 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!

[edeak]

This looks a good approach to me. Another option could be having separate Database instance for all the tenant databases, but that would make connection pooling trickier and probably it would waste a lot of resources.

First approach looks more robust because it delegates the database specific switch to the framework, so if you switch from Postgres, you probably don't have to rewrite this part.

[cbergau]

We are having the same thing right now.

Actually, in order to make it work, we have to put the schema name in double quotes, so the resulting sql is

SET search_path TO "CUSTOMER123"

Here the Exposed Code:

transaction {
    SchemaUtils.setSchema(schema = Schema("\"$clientId\""))
    // whatever query here
}

I do not like this solution. Also i am not sure about security here.