How do I view the SQLite database on an Android device?

Question

I have a set of data in an SQLite database. I need to view the database on a device. How do I do that?

I have checked in ddms mode. The data in file explorer is empty.

Solution 1

Here are step-by-step instructions (mostly taken from a combination of the other answers). This works even on devices that are not rooted.

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

2. You may want to use adb -d shell "run-as com.yourpackge.name ls /data/data/com.yourpackge.name/databases/" to see what the database filename is.

Notice: com.yourpackge.name is your application package name. You can get it from the manifest file.

3. Copy the database file from your application folder to your SD card.

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

Notice: filename.sqlite is your database name you used when you created the database

4. Pull the database files to your machine:

   adb pull /sdcard/filename.sqlite

This will copy the database from the SD card to the place where your ADB exist.

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

6. Open Firefox SQLite Manager (Tools->SQLite Manager) and open your database file from step 3 above.

7. Enjoy!

Solution 2

UPDATE 2020

Database Inspector (for Android Studio version 4.1). Read the Medium article

For older versions of Android Studio I recommend these 3 options:

1. Facebook's open source [Stetho library] (http://facebook.github.io/stetho/). Taken from here

In build.gradle:

dependencies {
  // Stetho core
  compile 'com.facebook.stetho:stetho:1.5.1'        
  //If you want to add a network helper
  compile 'com.facebook.stetho:stetho-okhttp:1.5.1'
}

Initialize the library in the application object:

Stetho.initializeWithDefaults(this);

And you can view you database in Chrome from chrome://inspect

2. Another option is this plugin (not free)
3. And the last one is this free/open source library to see db contents in the browser https://github.com/amitshekhariitbhu/Android-Debug-Database

Solution 3

The best way I found so far is using the Android-Debug-Database tool.

Its incredibly simple to use and setup, just add the dependence and connect to the device database's interface via web. No need to root the phone or adding activities or whatsoever. Here are the steps:

STEP 1

Add the following dependency to your app's Gradle file and run the application.

debugCompile 'com.amitshekhar.android:debug-db:1.0.0'

STEP 2

Open your browser and visit your phone's IP address on port 8080. The URL should be like: http://YOUR_PHONE_IP_ADDRESS:8080. You will be presented with the following:

NOTE: You can also always get the debug address URL from your code by calling the method DebugDB.getAddressLog();

To get my phone's IP I currently use Ping Tools, but there are a lot of alternatives.

STEP 3

That's it!

---

More details in the official documentation: https://github.com/amitshekhariitbhu/Android-Debug-Database

Solution 4

The best way to view and manage your Android app database is to use the library DatabaseManager_For_Android.

It's a single Java activity file; just add it to your source folder. You can view the tables in your app database, update, delete, insert rows to you table. Everything from inside your app.

When the development is done remove the Java file from your src folder. That's it.

You can view the 5 minute demo, Database Manager for Android SQLite Database .

Solution 5

You can do this:

1. adb shell
2. cd /go/to/databases
3. sqlite3 database.db
4. In the sqlite> prompt, type .tables. This will give you all the tables in the database.db file.
5. select * from table1;

View more solutions

Comments

[user]

I need to where the database file is present.I have checked it in file explorer but data is empty.then where to get the database file.

[Andrew T.]

Are you using emulator or real device? If on real device, then I guess DDMS won't list the internal storage (including /data). However, you can use adb pull path_to_db_file_in_device path_to_save to save it to computer.

[Viachaslau Tysianchuk]

It worked, but I had to replace single quotes in step 2 with double quotes. It doesn't work with single quotes, but works with double quotes (at least on Windows).

[Muneeb Mirza]

adb pull /sdcard/ will pull whole sdcard. use adb pull /sdcard/filename.sqlite

[Jasper]

Can this be used to view any database (in sqllite) or only the one created by your app?

[sanath_p]

Only the one created by your app

[kmalmur]

