89

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.

Willi Mentzel
  • 27,862
  • 20
  • 113
  • 121
Terence Chow
  • 10,755
  • 24
  • 78
  • 141

7 Answers7

175

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
thanhbinh84
  • 17,876
  • 6
  • 62
  • 69
Dheeraj Bhaskar
  • 18,633
  • 9
  • 63
  • 66
76

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.

enter image description here enter image description here

aknay
  • 3,415
  • 2
  • 22
  • 16
  • 9
    I think you cannot open the data directory from Device Monitor, at least i cannot do it. – Estevex Nov 20 '14 at 13:31
  • 6
    You can't open the data directory on a non-rooted device, but you can do it on an emulator. – Tad Jan 21 '15 at 01:32
  • 3
    how to open data on a non-rooted device? – Sujith S Manjavana Feb 22 '15 at 09:29
  • 4
    While emulator is running you can download files from emulated Android using adb. Example: adb pull /data/data/com.yourpackagename.here/databases/yourdbfile.db – Baker Oct 04 '16 at 20:52
  • I think @SujithManjavana is talking about just open the sqlite database from DataBase Browser while it was in the emulator path in the guest os. Not after copying back to host os. – Pradeep Sanjaya Apr 25 '19 at 05:04
15

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.

Community
  • 1
  • 1
hoss
  • 2,430
  • 1
  • 27
  • 42
11
  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.

Willi Mentzel
  • 27,862
  • 20
  • 113
  • 121
9

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
log1000
  • 587
  • 5
  • 17
  • Well...I don't know where to type `adb`...When I type it in the SQLite3 shell it gives me `...>` and it's expecting a continuation of a command. It is basically waiting for a `;`. If I type the semicolon I get `error: near "adb": syntax error`...Typing `adb shell` in a command prompt tells me adb is not a recognized command...Am I supposed to add adb to my path files? – Terence Chow Aug 22 '13 at 01:45
  • first:your have to get into adb shell(mac or linux: execute in terminal ;windows:run at cmd);then execute sqlite3 commond in the adb shell. – log1000 Aug 22 '13 at 02:06
  • 1
    How do I execute sqlite3 command from the ADB shell? it says: `/system/bin/sh: sqlite3: not found`...And I've added SQLite3 and ADB as path variables already... – Terence Chow Aug 22 '13 at 02:43
  • what device you use?wish this will give ou a hint http://stackoverflow.com/questions/3645319/why-do-i-get-a-sqlite3-not-found-error-on-a-rooted-nexus-one-when-i-try-to-op – log1000 Aug 22 '13 at 06:21
5

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

yshahak
  • 4,996
  • 1
  • 31
  • 37
4

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.

Community
  • 1
  • 1
Nitesh Verma
  • 2,460
  • 4
  • 20
  • 36
  • Tried this (starting with "adb shell", then doing the "chmod", then doing the "adb pull" of the database) but get a "adb: error: failed to stat remote object '/path/to/db': Permission denied" error. And the "chmod 666" seemed to work (there was no error, anyway). I have been able to pull the db file using Android Device Monitor in the past but if I try it now I can't even expand the initial "data" directory. I've tried Android Device Monitor with the emulator window running and not running with the same results. God this is stupid. – clamum Mar 25 '18 at 23:12
  • As a new hack, you can use stetho library for all data and network monitoring purpose. https://github.com/facebook/stetho – Nitesh Verma Mar 26 '18 at 17:23
  • Hmmm, yeah I guess I'll have to use Stetho. I saw it recommended before but didn't realize I could run SQL queries with it, so it'll work for me. I don't use Chrome browser anymore but if installing it allows me to inspect and query against Android dbs, then I'll do it. It's not like the Android Device Monitor way was super quick, you'd have to pull the db then open it in another app anyway. Thanks Nitesh. – clamum Mar 27 '18 at 17:54