Hi everyone, I'm running into a persistent `no suc...

# squarelibraries

[Yassine Abou]

Hi everyone, I'm running into a persistent no such table: user error with SQLDelight on a Kotlin Multiplatform project.
I've tried the common solutions like manually calling Schema.create(driver), deleting the old database file, and ensuring I'm using a singleton instance for the database, but the problem persists. It seems the schema creation is either not happening or is happening after my first query is executed.
Full stacktrace and code in thread 👇🏽

[Yassine Abou]

Full Stacktrace:

text
Exception in thread "AWT-EventQueue-0" org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such table: user)
	at org.sqlite.core.DB.newSQLException(DB.java:1179)
	at org.sqlite.core.DB.newSQLException(DB.java:1190)
	at org.sqlite.core.DB.throwex(DB.java:1150)
	at org.sqlite.core.NativeDB.prepare_utf8(Native Method)
	at org.sqlite.core.NativeDB.prepare(NativeDB.java:135)
	at org.sqlite.core.DB.prepare(DB.java:264)
	at org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:46)
	at org.sqlite.jdbc3.JDBC3PreparedStatement.<init>(JDBC3PreparedStatement.java:32)
	at org.sqlite.jdbc4.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:25)
	at org.sqlite.jdbc4.JDBC4Connection.prepareStatement(JDBC4Connection.java:34)
	at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:225)
	at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:205)
	at app.cash.sqldelight.driver.jdbc.JdbcDriver.executeQuery(JdbcDriver.kt:159)
	at app.cash.sqldelight.SimpleQuery.execute(Query.kt:98)
	at app.cash.sqldelight.async.coroutines.QueryExtensionsKt.awaitAsOneOrNull(QueryExtensions.kt:33)
	at app.cash.sqldelight.coroutines.FlowQuery$mapToOneOrNull$1$1.invokeSuspend(FlowExtensions.kt:76)

LlmsDatabase.sq Schema:

SQL
CREATE TABLE chats (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    description TEXT,
    text_model_name TEXT NOT NULL,
    is_bookmarked INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE chat_messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    chat_id TEXT NOT NULL,
    message TEXT NOT NULL,
    is_user INTEGER NOT NULL,
    timestamp TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(chat_id) REFERENCES chats(id) ON DELETE CASCADE
);

CREATE TABLE generated_images (
    id TEXT PRIMARY KEY,
    prompt TEXT NOT NULL,
    image_data BLOB NOT NULL,
    image_model_name TEXT NOT NULL,
    generated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user (
    id INTEGER PRIMARY KEY DEFAULT 1 CHECK (id = 1),
    google_sub_id TEXT NOT NULL,
    email TEXT NOT NULL,
    username TEXT NOT NULL,
    profile_pic_url TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_chat_messages_chat_id ON chat_messages(chat_id);

-- QUERIES
getUser:
SELECT * FROM user WHERE id = 1;

insertOrUpdateUser:
INSERT OR REPLACE INTO user(google_sub_id, email, username, profile_pic_url, created_at)
VALUES (?, ?, ?, ?, ?);

-- ... other queries ...

DatabaseFactory Implementations:
commonMain:

Kotlin
expect class DatabaseFactory {
    fun createDriver(): SqlDriver
}

fun SqlDriver.enableForeignKeys() {
    this.execute(null, "PRAGMA foreign_keys = ON;", 0)
}

jvmMain:

Kotlin
actual class DatabaseFactory {
    actual fun createDriver(): SqlDriver =
        JdbcSqliteDriver(
            url = "jdbc:sqlite:${appCacheDirectory("org.yassineabou.llms", true)}/LlmsDatabase.db",
            schema = LlmsDatabase.Schema.synchronous()
        ).apply { enableForeignKeys() }
}

iosMain:

Kotlin
actual class DatabaseFactory {
    actual fun createDriver(): SqlDriver =
        NativeSqliteDriver(
            schema = LlmsDatabase.Schema.synchronous(),
            name = "LlmsDatabase.db"
        ).apply { enableForeignKeys() }
}

jsMain:

Kotlin
private val workerScriptUrl: String =
    js("""new URL("@cashapp/sqldelight-sqljs-worker/sqljs.worker.js", import.meta.url)""")

actual class DatabaseFactory {
    actual fun createDriver(): SqlDriver {
        val driver = WebWorkerDriver(Worker(workerScriptUrl)).apply {
            enableForeignKeys()
        }
        LlmsDatabase.Schema.create(driver)
        return driver
    }
}

[jw]

If you are running on the JVM you need to manage creation and migration of the schema yourself

[jw]

Something like this: https://github.com/JakeWharton/dodo/blob/0ba2fe7bb2d6c4964b94af6eadd9a6eb7477f667/src/main/kotlin/com/jakewharton/dodo/db.kt#L15