Is it possible to load a pre-populated database from local resource using sqldelight

Question

I have a relatively large db that may take 1 to 2 minutes to initialise, is it possible to load a pre-populated db when using sqldelight (kotlin multiplatform) instead of initialising the db on app launch?

Arpent

Answer 1

Sorry, but I can't add any comments yet, which would be more appropriate...

---

Although not directly answering your question, 1 to 2 minutes is really, really long for sqlite. What are you doing? I would first make sure you're using transactions properly. 1-2 minutes of inserting data would (probably) result in a huge db file.

Alternatively, my problem due to which I had to use a pre-populated database was associated with the large size of .sq files (more than 30 MB text of INSERTs per table), and SqlDeLight silently interrupted the generation, without displaying error messages.

---

You'll need to put the db file in assets on android and in a bundle on iOS and copy them to their respective folders before initializing sqldelight.

Having to load a db from resources on both android and ios feels a lot of work + it means the shared project wont be the only place where the data is initialised.

Kotlin MultiPlatform library Moko-resources solves the issue of a single source for a database in a shared module. It works for KMM the same way for Android and iOS.

Unfortunately, using this feature are almost not presented in the samples of library. I added a second method (getDriver) to the expected class DatabaseDriverFactory to open the prepared database, and implemented it on the platform. For example, for androidMain:

actual class DatabaseDriverFactory(private val context: Context) {
    actual fun createDriver(schema: SqlDriver.Schema, fileName: String): SqlDriver {
        return AndroidSqliteDriver(schema, context, fileName)
    }

    actual fun getDriver(schema: SqlDriver.Schema, fileName: String): SqlDriver {
        val database: File = context.getDatabasePath(fileName)

        if (!database.exists()) {
            val inputStream = context.resources.openRawResource(MR.files.dbfile.rawResId)
            val outputStream = FileOutputStream(database.absolutePath)

            inputStream.use { input: InputStream ->
                outputStream.use { output: FileOutputStream ->
                    input.copyTo(output)
                }
            }
        }

        return AndroidSqliteDriver(schema, context, fileName)
    }
}

MR.files.fullDb is the FileResource from the class generated by the library, it is associated with the name of the file located in the resources/MR/files directory of the commonMain module. It property rawResId represents the platform-side resource ID.

Iberian

Answer 2

I have converted the code by @Denis Luttcev to the ios target side and it works perfectly in my kotlin multiplatform project. Had some troubles figuring out how to make it work, so in case anyone else will need it here is what I got:

actual class DriverFactory {
    actual fun createDriver(schema: SqlDriver.Schema, fileName: String): SqlDriver {
        val fileManager = NSFileManager.defaultManager
        val documentsPath = NSSearchPathForDirectoriesInDomains(
            directory = NSApplicationSupportDirectory,
            domainMask = NSUserDomainMask,
            expandTilde = true
        ).first() as NSString

        val dbDirectoryPath = documentsPath.stringByAppendingPathComponent("databases")
        val targetDbPath = documentsPath.stringByAppendingPathComponent("databases/$fileName")
        val sourceDbPath = MR.files.dbfile.path

        val directoryExists = fileManager.fileExistsAtPath(dbDirectoryPath)
        val databaseExists = fileManager.fileExistsAtPath(targetDbPath)

        if (databaseExists.not()) {
            memScoped {
                // you can use it to log errors
                val error: ObjCObjectVar<NSError?> = alloc()

                if (directoryExists.not()) {
                    val createSuccess = fileManager.createDirectoryAtPath(
                        path = dbDirectoryPath,
                        withIntermediateDirectories = true,
                        attributes = null,
                        error = error.ptr
                    )
                }

                val copySuccess = fileManager.copyItemAtPath(
                    srcPath = sourceDbPath,
                    toPath = targetDbPath,
                    error = error.ptr
                )
            }
        }

        return NativeSqliteDriver(schema, fileName)
    }
}

Helical

Answer 3

Yes, but it can be tricky. Not just for "Multiplatform". You need to copy the db to the db folder before trying to init sqldelight. That probably means i/o on the main thread when the app starts.

There is no standard way to do this now. You'll need to put the db file in assets on android and in a bundle on iOS and copy them to their respective folders before initializing sqldelight. Obviously you'll want to check if the db exists first, or have some way of knowing this is your first app run.

If you're planning on shipping updates that will have newer databases, you'll need to manage versions outside of just a check for the existance of the db.

Although not directly answering your question, 1 to 2 minutes is really, really long for sqlite. What are you doing? I would first make sure you're using transactions properly. 1-2 minutes of inserting data would (probably) result in a huge db file.

Demosthenes

I am trying kotlin multiplatform, and I have a search app with a book on a local db and all unique words + words mappings to sentences and pages which adds up to a lot for sqlight. I am kind of doing the db init on the io coroutine but was hoping to avoid the db init at all (easily :) ). The next best thing would be to be able to run contents of .sq files on the db directly at will, i.e separate from the table init .sq files, is this doable? It would allow me to initialise the db with the essential info and run the rest of the init job on a background thread. – Arpent

I don't know if you can run arbitrary .sq files, but if you get a handle to the sqlite driver directly you could just have sql statements in a resource file and run them. If I understand your use case, you have one table with about 2000-5000 inserts (the word list) and one with 50k-100k inserts (the map), using stats from a quick web search on the size of novels. I would start trying to simple improve insert performance (medium.com/@JasonWyatt/…), but assuming you have inserts in a transaction, you probably can't speed it up much – Demosthenes

I would try to reduce the complexity of the mapping table to see if you can speed it up, as that'll be the bulk of your time. Otherwise, just have a special init flow that copies the db file from resources to the db folder the first time the app is run. Show a progress bar. That kind of thing. Having a partially initialized database seems like it'll be complex. – Demosthenes

Yes, you understood the case correctly. Having to load a db from resources on both android and ios feels a lot of work + it means the shared project wont be the only place where the data is initialised. What I ended up doing is, leave all the short number of the inserts inside the sqldelight .sq table files and move the large inserts (the mappings in this case) to an init process. When the app is run for the first time, the book content will be ready to brows but the search feature will not be available until the db initialisation is completed, a progress bar is shown to indicate this. – Arpent

In my init process, I converted the sql inserts into kotlin objects stored inside lists, broken into multiple files. On the firs run these mapping objects are inserted using the sqldelight insert that i have defined inside the mapping table .sq file. As you mentioned, i could just run the sql statements without having to convert them if i could get a handle to the sqlite driver, but not sure if this is going to have noticeable improvement on time taken to insert all the mappings. – Arpent

Answer 4

The only thing you need is to specify the path to the DB file using the driver.

Let's assume your DB lies in /mnt/my_best_app_dbs/super.db. Now, pass the path in the name property of the Driver. Something like this:

val sqlDriver: SqlDriver = AndroidSqliteDriver(Schema, context, "/mnt/my_best_app_dbs/best.db")

Keep in mind that you might need to have permissions that allow you to read a given storage type.

Chickadee

I don't think this is true. Your need to explicitly copy the database to the default folder. – Evaporate

@Evaporate give it a try, it works fine in my projects 😉 – Chickadee

I did and can confirm it didn't work for me. In the Kotlin slack, there was a discussion about how this functionality which is implemented in Room, is not implemented with SQL delight. @Denis Luttcev solution is the best AFAIK. – Evaporate

Does your app have storage permissions granted? The line above is copied from the project where it works but it's a system app and all permissions are granted out of the box. – Chickadee