Pulling Sqlite database from android gives empty database

Question

I am developing a mobile application that uses Sqlite and I would like to pull the .db file from the phone and view it in DB Browser for Sqlite.

The issue comes from the fact that when I run the adb commands the .db file I get is empty, even though I have creatd tables and added data to those tables in the database.

In the application I query the database for the inserted records and they are returned. Any thoughts?

adb command: adb exec-out run-as projectname cat databases/my.db > my.db

db creation code:

if(!Sqlite.exists("my.db")){
        new Sqlite("my.db")
        .then(
            (db) => {
            //create table here
                db.execSQL("CREATE TABLE IF NOT EXISTS Times (Id INTEGER PRIMARY KEY AUTOINCREMENT, clockIn TEXT, clockOut TEXT)")
                .then(
                    () => {
                        console.log("succcess")
                        db.execSQL("INSERT INTO Times (clockIn, clockOut) VALUES (?, ?)", ["Nic", "Raboy"]).then(() => {
                            db.all("SELECT * FROM Times").then((rows) => {
                                console.log(rows)
                            })
                        })

                    }, 
                    (error) => { 
                        console.log('Error occurred creating table');
                    });
        },
            (error) => {
                console.log("Error creating Database");
            });
    }

In my project I do not have the if(!Sqlite.exists("my.db")) and it doesn't mess with my existing database. Does removing this line work for you? I believe that the new Sqlite('my.db') will read the database if it exists and hand you the reference to it. — Shawn Pacarar
– Shawn Pacarar, Commented May 10, 2019 at 17:30
@ShawnPacarar No, the database is still empty after running the adb command — cvb
– cvb, Commented May 10, 2019 at 18:27
@Manoj Android version 9. I'm using an emulator if that makes a difference. — cvb
– cvb, Commented May 10, 2019 at 18:29
Then it should be working, may be you want to give a try for the alternative commands in this SO thread — Manoj
– Manoj, Commented May 10, 2019 at 18:31

cvb · Accepted Answer · 2019-05-10 19:16:58Z

9

I figured out how to solve this issue. On android 9 you have to include the -wal and -shm files when pulling using adb. Then when the .db file is opened you will see your database as you have built it and not an empty skeleton db.

This may not be the best way of doing this but it will work:

adb exec-out run-as projectname cat databases/my.db > my.db
adb exec-out run-as projectname cat databases/my.db-shm > my.db-shm
adb exec-out run-as projectname cat databases/my.db-wal > my.db-wal

edited May 10, 2019 at 19:16

answered May 10, 2019 at 19:00

cvb

7917 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Michael Dougan Over a year ago

You should also be able to download the database through the Device Manager in Android Studio (if that is what you are using). You navigate to your device (which can be a simulator AVD) and search your app's data/data/[database] folder.

MikeT Over a year ago

An alternative is to ensure that the database is fully checkpointed using see PRAGMA schema.wal_checkpoint;. In which case the -shm and -wal files will be empty and not required. It comes to light on Android 9 because from Android 9 the default is to use WAL as opposed to the SQLite default of journal mode.

Deepak J · Accepted Answer · 2020-07-21 13:02:33Z

0

You have to use appDatabase.close() in order to work it.

After closing the db in your activity's onStop(), pull the db file from Android Device File Explorer in Android Studio and use any Broswer sqlite viewer to view the tables in it.

answered Jul 21, 2020 at 13:02

Deepak J

2342 silver badges10 bronze badges

Collectives™ on Stack Overflow

Pulling Sqlite database from android gives empty database

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related