Getting Started with SQLite on Multiplatform

First apply the gradle plugin in your project.

KotlinGroovy

plugins {
  id("app.cash.sqldelight") version "2.1.0-SNAPSHOT"
}

repositories {
  google()
  mavenCentral()
}

sqldelight {
  databases {
    create("Database") {
      packageName.set("com.example")
    }
  }
}

plugins {
  id "app.cash.sqldelight" version "2.1.0-SNAPSHOT"
}

repositories {
  google()
  mavenCentral()
}

sqldelight {
  databases {
    Database { // This will be the name of the generated database class.
      packageName = "com.example"
    }
  }
}

Defining the Schema

Write your SQL statements in a .sq file under src/main/sqldelight. Typically the first statement in the .sq file creates a table, but you can also create indexes or set up default content.

src/main/sqldelight/com/example/sqldelight/hockey/data/Player.sq

CREATE TABLE hockeyPlayer (
  player_number INTEGER PRIMARY KEY NOT NULL,
  full_name TEXT NOT NULL
);

CREATE INDEX hockeyPlayer_full_name ON hockeyPlayer(full_name);

INSERT INTO hockeyPlayer (player_number, full_name)
VALUES (15, 'Ryan Getzlaf');

From these statements, SQLDelight will generate a Database class with an associated Schema object that can be used to create your database and execute statements on it. The Database class is generated by the generateSqlDelightInterface Gradle task which is run automatically by the SQLDelight IDE plugin when you edit a .sq file, and also as part of a normal Gradle build.

To use the generated database in your code, you must add a SQLDelight driver dependency to your project. Each target platform has its own driver implementation.

KotlinGroovy

kotlin {
  sourceSets.androidMain.dependencies {
    implementation("app.cash.sqldelight:android-driver:2.1.0-SNAPSHOT")
  }

  // or iosMain, windowsMain, etc.
  sourceSets.nativeMain.dependencies {
    implementation("app.cash.sqldelight:native-driver:2.1.0-SNAPSHOT")
  }

  sourceSets.jvmMain.dependencies {
    implementation("app.cash.sqldelight:sqlite-driver:2.1.0-SNAPSHOT")
  }
}

kotlin {
  sourceSets.androidMain.dependencies {
    implementation "app.cash.sqldelight:android-driver:2.1.0-SNAPSHOT"
  }

  // or iosMain, windowsMain, etc.
  sourceSets.nativeMain.dependencies {
    implementation "app.cash.sqldelight:native-driver:2.1.0-SNAPSHOT"
  }

  sourceSets.jvmMain.dependencies {
    implementation "app.cash.sqldelight:sqlite-driver:2.1.0-SNAPSHOT"
  }
}

Constructing Driver Instances

Create a common factory class or method to obtain a SqlDriver instance.

src/commonMain/kotlin

import com.example.Database

expect class DriverFactory {
  fun createDriver(): SqlDriver
}

fun createDatabase(driverFactory: DriverFactory): Database {
  val driver = driverFactory.createDriver()
  val database = Database(driver)

  // Do more work with the database (see below).
}

Then implement this for each target platform:

src/androidMain/kotlinsrc/nativeMain/kotlinsrc/jvmMain/kotlin

actual class DriverFactory(private val context: Context) {
  actual fun createDriver(): SqlDriver {
    return AndroidSqliteDriver(Database.Schema, context, "test.db")
  }
}

actual class DriverFactory {
  actual fun createDriver(): SqlDriver {
    return NativeSqliteDriver(Database.Schema, "test.db")
  }
}

actual class DriverFactory {
  actual fun createDriver(): SqlDriver {
    val driver: SqlDriver = JdbcSqliteDriver("jdbc:sqlite:test.db", Properties(), Database.Schema)
    return driver
  }
}

For use with Kotlin/JS, see here.

Defining Typesafe Queries

SQLDelight will generate a typesafe function for any labeled SQL statement in a .sq file.

src/main/sqldelight/com/example/sqldelight/hockey/data/Player.sq

selectAll:
SELECT *
FROM hockeyPlayer;

insert:
INSERT INTO hockeyPlayer(player_number, full_name)
VALUES (?, ?);

insertFullPlayerObject:
INSERT INTO hockeyPlayer(player_number, full_name)
VALUES ?;

A "Queries" object will be generated for each .sq file containing labeled statements. For example, a PlayerQueries object will be generated for the Player.sq file shown above. This object can be used to call the generated typesafe functions which will execute the actual SQL statements.

fun doDatabaseThings(driver: SqlDriver) {
  val database = Database(driver)
  val playerQueries: PlayerQueries = database.playerQueries

  println(playerQueries.selectAll().executeAsList()) 
  // [HockeyPlayer(15, "Ryan Getzlaf")]

  playerQueries.insert(player_number = 10, full_name = "Corey Perry")
  println(playerQueries.selectAll().executeAsList()) 
  // [HockeyPlayer(15, "Ryan Getzlaf"), HockeyPlayer(10, "Corey Perry")]

  val player = HockeyPlayer(10, "Ronald McDonald")
  playerQueries.insertFullPlayerObject(player)
}

And that's it! Check out the other pages on the sidebar for other functionality.