Getting Started on JVM with SQLite

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 SQLite driver dependency to your project.

KotlinGroovy

dependencies {
  implementation("app.cash.sqldelight:sqlite-driver:2.0.2")
}

dependencies {
  implementation "app.cash.sqldelight:sqlite-driver:2.0.2"
}

An instance of the driver can be constructed as shown below. The constructor accepts a JDBC connection string that specifies the location of the database file. The IN_MEMORY constant can also be passed to the constructor to create an in-memory database.

On-DiskIn-Memory

val driver: SqlDriver = JdbcSqliteDriver("jdbc:sqlite:test.db")
Database.Schema.create(driver)

val driver: SqlDriver = JdbcSqliteDriver(JdbcSqliteDriver.IN_MEMORY)
Database.Schema.create(driver)

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.