Getting started with SQLDelight on Kotlin/JS¶

Info

The synchronous sqljs-driver (pre-2.0) has been replaced with the asynchronous web-worker-driver. This requires configuring the generateAsync setting in your Gradle configuration.

First apply the gradle plugin in your project. Make sure to set generateAsync to true when creating your database.

KotlinGroovy

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

repositories {
  google()
  mavenCentral()
}

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

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

repositories {
  google()
  mavenCentral()
}

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

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.

KotlinGroovy

kotlin {
  sourceSets.jsMain.dependencies {
    implementation("app.cash.sqldelight:web-worker-driver:2.0.0")
    implementation(devNpm("copy-webpack-plugin", "9.1.0"))
  }
}

kotlin {
  sourceSets.jsMain.dependencies {
    implementation "app.cash.sqldelight:web-worker-driver:2.0.0"
    implementation devNpm("copy-webpack-plugin", "9.1.0")
  }
}

The web worker driver allows SQLDelight to communicate with a SQL implementation that is running in a Web Worker. This allows all database operations to happen in a background process.

Info

The web worker driver is only compatible with browser targets.

Configuring a Web Worker¶

SQLDelight's web worker driver isn't tied to a specific implementation of a worker. Instead the driver communicates with the worker using a standardized set of messages. SQLDelight provides an implementation of a worker that uses SQL.js.

See the SQL.js Worker page for details on setting it up for your project, or the Custom Workers page for details on implementing your own.

Using a Web Worker¶

When creating an instance of a web worker driver, you must pass a reference to the web worker that will be used to handle all SQL operations. The Worker constructor accepts URL object that references the worker script.

Webpack has special support for referencing a worker script from an installed NPM package by passing import.meta.url as a second argument to the URL constructor. Webpack will automatically bundle the worker script from the referenced NPM package at build time. The example below shows a Worker being created from SQLDelight's SQL.js Worker.

val driver = WebWorkerDriver(
  Worker(
    js("""new URL("@cashapp/sqldelight-sqljs-worker/sqljs.worker.js", import.meta.url)""")
  )
)

Warning

In order for Webpack to correctly resolve this URL while bundling, you must construct the URL object entirely within a js() block as shown above with the import.meta.url argument.

From here, you can use the driver like any other SQLDelight driver.

Using Queries¶

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.

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

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

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

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

Warning

When using an asynchronous driver, use the suspending awaitAs*() extension functions when running queries instead of the blocking executeAs*() functions.

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