Exposed: In-memory Sqlite DB disappears between transactions

[kylegoetz]

I have an in-memory sqlite db. The following code yields an error. Here's the code:

import org.jetbrains.exposed.dao.EntityID
import org.jetbrains.exposed.dao.UUIDEntity
import org.jetbrains.exposed.dao.UUIDEntityClass
import org.jetbrains.exposed.dao.UUIDTable
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.TransactionManager
import org.jetbrains.exposed.sql.transactions.transaction
import photo.backup.kt.data.HashTable
import java.sql.Connection
import java.util.*

object MyTable: UUIDTable() {
    val name = varchar("name", 50)
}

class Name(id: EntityID<UUID>): UUIDEntity(id) {
    companion object: UUIDEntityClass<Name>(MyTable)

    var name by MyTable.name
}

fun main() {
    val db = Database.connect("jdbc:sqlite:file:test?mode=memory&cache=shared", "org.sqlite.JDBC")
    TransactionManager.manager.defaultIsolationLevel = Connection.TRANSACTION_SERIALIZABLE
    transaction(db) {
        addLogger(StdOutSqlLogger)
        SchemaUtils.create(MyTable)
    }
    println(UUID.randomUUID())
    val id = transaction(db) {
        MyTable.insert {
            it[name] = "Foo"
        } get MyTable.id
    }
    println(id)
}

main()

Here's the error:

Exception in thread "main" org.jetbrains.exposed.exceptions.ExposedSQLException: org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such table: My)
SQL: [INSERT INTO My (id, "name") VALUES (?, ?)]
    at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core(Statement.kt:50)
    at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:122)
    at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:108)
    at org.jetbrains.exposed.sql.statements.Statement.execute(Statement.kt:29)
    at org.jetbrains.exposed.sql.QueriesKt.insert(Queries.kt:45)
    at org.jetbrains.kotlin.idea.scratch.generated.ScratchFileRunnerGenerated$ScratchFileRunnerGenerated$main$id$1.invoke(tmp.kt:34)
    at org.jetbrains.kotlin.idea.scratch.generated.ScratchFileRunnerGenerated$ScratchFileRunnerGenerated$main$id$1.invoke(tmp.kt:16)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$1.invoke(ThreadLocalTransactionManager.kt:156)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$2.invoke(ThreadLocalTransactionManager.kt:197)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:205)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:196)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$transaction$1.invoke(ThreadLocalTransactionManager.kt:134)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:205)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:106)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:104)
    at org.jetbrains.kotlin.idea.scratch.generated.ScratchFileRunnerGenerated$ScratchFileRunnerGenerated.main(tmp.kt:33)
    at org.jetbrains.kotlin.idea.scratch.generated.ScratchFileRunnerGenerated$ScratchFileRunnerGenerated.generated_get_instance_res0(tmp.kt:40)
    at org.jetbrains.kotlin.idea.scratch.generated.ScratchFileRunnerGenerated.main(tmp.kt:52)
Caused by: org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such table: My)
    at org.sqlite.core.DB.newSQLException(DB.java:909)
    at org.sqlite.core.DB.newSQLException(DB.java:921)
    at org.sqlite.core.DB.throwex(DB.java:886)
    at org.sqlite.core.NativeDB.prepare_utf8(Native Method)
    at org.sqlite.core.NativeDB.prepare(NativeDB.java:127)
    at org.sqlite.core.DB.prepare(DB.java:227)
    at org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:41)

chaging Database.connect("jdbc:sqlite:file:test?mode=memory&cache=shared", "org.sqlite.JDBC") to Database.connect("jdbc:sqlite:/path/to/data.db", "org.sqlite.JDBC")

then the DB is created and the insertion happens correctly

[kylegoetz]

From the Sqlite docs re in-memory DBs:

The database ceases to exist as soon as the database connection is closed.

I haven't read through the code yet, but if Exposed disconnects after a transaction completes, that would explain it. I don't see an argument in the connect function header that lets me force a connection to remain open.

[kylegoetz]

This is apparently also the case with H2 in-memory DBs. I don't have H2 set up on my computer, but Exposed supports H2 in-memory DBs, so this is going to be an issue for that use case as well. https://dba.stackexchange.com/questions/224338/keep-h2-in-memory-database-between-connections

[Tapac]

@kylegoetz , h2 could keep a database available in-memory until the application does not finish the work.
The only thing I can advise is to create one thread/coroutine with while(true) delay(1000) inside the opened transaction to keep database alive.

[kylegoetz]

@Tapac I don't think that would work. If I open a coroutine, the transaction will continue and terminate even if the coroutine is still running. I gave it a try and I'm still getting the same error about no such table. At least this is true when I'm using a Sqlite in-memory DB. If we can find a way to force the Sqlite in-memory connection to remain open after the initial transaction, I'll submit a PR for the documentation with how to do it. Otherwise, I'll submit a PR mentioning that in-memory DBs (for Sqlite ) won't work with Exposed unless all DB work is wrapped in a single transaction.

[Tapac]

@kylegoetz , I'm not aware of how to work with shared in-memory SQLite, so I can't advise you anything until you share an example of how it works with another frameworks.

[kylegoetz]

@Tapac Other frameworks do not force you to close a connection after a transaction. That's how they accomplish this. For example, in Python (with code showing a manual connection close()): https://pythontic.com/database/sqlite/create%20table

