Android | SQLite Database Debugging From a Remote Shell

The Android SDK includes a sqlite3database tool “adb remote shell” that allows you to to manage SQLite databases created on actual device or emulator “AVD” from your PC . You can use the shell to browse table contents, run SQL commands, and perform other useful functions on SQLite databases.

Objectives:

• How to connect to a sqlite database existing on Android devices from your PC?
• How to interact with Android sqlite database from remote shell?

Environment & Tools:

• Android SDK should be installed.
• Command “shell” window
• Running AVD “emulator”
• Connected Android device “Samsung Galaxy SII”

Before Starting:

In a previous post “Android | Simple SQLite Database Tutorial” I have created an App that creates a database named “BookDB” and one table “books“.

( 1 ) List Connected Devices

• Open the command-line window “shell”
• You can find all running emulator and connected devices using the below command.
• >adb devices

C:\>adb devices
List of devices attached
304D19......    device  //this is my device serial #
emulator-5554   device  // this is an emulator running on my PC

( 2 ) Connect to a Device

• Now you can connect to one of the devices using their serial #.
• We will connect to the emulator.
• >adb -s emulator-5554 shell

C:\>adb -s emulator-5554 shell
root@generic:/ #

( 3 ) List All App IDs Pakcage

• After connecting to a device you can list available Apps IDs “packages”
• App ID is its package that is defined in the manifest xml file.
• Android stores Apps data under /data/data folder.
• # ls /data/data

root@generic:/ # ls /data/data
ls /data/data
com.example.android.apis
......
com.hmkcode.android

( 4 ) List All Databases for an App

• We can use ls command to list all databases available for a specific App
• I will list database of my App com.hmkcode.android
• # ls /data/data/com.hmkcode.android/databases

root@generic:/ # ls /data/data/com.hmkcode.android/databases
ls /data/data/com.hmkcode.android/databases
BookDB
BookDB-journal

( 5 ) Open a Database

• Now to connect to a database of a specific App use sqlite3 command and the path to the database as a parameter.
• # sqlite3 /data/data/com.hmkcode.android/databases/BookDB

root@generic:/ # sqlite3 /data/data/com.hmkcode.android/databases/BookDB
sqlite3 /data/data/com.hmkcode.android/database/BookDB
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

( 6 ) List All Tables

• After a opening a database you can list all tables under that database by using .tablescommand.
• sqlite> .tables

sqlite> .tables
.tables
android_metadata  books // we have one table "books"

( 7 ) SQL command

• You can execute SQL command to select, update, delete or insert records.
• e.g. to view all books data use the below SQL statement.
• sqlite> select * from books;

sqlite> select * from books;

( 8 ) .help for All Command

• You can always refer to .help command to list all available commands.

sqlite> .help