Dbcli Interactive

Command Style (Use PATH)

All examples use the plain command name dbcli (no directory prefix). Ensure dbcli is on PATH instead of hardcoding paths like .\.claude\skills\dbcli\dbcli.exe.

DbCli Interactive Skill

Interactive SQL mode (REPL) for database exploration and administration with built-in safety prompts.

When to Use This Skill

• User wants to explore database interactively
• User needs to run multiple ad-hoc queries
• User prefers REPL environment over one-off commands
• User wants to prototype SQL statements
• User needs database administration session
• Learning/teaching SQL on real databases

⚠️ Safety Features

Interactive mode includes:

• Safety prompts before UPDATE/DELETE/DROP operations
• Automatic backup suggestions for dangerous operations
• Query preview before execution
• Confirmation dialogs for destructive commands
• Transaction rollback support (where available)

Command Syntax

# Set connection via environment variable
export DBCLI_CONNECTION="CONNECTION_STRING"
export DBCLI_DBTYPE="DATABASE_TYPE"  # Optional, default: sqlite
dbcli interactive

Aliases

dbcli i          # Short form
dbcli -i         # Alternative

Global Options

• -t, --db-type: Database type (default: sqlite)
• Environment variables:
  • DBCLI_CONNECTION: Database connection string (required)
  • DBCLI_DBTYPE: Database type (alternative to -t)

Starting Interactive Mode

Basic Usage

# SQLite
export DBCLI_CONNECTION="Data Source=app.db"
dbcli interactive

# Welcome to DbCli Interactive Mode
# Type .help for commands, .exit to quit
# dbcli>

Different Databases

# SQL Server
export DBCLI_CONNECTION="Server=localhost;Database=mydb;Trusted_Connection=True"
dbcli -t sqlserver interactive

# MySQL
export DBCLI_CONNECTION="Server=localhost;Database=mydb;Uid=root;Pwd=xxxxxxxxxx"
dbcli -t mysql interactive

# PostgreSQL
export DBCLI_CONNECTION="Host=localhost;Database=mydb;Username=postgres;Password=xxxxxxxxxx"
dbcli -t postgresql interactive

# DaMeng
export DBCLI_CONNECTION="Server=localhost;User Id=SYSDBA;PWD=xxxxxxxxxx;DATABASE=mydb"
dbcli -t dm interactive

# GaussDB
export DBCLI_CONNECTION="Host=localhost;Port=8000;Database=mydb;Username=gaussdb;Password=xxxxxxxxxx"
dbcli -t gaussdb interactive

Interactive Commands

Meta Commands (Dot Commands)

.help                 - Show help message
.tables               - List all tables
.columns <table>      - Show table structure
.format <type>        - Change output format (json/table/csv)
.exit / .quit         - Exit interactive mode
.clear                - Clear screen
.history              - Show command history

SQL Execution

dbcli> SELECT * FROM Users LIMIT 5;
-- Results displayed immediately

dbcli> SELECT COUNT(*) as user_count FROM Users;
-- Returns: { "user_count": 42 }

Interactive Session Examples

Exploration Session

$ export DBCLI_CONNECTION="Data Source=app.db"
$ dbcli interactive

dbcli> .tables
Users
Orders
Products

dbcli> .columns Users
ColumnName | DataType | IsNullable | IsPrimaryKey
-------------------------------------------------------
Id         | INTEGER  | False      | True
Name       | TEXT     | False      | False
Email      | TEXT     | True       | False
CreatedAt  | TIMESTAMP| True       | False

dbcli> SELECT * FROM Users LIMIT 3;
+----+-------+-------------------+
| Id | Name  | Email             |
+----+-------+-------------------+
| 1  | Alice | alice@example.com |
| 2  | Bob   | bob@example.com   |
| 3  | Carol | carol@example.com |
+----+-------+-------------------+

dbcli> .format json
Output format changed to: json

dbcli> SELECT Name, Email FROM Users WHERE Id = 1;
[{"Name":"Alice","Email":"alice@example.com"}]

dbcli> .exit
Goodbye!

Data Analysis Session

dbcli> -- Check total records
dbcli> SELECT COUNT(*) as total FROM Orders;
{"total": 1547}

dbcli> -- Find top customers
dbcli> SELECT CustomerId, COUNT(*) as order_count, SUM(Total) as total_spent
       FROM Orders
       GROUP BY CustomerId
       ORDER BY total_spent DESC
       LIMIT 5;

+------------+-------------+-------------+
| CustomerId | order_count | total_spent |
+------------+-------------+-------------+
| 42         | 23          | 15420.50    |
| 17         | 19          | 12350.00    |
...

