SQLite3 Command Line: Usage & Examples

The sqlite3 command-line tool lets you create, query, and manage SQLite database files directly from a terminal. You start it with sqlite3 database.db, then type SQL statements or special dot-commands to interact with the data. This article explains how to use it effectively, from basic setup to advanced scripting.

What Is SQLite and Why Use the Command Line?

SQLite is a self-contained, serverless, zero-configuration relational database engine. Unlike MySQL or PostgreSQL, it doesn’t run as a separate process; it reads and writes to a single file. The sqlite3 command-line program is the official way to interact with that file from a shell. It’s included by default on macOS and most Linux distributions, and it’s available for Windows too. The command line is especially useful for quick data inspection, running scripts, migrating data, and automating tasks without needing a GUI.

Getting Started: Opening a Database

To open (or create) a database, run:

sqlite3 mydatabase.db

If the file doesn’t exist, SQLite creates it. If you run sqlite3 without a filename, you get an in-memory database that disappears when you exit. Once inside, you’ll see the sqlite> prompt. Type .help to see a list of dot-commands, and .quit (or press Ctrl+D) to exit.

Essential Dot-Commands

Dot-commands are special instructions that start with a period. They control the tool’s behavior, not the database itself. Here are the ones you’ll use most:

• .tables – list all tables in the database.
• .schema [table] – show the CREATE statement for a table (or all tables).
• .headers on – display column names in query results.
• .mode column – format output in aligned columns (other modes: list, csv, json).
• .import file.csv table – import CSV data into a table.
• .output file.txt – send query results to a file instead of the screen.
• .exit – leave the tool.

These commands make the command line far more practical for everyday work.

Running SQL Queries

You can type any SQL statement directly at the prompt. For example:

SELECT * FROM users WHERE age > 30;

SQLite supports standard SQL: SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, JOINs, and more. You can also run multiple statements in one line (separated by semicolons). To execute a SQL file, use .read script.sql or pipe it in from the shell: sqlite3 db.sqlite < script.sql. This is handy for running migrations or seed data.

Common Use Cases and Examples

Here are a few real-world scenarios where the command line shines:

• Quick data inspection: sqlite3 my.db "SELECT * FROM orders LIMIT 10;" – run a query without entering interactive mode.
• Exporting data: Use .mode csv and .output data.csv to dump a table to CSV for use in a spreadsheet.
• Backing up: The .backup command creates a consistent backup file: sqlite3 my.db ".backup backup.db".
• Checking integrity: Run PRAGMA integrity_check; to verify the database file isn’t corrupted.

Command-Line Options and Flags

You don’t always need to enter interactive mode. The tool accepts SQL as a command-line argument:

sqlite3 my.db "SELECT * FROM users;"

Useful flags include:

• -header – same as .headers on.
• -csv – output in CSV format.
• -json – output as JSON (available in newer versions).
• -cmd – run a dot-command before executing SQL (e.g., -cmd ".mode column").
• -init file – read commands from a file before starting.
• -version – print the SQLite version and exit.

Combining these lets you write one-liners like:

sqlite3 -header -column my.db "SELECT * FROM products;"

Scripting and Automation

Because it’s a command-line tool, sqlite3 fits perfectly into shell scripts. You can loop over databases, generate reports, or integrate with cron jobs. For example, to back up all databases in a directory:

for db in *.db; do sqlite3 "$db" ".backup backup_$db"; done

You can also use the --batch flag to suppress interactive prompts, which is useful in pipelines. And if you need to pass values safely, use parameterized queries within your programming language rather than string concatenation in the shell.

Common Problems and How to Fix Them

Here are a few issues you might run into:

• “Unable to open database file” – the directory doesn’t exist or you lack write permission. Check the path and permissions.
• Database is locked – another process has the file open for writing. SQLite allows only one writer at a time. Wait or use PRAGMA busy_timeout;.
• Output looks messy – switch to column mode and turn on headers: .mode column and .headers on.
• Accidentally deleted data – SQLite doesn’t have an undo command. Restore from a backup, or if you haven’t committed, use ROLLBACK;.
• Importing CSV with commas in fields – use .mode csv before importing to handle quoting correctly.

Frequently Asked Questions

How do I exit the sqlite3 prompt?

Type .quit or .exit, or press Ctrl+D (on Unix) or Ctrl+Z then Enter (on Windows).

Can I use sqlite3 to create a database without entering interactive mode?

Yes. Simply run sqlite3 new.db "CREATE TABLE t(id INT);" – the file is created automatically if it doesn’t exist.

How do I see all tables in a database?

Use the dot-command .tables. For more detail, .schema shows the CREATE statements.

What’s the difference between sqlite3 and SQLite the library?

sqlite3 is a command-line front-end that uses the SQLite library. The library is what other programs link to; the command line is just one way to talk to it.

How do I import a CSV file into a table?

First create the table with the correct schema, then run .mode csv and .import file.csv tablename. Make sure the CSV has no header row, or use --skip 1.

Is sqlite3 safe for production use?

SQLite is extremely reliable and used in production by many applications. However, it’s designed for moderate write concurrency; if you have many simultaneous writers, a client-server database may be more appropriate.

Related

• Borderlands 4: Defeat Dr. Elena Smith Boss
• TV-B-Gone: How It Works, Range & TV Compatibility
• Web Page Hijacking: Types & How It Happens