Getting started with SQLDelight on Kotlin/Native

Kotlin/Native Memory Manager

Since SQLDelight 2.0, the SQLDelight Native driver only supports Kotlin/Native's new memory manager.

First apply the gradle plugin in your project.

KotlinGroovy

plugins {
  id("app.cash.sqldelight") version "2.0.2"
}

repositories {
  google()
  mavenCentral()
}

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

plugins {
  id "app.cash.sqldelight" version "2.0.2"
}

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 the SQLDelight Native driver dependency to your project.

KotlinGroovy

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

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

An instance of the driver can be constructed as shown below, and requires a reference to the generated Schema object.

val driver: SqlDriver = NativeSqliteDriver(Database.Schema, "test.db")

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.

Reader Connection Pools

Disk databases can (optionally) have multiple reader connections. To configure the reader pool, pass the maxReaderConnections parameter to the various constructors of NativeSqliteDriver:

val driver: SqlDriver = NativeSqliteDriver(
    Database.Schema, 
    "test.db", 
    maxReaderConnections = 4
)

Reader connections are only used to run queries outside of a transaction. Any write calls, and anything in a transaction, uses a single connection dedicated to transactions.