Getting Started with SQLite on Android

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"
    }
  }
}

Tip

It's recommended to switch Android Studio to use the "Project" view instead of the "Android" view of your files, to make it easier to find and edit SQLDelight files.

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

KotlinGroovy

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

dependencies {
  implementation "app.cash.sqldelight:android-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 = AndroidSqliteDriver(Database.Schema, context, "test.db")

Info

The AndroidSqliteDriver will automatically create or migrate your schema when the driver is constructed. Migrations can also be executed manually if needed. See Code Migrations for more

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.

SQLite Versions

For Android projects, the SQLDelight Gradle plugin will automatically select the SQLite dialect version based on your project's minSdkVersion setting. See here for the list of supported SQLite versions on each Android SDK level.