sqlite3 Command Basics - The Shortest Path to Trying SQL in the Terminal

2026-10-24 Reading time: About 10 min Difficulty: Beginner

What You'll Learn

• How to create a database as a single file with sqlite3
• The smallest SQL to make a table, add data, and pick it out with SELECT
• Why the numbers differ between COUNT(*) and wc -l
• How to pipe SQL results into sort / uniq, and how to turn a database back into text with .dump

1. Why sqlite3?

Conclusion: SQLite needs no server. The whole database is a single file, so you can handle it like any file you see with ls.

$ ls -l gym.db

-rw-r--r-- 1 user user 8192 Oct 24 02:18 gym.db

There is no gym.db yet, so you can try this ls -l after section 4. The owner name and the date depend on your environment.

2. How Do I Install sqlite3?

Conclusion: On Ubuntu and WSL, install it with apt. A Mac already has it. On both, check it with sqlite3 --version.

On Ubuntu / Debian-based systems

$ sudo apt update
$ sudo apt install sqlite3
$ sqlite3 --version

3.45.1 2024-01-30 16:01:20 e876e51a0ed5c5b3126f52e532044363a014bc594cfefa87ffb5b82257ccalt1 (64-bit)

sudo runs a command with administrator rights. If it asks for a password, type your own login password. The screen shows nothing as you type, not even *. Type it all the way and press Enter. The first number printed by sqlite3 --version is the version. In this example it is 3.45.1.

On Windows: open Ubuntu in WSL (a way to run Linux inside Windows). Then follow the same apt steps. If you do not have WSL yet, start with WSL2 Introduction.

On a Mac: macOS already comes with sqlite3. Check it with sqlite3 --version. You can try everything in this article with the sqlite3 that ships with macOS.

$ mkdir ~/sqlite-practice
$ cd ~/sqlite-practice

3. What Changes When I Start sqlite3?

Conclusion: Starting sqlite3 gym.db changes the prompt to sqlite>. Commands that begin with a dot are orders to sqlite3 itself.

$ sqlite3 gym.db

SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.
sqlite>

These are the five you use first.

Version 3.54.0 and later show a different prompt (see the 3.54.0 entry in the release history). The example in the official documentation is SQLite-3.54 ex1.db->. What you type is the same.

In the code examples, do not type the leading $ or sqlite>. Type only what comes after it. If you use the copy button, delete the leading sqlite> (or $ ) after pasting, then press Enter.

Sections 4 and 5 continue at the sqlite> prompt. If you stop halfway, leave with .quit and come back with sqlite3 gym.db. The table and its data stay in the file. Do not type the CREATE TABLE and INSERT from section 4 again (doing so adds the same rows again). .headers on and .mode column are cleared, so type those two again when you resume from section 5.

4. How Do I Make a Table and Add Data?

Conclusion: CREATE TABLE decides the shape of a table, and INSERT adds data one row at a time. Always end SQL with ;.

sqlite> CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT, city TEXT, age INTEGER);
sqlite> INSERT INTO members (name, city, age) VALUES ('Pip', 'Tokyo', 24);
sqlite> INSERT INTO members (name, city, age) VALUES ('Momo', 'Osaka', 31);
sqlite> INSERT INTO members (name, city, age) VALUES ('Kai', 'Tokyo', 19);

If it works, nothing is printed and the next sqlite> appears.

• INTEGER means a whole number, and TEXT means text (a string)
• Because of id INTEGER PRIMARY KEY, id gets the numbers 1, 2, 3 automatically
• Put text in single quotes ('), like 'Tokyo'

Check the result with dot-commands.

sqlite> .tables

members

sqlite> .schema members

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT, city TEXT, age INTEGER);

If you forget ; and press Enter, the prompt changes to ...>. It means "waiting for the rest". Type just ; and press Enter to run it.

5. How Do I Pick Out Only the Rows I Need?

Conclusion: Narrow with WHERE, order with ORDER BY, and cut with LIMIT. They play the same roles as grep, sort, and head.

First, print everything in the table. * means "all columns".

sqlite> SELECT * FROM members;

1|Pip|Tokyo|24
2|Momo|Osaka|31
3|Kai|Tokyo|19

sqlite> .headers on
sqlite> .mode column
sqlite> SELECT * FROM members;

id  name  city   age
--  ----  -----  ---
1   Pip   Tokyo  24
2   Momo  Osaka  31
3   Kai   Tokyo  19