It's the same thing with Sequelize, an ORM for Node.js. Here is the code for instantiating a connection. The close is, again, something you manually run instead of being forced. Here is the code for transactions.

Again, there is no connection.close() in the code that runs a transaction. It only commits. And, even then, you can create a transaction that does not auto-commit but rather leaves it to the user to manually commit.

I've worked across a few languages and actually never have seen a SQL library that both

1. requires you to enclose your code in a transaction; and
2. forces the connection closed after the transaction completes

Great library, so if you're one of the people who's put work into it, thank you! I'm just suggesting the docs should be updated to clarify that MySQL in-memory DBs do not, in fact, work as expected (or, alternatively, put a note in that the connection is closed after after transaction, which means you must do your entire Sqlite in-memory DB interaction in a single transaction or you will wipe everything out. Sqlite does not require a connection closed after a transaction, so this behavior is not the expected for someone with experience working with Sqlite.

At least not without some hacking around, which I'm still trying to figure out. I haven't figured out a way yet. The suggested method of launching a coroutine inside the first transaction to keep it open doesn't seem to work. Here is the code I used

var database: Database = Database.connect("jdbc:sqlite::memory:", "org.sqlite.JDBC")
TransactionManager.manager.defaultIsolationLevel = Connection.TRANSACTION_SERIALIZABLE

transaction(database) {
    SchemaUtils.create(HashTable)
    GlobalScope.launch {
        while(true) delay(1000)
    }
}

transaction(database) {
    HashTable.insert {
        it[hash] = "foo"
        it[sessionId] = UUID.randomUUID()
    }
}

object HashTable : UUIDTable(name="hashes") {
    val hash = varchar("hash", 50)
    val sessionId = uuid("session_id")
    val hashIndex = index(true, hash)
}

Yielding the error

Exception in thread "main" org.jetbrains.exposed.exceptions.ExposedSQLException: org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such table: hashes)
SQL: [INSERT INTO hashes (hash, id, session_id) VALUES (?, ?, ?)]

I'm going to look at connection pooling, but it seems to require a third-party library for Sqlite connection pools. Will update after I investigate that.

[kylegoetz]

Connection pooling with Hikari works. It might be useful to put a note about this in an example for Sqlite in-memory DBs since the docs don't make it clear that a Sqlite in-memory DB will be destroyed between transactions. Here is sample code for using Hikari connection pooling with a Sqlite in-memory DB:

val cfg: HikariConfig = HikariConfig().apply {
    jdbcUrl = "dbc:sqlite::memory:"
    maximumPoolSize = 6
}
val dataSource = HikariDataSource(cfg)
val database = Database.connect(dataSource)

Now the in-memory DB will not be destroyed between transactions.

[gaultier]

Connection pooling with Hikari works. It might be useful to put a note about this in an example for Sqlite in-memory DBs since the docs don't make it clear that a Sqlite in-memory DB will be destroyed between transactions. Here is sample code for using Hikari connection pooling with a Sqlite in-memory DB:

val cfg: HikariConfig = HikariConfig().apply {
    jdbcUrl = "dbc:sqlite::memory:"
    maximumPoolSize = 6
}
val dataSource = HikariDataSource(cfg)
val database = Database.connect(dataSource)

Now the in-memory DB will not be destroyed between transactions.

Worked for me, big thanks, but there is a typo: it should be jdbcUrl = "jdbc:sqlite::memory:"

[marcoconti83]

The exposed documentation here now gives an example on how to connect to an in-memory DB:

SQLite

// In memory
Database.connect("jdbc:sqlite:file:test?mode=memory&cache=shared", "org.sqlite.JDBC") 

H2

// In memory / keep alive between connections/transactions
Database.connect("jdbc:h2:mem:regular;DB_CLOSE_DELAY=-1;", "org.h2.Driver") 

[marcoconti83]

@kylegoetz perhaps you can close this issue now, if the code recommended in the documentation works for you?

[kylegoetz]

@marcoconti83 My issue isn't about not being able to connect to one. It's about Exposed automatically closing your DB connection after you make a query. Which is a serious problem with in-memory Sqlite because your first query will build the schemas and when you try to insert any data later on, the schemas don't exist since it's a brand new DB. (Me and Tapac had a disagreement about whether this is the desired behavior or not.)

But it looks like the docs now show how to keep a reference to the in-memory DB that prevents Exposed from closing the connection. Even though the example is for H2, I assume it holds true for Sqlite as well. I am not using Kotlin any more (and when I do, I'm not using Exposed for DB), so I'm not going to test to see if the issue persists for Sqlite. No argument about closing the issue from me.

[marcoconti83]

@marcoconti83 My issue isn't about not being able to connect to one. It's about Exposed automatically closing your DB connection after you make a query. Which is a serious problem with in-memory Sqlite because your first query will build the schemas and when you try to insert any data later on, the schemas don't exist since it's a brand new DB. (Me and Tapac had a disagreement about whether this is the desired behavior or not.)

Thanks for clarifying,
That is actually what I meant too :-) the docs specifies how to connect to an in-memory DB without the in-memory DB being discarded at the end of the transaction. I came to this issue by searching a solution for the same problem you were having (I tried both H2 and SQLite) and the problem is solved for me using the connection parameters from the documentation.