A Guide to SQLDelight

1. Overview

In this tutorial, we’ll discuss the SQLDelight library by Cash App. SQLDelight is a powerful database library made specifically for Kotlin-based projects. This library simplifies the process of interacting with databases by providing a type-safe, compile-time approach. It also allows developers to write SQL queries directly to our code.

2. Advantages of Using SQLDelight

Let’s look at a few reasons why we should consider using SQLDelight:

• Emphasis on type safety: When developers interact with SQL queries in the codebase, we can catch potential errors at compile time. Therefore, we can minimize the runtime exceptions, enhance our code quality, and, most importantly, improve application stability.
• Automatic code generation: SQLDelight uses an annotation processor to generate code based on the defined SQL queries and schema. This enables us to create the necessary Kotlin classes and interfaces to interact with the schema, hence reducing the boilerplate code we need to write by ourselves.
• Efficiency in database interactions: SQLDelight eliminates the need for runtime query parsing and interpretation, too. This optimization improves the efficiency and performance of database interactions overall. Fast query execution leads to a significantly easier time in handling a large volume of transactions.
• Strongly typed database schemas as Kotlin interfaces: This approach leads to compile-time verification of schema changes. This makes it easier for us to detect and prevent database-related issues early in our development cycle.
• Support for Kotlin Multiplatform projects: This allows us to share database code across multiple platforms, such as Android, iOS, and backend servers.
• SQLDelight promotes a single source of truth for the database schema.
• Existing SQL code can be easily migrated to SQLDelight by simply copying and pasting it into the SQLDelight file, with the benefit of immediate type-safe query generation.
• IDE plugins: Provided by different teams, plugins such as the SQLDelight IntelliJ plugin provide additional features and assist us in writing SQL queries.

3. Integrating SQLDelight Into a Project

Let’s review the steps involved in integrating SQLDelight into a Kotlin project.

3.1. Adding the SQLDelight Dependencies

First, we’ll need to add a couple of dependencies to our project’s build.gradle file:

implementation("com.squareup.sqldelight:runtime:$version")
implementation("com.squareup.sqldelight:jdbc-driver:$version")
kapt("com.squareup.sqldelight:gradle-plugin:$version")

Or, if we’re using Maven, we’ll add them to our pom.xml file:

<dependency>
    <groupId>com.squareup.sqldelight</groupId>
    <artifactId>runtime</artifactId>
    <version>version</version>
</dependency>
<dependency>
    <groupId>com.squareup.sqldelight</groupId>
    <artifactId>kotlin-driver</artifactId>
    <version>version</version>
</dependency>

Remember to replace the variable version with the latest stable version of the SQLDelight library from Cash App.

3.2. Defining the Database Schema

We define our database schema using SQL syntax by creating a file ending with the .sq suffix. Afterward, we specify the tables, columns, and queries we need for our application.

Let’s begin our .sq file by creating our schema — for this article, it’s a single table:

CREATE TABLE user (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email_address TEXT
);

After we define the table schema, it is advisable to always build or rather sync our project to ensure the SQLDelight code generation process takes place. Once the code is generated, we can use the generated table class to write queries to our database.

3.3. Writing SQLDelight Queries

Next, let’s write a few queries in our SQLDelight .sq file for our database:

SelectAll:
SELECT * FROM user;
SelectUserById:
SELECT * FROM user WHERE id = ?;
InsertUser:
INSERT INTO user(first_name, last_name, email_address)
VALUES (?, ?, ?);
DeleteUser:
DELETE FROM contact WHERE id = ?;

In our example, after we build our project, the generated code for our model will look like:

String CREATE_TABLE = ""
  + "CREATE TABLE user (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n"
  + "   first_name TEXT NOT NULL, last_name TEXT NOT NULL,\n"
  + "   email_address TEXT)";
String SELECTALL = ""
  + "SELECT * FROM user";
String SELECTUSERBYID = ""
  + "SELECT * FROM user WHERE id = ?";
long id();
@NonNull
String first_name();
@NonNull
String last_name();
@Nullable
String email_address();

4. Common Mistakes to Avoid When Using SQLDelight

Despite SQLDelight being a great library to work with while dealing with databases, it’s important to always adhere to a few guidelines to ensure that we don’t create simple mistakes in our codebase.

First, we should always be keen to ensure that the schema definition accurately reflects the structure of the database tables, columns, indexes, and constraints. If possible, it’s advisable to avoid changing the generated code since any modifications made to our generated files will be overwritten during our next sync.

We should also be conscious not to write complex and unnecessary subqueries to our codebase, as these may impact its performance.

Additionally, runtime errors may occur due to database-related issues. We should ensure proper error handling and communicate errors effectively to our users. If possible, it’s best to always include tests for edge cases, such as handling null values or testing complex queries involving multiple tables.

Finally, whenever we modify the database schema, we must plan and execute proper database migrations to avoid data loss or application crashes.

5. Conclusion

In this article, we went through the SQLDelight library by the Cash App team, which is simply a library to enable us to interact with databases by providing a type-safe, compile-time approach. We also discussed how we could integrate the SQLDelight library into our project, create tables, provide database instances, and write queries.

The full implementation of these examples is available over on GitHub.