DBCli¶

# Installation (via pip or package manager)
pip install mycli           # MySQL
pip install pgcli           # PostgreSQL
pip install litecli         # SQLite

# Basic usage (mycli example)
mycli -u root -p -h localhost mydb
mycli -u user -p -h 192.168.1.100 production_db

# PostgreSQL (pgcli)
pgcli -U postgres -d mydb
pgcli -U app_user -d app_db -h db.example.com

# SQLite (litecli)
litecli ~/mydata.db
litecli :memory:            # In-memory database for testing

# Inside the CLI — special commands
\help                       # Show all special commands
\status                     # Connection status
\quit or \q                 # Exit
\e                          # Open query in $EDITOR
\watch <interval>           # Repeat query every N seconds
\f <function_name>          # Show function definition
\d <table>                  # Describe table schema

# Saving and running queries
\save_query <name>          # Save current query with name
\load_query <name>          # Load saved query
\run_query <name>           # Execute saved query

# Connect with .pgpass or .my.cnf (password in config files)
pgcli -U user -d mydb -h localhost
# .pgpass prevents password prompt — format: hostname:port:database:user:password
chmod 600 ~/.pgpass

# Output formatting
\pset format csv            # pgcli: output as CSV
SELECT * FROM users;        # Saves to .csv for import into other tools

# Watch queries for monitoring
\watch 5                    # Re-run every 5 seconds (useful for monitoring)
SELECT COUNT(*) FROM logs;

# Multiple queries in batch
echo "SELECT COUNT(*) FROM users; SELECT COUNT(*) FROM posts;" | pgcli -d mydb

# Run SQL file
\i /path/to/queries.sql     # Execute file

# Tab-separated output (copy-paste friendly)
\pset format tsv
SELECT id, email, created_at FROM users;
# Output is tab-separated, pastes cleanly into spreadsheets