When people in our profession think of a database, many will think of Oracle, MySQL, Postgresql, perhaps? But what about the database that is used more than all the others combined? Let’s talk about SQLite, the database that is in your pocket! In this article, I’ll explain what SQLite is and how to create and browse SQLite databases on the command line.

What is SQLite?

SQLite is a library that implements a SQL database engine. It has several properties that make it attractive to use:

• Written in C: it’s extremely fast
• It’s small: SQLite runs on small devices (like phones and microwaves)
• Proven reliability: the library is used daily by billions of people since almost every phone app uses SQLite in one way or another
• It’s full-featured: despite its name, SQLite comes with an impressive number of features.

It’s a library

Unlike other databases, SQLite is serverless. After all, it’s a library! SQLite can be shipped with your code and stores its data in files on the filesystem.

SQLite is often included in other languages. Python offers it out of the box! For this reason, the popular Django web framework will by default use a SQLite database to get you started quickly. But you can also use SQLite to store and analyze data without the hassle of setting up a database, and I think people should use it more often!

How to pronounce SQLite

Nothing is more confusing and sometimes even embarrassing than pronouncing stuff wrongly. There are two ways to pronounce SQLite, and both are considered OK.

SQL is short for Structured Query Language and is often pronounced as “ess queue ell” or “sequel.” Hence, SQLite can be pronounced as either:

1. ess queue ell light
2. sequel light

Pick whichever style you like; I prefer the last one.

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. Note that you can usually use your package manager on Linux distros. On Mac, you might want to use something like Homebrew.

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: plaintext (plaintext)

When opening SQLite without any arguments, it will create an in-memory database. As SQLite points out, 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: plaintext (plaintext)

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: plaintext (plaintext)

Note that I like to use the .db file extension. You can pick any filename you want, but I recommend using the .db extension to 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: plaintext (plaintext)

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 now. 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: plaintext (plaintext)

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: plaintext (plaintext)

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: plaintext (plaintext)

Browsing SQLite tables on the command line

At this point, we know which tables there are, and we even looked at 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 browse through the data safely. 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: Python (python)

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 table data.