sql-tool
PostgreSQL and TimescaleDB query and administration CLI tool.
Provides multiple output formats (table, JSON, CSV), flexible connection management (CLI flags, DSN, PG* env vars, config profiles), and admin commands for database inspection and maintenance.
Installation
git clone <repository-url>
cd sql-tool
uv sync
Run commands with uv run sql-tool or install globally:
uv tool install .
Quick Start
# Check connectivity
sql-tool service check
# Run a query
sql-tool query -e "SELECT now()"
# List databases
sql-tool databases
# List tables in a schema
sql-tool -s myschema table
# Show table columns and preview data
sql-tool table myschema.sensor_data --head 5
Commands
Query Execution
# Inline query
sql-tool query -e "SELECT * FROM users LIMIT 10"
# From file
sql-tool query queries/report.sql
# From stdin
echo "SELECT 1" | sql-tool query
# With timeout
sql-tool query -e "SELECT pg_sleep(5)" -t 3
Database Overview
# List all databases (sorted by size)
sql-tool databases
# List schemas with space usage
sql-tool schema
# Schemas across all databases
sql-tool schema --all-databases
Table Inspection
# List all tables with sizes
sql-tool table
# Filter by schema
sql-tool -s myschema table
# Show column definitions
sql-tool table myschema.sensor_data
# Preview data
sql-tool table myschema.sensor_data --head 10
sql-tool table myschema.sensor_data --tail 5
sql-tool table myschema.sensor_data --sample 3
# Show timestamp range for hypertables
sql-tool table myschema.sensor_data --range
Connections
# Active connections (non-idle)
sql-tool connections
# All connections including idle
sql-tool connections --all
# Connection summary with memory config
sql-tool connections --summary
# Filter by user, database, or state
sql-tool connections --filter-user appuser --filter-db mydb
sql-tool connections --min-duration 5
Service & Maintenance
# Check server connectivity and version
sql-tool service check
# Vacuum a table
sql-tool service vacuum my_table
sql-tool service vacuum my_table --full
sql-tool service vacuum --all
# Kill a backend process
sql-tool service kill 12345
sql-tool service kill 12345 --cancel # cancel query only
TimescaleDB
# List hypertables
sql-tool ts hypertables
# Show chunks for a hypertable
sql-tool ts chunks myschema.sensor_data
# Compression statistics
sql-tool ts compression
sql-tool ts compression myschema.sensor_data
# Compression settings and policies
sql-tool ts compression-settings -s myschema
# Configure compression
sql-tool ts compression-set myschema.sensor_data --segmentby "device_id, sensor" --orderby "timestamp DESC"
sql-tool ts compression-set myschema.sensor_data --policy "4 hours"
# Compress chunks
sql-tool ts compress myschema.sensor_data
sql-tool ts compress myschema.sensor_data --chunk 11420
# Continuous aggregates
sql-tool ts caggs
# Retention policies
sql-tool ts retention
# Background jobs
sql-tool ts jobs
sql-tool ts jobs --history
sql-tool ts jobs --history --job 1005
Configuration
# Show resolved config with source attribution
sql-tool config show
# List available profiles
sql-tool config profiles
Output Formats
All listing commands support multiple output formats:
# Table format (default in terminal)
sql-tool -f table databases
# JSON format
sql-tool -f json databases
# Compact JSON
sql-tool --compact -f json databases
# CSV format (default when piped)
sql-tool databases | head
# CSV without header
sql-tool --no-header databases
Auto-detection: table format for TTY, CSV when piped.
Connection Options
Global flags (must appear before the subcommand):
sql-tool -H myhost -p 5432 -d mydb -U myuser query -e "SELECT 1"
sql-tool --dsn "postgresql://user@host/db" databases
sql-tool -P production table
| Flag | Env Var | Description |
|---|---|---|
-H, --host |
PGHOST |
PostgreSQL host |
-p, --port |
PGPORT |
PostgreSQL port |
-d, --database |
PGDATABASE |
Database name |
-U, --user |
PGUSER |
User name |
-W, --password |
PGPASSWORD |
Password |
--dsn |
Connection DSN | |
-P, --profile |
SQL_PROFILE |
Named profile |
Configuration File
Location: ~/.config/sql-tool/config.toml
default_timeout = 30
default_format = "table"
[profiles.local]
host = "localhost"
port = 5432
dbname = "postgres"
user = "postgres"
[profiles.production]
dsn = "postgresql://readonly@prod-db.example.com:5432/myapp?sslmode=verify-full"
[profiles.staging]
dsn = "postgresql://user@staging-db.example.com/myapp"
sslmode = "verify-full"
connect_timeout = 5
See config.example.toml for a full example.
Precedence (highest to lowest)
- CLI flags (
--host,--port, etc.) --dsnflag- Environment variables (
PGHOST,PGPORT, etc.) - Named profile (
--profileorSQL_PROFILE) - Config file defaults
- Built-in defaults (
localhost:5432/postgres)
Exit Codes
| Code | Meaning |
|---|---|
| 0 | Success |
| 1 | General error (SQL syntax, permission denied) |
| 2 | Usage error (invalid arguments) |
| 3 | Input error (file not found, invalid parameters) |
| 5 | Network error (connection failed, authentication) |
| 6 | Timeout (query timeout, connection timeout) |
| 7 | Configuration error (malformed config, unknown profile) |
Development
# Run tests
uv run pytest
# Run with coverage
uv run pytest --cov=sql_tool
# Lint
uv run ruff check src/
# Type check
uv run mypy src/
Metadata
Release files for tsdb-sql-tool 0.1.0
For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.
Source distribution (sdist)
| File | Size | Uploaded | Provenance | Actions |
|---|---|---|---|---|
| tsdb_sql_tool-0.1.0.tar.gz | 226.6 kB | Without provenance | Details for tsdb_sql_tool-0.1.0.tar.gz |
Built distribution (wheel)
| File | Interpreter | ABI | Platform | Actions Reset |
|---|---|---|---|---|
| tsdb_sql_tool-0.1.0-py3-none-any.whl | Python 3 | none | any | Details for tsdb_sql_tool-0.1.0-py3-none-any.whl |
Total release size: 269.2 kB
Release files / tsdb_sql_tool-0.1.0.tar.gz
| Download URL | tsdb_sql_tool-0.1.0.tar.gz |
|---|---|
| Size | 226.6 kB |
| Tags | Source |
|
SHA-256 checksum How to use checksums |
f602a7d5501051b02a6287923da8234b24fd8bbe168a086aa3c291b1f76dfc04
|
|
BLAKE2b-256 checksum How to use checksums |
482dd4b5151d5e564e2bcd179c766bfd01a6dbed9ab6b9eacf4924b1ba863ad4
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
No |
| Uploaded via |
twine/6.2.0 CPython/3.11.3
|
Release files / tsdb_sql_tool-0.1.0-py3-none-any.whl
| Download URL | tsdb_sql_tool-0.1.0-py3-none-any.whl |
|---|---|
| Size | 42.6 kB |
| Tags | Python 3 |
|
SHA-256 checksum How to use checksums |
992d4b94f9ac798d7c769054407e9b4edabd109a4005acbadfad86ad1adf1fc3
|
|
BLAKE2b-256 checksum How to use checksums |
2afb382172bdb0310139c4856cd6cca815f43f86d141c1539482d4c577f096da
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
No |
| Uploaded via |
twine/6.2.0 CPython/3.11.3
|
Security for tsdb-sql-tool 0.1.0
Provenance
Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.
No provenance found
None of the files in this release were uploaded with provenance metadata.
A note on package security
PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.
To learn more about protecting yourself from malicious actors, visit our package security documentation.