Introduction

As with networking, working with a database is an equally frequent need when developing android applications. In part, the two areas are intertwined because the database usually stores data that is retrieved from the network and now needs to be cached to reduce the number of requests to the network. When working with a simple table with a few dozen rows, you won't have any problems.

However, in my practice, there was a case where you had to load dozens of databases with hundreds of tables, some of which contained about a million rows. I've also had cases where tables were linked via nested SELECT with nesting levels as high as 5-10. To solve all these problems, I had to use all the libraries and tools listed below to get the desired result or to fix bugs. Because one tool sometimes didn't allow me to do what the other one allowed me to do.

Observations and Assumptions

First, I want to draw your attention to some comments and assumptions:

• This article will look at specific examples whose code can be inserted into Android Studio, run and see what happens;
• This article will focus on working with relational databases. We will not consider non-relational (NoSQL) databases;
• We will consider the database sqlite;
• We will go from simple things, gradually increasing in complexity;
• The code will not be broken into layers according to MVP and MVVM patterns. This is done to simplify the examples and the article as a whole;
• The article is designed for developers with medium level and above, so we won't dwell on very simple things, for the same reason we won't go deep into each individual example or library.

Only an overview of the tools to broaden your horizons is provided.

Selecting an Example

To review the libraries and tools below, let's choose an example. Let's say we have this table with two columns: id and color. Let's try to write this table to the database in different ways and get this result:

Tools and Libraries for Working With the SQLite Database

SQLiteOpenHelper

Let's take a look at the first native tool SQLiteOpenHelper for working with a database on Android. Our task is to write data to the database on the device.

You don't need to pull in any libraries. Create an Activity, insert the following code and run it.

Don't forget to set it to the background thread as shown here:

package com.leonidivankin.draftsandroid.articles.db

import android.content.ContentValues
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.os.Bundle
import android.util.Log
import androidx.appcompat.app.AppCompatActivity

class SQLiteOpenHelperActivity : AppCompatActivity() {

    private val db by lazy {
        object : SQLiteOpenHelper(this, "colors_db", null, 1) {
            override fun onCreate(db: SQLiteDatabase) {
                db.execSQL("CREATE TABLE $TABLE_NAME (_id INTEGER PRIMARY KEY AUTOINCREMENT, color TEXT)")
            }
            override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {}
        }.readableDatabase
    }

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        Thread{
            insert( "green")
            insert( "red")
            insert( "blue")
            get()
        }.start()
    }

    private fun insert(color: String) {
        val contentValues = ContentValues()
        contentValues.put(COLUMN_NAME, color)
        db.insert(TABLE_NAME, null, contentValues)
    }

    private fun get() {
        val cursor = db.query(TABLE_NAME, arrayOf("_id", COLUMN_NAME), null, null, null, null, null)
        var i = 0
        while (!cursor.isLast) {
            cursor.moveToPosition(i)
            val id = cursor.getInt(0)
            val color = cursor.getString(1)
            Log.d("DbExample", "$id $color")
            i++
        }
    }

    override fun onDestroy() {
        super.onDestroy()
        db.close()
    }

    companion object {
        const val TABLE_NAME = "colors"
        const val COLUMN_NAME = "color"
    }
}

We created a colors_db database.

The output log shows that we achieved our goal:

D: 1 green
D: 2 red
D: 3 blue

As you can see, creating the table, filling it with data, and outputting it took no more than 50 lines of code.

Pros and cons

• +no additional libraries are needed
• -it is hard to work with complex queries and selections
• -it is necessary to map to ContentValues, Cursor, and back each time

Useful Links: https://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper https://www.sqlite.org/index.html

It doesn't handle errors, methods to change the database version, etc. in order to reduce the code.

We will need an example with this table next.

Room

Now make the same table with the help of Room from Google, which is part of Android Jetpack. In fact, Room is an ORM, i.e. a shell with a more user-friendly interface.

Adding a dependency on libraries:

implementation 'androidx.room:room-runtime:2.1.0'
kapt 'androidx.room:room-compiler:2.1.0'

Add a dependency to the kapt build.gradle module level file: apply plugin: 'kotlin-kapt'

Create an Activity, insert the code and run it as shown here:

package com.leonidivankin.draftsandroid.articles.db

import android.os.Bundle
import android.util.Log
import androidx.appcompat.app.AppCompatActivity
import androidx.room.*
import androidx.room.OnConflictStrategy.REPLACE

class RoomActivity : AppCompatActivity() {

    private val colorDao by lazy {
        Room
            .databaseBuilder(applicationContext, AppDatabase::class.java, "colors_room")
            .build()
            .colorDao()
    }

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        Thread {
            val list = listOf(Color(1, "green"), Color(2, "red"), Color(3, "blue"))
            colorDao.insert(list)
            Log.d("DbExample", "${colorDao.get()}")
        }.start()
    }
}

@Database(entities = [Color::class], version = 1, exportSchema = false)
abstract class AppDatabase : RoomDatabase() {
    abstract fun colorDao(): ColorDao
}

@Entity(tableName = "colors")
data class Color(@PrimaryKey val id: Int, val color: String)

@Dao
interface ColorDao {
    @Query("SELECT * FROM colors") fun get(): List<Color>
    @Insert(onConflict = REPLACE) fun insert(color: List<Color>)
}

We created the colors_room base.

Here we also reached our goal rather quickly, as confirmed by the log:

D: [Color(id=1, color=green), Color(id=2, color=red), Color(id=3, color=blue)]

Pros and cons

• +simplifies the creation of complex queries

