Greetings! I'm evaluating switching my Spring Boot project over to Ktor. Here's some relevant information: • The service is a resource server with Auth0 as the provider. • The original Spring Boot service is using schema-based tenant isolation. ◦ Using

org.hibernate.context.spi.CurrentTenantIdentifierResolver

and

org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean

to set the database schema using

SecurityContextHolder.getContext()

to determine the schema • The Ktor service is attempting to do the same schema-based tenant isolation, but instead is doing this with `Exposed`:

import org.jetbrains.exposed.sql.Table

fun getDynamicSchemaName(): String {
    TODO("How can I get access to the request-scoped JWTPrincipal to determine this?")
}

open class SchemaTable(private val name: String) : Table() {
    override val tableName: String
        get() = "${getDynamicSchemaName()}.$name"
}

Here is an example `Route`:

@ExperimentalCoroutinesApi
fun Route.fooFindAllRoute(fooService: FooService) {
    authenticate("auth0") {
        get("/foo") {
            val principal = call.principal<JWTPrincipal>() ?: error("missing JWTPrincipal on authenticated request")
            call.respond(fooService.findAll(principal).map { it.toResource() })
        }
    }
}

I don't like this because it poops up my service API by requiring the

principal

. --- Is there a better way to do what I'm trying to do here with Ktor and Exposed?

Not really sure where to ask this question, as it is Database related I will ask it here. We have a database with a table containing around 8Billion records. When we query this database using intellij and the sql-server driver. the response is usually within 10-20 seconds. If we run exactly the same query, with exactly the same sql-driver on the same development machine the query will take around 14 minutes. Is there any way to "capture" what intellij sends / does with this query.

Does anybody have any good example projects where there's Ktor used in combination with Exposed, and where I could find good examples as to how I can do CRUD properly? I can't seem to understand what would be a good way to send a JSON object from the browser to the server and then using the

ContentNegotiation

plugin to deserialize and edit an entry in the database.

Hi all! any suggestion how to run this kind of query in postgresql with expose?

UPDATE events_affecting_transit 
SET estimated_affect_end_time = estimated_affect_end_time + interval '5 hour'
WHERE current_timestamp > estimated_affect_end_time

class UserEntity(id: EntityID<Long>) : LongEntity(id) {
    companion object : LongEntityClass<UserEntity>(UserTable)
    var username by UserTable.username
}

class TestEntity(id: EntityID<Long>) : LongEntity(id) {
    companion object : LongEntityClass<TestEntity>(TestTable)
    var author by UserEntity referencedOn TestTable.autor
//    var author by TestTable.author // not this way
}

transaction {
    TestEntity.all().first().author // triggers fetch of entire entity
    TestEntity.all().first().readValues[TestTable.author] // returns the desired id but is a little awkward
}

I want to benefit from the dao api by fetching entire related entites (like author here). However, there are some use cases when the id of the referenced entity is sufficient and I don't want to cause another database query to fetch the other unneeded columns. The only - but awkward - way I found to achieve this was by using "readValues". Is there any better solution that I have overlooked?

I have a question

Hi all, I’m facing an issue with the latest exposed version 0.38.2 (also with 0.38.1, but cannot be reproduces with 0.37.3) in combination with HikariCP:5.0.1 and Postgres. For some reason when the maximumLifetime of the connection pool is reached which is 30min by default and the connections are renewed every call afterwards is producing a PSQLException complaining about the transaction isolation level. Am I doing something wrong or should i open a bug ticket since it worked with earlier versions?

"level":"WARN","stack_trace":"org.postgresql.util.PSQLException: Cannot change transaction isolation level in the middle of a transaction.\n\tat org.postgresql.jdbc.PgConnection.setTransactionIsolation(PgConnection.java:946)\n\tat com.zaxxer.hikari.pool.ProxyConnection.setTransactionIsolation(ProxyConnection.java:420)\n\tat com.zaxxer.hikari.pool.HikariProxyConnection.setTransactionIsolation(HikariProxyConnection.java)

I have this function to delete a model:

fun delete(id: Int) = transaction {
        CarEntity[id].delete()
    }

