Via non-GUI means

The SQLite comes with sqlite3.exe command line program for Windows-based databases. The same program is available from the shell of Android for creating SQLite databases and then creating tables, indexes, etc in them.

For additional information, on the SQLite shells, refer to the following articles:

Creating databases from SQLite shell

Using SQLite from Shell in Android

Now for the GUI Database Managers. There are no SQLite GUI managers from the sqlite.org site. In addition to the sqlite3 command line program, there is another command line based analyzer program. However, there are plenty of commercial GUI DB Managers for SQLite.

And the following SQLite Database Browser is an open source program.

SQLite Database Browser

You can get SQLite Database Browser from Source Forge:

SQLite Database Browser

Eventhough the dev activity seems to have slowed down, this program seems to work ok for all the basic tasks. Following sections describe how to do the basic SQLite db-related tasks using SQLite Database Browser.

Creating a database

In SQLite, one normal OS file is used to store all the data belonging to a database. So, to create a database, use File-New Database menu.

Figure 1. Creating Database from SQLite Database Browser

In the above example, contactsext.db is created. So, you will see an OS file named contactsext.db file in the path you specified.

Creating Tables

SQLite Browser lets you create tables (and the columns in them) when you created a new database. Or, you can use the menu Edit-Create Table.

Figure 2. Creating table and the columns in them

Here the available column types are: TEXT, NUMERIC, BLOB, and INTEGER PRIMARY KEY. In the above example, _id is an integer primary key. Android seems to use ‘_id’ (underscore followed by id) for the primary key ids.

Database Structure

In the ‘Database Structure’ tab, you can see the structure of the entire database.

Figure 3. The database structure

The foreign keys are not supported (i.e. enforced) in SQLite. So, you won’t see any explicit linkage between those keys (for example, _id in Address and addrid in Contacts).

Browsing Data

You can add new records (or delete them) or browse the contents of the tables from the Browse Data tab. You can also search for the records. The features here are very rudimentary.

Figure 4. Browsing SQLite data

SQL Log

One interesting feature of SQLite Database Browser is the SQL that the application (or user) submits to the database engine in the backend.

Code Listing 1. The Application SQL Log

…
…

CREATE TABLE Contacts (_id INTEGER PRIMARY KEY, addrid NUMERIC, name TEXT);

SELECT name, sql FROM sqlite_master WHERE type=’table’ ORDER BY name;
PRAGMA TABLE_INFO(Contacts);
SELECT name, sql FROM sqlite_master WHERE type=’index’ ORDER BY name;
SELECT rowid, * FROM Contacts ORDER BY rowid;

CREATE TABLE Address (_id INTEGER PRIMARY KEY, city TEXT, country TEXT, line1 TEXT, line2 TEXT, region TEXT);
SELECT name, sql FROM sqlite_master WHERE type=’table’ ORDER BY name;
PRAGMA TABLE_INFO(Address);
PRAGMA TABLE_INFO(Contacts);

…
…

This SQL can come in very handy if you first create or play with the database creation from the GUI and then you want to apply these commands from within the code (for example, from the Java files in the Android application).

Other Features

This program also comes with the commands to create/delete indices (indexes), execute SQL, and other things like importing/exporting data and compacting the database. This is a simple utility; but it will do fine for simpler tasks (and, of course, it’s open source).