Tuning Your SQLite With SQLDelight & SQLBrite: Part 2
In Lalafo for Android we use SQLite database, because it’s fast, flexible and it’s SQL. However, if you want to use…medium.com
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.
To use SQLDelight, put your SQL statements in a
.sq file, such as
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:
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.
In the world of startups and rapid mobile development, developers can drown in an ocean of new features and forget…medium.com
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 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
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
Don’t start over your Minimum Viable Product from scratch after you’ve received good results. You’ll lose a lot of time…medium.com
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.
- Shared language (SQL)
- Tight coupling with views (MVVM)
- Easier code review
- Confidence writing SQL