SQLDelight: [SQLITE_ERROR] SQL error or missing database (table already exists)

Question

I am trying to build a small POC with JetpackCompose Desktop and SQLDelight. I want that the data is persisted even after the application is restarted (not only in memory as all the tutorial examples I encountered show), so I tried this:

// ArticlesLocalDataSource.kt

class ArticlesLocalDataSource {
    private val database: TestDb

    init {
        val driver: SqlDriver = JdbcSqliteDriver(url = "jdbc:sqlite:database.db")
        TestDb.Schema.create(driver)

        database = TestDb(driver)
    }

    // ...
}

When I run the application for the first time this works, i.e: a database.db file in the project root is created and the data is stored successfully.

However, when I try to run the application a second time, then it crashes immediately with:

Exception in thread "main" org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (table ArticleEntity already exists)
    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:109)
    at com.squareup.sqldelight.db.SqlDriver$DefaultImpls.execute$default(SqlDriver.kt:52)
    at com.vgrec.TestPlus.TestDbImpl$Schema.create(TestDbImpl.kt:33)
    at com.vgrec.data.local.ArticlesLocalDataSource.<init>(ArticlesLocalDataSource.kt:20)

I understand that it's crashing because there's an attempt to create the database again, but the database already exists. What is not clear to me, is how do I connect to the DB if a DB already exists?

For completeness, here's the build file:

// build.gradle


plugins {
    kotlin("jvm") version "1.6.10"
    id("org.jetbrains.compose") version "1.1.0"

    // ...
    id("com.squareup.sqldelight") version "1.5.3"
    
}

sqldelight {
    database("TestDb") {
        packageName = "com.test"
    }
}

dependencies {
    implementation(compose.desktop.currentOs)
    // ..
    implementation("com.squareup.sqldelight:sqlite-driver:1.5.4")
    implementation("com.squareup.sqldelight:coroutines-extensions-jvm:1.5.4")
}

Answer 1

OK, so in the end I decided to just check if the database file exists and invoke the creation only if it does not exist.

Something like this:

init {
  val driver: SqlDriver = JdbcSqliteDriver("jdbc:sqlite:database.db")

  if (!File("database.db").exists()) {
      TestDb.Schema.create(driver)
  }

  // ...
 
}

From first glance this seems to work as expected, but I am not sure this is the recommended approach as I am very new to SQLDelight, so other suggestions are welcome.

Answer 2

May be it's too late, but may be helpful for someone other in future, you can add check while creating table in your table.sq file. For example

CREATE TABLE IF NOT EXISTS productEntity()