Database

Database CLI

Access and manage MySQL, PostgreSQL, SQLite databases, and Redis key-value stores using their respective command-line clients.

Critical Rules

NEVER do the following:

• NEVER guess or assume table names - always list tables first with SHOW TABLES, \dt, or .tables
• NEVER guess or assume column names - always describe the table first with DESCRIBE, \d, or PRAGMA table_info
• NEVER run SELECT queries on an unfamiliar database without exploring the schema first
• NEVER assume ORM naming conventions (e.g., Order vs orders, userId vs user_id) - verify with schema

ALWAYS follow the Query Workflow below when querying data the user asks for.

Database Type Detection

Determine the database type from context:

If database type cannot be determined, ask using AskUserQuestion:

Which database are you working with?
1. MySQL
2. PostgreSQL
3. SQLite
4. Redis

Prerequisites

Verify the appropriate CLI is installed:

If CLI is not installed, offer to help install it:

1. Detect the OS using uname -s (Darwin=macOS, Linux=Linux)
2. Ask the user using AskUserQuestion:

The [DATABASE] CLI is not installed. Would you like me to install it?

I detected you're on [OS]. I would run:
- [INSTALL_COMMAND]

1. Yes, install it for me
2. No, I'll install it myself

Installation commands by OS:

For macOS with Homebrew, after installing mysql-client or libpq, the user may need to add to PATH:

• MySQL: echo 'export PATH="/opt/homebrew/opt/mysql-client/bin:$PATH"' >> ~/.zshrc
• PostgreSQL: echo 'export PATH="/opt/homebrew/opt/libpq/bin:$PATH"' >> ~/.zshrc

Credential/Path Acquisition

CRITICAL: Never use environment variables from the shell without explicit user permission.

Ask the user using AskUserQuestion:

For MySQL/PostgreSQL:

How would you like to provide database credentials?
1. Enter credentials manually (host, user, password, database)
2. Read from a file (provide path to .env, docker-compose.yml, or config file)

For SQLite:

How would you like to provide the SQLite database path?
1. Enter the file path manually (e.g., ./data.db, /path/to/database.sqlite)
2. Read from a file (provide path to .env or config file)
3. Use in-memory database (:memory:)

For Redis:

How would you like to provide Redis connection details?
1. Enter connection details manually (host, port, password, database number)
2. Read from a file (provide path to .env, docker-compose.yml, or config file)

After reading any config file, confirm with user before connecting.

For detailed credential formats and CLI syntax, see the database-specific references:

• MySQL: See references/mysql.md
• PostgreSQL: See references/postgres.md
• SQLite: See references/sqlite.md
• Redis: See references/redis.md

Connection Test

Before any operation, test the connection using the appropriate command from the reference docs.

Query Workflow (For Unfamiliar Databases)

When user asks to query or check data, follow these steps in order:

Step 1: List Tables

First, discover what tables exist:

Step 2: Identify Target Table

Match user's intent to actual table name:

• User says "order" → look for orders, order, Order, tbl_orders, etc.
• User says "user" → look for users, user, accounts, members, etc.
• Do NOT assume - pick from the actual table list

Step 3: Describe Table Structure

Get actual column names before querying:

Step 4: Build and Execute Query

Now build the SELECT using actual column names from Step 3:

-- Use real columns, not guessed ones
SELECT actual_col1, actual_col2, actual_col3
FROM actual_table_name
ORDER BY created_at DESC
LIMIT 10;

Query Execution

Read Operations (SELECT)

Add LIMIT 100 to large result sets by default unless user specifies otherwise.

Write Operations (INSERT, UPDATE, DELETE)

ALWAYS require user confirmation before executing.

For UPDATE/DELETE, first show affected rows count, then ask: "This will affect X rows. Proceed? (yes/no)"

Redis Operations

Redis uses commands, not SQL queries. See references/redis.md for:

• Key-value operations (GET, SET, DEL)
• Data structure operations (lists, sets, hashes, sorted sets)
• Key pattern exploration (SCAN, KEYS)
• Official command reference: https://redis.io/docs/latest/commands/

Schema Exploration (SQL Databases)

Common operations (see reference docs for exact syntax):

Safety Rules

Destructive Operations - REQUIRE CONFIRMATION

These operations MUST show a warning and require explicit user confirmation:

Redis Destructive Operations - REQUIRE CONFIRMATION

Password/Credential Security

• NEVER echo password to terminal output
• NEVER include password in error messages shown to user
• NEVER print or show the full command that contains passwords to the user
• When executing commands, do NOT display the command itself - only show the query results
• Do not log queries containing passwords

Production/System Database Warning

Show warning if:

• Host/path contains: prod, production, live, master
• Cloud database hostnames (RDS, Cloud SQL, Azure, Redis Cloud, ElastiCache)
• System databases (browser DBs, /Library/, /var/lib/)

SQLite File Safety

• Verify database path before operations
• Warn if creating a new database file
• Don't modify system SQLite databases without explicit permission (browser DBs, OS DBs)

Common SQL Operations

These work across MySQL, PostgreSQL, and SQLite (Redis uses commands, not SQL):

-- List all records (with limit)
SELECT * FROM table_name LIMIT 100;

-- Find by condition
SELECT * FROM table_name WHERE column = 'value' LIMIT 100;

-- Count records
SELECT COUNT(*) FROM table_name;

-- Recent records
SELECT * FROM table_name ORDER BY created_at DESC LIMIT 10;

-- Insert
INSERT INTO table_name (col1, col2) VALUES ('val1', 'val2');

-- Update (show count first, then confirm)
UPDATE table_name SET col1 = 'value' WHERE condition;

-- Delete (show count first, then confirm)
DELETE FROM table_name WHERE condition;

Database-Specific References

For detailed CLI commands, credential formats, and database-specific features:

• MySQL: references/mysql.md - CLI syntax, credential formats, SHOW commands
• PostgreSQL: references/postgres.md - psql meta-commands, SSL/TLS, pg_dump
• SQLite: references/sqlite.md - PRAGMA commands, backup/import, file formats
• Redis: references/redis.md - CLI syntax, key operations, data structures, SCAN patterns

Error Handling

Common errors across databases:

See database-specific references for detailed error handling.

Converted and distributed by TomeVault — claim your Tome and manage your conversions.