I see similar problems to: https://github.com/JetBrains/Exposed/issues/1264 and https://github.com/JetBrains/Exposed/issues/1360, they have been open since 2021. Is nested transactions not supported?

Hello, everyone. I'm having a coroutine problem, giving the following exception

Exception in thread "main" kotlinx.coroutines.CoroutinesInternalError: Fatal exception in coroutines machinery for DispatchedContinuation[BlockingEventLoop@7ca48474, Continuation at org.jetbrains.exposed.sql.transactions.experimental.SuspendedKt$suspendedTransactionAsyncInternal$1.invokeSuspend(Suspended.kt)@59f99ea]. Please read KDoc to 'handleFatalException' method and report this incident to maintainers

Hey all, I’ve been making an effort to help out with the gradle plugin for code generation and have submitted some PRs to the repo, specifically allow other datetime providers and fixing codegen issues with Flyway (the PrimaryKey import problem). Only posting here in case the conversation is better suited for slack than github https://github.com/JetBrains/exposed-intellij-plugin/pull/15

Hello everyone, I am currently working on a full-stack web app using the tutorial found here: https://kotlinlang.org/docs/multiplatform-full-stack-app.html#include-kmongo-in-the-process. We have been using MongoDB and KMongo, but I am trying to replace MongoDB with Postgres DB and Kmongo with Exposed. As an example, using Kmongo, we have this code: val client = KMongo.createClient().coroutine val database = client.getDatabase("shoppingList") val collection = database.getCollection<ShoppingListItem>() get { call.respond(collection.find().toList()) } I am wondering if there is a way to do the same thing with Exposed. My current understanding is to create an object DatabaseFactory for setup in Server.kt, create a Table and then use select:

object DatabaseFactory {

fun init() {

Database.connect(

"jdbc:<postgresql://host>:port/5432",

driver = "org.postgresql.Driver",

user = "user",

password = "password"

)

}

}

object ShoppingListItems: Table() {

val id = integer("id").autoIncrement().primaryKey()

val name = varchar("name", length = 255)

val owners = varchar("owners", length = 255)

}

get {

call.respond(ShoppingListItems.select { ShoppingListItems.owners like user }.toList())

}

I am also wondering if we need to create a table. Instead, I want to use a data class like this: val collection = database.getCollection<ShoppingListItem>(). Thank you in advance for your help!

Are there any example projects where Exposed is used without everything being statically defined? So instead of

object

tables and

(companion) object

EntityClasses, have classes that are instantiated e.g. in ktor module on app start, which would give the ability to properly pass in dependencies instead of relying on global variables? For example, for my own extensions of Exposed for handling jsonb columns, I need to pass in a Jackson

ObjectMapper

. Right now, I have a global object where I set a var on application start, but the Exposed part is the only part of my ktor application not having dependencies properly passed in via constructor params (I don’t use any DI framework).

hello everyone, i'm stuck with .sum() aggregate function. I have a column of type ushort and i have a query which should retrieve a sum of the column values. Even though the values are ushort (SMALLINT UNSIGNED) resulted sum might be out of the ushort range. And for plain MariaDB query in console it's absolutely legit query. But

MyTable.uShortCol.sum()

truncates result to ushort, obviously. Any adwise? I've found an issue on the GitGub https://github.com/JetBrains/Exposed/issues/1035 which seems relevant to me but there's no solution

Hello, evenryone! I`m a novice with a little question: how could I create a table`s column with a check constraint between values of two columns like in the example (

"limit" >= balance

):

CREATE TABLE IF NOT EXISTS account (
  id          uuid PRIMARY KEY,
  balance     DECIMAL(16,2) NOT NULL DEFAULT 0 CHECK (balance >= 0),
  "limit"     DECIMAL(16,2) NOT NULL DEFAULT 0 CHECK ("limit" >= balance)
);

How could I get the current value of column here?

object AccountTable : Table("account") {
...
val limit: Column<BigDecimal> = decimal("limit", 16, 2)
    .default(BigDecimal.ZERO)
    .check {it.greaterEq(??????)}
}

Hello everyone. I am thinking of using exposed for the next customer project. Are there any experience of using it in the field? I mean, it’s version number is still 0.X and it is not supported by JetBrains but a staff project. Do you have any references to customers using exposed commercially / professionally?

Hey all! does Exposed expose (🤭) any monitoring metrics i.e. connection timeouts, etc. out of the box? I have tried to find those in the code, but couldn't find any hint for that.

set the channel topic: https://github.com/jetbrains/exposed Some words around Exposed usage in prod: https://kotlinlang.slack.com/archives/C0CG7E0A1/p1661259081172889

Inside the transaction, can I update the data immediately before the transaction ends? Or am I supposed to split transaction myself?

Is it no-op to nest transactions by default?

can anyone give me a sort introduction how can i work with view table with exposed and ktor ?

PostgreSQL and Exposed - Kotlin database access

Using PostgreSQL as an Append-only Datastore with Kotlin and Exposed

HI, is it possible in the DAO api to transform multiple DB fields into a single DAO field? e.g. in DB I store

portion

(EARLY, MID, LATE) and

month

and I have data class for it and I would like the DAO to have such field instead of 2 fields

Is anyone actively maintaining Exposed at this point? Would like to understand (given there are a number of issues and PRs pending)

Hello, I added a benchmark portion in the TechEmpower Framework Benchmarks project to test the permance of Exposed DSL and DAO on top of the existing Ktor benchmark at https://github.com/TechEmpower/FrameworkBenchmarks/pull/7995. Maintainers and contributors please help me check if this is OK if you guys have time.

Does Exposed have a way to stream records or batch fetch records for

findAll()

