sqlite3

Start an interactive shell with a new database

$ sqlite3

$ sqlite3 [path/to/database.sqlite3]

$ sqlite3 [path/to/database.sqlite3] '[SELECT * FROM some_table;]'

sqlite3 [OPTIONS] [DATABASE_FILE] [SQL_COMMAND]

Examples:
sqlite3 mydatabase.db
sqlite3 mydatabase.db "SELECT * FROM users;"
sqlite3 -batch mydatabase.db ".dump"

-batch
    Run in batch mode, suppressing the interactive prompt.

-bail
    Stop and exit after encountering an error.

-column
    Set output mode to aligned columns (default for interactive mode).

-csv
    Set output mode to CSV (Comma Separated Values).

-echo
    Echo SQL statements before executing them.

-header
    Display column headers in output.

-init FILE
    Read and execute initial commands from the specified FILE.

-json
    Set output mode to JSON format.

-line
    Set output mode to one value per line, labeled with column names.

-list
    Set output mode to list, with field separator.

-markdown
    Set output mode to Markdown table format.

-noheader
    Suppress column headers in output.

-nullvalue STRING
    Print STRING for NULL values instead of an empty string.

-readonly
    Open the database file in read-only mode.

-separator STRING
    Set the output field separator to STRING.

-stats
    Print database statistics after each command.

-table
    Set output mode to a fancy ASCII art table.

-version
    Display the SQLite library version and exit.

The sqlite3 command is the official command-line interface (CLI) for interacting with SQLite database files. It provides a simple, yet powerful environment to create new databases, open existing ones, execute SQL commands, manage tables, import/export data, and perform various administrative tasks. Unlike traditional client-server database systems, SQLite operates directly on disk files, making sqlite3 a lightweight and highly portable tool. It's widely used for scripting, debugging, application development, and general database administration where a full-fledged database server is unnecessary. Its capabilities include support for standard SQL features, transactions, views, and triggers, all managed through a single executable.

Concurrency: While SQLite supports concurrent reads and multiple writers via write-ahead logging (WAL), heavy, simultaneous write operations from many processes can lead to contention and potential performance bottlenecks. It is not designed as a high-concurrency server for thousands of simultaneous clients. Network Access: SQLite is a file-based database and does not provide direct network access or a server process. Accessing a SQLite database over a network file system can lead to issues if the file system does not support proper locking semantics.

Within the interactive sqlite3 shell, special commands prefixed with a dot ('.') are available. These 'dot-commands' are not SQL statements but shell-specific directives to control the environment or perform utility operations. Examples include:
.help: display help message
.tables: list tables
.schema TABLE_NAME: show schema for a table
.quit: exit the shell
.dump: dump the entire database in SQL format
.read FILE: read SQL from a file

sqlite3 is frequently used for:
Local Application Data: Storing data for desktop or mobile applications.
Development & Testing: Quick database setup for development and unit testing without needing to install a separate database server.
Scripting: Automating database operations in shell scripts.
Data Analysis: Performing ad-hoc queries on structured data files.
Embedded Systems: Its small footprint and self-contained nature make it ideal for resource-constrained devices.

SQLite was created by D. Richard Hipp in 2000, initially designed for the US Navy for a highly robust, server-less database. The sqlite3 command-line shell has been an integral part of the SQLite project from its early days, evolving alongside the library to provide a versatile and indispensable tool for interacting with this unique embedded database system. Its philosophy of simplicity, self-containment, and reliability has driven its widespread adoption across countless applications and operating systems.

psql(1), mysql(1), mongo(1), db_dump(1)