Set up SQLite for KMP

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.

Set up dependencies

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

[versions]
sqlite = "2.7.0"

[libraries]
# The SQLite Driver interfaces
androidx-sqlite = { module = "androidx.sqlite:sqlite", version.ref = "sqlite" }

# The bundled SQLite driver implementation
androidx-sqlite-bundled = { module = "androidx.sqlite:sqlite-bundled", version.ref = "sqlite" }

[plugins]
ksp = { id = "com.google.devtools.ksp", version.ref = "ksp" }

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 three main interfaces:

• SQLiteDriver: The entry point to SQLite, used to open database connections.
• SQLiteConnection: Represents the sqlite3 object.
• SQLiteStatement: Represents 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 the SQLite C APIs, the common usage pattern consists of these steps:

1. Open a database connection using the instantiated SQLiteDriver implementation.
2. Prepare a SQL statement using SQLiteConnection.prepare.
3. Execute a SQLiteStatement by doing the following:
   1. Optional: Bind arguments using the bind* functions.
   2. Iterate over the result set using the step function.
   3. Read columns from the result set using the get* functions.

Driver Implementations

The following table summarizes the available driver implementations:

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 more robust access to SQLite, we recommend Room.

A RoomDatabase relies on a SQLiteDriver to perform database operations, and you must configure an implementation using RoomDatabase.Builder.setDriver. Room provides RoomDatabase.useReaderConnection and RoomDatabase.useWriterConnection for more direct access to the managed database connections.

Migrate to Kotlin Multiplatform

You must migrate any use of low-level Support SQLite API components, such as the SupportSQLiteDatabase interface, to the equivalent SQLite Driver components.

val connection: SQLiteConnection = ...
connection.execSQL("BEGIN IMMEDIATE TRANSACTION")
try {
  // perform database operations in transaction
  connection.execSQL("END TRANSACTION")
} catch(t: Throwable) {
  connection.execSQL("ROLLBACK TRANSACTION")
}

val connection: SQLiteConnection = ...
connection.execSQL("ALTER TABLE ...")

val connection: SQLiteConnection = ...
connection.prepare("SELECT * FROM Pet").use { statement ->
  while (statement.step()) {
    // read columns
    statement.getInt(0)
    statement.getText(1)
  }
}

connection.prepare("SELECT * FROM Pet WHERE id = ?").use { statement ->
  statement.bindInt(1, id)
  if (statement.step()) {
    // row found, read columns
  } else {
    // row not found
  }
}

val database: SupportSQLiteDatabase = ...
database.beginTransaction()
try {
  // perform database operations in transaction
  database.setTransactionSuccessful()
} finally {
  database.endTransaction()
}

val database: SupportSQLiteDatabase = ...
database.execSQL("ALTER TABLE ...")

val database: SupportSQLiteDatabase = ...
database.query("SELECT * FROM Pet").use { cursor ->
  while (cursor.moveToNext()) {
    // read columns
    cursor.getInt(0)
    cursor.getString(1)
  }
}

database.query("SELECT * FROM Pet WHERE id = ?", id).use { cursor ->
  if (cursor.moveToNext()) {
    // row found, read columns
  } else {
    // row not found
  }
}