Android anko SQLite

Stress-free SQLite with Anko

This post is about Anko SQLite, a library promising to simplify working with SQLite on Android. Persistent storage is a recurring theme in mobile development, a huge percentage of apps rely on it. The database itself varies, but developers hardly ever choose the default SQLite implementation, they rather switch to something that's easier to use, even if that means including a robust library.

The issues with SQLite

When it comes to SQLite on Android, developers have to deal with the following:

  • tons and tons (and tons) of boilerplate;
  • mapping Cursors and ContentValues;
  • String based SQL commands - error prone, no compile-time verification;
  • closing the database each and every time;
  • threads and synchronized access;
  • managing changes in models.

Each of these issues get more and more severe as the database evolves into a more and more elaborate structure. So no wonder that people choose ORMs (wrappers around SQL) or NoSQL databases instead. But convenience comes at a price of increased size and method count, and often a strict way of structuring model classes. Not to mention conscience: one can feel remorse when pulling in complex and huge dependencies just to store instances of a simple class or two.

Enter Anko SQLite

Anko SQLite is a wrapper around the built-in SQLite APIs, that promises to eliminate a lot of boilerplate code by introducing convenience functions, and saying goodbye to Cursors and ContentValues. It has a built-in safety mechanism in the use block, guaranteeing the database will be closed after executing all code inside.

Basics

After including the library (implementation "org.jetbrains.anko:anko-sqlite:$anko_version"), the first step is to create the database helper class. This needs to extend ManagedSQLiteOpenHelper, which is the substitute of the built-in SQLiteOpenHelper, that is concurrency-aware, and takes care of closing the database after using it. The recommended way of doing this, based on the official documentation:

class DatabaseHelper(ctx: Context) : ManagedSQLiteOpenHelper(ctx, "LibraryDatabase", null, 1) {
  companion object {
    private var instance: DatabaseHelper? = null

    @Synchronized
    fun Instance(context: Context): DatabaseHelper {
      if (instance == null) {
        instance = DatabaseHelper(context.applicationContext)
      }
      return instance!!
    }
  }

  override fun onCreate(database: SQLiteDatabase) {
    createTable(Book.TABLE_NAME, true, Book.COLUMN_ID to INTEGER + PRIMARY_KEY, Book.COLUMN_TITLE to TEXT, Book.COLUMN_AUTHOR to TEXT)
  }

  override fun onUpgrade(database: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
    dropTable(Book.TABLE_NAME, true)
  }
}

Accessing the database depends on the use case, a good solution can be dependency injection, or adding an extension to Context:

val Context.database: DatabaseHelper
  get() = DatabaseHelper.Instance(applicationContext)

And this is how a simple model class looks like:

data class Book(val id: Int, val title: String, val author: String) {
  companion object {
    val Book.COLUMN_ID = "id"
    val TABLE_NAME = "books"
    val COLUMN_TITLE = "title"
    val COLUMN_AUTHOR = "author"
  }
}

With the database up and running, it can accessed via the use function:

database.use {
    // code
}

The database will be closed after the code block executes, if there's no other active use block. Also, the function inside is an extension of SQLDatabase, so those methods can be accessed as well. But why work with those, if we have Anko's extension functions?

Inserting a row is super easy:

database.use {
    insert(Book.TABLE_NAME, Book.COLUMN_ID to 1, Book.COLUMN_TITLE to "2666", Book.COLUMN_AUTHOR to "Roberto Bolano")
}

A good practice is to adding the operations to the respective model classes as extension functions, thus encapsulating DAO-functionality (Book.selectAll(database)).

Queries requires a bit more code. Select queries follow a builder-like, fluent pattern, by first specifying the table, then the statements (where, limit, etc - these are optional), and then a parser. The latter is responsible for converting the columns (formerly a Cursor object) into objects. It can be used with constructors, so in our example, classParser<Book>() works automatically. But it can be overridden as well, if you want to do more than mapping columns to variables. This is how select * from Books looks like:

val books = database.use {
    select(Book.TABLE_NAME).exec { parseList(classParser()) }
}

As per statements, the following are supported:

  • column
  • distinct
  • where
  • orderBy
  • groupBy
  • limit
  • having

This is how a select looks like with a where statement:

select(Book.TABLE_NAME)
    .whereArgs(Book.COLUMN_AUTHOR + " = {author}", "author" to author)
    .exec { parseList(classParser()) }

Last, but not least, since database operations shouldn't run on the UI thread, every call should be wrapped in Rx.

Apk size impact

The following table contains the method count and size impliactions of some of the most common persistence libraries (based on METHODS COUNT).

library method count DEX size method count of dependencies DEX size of dependencies
Anko SQLite 0.10.1 437 90KB 7304 1298KB
Room 1.0.0-alpha9-1* 328 47KB 6580 760KB
GreenDAO 3.2.0 1169 142KB 27 5KB
ORMLite Android 5.0 598 74KB 2342 313KB
CouchBase Lite 1.4.0 203 24KB 17097 2357KB
Realm 3.7.2 3873 501KB 3048 443KB

*without the RxJava component

The Anko library has a huge dependency size, but that's a bit deceiving, since it includes the Kotlin runtime (method count: 6298, DEX size: 1117 KB) and the Anko Commons module (method count: 982, DEX size: 174 KB) as well. These are already added as dependencies (at least the former is), independent from the SQLite wrapper. The conclusion here is that if you already code in Kotlin, the SQLite module itself doesn't have a big impact in terms of method count and size.

Bottom line

Using the Anko SQLite wrapper is far easier than dealing with pure SQLite, it is much smaller in size than the ORM/NoSQL libs, and does not require you to build your model classes in a certain way.

I'd recommend using Anko SQLite for simple database structures, where it would only took a couple of lines of code to create a quick and reliable database, without including heavy dependencies. However, mappers, DAOs, and SQL queries can get painful when the database structure gets more complex - this is when ORMs and NoSQL solutions are preferred.

This post is the third in a series about Anko - the first one covered the Commons module, and the second was about the layout DSL.