An absolute delight

SQLDelight 1.0 arrives with a complete rework

Eirini-Eleni Papadopoulou
© Shutterstock / Zakhar Mar

SQLDelight, a library that generates typesafe APIs from your SQL statements, just reached its 1.0 milestone, which happens to bring a complete rework. Let’s have a closer look at what this library has to offer.

Some of you may already be familiar with SQLDelight since the project has been around for quite some time now, but for those of you who hear about it for the first time, SQLDelight is a library that generates typesafe APIs from your SQL statements.

However, what’s more interesting about this tool is that the team behind recently announced some big changes.

First and foremost, SQLDelight 1.0 is live and it’s completely reworked!

Since its inception, SQLDelight has been generating Java, however, the team was not happy with a Java API that they knew could be done better in Kotlin, so a year ago the embarked on a journey to completely rewrite the library focusing entirely on Kotlin.

Let’s have a look at the results.

An absolute delight

Custom projections – By default queries will return a data class implementation of the table schema. To override this behavior pass a custom mapper to the query function. Your custom mapper will receive typesafe parameters which are the projection of your select statement.

Query arguments – .sq files use the exact same syntax as SQLite, including SQLite Bind Args. If a statement contains bind args, the associated method will require corresponding arguments. Sets of values can also be passed as an argument and you can use named parameters or indexed parameters.

Types – SQLDelight column definitions are identical to regular SQLite column definitions but support an extra column constraint which specifies the Kotlin type of the column in the generated interface. Also, it natively supports Long, Double, String, ByteArray, Int, Short, Float, and Booleans. Boolean columns are stored in the db as INTEGER, and so they can be given INTEGER column constraints. Use DEFAULT 0 to default to false, for example.

Custom column types – If you’d like to retrieve columns as custom types you can specify a Kotlin type like this:

import kotlin.collections.List;

CREATE TABLE hockeyPlayer (
  cup_wins TEXT AS List<String> NOT NULL

However, creating the Database will require you to provide a ColumnAdapter which knows how to map between the database type and your custom type. Check out the relevant info here.

Enums – For convenience, the SQLDelight runtime includes a ColumnAdapter for storing an enum as TEXT.

RxJava – To observe a query, depend on the RxJava extensions artifact and use the extension method it provides. Check out the information here.

Multiplatform – To use SQLDelight in Kotlin multiplatform configure the Gradle plugin with a package to generate code into. You can find more information here.

Android paging – To use SQLDelight with Android’s Paging Library add a dependency on the paging extension artifact. Find the detailed information here.

Supported dialects – Full support of SQLite dialect including views, triggers, indexes, FTS tables, etc.

IntelliJ plugin – The IntelliJ plugin provides language-level features for .sq files, including:

  • Syntax highlighting
  • Refactoring/Find usages
  • Code autocompletion
  • Generate Queries files after edits
  • Right click to copy as valid SQLite
  • Compiler errors in IDE click through to file

SEE ALSO: SQL rejoins TIOBE Index while Python continues to climb

Getting started

If you are interested in giving it a go, all you will need is a simple file with some CREATE TABLEINSERT, and SELECT statements.

-- src/main/sqldelight/com/sample/TennisPlayer.sq
CREATE TABLE TennisPlayer(
  name TEXT,
  points INTEGER,
  plays TEXT AS Handedness
INSERT INTO TennisPlayer
VALUES (?, ?, ?);
FROM TennisPlayer
Eirini-Eleni Papadopoulou
Eirini-Eleni Papadopoulou is an assistant editor for Just finished her masters in Modern East Asian Studies and plans to continue with her old hobby that is computer science.