Android | SQLite Database Debugging From a Remote Shell

android-sqlite-shellThe Android SDK includes a sqlite3 database 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 .tables command.
  • 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