• +can give objects to RxJava, LiveData, Flow

• +works with objects, not strings

• +can give a list of objects at once

• +it has automigrations

• -like any library it makes the project heavier

Useful Links:

https://developer.android.com/jetpack/androidx/releases/room

We achieved the initially set task in 30-40 lines. As you know, Room is currently the most popular library for working with databases on Android due to its simplicity and speed.

Other ORMs

In fact, the Room library is an ORM. Its main task is to simplify the work with the SQLite database. On Android, there were previously popular other ORMs for working with databases, such as ORMLite, SugarORM, Freezer, and others.

There are more than a dozen of them. They lost their popularity after the release of Room, especially considering the fact that Room is faster than other ORMs, as evidenced by benchmarks like this one.

This concludes our discussion of database libraries. Next we'll take a look at the inspection tools.

Database Inspection

Device File Explorer + SQLite Studio

The entire SQLite database is stored on Android in Internal Storage, in one or more files. There is no need to raise additional servers or connections to work with it. Therefore, the easiest way to see what is in the resulting file is to download it to your computer (it's free) and open it in SQLite Studio editor. That's what we'll do.

Go to the following path: Device File Explorer > Data > Data > your_package_name > databases and find the previously created databases colors_db and colors_room:

Select these files and save them to your computer:

Next, download SQLite Studio https://sqlitestudio.pl/ and install it.

Open SQLite Studio and find our file, as shown in the figure below:

As a result, the databases should appear in the window on the left. Double-click to open the desired database and table, and it should appear in the window on the right:

In addition to viewing the database, you can also change the data and upload it back to your mobile device. That's what we'll do. Let's change the color green to purple and save it as an example:

Then go back to Device File Explorer and upload the new file by clicking the Upload button:

Sometimes you need to restart the application in order for the data to be updated. Go to RoomActivity, comment out some of the code to write and restart the application:

//val list = listOf(Color(1, "green"), Color(2, "red"), Color(3, "blue"))
//colorDao.insert(list)

See LogCat:

D: [Color(id=1, color=purple), Color(id=2, color=red), Color(id=3, color=blue)]

We see that the color value of the first Color object has changed to purple. This gives you a great toolkit for managing the base during development, debugging and error correction.

Pros and cons

• +you can record data
• -you have to download and upload the database every time

Useful Links:

https://www.sqlite.org/index.html

We have analyzed one of the easiest and most reliable ways to inspect the database. Let's look at a few more.

Android Studio Database Inspector

The method shown in the example above has long been the main way to view the database using the GUI. However, Android Studio 4.1 introduced the Database Inspector.

To use it, you need to:

• go to the App Inspection tab;
• select the process of your application (in my case it is com.leonidivankin.draftandroid);
• find your database (in my case it is colors_room);
• find the necessary table (in my case it is colors).

As you can see, the data is the same. In this tool it is also possible to edit the value. Click twice on the line and enter the desired value, for example, I will enter rose:

Restart the application and look at LogCat:

D: [Color(id=1, color=rose), Color(id=2, color=red), Color(id=3, color=blue)]

As you can see, the value in the logs changed to rose.

I want to note that in Android Studio 4.1 the tool worked rather crookedly: it was not always possible to connect to the necessary process, sometimes the connection to the database was unexpectedly lost, but the quality and functions are constantly improving.

In Android Studio Chipmunk 2021.2.1 Database Inspector works much better, although some of the problems remain.

Pros and cons

• +no need to copy the base and fill it back in
• -raw tool

We saw how to view the SQLite database without leaving Android Studio. Next, let's see how to do it via ADB.

Adb+sqlite

Sometimes the GUI functions are not enough, for example, if you want to create a new empty database. Or if you want to write a script with a sequence of actions for testing. Adb+sqlite tools will help.

To use it, run the application on an emulator (!) and enter the following lines:

adb shell
su
cd data/data/com.leonidivankin.draftsandroid/databases
sqlite3 colors_room
SELECT * FROM colors;

Where, com.leonidivankin.draftsandroid - the name of my package, colors_room, colors - the name of the base and table from the examples above.

We see in the console that the contents of the table are displayed, as in the previous examples:

A few observations:

• It is necessary to run the application on an emulator, otherwise, there will be an error Permission denied or su: not found, because you need to root the phone;

• It is obligatory to put a semicolon ";" after SELECT * FROM colors; otherwise, it may cause an error or the query will not be executed.

You can also view a list of all databases. To do this, you must enter:

ls -R /data/data/com.leonidivankin.draftsandroid/databases

As a result, we have all the files contained in the databases folder:

Pros and cons

• +A very wide range of commands are available compared to graphical commands
• +it is possible to write a script for automation or self-testing
• -You have to enter commands via the console
• -Need a rooted phone or emulator

Useful Links: https://developer.android.com/studio/command-line/adb

Despite the fact that Adb+sqlite tools do not have a graphical interface, it deserves attention, because it is more flexible and provides more and more opportunities for automation of development or testing, for example, to correctly handle migrations.

Conclusion

Working with SQLite databases that contain dozens of tables with thousands of rows is a non-trivial task. Very often, finding an error requires all the possible arsenal that is only available on Android. The tools presented above will help you solve these problems.

Android has been using the SQLite database since its inception. And even though some other tools change every year, SQLite has remained one of the main repositories for the second decade. And, by all accounts, it will stay that way.

That's why the advice we offer here will never lose its relevance. If you use any tools not mentioned in this article, please do share them in the comments.

Also, read about networking tools for android development here.