Hi, what is the best me for me to profile coroutines / Exposed? I am having some major performance issues, but struggling to narrow it down to a specific cause. My 2 very simple SELECT queries seem to be taking about 2 seconds.

[19:51:35 INFO]: [Nodes] [STDOUT] SQL: SELECT Users.id, Users.title, Users.town, Users.`role` FROM Users WHERE Users.id = '3e4bdaa7-33ae-3a69-a578-f43f78094268'
[19:51:35 INFO]: [Nodes] [STDOUT] SQL: SELECT Towns.id, Towns.`name`, Towns.nation_id, Towns.home_block_world, Towns.home_block_x, Towns.home_block_y, Towns.home_block_z, Towns.node_id FROM Towns WHERE Towns.id = '33d428d9-1b53-4b4b-bd74-c9583549a390'

Hi, I stumbled upon a rather strange error the other day (see discussion for example code). In retrospect I should have taken a better look at what the IDE imported, but still the effect was very confusing...

Hey folks, we’re looking into adopting exposed on the server-side for our new startup but have some concerns around the amount of boilerplate required • we define our migrations in a separate system so redefining the tables in exposed is troublesome, esp with drift. • we looked into the exposed gradle plugin but there’s some usability issues: ◦ multiple file codegen is disabled ◦ imports are incorrect which breaks compilation unless generated files are manually edited ◦ specifying schema in the connection properties doesn’t work - it pulls in all the tables from all the schemas Otherwise love the api and exposed itself, but just wondering how people deal with using exposed esp with separate migration systems (e.g flyway)

The following code produces a

Can't init value outside the transaction

, but its in a transaction. Has anyone encountered this before?

override suspend fun createUser(
        name: String,
        username: String,
        bio: String,
        igUsername: String?,
        tiktokUsername: String?,
        imageUrl: String,
        imageWidth: Int,
        imageHeight: Int
    ): User = dbQuery {
        val avatar = Image.new(UUID.randomUUID()) {
            url = imageUrl
            height = imageHeight
            width = imageWidth
        }
        val user = User.new(UUID.randomUUID()) {
            this.name = name
            this.username = username
            this.bio = bio
            this.igUsername = igUsername
            this.tiktokUsername = tiktokUsername
            this.avatar = avatar
        }
        return@dbQuery user
    }

internal suspend fun <T> dbQuery(block: () -> T): T =
    withContext(<http://Dispatchers.IO|Dispatchers.IO>) {
        transaction {
            if (System.getenv("environment").contentEquals("debug", ignoreCase = true)) {
                addLogger(StdOutSqlLogger)
            }
            block()
        }
    }

I have a micro-service in Ktor and I wanted to add a new column (attribute) in a Table. Let’s call this attribute

given_name

. Using Flyway, I’ve successfully added this new attribute to my table (I can see it using a DB file explorer) and I have added the following line :

object MyTable : ExtendedUUIDTable("whateverTable") {
    ...
    val givenName: Column<String> = text("given_name")
}

When I try to use this field, I get the following exception :

java.lang.IllegalStateException: com.innovorder.server.data.model.WhateverTable.given_name is not initialized yet

I found that Exposed is missing some wiki pages regarding doing DB migration. Did someone run into this error as well?

regarding this in the exposed sqlite

Database.connect("jdbc:sqlite:/data/data.db", "org.sqlite.JDBC")

does the exposed automatically created the file for you?

Is there any way to run a sort/select on a jsonb column in Postgres? (e.g.

order by my_json_column->'key' ASC

). Is it possible to write a raw/custom order expression for this? Not sure where to start.

Is there a efficient way to update entities without manually setting every field ? In repositories we update fields the following way:

Entity[id].apply {
      	updatedField = "test123"
      }

But this is a bit tedious if the objects are very large and/or contain lists. Is there a method that detects the changes between two objects (the entity object and an updated version of the object) and updates the entity accordingly?

I'm looking into using savepoints but what I see says it only works with raw connections. This means if I'm using a datasource with pooling I can't do savepoints? What I'm trying to implement is an "all or none" type functionality for updates using the DAO and not sure the best way to approach it. Let's say I'm writing three records to update a timestamp to the current time. If a write fails on any of them, I want to be able to rollback all three. Since a write to the DB only happens at the end of a transaction, aside from savepoint is there a way I can confirm all writes were successful and then rollback if it failed?

Hi, is there a way to add lateral to a join (PostgreSQL)? The current implementation is very close: https://github.com/JetBrains/Exposed/blob/master/exposed-core/src/main/kotlin/org/jetbrains/exposed/sql/Table.kt#L215 I only need " JOIN LATERAL " instead of " JOIN " Any suggestions on where to start?

Hi everyone. I'm trying to add a custom function for postgres (the

nullif

function). But I have no idea what to specify the column type as. Any thoughts?

In the DAO does a transaction return anything to inspect the results? For example if I update a record in a transaction, does it return any success/failure information or a result or will it only throw an exception if there's a failure to make the write? Similarly, for the DSL I see calling the Table.update returns an Int that looks like the number of rows actually updated? Does anything other than the expected number indicate a failure, or would those only be surfaced as exceptions?