dbcli> -- Analyze by month
dbcli> SELECT strftime('%Y-%m', OrderDate) as month,
       COUNT(*) as orders,
       SUM(Total) as revenue
       FROM Orders
       GROUP BY month
       ORDER BY month DESC
       LIMIT 6;

Safety Prompts for Dangerous Operations

UPDATE with Safety Prompt

dbcli> UPDATE Users SET status = 'verified' WHERE email_confirmed = 1;

⚠️  WARNING: UPDATE operation detected
This will modify records in table: Users

Preview affected records? (yes/no): yes

Records to be updated:
+----+--------+----------------------+
| Id | Name   | Email                |
+----+--------+----------------------+
| 5  | John   | john@example.com     |
| 8  | Sarah  | sarah@example.com    |
+----+--------+----------------------+

Estimated affected records: 2

Recommended actions:
  1. Create backup: .export Users Users_backup_20250127_143022.sql
  2. Create table copy: CREATE TABLE Users_copy_20250127_143022 AS SELECT * FROM Users

Create automatic backup before UPDATE? (yes/no): yes

Creating backup: Users_backup_20250127_143022.sql... Done.

Proceed with UPDATE? (yes/no): yes

Executing UPDATE...
Updated 2 rows.
Backup saved: Users_backup_20250127_143022.sql

DELETE with Safety Prompt

dbcli> DELETE FROM Orders WHERE status = 'cancelled' AND created_at < date('now', '-365 days');

⚠️  DANGER: DELETE operation detected
This will permanently remove records from table: Orders

Preview records to be deleted? (yes/no): yes

Records to be deleted:
+----+-----------+------------+
| Id | Status    | CreatedAt  |
+----+-----------+------------+
| 23 | cancelled | 2023-05-10 |
| 45 | cancelled | 2023-08-22 |
...
+----+-----------+------------+

Estimated affected records: 37

⚠️  This operation CANNOT BE UNDONE without backup!

Create automatic backup before DELETE? (yes/no): yes

Creating backup: Orders_backup_20250127_143022.sql... Done.

Type 'DELETE' to confirm deletion: DELETE

Executing DELETE...
Deleted 37 rows.
Backup saved: Orders_backup_20250127_143022.sql

DROP TABLE with Critical Warning

dbcli> DROP TABLE TempData;

🛑 CRITICAL WARNING: DROP TABLE operation detected
This will PERMANENTLY DESTROY table: TempData

Table information:
  - Records: 1,245
  - Columns: 7
  - Indexes: 2
  - Size: ~450 KB

This operation is IRREVERSIBLE!

Recommended actions:
  1. Export data: .export TempData TempData_backup.sql
  2. Export schema: .columns TempData > TempData_schema.txt
  3. Create table copy: CREATE TABLE TempData_copy AS SELECT * FROM TempData

Create complete backup (data + schema)? (yes/no): yes

Creating backups...
  ✓ Data exported: TempData_backup_20250127_143022.sql
  ✓ Schema saved: TempData_schema_20250127_143022.txt
  ✓ Table copy created: TempData_copy_20250127_143022

Type 'DROP TABLE TempData' exactly to confirm: DROP TABLE TempData

Executing DROP TABLE...
Table 'TempData' has been dropped.
Recovery files available in: backups/

Special Interactive Features

Auto-Completion (Planned)

dbcli> SELECT * FROM Us<TAB>
-- Auto-completes to: SELECT * FROM Users

dbcli> SELECT Na<TAB>, Em<TAB> FROM Users
-- Auto-completes column names

Command History

dbcli> .history
1. SELECT * FROM Users LIMIT 5
2. .tables
3. .columns Orders
4. SELECT COUNT(*) FROM Orders
5. UPDATE Users SET status = 'active'

dbcli> !3
-- Re-executes: .columns Orders

Multi-Line Queries

dbcli> SELECT u.Name,
...>        o.OrderDate,
...>        o.Total
...>  FROM Users u
...>  JOIN Orders o ON u.Id = o.UserId
...>  WHERE o.Total > 100
...>  ORDER BY o.OrderDate DESC;
-- (Press Enter on empty line or end with ';' to execute)

Transaction Support

dbcli> BEGIN TRANSACTION;
Transaction started.

dbcli> UPDATE Users SET balance = balance - 100 WHERE Id = 5;
Updated 1 row.

dbcli> UPDATE Users SET balance = balance + 100 WHERE Id = 8;
Updated 1 row.

