pgcli-rs

A self-contained PostgreSQL CLI written in pure Rust. No libpq. No system libraries. One binary.

🤔 Why pgcli-rs?

psql requires a full PostgreSQL client installation (libpq, system libraries, OS packages). This creates friction in containers, CI pipelines, minimal Linux distros, macOS without Homebrew, and Windows. pgcli-rs eliminates that friction: one binary, zero system dependencies, cross-platform.

Feature	psql	pgcli
Requires libpq	✅ Yes	❌ No
Single static binary	❌ No	✅ Yes
Windows native	⚠️ Limited	✅ Yes
SQL syntax highlighting	❌ No	✅ Yes
JSON / CSV / JSONL / TSV export	❌ No	✅ Yes
Deep schema introspection	⚠️ Limited	✅ Yes
DDL generation	❌ No	✅ Yes
Benchmark / profiling mode	❌ No	✅ Yes
SCRAM-SHA-256 auth (pure Rust)	✅ Yes	✅ Yes
musl static binary	❌ No	✅ Yes

✨ Key Features

🔌 Zero dependencies - pure Rust wire protocol, no libpq, no system libs
🦀 Single static binary - drop it anywhere and run (musl on Linux)
🖥️ Cross-platform - Linux, macOS, Windows (x86_64 and ARM64)
🔐 Full auth support - SCRAM-SHA-256, MD5, Trust, .pgpass file
🔒 TLS modes - disable / prefer / require / verify-full
🎨 SQL syntax highlighting - real-time coloring in the REPL
📜 Multi-line input - automatic continuation prompt for incomplete statements
📊 Multiple output formats - table, csv, json, jsonl, tsv, html
📤 Data export - INSERT statements, COPY format, file output
🔍 Deep introspection - system catalog queries for tables, indexes, functions, sequences
🧾 DDL generation - reconstruct CREATE TABLE statements from live schema
📝 Script execution - .sql file runner with variable substitution
🏁 Single-transaction mode - wrap script files in a transaction
📈 Benchmark mode - --repeat N + --stats for latency histograms and throughput
🕘 Persistent history - deduplication, configurable size

📦 Installation

Prebuilt binaries

Download from the Releases page:

Platform	Binary
Linux x86_64	`pgcli-rs-linux-x86_64.tar.gz`
Linux ARM64	`pgcli-rs-linux-aarch64.tar.gz`
macOS x86_64	`pgcli-rs-macos-x86_64.tar.gz`
macOS ARM64	`pgcli-rs-macos-aarch64.tar.gz`
Windows x86_64	`pgcli-rs-windows-x86_64.zip`

Build from source

git clone https://github.com/jhonsferg/pgcli-rs
cd pgcli-rs
cargo build --release
./target/release/pgcli --version

Fully static musl binary (Linux):

rustup target add x86_64-unknown-linux-musl
cargo build --release --target x86_64-unknown-linux-musl
# Result: target/x86_64-unknown-linux-musl/release/pgcli (~7 MB, no dynamic deps)

🚀 Quick Start

Interactive REPL

# Connect with individual flags
pgcli -h localhost -p 5432 -U postgres -d mydb

# Connect via URI
pgcli "postgresql://myuser:mypass@localhost:5432/mydb"

# Connect with TLS (require encrypted connection)
pgcli -h db.example.com -U postgres -d prod --require-tls

# Connect to a local socket (Unix domain socket)
pgcli -h /var/run/postgresql -U postgres -d mydb

Once connected you will see:

pgcli 0.1.0-connected to mydb at localhost:5432
Type \? for help. Type \q or press Ctrl-D to exit.

mydb=#

One-shot command mode (`-c`)

# Run a SQL query and exit
pgcli -h localhost -U postgres -c "SELECT version();"

# Run a meta-command (backslash command) and exit
pgcli -h localhost -U postgres -c "\l"
pgcli -h localhost -U postgres -d mydb -c "\dt"

