Announcing SQLDelight 1.0

Heads up, we’ve moved! If you’d like to continue keeping up with the latest technical content from Square please visit us at our new home https://developer.squareup.com/blog

SQLDelight started as a project 4 years ago on the ContentValues and SQLiteOpenHelper APIs from Android with the goal of making writing SQL easy and safe. The library was an early adopter of Kotlin internally, but has been generating Java since its inception. We love Kotlin and weren’t satisfied with a Java API that we knew could be done better in Kotlin, so a year ago we embarked on a complete rewrite focusing entirely on Kotlin.

At the same time Kotlin multiplatform was newly announced and promised easy and safe code sharing across Android and iOS. For Cash App it was the perfect first step to sharing meaningful code, once the schema is shared everything above it could be too: creating viewmodels, syncing with the server, running tests. So, after over a year of rewriting SQLDelight from the ground up, we’re excited to start talking about the new version and what changes it brings for Android and multiplatform development.

The premise of SQLDelight is unchanged: Write SQLite and let the Gradle plugin generate APIs to run your queries for you. SQLDelight files use the .sq extensions and are contained in your src/main/sqldelight folder in a Gradle module.

A simple file with some CREATE TABLE, INSERT, and SELECT statements is all you need to get started:

-- src/main/sqldelight/com/sample/TennisPlayer.sq
CREATE TABLE TennisPlayer(
name TEXT,
points INTEGER,
plays TEXT AS Handedness
);
insert:
INSERT INTO TennisPlayer
VALUES (?, ?, ?);
top10:
SELECT *
FROM TennisPlayer
ORDER BY points DESC
LIMIT 10;

SQLDelight will generate a TennisPlayerQueries class which can run these queries.

val tennisPlayers: TennisPlayerQueries
tennisPlayers.insert("Naomi Osaka", 5270, Handedness.RIGHT)
tennisPlayers.insert("Aryna Sabalenka", 3365, Handedness.RIGHT)
tennisPlayers.insert("Simona Halep", 6641, Handedness.RIGHT)
val top10: List<TennisPlayer> = tennisPlayers.top10()
.executeAsList()
println(top10[0].name) // prints "Simona Halep"

Getting the TennisPlayerQueries object requires a platform driver. SQLDelight includes drivers for SQLite usage in Android, JVM, and iOS platforms. For more in depth information refer to the readme.

For previous users of Square’s SQLite libraries, the code generation has been overhauled using Kotlin, which means a new API generated off of SQL for Kotlin users. To migrate your code from an earlier release of SQLDelight we published some artifacts and have a short guide.

The compiled code also keeps track of active queries and will notify downstream when the results have changed. There’s an extension available to expose this behavior as an RxJava Observable, meaning this release also deprecates SQLBrite. We’ve also added an Android Paging extension to allow exposing queries through a DataSource.

There’s a lot of other features like migration verification, custom types, and set parameters which are all documented in the readme. Give it a try and reach out if you run into any problems!