Dbcli Query

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 | [email protected] |
# | 2  | Bob   | [email protected]   |
# +----+-------+-------------------+

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