it doesn't work on all devices (for example Nexus 9). For those I recommend this solution stackoverflow.com/a/29257875/291427

[Ojonugwa Jude Ochalifu]

This only works on an emulator.

[Lukas Knuth]

It's also important to note that if you want to copy the DB to your SDcard, your App (the one you're "running as") needs the android.permission.WRITE_EXTERNAL_STORAGE-permission. Otherwise, it'll tell you "permission denied".

[Choletski]

very confused... you should explain where to put your files, and how to execute those commands... I made copy/paste in my project, open cmd and run humpty.sh -d com.mypak.name databases/MyDB.db it says Succes! ... but in dumps/com.mypak.name/databases folder is just a bat file witch says : run-as: Package 'com.mypak.name' is unknown

[Bahadır Yıldırım]

@Choletski It shouldn't be outputting a batch file, that sounds like a bug. How are you executing a shell script on Windows? Through cygwin or mingw? As for extracting the database, you may want to execute the commands through adb shell yourself to see if your device supports run-as.

[srchulo]

You can skip step 4 by just redirecting it to a file on your local system: adb -d shell "run-as com.yourpackge.name cat /data/data/com.yourpackge.name/databases/filename.sqlite > /sdcard/filename.sqlite" > filename.sqlite

[everydayapps]

Works like a charm. Surprized no one upvoted this!! Thanks !

[Hoo]

Is this only work for emulator?

[r4jiv007]

For rooted device also it works fine

[Hoo]

It can access data-->data-->your.package.name-->databases--->yourdbfile.db after rooted?

[r4jiv007]

Yes, it can I have tried it

[John]

I have rooted, but data in file explorer still empty

[John]

data in file explorer still empty even device is rooted

[Capricorn]

Append a . to step 4 command to copy the file to the current directory instead of the adb programm location: adb pull /sdcard/filename.sqlite .

[Rm558]

this works with SQLiteAssetHelper as well. great tool!

[Henrique de Sousa]

Good app, but you need to add a dependency to the app's build.gradle file.

[TheIT]

Just wanted to mention that I couldn't 'cat' the file to the sdcard and instead had to 'cp' it there, otherwise I would get an empty file in the sdcard.

[Jerec TheSith]

I can't get gradle to resolve the dependencies listed in this project's readme

[NovemberEleven]

Oh~, could you tell me what error you get?

[vivek]

what is the meaning of rooting @Abhijit ?

[Abhijit Chakra]

Root means you have superuser access..Like admin access in windows

[Jerec TheSith]

Error:(48, 18) Failed to resolve: io.github.skyhacker2:sqliteonweb:1.0.1 and Error:(49, 20) Failed to resolve: io.github.skyhacker2:sqliteonweb-no-op:1.0.2. i'm using mavenCentral in repositories

[NovemberEleven]

please use jcenter in repositories :)

[Istiaque Ahmed]

how to use Stetho in eclipse ?

[Kai Wang]

I use chrome app JADE for sqllite viewing. It works great

[LifeQuestioner]

Which adding the dependency takes all of 5 seconds. Thanks so much Peter this is brilliant!

[Mahsa2]

This is working. The path is /data/data/COM.YOUR_PACKAGE.NAME/databases which COM.YOUR_PACKAGE.NAME is the name of the package you want to see its databases. Number 4 did not work, though.

[Deepu]

Thanks Mahsa Mohammadkhani, updated step 4, as you mentioned in step 2, path is app data path.

[Alberto M]

not everyone wants to add third party libraries to a project. Especially because this requires to change your code.

[androidguy]

This took me about an hour, so I hope this helps someone: if you're using DBFlow, make a class called DummySQLiteOpenHelper and put the getData method inside it, and get the database like this: SQLiteDatabase sqlDB = ((AndroidDatabase)FlowManager.getWritableDatabase("Main")).g‌​etDatabase();

[Mark Gjøl]

