Tuning Your SQLite With SQLDelight & SQLBrite: Part 2

In a previous post we considered how we can make our database observable and how to get Rx support. Today we’ll talk about SQLDelight.

Tuning your SQLite with SQLDelight & SQLBrite: Part 1

SQLDelight is a Gradle plugin that generates Java models from your SQL CREATE TABLE statements. These models provide you with a typesafe API able to read & write the rows of your tables. It helps you keep your SQL statements together, organized, and easy to access from Java.

Setup

To use SQLDelight, put your SQL statements in a .sq file, such as src/main/sqldelight/com/example/Images.sq

From this, SQLDelight will generate an ImagesModelJava interface with nested classes for reading and writing the database. Then you only need to implement this Java interface. Using Google’s AutoValue you can minimally make implementations of the model:

The Factory, nested class of ImagesModel, is responsible for mapper and query creation. To create the factory you need to implement the Create interface, which is responsible for creation of Java model.

Notice, that each of statements will have an interface and mapper generated for it, as well as a method in the factory for creating a new instance of the mapper.

As you can see, there are no getColumnIndex method calls. The plugin requires that you specify the table’s in .sq files and plugin generate column IDs in the order you specify automatically. It gives you a 20%, or so, increase in query time as compared to calling the getColumnIndex, which is a huge benefit.

Android dimension with Kotlin extension function: 2.dp 8.sp

Insert example

SQLDelight generates a typesafe class for any statements which should be compiled.

Let’s consider the bind method. SQLDelight use the SQLiteProgram class for arguments binding, not ContentValues. This solution is much better than ContentValues. ContentValues API it’s just a wrapper for the same things. So, in this case, it’s just an unnecessary overhead.

In java code

Example of query

generate this class

In java code

Sets of values also can be passed as an argument

In java you have typesafe method arguments you need

Query with join

The plugin will generate the Java model interface for this join.

Then you need implement this interface like ImagesModel.

Support imports like Java in .sq files

You can use java class as a table field type. For example, create_time field saved in a table as an integer, but when you do a query or insertion in java code you need to pass a Calendar. For the magic to happen, you need to create ColumnAdapter and pass it to Factory constructor.

And for the last one, here's an example of usage SQLBrite and SQLDelight:

Intellij IDEA plugin features:

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

Tips for Creating a Scalable Minimum Viable Product for Android

Conclusion

So, with SQLBrite and SQLDelight we have support for RxJava, Type Safety, Compiler Errors and we still have all-the SQLite features. Also, with AutoValue we have immutability and easier model implementation.

Other benefits:

• Shared language (SQL)
• Tight coupling with views (MVVM)
• Easier code review
• Confidence writing SQL