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:
- ess queue ell light
- 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. macOS comes with the sqlite3 command already installed, and on most Linux distributions it’s one package away (for example, sudo apt install sqlite3 on Debian and Ubuntu). Windows doesn’t include it. You can install it with winget:
winget install SQLite.SQLiteCode language: plaintext (plaintext)Or download the command-line tools from the SQLite download page. If you have Python installed, you can also skip installing anything, as I’ll show you further down.
To open the SQLite shell, we must enter the sqlite3 command in a terminal or command prompt. Once it’s installed, this works the same on all the operating systems (Windows, macOS, Linux). The version number you see depends on your installation:
C:\> sqlite3
SQLite version 3.51.0 2025-06-12 13:14:41
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.51.0 2025-06-12 13:14:41
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.51.0 2025-06-12 13:14:41
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.51.0 2025-06-12 13:14:41
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
...
.headers on|off Turn display of headers on or off
...
.mode ?MODE? ?OPTIONS? 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 shows 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, which also shows the column names:
sqlite> .mode column
sqlite> select * from customers;
name age
---- ---
Erik 40
Mary 53Code language: SQL (Structured Query Language) (sql)Recent versions of SQLite also have the box and table modes, which draw lines around the data. I like .mode box best:
sqlite> .mode box
sqlite> select * from customers;
┌──────┬─────┐
│ name │ age │
├──────┼─────┤
│ Erik │ 40 │
│ Mary │ 53 │
└──────┴─────┘Code language: SQL (Structured Query Language) (sql)In the plain list mode (the default), you can turn on column names with .headers on. To make your favorite settings permanent, put them in a file called .sqliterc in your home directory.
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> .mode column
sqlite> select * from customers limit 1;
name age
---- ---
Erik 40 Code language: SQL (Structured Query Language) (sql)To skip rows, add an OFFSET. Remember computers start counting at zero, so if we only want to see the 2nd row, we skip one row and return one row:
sqlite> select * from customers limit 1 offset 1;
name age
---- ---
Mary 53 Code language: SQL (Structured Query Language) (sql)So the syntax is:
sqlite> select * from TABLE limit NUMBER_OF_ROWS offset ROWS_TO_SKIP;Code language: SQL (Structured Query Language) (sql)You may also run into the short form limit 1, 1, which means the same thing: skip one row, return one row. Note that the order of the numbers is reversed there, which is easy to get wrong. The OFFSET form is clearer, and it also works in other databases like PostgreSQL.
Without an ORDER BY clause, the database doesn’t promise any particular order of the rows. For browsing that’s fine, but if the order matters, add something like order by name before the LIMIT.
No sqlite3 command? Use Python
Python comes with SQLite built in, through the sqlite3 module. Since Python 3.12, that module also has a small shell of its own. So if you have Python but not the sqlite3 command, you can open a database like this:
$ python3 -m sqlite3 customers.db
sqlite3 shell, running on SQLite version 3.54.1
Connected to 'customers.db'
Each command will be run using execute() on the cursor.
Type ".help" for more information; type ".quit" or CTRL-D to quit.
sqlite> select * from customers;
('Erik', 40)
('Mary', 53)Code language: plaintext (plaintext)On Windows, use py -m sqlite3 customers.db. This shell is much more basic than the real thing. It shows rows as Python tuples, and it doesn’t know commands like .tables and .schema. You can get the same information with a query, though:
sqlite> select name, sql from sqlite_schema where type = 'table';
('customers', 'CREATE TABLE customers(name text, age int)')Code language: SQL (Structured Query Language) (sql)You can also pass a query as an extra argument, run it, and get the result right away: python3 -m sqlite3 customers.db "select * from customers". To use SQLite from your own Python code, have a look at the official documentation of the sqlite3 module.
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, and you learned to use the SELECT statement to see the table data. For everything else the shell can do, see the official SQLite CLI documentation.