Migration from Room to SQLDelight

Kotlin Multiplatform is one of the most interesting trends in mobile development this year. It's dedicated to sharing the code between many different platforms, including mobile ones — Android and iOS.

This technology concentrates on sharing the business logic in a form of a library, which means that you have quite limited access to platform-specific functionality when writing shared code. The database is a part of this limitation.

There are 2 options for database management with Kotlin Multiplatform out of the box: SQLDelight and SQLighter. Starting with them from the scratch is rather straightforward. But what to do with an existing database?

It's all the same

Having that many ready-made solutions for database management make us forget that it's all the same in the end. At least, when it comes to relational databases, like Room or SQLDelight.

In both cases, you have a simple *.db file, which can be treated with SQL requests, have database schema and version, can be migrated, and so on. It means that you can easily swap the library, but still use the same database file, keeping all the user's data and pretty much the same UX.

Real-life story

While developing Codeforces WatchR for both, Android and iOS, we've noted that business-logic is becoming highly repetitive and apps are starting to behave and look differently. So we've decided to try Kotlin Multiplatform.

In Android app, Room persistence library has been used. There were 3 tables with some unique users' data, so it wasn't possible to just drop it as a cache. We needed to implement the migration from Room to SQLDelight instead.

PR with results of this migration can be found here.

SQL statements

As it's said at the official SQLDelight GitHub repo, "SQLDelight generates typesafe APIs from your SQL statements". What those SQL statements are?

An example is better than a thousand words. Here is User.sq, which gives a good idea of how one of such files can look like.

The first SQL statement is CREATE TABLE, which declares a table for the user's data. It's pretty straightforward if you've taken your course on databases in the university, haven't you? 😉

Then there are other SQL statements with operation names, which will be used for naming methods in Kotlin code, generated by SQLDelight Gradle plugin for you. The same plugin will validate all your .sq files and will warn you if something goes wrong.

You may have a few .sq files with different tables declared. Obviously, SQLDelight doesn't execute CREATE TABLE on each app launch. It's only used to validate the current database schema or to create a new database if needed.

Schema

It means that we needed to duplicate our existing Room database schema. If we succeed, all user's data will remain in place and accessible.

But how to get a database schema from Room? There are at least 2 methods:

• Grab Room database from data/data/app.package.name/databases and open it with any database viewer (for example, this one).
• Export Room database schema to JSON using Gradle annotation processor as described here.

Now when you have a schema, you need to duplicate it with SQLDelight by writing proper SQL CREATE TABLE statements. To check them, just run the app with your old (current) database. Not that fast!

Update: To be 100% sure you need to write a test query for each table, which selects all columns by names (not a generic *) and executes it from Kotlin code. Looks like queries are checked the first time they executed.

Version

Most probably your current database version is greater than 1, so your app will crash at the start because by default SQLDelight uses a version equal to 1.

Maybe there is a more obvious way, but we've ended up creating 4 migration files *.sqm , which duplicates migrations from Room. Looks like SQLDelight takes the maximum index of migrations provided and increments it by one. Official migration guide can be found here.

Kotlin table wrappers

Room used names of Kotlin classes for table names. Those classes were so Kotlin, that we didn't even consider them as database classes and didn't abstract from them.

SQLDelight behaves the same way from another side: it uses table names for generated Kotlin classes. But these are quite limited by SQL types and can't be conveniently used in business-logic.

So we've abstracted business-logic class from database classes and mapped them. The only problem is that they have the same names now (but different packages, of course). Is there any solution? Sure!

Let's write another migration to rename tables: c08bb2e.

Batch operations

Room supports batch operations (when you need to CRUD list of database entities in a single operation) out of the box. SQLDelight isn't there yet.

Accessing a database for each entity is too inefficient and can take many seconds or even minutes. Fortunately (and quite obviously) there is a transaction mechanism, which can help:

BONUS: Anticipating crashes

We are doing an extensive testing before every release to Google Play / App Store, but sometimes major crashes are leaking out anyway. This time it happened because of some wrong assumptions about SQLDelight.

Shortly, we've messed up names for 2 columns in the existing database. We were thinking that SQLDelight will surely validate the schema and all database queries for us at the app start. But it didn't!

SQLDelight won't check anything before given query is made. And it checks only tables / fields needed for that query. It becomes even more interesting with the following SQL statement:

getAll: SELECT * FROM DbContest;

Produces following generated Kotlin code:

Which doesn't check field names at all, instead of relying on their indexes. This is exactly why we didn't discover crash with:

insert: INSERT OR REPLACE INTO DbContest(id, name, startTimeSeconds, durationSeconds, phase) VALUES(?,?,?,?,?)

Which was used in relatively rare cases. The fix can be found here: 6250eeb

Conclusions

SQLDelight is a great production-ready tool, especially for Kotlin Multiplatform use case. Even though it's not yet widely-adopted and you should be ready for discovering and overcoming some edge cases.

You need to make sure that you have the time and resources to search for solutions, which aren't yet described on Medium or StackOverflow 😉

My lightning talk on .Droidcon EMEA 2020 dedicated to migration to SQLDelight: https://www.droidcon.com/media-detail?video=470608167.