Have you ever been tired of parsing SQLite query results using Android cursors? You have to write lots of boilerplate code just to parse query result rows, and enclose it in countlesstry..finally blocks to properly close all opened resources.

Anko provides lots of extension functions to simplify working with SQLite databases.

• Using Anko SQLite in your project
• Accessing database
• Creating and dropping tables
• Inserting data
• Querying data
• Deleting data
• Parsing query results
• Custom row parsers
• Cursor streams
• Updating values
• Transactions

Add theanko-sqlite dependency to yourbuild.gradle:

dependencies {    implementation"org.jetbrains.anko:anko-sqlite:$anko_version"}

If you useSQLiteOpenHelper, you generally callgetReadableDatabase() orgetWritableDatabase() (result is actually the same in production code), but then you must be sure to call theclose() method on the receivedSQLiteDatabase. Also, you have to cache the helper class somewhere, and if you use it from several threads, you must be aware of the concurrent access. All this is pretty tough. That is why Android developers are not really keen on default SQLite API and prefer to use fairly expensive wrappers such as ORMs instead.

Anko provides a special classManagedSQLiteOpenHelper that seamlessly replaces the default one. Here's how you can use it:

classMyDatabaseOpenHelper private constructor(ctx:Context) : ManagedSQLiteOpenHelper(ctx,"MyDatabase",null,1) {init {        instance=this    }companionobject {privatevar instance:MyDatabaseOpenHelper?=null        @SynchronizedfungetInstance(ctx:Context)= instance?:MyDatabaseOpenHelper(ctx.applicationContext)    }overridefunonCreate(db:SQLiteDatabase) {// Here you create tables        db.createTable("Customer",true,"id" toINTEGER+PRIMARY_KEY+UNIQUE,"name" toTEXT,"photo" toBLOB)    }overridefunonUpgrade(db:SQLiteDatabase,oldVersion:Int,newVersion:Int) {// Here you can upgrade tables, as usual        db.dropTable("User",true)    }}// Access property for ContextvalContext.database:MyDatabaseOpenHelper    get()=MyDatabaseOpenHelper.getInstance(this)

So what's the sense? Instead of enclosing your code intotry blocks, now you can just write this:

database.use {// `this` is a SQLiteDatabase instance}

The database will definitely be closed after executing all code inside{}.

Asynchronous call example:

classSomeActivity :Activity() {privatefunloadAsync() {        async(UI) {val result= bg {                 database.use {... }            }            loadComplete(result)        }    }}

With Anko you can easily create new tables and drop existing ones. The syntax is straightforward.

database.use {    createTable("Customer",true,"id" toINTEGER+PRIMARY_KEY+UNIQUE,"name" toTEXT,"photo" toBLOB)}

In SQLite, there are five main types:NULL,INTEGER,REAL,TEXT andBLOB. But each column may have some modifiers likePRIMARY KEY orUNIQUE. You can append such modifiers with "adding" them to the primary type name.

To drop a table, use thedropTable function:

dropTable("User",true)

Usually, you need aContentValues instance to insert a row into the table. Here is an example:

val values=ContentValues()values.put("id",5)values.put("name","John Smith")values.put("email","user@domain.org")db.insert("User",null, values)

Anko lets you eliminate such ceremonies by passing values directly as arguments for theinsert() function:

// Where db is an SQLiteDatabase// eg: val db = database.writeableDatabasedb.insert("User","id" to42,"name" to"John","email" to"user@domain.org")

or from withindatabase.use as:

database.use {    insert("User","id" to42,"name" to"John","email" to"user@domain.org"   )}

Please note, in the above exampledatabase is a database helper instance, anddb is aSQLiteDatabase object

FunctionsinsertOrThrow(),replace(),replaceOrThrow() also exist and have the similar semantics.

Anko provides a convenient query builder. It may be created withdb.select(tableName, vararg columns) wheredb is an instance ofSQLiteDatabase.

Functions marked with ⭐ parse its arguments in a special way. They allow you to provide values in any order and support escaping seamlessly.

db.select("User","name")    .whereArgs("(_id > {userId}) and (name = {userName})","userName" to"John","userId" to42)

Here,{userId} part will be replaced with42 and{userName} — with'John'. The value will be escaped if its type is not numeric (Int,Float etc.) orBoolean. For any other types,toString() representation will be used.

whereSimple function accepts arguments ofString type. It works the same asquery() fromSQLiteDatabase (question marks? will be replaced with actual values from arguments).

How can we execute the query? Using theexec() function. It accepts an extension function with the type ofCursor.() -> T. It simply launches the received extension function and then closesCursor so you don't need to do it by yourself:

db.select("User","email").exec {// Doing some stuff with emails}

So we have someCursor, and how can we parse it into regular classes? Anko provides functionsparseSingle,parseOpt andparseList to do it much more easily.

Note thatparseSingle() andparseOpt() will throw an exception if the received Cursor contains more than one row.

Now the question is: what isrowParser? Well, each function supports two different types of parsers:RowParser andMapRowParser:

interfaceRowParser<T> {funparseRow(columns:Array<Any>):T}interfaceMapRowParser<T> {funparseRow(columns:Map<String,Any>):T}

If you want to write your query in a very efficient way, use RowParser (but then you must know the index of each column).parseRow accepts a list ofAny (the type ofAny could practically be nothing butLong,Double,String orByteArray).MapRowParser, on the other hand, lets you get row values by using column names.

Anko already has parsers for simple single-column rows:

• ShortParser
• IntParser
• LongParser
• FloatParser
• DoubleParser
• StringParser
• BlobParser

Also, you can create a row parser from the class constructor. Assuming you have a class:

data classPerson(valfirstName:String,vallastName:String,valage:Int)

The parser will be as simple as:

val rowParser= classParser<Person>()

For now, Ankodoes not support creating such parsers if the primary constructor has optional parameters. Also, note that constructor will be invoked using Java Reflection so writing a customRowParser is more reasonable for huge data sets.

If you are using Ankodb.select() builder, you can directly callparseSingle,parseOpt orparseList on it and pass an appropriate parser.

For instance, let's make a new parser for columns(Int, String, String). The most naive way to do so is:

classMyRowParser :RowParser<Triple<Int,String,String>> {overridefunparseRow(columns:Array<Any>):Triple<Int,String,String> {returnTriple(columns[0]asInt, columns[1]asString, columns[2]asString)    }}

Well, now we have three explicit casts in our code. Let's get rid of them by using therowParser function:

val parser= rowParser { id:Int, name:String, email:String->Triple(id, name, email)}

And that's it!rowParser makes all casts under the hood and you can name the lambda parameters as you want.

Anko provides a way to access SQLiteCursor in a functional way. Just callcursor.asSequence() orcursor.asMapSequence() extension functions to get a sequence of rows. Do not forget to close theCursor :)

Let's give a new name to one of our users:

update("User","name" to"Alice")    .where("_id = {userId}","userId" to42)    .exec()

Update also has awhereSimple() method in case you want to provide the query in a traditional way:

update("User","name" to"Alice")    .`whereSimple`("_id = ?",42)    .exec()

Let's delete a row (notice the delete method doesn't have anywhereSimple() method; instead you supply the query directly in the arguments):

val numRowsDeleted= delete("User","_id = {userID}","userID" to37)

There is a special function calledtransaction() which allows you to enclose several database operations in a single SQLite transaction.

transaction {// Your transaction code}

The transaction will be marked as successful if no exception was thrown inside the{} block.