Using sqlite3

sqlite3 is the tool I reach for constantly, whether I’m debugging an app’s local database, exploring a dataset, or just testing a quick SQL idea before committing it to real code. It’s the official command-line shell for SQLite, and once you know your way around it, it becomes an incredibly fast environment for working with data. In this article, I’ll walk through everything I use sqlite3 for on a regular basis — from the basics to some of its lesser-known but genuinely useful features.

Getting Started

Opening a database is as simple as:

sqlite3 mydata.db

If mydata.db doesn’t exist yet, SQLite creates it the moment you write your first table to it. If you just want a scratch database that disappears when you close the session, use:

sqlite3

with no filename, which opens a temporary in-memory database, or explicitly:

sqlite3 :memory:

The Dot Commands

One of the first things that confused me about sqlite3 is that it has two different kinds of commands: regular SQL statements, and special “dot commands” that control the shell itself rather than the database. Dot commands don’t end with a semicolon, and they’re not SQL — they’re shell-specific utilities.

.help

This lists every dot command available, which is genuinely worth reading through once.

Listing Tables and Schema

.tables

Shows every table in the current database.

.schema users

Shows the exact CREATE TABLE statement used to define the users table, which is one of the commands I use most often when I forget the exact column definitions of a table I’m working with.

Changing Output Format

By default, sqlite3 prints query results in a fairly plain format. I almost always improve this immediately:

.mode column
.headers on

This gives me nicely aligned columns with header names, which is dramatically easier to read than the raw default output.

Other useful modes:

.mode csv
.mode json
.mode markdown
.mode box

.mode box in particular produces genuinely nice-looking bordered tables directly in the terminal.

.mode box
SELECT * FROM users LIMIT 5;

Importing and Exporting Data

Importing a CSV

.mode csv
.import data.csv my_table

This is one of my favorite features — I can take a raw CSV file and have it queryable as a real SQL table within seconds, without writing any import code.

Exporting Query Results to CSV

.mode csv
.headers on
.output results.csv
SELECT * FROM orders WHERE total > 100;
.output stdout

The .output command redirects everything the shell would normally print to a file instead — and .output stdout switches it back to printing on the terminal again.

Dumping an Entire Database

.dump

This prints the complete set of SQL statements needed to recreate the database from scratch — every CREATE TABLE, every INSERT. I use this constantly for quick backups:

sqlite3 mydata.db .dump > backup.sql

And restoring is just as simple:

sqlite3 restored.db < backup.sql

Running SQL From the Command Line Directly

You don’t always need to open an interactive session. You can pass SQL directly:

sqlite3 mydata.db "SELECT COUNT(*) FROM users;"

This is extremely useful in shell scripts, cron jobs, or quick one-off checks without opening a full interactive session.

Executing a Script File

sqlite3 mydata.db < setup.sql

I use this pattern to apply schema migrations or seed data consistently — setup.sql might contain a full sequence of CREATE TABLE and INSERT statements to run in order.

Useful Introspection Commands

.databases

Shows the currently attached database files (SQLite supports attaching multiple database files to a single session).

.indexes products

Lists indexes defined on the products table.

PRAGMA table_info(products);

This gives me a detailed breakdown of every column, its type, whether it’s nullable, and its default value — genuinely one of the most useful introspection commands available.

Timing Queries

.timer on

Once enabled, every query you run prints its execution time, which I use constantly when comparing the performance of different query approaches or checking whether an index actually helped.

Explaining Query Plans

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE user_id = 5;

This shows how SQLite intends to execute a query — whether it’s using an index scan or a full table scan — which is essential for diagnosing slow queries.

Attaching Multiple Databases

sqlite3 lets you work across multiple database files in a single session:

ATTACH DATABASE 'archive.db' AS archive;

SELECT * FROM main.users
UNION
SELECT * FROM archive.users;

I’ve used this pattern when migrating data between an active database and an older archive file without writing a separate script.

Editing Multi-Line Statements

By default, sqlite3 waits for a semicolon before executing a statement, which means you can comfortably write multi-line SQL directly in the shell:

SELECT
    username,
    email
FROM users
WHERE created_at > '2024-01-01';

Quitting the Shell

.quit

or simply pressing Ctrl+D on most systems.

A Typical Session I Might Run

sqlite3 shop.db
.headers on
.mode box
.tables
.schema orders
SELECT * FROM orders ORDER BY total DESC LIMIT 5;
.quit

That short sequence — check the tables, inspect a schema, run a quick query — covers probably 80% of what I actually use sqlite3 for day to day.

Best Practices

• Turn on .headers on and a readable .mode (like column or box) immediately in every session — the default output is much harder to read.
• Use .schema liberally instead of trying to remember table structures from memory.
• Prefer .dump for quick, reliable backups of small-to-medium databases rather than manually copying files while the database might be in use.
• Use EXPLAIN QUERY PLAN before assuming a slow query needs an index — verify it first.
• Script repetitive setup tasks into .sql files and run them with input redirection instead of retyping commands interactively each time.

Frequently Asked Questions

What’s the difference between a dot command and a SQL statement? Dot commands control the sqlite3 shell itself (like changing output format or listing tables) and don’t require a semicolon. SQL statements are sent to the database engine and always end with a semicolon.

How do I see the exact structure of a table? Use .schema table_name to see its full CREATE TABLE definition, or PRAGMA table_info(table_name); for a structured column-by-column breakdown.

Can I import a CSV file directly into SQLite? Yes, using .mode csv followed by .import filename.csv table_name, which is one of the fastest ways to get tabular data into a queryable format.

How do I back up an SQLite database safely? The .dump command or the dedicated .backup command are both reliable options; for a live database under write activity, .backup is generally the safer choice since it handles consistency more carefully.

Is sqlite3 the same thing as the SQLite library? Not exactly — sqlite3 is the official command-line shell built on top of the SQLite library. The library itself is what gets embedded into applications; the shell is just one particular tool for interacting with it directly.

Wrapping Up

sqlite3 might look like a bare-bones terminal tool at first glance, but once I learned its dot commands and a handful of good habits, it became one of the fastest ways I have to explore, debug, and manipulate data. Whether I’m inspecting an app’s local database, importing a CSV for quick analysis, or scripting a repeatable setup process, it’s rarely more than a few keystrokes away from giving me exactly what I need.