Design and Implementation of SQLlin: A Kotlin Multiplatform SQLite DSL Framework

Background – After Kotlin 1.7.20 and KMM entering beta, the Ctrip ticket team continued open‑source work, releasing MMKV‑Kotlin and now developing a Kotlin‑based SQLite DSL called SQLlin.

Requirement research – SQLite is the default choice for complex CRUD on mobile, but its native API is low‑level, string‑based, and lacks object mapping, prompting the need for a KMM‑compatible wrapper that solves these three pain points.

Open‑source solutions surveyed

Jetpack Room – Android‑only, annotation‑driven DAO, but requires raw SQL strings and lacks KMM support.

Exposed – JVM‑focused DSL using JDBC; expressive but not KMM and requires verbose lambda nesting.

SQLDelight – Supports KMM via Kotlin Compiler Plugin, provides compile‑time SQL checking, but adds considerable configuration complexity and earlier iOS binary size overhead.

Requirement determination – The team decided to build their own framework with the following goals: (1) support Android and iOS, (2) enable compile‑time SQL validation, (3) provide automatic object‑SQL serialization/deserialization, and (4) keep binary size minimal.

Basic design and implementation

Architecture splits the project into modules: a low‑level sqllin-driver that abstracts platform‑specific SQLite access, a common sqllin-dsl layer exposing a Kotlin‑fluent API, and a sqllin-processor module for KSP code generation.

Driver layer uses Android Framework SQLite Java API on Android and TouchLab’s SQLiter on native platforms. Example of opening a database on native:

fun openDatabase() = memScoped {
    val dbPtr = alloc
>()
    val openResult = sqlite3_open_v2(path, dbPtr.ptr, sqliteFlags, null)
    if (openResult != SQLITE_OK) {
        throw IllegalStateException(sqlite3_errmsg(dbPtr.value)?.toKString() ?: "")
    }
}

Common expect/actual declarations illustrate the multiplatform contract:

public expect fun openDatabase(config: DatabaseConfiguration): DatabaseConnection

Implementation on Android:

public actual fun openDatabase(config: DatabaseConfiguration): DatabaseConnection {
    SQLiteDatabase.openDatabase()
    // ...
}

Implementation on native:

public actual fun openDatabase(config: DatabaseConfiguration): DatabaseConnection {
    createDatabaseManager(configNative).createMultiThreadedConnection()
    // ...
}

KSP‑generated Table example – Annotating a data class with @DBRow("person") produces a singleton table with column properties and setter extensions, eliminating boilerplate.

// KSP generated:
object PersonTable : Table
("person") {
    val name: ClauseString get { … }
    val age: ClauseNumber get { … }
    var SetClause
.name: String set(value) { … }
    var SetClause
.age: Int set(value) { … }
}

Custom deserialization – A QueryDecoder implements AbstractDecoder to turn a CommonCursor into Kotlin objects without reflection, leveraging kotlinx‑serialization.

@OptIn(ExperimentalSerializationApi::class)
internal class QueryDecoder(private val cursor: CommonCursor) : AbstractDecoder() {
    private var elementIndex = 0
    private var elementName = ""
    override val serializersModule = EmptySerializersModule()
    // decodeElementIndex, beginStructure, and primitive decode methods omitted for brevity
}

Final effect – Sample usage demonstrates concise DSL syntax for INSERT, DELETE, UPDATE, and SELECT, with tables generated by KSP and no operator overloading due to type‑system constraints.

fun sample() {
    lateinit var statement: SelectStatement
database {
        PersonTable { table ->
            table INSERT listOf(tom, jerry, nick)
            table DELETE WHERE (name EQ "Jerry")
            table UPDATE SET { age = 27 } WHERE (name NEQ "Nick")
            statement = table SELECT WHERE (name EQ "Tom") ORDER_BY (age to DESC)
        }
    }
    val result: List
= statement.getResult()
}

Future plans – Add sub‑query and JOIN support, DDL operations (create/drop tables, alter columns), extend to JVM backend, and eventually support Windows builds once CI/CD pipelines are ready.

References – Links to the original Ctrip articles, MMKV‑Kotlin repo, Jetpack Room docs, Exposed, SQLDelight, custom decoder docs, and the SQLlin GitHub repository.