I am trying to use SqlDelight on a Compose for Des...

# squarelibraries

[Daniele B]

I am trying to use SqlDelight on a Compose for Desktop app.
I am getting the driver like this:

val sqlDriver = JdbcSqliteDriver(JdbcSqliteDriver.IN_MEMORY)

however when I make a DB operation, I get this error:

Exception in thread "AWT-EventQueue-0 @coroutine#4" org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such table: Countries)

full stacktrace in the comment
(using com.squareup.sqldelight:sqlite-driver:1.5.0 )

[Daniele B]

full stacktrace:

Exception in thread "AWT-EventQueue-0 @coroutine#4" org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such table: Countries)
	at org.sqlite.core.DB.newSQLException(DB.java:1012)
	at org.sqlite.core.DB.newSQLException(DB.java:1024)
	at org.sqlite.core.DB.throwex(DB.java:989)
	at org.sqlite.core.NativeDB.prepare_utf8(Native Method)
	at org.sqlite.core.NativeDB.prepare(NativeDB.java:134)
	at org.sqlite.core.DB.prepare(DB.java:257)
	at org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:45)
	at org.sqlite.jdbc3.JDBC3PreparedStatement.<init>(JDBC3PreparedStatement.java:30)
	at org.sqlite.jdbc4.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:25)
	at org.sqlite.jdbc4.JDBC4Connection.prepareStatement(JDBC4Connection.java:35)
	at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:241)
	at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:205)
	at com.squareup.sqldelight.sqlite.driver.JdbcDriver.execute(JdbcDriver.kt:106)
	at mylocal.db.shared.CountriesQueriesImpl.upsertCountry(LocalDbImpl.kt:107)
	at eu.baroncelli.dkmpsample.shared.datalayer.sources.localdb.countries.CountriesFunctionsKt$setCountriesList$1.invoke(CountriesFunctions.kt:13)
	at eu.baroncelli.dkmpsample.shared.datalayer.sources.localdb.countries.CountriesFunctionsKt$setCountriesList$1.invoke(CountriesFunctions.kt)
	at com.squareup.sqldelight.TransacterImpl.transactionWithWrapper(Transacter.kt:218)
	at com.squareup.sqldelight.TransacterImpl.transaction(Transacter.kt:197)
	at com.squareup.sqldelight.Transacter$DefaultImpls.transaction$default(Transacter.kt:82)
	at eu.baroncelli.dkmpsample.shared.datalayer.sources.localdb.countries.CountriesFunctionsKt.setCountriesList(CountriesFunctions.kt:11)
	at eu.baroncelli.dkmpsample.shared.datalayer.functions.GetCountriesListKt$getCountriesListData$2.invokeSuspend(GetCountriesList.kt:22)
	at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:33)
	at kotlinx.coroutines.DispatchedTask.run(DispatchedTask.kt:106)
	at kotlinx.coroutines.scheduling.CoroutineScheduler.runSafely(CoroutineScheduler.kt:571)
	at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.executeTask(CoroutineScheduler.kt:750)
	at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.runWorker(CoroutineScheduler.kt:678)
	at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.run(CoroutineScheduler.kt:665)

[alec]

Are you calling the create and migrate methods on the generated schema

[alec]

It’s only android where that’s done automatically, everywhere else you have to do it yourself

[Daniele B]

Ah ok, I didn’t know.

[Daniele B]

where can I read about?

[Daniele B]

OK, I found an example in the todoapp by JetBrains:
https://github.com/JetBrains/compose-jb/tree/master/examples/todoapp/common/database/src/desktopMain/kotlin/example/todo/common/database

[John O'Reilly]

fwiw this is what PeopleInSpace is doing for that https://github.com/joreilly/PeopleInSpace/blob/main/common/src/jvmMain/kotlin/com/surrus/common/repository/actual.kt

[Daniele B]

thanks @John O'Reilly
I can see the todoapp is using:

val databasePath = File(System.getProperty("java.io.tmpdir"), "ComposeTodoDatabase.db")
    val driver = JdbcSqliteDriver(url = "jdbc:sqlite:${databasePath.absolutePath}")
    TodoDatabase.Schema.create(driver)

while PeopleInSpace is using:

val driver = JdbcSqliteDriver(JdbcSqliteDriver.IN_MEMORY)
    .also { PeopleInSpaceDatabase.Schema.create(it) }
PeopleInSpaceDatabaseWrapper(PeopleInSpaceDatabase(driver))

what the difference?
are they both saving the db on the disk?

[John O'Reilly]

I think what I'm using is more or less what's in https://cashapp.github.io/sqldelight/jvm_sqlite/

[Daniele B]

does it actually get saved on disk?

[John O'Reilly]

ah, yes....that's probably a difference as well

[John O'Reilly]

I pulled that in early in project and haven't really re-visited it since.....thought this was ok anyway for nature of this sample

[Daniele B]

I can see the todoApp is saving it to a Java tmpdir, so it’s not very “permanent” either.

[Daniele B]

@alec what does it take to have the data saved permanently in a proper way?

[Nikky]

put it somewhere you can be sure it does not get deleted (appdata , ~/.local/share or such),
handle migrations
and exceptions that are caused by incomplete writes or such leaving the db file in broken state
adding a jvm shutfown hook to make sure the database is closed properly might be helpful