litecli Enhanced SQLite

What it is

A CLI for SQLite that provides auto-completion, syntax highlighting, and query history.

Installation

Linux:

sudo apt update && sudo apt install litecli
# or
sudo dnf install litecli
# or
pip install litecli

macOS:

brew install litecli
# or
pip install litecli

Windows:

pip install litecli

(Requires Python 3 and pip to be installed and in your PATH)

Core Concepts

• Database Connection: litecli connects to a SQLite database file. If the file doesn’t exist, it will be created.
• Interactive Mode: The primary way to use litecli is through its interactive shell, where you can type SQL commands and navigate history.
• SQL Execution: You type standard SQL commands. litecli enhances the experience with features like auto-completion.
• Special Commands: Commands starting with a backslash (\) are litecli specific commands, not SQL.

Commands / Usage

Connecting to a Database

litecli mydatabase.db

Connect to or create the SQLite database file named mydatabase.db.

litecli

Start litecli and create an in-memory database (data is lost when you exit).

Navigating and Editing

.help

Show the help message for litecli special commands.

.tables

List all tables in the current database.

.schema users

Show the CREATE TABLE statement for the users table.

.mode column

Set the output mode to column for aligned columns.

.mode csv

Set the output mode to csv for comma-separated values.

.headers on

Display column names as headers.

.headers off

Hide column names.

.quit

Exit litecli.

.history

Show the command history.

.clear

Clear the screen.

Ctrl+R

Search command history interactively.

Executing SQL Queries

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

Create a new table named employees.

INSERT INTO employees (name, salary) VALUES ('Alice', 60000.50);

Insert a new row into the employees table.

SELECT * FROM employees WHERE salary > 50000;

Select all columns from employees where the salary is greater than 50000.

UPDATE employees SET salary = 65000.00 WHERE name = 'Alice';

Update the salary for the employee named 'Alice'.

DELETE FROM employees WHERE id = 1;

Delete the employee with id equal to 1.

.read /path/to/your/script.sql

Execute SQL commands from a file.

Auto-completion and Syntax Highlighting

(These are features, not commands. They work automatically when you type.)

• Type SELECT * FROM and press Tab to see available table names.
• Type SELECT name, and press Tab to see available column names for the current table.
• SQL keywords like SELECT, FROM, WHERE will be highlighted in a distinct color.
• String literals will be highlighted in another color.

Common Patterns

Running a single query from the command line and exiting:

litecli mydatabase.db -execute "SELECT COUNT(*) FROM users;"

Executes the SQL query and prints the result before exiting litecli.

Importing CSV data into a table:

# First, create the table if it doesn't exist
litecli mydatabase.db -execute "CREATE TABLE IF NOT EXISTS products (id INTEGER PRIMARY KEY, name TEXT, price REAL);"

# Then, import data (assuming data.csv has columns matching the table)
# Note: litecli doesn't have a direct import command like .import in sqlite3-cli.
# You'd typically use a script or process the CSV externally.
# For demonstration, here's how you might do it with external processing:
# cat data.csv | sed 's/^/"/;s/$/"/' | sed 's/,/","/g' | awk '{print "INSERT INTO products (name, price) VALUES (" $0 ");"}' | litecli mydatabase.db -execute -

This example simulates importing CSV by transforming it into INSERT statements and piping them to litecli. A more robust solution might involve Python or other scripting languages.

Exporting query results to a file:

litecli mydatabase.db -execute "SELECT * FROM orders WHERE status = 'shipped';" > shipped_orders.txt

Executes the query and redirects the standard output (including headers if enabled) to shipped_orders.txt.

Using litecli with standard input:

echo "SELECT name FROM users LIMIT 5;" | litecli mydatabase.db

Pipes a single SQL command to litecli for execution.

Gotchas

• In-Memory Databases: If you start litecli without specifying a database file (litecli), your database exists only in RAM and is lost upon exiting.
• File Permissions: Ensure you have read/write permissions for the database file and the directory it resides in.
• .import Command: litecli does not have the .import command found in the standard sqlite3 command-line shell. You need to use SQL INSERT statements or external tools for data import.
• Shell vs. -execute: Commands starting with \ (like .help, .tables) are litecli shell commands and only work in interactive mode. They will cause errors if used with the -execute flag or piped input.