SQLite CLI Overview

The sqlite3 shell is your direct control panel for SQLite.

You can open a database file, execute SQL, inspect metadata, and automate simple workflows without writing application code.

Core Concepts

How the CLI Works

flowchart LR
  A[You type command] --> B{Starts with dot?}
  B -->|Yes| C[Handled by CLI shell]
  B -->|No| D[Sent to SQLite SQL engine]
  C --> E[Shell output]
  D --> F[Query result or data change]

SQL vs Dot Commands

Command type	Example	Ends with ;?	Purpose
SQL statement	SELECT 1;	Yes	Query/change data
Dot command	.tables	No	Control shell behavior

Interactive Mode

# Open sqlite3 shell (in-memory database).
sqlite3 ":memory:"

Expected output
SQLite banner and sqlite> prompt appear.

One-Shot Mode

# Run one SQL statement without entering interactive shell.
sqlite3 ":memory:" "SELECT 'one-shot' AS mode;"

mode
one-shot

Code Examples

-- Run this after opening sqlite3 interactively.
-- SQL statements use a semicolon terminator.
SELECT 'CLI is ready' AS status;

status
CLI is ready

SQLite-Specific Nuances

SQLite Nuance

Dot commands (like .tables) are features of the sqlite3 shell, not SQL syntax.

They work in the CLI but not in application SQL APIs.

Common Pitfalls / Best Practices

Pitfall

Adding ; after dot commands (for example, .tables;).

Dot commands should not end with semicolons.

Best Practice

Use interactive mode for learning and one-shot mode for scripts/automation.

Quick Challenge

Open an in-memory shell and run one SQL statement that returns hello as msg.

View Solution

SELECT 'hello' AS msg;

msg
hello