Learn how you can inspect the contents of your SQLite database from your terminal. Annyce shows you how to use the sqlite3 command line tool in this video.
- [Instructor] Now that we've created our database and successfully added a few records, I want to show you how you can inspect its contents from the command line. Let's go ahead and open up our Terminal. You will need to have Android Debug Bridge or ADB available on your class Pad. ADB is included in the Android SDK Platform Tools Package. If you have Android Studio running, then you will have ADB available. You can double check that it's on your Pad by typing which adb.
Let's do that now, which adb. This will give you the location of adb on your machine. If you don't see the location, please refer to the sample bash profile that I've included in the exercise files for this video. The next thing we'll do is connect to our running emulator. Make sure you only have one device connected to follow this command exactly. I'm going to type adb shell.
Notice how the prompt changed in front of the pound sign. We now have access to the database stored on the device. We're going to use the SQLite 3 command line tool in order to inspect our database. The SQLite command line uses simple commands that begin with a dot, like .help or .tables. To see a full listing of the available commands I like the reference provided by tutorialspoint. Along the left hand side, they have examples of each command that is available to you.
It shows the syntax and the output. If we look at the Select Query, right over here, I'll choose Select Query. If we scroll down some on the Select Query page, we can see the examples as well as the various options that are available, like .header and .mode, to display the output in a more readable format. I encourage you to bookmark this site as a reference when using the SQLite 3 command line tool.
For now, let's go back to our Terminal and look at our topsy turvey database and its recipe table. To open the database on an emulator, we will use the SQLite 3 command and supply the path to our database. The format is the following, sqlite3 then a space, and you begin to type the path to the database. The database on the emulator is stored in /data, /data again, and then, a forward slash followed by the package name.
In this case, it's info.adavis.topsy.turvey. That's the package name that we chose for our application. Next, we put another forward slash and the word databases. Finally, we have a forward slash and the name that we chose when we created our database with SQLite. That was topsy_turvey.db.
Once we click Enter, notice that we're now in the SQLite 3 prompt. This is where we will start to use those dot commands that we spoke about earlier. If you ever get lost or a command does not seem to be working, check the spelling and compare against the options available when you type .help. Let's do that now, .help. You will see a full list of all of the available commands.
Now, the first thing we want to do is inspect the tables. For that, we will use the .tables command. We'll type .tables and then Enter. Notice that we have three tables in our database, android_metadata, recipe, and recipe_step. The android_metadata table was created automatically for you by the Android system. The other two tables were created by using our SQLiteOpenHelper implementation.
We can confirm that by looking at the schema for the tables. Let's look at the schema for the recipe table. We're going to use the .schema command, followed by the table name, recipe. Here we can see the command we supplied to our create method. The final thing we want to do is view the records that we've inserted so far. This is going to require a simple SQL query.
We want to retrieve all the records from the recipe table, so we'll use a select statement. SELECT * FROM recipe. But since this is an SQL query, we need to end this statement with a semicolon. Now, we'll type Enter. Now we can see the recipe information printed out to our screens. Why don't you try using the command line with the SQLite 3 tool before moving on to the next video.
To begin, Annyce Davis reviews using the de facto data persistence solution available on Android: SQLite. She takes you through basic SQLite concepts—such as how to create a database wrapper and insert data into a table—as well as a few more advanced topics. Next, she covers working with Cupboard, an open-source project dedicated to simplifying your SQLite interactions. To wrap up, she dives into working with Realm, an object database solution designed with mobile devices in mind. Throughout the course, Annyce discusses establishing relationships between database tables, creating and running queries, as well as performing data migrations.
- Overview of SQLite
- Defining a database table's schema
- Exploring SQLite create table syntax
- Executing create table statements
- Inserting data with a foreign key relation
- Inspecting the database using a terminal
- Updating and deleting records
- Working with Cupboard
- Working with Realm