databases-anko-kotlin

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 can not 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 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.

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

  1. @Antonio Leiva Can you please send me the sample code of anko sqlite andriod with some dummy data. Actually I am getting error that table has been created.

  2. Where can I get detailed documentation on this?

    I came from php and there we linked the input data dynamically to the table data and then the persistence in the database.
    Here in kotlin everything seems to be done in the hand.
    Quite tiring.
    ;(

    1. There are more powerful libraries, such as Room, that make dealing with databases a little less frustrating. But yeah, it’s one of the pain points of Android development.

  3. Great tutorial, thank you.
    In MySqlHelper inside fun onCreate I want insert few initial rows (using data from R.string). To do that I need use getString(R.string.info) and getString need context. When I create variable mContext: Context = ctx
    Is working but Android Studio show warning about memory leak in static field.
    How Can I change code to have possibility using getString() inside MySqlHelper without memory leak?
    More info:
    https://stackoverflow.com/questions/54580662/anko-dbhelper-singleton-companion-context-memory-leak-warning

    Best Regards

    1. You have several options. The first is to always use the application context: “ctx.getApplicationContext()”. That won’t leak (and in fact it’s probably what you’re already doing), but the lint is not so clever, and can still warn. So an alternative is to use a variable of type “Application” instead. That way you make sure that you don’t save a wrong context there, and also that Lint doesn’t detect it as a possible issue.

      1. Thank you for fast answer
        So if I create instance by
        get() = DbHelper.getInstance(applicationContext)
        and there are
        [..]
        instance = DbHelper(ctx.applicationContext)
        [..]
        and use
        private val mContext : Context = ctx
        is ok (no real memory leak, and I can ignore warning?)
        Or I should use?
        private val mContext : Context = ctx.getApplicationContext

        I will try use LeakCanary maybe give me some info.

        Best Regards & big thanks

      2. It’s a good practice not to rely on what we are getting through the constructor (if it’s a context, anyone could make a bad use of it from any part of the App), and do ctx.applicationContext as you mentioned. But if you do it the other way round and make sure that you’re never passing an activity context, then it’d be ok too.

Comments are closed.