Is there any tool to generate structures for existing database?

seems there’s a breaking change in version

0.30.1

-

Query.prepareSql(queryBuilder: QueryBuilder)

is now internal. What’s the right way to print out the Query SQL then?

Hello everyone. Do anyone knows if exposed is suitable for production?

Thank you for your answer 😃

Hi, I found a weird behavior: I've got my table defined like this:

object Profiles : IntIdTable() {
  val email = varchar("email", 50).uniqueIndex() // important!
  val name = varchar("name", 50)
}

Looking at the logs, it seems that the table is created correctly by Exposed:

DEBUG Exposed - CREATE TABLE IF NOT EXISTS Profiles (id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(50) NOT NULL, name VARCHAR(50) NOT NULL)
DEBUG Exposed - ALTER TABLE Profiles ADD CONSTRAINT Profiles_email_unique UNIQUE (email)

Because of the unique index, I would expect

insert

requests with an email already present in the database, to fail:

Profiles.insert { profile ->
  profile[this.email] = email
  profile[this.name] = name
}

In the above example, I would expect an

insert

to be fail if the email is already used by another profile. Nothing seems weird in debug either;

DEBUG Exposed - INSERT INTO Profiles (email, name) VALUES ('some random <http://email.fr|email.fr>', 'my full name')

However, when running this code multiple times, instead of failing on the

insert

, it simply replaces the previous entry that had the same email. In case it's relevant, I'm using MariaDB. For obvious reasons, I don't want users of the software to be able to replace other accounts. I'm not sure if the current behavior is 'normal' or 'bugged', so I'm asking here whether: 1. Is it normal behavior, or should I submit a bug report? 2. If this is normal behavior, what should I change in my code so the

insert

fails (with an exception or something else)?

Hello, I have already searched here, but I did not find an answer. How can I build a sql via DSL or Dao that looks like this:

select count(ID) from employer

The ID column is primary key, so uniq and has no nulls.

Employer.slice(Employer.Id).count()

produces

select count(*) from employer

Thanks!

Is R2DBC coming soon? Also, would there be a way to separate the entity definitions that are only for DDL? I'm sure alot of us use tools like flyway for that and exposed only for working with the existing structure... and I'm never sure what is the minimum I need for no DDL use @tapac ...

i get errors with the following error message when i try to query using an enum value:

Transaction attempt #0 failed: org.postgresql.util.PSQLException: ERROR: operator does not exist: "MyEnumColumn" = character varying\n  Hint: No operator matches the given name and argument types. You might need to add explicit type casts

I've practically copy and pasted this from the docs, but I get an error in Intellij. I will edit to provide, one moment (see thread)

UserTable.update({ UserTable.userId eq userId }) {
                    it[UserTable.hashedPassword] = hash(randomAlphanumerical(8))
                }

@tapac I have this code

object BuddyTable: IdTable<Int>("toh.buddy"){
    override val id = integer("id").autoIncrement("toh.buddy_seq_id").entityId()
    val name = varchar("name", 50)
    val hero = reference("hero_id", HeroTable)
}

class BuddyDao(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<BuddyDao>(BuddyTable)

    var name by BuddyTable.name
    var hero by HeroDao referencedOn BuddyTable.hero
}

override fun save(entity: Buddy): Int = transaction {
        BuddyTable.insert {
            it[name] = entity.name
            it[hero] = entity.hero.id
        }[BuddyTable.id].value
}

