Saturday, June 30, 2012

How to access SQLite database file in Android.

As we know that Android has inbuilt database called SQLite where application can store their data. SQLite was mainly developed for embedded devices. I am not going to discuss here about SQLite database but I am going to demonstrate how Android developer can access the SQLite database. This might be helpful while debugging the code especially to store application data in SQLite database tables. You can access the SQLite database file in emulator or rooted device.

Ways of accessing SQLite database file:

  • Copying from Phone/Emulator to your laptop
  • Accessing via Android shell and Sqlite commands.

Copying from phone/Emulator to your laptop:

You can easily copy the database file by just executing the command given below. But before that make sure  ...<android-sdk>/platform-tools/ is in environment path otherwise this command can executed only in .../platform-tools/ directory.
$adb shell /data/data/your.package.name/databases/databasefilename.db 
Database file can also be downloaded by Eclipse. If you have already installed the ADT plugin then it will be easier to browse the Android Emulator files. In Eclipse menu Window > Open Perspective > Other > DDMS . If the device/emulator is connected to your system, DDMS perspective shown in picture will appear. 
Eclipse Android file browser for SQLite database file access
Eclipse android file browser
DDMS perspective button marked as 1 in the picture: Once you select this perspective click the "File Explorer"(Which is marked as 2 in above picture) tab you will see the list of folder in the window. You can browse /data/data/your.package.name/database  where you will see a list of database files. Once you select the desired database file, "disket icon" on the upper-right corner of the window will get activated(which is marked as 4 in the above picture). Once you press this icon, the Eclipse will ask you to browse the place where you want to download the database file. 
Database table can be browsed with help of sqlite GUI or Mozilla browser if this extension is installed in your Mozilla browser.


Accessing via Android shell and Sqlite commands:

The following command is used for accessing file either from rooted device or emulator. 
$adb devices               #This command lists all the devices attached to your system
List of devices attached 
emulator-5554 device     #This emulator is the one which was running on my system.

$adb shell       #This allows you to access Android shell.
$ls              #This command lists all directories present in the current directory
.
.
data             #Sqlite database files are present in this directory
sdcard
sys
system
.
.
$cd data/data   #Now you need to find your package name here. eg com.rakesh.android.test

$cd com.rakesh.android.test/databases
$ls
mylist.db
$sqlite3 mylist.db
sqlite> .help        #This command lists the commands available in SQLite.
sqlite>.tables       #This command lists the tables present in the SQLite database.
sqlite>.schema       #This command shows the schema of the database.
sqlite>select * from myTable  #You can also run the queries in similar way.
sqlite>.exit         #This command exits you from SQLite command prompt.
$                    #Press Ctrl+C to come out of Android shell

For more commands and info about SQLite, please visit this site www.sqlite.org


Your valuable comments are always welcomed. It will help to improve my post and understanding.

"By three methods we may learn wisdom: First, by reflection, which is noblest; Second, by imitation, which is easiest; and third by experience, which is the bitterest."
By : Confucius