where

andWhere

fun where(predicate: Op<Boolean>): Query {
    where?.let {
        error("WHERE clause is specified twice. Old value = '$it', new value = '$predicate'")
    }
    where = predicate
    return this
}

andWhere

fun Query.andWhere(andPart: SqlExpressionBuilder.() -> Op<Boolean>) = adjustWhere {
    val expr = Op.build { andPart() }
    if (this == null) expr else this and expr
}

where

class HogeService {
  suspend fun getArticle(id: String){
    newSuspendedTransaction {
       val article = articleRepo.find(id)
       // what ever other repo
       viewCountRepo.increase(article.id)
    }
    // other suspend logic here, like fetching some info from remote
    ....
    return article
  }
}

suspend fun <T : Any> dbQuery(context: CoroutineContext?, statement: suspend () -> T?): T? =
    TransactionManager.currentOrNull()?.withSuspendTrnsaction(context, statement)
    ?:newSuspendedTransaction(context, statement = statement)

class ArticleRepo {
  suspend fun find(id:String) {
     return dbQuery(Dispatchers.IO) {
           //....
     }
  }
}
// Same patter as ArticleRepo
class ViewCountRepo...

No value specified for parameter 3

class JsonContainsAnyExpression(
  expression: Expression<*>,
  elements: List<String>
) : CustomOperator<Boolean>("?|", BooleanColumnType(), expression, arrayLiteral(elements))

fun Expression<*>.containsAny(elements: List<String>): JsonContainsAnyExpression =
  JsonContainsAnyExpression(this, elements)

NullPointerException

newSuspendedTransaction

TransactionManager.currentOrNull()

delay()

get("/") {
    require(TransactionManager.currentOrNull() == null) { "1 - transaction should not exist at the start" }
    newSuspendedTransaction {
      require(TransactionManager.currentOrNull() != null) { "2 - transaction should exist now" }
      delay(2000) // Simulate some processing time - if I remove this delay things will work as I expect
    }
    // this next line fails
    require(TransactionManager.currentOrNull() == null) { "3 - transaction should not exist at the end" }
    call.respondText("OK")
  }

TransactionManager.currentOrNull()

newSuspendedTransaction

suspend fun main() {
  val database = TestPostgresDatabase

  val fixtures = Fixtures(database).initialise()

  coroutineScope {
    launch(Dispatchers.IO) {
      require(TransactionManager.currentOrNull() == null) { "1 - transaction should not exist at the start" }
      newSuspendedTransaction {
        require(TransactionManager.currentOrNull() != null) { "2 - transaction should exist now" }
        delay(2000) // Simulate some processing time
      }
      require(TransactionManager.currentOrNull() == null) { "3 - transaction should not exist at the end" }
      println("Transaction completed successfully ${TransactionManager.currentOrNull()}")
    }.join()
  }
}

TransactionManager.currentOrNull()

value: String

String

fun <T : Any, V: T?> Column<V>.compareWithString(value: String): Op<Boolean> {
    if(columnType is EntityIDColumnType<*>){
        val checkType = (columnType as EntityIDColumnType<T>).idColumn.columnType
        val typedValue = when (checkType) {
            is IntegerColumnType -> value.toInt() as T
            is LongColumnType -> value.toLong() as T
            is UUIDColumnType -> UUID.fromString(value) as T
            is StringColumnType -> value as T
            is BooleanColumnType -> value.toBooleanStrictOrNull() as T
            else -> error("Unsupported column type for comparison: $columnType")
        }
        return (this as Column<EntityID<T>>) eq typedValue
    } else {
        val typedValue = when (columnType) {
            is IntegerColumnType -> value.toInt() as V
            is LongColumnType -> value.toLong() as V
            is UUIDColumnType -> UUID.fromString(value) as V
            is StringColumnType -> value as V
            is BooleanColumnType -> value.toBooleanStrictOrNull() as V
            else -> error("Unsupported column type for comparison: $columnType")
        }
        return this eq typedValue
    }
}

load

1.0.0-beta-1

java.lang.NoSuchMethodError: 'kotlinx.datetime.Instant kotlinx.datetime.ConvertersKt.toKotlinInstant(java.time.Instant)'

