Kotlin Exposed does not commit transaction

Question

Following the example provided on Exposed I am not able to read the created tables/data outside the transaction creating it. I am using h2-in-memory database.

The exception is:

Exception in thread "main" org.h2.jdbc.JdbcSQLException: Table "CITIES" not found; SQL statement:

I have added a call to commit but this does not help. If I read the data within the transaction creating the data, as in the example on the link to github, it works fine. Here the a simplified version of it:

fun main(args: Array<String>) {
    Database.connect("jdbc:h2:mem:test", driver = "org.h2.Driver")

    transaction {
        create(Cities)

        City.new {
            name = "St. Petersburg"
        }

        println("Cities: ${City.all().joinToString { it.name }}")
        //I have added this commit here
        commit()
    }
    //I want to read the data outside the transaction, but it does not work
    transaction {
        println("Cities: ${City.all().joinToString { it.name }}")

    }
}

How can I persist the data?

Adding logger.addLogger(StdOutSqlLogger) gives the following output:

SQL: CREATE TABLE IF NOT EXISTS CITIES (ID INT AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(50) NOT NULL)
SQL: INSERT INTO CITIES (NAME) VALUES ('St. Petersburg')
SQL: SELECT CITIES.ID, CITIES.NAME FROM CITIES

Answer 1

It looks like you moved away from in-memory H2 to solve your problem. Be advised that the root of your initial problem was probably because H2 needs to be told to keep your tables for the life of the JVM:

jdbc:h2:mem:test;DB_CLOSE_DELAY=-1

further explained here: H2 in-memory database. Table not found

Answer 2

It does commit the transaction. The problem is that when the in memory database is closed then it's deleted and the in memory database is closed under the following circumstances:

By default, H2 closes the database when the last connection is closed

_Source

Here is a diagram so it's easier to understand what happens step by step (when the database is closed it's deleted altogether)

The easiest solution is to simply use a actual file database instead of a in memory one.

Answer 3

Changing the DB from in memory to Database.connect("jdbc:h2:~/test", driver = "org.h2.Driver") fixed the problem.