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")
}

score 0 · Answer 1 · answered Nov 12 '22 at 14:05

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.

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

1 Answers1