Sqlite database manipulation with Sqlite3 and Stetho

During creation of our apps we often find it necessary to have data for testing views and logic.

Think of a situation when you have to display a lot of data in the list, it’s quite a common case for an android dev. Our example app also has to store data in some way, let’s assume that we have a lot of data so we choose to use a database.

The app which code is here: https://github.com/rstit/sqlite-manipulation
displays sample data from .sqlite database.

Now, we have a couple of ways of changing data saved in the database. First but not the best one is to edit data inside our code, for example after clicking a button. We call a method which updates the requested row. When using ORM like requery or realm this task seems to be simple, but we have to create an additional functionality in our app (we’re assuming that data is obtained from the server and user shouldn’t edit it directly).

Manage database with adb and sqlite3

Please note that you need root access to use this method.

First of all you need to get to the device shell with adb. In order to do that you have to call

adb shell  

or

adb shell <device name>  

if you have more than one device connected, to get a list of devices simply call

adb devices  

When it’s done you have to get root access by calling

su  

Root access is needed because you’re going to open a file from /data/data directory which only the root can access.

The last step is to open sqlite3 command line tool with yours database's name

To find the database you should locate your app folder in /data/data path

For example:

/data/data/com.example.appname/databases/default

Now you can type into console, which will open your database in sqlite3:

sqlite3 <database_name>  

You should see this message:

SQLite version 3.9.2 2015-11-02 18:31:45  
Enter ".help" for usage hints.  
sqlite>  

If you want to display the database scheme you can use:

.tables

In order to display data from the table named “News” you should call:

SELECT * FROM News WHERE id < 2;  

Remember about ; at the end, console will wait for it.

OK, so now you know how to display the table content, if you want to update the data you can send a query:

UPDATE News SET content = 'Changed content' WHERE id = 1;  

With sqlite3 tool you can use all commands from sqlite and if you want to see additional options of the tool itself just type .help in the console input

For more info visit:
http://www.sqlite.org/cli.html
https://developer.android.com/studio/command-line/sqlite3.html

Manage database with Stetho

If you prefer to use a tool with a GUI you should take a look at Facebook Stetho.
Basically it’s a debug bridge which allows you to use Chrome Developer Tools to debug your android app. I won’t get into details with this, for now we’ll only use database manipulation.

For our purpose only the main Stetho module is required, so add this to your app dependencies:

 dependencies { 
    compile 'com.facebook.stetho:stetho:1.5.0' 
  } 

The next thing is to create or edit the application class to initialize Stetho modules.

Add the below initialization in onCreate() method of your application class:

public class SqliteExample extends Application {

   @Override
   public void onCreate() {
       super.onCreate();

       Stetho.initialize(
               Stetho.newInitializerBuilder(this)
                       .enableDumpapp(Stetho.defaultDumperPluginsProvider(this))
                       .enableWebKitInspector(Stetho.defaultInspectorModulesProvider(this))
                       .build());
   }

Remember to inform android about yours custom application class by altering AndroidManifest.xml file

<application android:name=".SqliteExample" android:allowBackup="true"  
        android:label="@string/app_name"
        android:icon="@mipmap/ic_launcher" android:theme="@style/AppTheme"> 

        ...

</application>  

After running the app on an emulator or a device you have to open chrome://inspect in Google Chrome browser.

You should see this screen:

Click inspect to open Developer Tools.

In order to preview database go to Resources tab, then open Web SQL group from menu on left and choose the table you want to display from your database.

With the same commands as in sqlite you can display or alter the data in the tables. To use sqlite commands click on your database name in the menu on left, in my case it’s “default”

For more info visit:
http://facebook.github.io/stetho/

Summary

I’ve demonstrated that previewing and editing database on android is a pretty straightforward thing to do. Sometimes it’s very useful to have direct access to the database.

As you can see, there are differences between using sqlite3 and Stetho. The main one is that you don’t have to have root access while using Stetho. There is some overhead with Stetho because you have to edit the application class and edit the manifest. It’s also a problem when you don’t have access to the application’s code and you’re in need of editing the database, then sqlite3 is the best solution.

Personally, I think that Stetho is a very solid tool and GUI is also a big advantage, on the other hand sqlite3 is built-in into android and it’s ready to use, but requires root access, which you can of course get by using an emulator.