Kotlin Multiplatform bindings exposing a near-complete SQLite API, backed by the same SQLite build across every supported target.

• Why Ksqlite
• Supported platforms
• Capabilities
• Limitations
• Project state
• Choosing an implementation
• Requirements
• Getting started
• Modules
• Contributing
• Documentation
• License

This project exists because another, upcoming project of mine needed SQLite features that no existing "driver-oriented" wrapper covered. As a power user, I also like "owning" my apps. Cutting a feature, or piling up workarounds until the app itself starts feeling like one big workaround, wasn't something I was willing to accept.

A few recurring pain points from existing solutions pushed me toward writing my own:

• Missing support for a legitimate Kotlin target
• Having to write your own expect/actual code just to configure/load the native library
• Every function marked suspend because of one problematic target
• Solutions replicating Android's behavior everywhere, introducing inconsistencies you have to work around yourself, even though 90% of your targets aren't Android
• Unaligned compile options and SQLite versions across platforms, leaving you unsure whether a given feature is actually available

Ksqlite avoids all of that. The contract is simple: a nearly one-to-one mapping of SQLite's C API, so the official SQLite documentation stays your primary reference. Delivering that consistently across every target means this project owns the whole pipeline, from compiling SQLite itself to shipping the right resources to the end application.

Covering this many SQLite APIs was never the plan. At some point I lost control, purely for the pleasure of problem-solving, and kept implementing more than I actually needed. At this point there's little reason left for another SQLite library to show up, unless it can meaningfully cut the interop cost in hot loops, something most apps never get close to needing.

Every Kotlin target is supported, including simulators, except wasmWasi:

• On JVM, only major operating systems are currently supported, though nothing prevents adding a non-major one. Artifacts are embedded in the JAR and can be found at <jar>/native/<linux|macos|windows>_<arch>/<artifact>.
• On JS and WasmJs, only the browser sub-target is supported, no Node.js. Long also compiles down to native BigInt on JS specifically, see The JS case.
• Although SQLite seems to support WASI builds, there is currently no support for the Kotlin wasmWasi target. Support may be added in a future release.

[!NOTE] Every target embeds a compiled library. Expect the final application to grow by a few megabytes, because of it. The library sizes range from 1.5MB to 2.6MB. The Wasm resources are less than 4MB.

[!IMPORTANT] macosX64, tvosX64, and watchosX64 were deprecated by Kotlin itself as of 2.3.20. Ksqlite still builds and ships them for now.

• Same SQLite version everywhere, kept at the latest, or near-latest, release
• Some compile options guaranteed, so a given feature's availability is consistent across targets, see Sqlite.kt for the (non-exhaustive) list of common compile options
• Every build includes SQLite3MultipleCiphers, full encryption support is on its way, see Project state

• ~220+ supported SQLite C functions
• Generous configuration support: sqlite3_config(), sqlite3_db_config(), sqlite3_vtab_config()
• Every global and connection-level hook
• Application-defined functions: scalar, aggregate, window
• Backup API
• (De)serialization API
• Incremental blob I/O API
• Snapshot API
• Write-ahead log API
• Virtual tables: regular, eponymous, eponymous-only

• Automatic native memory management, with a few explicit exceptions, see choosing a module
• Enumerations and sealed hierarchies instead of raw integers, wherever it's meaningful
• SQLite's own semantics preserved almost everywhere
• Automatic native library loading. The first thing you still have to call yourself is sqlite3_initialize()
• A small ksqlite.h/ksqlite.c shim on top of SQLite itself: interop-friendly versions of a few C functions, and a couple of extra declarations. Extra functions purely to save native call round-trips in hot loops are a possibility.

Unless listed below, there's no reason a given SQLite API or feature can't be supported: if it's missing from the source, it simply hasn't been implemented yet.

None of SQLite's UTF-16 routines are exposed. This isn't really a limitation, more a choice: adding UTF-16 support on every target is extra work, though some platforms would genuinely benefit from it. See Project state for the current per-target picture and notes on picking it up.

The xShadowName virtual table hook isn't supported, or at least needs a workaround, due to the lack of context SQLite provides when invoking it.