# List all databases
pgcli -l -h localhost -U postgres

Output formats

# Table (default)
pgcli -h localhost -U postgres -d mydb -c "SELECT id, name, salary FROM employees LIMIT 5;"

# CSV-pipe-friendly
pgcli -h localhost -U postgres -d mydb --format csv \
  -c "SELECT id, name, salary FROM employees LIMIT 5;"

# JSON array
pgcli -h localhost -U postgres -d mydb --format json \
  -c "SELECT id, name FROM employees LIMIT 3;"

# JSON Lines (one object per line)
pgcli -h localhost -U postgres -d mydb --format jsonl \
  -c "SELECT id, name FROM employees LIMIT 3;"

# Tab-separated
pgcli -h localhost -U postgres -d mydb --format tsv \
  -c "SELECT id, name, salary FROM employees LIMIT 5;"

# HTML table
pgcli -h localhost -U postgres -d mydb --format html \
  -c "SELECT * FROM products LIMIT 10;" -o report.html

File execution

# Execute a SQL script
pgcli -f schema.sql -h localhost -U postgres -d mydb

# Execute as a single transaction (all-or-nothing)
pgcli -f migrations/0042_add_column.sql -h localhost -U postgres -d mydb \
  --single-transaction

Benchmark / profiling mode

# Run the same query 20 times and print a latency histogram
pgcli -h localhost -U postgres -d mydb --repeat 20 --stats \
  -c "SELECT count(*), avg(salary) FROM employees;"

# Run 10 iterations without printing intermediate results (stats only)
pgcli -h localhost -U postgres -d mydb --repeat 10 --stats \
  -c "SELECT * FROM orders WHERE status = 'delivered' LIMIT 1000;"

Sample benchmark output:

-- Benchmark: 20 runs -----------------------------
  Latency (ms)    min=1.899  avg=2.360  max=3.612
  Percentiles     p50=2.180  p95=3.295  p99=3.612
  Throughput      424 rows/sec
  Data xfer       14.2 KB total  (6017 KB/sec)
  Total rows      20
  Process RSS     5192 KB
------------------------------------------------

🔧 Full Flag Reference

Connection flags (psql compatible)

Flag	Long form	Default	Description
`-h HOST`	`--host`	`localhost` or `$PGHOST`	Server hostname or IP address
`-p PORT`	`--port`	`5432` or `$PGPORT`	Server TCP port
`-U USER`	`--username`	`$PGUSER` or current OS user	Database login user
`-d DBNAME`	`--dbname`	`$PGDATABASE` or username	Target database name
`-W`	`--password`	-	Force interactive password prompt
`-w`	`--no-password`	-	Never prompt for password; fail instead

Execution flags

Flag	Long form	Description
`-c SQL`	`--command`	Execute single SQL statement or meta-command and exit
`-f FILE`	`--file`	Execute a SQL script file and exit
`-o FILE`	`--output`	Write output to FILE instead of stdout
`-l`	`--list`	List all databases and exit
`-1`	`--single-transaction`	Wrap `-f` script in a single transaction

Output flags (psql compatible)

Flag	Long form	Description
`-t`	`--tuples-only`	Print rows only; suppress headers and footers
`-A`	`--no-align`	Unaligned output mode
`-H`	`--html`	HTML `<table>` output
`-F SEP`	`--field-separator`	Field separator for unaligned mode (default: `\|`)
`-R SEP`	`--record-separator`	Record separator (default: newline)
`-q`	`--quiet`	Suppress informational messages
`-e`	`--echo-queries`	Echo each query to stderr before executing

TLS / SSL flags

Flag	Description
`--no-tls`	Disable TLS completely (insecure)
`--require-tls`	Require TLS; fail if server does not support it
`--tls-ca FILE`	CA certificate file for server certificate verification
`--tls-cert FILE`	Client certificate file (mutual TLS)
`--tls-key FILE`	Client private key file (mutual TLS)

