SQLite dot commands

SQLite is a lightweight relational database management system (RDBMS) that supports a subset of standard SQL syntax. In addition to the SQL commands, SQLite also provides several dot commands that allow users to perform various tasks such as managing databases, modifying settings, and debugging queries. In this article, we will explore some of the most commonly used SQLite dot commands.

Dot-commands overview

In addition to SQL commands, dot commands allow for more advanced interaction with the database. These dot commands are executed by prefixing them with a period (i.e. .command) and can be used to perform tasks such as importing data, managing schemas, and optimizing performance.

Below we have a table with the most frequently used dot commands and their brief description:

Basic commands

The basic commands are used for basic operations like getting help information, exiting the SQLite prompt, and opening a database file.

• .help : The command shows a list of all available dot commands in SQLite and their descriptions. Here's an example of an application:

sqlite> .help

• .exit : The command is used to exit the SQLite prompt. Here's an example of usage:

sqlite> .exit

This will exit the SQLite prompt and return you to your command prompt.

• .open : The command is used to open a database file. Here's an example:

sqlite> .open mydatabase.db

This will open the mydatabase.db file in the SQLite prompt.

Table-related commands

The table-related commands are used for working with tables in the database, such as listing all tables and displaying the schema for a specified table.

• .tables: This command lists all the tables in the current database.

Suppose we have a database named mydb.db that contains three tables: customers, orders, and products. To list all the tables in this database, we can use the following command:

sqlite> .tables

The output will be as follows:

customers orders products

• .schema: This command displays the SQL schema for a specified table. The schema includes the table's column names, data types, and any constraints.

Syntax: .schema [table_name]

Suppose we want to view the schema for the customers table in our mydb.db database. We can use the following command:

sqlite> .schema customers

This command shows us the SQL code used to create the customers table, including the column names and data types.

The output will be as follows:

CREATE TABLE customers ( id INTEGER PRIMARY KEY,

name TEXT,

email TEXT,

address TEXT,

city TEXT,

state TEXT,

zip TEXT );

Output-related commands

The output-related commands are used to control the output format of the query results, such as setting the output mode and toggling column headers on or off.

• .mode : This command is used to set the output mode of the SQLite command-line interface.

The available modes are:

Syntax: .mode [mode]

Here's an example of how to use .mode to display query results in CSV format:

sqlite> .mode csv

We will get the following result after the request:

sqlite> SELECT * FROM users;
1,"Alice","Smith",30
2,"Bob","Jones",45
3,"Charlie","Brown",25

• .header: This command toggles the display of column headers on or off in query results.

Syntax: .header on|off

By default, headers are displayed. Here's an example of how to turn headers off:

sqlite> .header off

The requested result will be:

sqlite> SELECT * FROM users;
1|Alice|Smith|30
2|Bob|Jones|45
3|Charlie|Brown|25

Import and export commands

The import and export commands are used for importing data from a file into a table and exporting data from a table into a file, respectively. The file must be in a format that is recognized by SQLite, such as CSV or TSV.

• .import : The command is used to import data from a file into a table.

Syntax: .import [file_name] [table_name]

Assuming we have a CSV file named customers.csv with the following content:

1,John Doe,[email protected],(123) 456-7890
2,Jane Smith,[email protected],(555) 555-1212

We can import the data into the customers table as follows:

sqlite> .import customers.csv customers
sqlite> SELECT * FROM customers;
id          name        email                   phone
----------  ----------  ----------------------  -------------
1           John Doe    [email protected]    (123) 456-7890
2           Jane Smith  [email protected]  (555) 555-1212

• .output : This command outputs the results of a query to a file.

For example, if we want to output the names and emails of our customers to a file called output.txt, we can do the following:

sqlite> .output output.txt
sqlite> SELECT name, email FROM customers;

Note, that after running this command, any query results will be written to the output.txt file instead of being displayed in the SQLite prompt. You can turn off the output mode by entering .output with no arguments or by setting the output file name to /dev/stdout.

Backup and restore commands

The backup and restore commands are used for creating a backup copy of the database and restoring it if necessary.

• .backup: The command is used to create a backup copy of the current database.

Syntax: .backup [destination_file]

sqlite> .backup backup.db

• .restore : The command is used to restore a database from a backup file.

Syntax: .restore [backup_file]

sqlite> .restore backup.db

Conclusion

In this topic, we have explored some of the most commonly used SQLite dot commands that include basic commands for opening, exiting the SQLite prompt, and getting the help information. We have also examined table-related commands that help in working with tables in the database and output-related commands that control the output format of query results. Lastly, we have looked at import and export commands used to import data from a file into a table and export data from a table into a file, respectively. With the help of these dot commands, we can enhance productivity when working with SQLite databases. Now it is high time for us to have some practice on what we have learned!