https://www.marcobehler.com/guides/jdbc https://www.marcobehler.com/guides/java-databases These are the two articles. Must read for everyone beginning with Databases in the JVM world. I would also write an article on this for new Kotlin programmers who are starting with databases to save them from the nightmare I faced.

I need to implement pagination and this issue has been bugging me for hours now.

heya all, i'm using the exposed kotlin dao, with a basic intidtable. how can i at runtime create tables based on the schema of that table with different names of my choosing?

It would be great if SchemaUtils.drop and .create would be smart enough to run the list in the varargs version in an order that would work using references info to know with foreign keys would get in the way...

What is the difference between ie.

booleanParam()

and

booleanLiteral()

? I can look at the code but the different use cases escape me. They seem extremely similar

I've found weird transaction behaviour on MYSQL. having

object TransactionTestTable: LongIdTable(){
    val foo = integer("foo").check { it greater 0 }
}

when I do

newSuspendedTransaction {
    TransactionTestTable.insert { it[foo] = 1 }
    throw IllegalStateException("FAIL")
}

the transaction performs a rollback correctly and nothing gets inserted into the table but when I do

newSuspendedTransaction {
    TransactionTestTable.insert { it[foo] = 1 }
    TransactionTestTable.insert { it[foo] = 0 }
}

the transaction still fails, but the first value gets inserted in the database. Is this intentional or a bug?

Is there a changelog for 0.36.1 somewhere?

How do I generate a unique id like INV0001 using exposed?

For example: right now I have problem with how to use aliases. I don't fully get them and I'm not good enough at kotlin to understand it from reading Exposed source code (I tried). How do you use alias to create function transforming ResultRow to Domain data?

I have a very complex set of relationships of a bunch of tables, and from table A, I want to be able to access A.B.C.D.name... it'd be a pity to have to create an entity for each one, load them all and traverse them all just to access that field's value...

some thoughts about Exposed on Reddit (not my post): https://www.reddit.com/r/Kotlin/comments/qqp87z/thoughts_aloud_about_exposed/

@tapac How could I be running into this issue https://github.com/JetBrains/Exposed/issues/186 when I'm currently holding by

com.h2database:h2:{require 1.4.200; reject _} -> 1.4.200

...?

Hello Can any one help how can I do this simple SQL insert in Exposed INSERT into Table 1(c1,c2,c3) Select c1,c2,c3 from table2

Hi all, not sure whether this channel or the ktor channel might be better suited for this question, but I'll give a try here first. We have a service that handles data for multiple customers. We want to improve data isolation by creating different database users for each of our customers. We must therefore tell Exposed to dynamically use different credentials when it retrieves a database connection. One of the provided Database.connect functions should allow us to do exactly that:

fun connect(getNewConnection: () -> Connection, ...)

We use ktor as our http backend and now the only problem is how we can get the auth information from ktor into this lambda. We are able to store the required info in the pipeline context of ktor. So what we specifically try right now is to get PipelineContext data into the getNewConnection lambda:

Database.connect(getNewConnection = {
    val jdbcUrl = "jdbc:${DbConfig.jdbcProtocol}://${DbConfig.hostname}:${DbConfig.port}/${DbConfig.database}"
    val user = "" // TODO how to get the data from ktor pipeline context into this lambda here?
    val password = ""  // TODO how to get the data from ktor pipeline context into this lambda here?
    DriverManager.getConnection(jdbcUrl, user, password)
})

Has somebody tried such a thing already? We need to somehow make Exposed aware of the ktor pipeline context, so we can set the credentials for the database based on the rest call at hand. Any tips or suggestions at all would be very much appreciated.

Hi! I'm working on ClickHouse support for exposed, and the problem is that its

javax.sql.Cnnection

implementation supports only small subset of interface features. So, I wrote my own

ExposedConnection

implementatiion, but I don't know how to pass it in

Database

. In other words, how can I reimplement

org.jetbrains.exposed.sql.statements.jdbc.JdbcConnectionImpl

and pass it in

Database.connect()

?

