SQLite - Commands

SQLite - Command-Line Interface (CLI) Commands

The SQLite command-line interface (CLI) is a powerful tool for managing SQLite databases. It features a set of meta-commands, often called “dot-commands” because they begin with a period (.). These commands are not SQL; they are instructions for the CLI tool itself and must not be terminated by a semicolon (;).

To start a session, open your terminal and run the sqlite3 command, optionally providing a database file name.

$ sqlite3 mydatabase.db
SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.
sqlite>

You can get a full list of available dot-commands by typing .help.

sqlite> .help

Most Commonly Used Dot-Commands

While there are many commands, here are some of the most essential ones for day-to-day development:

Command	Description & Modern Usage
.databases	List all attached databases, their names, and file paths.
.tables ?PATTERN?	List table names. Use a pattern like .tables %log% to find specific tables.
.schema ?TABLE?	Show the CREATE statement for a table, index, or view. Essential for understanding database structure.
.dump ?TABLE?	Dump the entire database or a specific table as SQL statements. Great for backups and migrations.
.import FILE TABLE	Import data into a table. Use with the --csv flag for robust CSV file importing.
.output FILE	Redirect subsequent command output to a file. Use .output stdout to switch back to the screen.
.mode MODE	Change the output format. column is good for readability. csv, json, and markdown are excellent for interoperability.
.header on|off	Toggle the display of column headers in the output.
.explain query plan …	Show how SQLite plans to execute a query. Crucial for performance tuning.
.read FILENAME	Execute SQL commands from a file. Useful for running scripts.
.quitor.exit	Exit the SQLite CLI.

Practical Workflow: Inspecting and Exporting Data

Let’s combine a few commands to perform a common task: querying data and exporting it to a JSON file.

sqlite> .mode json
sqlite> .header on
sqlite> .output employees.json
sqlite> SELECT id, first_name, department FROM employees WHERE age > 30;
sqlite> .output stdout -- Reset output to the console
sqlite> .quit

After running these commands, a file named employees.json will be created with the query results formatted as a JSON array, ready to be used in a web application or API.

The Schema Table: sqlite_schema

The internal schema of a database is stored in a special table named sqlite_schema. While the older sqlite_master still exists for backward compatibility, sqlite_schema is the modern standard. You can query it directly to get metadata about your database objects.

sqlite> .schema sqlite_schema

CREATE TABLE sqlite_schema(
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);

For example, to list all triggers in your database:

SELECT name, sql FROM sqlite_schema WHERE type = 'trigger';

Beyond the CLI: GUI Tools

While the CLI is powerful, several excellent graphical user interface (GUI) tools can enhance productivity, especially for complex analysis and visualization. Popular choices include:

• DB Browser for SQLite: A free, open-source, and user-friendly tool.
• DBeaver: A universal database tool that supports SQLite and many other databases.
• JetBrains DataGrip: A professional, multi-engine database IDE with advanced features.