SQLite

SQLite (pronounced either "S. Q. Lite" or "sequelite”) is a relational database management system (RDBMS). Most databases are actually "database servers" that require installation and configuration, and work in separate processes that accept network connections. Examples include relational PostgreSQL, MySQL and MariaDB, non-relational Elasticsearch, MongoDB, and more. Unlike these, SQLite is embedded, which means it doesn't spawn a separate process that you can connect to. Instead, it works within your application, avoiding memory and interprocess communication overhead.

You are already familiar with SQL. So, in this topic, you will learn about SQLlite by declaring a schema, executing some DDL (data definition language) statements, and trying out all the CRUD (create, read, update, delete) operations.

DDL

Imagine you want to store people's details. For example, an appropriate Kotlin class can look like this:

class Person(
    val id: Long,
    val name: String,
    val birth: LocalDate,
)

java.time.LocalDate is here to illustrate how you can store anything in a database. It is not necessary to be familiar with java.time in order to understand the topic.

First, we need a table with columns corresponding to Person's properties:

CREATE TABLE people(
    _id INTEGER PRIMARY KEY
  , name TEXT NOT NULL
  , birth INTEGER NOT NULL
)

There's plenty to explain, so let's move on step by step:

• _id is a common convention for the primary key name in Android.

• PRIMARY KEY implies that the column is NOT NULL.

• ID has INTEGER type. SQLite doesn't actually have LONG or VARCHAR, it only supports INTEGER, REAL, TEXT, and BLOB. But INTEGER can take up to 8 bytes (64 bits) when necessary, which is large enough.

• Note that there's no AUTO INCREMENT: we just don't need it in SQLite.

• There are also strange-looking commas at the start of lines. In version control systems (VCS) like Git, every altered line is highlighted when viewing differences between versions, so it's best to avoid altering the previous line while adding another. Unlike Kotlin, SQL doesn't support trailing commas, so here's a workaround.

In Android, there's an abstract class calledSQLiteOpenHelper which enables us to initialize the database. It's handy to extend in order to create tables, execute migrations, and get access to CRUD operations afterward.

class DbHelper(
    context: Context,
) : SQLiteOpenHelper(context, "app.db", null, 1) {

    override fun onConfigure(db: SQLiteDatabase) {
        db.execSQL("PRAGMA foreign_keys = 1")
        db.execSQL("PRAGMA trusted_schema = 0")
    }

    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL("CREATE TABLE …")
    }

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

}

Let's take a look at constructor arguments.

• First, we pass ourContext, which is quite typical for Android. SQLiteOpenHelper uses it to locate our app's private directory for storing the database file.

• app.db is the name of that file. Passing null instead of a file name would result in the creation of an in-memory database.

• The next parameter is CursorFactory, which isn't needed in the vast majority of cases, so we pass null.

• 1 is the version number of our database. In later versions of the application, we could change the database structure and its version number. Doing this would trigger an onUpgrade() invocation, allowing us to alter all the modified tables, create some new ones, or drop any that have become obsolete.

The onConfigure method is called every time when a database connection gets opened and allows us to modify SQLite behavior using PRAGMA statements.

• PRAGMA foreign_keys = 1 enables foreign key checks. These weren't supported in the early versions of SQLite and were left disabled for compatibility reasons.

• trusted_schema is another flag that has a default that isn't optimal for compatibility reasons.

If you are going to use temporary tables, you can also specify that they should be in-memory by executing PRAGMA temp_store = MEMORY.

CRUD

Now that the schema is defined, it's time to store some data and retrieve it.

To initialize our Helper, we call getWritableDatabase to acquire an SQLiteDatabase instance, and we're ready to go: val db = DbHelper(context).writableDatabase.

Calling getWritableDatabase() for the first time triggers Helper to go through the configure-and-create-or-upgrade lifecycle. This can take a long time, so it's best to make the call from a background thread.

To encapsulate all the database logic, let's create a PersonStore class:

class PersonStore(private val db: SQLiteDatabase) : Closeable {


    override fun close() {
        // TODO
    }
}

Implementing the Closeable interface signals to the user of this class that its instances must be disposed of after use. That's because we're going to ask SQLiteDatabase to construct several Closeable objects and close them within our own close() method. SQLite is written in C, so we need to manage memory manually.

Create

Below, you can see how INSERT looks:

class PersonStore(private val db: SQLiteDatabase) : Closeable {
    private val insert = db.compileStatement(
        "INSERT INTO people (name, birth) VALUES (?, ?)"
    )

    fun insert(name: String, birth: LocalDate): Long {
        insert.bindString(1, name)
        insert.bindLong(2, birth.toEpochDay())
        return insert.executeInsert()
    }

    override fun close() {
        insert.close()
    }
}

compileStatement returns a prepared statement that's useful when you're going to execute the same SQL statement multiple times during the application lifetime. Executing this kind of statement is generally faster than executing a statement directly.

The family of functions beginning with bind* are used for setting values for positional placeholders (?). Note that indices are 1-based.

Accepting a Person instance is a bad idea because it doesn't have an ID until it is inserted. Instead, the insert() function takes name and birth and returns the primary key of the newly inserted Person.

Read

Now we're ready to query the database for a single Person:

