Debugging sqlite database on the device

Question

I am presently working on an WiFi application for Android. I am having trouble trying to access the database on the device. Debugging in the emulator doesn't work for me, because there is no WiFi support in the emulator. I tried pulling the database file out of the device by using

adb pull data/data/package-name/databases/database-name

But I get the error "Permission denied.". In this answer Android: Where are database files stored?, Commonsware has suggested to pull database file by running in debug mode. But it doesn't work too. Any help on how to debug the database without rooting the device would be much appreciated.

Answer 1

I'll repeat myself from another answer:

Starting from API level 8 (Android 2.2), if you build the application as debuggable, you can use the shell run-as command to run a command or executable as a specific user/application or just switch to the UID of your application so you can access its data directory.

Copy files to external storage on the device

So if you wish to pull your application database from the device you can launch a debug build of the application from Android Studio, connect with adb shell and run the following command:

run-as com.your.package sh -c "cat ~/databases/db-file.db" > /sdcard/db-file.db

or you can use a shorter version which uses relative paths and by skipping the reference to the home directory ~ can omit the sh -c part too:

run-as com.your.package cat databases/db-file.db > /sdcard/db-file.db

This will copy your db-file.db to the root of your SD card / external storage. Now you can easily get it from there by using file manager, adb pull or whatever else you like. Note that with this approach, there is NO need for your app to have WRITE_EXTERNAL_STORAGE permission, as the copying is done by the shell user who can always write to the external storage.

Copy files directly to the computer

You can also copy a database directly to your computer with the following command:

adb shell 'run-as com.your.package cat databases/db-file.db' > db-file.db

The adb shell command above will not work correctly on Windows host because of the CR/LF symbols conversion, unless you run it from a bash shell or use the undocumented adb exec-out command instead (some comments mention they still cannot get it to work correctly on a Windows machine, so your mileage may vary):

adb exec-out 'run-as com.your.package cat databases/db-file.db' > db-file.db

Write files back to the device

If you modified the file on your computer and wish to write the modifications back to the device use the following command:

adb exec-in 'run-as com.your.package tee databases/db-file.db' < db-file.db

or if you prefer an adb shell version over undocumented exec-in, use the following (but read the concern about the Windows CR/LF conversion above):

adb shell 'run-as com.your.package tee databases/db-file.db' < db-file.db >/dev/null

Status update

All the commands above are still working as of July, 2023

Answer 2

I use this shell script on my MAC, that copies database directly to my home folder. Easy one click solution, just change package name (com.example.app) and database name (database.sqlite)

Simple Script

#!/bin/bash
adb -d shell 'run-as com.example.app cat /data/data/com.example.app/databases/database.sqlite > /sdcard/database.sqlite'
adb pull /sdcard/database.sqlite ~/

Script which accepts arguments [package_name] [database]

#!/bin/bash

REQUIRED_ARGS=2
ADB_PATH=/Users/Tadas/Library/sdk/platform-tools/adb
PULL_DIR="~/"