In postgres it looks like it is not using the sequence to generate the new id (also if i don't override the id field) Prefixing or not with the schema "toh" doesn't change. The error is

Detail: Key (id)=(3) already exists.. Statement(s): INSERT INTO toh.buddy (hero_id, "name") VALUES (?, ?)

org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "buddy_pkey"
  Detail: Key (id)=(3) already exists.

It does 3 transaction attempts with ids 1, 2 and 3

Expose 0.31.1 released with TIME column support (java-time only), inList on Pairs and Triples, few fixes, and performance improvement. Check change log for details.

hey folks, is there a way to know if my row was inserted or not with

insertIgnore

on a non IdTable? I'm currently querying for it right afterwards but was wondering if there is a cleaner way

Hi, I am on ktor 1.5.2 and I am having issues with SchemaUtils.create. I have 3 tables, and the third has a reference to the first one. When it gets to creating the third table it concludes that the first one doesnt exist and tries to create it again which causes an error "relation "users_username_unique" already exists". I debugged and stepped thru the code and found that the problem is in VendorDialect.tableExists function.

override fun tableExists(table: Table): Boolean {
        val tableScheme = table.tableName.substringBefore('.', "").takeIf { it.isNotEmpty() }
        val scheme = tableScheme?.inProperCase() ?: TransactionManager.current().connection.metadata { currentScheme }
        val allTables = getAllTableNamesCache().getValue(scheme)
        return allTables.any {
            when {
                tableScheme != null -> it == table.nameInDatabaseCase()
                scheme.isEmpty() -> it == table.nameInDatabaseCase()
                else -> it == "$scheme.${table.tableNameWithoutScheme}".inProperCase()
            }
        }
    }

What happens here by looking at the debugger is that

alltables

is

[ "public.tablename", "public.someothertablename" ]

but

table.name

misses the schema name and I am not sure why. The code is fairly straightforward and I used

SchemaUtils.create

successfully in the past.

Do I need to call Database.connect(dataSource) every time I create a transaction? I'm receiving errors and I'm kind of confused because I do call it after I create the datasource

It's possible that my database doesn't connect. I just went into my database and it doesn't seem that it created anything:

val dataSource = HikariDataSource().apply {
        maximumPoolSize = 20
        driverClassName = "com.mysql.jdbc.Driver"
        jdbcUrl = File("jdbc_url.txt").readText()
        isAutoCommit = false
    }

    Database.Companion.connect(dataSource)

    transaction {
        SchemaUtils.create(MediaPropertiesTable)
        SchemaUtils.create(MediaContentTable)
        SchemaUtils.create(UserAPIKeysTable)
        SchemaUtils.create(UserTable)
    }

The file jdbc_url.txt does exist and has a connection url generated from pterodactyl UPDATE: Tried removing pterodactyl from the equation and still doesnt work. It gets past the create code and all, nothing gets logged even after adding

addLogger(StdOutSqlLogger)

and the tables arent in the db.

Hi all! I wish to create some custom column types, mainly to wrap inline classes for UUID's. I have multiple classes that wrap a UUID, and I was wondering that if there is any guide to help me through it? I could copy paste and adjust the UUIDColumnType, but I was wondering, is it somehow possible to wrap something around it?

does expose always use threadLocals? i thought that if I pass around a database and always specify the database like

transaction(db) {..}

it will avoid threadlocals, but it seems that all exposed calls inside the transaction still get their transaction from a threadlocal. so it only works if the thread stays the same, and for example it wont work if i have callbacks (or coroutines) that potentielly run in different threads.

I'm getting some odd error regarding with DAOs when writing tests

<http://dao.Companies.org|dao.Companies.org>_id is not in record set

. All tables are created in the same transaction as the test then everything is rolled back. I've tried using both H2 and SQLite, getting same error with both.. Not sure where to start looking. Most search hits seems to be related to renaming the id column, but this is a FK to another entity

Is there a way to return a

Flow

from a query?

Am I missing a way to put a Query in the slice ? ie.

INSERT INTO Tbl (parentId, number)
VALUES (42,
        COALESCE((SELECT MAX(aTbl.number) FROM Tbl as aTbl WHERE aTbl.parentId = 42) + 1, 1)
        );

I kind of expected a way to turn a query into an Expression<Int>.

Hello ! Is there a way to avoid duplicate row when insert data in many to many table with exposed ?

I have a Publication object and i’d like to insert a batch of them. Two options: 1. Insert only via the Entity

for loop
  Publication.new { ... }

2. Insert via entity table

Publication.table.batchInsert

1? 2? I’m not sure

Is there any way I can set the childs' ReferenceOption.Cascade from the parent entity? The child table is referenced from multiple tables, and I want to avoid optionally referencing each of the tables at the child level Example:

object Person : UUIDTable() {
    val name = varchar("name", 255)
    val address = reference("address", Address)
}

object Company : UUIDTable() {
    val name = varchar("name", 255)
    val address = reference("address", Address)
}

object Address : UUIDTable() {
    val number = varchar("number", 255)
    val street = varchar("street", 255)
    val city = varchar("city", 255)
    val zipcode = varchar("zipcode", 255)
    val country = varchar("country", 255)
}

I'm kinda new with delegates.. I'd like to create one to help me store json in a varchar column.. My gut feeling tells me I'm overcomplicating things here.. Any feedback on better approaches? 🙏

// Entity
class Client(id: EntityID<Int>) : IntEntity(id) {
   companion object : IntEntityClass<Client>(Clients)
   // Some code omitted
   var tags2: List<String> by jsonStore(Clients.tags)
}

// Delegate provider
inline fun <reified S, T : Comparable<T>> Entity<T>.jsonStore(column: Column<String>) = JsonStore<T, S>(
   serializer(),
   column,
   { column.lookup() },
   { thisRef, property, value -> column.setValue(thisRef, property, value) }
)

// Delegate
class JsonStore<T : Comparable<T>, S>(
   val serializer: KSerializer<S>,
   val column: Column<String>,
   val lookup: (Column<String>) -> String,
   val write: (Entity<T>, KProperty<*>, String) -> Unit
) {
   
   operator fun getValue(thisRef: Any?, property: KProperty<*>): S = 
      json.decodeFromString(serializer, lookup(column))
   
   operator fun setValue(thisRef: Entity<T>, property: KProperty<*>, value: S) = 
      write(thisRef, property, json.encodeToString(serializer, value))
}

I want to use an alias for the order by clause in Exposed Kotlin like so:

6371 * ACOS(COS(RADIANS(:latitude)) * COS(RADIANS(`lat`)) * COS(RADIANS(:longitude) - RADIANS(`lng`)) + SIN(RADIANS(:latitude)) * SIN(RADIANS(`lat`))) AS `distance`

Anyone with an idea on how to implement this?

Hi everyone! I’m having an issue using Exposed, in my app i’m doing several inserts to a MySQL DB with Exposed, and sometimes instead of inserting the right data, it inserts zeroes on every column. This does not happen on every insert, for testing purposes I made two identical inserts, with the same hardcoded data, and even then one was saved with zeroes on every column while the other was correctly saved. I managed to turn on the Exposed debug logs and saw that the Insert query is formed correctly, even tried copying and executing it by hand on MySQL Workbench and it worked, but when I try via my app, it sometimes stores zeroes, does this happened to anyone before or know what could be de problem? Thanks!

Hello everyone, I'm glad to announce that the

exposed-gralde-plugin

is here with a very small but usefult gradle task which allows to generate tables definitions by existing scheme. https://plugins.gradle.org/plugin/com.jetbrains.exposed.gradle.plugin The main idea is allow easily migrate your large (or not so large) existing projects to Exposed. Please check a documentation on github and share your ideas on things that can be improved/added to a gradle plugin. For now I think that it can be useful to generate/validate schemas or prepare migration scripts (like ShemaUtils do). What do you think? The on of the next steps is to have intellij-idea plugin to have same things in the IDE, so share your thoughts about that too:) P.S. There can be bugs for sure:)

If I connect to a SQLite DB in Exposed and use the same

Database

instance in separate transactions in two separate threads... I would expect to see uncommitted reads and similar. But I can't seem to reproduce that with a test. Does Exposed do something to ensure isolation?

The

generateExposedTask

has been stuck for more than half-an-hour without crashing nor telling me what's wrong... how can I debug this?