pgcli extension flags

Flag	Default	Description
`--format FORMAT`	`table`	Output format: `table` `csv` `json` `jsonl` `tsv` `html`
`--export FILE`	-	Save result to FILE (format inferred from extension)
`--timeout SECS`	`30`	Connection and query timeout in seconds
`--max-rows N`	`1000`	Max rows displayed in interactive mode
`--pager CMD`	`less -RFX`	Override pager command
`--no-pager`	-	Disable pager output
`--theme THEME`	`dark`	Color theme: `dark` `light` `none`
`--history-file FILE`	`~/.pgcli-rs_history`	History file path
`--config FILE`	`~/.config/pgcli-rs/config.toml`	Config file path
`--repeat N`	`1`	Repeat `--command` N times (benchmark mode)
`--stats`	-	Print latency histogram and throughput after execution

🔡 Meta-Commands (REPL)

Navigation & Connection

Command	Description
`\q`, `\quit`	Exit pgcli
`\c DBNAME [USER]`	Connect to a different database
`\conninfo`	Show current connection details
`\! CMD`	Execute a shell command
`\?` , `\help`	Show meta-command help

Schema Introspection

Command	Description
`\l [PATTERN]`, `\list`	List databases
`\d [PATTERN]`	Describe tables, views, and sequences matching pattern
`\dt [PATTERN]`	List tables (`schema.table` patterns supported, e.g. `\dt hr.*`)
`\dv [PATTERN]`	List views
`\di [PATTERN]`	List indexes
`\ds [PATTERN]`	List sequences
`\df [PATTERN]`	List functions
`\dn [PATTERN]`	List schemas
`\du [PATTERN]`	List roles and permissions

Pattern syntax: * matches anything, ? matches a single character, schema.name filters by both schema and object name.

-- List all tables in the "hr" schema
\dt hr.*

-- List tables whose names start with "ord"
\dt ord*

-- Describe the employees table
\d hr.employees

Display & Formatting

Command	Description
`\x [on\|off]`	Toggle expanded (vertical) display mode
`\timing [on\|off]`	Toggle query execution timing
`\format FORMAT`	Switch output format mid-session
`\theme THEME`	Switch color theme mid-session

pgcli Extensions

Command	Description
`\size [TABLE]`	Show disk size of entire database or a specific table
`\locks`	Show active locks from `pg_locks`
`\activity`	Show active queries from `pg_stat_activity`
`\introspect TABLE`	Deep introspection of a table (columns, constraints, indexes)
`\ddl TABLE`	Generate `CREATE TABLE` DDL for a table

Variables

Command	Description
`\set [VAR VALUE]`	Set a session variable (or list all variables)
`\unset VAR`	Remove a session variable
`\echo TEXT`	Print text to stdout

🔑 Authentication

pgcli supports all common PostgreSQL authentication methods:

Method	Description
Trust	No password required
Password (cleartext)	Simple password over the wire (use TLS!)
MD5	Salted MD5 password hash
SCRAM-SHA-256	Modern, secure challenge-response auth (default in PG14+)

Password sources (priority order)

--password / -W flag → interactive prompt
$PGPASSWORD environment variable
~/.pgpass file (or $PGPASSFILE)
.pgpass record format: hostname:port:database:username:password

# ~/.pgpass example
db.example.com:5432:mydb:myuser:s3cr3t
*:5432:*:readonly:readonlypass

🌐 Environment Variables

pgcli respects all standard PostgreSQL environment variables:

Variable	Description
`PGHOST`	Server hostname
`PGPORT`	Server port
`PGUSER`	Database user
`PGPASSWORD`	Password (prefer `~/.pgpass` for security)
`PGDATABASE`	Database name
`PGPASSFILE`	Path to `.pgpass` file
`PGSSLMODE`	TLS mode: `disable` `allow` `prefer` `require` `verify-full`
`PAGER`	Pager command (e.g. `less -RFX`, `more`)
`RUST_LOG`	Log level filter (e.g. `RUST_LOG=info`)

