Databases on Android with Anko and Kotlin: SQLite never was so easy (KAD 25)

Truth to be told: writing databases in Android is quite boring.

All the boilerplate required to work with SQLite is far from being the most delightful thing in the world.

Fortunately, there are already enough libraries that simplify this task, one of them announced during the latest Google I/O, called Room.

With Anko, however, we can continue to work as low-level as when we use the framework, but get rid of some of the more tedious parts of its implementation. Today we’ll see how.

If you want to start today, I recommend you take a look at my free training, where you will have an hour and a half of content to know what are your next steps to become an expert in Kotlin.

Create your database with Anko

You’ll see here how to create your database from scratch. On Android, you’ll need a SQLiteOpenHelper, which you use to retrieve the database. You’ll then need to remember to close after the requests. Anko does this for you.

For this you need to include SQLite dependency for Anko:

compile 'org.jetbrains.anko:anko-sqlite:0.10.0'

Implement ManagedSQLiteOpenHelper

If you use this class, which extends SQLiteOpenHelper, you can create blocks of code where you will execute the database operations, as follows:

database.use { 
    ...
}

The content inside the braces is an extension function on the SQLiteDatabase class, so you can call its methods directly. With the added point that the table will be open just before and closed right after executing this block.

How do we implement this class? Based on Anko’s documentation, the recommended way is:

class MySqlHelper(ctx: Context) : ManagedSQLiteOpenHelper(ctx, "mydb") {

    companion object {
        private var instance: MySqlHelper? = null

        @Synchronized
        fun getInstance(ctx: Context): MySqlHelper {
            if (instance == null) {
                instance = MySqlHelper(ctx.applicationContext)
            }
            return instance!!
        }
    }

    override fun onCreate(db: SQLiteDatabase) {
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
    }

}

// Access property for Context
val Context.database: MySqlHelper
    get() = MySqlHelper.getInstance(applicationContext)

We create a small singleton that will save the instance of the helper, in a synchronized way so that different threads cannot generate more than one instance.

In addition, we create an extension property for the context, so that any class that requires Context can access the database directly.

This allows us to make use of the initial code.

Define the table structure

To create our database, all we have to do is implement the onCreate method of the helper, and use the createTable extension function provided by Anko:

override fun onCreate(db: SQLiteDatabase) {
    db.createTable("Person", true,
            "_id" to INTEGER + PRIMARY_KEY,
            "name" to TEXT,
            "surname" to TEXT,
            "age" to INTEGER)
}

The first parameter is the name of the database, and the second parameter checks that the table doesn’t exist before doing the create.

The third is a vararg pair. That is, you can add as many as you like. As you can see, the pairs are created with the form A to B. This is called infix function, and is specified with the reserved word infix.

The second part of the pairs are constants of the SqlType class. I recommend that you take a look at the implementation, because it’s very interesting. Here it makes a clever use of the operator overloading.

Insert and query data

Everything becomes much easier with Anko. Instead of having to create your own ContentValue to add all the data during insertion, you can use an extension function on the database object, which is such that:

database.use {
    insert("Person",
            "_id" to 1,
            "name" to "John",
            "surname" to "Smith",
            "age" to 20)
}

For queries, there are several ways to do them. In one of them you can add names to the variables of the query and include the values as pairs:

select("Person")
        .where("(_id = {id}) and (name = {name})",
                "id" to 1,
                "name" to "John")

And there is another, more similar to that used in the Android framework, which allows to include interrogation sign and values more later. All of them must be String in this case:

select("Person")
        .whereSimple("(_id = ?) and (name = ?)",
                1.toString(), "John")

Personally in this case I find the second option simpler.

You can also apply all regular database operations such as limit, orderBy, having or groupBy. You can see all the operations in Anko documentation.

To deal with the resulting cursor, Anko also provides us with different functions, such as parseSingle (for one result) or parseList (for several). These functions receive a rowParser.

There are a lot of different parsers. One interesting is the MapRowParser, which maps the columns to a map.

With this parser and map delegate, you can parse directly the values from a map to a class. You can see this explained in my book.

Conclusion

While it’s true that there are libraries that simplify much more the work with databases, for simple things Anko can be more than enough.

It saves us many of the things that make working with databases really painful, so it’s a good alternative for simple databases.

In addition, it shows us other ways to use language that can be very useful when you are facing other problems.

Don’t forget to take a look at the previous articles to learn more about Kotlin!

If you like what you’ve seen, I encourage you to sign up for my free training, where I’ll tell you everything you need to learn about how to create your own Android Apps in Kotlin from scratch.