This project is in active development, and still experimental. Breaking changes can land at any time, though not many are expected at this point.

Done:

• Mastering the SQLite build across every target
• The one-to-one mapping in ksqlite-capi
• The object-oriented API in ksqlite-kapi

Planned:

• Stabilizing ksqlite-kapi
• WASM OPFS support, plus WAL mode
• A proxy API (client/server), a requirement for WASM OPFS
• A suspend API, for the two cases that actually call for it: a ksqlite-kapi wrapper for local SQLite, and a proxy wrapper for non-local SQLite, WebWorkers included
• The full SQLite3MultipleCiphers API, enabling encryption on every target (its own pragmas shouldn't be relied on until then)
• Wrapper functions in ksqlite.h/ksqlite.c, to cut native call round-trips during large queries

Not planned:

• Deprecated SQLite APIs
• Dynamic string APIs (kotlin.String and string templates already cover this)
• Mutex APIs (the JVM has its own, Kotlin/Native has third-party libraries for it)
• Windows-specific APIs
• UTF-16 support
• VFS support

None of this is set in stone, but my attention has been drifting toward the bigger project this one was born from, and the pressure to find a job is real. I've heard time can be bought, though 😈

Multiple implementations are available, and at least one more is coming.

Every implementation goes through a native call, which costs a few nanoseconds each time. That's irrelevant for a handful of rows, but caching a value read out of an interop call starts to matter once a hot loop iterates over tens of thousands of rows or more. Wrapper functions are planned to further reduce these costs, particularly when pagination is not possible or desired.

Some enumerations and sealed hierarchies are shared across every implementation.

[!TIP] Enabling Kotlin's context-sensitive resolution makes working with Ksqlite's enumerations and sealed hierarchies noticeably nicer, short names instead of fully qualified ones.

The main module of this project.

• Automatic memory management, with a few exceptions described here
• Automatic loading of the Ksqlite library
• The one-to-one mapping of the SQLite C API
• Mostly top-level declarations
• Never throws. If it does, one of the following is true:
  • it's a bug, please open an issue
  • a hard contract was violated, check the documentation
  • the VM ran out of memory (platform-dependent), watch for your platform's low-memory signal
• Every other module, current or future, depends on it directly or indirectly

ksqlite-capi is the lightest module and the closest to raw SQLite, at the cost of a Kotlin-unfriendly API. Pick it for minimal dependencies, or as a base to build your own object-oriented API on top of.

[!WARNING] ksqlite-capi needs to be initialized.

[!TIP] For Android and JVM, Ksqlite can be loaded eagerly by initializing SQLite at the desired time (for example, at application startup).

One of infinitely many possible object-oriented APIs for SQLite.

• Wraps ksqlite-capi without exposing it, don't depend on both at once
• A Kotlin-friendly API
• kotlin.Boolean is back
• Default parameter values
• Extended memory management, able to release some of the resources ksqlite-capi can't, see here
• Every lifecycle-aware resource implements AutoCloseable, the Kotlin standard library handles the rest
• Stronger protection against SQLITE_MISUSE, only exposing the API available in the current context
• Throws SQLiteException wherever SQLite can fail, instead of returning a result code
• Throws IllegalStateException when accessing a closed resource, or attempting an operation with potentially undesirable side effects

ksqlite-kapi is generally simpler and safer than ksqlite-capi, though try/catch may still be everywhere in your code, like with any SQLite library. It should be your default choice, unless you target Wasm and need OPFS. That still needs the third module.

• Kotlin 2.4.0 is the baseline
• Android minSdk 21 or above
• ESM and BigInt-backed Long on Kotlin/JS, see The JS case
• JDK 17+ or 22+ if desktop JVM is targeted since Ksqlite makes use of Java FFM
• Gradle:
  • 9.1.0+ if the Ksqlite Gradle plugin is not needed
  • 9.4.0+ otherwise, as modern lazy API is used by the plugin

SQLite's C API relies on 64-bit integers throughout, row ids included. To keep that precision consistent with every other target, Ksqlite enables a 64-bit WASM build, so those values cross the JS↔WASM boundary as native BigInt. Kotlin/JS's Long doesn't use that representation by default, and mixing the two crashes at runtime.

Consuming projects need useEsModules(), and may need to enable BigInt support too:

// build.gradle.kts
kotlin {
    js {
        useEsModules()

        compilerOptions {
            freeCompilerArgs.add("-Xes-long-as-bigint")
        }
    }
}

This compiler flag isn't required for WasmJs.

[!NOTE] -Xes-long-as-bigint is still an experimental Kotlin compiler flag.

// build.gradle.kts
kotlin {
    sourceSets {
        commonMain.dependencies {
            implementation("io.github.manriif.ksqlite:ksqlite-capi:<version>")
        }
    }
}

From there, this is close to using SQLite's C API directly. The snippet below uses check for result validation, for simplicity, and assumes Kotlin's context-sensitive resolution is enabled so bare names like OK resolve to SqliteResultCode.OK:

val initResult = sqlite3_initialize()

if (initResult != SqliteResultCode.OK) {
    error("SQLite initialization failed")
}

val outDb = sqlite3.OutputParam()
val openResult = sqlite3_open(":memory:", outDb)

val db = if (openResult.isOk) {
    // outDb.value is guaranteed to be non-null if and only if openResult is SqliteResultCode.OK
    outDb.value!!
} else {
    // sqlite3_open[_v2] may return a failure result code but still a non-null sqlite3 object,
    // left in an 'error state'
    val errMsg = outDb.value?.let(::sqlite3_errmsg) ?: "attach your debugger, problems started"
    error("Open connection failed with result $openResult: $errMsg")
}

check(sqlite3_exec(db, "CREATE TABLE fruits(name TEXT NOT NULL);", null, null, null) == OK)

val outInsert = sqlite3_stmt.OutputParam()
check(sqlite3_prepare_v2(db, "INSERT INTO fruits VALUES (?);", outInsert) == OK)

val insert = checkNotNull(outInsert.value)
check(sqlite3_bind_text(insert, 1, "Kiwi") == OK)
check(sqlite3_step(insert) == DONE)
check(sqlite3_finalize(insert) == OK)

val outSelect = sqlite3_stmt.OutputParam()
check(sqlite3_prepare_v2(db, "SELECT name FROM fruits;", outSelect) == OK)
val select = checkNotNull(outSelect.value)

while (sqlite3_step(select) == ROW) {
    println(sqlite3_column_text(select, 0))
}

check(sqlite3_finalize(select) == OK)
check(sqlite3_close(db) == OK)
check(sqlite3_shutdown() == OK)

// build.gradle.kts
kotlin {
    sourceSets {
        commonMain.dependencies {
            implementation("io.github.manriif.ksqlite:ksqlite-kapi:<version>")
        }
    }
}

Most of the calls from below snippet can throw an SQLiteException carrying the error code and message. They aren't caught for brevity:

val sqlite = SQLite.initialize()
val db = sqlite.open(":memory:")

db.execute("CREATE TABLE fruits(name TEXT NOT NULL);")

db.prepare("INSERT INTO fruits VALUES (?);").use { insert ->
    insert.parameters.bind(1, "Kiwi")
    insert.step()
}

val select = db.prepare("SELECT name FROM fruits;")
var row: Row? = select.step()

while (row != null) {
    println(row.getString(0))
    row = select.step()
}

select.close()
db.close()
sqlite.close()

When targeting JS or WasmJs, the ksqlite-gradle-plugin must also be applied, in the final application module, intermediate modules don't need it:

// webApp/build.gradle.kts
plugins {
    id("io.github.manriif.ksqlite") version "<version>"
}

See its README for how to enable a test runner, or for more detail on what the plugin does.

Bug reports and pull requests are welcome. See CONTRIBUTING.md for how to build this project locally, the hardware and IDE it currently expects, and how to run its tests.

UTF-16 and VFS support are two ready-made opportunities if you're looking for a way in, see Project state for notes on both.

Documentation for the ksqlite-capi module is available here. For the other modules, Dokka generated one is there.

Kotlin SQLite is licensed under the Apache 2.0 License.