SQLite (Kotlin Multiplatform)

The androidx.sqlite library contains abstract interfaces along with basic implementations which can be used to build your own libraries that access SQLite. You might want to consider using the Room library, which provides an abstraction layer over SQLite to allow for more robust database access while harnessing the full power of SQLite.

Setting up dependencies

The current version of androidx.sqlite that supports Kotlin Multi-Platform (KMP) is 2.5.0-alpha01 or higher.

To setup SQLite in your KMP project, add the dependencies for the artifacts in the build.gradle.kts file for your module:

• androidx.sqlite:sqlite - The SQLite Driver interfaces
• androidx.sqlite:sqlite-bundled - The bundled driver implementation

SQLite Driver APIs

The androidx.sqlite library groups offer low-level APIs for communicating with the SQLite library either included in the library when using androidx.sqlite:sqlite-bundled or in the host platform, such as Android or iOS when using androidx.sqlite:sqlite-framework. The APIs closely follow the core functionality of SQLite C API.

There are 3 main interfaces:

• SQLiteDriver - It is the entry point to use SQLite and is responsible for opening database connections.
• SQLiteConnection - Is the representation of the sqlite3 object.
• SQLiteStatement - Is the representation of the sqlite3_stmt object.

The following example showcases the core APIs:

fun main() {
  val databaseConnection = BundledSQLiteDriver().open("todos.db")
  databaseConnection.execSQL(
    "CREATE TABLE IF NOT EXISTS Todo (id INTEGER PRIMARY KEY, content TEXT)"
  )
  databaseConnection.prepare(
    "INSERT OR IGNORE INTO Todo (id, content) VALUES (? ,?)"
  ).use { stmt ->
    stmt.bindInt(index = 1, value = 1)
    stmt.bindText(index = 2, value = "Try Room in the KMP project.")
    stmt.step()
  }
  databaseConnection.prepare("SELECT content FROM Todo").use { stmt ->
    while (stmt.step()) {
      println("Action item: ${stmt.getText(0)}")
    }
  }
  databaseConnection.close()
}

Similar to SQLite C APIs the common usage is to:

• Open a database connection using the instantiated SQLiteDriver implementation.
• Prepare a SQL statement using SQLiteConnection.prepare()
• Execute a SQLiteStatement by:
  • Optionally binding arguments using the bind*() functions.
  • Iterating over the result set using the step() function.
  • Reading columns from the result set using the get*() functions.

Driver Implementations

The following table summarizes the available driver implementations:

Class Name	Artifact	Supported Platforms
AndroidSQLiteDriver	androidx.sqlite:sqlite-framework	Android
NativeSQLiteDriver	androidx.sqlite:sqlite-framework	iOS, Mac, and Linux
BundledSQLiteDriver	androidx.sqlite:sqlite-bundled	Android, iOS, Mac, Linux and JVM (Desktop)

The recommended implementation to use is BundledSQLiteDriver available in androidx.sqlite:sqlite-bundled. It includes the SQLite library compiled from source, offering the most up-to-date version and consistency across all the supported KMP platforms.

SQLite Driver and Room

The driver APIs are useful for low-level interactions with an SQLite database. For a feature rich library that provides a more robust access of SQLite then Room is recommended.

A RoomDatabase relies on a SQLiteDriver to perform database operations and an implementation is required to be configured using RoomDatabase.Builder.setDriver(). Room provides RoomDatabase.useReaderConnection and RoomDatabase.useWriterConnection for more direct access to the managed database connections.