if [ $# -ne $REQUIRED_ARGS ]
    then
        echo ""
        echo "Usage:"
        echo "android_db_move.sh [package_name] [db_name]"
        echo "eg. android_db_move.sh lt.appcamp.impuls impuls.db"
        echo ""
    exit 1
fi;


echo""

cmd1="$ADB_PATH -d shell 'run-as $1 cat /data/data/$1/databases/$2 > /sdcard/$2' "
cmd2="$ADB_PATH pull /sdcard/$2 $PULL_DIR"

echo $cmd1
eval $cmd1
if [ $? -eq 0 ]
    then
    echo ".........OK"
fi;

echo $cmd2
eval $cmd2

if [ $? -eq 0 ]
    then
    echo ".........OK"
fi;

exit 0

Answer 3

The best way to view and manage you android app database is to use this library https://github.com/sanathp/DatabaseManager_For_Android

With this library you can manage your app SQLite database from you app itself. you can view the tables in your app database , update ,delete, insert rows to your tables .Everything from your app.

Its a single java activity file ,just add the java file to your source folder.When the development is done remove the java file from your src folder thats it .

It helped me a lot .Hope it helps you too .

You can view the 1 minute demo here : http://youtu.be/P5vpaGoBlBY

Answer 4

Although, it's an old question I think it's still relevant and deserves a current state answer. There are tools available, which allow you to inspect databases directly (without the need to pull them from the device or emulator).

The tool, I most recently discovered (and favor most) is Android Debug Database.

You only need to add this dependency:

debugImplementation 'com.amitshekhar.android:debug-db:1.0.3'

No further code is required. After you started your app, open logcat and filter for "DebugDB" and you will find a message saying

D/DebugDB: Open http://192.168.178.XXX:8080 in your browser

It works with every browser and you can inspect your database tables and shared preferences.

It also works with the default and the Genymotion emulators.

---

The tool I used before is stetho.

Downside: You need to add a bit of code and you are bound to the Chrome browser.

Advantage: You have the option to also inspect network traffic.

Answer 5

In the new Android Studio 4.1 there is the new Database Inspector.

You can select the following options from the menu bar View > Tool Windows > Database Inspector to open it (App Inspector in Android Studio 4.2). More detailed instructions can be found in this blog and in Exploring the Database Inspector in Android Studio medium article.

Another way is to use stetho for this. You add the dependency and then can use the Chrome DevTools ( chrome://inspect ) to check the database when the device is plugged in.

Answer 6

If you get

The system cannot find the path specified.

try

adb -d shell "run-as com.yourpackage cat /data/data/com.yourpackage/databases/dbname.sqlite > /sdcard/dbname.sqlite"

Note the double quote!

Answer 7

In my application I export the database to the SD card. Once the database is on the SD card it can be accessed by plugging the device into your computer.

Look at this post: Making a database backup to SDCard on Android

Answer 8

I simply did:

$ adb shell
shell@android:/ $ run-as myapp.package.name sh
shell@android:/data/data/myapp.package.name $

Then I can debug an sqlite database or whatever I wanna do from shell with the right permissions.

Answer 9

Android Studio 4.1 Added a new feature to view/ edit Android SQLite databases.

How to open Database Inspector

To open the Database Inspector in Android Studio, you need to select View > Tool Windows > Database Inspector from the menu bar.

Also you need to run the app to a device running API level 26 or higher.

Using this tool you can

• Query your databases

• Modify and debug your database

Answer 10

Here is step by step instructions - mostly taken from a combination of the other answers. This works with devices that are not unlocked.

1. Connect your device and launch the application in debug mode.

2. Copy the database file from your application folder to your sd card: execute:

   ./adb -d shell 'run-as com.yourpackge.name cat /data/data/com.yourpackge.name/databases/filename.sqlite > /sdcard/filename.sqlite'

3. Pull the database files to your machine: execute:

   ./adb pull /sdcard/ execute: ./adb

4. Install Firefox SQLLite Manager: https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/

5. Open Firefox SQLLite Manager and open your database file from step 3 above.

6. Enjoy!

Answer 11

There is a way if an apk is debuggable to use a program called run-as from the (non-root) adb shell to copy an application's private file.

Answer 12

None of the run-as-and-cat-to-sdcard solutions worked for me on Android 4.4.2. I'm not sure, but I suspect it may be due to the run-as tool not correctly handling the new sdcard_r and sdcard_rw permissions.

I first had to copy the database file to /files in my application's private internal storage:

shell@hammerhead:/ $ run-as com.example.myapp   
shell@hammerhead:/data/data/com.example.myapp $ cp databases/mydb files/mydb

Then I copied to /sdcard/Android/data/com.example.myapp/files in Javaland (this requires the WRITE_EXTERNAL_STORAGE permission):

public class MainActivity extends BaseActivity {

    @Override
     protected void onCreate(Bundle savedInstanceState) {
         ...

         if (isExternalStorageWritable()) {
             final FileInputStream input;
             try {
                 input = openFileInput("mydb");

                 File output = new File(getExternalFilesDir(null), "mydb");

                 copy(input, output);
             } catch (FileNotFoundException e) {
                 e.printStackTrace();
             } catch (IOException e) {
                 e.printStackTrace();
             }
         }
     }

     public void copy(FileInputStream in, File dst) throws IOException {
         OutputStream out = new FileOutputStream(dst);

         // Transfer bytes from in to out
         byte[] buf = new byte[1024];
         int len;
         while ((len = in.read(buf)) > 0) {
             out.write(buf, 0, len);
         }
         in.close();
         out.close();
     }

     public boolean isExternalStorageWritable() {
         String state = Environment.getExternalStorageState();
         return Environment.MEDIA_MOUNTED.equals(state);
     }
 }

Finally, I copied the file to my laptop:

$ adb pull /sdcard/Android/data/com.example.myapp/files/mydb

Answer 13

My Device was not having sdcard

so the first solution did not work for me.

If you are having similar issue try like this:

  adb shell "run-as package chmod 777 /data/data/package/databases/yourdb.sqlite";
  adb pull /data/data/package/databases/yourdb.sqlite

Answer 14

You need to be running adb as root, or be using it on a rooted phone.

To run adb as root, use adb root

See also: Why do I get access denied to data folder when using adb?

Answer 15

Try this app: SQLiteWeb (https://play.google.com/store/apps/details?id=br.com.cm.sqliteweb). It provides remote access to your database without pulling it out.

In paid version, it has root access for private database (/data/data/package-name...) or implement a Content Provider to connect using SQLiteWeb (Instructions inside app)

But if want to stay with the free version, you may create your database in external storage:

database = SQLiteDatabase.openDatabase(Environment.getExternalStorageDirectory()
        + "/" + DATABASE_NAME, null, DATABASE_VERSION);

Answer 16

On OSX,using @Tadas answer with automator and sqllitebrowser(https://github.com/sqlitebrowser/sqlitebrowser):

1. open Automator and create new workflow.

2. add "Run Shell Script" action.

3. Paste this :

   source ~/.bash_profile adb shell 'run-as cat /data/data/your_app_uid/databases/db.name > /tmp/db.name' adb pull /tmp/db.name ~/ open -a sqlitebrowser ~/db.name

4. click run to refresh the database on sqlitebrowser.

Answer 17

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.