private val personCols = arrayOf("_id", "name", "birth")
…
class PersonStore(private val db: SQLiteDatabase) : Closeable {
    …
    fun singleOrNull(id: Long): Person? {
        val cursor = db.query(
            "people", personCols, "_id = ?", arrayOf(id.toString()),
            null, null, null
        )
        return try {
            if (cursor.moveToFirst()) Person(cursor)
            else null // not found
        } finally {
            cursor.close()
        }
    }
    private fun Person(cur: Cursor) =
        Person(
            cur.getLong(0),
            cur.getString(1),
            LocalDate.ofEpochDay(cur.getLong(2)),
        )

Let's peek into the details of this code:

• We don't compileStatement here. Compiled statements can be used with INSERT, UPDATE, DELETE, and a special SELECT case: to read a single value when the selection has exactly one column and one row (also known as "single cell" or "1×1 grid"). Compiled statements cannot be used with a general-purpose SELECT.

• The query() function is a simple SQL query builder:

  public Cursor query(String table, String[] columns, String selection,
          String[] selectionArgs, String groupBy, String having,
          String orderBy)
  

  We just pass nulls for groupBy, having, and orderBy because we don't need them for this example.

  You are free to use rawQuery() and pass your SQL query there if you need to make some joins or just to save some memory used by query() for StringBuilders.

• Cursor is a gateway to query results. You can move it along selected rows and read cell (a particular column in a specific row) values with Cursor.getLong, Cursor.getString, and a bunch of similar methods. Unlike 1-based bind*, get* methods are 0-based. If you're familiar with JDBC, you can think of Cursor as a ResultSet. If not, just be happy you haven't needed to use such an awkward API.

• Cursor is Closeable. That's why we use try…finally construct.

• moveToFirst() checks whether we can read the first row. If not, the selection is empty.

• db.query("people", null, …) would effectively mean SELECT *. The column set wouldn't be known at this point, but you can still use cursor.getLong(cursor.getColumnIndexOrThrow("_id")), and so on.

At this point, we're able to insert a Person record and select it. But in most cases, we need lists of entities, so let's create a Cursor of all the Person records in the database and wrap it in a List. A list of this kind requires a live Cursor to work properly, and we must close it after use, so we need an additional type: interface CloseableList<E> : List<E>, RandomAccess, Closeable:

    fun all(): CloseableList<Person> {
        val cursor = db.query(
            "people", personCols, null, null, null, null, null
        )
        return object : AbstractList<Person>(), CloseableList<Person> {

            override val size: Int
                get() = cursor.count

            override fun get(index: Int): Person {
                check(cursor.moveToPosition(index))
                return Person(cursor)
            }

            override fun close() {
                cursor.close()
            }
        }
    }

Using the above approach will give us a normal list that can be passed to Adapter or utilized in any other common way. Just don't forget to close it in onDestroy() or another lifecycle method, symmetrically to creation.

check() is used to assert that moveToPosition() returned true. Otherwise, the operation will fail immediately because false is totally unexpected here.

This approach will work as anticipated but has at least two flaws:

• Subsequent calls to get(n) will still rewind the Cursor to position n and allocate another Person.

• When the table is long enough, the Cursor will load fairly large chunks of data, which could lead to periodic freezes.

To address these issues, we can select all IDs at once, query Person records one by one (select by primary key is super fast, especially in SQLite), and memoize every Person we fetch:

private val idCol = arrayOf("_id")

class PersonStore(private val db: SQLiteDatabase) : Closeable {
    …
    fun all(): List<Person> {
        val cursor = db.query(
            "people", idCol, null, null, null, null, null
        )
        val ids = try {
            LongArray(cursor.count) { _ ->
                check(cursor.moveToNext())
                cursor.getLong(0)
            }
        } finally {
            cursor.close()
        }
        return object : AbstractList<Person>() {

            override val size: Int
                get() = ids.size

            private val memo = arrayOfNulls<Person>(size)
            override fun get(index: Int): Person =
                memo[index]
                    ?: singleOrNull(ids[index])!!.also { memo[index] = it }
        }
    }

As a bonus, we don't need to close the list separately. It dies at the same time as the whole PersonStore.

Update, Delete

Executing an UPDATE or DELETE statement is as easy as executing INSERT. Instead of SQLiteStatement.executeInsert() you call executeUpdateDelete(), which returns the number of altered rows. Or, you can simply use execute() if you don't mind the altered row count:

    …

    private val update = db.compileStatement(
        "UPDATE people SET name = ?, birth = ? WHERE _id = ?"
    )
    
    fun update(person: Person) {
        update.bindString(1, person.name)
        update.bindLong(2, person.birth.toEpochDay())
        update.bindLong(3, person.id)
        check(update.executeUpdateDelete() == 1)
    }

    private val delete = db.compileStatement(
        "DELETE FROM people WHERE _id = ?"
    )
    
    fun delete(person: Person) {
        delete.bindLong(1, person.id)
        check(update.executeUpdateDelete() == 1)
    }

    …

    override fun close() {
        insert.close()
        update.close()
        delete.close()
    }

Don't forget to close the database and all the statements after use. Note that PersonStore.close() only frees statements because it isn't an owner of an SQLiteDatabase, so this is still your responsibility.

Also, having a database instance requires a lot of memory, so it's good practice to have only a single SQLiteDatabase instance throughout the application. Subsequent calls to SQLiteOpenHelper.getWritableDatabase() return the same SQLiteDatabase instance, so you should instantiate your Helper only once.

Conclusion

Now you know the basics of the Android SQLite API. You can initialize a database using DDL and perform CRUD operations. You are also aware that most mutations can be done via compiled statements, but the majority of queries cannot.

In addition to this, now you know how to present SQL query results as a List with lazy fetching and memoization.