The SQLite shell

In the last lesson, you installed SQLite and created your first database file. You also saw the sqlite> prompt for a brief moment before quitting. That prompt is where you will spend most of your time in this course. It is where you will experiment, test queries, and inspect your data, so before we write any real SQL, let’s get comfortable with it. A few minutes here will save you a lot of confusion later.

Open your database again (or create one if you have not already):

sqlite3 myapp.db

You are now at the sqlite> prompt. There are two kinds of things you can type here, and it is important to know the difference.

SQL statements vs. dot-commands

The first kind is SQL statements. These are things like SELECT, CREATE TABLE, and INSERT. SQL statements must end with a semicolon (;). The semicolon tells SQLite “I am done typing, run this now.”

The second kind is dot-commands. These start with a period, like .tables, .quit, and .help. Dot-commands do not need a semicolon. They are instructions for the shell itself, not for the database.

Here is a common mistake that trips up beginners. If you type a SQL statement and forget the semicolon, the shell does not run it. Instead, it shows a ...> continuation prompt. It is waiting for you to finish the statement.

sqlite> SELECT * FROM users
   ...>

What do you do if you see ...> and you do not know why? Just type ; and press Enter to finish the statement. Or type .quit to exit and start over. This happens to everyone at first, so do not worry about it.

sqlite> SELECT * FROM users
   ...> ;

Make the output readable

By default, SQLite’s output is hard to read. It smashes columns together separated by pipes, with no headers. Let’s fix that right away.

.headers on
.mode column

.headers on tells the shell to show column names above the results. .mode column aligns the output in neat columns. The difference is dramatic.

Before:

1|[email protected]|Alice
2|[email protected]|Bob

After:

id  email              name
--  -----------------  -----
1   [email protected]  Alice
2   [email protected]    Bob

Much better. You will want these settings on every time you open the shell.

Save your preferences

You can make these settings permanent so you do not have to type them every time. Create a file called .sqliterc in your home directory:

echo '.headers on' >> ~/.sqliterc
echo '.mode column' >> ~/.sqliterc

The >> means “append to the file.” Use >>, not >. A single > would overwrite the file, erasing anything already in it.

Everything in .sqliterc runs automatically every time you open the sqlite3 shell. Now your output will always be readable without any extra effort.

Essential dot-commands

Here are the commands you will use most often:

.tables            List all tables in the database
.schema            Show the CREATE statements for all tables
.schema users      Show the CREATE statement for just the 'users' table
.headers on        Show column names in output
.mode column       Align output in columns
.mode json         Output results as JSON
.quit              Exit the shell

[!TIP] Type .help to see every available dot-command. There are many, but the ones above cover 90% of what you need day to day.

Running SQL from the command line

You do not have to enter the interactive shell every time you want to run a query. You can pass SQL directly from your terminal:

sqlite3 myapp.db "SELECT count(*) FROM users;"

This runs the query and prints the result without entering the interactive prompt. It is useful for quick checks when you already know exactly what you want to ask.

You can also pipe in a SQL file:

sqlite3 myapp.db < setup.sql

This reads every SQL statement from setup.sql and executes them against your database. We will use this pattern heavily when we get to migrations.

Exporting data

The shell can export query results in various formats. This is handy when you need to share data or feed it into another tool:

sqlite3 -json myapp.db "SELECT * FROM users;" > users.json
sqlite3 -csv myapp.db "SELECT * FROM users;" > users.csv

The -json flag outputs results as a JSON array. The -csv flag outputs them as comma-separated values. The > redirects the output into a file.

Importing CSV

You can also go the other direction and import data from a CSV file. Inside the shell:

.mode csv
.import data.csv users

This reads data.csv and inserts each row into the users table, matching columns by position. The table must already exist with the right columns, otherwise the import will fail.

Exercises

Exercise 1: Open sqlite3 myapp.db, run .tables, and run .schema. If you have not created any tables yet, both will return nothing. That is expected.

Exercise 2: Set up your .sqliterc file with .headers on and .mode column. Close and reopen the shell to verify the settings are applied automatically.

Exercise 3: Run a query directly from the command line without entering the interactive shell: sqlite3 myapp.db "SELECT 1 + 1;". You should see 2.

You can now open a database, type SQL, run dot-commands, and read the output without squinting. That is everything you need to start putting real data in. In the next lesson, we will stop poking around an empty database and create our first table.