Hey, after 2 years, H2 released a new version: 2.0.202, which has some incompatibles. Should Exposed support both versions, or only the latest one? Often, H2 db's are used for testing, so supporting latest version only would be possible. The maintainer of H2 also support the latest version only and won't backport any fixes. Currently, I tried to adopt the versions changes in Exposed and I have some problems with the MySQL mode, because it is very buggy from H2 and fixes are only available in latest H2 version.

Hey! anyone know if Exposed supports OffsetDateTime as a column type? or any suggestions how to work around not being able to save a timezone would be really appreciated 🙂

Why the default transaction level for MSSQL is different that other databases?

fun getDefaultIsolationLevel(db: Database): Int =
            when (db.vendor) {
                SQLiteDialect.dialectName -> Connection.TRANSACTION_SERIALIZABLE
                OracleDialect.dialectName -> Connection.TRANSACTION_READ_COMMITTED
                PostgreSQLDialect.dialectName -> Connection.TRANSACTION_READ_COMMITTED
                PostgreSQLNGDialect.dialectName -> Connection.TRANSACTION_READ_COMMITTED
                else -> DEFAULT_ISOLATION_LEVEL
            }

const val DEFAULT_ISOLATION_LEVEL = Connection.TRANSACTION_REPEATABLE_READ

Hello! There is a colleague who wanted to write in exposed such a thing as

select * from (select "1" as id) as idSource left join demo on idSource.id=demo.ID ...<other 10 tables where this id could exist>.

Write via full join and 10 conditions demo.id ="1" or demo 2.id ="1" he doesn't really want to. Maybe someone knows how (select "1" as id) as idSource can be written in exposed? join function accept ColumnSet as parameter. ColumnSet has Columns which refers to Table. But idSource is not table here. @tapac, Bogdan Panchenko gave me advise tag you under the question.

Hi, I am kotlin/exposed newbie and I am having trouble inserting into a table with a foreign key reference. Please can someone tell me what I am doing wrong?

object Profile : IntIdTable("profile", "id") {
  val externalId: Column<String> = varchar("external_id",
    255).uniqueIndex("external_profile_external_id_uindex")

  val profileUrl: Column<String?> = varchar("profile_url",
    255).uniqueIndex("external_profile_profile_url_uindex").nullable()

  val imageUrl: Column<String?> = varchar("image_url", 255).nullable()

  val uri: Column<String> = varchar("uri", 255).uniqueIndex("external_profile_uri_uindex")
}

object User : IntIdTable("user", "id") {
  val username: Column<String> = varchar("username",
    20).uniqueIndex("internal_user_username_uindex")

  val email: Column<String> = varchar("email",
    255).uniqueIndex("internal_user_email_uindex")

  val displayName: Column<String?> = varchar("display_name", 255).nullable()

  val externalProfileId: Column<Int> =
    integer("profile_id").references(Profile.id).uniqueIndex("internal_user_external_profile_id_uindex")
}


val profile = Profile.insert {
  it[externalId] = "externalId"
  it[profileUrl] = "profileUrl"
  it[imageUrl] = "imageUrl"
  it[uri] = "uri"
}

val user = User.insert {
  it[username] = "username"
  it[email] = "email"
  it[displayName] = "displauName"
  it[externalProfileId] = profile[id] // <--- this line fails to compile
}

I cant set the externalProfileId foreign key to the ID from the new profile created. is there a better way to do this? Thanks!

Hello, I set up batch insert in Exposed but is there an option setting batch size like in hibernate?

Hi folks. I was following the recommendation for server side timestamps by using the following:

val updatedAt = datetime("updated_at").defaultExpression(CurrentDateTime())

I was looking at the following chunk for using the

EntityHook

and it wasn’t clear how to leverage the

CurrentDateTime

here as well:

EntityHook.subscribe { action ->
            if (action.changeType == EntityChangeType.Updated) {
                action.toEntity(this)?.updatedAt = LocalDateTime.now(Clock.systemUTC())
            }
        }

I’ve tried looking through the code/docs but wasn’t able to find an example of how to do this properly. Is there something obvious that I’m missing here, or do these two not work very well together. Any help would be appreciated. Thanks!

I cannot update the expose dependencies reason being the IntIdTable primaryKey field is now final (in newer versions of the dependencies, from version 0.36.1 to be precise):

