DuckDB CLI: A Guide to Dot Commands

.help

The .help command is your go-to resource for accessing the built-in help system in the DuckDB CLI. It provides a comprehensive list of available dot commands along with their descriptions and usage instructions.

Example:

Copy code
D .help
.auth OFF|ON           Show authorizer callbacks
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail on|off           Stop after hitting an error.  Default OFF
.binary on|off         Turn binary output on or off.  Default OFF
.cd DIRECTORY          Change the working directory to DIRECTORY
.changes on|off        Show number of rows changed by SQL
.check GLOB            Fail if output since .testcase does not match
.clone NEWDB           Clone data into NEWDB from the existing database
...

.mode

The .mode command allows you to change the output format of the query results in the DuckDB CLI. It supports various output modes, enabling you to customize the presentation of your data.

Available modes:

• ascii: Renders the output in ASCII table format.
• box: Displays the output in a box-style table format.
• csv: Outputs the results in comma-separated values (CSV) format.
• column: Presents the output in a columnar format.
• html: Generates an HTML table for the output.
• insert: Outputs the results as SQL INSERT statements.
• json: Renders the output in JSON format.
• line: Displays the output in a one-value-per-line format.
• list: Presents the output as a list, with each row on a separate line.
• markdown: Generates a Markdown table for the output.
• quote: Displays the output with each value quoted.
• table: Renders the output in a clean table format.
• tabs: Outputs the results in tab-separated format.
• tcl: Generates a Tcl list for the output.

Example:

Copy code
D .mode csv
D SELECT * FROM users LIMIT 3;
id,name,email
1,John Doe,john@example.com  
2,Jane Smith,jane@example.com
3,Alice Johnson,alice@example.com

.timer

The .timer command enables or disables the display of query execution time in the DuckDB CLI. When enabled, it shows the time taken for each query to execute, providing valuable insights into performance.

Example:

Copy code
D .timer on
D SELECT * FROM large_table LIMIT 10;
... query output ...
Run Time: real 0.012 user 0.000122 sys 0.000149

.tables

The .tables command lists all the tables in the current database. It provides a quick overview of the available tables, making it easier to navigate and explore your data.

Example:

Copy code
D .tables
users
products
orders

.schema

The .schema command displays the structure of a specific table, including column names, data types, and constraints. It helps you understand the schema of your tables without having to write complex queries.

Example:

Copy code
D .schema users
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name VARCHAR,
  email VARCHAR
);

.import

The .import command allows you to import data from external files into DuckDB tables. It supports various file formats, such as CSV, JSON, and Parquet, making it convenient to load data from different sources.

Example:

Copy code
D .import users.csv users

This command imports the data from the users.csv file into the users table.

.export

The .export command enables you to export data from DuckDB tables to external files. It supports multiple file formats, allowing you to share your data with other systems or backup your tables.

Example:

Copy code
D .export users users.csv

This command exports the data from the users table to the users.csv file.

.dump

The .dump command generates a SQL script that recreates the entire database, including table structures and data. It is useful for creating backups or transferring your database to another system.

Example:

Copy code
D .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name VARCHAR,
  email VARCHAR
);
INSERT INTO users VALUES(1,'John Doe','john@example.com');
INSERT INTO users VALUES(2,'Jane Smith','jane@example.com');
...
COMMIT;

.read

The .read command allows you to execute SQL statements from an external file. It is particularly handy when you have complex queries or scripts that you want to run repeatedly.

Example:

Copy code
D .read analytics.sql

This command reads and executes the SQL statements from the analytics.sql file.

There are several other ways to run SQL commands in DuckDB, including by using command-line arguments and pipe syntax.

.log

The .log command enables or disables logging of the DuckDB CLI session to a file. It captures all the commands executed and their output, providing a record of your interactions with the database.

Example:

Copy code
D .log session.log

This command starts logging the session to the session.log file.

.exit or .quit

The .exit or .quit command terminates the DuckDB CLI session gracefully. It ensures that any pending transactions are committed, and the database is properly closed.

Example:

Copy code
D .exit

Mastering the DuckDB CLI with dot commands empowers you to efficiently manage and analyze your data, unlocking a world of possibilities. As you embark on your data journey, remember that the DuckDB CLI is a powerful ally, and the dot commands are your key to unlocking its full potential. If you're ready to take your data management skills to the next level, Get Started with us today and experience the power of DuckDB firsthand.