How to properly create and connect to database with Kotlin Exposed?

Question

I did check the similar question here, but it didn't work as expected for me, I still need to reconnect to the created database and I'm not sure how or even if I can avoid that.

Here is my code:

hikari.properties:

jdbcUrl=jdbc:mariadb://localhost:3306/
driverClassName=org.mariadb.jdbc.Driver
username=root
dataSource.databaseName=DBNAME //this doesn't seem to do much, I'm getting the same behavior with or without it

fun initDB() {
    val config = HikariConfig("/hikari.properties")
    val ds = HikariDataSource(config)
    transaction(connect(ds)) {
        SchemaUtils.createDatabase("DBNAME")
    }
        
    config.jdbcUrl = "jdbc:mariadb://localhost:3306/DBNAME"
    //ds.jdbcUrl = "jdbc:mariadb://localhost:3306/DBNAME" //THIS WILL NOT WORK
    val ds2 = HikariDataSource(config)
    transaction(connect(ds2)) {
        SchemaUtils.create( Tables... )
    }
}

The reason I make a new datasource, is because otherwise I'll get this error: java.lang.IllegalStateException: The configuration of the pool is sealed once started. Use HikariConfigMXBean for runtime changes. HikariConfigMXBean doesn't seem allow jdbcUrl changes.

There must be a more elegant way to do this, right?

score 0 · Answer 1 · answered May 04 '21 at 10:25

It is not a good practice. Creating and filling db it is two separate processes. DB creates once by the DBA, then you just connect and use it. Your approach has huge security violation. User from the datasource must have create db privilege.

But if you want proceed with your current approach, first you should create a db without using datasource, then create datasource and connect to the db. Something like this

import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource
import org.jetbrains.exposed.sql.Database
import org.jetbrains.exposed.sql.SchemaUtils
import org.jetbrains.exposed.sql.Table
import org.jetbrains.exposed.sql.transactions.transaction

fun main(args: Array<String>) {
    
    //LOAD proerties
    val config = HikariConfig("/hikari.properties")
    //Hikari properties content
    //    jdbcUrl=jdbc:mysql://localhost:3306/hikari
    //    driverClassName=com.mysql.cj.jdbc.Driver
    //    username=root
    //    password=<pwd here>
    //Here replace dbname from jdbc url to empty
    transaction(Database.connect(url = config.jdbcUrl.replace("hikari", ""),
        driver = config.driverClassName,
        user = config.username,
        password = config.password)) {
        SchemaUtils.createDatabase("hikari")
    }

    val ds = HikariDataSource(config)
    transaction(Database.connect(ds)) {
        SchemaUtils.create(Users)
    }
}

object Users : Table() {
    val id = varchar("id", 10) // Column<String>
    val name = varchar("name", length = 50) // Column<String>
    override val primaryKey = PrimaryKey(id, name = "PK_User_ID")
}

Thanks it works! It's for a university project, that's why I wanted everything to be in one place, so it's easier to demonstrate and explain if needed, but I didn't like how my code looked. So in my case it should be fine even if it's not a good practice, but maybe I should reconsider. — pollux552, May 04 '21 at 11:04

How to properly create and connect to database with Kotlin Exposed?

1 Answers1