suspend fun main() {
    R2dbcDatabase.connect("r2dbc:pool:<postgresql://postgres:postgres@localhost:54325/postgres>")

    val coroutineScope = CoroutineScope(SupervisorJob() + Dispatchers.IO)

    val lockId = 1337L

    val jobs = mutableListOf<kotlinx.coroutines.Job>()

    for (i in 1..10) {
        jobs += coroutineScope.launch {
            suspendTransaction {
                println("Task $i trying to acquire lock with ID $lockId")
                exec("SELECT pg_advisory_xact_lock(?)", listOf(
                    LongColumnType() to lockId
                ))
                println("Task $i acquired lock with ID $lockId")

                async {
                    println("Task $i started")
                    delay(1000) // Simulate some work
                    println("Task $i completed after 1 second")
                }.await()

                exec("SELECT pg_advisory_unlock(?)", listOf(
                    LongColumnType() to lockId
                ))
                println("Task $i released lock with ID $lockId")

            }
        }
    }

    jobs.joinAll()
}

Database.connect("jdbc:h2:mem:test", driver = "org.h2.Driver")

SqlException

ExposedSqlException

PSQLException

SQLException

suspendTransaction

kotlin

suspendTransaction(db = db) {
    SchemaUtils.create(UsersTable)
}

suspendTransaction(db = db) {
    // ===> Fails here
    UsersTable.insert {
        it[name] = "Alice"
    }
}

exec

batchUpsert

shouldReturnGeneratedValues: Boolean = true,

/**
 * Represents the SQL statement that either batch inserts new rows into a table, or updates the existing rows if insertions violate unique constraints.
 *
 * @param data Collection of values to use in batch upsert.
 * @param keys (optional) Columns to include in the condition that determines a unique constraint match. If no columns are provided,
 * primary keys will be used. If the table does not have any primary keys, the first unique index will be attempted.
 * @param onUpdate Lambda block with an [UpdateStatement] as its argument, allowing values to be assigned to the UPDATE clause.
 * To specify manually that the insert value should be used when updating a column, for example within an expression
 * or function, invoke `insertValue()` with the desired column as the function argument.
 * If left null, all columns will be updated with the values provided for the insert.
 * @param onUpdateExclude List of specific columns to exclude from updating.
 * If left null, all columns will be updated with the values provided for the insert.
 * @param where Condition that determines which rows to update, if a unique violation is found.
 * @param shouldReturnGeneratedValues Specifies whether newly generated values (for example, auto-incremented IDs) should be returned.
 * See [Batch Insert](<https://github.com/JetBrains/Exposed/wiki/DSL#batch-insert>) for more details.
 * @sample org.jetbrains.exposed.sql.tests.shared.dml.UpsertTests.testBatchUpsertWithNoConflict
 */
fun <T : Table, E : Any> T.batchUpsert(
    data: Iterable<E>,
    vararg keys: Column<*>,
    onUpdate: (UpsertBuilder.(UpdateStatement) -> Unit)? = null,
    onUpdateExclude: List<Column<*>>? = null,
    where: (SqlExpressionBuilder.() -> Op<Boolean>)? = null,
    shouldReturnGeneratedValues: Boolean = true,
    body: BatchUpsertStatement.(E) -> Unit
): List<ResultRow> {
    return batchUpsert(data.iterator(), null, onUpdate, onUpdateExclude, where, shouldReturnGeneratedValues, keys = keys, body = body)
}

12:14:21.307 [Test worker @kotlinx.coroutines.test runner#298] ERROR t.s.h.common.database.ExposedDB - Database operation threw: 
java.lang.ClassCastException: class org.jetbrains.exposed.v1.r2dbc.transactions.TransactionManager cannot be cast to class org.jetbrains.exposed.v1.jdbc.transactions.TransactionManager (org.jetbrains.exposed.v1.r2dbc.transactions.TransactionManager and org.jetbrains.exposed.v1.jdbc.transactions.TransactionManager are in unnamed module of loader 'app')
	at org.jetbrains.exposed.v1.jdbc.transactions.TransactionManager$Companion.getManager(TransactionManager.kt:162)
	at org.jetbrains.exposed.v1.jdbc.transactions.TransactionManager$Companion.managerFor(TransactionManager.kt:156)
	at org.jetbrains.exposed.v1.jdbc.transactions.JdbcTransactionInterfaceKt.getTransactionManager(JdbcTransactionInterface.kt:37)
	at org.jetbrains.exposed.v1.jdbc.transactions.TransactionManagerKt.keepAndRestoreTransactionRefAfterRun(TransactionManager.kt:470)
	at org.jetbrains.exposed.v1.jdbc.transactions.TransactionManagerKt.transaction(TransactionManager.kt:322)
	at org.jetbrains.exposed.v1.jdbc.transactions.TransactionManagerKt.transaction$default(TransactionManager.kt:317)

val versions =
  suspendTransaction {
    Applications
      .selectAll()
      .toList()
  }