In version 3.53.0 and later, a terminal session shows a boxed table from the start (per https://sqlite.org/climode.html and the 3.53.0 entry in the release history). Even with a newer version, type .headers on and .mode column in this article.

5-1. Narrow rows with WHERE (like grep)

sqlite> SELECT name, age FROM members WHERE city = 'Tokyo';

name  age
----  ---
Pip   24
Kai   19

Like grep Tokyo, only the rows that match the condition appear.

5-2. Order rows with ORDER BY (like sort)

sqlite> SELECT name, age FROM members ORDER BY age DESC;

name  age
----  ---
Momo  31
Pip   24
Kai   19

DESC means largest first. It is the same direction as sort -rn. Without DESC, the order is smallest first.

5-3. Cut the count with LIMIT (like head)

sqlite> SELECT name, age FROM members ORDER BY age DESC LIMIT 2;

name  age
----  ---
Momo  31
Pip   24

Like head -n 2, only the first two rows appear.

6. What Happens When I Count?

Conclusion: COUNT(*) counts the rows of a table. Write it to CSV and count with wc -l, and the column-name line adds one extra.

sqlite> SELECT COUNT(*) FROM members;

COUNT(*)
--------
3

sqlite> .mode csv
sqlite> .once members.csv
sqlite> SELECT * FROM members;
sqlite> .quit
$ wc -l members.csv

4 members.csv

.quit brought the prompt back to $, so the last wc -l runs as a normal Linux command.

$ cat members.csv

id,name,city,age
1,Pip,Tokyo,24
2,Momo,Osaka,31
3,Kai,Tokyo,19

To count without the column-name line, use tail -n +2 (print from line 2 to the end).

$ tail -n +2 members.csv | wc -l

3

Now it matches the 3 from COUNT(*). Settings such as .headers on go back to the defaults when you leave sqlite3.

7. How Do I Pipe SQL Results?

Conclusion: Writing sqlite3 FILE "SQL" runs it once and exits. The result is plain text, so it pipes straight into sort or uniq.

$ sqlite3 gym.db "SELECT city FROM members" | sort | uniq -c

      1 Osaka
      2 Tokyo

• sqlite3 gym.db "SELECT city FROM members" prints the city names one per line
• sort puts the same cities next to each other
• uniq -c counts identical lines

This form does not use .headers on, so no column-name line appears. According to the official documentation, results come out in "list mode" (separated by |).

7-1. Try interactive mode in the virtual terminal

The state where the sqlite> prompt from section 3 is showing is called "interactive mode". You type one line at a time and see the result right away.

You can also try interactive mode in the virtual terminal on this site. Open the lesson "Count the Members" and type the lines below in order. Do not type the leading $ or sqlite>.

$ sqlite3 gym.db

sqlite> SELECT COUNT(*) FROM members;

(one line with the number of members in the lesson's gym.db)

sqlite> .quit

The gym.db in the virtual terminal is prepared for the lesson, so the count differs from the 3 members in this article. When you count correctly, the lesson shows as complete. The one-shot form from section 7 (sqlite3 gym.db "SELECT COUNT(*) FROM members;") completes it as well. In the virtual terminal, the prompt is sqlite> whatever the version.

8. How Do I Turn a Database Back into Text?

Conclusion: .dump turns the whole database into SQL text. Feed that text to sqlite3 and you can rebuild the same database.

$ sqlite3 gym.db .dump > backup.sql
$ cat backup.sql

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT, city TEXT, age INTEGER);
INSERT INTO members VALUES(1,'Pip','Tokyo',24);
INSERT INTO members VALUES(2,'Momo','Osaka',31);
INSERT INTO members VALUES(3,'Kai','Tokyo',19);
COMMIT;

BEGIN TRANSACTION and COMMIT run the SQL between them as one unit. The PRAGMA line is a setting that sqlite3 added on its own. You can skip them for now.

$ sqlite3 new.db < backup.sql
$ sqlite3 new.db "SELECT * FROM members"

1|Pip|Tokyo|24
2|Momo|Osaka|31
3|Kai|Tokyo|19

< feeds the contents of a file to a command. The SQL in backup.sql ran from top to bottom, and new.db now has the same table.

9. Common Mistakes and Fixes

Conclusion: Most mistakes come from four things: semicolons, quotes, letter case, and file names. If an error appears, read it. If there is no error but the result looks wrong, look at the contents directly with SELECT * or ls -l.

The 'tokyo' case returns zero rows without an error. When a result is empty, first run SELECT * FROM members; to see how the values are stored.

10. Mini Exercises: Try It Yourself

Conclusion: Try ordering, counting, and piping with your own hands. The answers are inside the folded sections.

Exercise 1: Order by youngest first

Task: Enter with sqlite3 gym.db and type .headers on and .mode column. Then print every member's name and age, youngest first.

sqlite> SELECT name, age FROM members ORDER BY age;

name  age
----  ---
Kai   19
Pip   24
Momo  31

Exercise 2: Count the members in Osaka

Task: Count how many members have city set to Osaka, using SQL only.

sqlite> SELECT COUNT(*) FROM members WHERE city = 'Osaka';

COUNT(*)
--------
1

Exercise 3: Count Tokyo members with a pipe

Task: First, go back to $ with .quit. Then, without entering sqlite3, print the names of the members in Tokyo, and count those lines with a Linux command.

$ sqlite3 gym.db "SELECT name FROM members WHERE city = 'Tokyo'" | wc -l

2

11. Review

Today's 3-Line Summary

1. SQLite needs no server, and the database is one file you can see with ls
2. SELECT / WHERE / ORDER BY / LIMIT match up with grep / sort / head
3. COUNT(*) and wc -l count different things. Watch the first line of a CSV (the column names)