I am trying to process ~400k records in a cron-job and I don’t want to load all of them into memory at once. I’ve been googling for Kotlin Exposed Sequence or Kotlin Exposed Iterable but I am not sure if they actually only pull records on demand from postgres or if they just expose the result of the query as an Iterable

Hello there! I am looking for help. We have created two IdTables (Boats and Customers) and their corresponding Classes to actually create new Entities in the database. Customers can rent a boat up to a date must return it after that. To keep track of these rents, we created a third Table called "Rents" which inherits from the "Table" class. BUT how do we create entities for that third table?

It is tricky for us because the primary key is not an int. It is a compound key which consists of the CustomerID and the BoatID. It looks like this in our code:

object Rents : Table("T_Rents") {
    val customerId = reference("Customer_Id", Customers.id)
    val boatId = reference("Boat_Id", Boats.id)
    val dateOfRent = datetime("Date_of_rent")
    val dateOfReturn = datetime("Date_of_return")

    override val primaryKey = PrimaryKey(customerId, boatId, name = "CustomerAndBoatID")
}

How would we create an entity in this table?

We also face problems with auto-incrementation because we need it to work within a specified range of numbers like 1000-9999. At the moment we have a bad workaround which really is a scuffed auto incrementation implemented by ourselfs. It looks like this:

integer("Customer_Id").check { it.between(1000, 9999) }.uniqueIndex().entityId().also {
    it.defaultValueFun = {
        val incrementId = (1000 + Customer.count() + 1).toInt()
        EntityIDFunctionProvider.createEntityID(incrementId, this)
    }
}

How would one use auto-incrementation within a specified range? I appreciate any help!

Hi 👋! We are building a feature where we are supposed to encrypt some specific column values and one detail is that we are supposed to have different encryption keys depending on the tenant that is doing the request. We have found two approaches that we are evaluating. Approach 1: We write a class implementing

ColumnType

that will be responsible for encryption and decryption when constructing the queries and returning results. We will need to create multiple instances, one per tenant, so that the appropriate encryption key is used. A snippet demonstrating this idea can be found here: https://pl.kotl.in/9azT-Jlr2 What we found is that this solution works when only using DSL but we are also using DAO to define relations and Entities are constructed by the Exposed library (using reflection?). To my knowledge there is no way of constructing Entities, passing our

Table

instances to it. This creates a problem for us as the codebase is using many DAOs today. Does anyone know if there is a way to get around that in any way and we can provide our own "factories" for creating

Entity

instances? Also, I sense that this approach is a bit “hacky” to depend on the tenant inside the DB layer. So this made us look into a second approach. Approach 2: We handle encryption/decryption in a repository. To make sure we can’t mix up what is encrypted and what is not we are introducing a new type for encrypted text and then writing a

ColumnType

for it to wrap/unwrap the type. We then move the responsibility for encryption/decryption to the repository. We will have different repository instances scoped to tenants so that they can fetch the appropriate encryption key. This is an example how it would look: https://pl.kotl.in/E0ewX3hZ9 This approach will introduce more boilerplate but in this case I think it's not too bad as we will get type safety and it will be impossible to confuse encrypted text with a String. 1. Which would you say is the best approach? 2. Is passing scoped repositories into the ColumnTypes the wrong abstraction and is it better to keep them as simple as possible?

Can someone please point me to a resource that explains FK relationships with exposed? For instance, I noticed there are multiple ways to declare relationships, so I'm a bit confused. I'd prefer to work with a standard way or something

Hi, I am trying to use exposed DSL to simplify accessing legacy SQL server database that already exists. I need only read-only access to some of the complex tables and I was curious, do I need to define all of their columns to do that or can I specify only the ones I am interested in?

If no resources, I'd appreciate if someone describes how to add many-to-many, many-to-one and one-to-one relationships in the model.

We have some tables created with qoutes in postgres, thus case sensitive, but exposed seems to make the table name lowercase even if it is in quotes (https://github.com/JetBrains/Exposed/issues/1658), is there any existing workaround?

I have exposed code along the lines of this (names changed):

private suspend fun <T> executeQuery(timeout: Duration = 3.seconds, block: suspend Transaction.() -> T): T =
	withTimeout(timeout) { newSuspendedTransaction(<http://Dispatchers.IO|Dispatchers.IO>) { block() } }

public suspend fun test1() = executeQuery(10.seconds) {
	with(SomeTable) {
		slice(id, other_id, valid_from).select {
			(status eq BigDecimal.ONE) and (someFlag eq BigDecimal.ZERO) and
			(CurrentDateTime.between(valid_from, valid_to))
		}.forEach {
			println("$it")
		}
	}
}

but I am getting

Exception in thread "main" java.lang.IllegalStateException: Unexpected value: com.example.MayDatabase$SomeTable.valid_from of org.jetbrains.exposed.sql.Column

I am not sure what I am doing wrong. Very similar code to this worked before

I have tried to add

HASHBYTES

as

CustomFunction

as this:

class HashBytes(algo: Algo, input: Expression<*>) : CustomFunction<ExposedBlob?>("HASHBYTES", BlobColumnType(), QueryParameter(algo.algoName, TextColumnType()), input) {
        enum class Algo(val algoName: String) {
            MD2("MD2"),
            MD4("MD4"),
            MD5("MD5"),
            SHA("SHA"),
            SHA1("SHA1"),
            SHA256("SHA2_256"),
            SHA512("SHA2_512"),
        }
}

but when use I it in

slice(someColumn, otherColumn, HashBytes(HashBytes.Algo.MD5, Concat("", someColumn, otherColumn)))

I get the text representation of the hashbytes query in the results instead of the resulting value