Two great tools to deal with SQLite within your Android app: SQLDelight and SQLBrite

In case you are using SQLite and RxJava in your Android app projects, it’d be helpful to pay attention to such tools such as SQLDelight and SQLBrite. The first one generates code to facilitate reading and writing table data, the other one provides Rx style queries and table data update notifications. In addition, they interact well together in a pair in the way of layers one above the other. The following article aims to show the main aspects of the implementation of these layers and also the restrictions imposed by the use of these libraries.

I strongly recommend pre-installing the SQLDelight plugin, which greatly helps with writing SQL commands and refactoring, due to autocompletion, syntax checks and other useful features. When the filling of the .sq files is completed, SQLDelight generates a bunch of classes and interfaces which allows you to create statements, bind values to those statements in a typesafe way and to map a query cursor to the model. Now I want to give my pros and cons of using this library.

A lightweight wrapper around SQLiteOpenHelper and ContentResolver which introduces reactive stream semantics to queries.

In practice, it means that by passing a query to SQLBrite we get a QueryObservable that emits DatabaseQuery. Immediately after subscribing to QueryObservable, the observer(subscriber) will receive onNext (DatabaseQuery). Also insert, update, or delete operations will trigger onNext call for those observers that are subscribed to the modified tables. It should be noted, there are no calls to the database, until a run():Cursor method of the DatabaseQuery is called. It means that we can use QueryObservable also for getting a data change notification without requesting data from the database or delegate the decision on the execution of the request further along the chain of call. In case when we need to pass the data we can return Cursor or use the methods for mapping QueryObservable, such as mapToOne(), mapToOneOrDefault() and mapToList(). For instance, they can return Observable<Optional>, Observable or Observable<List> respectively. There is some more about it.

At this moment the latest version of SQLBrite works with RxJava 1.2.3. But a few days ago the support RxJava2 feature was merged into master, so I hope in the near future the next version of SQLBrite will be released and there will be no need to use the interoperability library(RxJava to RxJava2) any more.

In order to demonstrate SQLDelight and SQLBrite in action I made a small application in Kotlin. It’s built based on my vision of MVP. Source code on github. The project operates two main entities – Person and Vehicle. The data from the web service is saved to the database and UI, which is given notifications, pulls that data and updates itself. The project has some extra unused methods for showing different approaches.

At the first stage, SQLDelight generates abstract classes PersonModel and VehicleModel based on Person.sq and Vehicle.sq files. Those classes contain everything you need to interact with the related tables.

In my app, I used the models of the heirs of PersonModel and VehicleModel both as domain models as well as models of the presentation layer. The use of such universal models is acceptable in simple applications, however, it is much better practice to create separate models for different layers. Furthermore, in some cases, Cursors are the most rational form to operate with data, despite the obvious disadvantages of this approach. Fortunately, the tools being under consideration do not restrict us in the choice of methods and allow us to do everything that we can do using the original approach of working with SQLite through SQLiteDatabase.

In Java with all its getters, equals, hashCode, toString to create VehicleModel inheritor I would use AutoValue, as it’s shown in SQLDelight docs on github. AutoValue makes it easy to refactor. After changing fields in yours sq. file, it will change all POJO’s members accordingly to the updated VehicleModel. I strongly recommend using AutoValue, since it saves from annoying mistakes, for instance, we can easily forget to add a new field to equals method in case of doing it manually. Kotlin has data class, so we don’t have to use AutoValue. As you can see the implementation of Vehicle class without AutoValue has some redundant getters, but they can be made private and expose only VehicleModel public methods.

The implementation of all models and the auxiliary classes they need is completed. All that’s left is to add the CREATE TABLE and the migration statements to the successor of SQLiteOpenHelper. At this point working with SQLDelight should be considered as completed and we got the insert, update, delete statements constructors and the mappers to map queries cursors to the models. The next step is implementing DAO interface with SQLBrite.

DaoImpl contains several queries and insert, update, delete methods which show most approaches to interact with a database via SQLBrite. It’s important to notice, every call of almost all of insert, update, delete methods trigger QueryObservable emits next object. So it’s easy to flood Observers during execution of a batch of methods calls. For example, we have a query ‘select_vehicle_plate_number_and_owner_name’, which gets the data from two tables, and an Observer, which is subscribed to that query. Let’s imagine we call 10 inserts into the first table and 2 inserts into the second through SQLBrite. In that case the Observer would get 12 onNext calls. Usually, we want to get only one onNext call after all insertions to be done. There are a few techniques how to do it. The first way is to execute all operations within a single transaction, as it is shown in the example data.storage.DaoImpl.saveOwnersVehiclesWithinSingleTransaction. In that case every of the Observers receives only one onNext even if the operations of the transaction modify more than one table. In the example a presentation.vehicleList.VehicleListPresenter.subscribeForSingleTransactionTest() subscribes three Observers, the first is subscribed to “vehicle” and “person” tables data, the second one to a “vehicle” data and the third one to a “person” data. After call of saveOwnersVehiclesWithinSingleTransaction method and writing the data to both tables each of the three Observers receive one onNext which confirms the correctness of the mechanism of data update notifications.

Of course, it is not always possible to perform everything in one transaction, for those cases we may do insert, update and delete without triggering of notifications. The BriteDatabase.execute() method doesn’t trigger notifications, also we might pass an empty set of tables argument to executeInsert (but not insert()), executeAndTrigger, executeUpdateDelete. Or using getWritableDatabase and working with it directly. And when all the operations are done, we will trigger the notification. It could be done via BriteDatabase.sendTableTrigger, but it is private, so we have to use some workaround, e.g. the one with combineLast, that was proposed by Jake Wharton here.

The next step is implementing business logic. My example downloads the data from the webservice mock and saves it to the database through SQLBrite. In my case Presenters are subscribed to this data, so they get notification after saving and update my Views.

My VehicleListPresenter is also subscribed to Observable and Observable, which emit the current state of the data fetching process and all errors of that process. Due to the fact that Observable in the sample is BehaviorSubject, the Presenter receives the last FetchingState value right after subscribing. I use that behavior for showing the fetching state in the progress bar correctly, even after configuration change(screen rotation). In general, I aspire in my Presenters to avoid state storing and move it to the model layer as much as possible. RxJava и SQLBrite help me a lot with that approach.

Nowadays, there are a lot different solutions for data storing, including complex standalone products, ORMs or simple helpers/wrappers for SQLite. All of them, like SQLDelight and SQLBrite, have their strengths and weaknesses, so they should only be there where suited best. SQLDelight and SQLBrite are highly specialized, very lightweight(~160 methods + generated code and widely-used dependencies), but at the same time they are quite useful tools. Built my DAOs on top of SQLDelight, I’m sure that there are only statements written by me and there no suboptimal heavy queries hidden behind the simple API of some library. However, I do not need to work with SQL and tables and columns identifiers in the main project code, furthermore the plugin significantly speeds up SQL writing. SQLDelight does not restrict my work with SQLite database and makes it possible to read data from the Cursor when and where it is needed. SQLBrite, in its turn, is quite a specific tool, in my opinion, it is only necessary when SQLite and RxJava are already used in the project or are planned to be used. Otherwise importing of RxJava library only for operation with a DB is irrational. The essential advantage of them, in contrast to the Realm, is that these libraries do not spread throughout the project, but exist in one layer and can be easily integrated both and replaced with something else in the future.
To sum up, they are an excellent addition to the android developer toolkit.