SQLite is a terrific database library, and it’s easy to use the SQLite command line tool to browse, inspect, and manipulate a database. This article will teach you how to open a database, view its tables and schemas, and browse through the data that’s inside these tables.
Table of Contents
Command Line Browsing of SQLite databases
We can use the SQLite shell to browse SQLite databases. If you haven’t done so already, install SQLite first.
To open the SQLite shell, we must enter the sqlite3 command in a terminal or command prompt. This works the same on all the operating systems (Windows, MacOS, Linux):
C:\> sqlite3
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
Code language: Bash (bash)When opening SQLite without any arguments, it will create an in-memory database. As SQLite points out to use, we can open a database by using the .open FILENAME command. If you already have a file-based SQLite database, however, it’s easier to directly open it like so:
C:\> sqlite3 customers.db
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
sqlite> Code language: Bash (bash)Create an SQLite database on Command Line
If you don’t have a database, let’s first create one from scratch so we have something to work with. We need to start SQLite with the sqlite3 command again to get an SQLite shell. We can directly give our database a name by giving it as the first argument:
$ sqlite3 customers.db
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints
sqlite>Code language: Bash (bash)Note that I like to use the .db file extension. You can pick any filename you want though, but I recommend using the .db extension to clearly mark this as a database file.
Now create a table and insert some data by entering the following lines:
sqlite> create table customers(name text, age int);
sqlite> insert into customers values('Erik', 40);
sqlite> insert into customers values('Mary', 53);Code language: SQL (Structured Query Language) (sql)We can see the results with a simple SELECT statement:
sqlite> select * from customers;
Erik|40
Mary|53Code language: SQL (Structured Query Language) (sql)To close the shell, press control+D, which sends the end-of-file character to your terminal, or type in the .exit command.
If you inspect the filesystem, you’ll see that SQLite has created the customers.db file for us.
Open a database on the command line
Now we have an existing database to work with. Let’s start by opening this SQLite database in a file on the filesystem. We’ll work with our previous database called customers.db. We can open it with the sqlite3 command like this:
C:\> sqlite3 customers.db
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
sqlite> Code language: plaintext (plaintext)Now we are all set up to browse our SQLite database!
Useful SQLite commands
SQLite opens the database without much fanfare, but it does print a helpful message to the screen. We can enter .help for usage hints, but it will output a large list of commands, most of which we don’t care about at this point. In the extract below, I’ve snipped large parts of the output and only included the commands that are useful when using SQLite as a browser:
sqlite> .help
...
.databases List names and files of attached databases
...
.mode MODE ?TABLE? Set output mode
.schema ?PATTERN? Show the CREATE statements matching PATTERN
.show Show the current values for various settings
...
.tables ?TABLE? List names of tables matching LIKE pattern TABLE
...
Code language: plaintext (plaintext)SQLite .databases command
With .databases we can see which databases are attached to this session. You can in fact open multiple databases at once, and copy data from one to another or join data from tables that are in different databases.
When entering this command, it will output something like this:
sqlite> .databases
main: C:\customers.db r/w
Code language: SQL (Structured Query Language) (sql)SQLite .tables command
The .tables command show all available tables. In our case, the output of this command looks like this:
sqlite> .tables
customersCode language: SQL (Structured Query Language) (sql)SQLite .schema command
The .schema command prints the CREATE statement that was used to create tables. When run without arguments, it will print the schema for all tables. You can print the schema for a specific table by supplying its name:
sqlite> .schema customers
CREATE TABLE customers(name text, age int);Code language: SQL (Structured Query Language) (sql)Browsing SQLite tables on the command line
At this point, we know which tables there are, and we even had a look a the schema behind those tables. If you want to look at what’s inside these tables, you need to use SQL syntax. If you’re unfamiliar with SQL syntax, you can use and tweak the examples I’ll provide below to safely browse through the data. None of these commands alter the database.
SELECT * FROM table_name;
The SELECT statement ‘selects’ data from a table. We can give it all kinds of options to filter the output. The easiest and quickest way to get started, is by selecting everything there is to see in the table. We do so by using the wildcard symbol *:
sqlite> select * from customers;
Erik|40
Mary|53Code language: SQL (Structured Query Language) (sql)What you see in the output, are rows from the table. However, the output is not that clear. We can fix that with the .mode column command:
sqlite> .mode column
sqlite> select * from customers;
name age
---- ---
Erik 40
Mary 53Code language: SQL (Structured Query Language) (sql)Limiting the number of rows
If there’s a lot of data in the table, you may want to limit the number of rows that you get to see. This is easily done by adding a LIMIT to the end of your select statement:
sqlite> select * from customers limit 1;
Erik|40Code language: SQL (Structured Query Language) (sql)LIMIT accepts two values, so the above command is actually a shorthand for limit 0, 1. Meaning: limit the number of rows, start at row 0, and return 1 row.
So the syntax for LIMIT is:
sqlite> select * from TABLE limit START_ROW, NUMBER_OF_ROWS;Code language: SQL (Structured Query Language) (sql)Remember computers start counting at zero, so if we only want to see the 2nd row, we can use this:
sqlite> select * from customers limit 1, 1;
name age
---- ---
Mary 53 Code language: SQL (Structured Query Language) (sql)Conclusion
You’ve learned how to create, open, and browse SQLite databases. We’ve looked at some of the SQLite commands that help us inspect the database and its tables. Finally, you learned to use the SELECT statement to see the data in tables.