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 Query Skill

Execute SELECT queries on 30+ databases using the DbCli command-line tool.

When to Use This Skill

• User wants to query database tables
• User needs to read data from a database
• User mentions SELECT, query, search, filter, or find data
• User wants to retrieve records from any supported database

Supported Databases

DbCli is built on SqlSugar and supports 30+ databases. Examples:

SQLite, SQL Server, MySQL, PostgreSQL, Oracle, MongoDB, ClickHouse, Doris, OceanBase, TDengine, DuckDB, etc.

Command Syntax

dbcli -c "CONNECTION_STRING" [-t DATABASE_TYPE] [-f FORMAT] query "SQL_QUERY" [-p JSON] [-P params.json]

Options

Options can appear either before or after the subcommand. Command-first is recommended, and the legacy "options before command" style is also supported.

• -c, --connection

  : Database connection string (required)

• -t, --db-type

  : Database type (default: sqlite)

• -f, --format

  : Output format:

  json

  (default),

  table

  ,

  csv

• -p, --params

  : JSON parameters object (use

  @Param

  placeholders)

• -P, --params-file

  : Read JSON parameters from file

Subcommand Options

• -F, --file

  : Read SQL from file instead of command line

• --config

  : Path to configuration file

Usage Examples

Basic Query

# SQLite - Query all users
dbcli -c "Data Source=app.db" query "SELECT * FROM Users"

# SQL Server - Query with TOP
dbcli -c "Server=localhost;Database=mydb;Trusted_Connection=True" -t sqlserver query "SELECT TOP 10 * FROM Users"

# MySQL - Query with LIMIT
dbcli -c "Server=localhost;Database=mydb;Uid=root;Pwd=xxxxxxxxxx" -t mysql query "SELECT * FROM Users LIMIT 10"

# PostgreSQL - Query with WHERE clause
dbcli -c "Host=localhost;Database=mydb;Username=postgres;Password=xxxxxxxxxx" -t postgresql query "SELECT * FROM Users WHERE active = true"

Table Format Output

# Display results as formatted table
dbcli -c "Data Source=app.db" -f table query "SELECT * FROM Users"

# Output:
# +----+-------+-------------------+
# | Id | Name  | Email             |
# +----+-------+-------------------+
# | 1  | Alice | alice@example.com |
# | 2  | Bob   | bob@example.com   |
# +----+-------+-------------------+

Parameterized Query (RDB)

dbcli -c "Data Source=app.db" query "SELECT @Id AS Id, @Name AS Name" -p '{"Id":1,"Name":"Alice"}'

# IN (...) with array
dbcli -c "Data Source=app.db" query "SELECT * FROM Users WHERE Id IN (@Ids)" -p '{"Ids":[1,2,3]}'

Notes:

• SQLite providers may require

  DisableClearParameters: true

  in config (maps to SqlSugar

  IsClearParameters=false

  ).
• DbCli returns a single result set. SqlSugar supports multi-result sets/output parameters, but DbCli does not surface them yet.

CSV Format Output

# Export query results as CSV
dbcli -c "Data Source=app.db" -f csv query "SELECT * FROM Products" > products.csv

Query from File

# Complex query stored in file
dbcli -c "Data Source=app.db" query -F complex_query.sql

Chinese Domestic Databases

# DaMeng (达梦)
dbcli -c "Server=localhost;User Id=SYSDBA;PWD=xxxxxxxxxx;DATABASE=mydb" -t dm -f table query "SELECT * FROM dm_test"

# KingbaseES (人大金仓)
dbcli -c "Server=localhost;Port=54321;UID=system;PWD=xxxxxxxxxx;database=mydb" -t kdbndp -f table query "SELECT * FROM kingbase_test"

# GaussDB
dbcli -c "Host=localhost;Port=8000;Database=mydb;Username=gaussdb;Password=xxxxxxxxxx" -t gaussdb -f table query "SELECT * FROM gauss_test"

Advanced Queries

# Aggregation
dbcli -c "Data Source=app.db" query "SELECT category, COUNT(*) as count, AVG(price) as avg_price FROM products GROUP BY category"

# Join query
dbcli -c "Data Source=app.db" query "SELECT u.name, o.order_date, o.total FROM users u JOIN orders o ON u.id = o.user_id"

