How to read the db file located in assets using sqldelight - Android

Question

I've got massive DB that I only need to create once the app is being installed and that's the reason why I've placed DB file in the assets folder, but I'm not sure how to read from it using SQLDELIGHT, any thoughts?

Answer 1

Yes, it's possible.

Before you create the driver implementation, above this code:

val driver: SqlDriver = AndroidSqliteDriver (Database.Schema, context, "test.db")

your pre-populated database must be placed under the specified name (here "test.db") into the directory where Android stores the database:

// 1. get the database file from the application context obtained in the DatabaseDriverFactory constructor
val database = context.getDatabasePath("test.db")

// 2. check the database file (in first launch the app it does not exist yet)
if (!database.exists()) {
    
    // 3. copy your pre-populated database from resources into the Android database directory
    val inputStream = context.assets.open("dbFileName")
    val outputStream = FileOutputStream(database.absolutePath)
    
    inputStream.use { input: InputStream ->
        outputStream.use { output: FileOutputStream ->
            input.copyTo(output)
        }
    }
}

// 4. create the driver
val driver: SqlDriver = AndroidSqliteDriver(Database.Schema, context, "test.db")

Please note that:

1. Resources placed in assets may be forcibly compressed by Android (I didn't check it, since my project was on Kotlin Multiplatform Mobile and I read the database file from the shared moko-resources). Other people recommend expanding the database file name in assets as incompressible (eg .pgn).
2. Although SQLDelight shouldn't try to create the database every time by the instructions in the .sq file, it tries... I stepped on this rake. Supplement your SQL statements CREATE (table, index etc.) in the .sq file with an IF NOT EXISTS instructions.