SQLDelight: Getting Started

I wanted to try out SQLDelight, a library (and IntelliJ plugin) that generates Java models based off of SQL statements. It took me a while to get it working, and try to understand the concepts. So here’s my getting started guide.

Step 1: Install the thing

For this example I’m going to be using Google’s AutoValue, because why not. Here’s what needs to be added to your gradle build files.

• Project build.grade file:

dependencies {
    // any other shizzle
    classpath ‘com.squareup.sqldelight:gradle-plugin:0.5.1’
    classpath ‘com.neenbedankt.gradle.plugins:android-apt:1.4’
}

• App build.gradle file:

apply plugin: ‘com.squareup.sqldelight’
apply plugin: ‘com.neenbedankt.android-apt’
 
dependencies {
    apt 'com.google.auto.value:auto-value:1.3'
    provided 'com.google.auto.value:auto-value:1.3'
}

• IntelliJ Plugin (from Android Studio):

As well as the gradle alterations, you may want to install the SQLDelight IntelliJ Plugin. Goto Preferences then Plugins then Browse Repositories. Find and install the plugin, and restart Android Studio:

We’re all installed and ready to go…

Step 2: Write the SQL Statements

Lets get the folder structure in order first. Goto your ../src/main directory and create a folder called “sqldelight”.
From inside that directory you need to replicate your package structure, to where you’re going to store your models. Example:

A picture tells a thousand words, here’s what my directories look like:

So now, inside the sqldelight/{your_structure} folder create a file. For this example I’m going to create a contacts database (yeah, original, I know).
So create a Contact.sq file. This file is going to house your create table statement, and any queries for that table.

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

SelectAll:
SELECT * FROM contact;

SelectContactById:
SELECT * FROM contact WHERE id = ?;

InsertContact:
INSERT INTO contact(first_name, last_name, email_address)
VALUES (?, ?, ?);

DeleteContact:
DELETE FROM contact WHERE id = ?;

The first statement in this file should be your CREATE TABLE statement;

All other queries need to be tagged. Now, the examples I’ve seen are using lowercase underscores, this works well when you’re accessing the constant, but looks bad when accessing the class. So I’ve opted to not use them…

Step 3: Build

Build your project, nothing happened, or did it? Yes, SQLDelight generated models. Search for ContactModel and you shall see.

Notice its marked email as nullable and the other not null, pretty neat

Step 4: Make models

What? SQLDelight already did this. Yes, but they’re not that useful yet.
Create a Model called Contact in your apps model package, annotate it with @AutoValue, make it abstract, and implement ContactModel.

Now to create some methods that are going to help us later.

public static final Creator<Contact> CREATOR = new Creator<Contact>() {
    @Override
    public Contact create(long id, @NonNull String first_name, @NonNull String last_name, @Nullable String email_address) {
        return new AutoValue_Contact(id, first_name, last_name, email_address);
    }
};

public static final Factory<Contact> FACTORY = new Factory<>(CREATOR);
public static final Mapper<Contact> MAPPER = new Mapper<>(FACTORY);

Step 5: Use the things

Create a SQLiteOpenHelper class, something like this:

public class OpenHelper extends SQLiteOpenHelper {
public static final String DB_NAME = “sqldelight.db”;
public static final int DB_VERSION = 1;
private static OpenHelper instance;
public static OpenHelper getInstance(Context context) {
    if (null == instance) {
        instance = new OpenHelper(context);
    }
    return instance;
 }
private OpenHelper(Context context) {
    super(context, DB_NAME, null, DB_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(Contact.CREATE_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // upgrade logic
}
}

• Create the table:

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(Contact.CREATE_TABLE);
}

SQLDelight took care of that nicely for us :)

• Insert some stuff:

Here’s where it gets cool (well, we might have to define cool):

Contact.InsertContact insertContact = new ContactModel.InsertContact(db);
insertContact.bind(“Joe”, “Smith”, “joe.smith@gmail.com”);
insertContact.program.execute();

We’ve just inserted a row, nice and simple.

• Get some stuff: (EDIT: 28 Mar 17)

List<Contact> contactList = new ArrayList<>();
SQLDelightStatement query = Contact.FACTORY.SelectAll()
Cursor cursor = sqLiteDatabase.rawQuery(query.statement, query.args);
cursor.moveToFirst();
while (!cursor.isAfterLast()){
    contactList.add(Contact.FACTORY.selectAllMapper().map(cursor));
    cursor.moveToNext();
}
cursor.close();

We just got all contacts, SQLDelight took care of all the mapping for us :)

• Delete some stuff:

long contactToDelete = 1;
Contact.DeleteContact deleteContact = new ContactModel.DeleteContact(sqLiteDatabase);
deleteContact.bind(contactToDelete);
deleteContact.program.execute();

We just deleted a contact.

We’re all done, for now.

I’ve written this to try and simplify the process of installation and setup. Using information from the following sites / blogs:

square/sqldelight

Delightful persistence on Android

.. and finally, I highly recommend using Facebook Stetho for help debugging database, as well as it’s awesome network debugging capabilities.