# Subquery
dbcli -c "Data Source=app.db" query "SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products)"

Configuration Methods

Method 1: Command Line (Direct)

dbcli -c "Data Source=app.db" -t sqlite query "SELECT * FROM Users"

Method 2: Environment Variables

# Set once
export DBCLI_CONNECTION="Data Source=app.db"
export DBCLI_DBTYPE="sqlite"

# Use without connection string
dbcli query "SELECT * FROM Users"

# Windows PowerShell
[Environment]::SetEnvironmentVariable("DBCLI_CONNECTION", "Data Source=app.db", "User")
[Environment]::SetEnvironmentVariable("DBCLI_DBTYPE", "sqlite", "User")

Method 3: Configuration File

Create

appsettings.json

:

{
  "ConnectionString": "Data Source=app.db",
  "DbType": "sqlite"
}

Use config file:

dbcli --config appsettings.json query "SELECT * FROM Users"

Connection String Reference

SQLite

Data Source=app.db

SQL Server

Server=localhost;Database=mydb;User Id=sa;Password=xxxxxxxxxx;

MySQL

Server=localhost;Database=mydb;Uid=root;Pwd=xxxxxxxxxx;

PostgreSQL

Host=localhost;Port=5432;Database=mydb;Username=postgres;Password=xxxxxxxxxx;

Oracle

Data Source=localhost:1521/orcl;User Id=system;Password=xxxxxxxxxx;

ClickHouse

Host=localhost;Port=8123;Database=default;User=default;Password=xxxxxxxxxx

Notes: identifiers (table/column) may be case-sensitive; ClickHouse does not support transactions.

### MongoDB

mongodb://localhost:27017/mydb

### Doris

Server=localhost;Port=9030;Database=mydb;Uid=root;Pwd=xxxxxxxxxx;Pooling=false

If you see errors like `Unsupported command` in some network/proxy environments, try `Pooling=false`.

Some environments may require disabling NVARCHAR-style literals. DbCli supports this via config: set `DisableNvarchar: true` in `appsettings.json`.

See `../CONNECTION_STRINGS.md` for complete reference.

## Programmatic Usage

### Python

```python
import subprocess
import json

result = subprocess.run([
    'dbcli', '-c', 'Data Source=app.db',
    'query', 'SELECT * FROM Users'
], capture_output=True, text=True)

users = json.loads(result.stdout)
for user in users:
    print(f"{user['Name']} - {user['Email']}")

Node.js

const { execSync } = require('child_process');

const result = execSync('dbcli -c "Data Source=app.db" query "SELECT * FROM Users"');
const users = JSON.parse(result.toString());

users.forEach(user => {
    console.log(`${user.Name} - ${user.Email}`);
});

PowerShell

$result = dbcli -c "Data Source=app.db" query "SELECT * FROM Users" | ConvertFrom-Json
$result | ForEach-Object { Write-Host "$($_.Name) - $($_.Email)" }

Error Handling

# Check exit code
dbcli -c "Data Source=app.db" query "SELECT * FROM Users"
if [ $? -eq 0 ]; then
    echo "Query succeeded"
else
    echo "Query failed"
fi

Performance Tips

1. Use specific columns instead of

   SELECT *

   for large tables
2. Add WHERE clauses to filter data at the database level
3. Use LIMIT/TOP to restrict result set size
4. Use table format (

   -f table

   ) for human review, JSON for programmatic processing
5. Store complex queries in files using

   -F

   option

Common Patterns

Check Record Count

dbcli -c "Data Source=app.db" query "SELECT COUNT(*) as total FROM Users"

Verify Table Exists

dbcli -c "Data Source=app.db" query "SELECT name FROM sqlite_master WHERE type='table' AND name='Users'"

Test Connection

dbcli -c "Data Source=app.db" query "SELECT 1"

Sample Data

# Get first 5 records
dbcli -c "Data Source=app.db" -f table query "SELECT * FROM Users LIMIT 5"

Security Considerations

1. Avoid SQL injection: Never concatenate user input directly into queries
2. Use environment variables for sensitive connection strings
3. Limit permissions: Use read-only database users for query operations
4. Validate input: Check user-provided table/column names before querying