/**

 * Identity table with autoincrement integer primary key

 *

 * @param name table name, by default name will be resolved from a class name with "Table" suffix removed (if present)

 * @param columnName name for a primary key, "id" by default

 */

open class IntIdTable(name: String = "", columnName: String = "id") : IdTable<Int>(name) {

    final override val id: Column<EntityID<Int>> = integer(columnName).autoIncrement().entityId()

    final override val primaryKey by lazy { super.primaryKey ?: PrimaryKey(id) }

}

This field was earlier not final and we were able to override it for several tables by combining/linking two tables:

object AnswersInQuestionTable : IntIdTable("answers_in_question"){

    val question = reference("question_id", QuestionsTable)

    val answer = reference("answer_id", AnswersTable)

    override val primaryKey = PrimaryKey(question, answer)

}

This is a an issue for our web service. Anyone who know what the best practice(s) are for resolving that?

Hey. Please, is it possible to override isolation level by

dataSource

isolation? I am using HikariCP as datasource and I defined

hikariDataSource.transactionIsolation = "TRANSACTION_READ_COMMITED"

. But when I call

transaction {
 val level = this.transactionIsolation
}

value of the

level

variable is different than isolation specified for dataSource..

Has anyone tryed connecting to a postgres sql db on heroku? the URI Herohu gives me has the dialect as postgros but expose is looking for postgrosql. i been looking to see if i can config on or the other to play nice with one an other but i am having no luck so far

Hello everyone. I’m in a Ktor project and I’m trying to install exposed. In which file am I supposed to put this :

val config = HikariConfig().apply {
    jdbcUrl         = "jdbc:<mysql://localhost/dbname>"
    driverClassName = "com.mysql.cj.jdbc.Driver"
    username        = "username"
    password        = "secret"
    maximumPoolSize = 10
}
val dataSource = HikariDataSource(config)
Database.connect(dataSource)

from the documentation here: https://github.com/JetBrains/Exposed/wiki/DataBase-and-DataSource

Hi, can we use

r2dbc

with exposed

Why does this fail?

@Test
    fun dbTest2() {
        Database.connect("jdbc:h2:mem:test", driver = "org.h2.Driver")
        transaction {
            SchemaUtils.create(Blogs)
        }
        transaction {
            Blog.all().also { println(it.toList()) }
            Blog.new {
                title = "asd"
                rawContent = "foo"
            }
            Blog.all().also { println(it.toList()) }
        }
    }

with

Table "BLOGS" not found (this database is empty)

even though I can see that create table call executed before

2022-01-16 20:32:57.273 [Test worker] DEBUG Exposed - CREATE TABLE IF NOT EXISTS BLOGS (ID INT AUTO_INCREMENT PRIMARY KEY, TITLE VARCHAR(100) NOT NULL, RAW_CONTENT TEXT NOT NULL, CREATE_DATE_TIME DATETIME(9) NOT NULL, UPDATE_DATE_TIME DATETIME(9) NOT NULL)
2022-01-16 20:32:57.289 [Test worker] DEBUG Exposed - SELECT BLOGS.ID, BLOGS.TITLE, BLOGS.RAW_CONTENT, BLOGS.CREATE_DATE_TIME, BLOGS.UPDATE_DATE_TIME FROM BLOGS

How do I check columns for null or set them to nullable values? table:

object CommentTable : LongIdTable() {
    val post = reference("post", PostTable)
    val parent = optReference("parent", CommentTable)
    val author = reference("author", UserTable)
    val content = text("content")
    val created = datetime("created")
    val updated = datetime("updated")
}

but when doing

CommentTable.parent eq null

I get

Overload resolution ambiguity: 
public open fun <T : Comparable<TypeVariable(T)>, E : EntityID<TypeVariable(T)>?> ExpressionWithColumnType<TypeVariable(E)>.eq(t: TypeVariable(T)?): Op<Boolean> defined in org.jetbrains.exposed.sql.SqlExpressionBuilder
public open fun <T> ExpressionWithColumnType<TypeVariable(T)>.eq(t: TypeVariable(T)): Op<Boolean> defined in org.jetbrains.exposed.sql.SqlExpressionBuilder