How can I return true if it worked, else false ? Using DSL I can do : `

Posts.deleteWhere { Posts.id eq id } > 0

but it doesn’t work with DAO

SOLVED: Answer in thread Is there a way to increase a timestamp field by a certain duration? SQL:

UPDATE server_premium
    SET expiration = expiration + $1
    WHERE server_id = $2
    RETURNING expiration

Where $1 is a python timedelta, that was converted to a Postgresql Interval I'm trying to figure out how to do it in exposed.

Good morning everyone. I can return my user in a mapping like :

val user = userDSL.show(id)
            call.respond(mapOf("user" to user))

but it won’t let me do

call.respond(user)

I get this error:

Type mismatch: inferred type is User? but TypeVariable(T) was expected

why ?

Hi all, I've been thinking about generating Exposed Tables (and maybe entities) from POJO entity classes, I am looking for a way to keep my domain models pure data classes, so don't clutter them with JPA annotations, while preventing having to write and maintain a separate database definition like with Exposed DSL + DAO. Komapper comes close, but uses KSP and meta classes to map annotations on, inducing the need to maintain these together with the domain models, and preventing IDE level type checking. What I have in mind is a DSL based configuration per entity Anyone seeing any (fundemental) obstacles on the road for a solution like this? Or knows a library that does something like this?

I upgraded from 0.37.3 to 0.38.2 a few days ago and have started seeing some

java.lang.NullPointerException

being thrown. It's only a few hundred out of many millions of calls, but it's still odd. This column is definitely not nullable, and I have confirmed there is proper data in every row. Anyone else seen this?

java.lang.NullPointerException
	at org.jetbrains.exposed.dao.Entity.lookup(Entity.kt:193)
	at org.jetbrains.exposed.dao.Entity.getValue(Entity.kt:173)
	at com.company.user.data.Profile.getBirthDay(Profile.kt:19)

It's looks like this in code:

object ProfileTable : LongIdTable("profile") {
    val birthDay = timestamp("birthDay")
    // etc
}

class Profile(id: EntityID<Long>) : LongEntity(id) {
    companion object : LongEntityClass<Profile>(ProfileTable, Profile::class.java)

    var birthDay by ProfileTable.birthDay
    // etc
}

Hello everyone! Exposed is wonderful. I manage to build API based on a very related MySQL database with very simple code, but I can’t understand why my DAO is performing so many queries instead of a single one. This is my DAO. When I do

findById

I see a large number of SELECT queries instead of a single one. This could be a problem if server and database are not in the same network. Is there any way to force Exposed use a single query?

class HighlightDAO(id: EntityID<Int>): IntEntity(id) {
    companion object : IntEntityClass<HighlightDAO>(Highlights)
    val video by VideoDAO referencedOn Highlights.video
    val preview by FileDAO referencedOn Highlights.preview
    val title by Highlights.title
    var createdAt by Highlights.createdAt
    var match  by MatchDAO referencedOn Highlights.matchId

    var players by PlayerDAO via HighlightsPlayers
    val events by HighlightEventDAO referrersOn HighlightEvents.highlightId

    val homeScore by Highlights.homeScore
    val awayScore by Highlights.awayScore
    val empty by Highlights.empty
}

...

HighlightDAO.findById(highlightId)

Hello all. Is it possible, using exposed, when it receives a request, it creates a table in the database?

Hey folks, does

ignore=True

on

batchInsert

work for postgres? I seem to be getting an error on the insert for duplicate key, but I thought the ignore flag would take care of this...

Hello everyone, is it possible to do common table expression like this example with exposed ?

WITH ten_parents AS (SELECT * from parent LIMIT 10)
SELECT *
  FROM ten_parents p
    LEFT JOIN child c
      ON c.parent_id = p.id

The goal here is to limit a join request but only for the parents, as they can have several children 🙂

Is it possible to transform

Column<EntityId<T>>

to

ExpressionWithColumnType<T>

in some way?

Hi, I'm interested in contributing exposed(from "good-first-issue"). Cloning repository, and I tried to run tests by

./gradlew test

as mentioned in wiki. But some tests failed. I understood that before tests, test classes download some database images, and it seems that test scripts(?) could not build docker images. Would you please tell me how to run tests succesfuly?

Hi guys, is it possible to someshow assign

transaction

to a variable and then something like

transaction.begin()

and

transaction.commit()

?

I'm trying the basic examples in the README and am getting compile errors for primary key

Any guidance?

resolved by using

org.jetbrains.exposed:exposed-core:0.38.2

instead of

org.jetbrains.exposed:exposed:0.17.14

how are folks structuring code for objects that extend

Table()

? In particular, say I want to do

SomeTable.insert { ... }

-- then I need to import

import org.jetbrains.exposed.sql.*

to get the

insert

extension This means, say, if I want to do this operation some place other than the

SomeTable

impl itself, I have that broad

import

statement This is different coming from popular ORMs in ruby, django -- not a bad thing per se Three ways to structure I can think of: • write wrapper instance methods for the the

SomeTable

that call insert under the hood • do the broad import • something else? I'm tentatively inclined to do the latter -- thoughts?

Really basic q. Suppose I do

SomeTable.id.count()

-- how do I get as int?

Hello all, WIth Postgres Is there a way to check if a schema exists using exposed? I can find out using psql with the command

SELECT schema_name FROM information_schema.schemata WHERE schema_name = 'schema';

hey! is there any way of safely casting

Column<EntityID<UUID>>

as

Column<UUID>

? the function I’m trying to write isn’t really concerned whether something is a primary key or not. backend I’m using is psql

Hey there, is it possible to model an one-many relation which is owned by the "one" side? I'm try to rebuild the @OneToMany annotation of JPA (including cascade update + delete) but could not find a solution.

Hello, I have an SQL error, but I don’t find where

INSERT INTO gateways (gateway_id, gateway_sub_type, gateway_type, migration_status) VALUES ('VIRT048', 14, 15, 0) ON DUPLICATE KEY UPDATE gateway_id=VALUES(gateway_id), gateway_type=VALUES(gateway_type), gateway_sub_type=VALUES(gateway_sub_type), migration_status=VALUES(migration_status)

I get ERROR: syntax error at or near “DUPLICATE” But with an online checker, it says it’s correct