dbcli> -- Check balances
dbcli> SELECT Id, Name, balance FROM Users WHERE Id IN (5, 8);
+----+-------+---------+
| Id | Name  | balance |
+----+-------+---------+
| 5  | Alice | 400     |
| 8  | Bob   | 600     |
+----+-------+---------+

dbcli> COMMIT;
Transaction committed.

-- Or rollback if something wrong:
dbcli> ROLLBACK;
Transaction rolled back.

Configuration in Interactive Mode

Set Output Format

dbcli> .format table
Output format: table

dbcli> SELECT * FROM Users LIMIT 2;
+----+-------+-------------------+
| Id | Name  | Email             |
+----+-------+-------------------+
...

dbcli> .format json
Output format: json

dbcli> SELECT * FROM Users LIMIT 2;
[{"Id":1,"Name":"Alice","Email":"alice@example.com"}...]

dbcli> .format csv
Output format: csv

dbcli> SELECT * FROM Users LIMIT 2;
Id,Name,Email
1,Alice,alice@example.com

Session Variables (Future Feature)

dbcli> .set safety_prompts on
Safety prompts enabled

dbcli> .set auto_backup on
Auto-backup before dangerous operations: enabled

dbcli> .set
Current settings:
  safety_prompts: on
  auto_backup: on
  output_format: table
  max_rows: 100

Use Cases

1. Database Development

# Test query iterations
dbcli> SELECT * FROM Products WHERE price > 100;
-- Review results

dbcli> SELECT * FROM Products WHERE price > 100 AND stock > 0;
-- Refine query

dbcli> SELECT name, price, stock FROM Products WHERE price > 100 AND stock > 0 ORDER BY price;
-- Final query for application

2. Data Cleanup

# Find duplicates
dbcli> SELECT email, COUNT(*) as count
       FROM Users
       GROUP BY email
       HAVING count > 1;

# Review duplicate records
dbcli> SELECT * FROM Users WHERE email = 'duplicate@example.com';

# Remove duplicates (with safety prompt)
dbcli> DELETE FROM Users WHERE Id IN (SELECT MAX(Id) FROM Users GROUP BY email HAVING COUNT(*) > 1);
⚠️  Safety prompt triggered...

3. Database Migration Testing

# Test migration script step by step
dbcli> BEGIN TRANSACTION;

dbcli> ALTER TABLE Users ADD COLUMN age INTEGER;

dbcli> .columns Users
-- Verify new column added

dbcli> UPDATE Users SET age = 25 WHERE Id = 1;
-- Test update

dbcli> SELECT * FROM Users WHERE Id = 1;
-- Verify data

dbcli> COMMIT;
-- Or ROLLBACK if issues found

4. Quick Data Inspection

# Explore unfamiliar database
dbcli> .tables
-- See what tables exist

dbcli> .columns Users
-- Check structure

dbcli> SELECT * FROM Users LIMIT 3;
-- Sample data

dbcli> SELECT COUNT(*) FROM Users;
-- Record count

Scripting with Interactive Mode

Pipe SQL from File

# Execute script in interactive mode
export DBCLI_CONNECTION="Data Source=app.db"
cat migration.sql | dbcli interactive

Heredoc Script

export DBCLI_CONNECTION="Data Source=app.db"
dbcli interactive <<EOF
.format table
.tables
SELECT COUNT(*) FROM Users;
SELECT * FROM Users LIMIT 5;
.exit
EOF

Best Practices

1. Enable safety prompts - Never disable for production databases
2. Use transactions - Wrap multiple updates in BEGIN/COMMIT
3. Test on backup first - Clone database for dangerous operations
4. Keep command history - Reference previous successful queries
5. Use .format table for review, json for programmatic use
6. Create backups before DELETE/UPDATE/DROP
7. Exit cleanly with .exit (ensures connection cleanup)

Keyboard Shortcuts (Future)

Ctrl+C        - Cancel current query
Ctrl+D        - Exit interactive mode
Ctrl+L        - Clear screen
Up/Down       - Navigate command history
Tab           - Auto-complete table/column names
Ctrl+R        - Reverse search history

Integration with Other Skills

Interactive mode can call other skills internally:

dbcli> .export Users
-- Internally calls: dbcli export Users

dbcli> .import backup.sql
-- Internally calls: dbcli exec -F backup.sql

Exit Codes

0  - Normal exit
1  - Connection error
2  - Syntax error in SQL
3  - User cancelled dangerous operation

Comparison with Other Skills

Use interactive mode when: Exploring, testing, multiple queries Use one-off commands when: Automation, scripts, single operations