HOME BLOG

Archive for the ‘SQLite’ Category

How to inspect an Android app SQLite databases

Posted on: October 30th, 2020 by Olu No Comments

Hi folks,

In this post I talk about how to view an Android app’s SQLite database. You may need to do this for debugging purpose if develoing and Android app that stores data.

You can do this using Android Studio.

Go to View -> Tool Windows -> Device File Explorer.

In the Device File Explorer pane, navigate to dat/data/<your.app.package.name>/databases.

In here you should see three files for your database: one for the database, one for the -shm file and another for the -wal file. So if your database is called somedb in code, you would see files somedb-db, somedb-db-shm, somedb-db-wal. Cmd+click on all three files.

Then right-click and click Save As.

Select where to save them on your computer.

Once saved, you can open the main database on your computer using your favourite SQLite browser e.g. DB Browser for SQLite.

That’s all for now.

Happy software development.

 

 

How to import SQLite tables quickly

Posted on: October 24th, 2020 by Olu No Comments

Hi folks,

In this post I’ll sharemy recent experience with importing a large SQLite table. By large, I mean a table with very many rows.

Usually I use DB Browser for SQLite to manage the database.

So at first I thought, maybe I should use the export and import functionality from DB Browser for SQLite.

I exported to an SQL file, made my changes to the dump, and then tried importing that dump to a new database using DB Browser for SQLite.

Lo and behold, it was super duper slow. On realizing it would take forever, I decided there must be a faster way.

More research showed that there’s an SQLite command-line tool that can carry out imports. I learnt that it may be a good idea to export to CSV format, then import it using the SQLite command-line tool. To export to csv, use the following command:

 

sqlite3 -header -csv your_db.db "select * from your_table;" > your_csv_file.csv

 

To import a csv file use the following commands:

 

sqlite3 your_db_file.db
.mode csv
.import /path/to/your/csv_file.csv your_table_name

 

So I tried it. Boy, was it much faster.

So, the takeaway is that if you must export and later import large SQLite table, first export the database to a CSV file, and use the SQLite command-line utility to import the file(s) into a new database.

That’s all for now. Happy software development.