I'm trying to modify an existing value in my update statement as follows

Guild.update({ Guild.id eq guild.id.toString() }) {
    it[data] = data + "abc"
}

For some reason, it always sets the value to "0" The table is defined like this

object Guild : Table() {
    val id = varchar("id", 18)
    val data = text("data").default("")

    override val primaryKey = PrimaryKey(id)
}

If I use

it[data] = "whatever"

then it actually sets to the proper value

I think this is a dumb question but I can't find the solution for this anywhere: I want to do this in Exposed:

update profiles set money = money * 0.95;

,

money

is a bigint field on the table. I tried doing this

Profiles.update {
                        with(SqlExpressionBuilder) {
                            it.update(Profiles.money, Profiles.money * 0.95)
                        }
                    }

However this doesn't work, because

Profiles.money

is a Long, so PostgreSQL doesn't allow long + double multiplication :(

How do I insert a newline in to a string column? I tried

\n

but it just inserts the raw string instead of a newline

Hey. I'm trying to do

query.orderBy(column)

This column is a string column, and I want the

orderBy

statement NOT to be case sensitive. How can I achieve that? (without changing the column altogether to

.lowercase()

Hi, with exposed what data type https://github.com/JetBrains/Exposed/wiki/DataTypes should I use for retrieving bytea from postgresql, is the binary type used for this

[Solved] I've got a join query where I have all data I need in a ResultSet

val innerJoin = ReceiptItems.join(Receipts, JoinType.INNER) { ReceiptItems.receiptId eq Receipts.id }
            .join(Items, JoinType.INNER) { Receipts.resultItemId eq Items.id }

Is it possible to

wrapRows

for a multiple entities, like

ReceiptEntity.wrapRows(query)

, and others? I can extract all I need from a

ResultSet

, but seems like iterating with

next()

is a bit boilerplaty

Damn, that

exposed

lib is so cool. I love you, guys. (had an experience with JPA, fcking dark magic there)

Got a question about exception handling when it comes to the fact that commits happen at the end of a transaction. Is my understanding correct that an exception in the sample code below would never be caught? Because the DAO style update doesn't commit to the DB until the end of the transaction we won't see an exception until that time, and that's outside the try/catch? Or is Exposed smart enough to know the exception happened as a result of that field update within the try block and catch the exception?

transaction() {               
    try {
        record.field = newValue
    } catch (e: Exception) {
        handleException()
    }
    doAThing()
}

Hi, I was trying to setup my first project with ktor and exposed but I'm coming across a strange issue... I created a Users table like this:

class Users : IntIdTable() {
    val username: Column<String> = varchar("username", 30)
    val email: Column<String> = varchar("email", 255)
    val password: Column<String> = varchar("password", 40)
}

now, if I try to invoke the functions of the Users class, Intellij gives no hints and screams about errors, for example I can't use

Users.insertAndGetId

as stated in the docs. Am I missing something?

Hey, I need to implement a fairly complex query that I don't know how to "translate"... I need to use the DENSE_RANK() window function with PARTITION BY and ORDER BY. Is there a way to do so?

A general question, how do you all handle opening transactions? For example, do you • open a new transaction when an endpoint is called, wrap the whole endpoint implementation in

newSuspendedTransaction { ... }

, and that’s it? • have all your opening of transaction blocks in the DAO layer or “close” to the models, hidden from the point of view of the endpoint logic? The problem here is that sometimes when dealing with Entity instances, you pass it down to some application logic and then realize you actually need an active transaction to get access to the properties of the entity. Since I don’t want explicit control of transaction rollback etc. in most cases, it feels unnecessary to try to be very fine-grained with it. Some other libraries like Ebean won’t require the user to open transactions at all, except they want to change and rollback multiple entities at once.

Hi there! I’m trying to cover the logic based on Exposed with unit tests and faced to a problem - how to create an entity manually to mock repository calls. Found the solution that works for “simple” entity fields but how to set manually entity relations?

class MyEntity(id: EntityID<UUID>) : UUIDEntity(id) {
    companion object : UUIDEntityClass<MyEntity>(MyEntities)
    var name by Migs.name
    var unit by Migs.unit
    val relations by MyRelationEntity referrersOn MyRelations.id
}

val myFieldIndex: Map<Expression<*>, Int> = mapOf(
        MyEntities.id to 0,
        MyEntities.name to 1,
        MyEntities.unit to 2,
)

val myResultRowData = arrayOfNulls<Any?>(3)
myResultRowData[0]=1
myResultRowData[1]="Name1"
myResultRowData[2]="Unit1"

val e = MyEntity(EntityID(UUID.randomUUID(), Migs))
e._readValues = ResultRow(fieldIndex = myFieldIndex, data = myResultRowData)

// e.setRelations < --- how to set relations ???

object UserRatings: IntIdTable() {
    val value = long("value")
    val film = reference("film", StarWarsFilms)
    val user = reference("user", Users)
}
class UserRating(id: EntityID<Int>): IntEntity(id) {
    companion object : IntEntityClass<UserRating>(UserRatings)
    var value by UserRatings.value
    var film by StarWarsFilm referencedOn UserRatings.film // use referencedOn for normal references
    var user by User referencedOn UserRatings.user
}

If I have the ID of a film how can I create a new rating with the DAO api? Example:

val filmId: UUID = getFilmId()
val rating = UserRating.new {
  // what goes here to set film?
}

Hi, I'm new to exposed, do you have any example on how to use it in a OOP way ? like I was prior to using exposed having

User

,

Channel

,

Message

etc classes, for a Compose Desktop project, but I now added a database and tables with what I was using, but when reading the documentation there's no way to transcript a

select

to a class instance with similar fields

Hi, when configuring Ktor with Exposed ORM, the Hikari CP is frequently used. Many tutorials about the Hikari configuration show the following setup:

private fun hikari(): HikariDataSource {
    val config = HikariConfig()
    config.driverClassName = "your db driver"
    config.jdbcUrl = "your jdbcUrl"
    config.maximumPoolSize = 3
    config.isAutoCommit = false //1
    config.transactionIsolation = "TRANSACTION_REPEATABLE_READ" //2
    config.validate()
    return HikariDataSource(config)
}

I see

config.maximumPoolSize

often set to 3. I am not sure why it is specifically set to 3 but understand the idea (that is described in the Hikari Wiki page here). On the other hand, what I really don't understand are commands

config.isAutoCommit = false

(1) and

config.transactionIsolation = "TRANSACTION_REPEATABLE_READ"

(2). Why are these two settings suggested when using Ktor with Exposed and not just sticking to default values?

Hey! I'm trying to test an application with an in memory sqlite database. My test class looks like this:

class ProjectRepositoryTest {
    companion object {
        val database = Database.connect("jdbc:sqlite:file:test?mode=memory&cache=shared", "org.sqlite.JDBC")
        val projectRepository = ProjectRepository(database)
        const val initialNumberOfProjects = 10

        @BeforeAll
        @JvmStatic
        fun setUp() {
            database.transactionManager.defaultIsolationLevel = Connection.TRANSACTION_SERIALIZABLE
            database.transactionManager.defaultRepetitionAttempts = 1
        }
    }

    @BeforeEach
    fun setUpEach() {
        transaction(database) {
            addLogger(StdOutSqlLogger)
            SchemaUtils.create(ProjectsTable)
        }
    }

    @Test
    fun testGetAllProjects() {
        println("Creating initial projects.")
        transaction(database) {
            addLogger(StdOutSqlLogger)
            repeat(initialNumberOfProjects) { i ->
                println("\tCreated project $i.")
                ProjectEntity.new {
                    name = "Project $i"
                    description = "Description $i"
                    archived = 0b0
                }
            }
        }
        ...
    }

    @AfterEach
    fun tearDown() {
        transaction(database) {
            SchemaUtils.drop(ProjectsTable)
        }
    }
}

I'm getting a database does not exist exception, saying that the Projects table doesn't exist. What am I doing wrong?

Hi, expanding on DAO#auto-fill-created-and-updated-columns-on-entity-change which uses

clientDefault

, are there any recommendations on what to do if I want

updatedAt

to be set to the same

datetime

as

insertedAt

when we do a

new()

? https://github.com/JetBrains/Exposed/issues/342#issuecomment-540487409 gives a nice server-side answer,

.defaultExpression(CurrentDateTime())

, but what if we want it client side so that the caller has the value set without committing the transaction? I noticed that the `clientDefault`s are `invoke`d in the order the columns are registered. Is is acceptable to rely on that behavior? If so, I could do something like:

private data class ControlledUtc(var currentTime: LocalDateTime = utcNow()) {
    fun getRefreshed(): LocalDateTime = utcNow().also { currentTime = it }
    fun get(): LocalDateTime = currentTime
}

private val controlledUtc = ControlledUtc()

val insertedAt = datetime("inserted_at").clientDefault { controlledUtc.getRefreshed() }

var updatedAt = datetime("updated_at").clientDefault { controlledUtc.get() }

Thoughts? Other ideas?

I'm trying to convert this query:

SELECT *,
    CASE
        WHEN reminder = 0 THEN start-$1::interval
        WHEN reminder = 1 THEN start-$2::interval
        WHEN reminder = 2 THEN start-$3::interval
        WHEN reminder = 3 THEN start
    END as notification
FROM "event"
WHERE start >= (now() + $4) AND active AND reminder <= 3
ORDER BY notification ASC LIMIT 1

The part I'm stuck with is subtracting from the

Instant

type column

start

, I'm looking for some kind of "interval literal" or something that I can use to add a

Duration

in there.

val start = timestamp("start")

I have tried durationLiteral, but I'm still unsure on how to add it:

.When(EventTable.reminder eq 0, EventTable.start - (durationLiteral(1.hours.toJavaDuration())))
            .When(EventTable.reminder eq 1, EventTable.start.minus(durationLiteral(1.hours.toJavaDuration())))