⚙️ Configuration File

Default location: ~/.config/pgcli-rs/config.toml

[connection]
host = "localhost"
port = 5432
timeout_secs = 30

[display]
theme = "dark"           # dark | light | none
syntax_highlight = true
max_rows = 1000
border = 1
null_display = ""
expanded = "auto"        # auto | on | off

[pager]
command = "less -RFX"
enabled = true

[history]
file = "~/.pgcli-rs_history"
max_entries = 10000

[output]
format = "table"         # table | csv | json | jsonl | tsv | html
timing = true

🏗️ Architecture

pgcli is built on a clean layered architecture with zero C dependencies:

main.rs
  └-- CliArgs            (cli/args.rs)         - clap-derive argument parsing
  └-- ConnectionConfig   (connection/config.rs) - merge CLI + env + .pgpass
        └-- TlsConfig    (connection/tls.rs)    - TLS mode handling
        └-- ConnectionPool (connection/pool.rs)   - single-connection pool + reconnect
              └-- AuthHandler (protocol/auth.rs)     - SCRAM-SHA-256 / MD5 / Trust
  └-- [non-interactive] ScriptPipeline / QueryExecutor
  └-- [interactive] ReplEditor
        └-- MetaCommandDispatcher (meta/commands.rs)
        └-- QueryExecutor         (executor/query.rs)
              └-- QueryResult     (protocol/messages.rs)
                    └-- Formatter (output/formats.rs)
                          └-- Pager (output/pager.rs)

Key dependencies:

Crate	Role
`tokio-postgres`	Pure-Rust PostgreSQL wire protocol
`tokio`	Async runtime
`rustls` / `native-tls`	TLS backends
`rustyline`	Readline/REPL input
`comfy-table`	Unicode table rendering
`clap`	CLI argument parsing
`tracing`	Structured logging

📈 Performance

Benchmarks measured against a PostgreSQL 16 server over LAN:

Workload	Platform	p50 latency	p95 latency	Throughput
Simple aggregate (20 runs)	Linux (loopback)	2.2 ms	3.3 ms	424 rows/sec
Simple aggregate (20 runs)	Windows (LAN)	12.0 ms	27.1 ms	62 rows/sec
1 000-row result (5 runs)	Linux (loopback)	9.9 ms	11.4 ms	99 K rows/sec
1 000-row result (5 runs)	Windows (LAN)	23.4 ms	32.2 ms	41 K rows/sec
Complex JOIN + GROUP BY	Linux (loopback)	14.3 ms	17.2 ms	476 rows/sec

Memory footprint: < 10 MB RSS at peak for a 1 000-row result set.

🧪 Running Tests

# Unit tests - no database required
cargo test --lib

# Lint check
cargo clippy -- -D warnings

# Format check
cargo fmt --check

# Integration tests - requires a live PostgreSQL instance
PGCLI_RS_TEST_DSN="postgresql://user:pass@localhost/testdb" \
  cargo test --features integration-tests

# Build documentation
cargo doc --no-deps --open

🔐 TLS Examples

# Prefer TLS (default) - upgrades if server supports it
pgcli -h db.example.com -U postgres -d mydb

# Require TLS - fail if server does not support it
pgcli -h db.example.com -U postgres -d mydb --require-tls

# Verify server certificate (verify-full)
pgcli -h db.example.com -U postgres -d mydb \
  --require-tls --tls-ca /etc/ssl/certs/my-ca.crt

# Mutual TLS (client certificate authentication)
pgcli -h db.example.com -U postgres -d mydb \
  --require-tls \
  --tls-cert ~/.config/pgcli-rs/client.crt \
  --tls-key  ~/.config/pgcli-rs/client.key \
  --tls-ca   ~/.config/pgcli-rs/ca.crt

