An Overview of Dot Commands in SQLite

Posted on by Ian

SQLite is a lightweight, self-contained, and highly reliable SQL database engine used widely for developing and testing small-scale applications. One unique feature of SQLite is the set of “dot commands” used in the SQLite command-line interface (CLI) to execute various actions related to database management, navigation, and configuration.

These dot commands are not part of SQL syntax itself but provide a convenient way to manage and interact with SQLite databases.

What Are Dot Commands in SQLite?

Dot commands are a set of instructions specific to the SQLite CLI, starting with a period (.) symbol, or “dot”, hence the name “dot commands.”

Unlike standard SQL statements, dot commands are exclusive to the command-line shell and are not compatible with embedded SQL queries in programming languages. They perform administrative and utility tasks such as displaying database schemas, configuring output format, managing database connections, and much more.

For example, the command .tables lists all tables in a database, and .schema shows the schema of a specified table. These commands simplify tasks that might otherwise require a more complex SQL query or additional code.

Commonly Used Dot Commands

Below are some of the most frequently used dot commands in SQLite, each serving a different purpose. Each dot command can be run directly in the SQLite CLI for easy interaction with the database.

.help

  • Purpose: Displays a list of all available dot commands.
  • Usage: This command is helpful to understand what options are available.
.help ?-all? ?PATTERN?

.databases

  • Purpose: Shows a list of databases currently attached to the SQLite session.
  • Usage: Useful when working with multiple databases in a single session, as it lists database names, file paths, and the database handle.
.databases

.tables

  • Purpose: Lists all tables and views in the connected database.
  • Usage: Helpful for quickly verifying the existence of tables within the current database.
.tables ?TABLE?

.schema

  • Purpose: Displays the SQL CREATE statements that define tables and indexes in the database.
  • Usage: You can use this command to view the schema of a specific table by appending the table name.
.schema ?PATTERN?

.mode

  • Purpose: Sets the output mode for query results, such as column, CSV, line, and more.
  • Usage: Commonly used when exporting data or changing the display format of query results.
.mode MODE ?OPTIONS?

.headers

  • Purpose: Toggles the display of column headers in query output.
  • Usage: Useful for making results easier to read when headers are needed or hiding them for a more compact display.
.headers on|off

.exit or .quit

  • Purpose: Exits the SQLite session.
  • Usage: Both commands can be used to close the command-line session.
.exit ?CODE?
.quit

Example: Using Dot Commands in SQLite

Let’s explore an example that illustrates how to use some of these dot commands. In this example, we will create a sample database, list its tables, and view the schema of a specific table.

Step 1: Start SQLite and Create a Database

To start using SQLite, open the terminal and enter the following command to start an SQLite session and create a new database called example.db:

sqlite3 example.db

Step 2: Create a Table

In the SQLite session, create a table called employees with some sample columns:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    position TEXT,
    salary REAL
);

Step 3: Use Dot Commands to Explore the Database

Here are some common dot command tasks we might want to perform:

List All Tables

Use the .tables command to list all tables in the example.db database:

.tables

Output:

employees

This displays the employees table, confirming that the table was created successfully.

View the Schema of the employees Table

To see the schema of the employees table, use the .schema command followed by the table name:

.schema employees

Output:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    position TEXT,
    salary REAL
);

This displays the SQL statement used to create the employees table, showing the columns and data types.

Configure Output Format

Use the .mode command to change the display format of query results to CSV:

.mode csv

Toggle Headers

Use the .header on command to enable column headers in the output:

.header on

Query Data

Now, if you insert some data and run a query, the output will be in CSV format with headers:

INSERT INTO employees (name, position, salary) VALUES ('Hector', 'Developer', 70000);
INSERT INTO employees (name, position, salary) VALUES ('Fitch', 'Designer', 60000);

SELECT * FROM employees;

The output should look like this, given that .mode csv and .header on were set:

id,name,position,salary
1,Hector,Developer,70000.0
2,Fitch,Designer,60000.0

Step 4: Exit the SQLite Session

After completing the session, you can exit SQLite using the .exit or .quit command:

.exit

Benefits of Using Dot Commands in SQLite

Dot commands provide several benefits in the SQLite CLI environment:

  • Efficient Navigation: Dot commands simplify database navigation, allowing users to view tables, schemas, and other information without writing SQL queries.
  • Output Customization: Commands like .mode and .header make it easy to control output format, which can be handy when exporting data.
  • Ease of Use: Dot commands require minimal syntax, making SQLite CLI accessible for quick tasks without needing to write extensive SQL code.
  • Session Management: Commands like .open, .save, and .exit streamline session handling, enabling users to manage multiple databases effectively.

Summary

Dot commands are handy tools in SQLite that help us manage, navigate, and interact with SQLite databases efficiently from the command line. They are especially beneficial for tasks that would otherwise require more complex SQL statements or scripts, such as listing tables, configuring output modes, and viewing schemas.