This works. Kinda. After step 1 you should do a 'run-as PACKAGE.NAME' otherwise you won't have read access.

[Pavlus]

Don't use shell parameter, use exec-out instead! shell tinkers with binary data converting \ns to \r\ns, while exec-out is designed for binary data transmission and won't break your DB file, copying directly to your PC like adb -d exec-out "run-as com.example.app cat databases/example.db" 1> example.sqlite

[niczak]

This won't work when pulling db from a real device as it will be encrypted. If anyone knows how to use this technique with a "real" database please let me know!

[Ankit Goel]

when i follow these steps and i push the database none of the tables are showing even though when i query for data while the app is running the data is there

[Ankit Goel]

I have an SDCard on my phone as well so im confused

[Ankit Goel]

To answer my own question you need to run cp instead of cat and remove the > ( pour into)

[Avijeet]

I tried these steps, able to get my db file(with .db extension) but files comes to be of size zero. Does this have to do something with the device on which I am running this ?

[deathangel908]

run-as: Could not set capabilities: Operation not permitted

[fiatjaf]

This is the correct answer. Ok, not the correct answer, but the simplest answer by far, and works as magic.

[Gene Bo]

After entering the inspect url, I wasn't easily seeing how to access the DB. This is how - after loading the url when your app is already running, there will be a url/link with text inspect on the loaded page for the App. Click that link .. then to access the DB : 1) Observe the Developer Tools window pops up from browser, 2) Click the Resources tab, 3) The databases are under Web SQL in the left-hand menu

[Denny Kurniawan]

thanks, now i can get sqlite by remote. Is this work for public address too?

[Max R.]

For me I had to use cp (copy). And as I don't have an SD Slot I wrote the data to the root of my emulated directory (you can access this with any file explorer) adb -d shell "run-as com.example.android cp /data/user/0/com.example.android/files/sqlite.db3 /storage/emulated/0/sqlite.db3"

[Alex]

In the step number 2, I introduce "cd /data/data/com.name.of.my.package/databases" but the result is "Permission denied". Do you have to have root access to the device? Thank you.

[Rupam Das]

The SQLite Manager addon is not supported in latest Firefox version 59.02 .. "This add-on is not compatible with your version of Firefox."

[Deepu]

see the comment from – Mark Gjøl

[Jeffrey]

If you get 0.0.0.0:8080 then use the adb forward option above

[Julien Kronegg]

The great advantage of this solution is that you also can do SQL INSERT and DELETE (which is not possible with the solutions based on adb pull). All all changes are reflected in realtime so you don't need to copy the database each time as with adb pullbased solutions.

[Julien Kronegg]

An adb pull will copy the full database, so you don't see the database changes in realtime (you will need to copy the database each time you change it).

[Udara Seneviratne]

We can download a Linux SQLite browser from sqlitebrowser.org

[lidox]

it works only, if the app is installed via android studio. if you install a signed apk on the device, this method does not work for me :(

[Bob]

@lindox That's because you've added it as a debugImplementation dependency in your gradle file. Use implementation instead of debugImplementation in your gradle file.

[zeeshan]

It times out, and doesn't work reliably for my database which has over 6000 rows.

[Gregor Ažbe]

I used online SQLite manager: sqliteonline.com

[iCantC]

The Db opens no doubt however, the page formatting is not up to standards. Columns are overlapped, and sluggish crawling screen.

[E J Chathuranga]

In Samsung devices shows run-as: Could not set capabilities: Operation not permitted

[renatoarg]

Fantastic! This library is essential, thanks for sharing!

[pravingaikwad07]

So I tried following : >> cd C:\Users\hp\AppData\Local\Android\Sdk\platform-tools ; >> adb shell >> cd data\data\com.myapp ... Its throwing "Permission denied" error.

[DragonFire]

it works but pulling the /data/data/your-package-name/databases/yourdb.db is cumbersone it would be nice if it would open automatically