SQLDelight "No such table" Error when Adding New Tables to Non-Empty Database in Android Kotlin App

Question

I'm working on an Android app using Kotlin and SQLDelight for database management. I've encountered an issue where adding new tables via .sq files leads to a "No such table" error, but this only happens when the database already contains data. When the database is empty, adding new tables works fine.

Environment:

• Kotlin Version: 1.8.0
• SQLDelight Version: 2.0.0
• Gradle:

    databases {
        register("TestDatabase") {
            packageName.set("com.test.app.db")
            verifyMigrations.set(true)
        }
    }
}

• SQLDelight configuration

fun provideSqlDriver(context: Context): AndroidSqliteDriver =
    AndroidSqliteDriver(
        schema = DanaosDatabase.Schema,
        context = context,
        name = "test-db",
        callback = object : AndroidSqliteDriver.Callback(TestDatabase.Schema) {
            override fun onConfigure(db: SupportSQLiteDatabase) {
                super.onConfigure(db)
                setPragma(db, "JOURNAL_MODE = WAL")
                setPragma(db, "SYNCHRONOUS = 1")
                setPragma(db, "JOURNAL_SIZE_LIMIT = 6144000")/
            }

            private fun setPragma(db: SupportSQLiteDatabase, pragma: String) {
                val cursor = db.query("PRAGMA $pragma")
                cursor.moveToFirst()
                cursor.close()
            }
        }
    )
fun provideDatabase(
    driver: SqlDriver,
): TestDatabase =
    TestDatabase(
        driver = driver
    )

Problem: After adding new .sq files to define new tables and rebuilding the project, I receive a "No such table" error when trying to interact with the newly added tables. This error does not occur if the database is empty at the time of adding these new tables.

What I've Tried:

1. Cleaning and rebuilding the project in Android Studio.
2. Ensuring that the .sq files are correctly placed and formatted.
3. Checking the generated Kotlin code from SQLDelight for any discrepancies.
4. Running the project with an empty database, which works fine.
5. Adding SQLDelight migration scripts (2.sqm) for the new tables, but the issue persists.

Code Samples:

• Here's how I define a new simple test table in the .sq file:

  CREATE TABLE IF NOT EXISTS NewTable (
      id INTEGER PRIMARY KEY,
      name TEXT NOT NULL
  );
  
  

Still not working

Answer 1

I assume you'll need to add the create table statement to your migration as well. The *.sq files are used for a fresh db, and everything else is migrations.

Side note, I would not use IF NOT EXISTS. You shouldn't ever get an error about trying to create an existing table, and if you do, something isn't right and it would be better to try and fix it.