Kotlin — Anko & the SQLite

In the previous post we went through a layout DSL from Anko that allows dynamic UI building entirely from code. Today we are going to check some great SQLite extensions that will make accessing a SQLite database look like one of the simplest things in our daily work.
I personally prefer Realm over SQLite for speed, API and it’s core written in C++, but i know many projects rely on SQLite, and we are covering all the good things in Anko, so let’s be good with it.

In the regular way, to open a SQLite database we need to subclass SQLiteOpenHelper, in Anko we need to subclass ManagedSQLiteOpenHelper and override some methods:

class MyDatabaseOpenHelper(ctx: Context) : ManagedSQLiteOpenHelper(ctx, “MyDatabase”, null, 1) {
    companion object {
        private var instance: MyDatabaseOpenHelper? = null
        @Synchronized
        fun getInstance(ctx: Context): MyDatabaseOpenHelper {
            if (instance == null) {
                instance =             
                   MyDatabaseOpenHelper(ctx.getApplicationContext())
            }
            return instance!!
        }
    }
    override fun onCreate(db: SQLiteDatabase) {
        // Here you create tables
        db.createTable(“User”, true, 
        “id” to INTEGER + PRIMARY_KEY + UNIQUE,
        “name” to TEXT,
        “email” to BLOB)
    }
    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int,    
                                           newVersion: Int) {
        // Here you can upgrade tables, as usual
        db.dropTable(“User”, true)
    }
}
// Access property for Context
val Context.database: MyDatabaseOpenHelper
 get() = MyDatabaseOpenHelper.getInstance(getApplicationContext())

The access property created makes it easy to use the database:

database.use {
    //’this’ refers to the SQLiteDatabase instance 
}

This is similar to:

val db = database.writableDatabase

But this last example requires running the commands and calling close() when finished.

In the previous snippet you see some details about how to create and drop tables:

db.createTable(“User”, true, 
 “id” to INTEGER + PRIMARY_KEY + UNIQUE,
 “name” to TEXT,
 “email” to BLOB)
///
db.dropTable(“User”, true)

Following we should see how to insert, update and delete:

database.use {
    insert(“User”,
    “id” to 42,
    “name” to “John”,
    “email” to “user@domain.org”)
 
    update(“User”, “name” to “Alice”)
    .where(“id = {userId}”, “userId” to 42)
    .exec()
    //or
    update(“User”, “name” to “Alice”)
    .`whereSimple`(“id = ?”, 42)
    .exec()
    val numRowsDeleted = delete(“User”, 
    “id = {userID}”, 
    “userID” to 42)
}

We could use a transaction for all these operations:

database.use {
    transaction {
        insert(“User”,
        “id” to 42,
        “name” to “John”,
        “email” to “user@domain.org”)
 
        update(“User”, “name” to “Alice”)
        .where(“id = {userId}”, “userId” to 42)
        .exec()
        //or
        update(“User”, “name” to “Alice”)
        .`whereSimple`(“id = ?”, 42)
        .exec()
        val numRowsDeleted = delete(“User”, 
        “id = {userID}”, 
        “userID” to 42)
    }
}

There are also replace() insertOrThrow() replaceOrThrow() methods that can be used accordingly.

Now we should know how to query data right?:

val db = database.readableDatabase
db.select(“User”, “email”).exec {
 //you receive a Cursor with the email column in the block
}
db.select(“User”).`whereSimple`(“id = ?”, 42).exec {
 //you receive a Cursor with the User columns in the block
}

There are more options for the query builder, also there’s a parsing helper that maps the cursor to an array or map.

This was the last post about Anko, i hope you found this helpful and encouraging to use these libraries.