# Via environment variable
PGSSLMODE=require pgcli -h db.example.com -U postgres -d mydb

📤 Data Export Examples

# Export query result to CSV file
pgcli -h localhost -U postgres -d mydb \
  -c "SELECT * FROM employees" \
  --format csv -o employees.csv

# Export to JSON
pgcli -h localhost -U postgres -d mydb \
  -c "SELECT id, name, salary FROM employees WHERE active = true" \
  --format json -o active_employees.json

# Export as SQL INSERT statements
pgcli -h localhost -U postgres -d mydb \
  -c "\export employees"

# Pipe CSV directly to another tool
pgcli -h localhost -U postgres -d mydb \
  --format csv --tuples-only \
  -c "SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 100" \
  | sort -t, -k2 -rn | head -10

🐋 Usage in Docker / CI

Because pgcli-rs is a single static binary, it is ideal for containers and CI pipelines:

# Multi-stage: copy the binary into a scratch container
FROM scratch
COPY --from=builder /workspace/pgcli /usr/local/bin/pgcli-rs

# GitHub Actions example
- name: Run DB migration check
  run: |
    ./pgcli -h ${{ secrets.DB_HOST }} \
               -U ${{ secrets.DB_USER }} \
               -d ${{ secrets.DB_NAME }} \
               -f migrations/latest.sql \
               --single-transaction
  env:
    PGPASSWORD: ${{ secrets.DB_PASSWORD }}

🤝 Contributing

See CONTRIBUTING.md for development setup, coding conventions, and the pull request process.

Coding rules at a glance:

No .unwrap() / .expect() outside main.rs
All pub items have /// doc comments
Every module has a #[cfg(test)] block
cargo fmt + cargo clippy -- -D warnings must pass
No libpq or C PostgreSQL bindings of any kind
Use tracing::error! / warn! / info!-never eprintln!

📜 License

MIT License. See LICENSE.

Built with 🦀 Rust | Pure PostgreSQL wire protocol | Zero system dependencies

Name		Name	Last commit message	Last commit date
Latest commit History 90 Commits
.github		.github
assets/logo		assets/logo
src		src
.editorconfig		.editorconfig
.gitignore		.gitignore
.rustfmt.toml		.rustfmt.toml
CHANGELOG.md		CHANGELOG.md
CODE_OF_CONDUCT.md		CODE_OF_CONDUCT.md
CONTRIBUTING.md		CONTRIBUTING.md
Cargo.lock		Cargo.lock
Cargo.toml		Cargo.toml
Cross.toml		Cross.toml
LICENSE		LICENSE
Makefile		Makefile
README.md		README.md
SECURITY.md		SECURITY.md
deny.toml		deny.toml
rust-toolchain.toml		rust-toolchain.toml

Folders and files

Latest commit

History

Repository files navigation

pgcli-rs

🤔 Why pgcli-rs?

✨ Key Features

📦 Installation

Prebuilt binaries

Build from source

🚀 Quick Start

Interactive REPL

One-shot command mode (-c)

Output formats

File execution

Benchmark / profiling mode

🔧 Full Flag Reference

Connection flags (psql compatible)

Execution flags

Output flags (psql compatible)

TLS / SSL flags

pgcli extension flags

🔡 Meta-Commands (REPL)

Navigation & Connection

Schema Introspection

Display & Formatting

pgcli Extensions

Variables

🔑 Authentication

Password sources (priority order)

🌐 Environment Variables

⚙️ Configuration File

🏗️ Architecture

📈 Performance

🧪 Running Tests

🔐 TLS Examples

📤 Data Export Examples

🐋 Usage in Docker / CI

🤝 Contributing

📜 License

About

Resources

License

Code of conduct

Contributing

Security policy

Uh oh!

Stars

Watchers

Forks

Releases 3

Packages 0

Uh oh!

Contributors

Uh oh!

Languages

One-shot command mode (`-c`)

Packages