How to use ADB in Android Studio to view an SQLite DB

Question

I need to view my SQLite db but I don't know how to do it. I've gone to http://www.sqlite.org/download.html and downloaded the command line shell for my OS, but when I run the program and type adb ... I get errors.

Note: I'm using Android Studio so I'm assuming I don't need to install anything extra because I recall Android Studio said it had all the SDK tools needed.

Answer 1

Easiest Way: Connect to Sqlite3 via ADB Shell

I haven't found any way to do that in Android Studio, but I access the db with a remote shell instead of pulling the file each time.

Find all info here: http://developer.android.com/tools/help/sqlite3.html

1- Go to your platform-tools folder in a command prompt

2- Enter the command adb devices to get the list of your devices

C:\Android\adt-bundle-windows-x86_64\sdk\platform-tools>adb devices
List of devices attached
emulator-xxxx   device

3- Connect a shell to your device:

C:\Android\adt-bundle-windows-x86_64\sdk\platform-tools>adb -s emulator-xxxx shell

4a- You can bypass this step on rooted device

run-as <your-package-name> 

4b- Navigate to the folder containing your db file:

cd data/data/<your-package-name>/databases/

5- run sqlite3 to connect to your db:

sqlite3 <your-db-name>.db

6- run sqlite3 commands that you like eg:

Select * from table1 where ...;

Note: Find more commands to run below.

SQLite cheatsheet

There are a few steps to see the tables in an SQLite database:

1. List the tables in your database:

   .tables
   

2. List how the table looks:

   .schema tablename
   

3. Print the entire table:

   SELECT * FROM tablename;
   

4. List all of the available SQLite prompt commands:

   .help
   

Answer 2

Easiest way for me is using Android Device Monitor to get the database file and SQLite DataBase Browser to view the file while still using Android Studio to program android.

1) Run and launch database app with Android emulator from Android Studio. (I inserted some data to database app to verify)

2) Run Android Device Monitor. How to run?; Go to [your_folder] > sdk >tools. You can see monitor.bat in that folder. shift + right click inside the folder and select "Open command window here". This action will launch command prompt. type monitor and Android Device Monitor will be launched.

3) Select the emulator that you are currently running. Then Go to data>data>[your_app_name]>databases

4) Click on the icon (located at top right corner) (hover on the icon and you will see "pull a file from the device") and save anywhere you like

5) Launch SQLite DataBase Browser. Drag and drop the file that you just saved into that Browser.

6) Go to Browse Data tab and select your table to view.

Answer 3

The issue you are having is common and not explained well in the documentation. Normal devices do not include the sqlite3 database binary which is why you are getting an error. the Android Emeulator, OSX, Linux (if installed) and Windows (after installed) have the binary so you can open a database locally on your machine.

The workaround is to copy the Database from your device to your local machine. This can be accomplished with ADB but requires a number of steps.

Before you start you will need some information:

• path of the SDK (if not included in your OS environment)
• your <package name>, for example, com.example.application
• a <local path> to place your database, eg. ~/Desktop or %userprofile%\Desktop

Next you will need to understand what terminal each command gets written to the first character in the examples below does not get typed but lets you know what shell we are in:

• > = you OS command prompt
• $ = ADB shell command Prompt
• ! = ADB shell as admin command prompt
• %

Next enter the following commands from Terminal or Command (don't enter first character or text in ())

> adb shell
$ su
! cp /data/data/<package name>/Databases/<database name> /sdcard
! exit
$ exit
> adb pull /sdcard/<database name> <local path>
> sqlite3 <local db path>
% .dump
% .exit  (to exit sqldb)

This is a really round about way of copying the database to your local machine and locally reading the database. There are SO and other resources explaining how to install the sqlite3 binary onto your device from an emulator but for one time access this process works.

If you need to access the database interactively I would suggest running your app in an emulator (that already had sqlite3) or installing sqlite onto your devices /xbin path.

Answer 4

1. Open up a terminal
2. cd <ANDROID_SDK_PATH> (for me on Windows cd C:\Users\Willi\AppData\Local\Android\sdk)
3. cd platform-tools
4. adb shell (this works only if only one emulator is running)
5. cd data/data
6. su (gain super user privileges)
7. cd <PACKAGE_NAME>/databases
8. sqlite3 <DB_NAME>
9. issue SQL statements (important: terminate them with ;, otherwise the statement is not issued and it breaks to a new line instead.)

Note: Use ls (Linux) or dir (Windows) if you need to list directory contents.

Answer 5

What it mentions as you type adb?

step1. >adb shell
step2. >cd data/data
step3. >ls -l|grep "your app package here"
step4. >cd "your app package here"
step5. >sqlite3 xx.db

Answer 6

You can use a very nice tool called Stetho by adding this to build.gradle file:

compile 'com.facebook.stetho:stetho:1.4.1'

And initialized it inside your Application or Activity onCreate() method:

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    Stetho.initializeWithDefaults(this);
    setContentView(R.layout.activity_main);
}

Then you can view the db records in chrome in the address:

chrome://inspect/#devices

For more details you can read my post: How to view easily your db records

Answer 7

Depending on devices you might not find sqlite3 command in adb shell. In that case you might want to follow this :

adb shell

$ run-as package.name
$ cd ./databases/
$ ls -l #Find the current permissions - r=4, w=2, x=1
$ chmod 666 ./dbname.db
$ exit
$ exit
adb pull /data/data/package.name/databases/dbname.db ~/Desktop/
adb push ~/Desktop/dbname.db /data/data/package.name/databases/dbname.db
adb shell
$ run-as package.name
$ chmod 660 ./databases/dbname.db #Restore original permissions
$ exit
$ exit

for reference go to https://stackoverflow.com/a/17177091/3758972.

There might be cases when you get "remote object not found" after following above procedure. Then change permission of your database folder to 755 in adb shell.

$ chmod 755 databases/

But please mind that it'll work only on android version < 21.