sqlite3 command in Linux
The sqlite3 command is a powerful command-line program that allows users to interface with SQLite databases. SQLite is a lightweight, self-contained, and serverless database engine commonly used in applications, development, and testing.
With sqlite3, you can build, query, update, and administer SQLite databases straight from the command line, making it a powerful tool for database management and development activities.
Table of Contents
Here is a comprehensive guide to the options available with the sqlite3 command −
- Overview of the sqlite3 Command
- Installation of sqlite3 on Linux
- Syntax of the sqlite3 Command
- Options Available for sqlite3 Command
- Examples of sqlite3 Command in Linux
Overview of the sqlite3 Command
SQLite databases are frequently used in mobile apps, embedded systems, and small-scale applications due to their simplicity and portability. The sqlite3 command acts as an interface to connect directly with these databases, allowing users to do activities such as executing SQL queries, importing/exporting data, and evaluating the schema.
Installation of sqlite3 on Linux
Make sure SQLite is installed on your Linux system before using the sqlite3 command. It can be found in the package repositories of the majority of Linux distributions.
Debian / Ubuntu-Based Systems
SQLite can be easily installed using the apt package manager −
sudo apt install sqlite3
Red Hat / CentOS-Based Systems
For RHEL-based distributions, use yum to install SQLite −
sudo yum install sqlite
Fedora
On Fedora, you can install SQLite with the dnf package manager −
sudo dnf install sqlite
Syntax of the sqlite3 Command
The sqlite3 command provides a simple yet powerful way to interact with databases. Its syntax is as follows −
sqlite3 [options] [database_name]
Where,
- [options] − Flags that modify the behavior of the sqlite3 command.
- [database_name] − Name of the SQLite database file to interact with. If the database does not exist, SQLite will create it automatically.
Options Available for sqlite3 Command
Listed below are the options provided by the sqlite3 command along with their descriptions −
| Options | Description |
|---|---|
| -A ARGS... | Executes the archive command using the designated arguments (ARGS) and leaves. |
| -append | Instead of rewriting a file, append the database content to its end. |
| -ascii | Puts the output mode on ASCII. |
| -bail | Stops execution immediately once an error is encountered. |
| -batch | Forces batch input/output mode, so turning off interactive input. |
| -box | Changes the output mode to box, displaying query results in a box-style grid format. |
| -column | Formats query results as aligned columns. |
| -cmd COMMAND | Runs a specified SQL COMMAND before reading further input. |
| -csv | Sets the output mode to CSV (Comma-Separated Values) |
| -deserialize | Opens the database using the sqlite3_deserialize() method |
| -echo | Prints input commands before executing them. |
| -init FILENAME | Reads and processes commands from the specified FILENAME before starting the interactive session. |
| -[no]header | Toggles the display of column headers in query results. |
| -html | Sets the output mode to HTML, formatting query results as HTML tables for web applications or presentations. |
| -help | Displays a help message detailing the available options |
| -interactive | Forces the command to operate in interactive mode, even if input is redirected from a file or script. |
| -json | Changes the output mode to JSON. |
| -line | Displays query results one record per line |
| -list | Formats output as a simple list. |
| -lookaside SIZE N | Configures lookaside memory, using N entries of SIZE bytes each. |
| -markdown | Sets the output mode to Markdown, generating query results in Markdown table format. |
| -maxsize N | Defines the maximum size for a --deserialize database. |
| -memtrace | Tracks all memory allocations and deallocations during the session. |
| -mmap N | Sets the default mmap size to N. |
| -newline SEP | Specifies the row separator (SEP) for query results. |
| -nofollow | Prevents symbolic links to database files from being opened |
| -nonce STRING | Sets a secure escape nonce (STRING) for safe-mode operations |
| -nullvalue TEXT | Defines a custom text string (TEXT) to replace NULL values in query results. |
| -pagecache SIZE N | Configures the page cache with N slots of SIZE bytes each. |
| -pcachetrace | Logs all page cache operations. |
| -quote | Sets the output mode to quote. |
| -readonly | Opens the database in read-only mode. |
| -safe | Enables safe-mode for the session. |
| -stats | Prints memory usage statistics before finalizing each query. |
| -table | Displays query results in a visually appealing table format. |
| -tabs | Sets the output mode to use tabs as column separators. |
| -unsafe-testing | Allows unsafe commands and modes for testing purposes. |
| -version | Shows the SQLite version installed on the system. |
| -vfs NAME | Specifies the default VFS (NAME) to be used for database operations. |
| -zip | Treats the file as a ZIP archive and opens it. |
Examples of sqlite3 Command in Linux
The following are a few practical examples demonstrating the versatility of the sqlite3 command in database management tasks.
- Creating a New SQLite Database
- Executing SQL Commands
- Exporting Data in CSV Format
- Displaying Querying Results in JSON
- Changing the Output Mode to Table
Creating a New SQLite Database
To create a new database file and start working with it −
sqlite3 mydatabase.db
This creates a new SQLite database named mydatabase.db if it does not already exist and opens an interactive session for you to execute SQL commands.
Executing SQL Commands
Once youâre inside the SQLite shell, you can execute standard SQL statements to create tables, insert data, and run queries −
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);
INSERT INTO users (name, age) VALUES ('Alice', 25), ('Bob', 30);
SELECT * FROM users;
These commands create a users table, populate it with two records, and retrieve all the rows from the table.
Exporting Data in CSV Format
To share data or use it in an external tool like Excel, you can export query results to a CSV file −
sqlite3 -csv -header mydatabase.db "SELECT * FROM employees;" > employees.csv
The query retrieves all records from the employees table, formats the results as CSV, and saves them in a file named employees.csv. The -header option includes the column headers.
Displaying Query Results in JSON
For developers integrating database data with a web application, JSON output is often needed −
sqlite3 -json mydatabase.db "SELECT * FROM employees;"
This outputs the query results as JSON, making them compatible with modern APIs and web tools.
Changing the Output Mode to Table
For a more visually appealing display of query results in the terminal −
sqlite3 -table mydatabase.db "SELECT * FROM employees;"
The results are presented as a neatly formatted table, making them easier to interpret than default text output.
Conclusion
The sqlite3 command simplifies database management, giving a powerful interface for creating, querying, and updating SQLite databases straight from the command line.
Whether exporting data, analyzing results, or running SQL queries, sqlite3 provides freedom for developers, analysts, and managers alike.