Basic Usage

Basic Usage

Welcome to the complete DBCrust user guide! This page covers everything you need to know to become productive with DBCrust.

Advanced ORM Analysis

DBCrust includes a powerful Django ORM query analyzer that can detect N+1 queries and optimization opportunities. Learn more in the Django Analyzer Guide.

🚀 Starting DBCrust

Command Line Interface

DBCrust follows a simple pattern: dbcrust [OPTIONS] <CONNECTION_URL>

# Basic connection
dbcrust postgres://user:password@localhost:5432/database

# With smart URL scheme completion
dbc pos[TAB] → postgres://
dbc docker://my[TAB] → docker://my-postgres-container
dbc session://prod[TAB] → session://production_db

# File formats (Parquet, CSV, JSON) and SQLite files
dbcrust ./data/sales_2024.parquet          # inferred from extension
dbcrust './logs/*.csv?header=true'         # globs work too
dbcrust ./app.sqlite                       # SQLite inferred from extension
dbcrust file://                            # interactive compatible-file picker
dbcrust json:///api_responses.json         # explicit schemes still work

# With options
dbcrust --ssh-tunnel jumphost.com postgres://user@db.internal/app

# Short alias
dbc postgres://user:password@localhost/database

Shell Autocompletion

Set up shell completion for smart URL scheme suggestions:

dbcrust --completions bash > ~/.local/share/bash-completion/completions/dbcrust

See URL Schemes & Autocompletion for complete setup instructions.

Interactive vs Non-Interactive Mode

Interactive Mode

# Start interactive session
dbcrust postgres://user:pass@localhost/mydb

# You'll see the prompt
mydb=#

Non-Interactive Mode

# Execute single query
dbcrust postgres://user:pass@localhost/mydb \
  --query "SELECT COUNT(*) FROM users"

# Execute file
dbcrust postgres://user:pass@localhost/mydb \
  --file report.sql

🎯 The DBCrust Prompt

When you enter interactive mode, you’ll see a rich prompt with context:

postgres://localhost:5432/myapp as postgres
myapp=#

The prompt shows:

• Database type and connection details
• Current database name
• User you’re connected as
• Transaction state (if in a transaction)

Server Information Display

When connecting to databases, DBCrust displays server version information (similar to pgcli):

$ dbcrust postgres://postgres@localhost/myapp
Server: PostgreSQL 17.5 (Debian 17.5-1.pgdg120+1)
Version: 0.16.1
✓ Successfully connected to database
Connected! Type \h for help or \q to quit.

postgres://localhost:5432/myapp as postgres
myapp=#

What’s shown:

• Server: Database type and version string
• Version: DBCrust client version
• Connection status: Success/failure indication

Control server info display:

-- Toggle server info on/off
\serverinfo

-- Status shows current setting
Server info display is now disabled.

Configuration:

# In ~/.config/dbcrust/config.toml
show_server_info = true  # Default: true

📝 Basic Query Execution

Simple Queries

-- Basic SELECT
SELECT * FROM users LIMIT 5;

-- With WHERE clause
SELECT name, email FROM users WHERE created_at > '2024-01-01';

-- Aggregations
SELECT status, COUNT(*) as count
FROM orders
GROUP BY status;

Multi-line Queries

DBCrust automatically detects when you’re typing a multi-line query:

-- Start typing...
SELECT
  u.name,
  u.email,
  COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email
HAVING COUNT(o.id) > 5;
-- Press Enter to execute

Regex Matching on Any Backend

DBCrust understands the PostgreSQL regex operators on every backend, so the same query syntax works whether you are connected to PostgreSQL, MySQL, SQLite, ClickHouse, MongoDB, Elasticsearch, or a Parquet/CSV/JSON file:

-- Find leaked AWS access keys, on any backend
SELECT * FROM logs WHERE line ~ 'AKIA[0-9A-Z]{16}';

SELECT * FROM users WHERE email ~* '@example\.(com|org)$';  -- case-insensitive
SELECT * FROM logs WHERE line !~ '^DEBUG';                   -- negated
SELECT * FROM logs WHERE line !~* 'heartbeat';               -- negated, case-insensitive

Operator	Meaning
~	Regex match (case-sensitive)
~*	Regex match (case-insensitive)
!~	Does not match
!~*	Does not match (case-insensitive)

PostgreSQL and file formats (DataFusion) support these operators natively. For the other backends, DBCrust rewrites the operator into the native equivalent before sending the query:

Backend	line ~ 'p' becomes	Notes
PostgreSQL	line ~ 'p' (unchanged)	Native operators
Parquet/CSV/JSON	line ~ 'p' (unchanged)	Native in DataFusion
MySQL/MariaDB	line REGEXP 'p'	~ case-sensitivity follows the column collation; ~* forces (?i)
SQLite	line REGEXP 'p'	Powered by a built-in regexp() function (Rust regex syntax)
ClickHouse	match(line, 'p')	RE2 syntax
MongoDB	{line: {$regex: 'p'}}	In SELECT ... WHERE translation
Elasticsearch	line RLIKE 'p'	Lucene regex syntax; ~*/!~* are rejected (Lucene has no case-insensitive flag)

The rewrite only happens when a query actually uses a regex operator; anything DBCrust cannot parse is passed to the backend unchanged.

Elasticsearch

RLIKE compiles to a standard Lucene regexp query, so plugins that accelerate regex search on custom field types (e.g. trigram/sparse-ngram indexes) benefit automatically.

🤖 One-Shot Mode (Scripts & Agents)

Everything DBCrust does interactively also works non-interactively — the foundation for shell scripts, CI checks, and AI coding agents:

# Inline SQL (repeatable; one -c may hold several ;-separated statements)
dbcrust postgres://localhost/mydb -c "SELECT count(*) FROM users"

# Backslash commands work too
dbcrust session://prod -c '\dt' -c '\ddl users orders'

# SQL files (pure SQL; interleaves with -c in command-line order)
dbcrust session://prod -f checks.sql -c "SELECT 'done'"

# Piped stdin runs as a SQL script
echo "SELECT 1" | dbcrust ./data.sqlite

Structured output

-o/--format selects table (default), expanded, csv, json, or jsonl — also settable via the DBCRUST_FORMAT environment variable:

dbcrust session://prod -o json -c "SELECT id, email FROM users LIMIT 2"
# {"columns":["id","email"],"rows":[["1","a@x.com"],["2","b@x.com"]],"row_count":2,"truncated":false}

dbcrust session://prod -o csv -c "SELECT * FROM users" > users.csv

In one-shot mode stdout carries results only (status messages go to stderr and the pager never engages), so output pipes cleanly into jq, files, and other tools.

Guardrails and exit codes

dbcrust session://prod --read-only --timeout 30 --max-rows 500 --no-input -o json -c "…"

• --read-only rejects write statements (exit code 4); --timeout and --max-rows bound each run; --no-input fails fast instead of ever opening a prompt.
• Exit codes are stable: 0 success · 1 statement failed · 2 usage · 3 connection failure · 4 blocked by --read-only · 130 interrupted.

The full programmatic contract lives in dbcrust agents and the AI agents section.

🧠 Smart Autocompletion

DBCrust provides intelligent, context-aware autocompletion that understands both your database schema and SQL syntax context:

SQL Context-Aware Completion

DBCrust automatically detects what SQL clause you’re in and suggests appropriate completions:

✨ NEW: Cursor Context-Aware Completion

DBCrust now supports forward-looking completion - it can read the full line and suggest columns even when the table appears after the cursor:

-- 🎯 NEW: Forward-looking column completion works!
SELECT [TAB] FROM users
-- Suggests: id, name, email, created_at, status (reads 'users' after cursor!)

-- Works with complex queries
SELECT u.[TAB] FROM users u JOIN orders o ON u.id = o.user_id
-- Suggests: id, name, email, created_at, status (from users table)

-- Even with aliases and multiple tables
SELECT [TAB] FROM users u, orders o WHERE u.id = o.user_id
-- Suggests columns from BOTH users and orders tables

SELECT Clause Suggestions

SELECT [TAB]
-- Context-aware suggestions:
-- • If FROM clause present after cursor: column names from those tables
-- • Otherwise: *, COUNT(, SUM(, AVG(, MAX(, MIN(, DISTINCT

-- Column completion works when table is visible before cursor:
SELECT * FROM users WHERE [TAB]
-- Suggests: id, name, email, created_at, status (from users table)

WHERE Clause Intelligence

-- After WHERE, suggests only column names (no tables or functions)
SELECT * FROM users WHERE [TAB]
-- Suggests: id, name, email, created_at, status, active
-- Does NOT suggest: users, orders, COUNT(, *

-- Works with complex queries and multiple tables
SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE [TAB]
-- Suggests columns from BOTH users and orders tables

FROM Clause Behavior

-- After FROM, suggests table names (existing behavior preserved)
SELECT * FROM [TAB]
-- Suggests: users, orders, products, categories
-- Does NOT suggest: *, COUNT(, column names

Traditional Schema-Based Completion

Table Name Completion

SELECT * FROM us[TAB]
-- Suggests: users, user_sessions, user_preferences

Column Completion with Dot Notation

-- After table.dot, suggests columns from that specific table
SELECT users.[TAB] FROM users
-- Suggests: id, name, email, created_at, status

-- Works with aliases too
SELECT u.[TAB] FROM users u
-- Suggests: id, name, email, created_at, status

Multi-Level Nested Field Completion

For file formats (Parquet, JSON) and databases with nested structures, DBCrust supports deep nested field navigation:

-- Navigate nested structures (Parquet/JSON files)
SELECT data.[TAB] FROM sales
-- Suggests: data, data.customer, data.customer.address, data.order

-- Navigate to any depth
SELECT data.customer.[TAB] FROM sales
-- Suggests: data.customer.name, data.customer.email, data.customer.address

-- Navigate even deeper
SELECT data.customer.address.[TAB] FROM sales
-- Suggests: data.customer.address.city, data.customer.address.state, data.customer.address.zip

-- Works with complex field names containing special characters
SELECT data.exact_paths.[TAB] FROM policies
-- Suggests: auth/token/create, aws_okta/creds/management-ecr, sys/mounts/aws_okta

-- Navigate through multiple levels
SELECT data.exact_paths.auth/token/create.[TAB] FROM policies
-- Suggests: capabilities

-- Use in WHERE clauses too
SELECT * FROM res WHERE data.exact_paths.[TAB]
-- Suggests nested paths for filtering

Schema Display for Nested Fields:

When you describe a table with nested structures, DBCrust shows both a summary and detailed nested field information:

\d policies

Table: policies
Column           | Type
-----------------+--------------------
id               | Int64
data             | Struct<4 fields>
timestamp        | Utf8

Nested field details:
  data (Struct):
    - chroot_namespace: Utf8
    - exact_paths: Struct<25 fields>
    - glob_paths: Struct<10 fields>
    - root: Utf8

Features:

• Deep Navigation: Navigate nested fields to any depth
• Direct Children Only: Autocomplete shows immediate children, not all descendants
• Context-Aware: Works in SELECT, WHERE, ORDER BY, and all SQL clauses
• Special Characters: Handles field names with /, -, and other characters
• Performance: Fast even with deeply nested structures

SQL Keywords

SEL[TAB] name FR[TAB] users WH[TAB] active = true
-- Expands to: SELECT name FROM users WHERE active = true

Advanced Context Examples

🎯 Forward-Looking Completion Examples

The breakthrough cursor context-aware completion enables these previously impossible patterns:

-- All of these NOW WORK with forward-looking completion!

-- Basic forward completion
SELECT i[TAB] FROM users
-- Suggests: id (from users table that comes after cursor)

-- Multiple tables
SELECT u[TAB] FROM users u, orders o
-- Suggests: user_id, username, updated_at (prefixed completions)

-- Complex JOINs with aliases
SELECT p[TAB] FROM users u
  JOIN orders o ON u.id = o.user_id
  JOIN products p ON o.product_id = p.id
-- Suggests: price, product_name, product_id (from products table)

-- Subqueries and CTEs
SELECT name[TAB] FROM (
  SELECT id, first_name, last_name FROM users
) u
-- Suggests: first_name, last_name (from subquery columns)

How It Works

DBCrust uses a highlighter bridge to access the complete line buffer, enabling:

• Full SQL parsing - reads entire statement, not just text before cursor
• Future table detection - finds tables that appear after cursor position
• Context-aware filtering - only suggests relevant completions for current clause
• Performance optimized - no noticeable delay despite advanced parsing

ORDER BY and GROUP BY

-- After ORDER BY, suggests columns from FROM tables
SELECT * FROM users ORDER BY [TAB]
-- Suggests: id, name, email, created_at, status

-- Same for GROUP BY
SELECT COUNT(*) FROM users GROUP BY [TAB]
-- Suggests: status, created_at, department_id

HAVING Clause

-- After HAVING, suggests aggregate functions AND column names
SELECT status, COUNT(*) FROM users GROUP BY status HAVING [TAB]
-- Suggests: COUNT(, SUM(, AVG(, MAX(, MIN( and column names

Multiple Table Support

-- Autocompletion understands complex FROM clauses
SELECT * FROM users u, orders o, products p WHERE [TAB]
-- Suggests columns from users, orders, AND products tables

-- Works with JOINs too
SELECT * FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  JOIN products p ON o.product_id = p.id
WHERE [TAB]
-- Suggests: u.id, u.name, o.status, o.total, p.name, p.price, etc.

Context-Aware Benefits

The context-aware completion provides intelligent suggestions:

• 🎯 Forward-looking completion - reads full line to suggest columns even when table comes after cursor
• Smart SELECT suggestions - aggregates, functions, * and column names from future tables
• Precise WHERE completions - only relevant column names from visible tables
• Smart FROM clause parsing - extracts tables from complex queries
• Table-qualified completions - table.[TAB] suggests columns from that table

NEW: No More Completion Limitations!

DBCrust now supports cursor context-aware completion:

• ✅ SELECT [TAB] FROM table - NOW WORKS! Suggests columns from table
• ✅ SELECT col[TAB] FROM users, orders - Suggests from all tables
• ✅ Complex queries - Works with JOINs, aliases, and subqueries

This breakthrough feature uses advanced line buffer analysis to overcome terminal limitations.

Performance

Context analysis is lightweight and fast:

• Real-time parsing - no noticeable delay
• Cached schema info - table/column data cached for speed
• Smart filtering - only relevant completions shown

📊 Result Display Options

Default Table Format

╭────┬─────────────┬──────────────────────┬────────────╮
│ id │ name        │ email                │ created_at │
├────┼─────────────┼──────────────────────┼────────────┤
│ 1  │ John Doe    │ john@example.com     │ 2024-01-15 │
│ 2  │ Jane Smith  │ jane@example.com     │ 2024-01-16 │
╰────┴─────────────┴──────────────────────┴────────────╯

Expanded Display

For wide tables, toggle expanded display:

\x  -- Toggle expanded display

SELECT * FROM users WHERE id = 1;

Output:

-[ RECORD 1 ]----------
id         | 1
name       | John Doe
email      | john@example.com
created_at | 2024-01-15
status     | active
bio        | Software engineer with 10 years of experience...

psql-Compatible Output

\pset border 2  -- Set border style
\pset format aligned  -- Set format

🔍 Query Analysis with EXPLAIN

Enable EXPLAIN mode to see query execution plans:

\e  -- Toggle EXPLAIN mode

-- Now all queries show execution plans
SELECT * FROM users WHERE email = 'john@example.com';

Output:

○ Execution Time: 0.89 ms
○ Planning Time: 0.12 ms

Index Scan using email_idx on users
│ Index Cond: (email = 'john@example.com'::text)
│ ○ Cost: 0.29..8.31
│ ○ Rows: 1
│ ○ Width: 156
└─ Returns: id, name, email, created_at, status, bio

EXPLAIN Options

-- Enable different EXPLAIN modes
\e on           -- Basic EXPLAIN
\e analyze      -- EXPLAIN ANALYZE
\e verbose      -- EXPLAIN VERBOSE
\e buffers      -- EXPLAIN (ANALYZE, BUFFERS)

-- Disable EXPLAIN
\e off

Advanced Performance Analysis

EXPLAIN is just the beginning! Explore comprehensive performance tools:

• Performance Analysis Guide - Complete query optimization
• Django ORM Analyzer - Detect N+1 queries and Django performance issues
• Advanced Features - Column selection and query profiling

💾 History and Sessions

Command History

DBCrust maintains a persistent history of your commands:

-- Search history with Ctrl+R
-- Navigate with Up/Down arrows
-- History is saved between sessions

Session Management

DBCrust provides two distinct features for managing connections:

Saved Sessions

Named sessions for frequently used connections:

-- Save current connection as a session
\ss production

-- List all saved sessions
\s

-- Connect to a saved session interactively
\s production

-- Delete a saved session
\sd old_staging

Command line access:

# Connect using saved session
dbcrust session://production

# Run query on saved session
dbcrust session://production -c "SELECT version()"

Advanced Session Features

DBCrust sessions integrate with enterprise security systems:

• SSH Tunneling - Automatic tunnel configuration
• Vault Integration - Dynamic credential management
• Docker Integration - Container-based sessions
• Advanced Features Guide - Complete session management

Connection History

Automatic tracking of all connections:

-- List recent connections with full URLs
\r

-- Clear connection history
\rc

Interactive reconnection:

# Select from recent connections interactively
dbcrust recent://

Full URL Storage

Connection history stores complete URLs including Docker containers:

• docker://user@my-postgres-container/myapp
• postgres://user@host:5432/database
• mysql://user@host:3306/database

📁 File Operations

Executing SQL Files

-- Execute a SQL file
\i scripts/create_tables.sql

-- Execute with relative path
\i ../migrations/001_add_users.sql

Saving Queries

-- Write last query to file
\w my_query.sql

-- Write specific content
\w backup_script.sql
SELECT pg_dump('mydb');

External Editor

For complex queries, use your preferred editor:

-- Open external editor (uses $EDITOR)
\ed

-- Edit, save, and close - query executes automatically

Editor integration works with:

• vim/nvim - Full syntax highlighting
• VS Code - code --wait for integration
• nano - Simple editing
• emacs - Advanced editing features

🏷️ Named Queries

Save frequently used queries with parameters:

-- Save a parameterized query
\ns active_users SELECT * FROM users WHERE status = '$1' AND created_at > '$2';

-- Use the named query
active_users premium '2024-01-01'
active_users trial '2024-06-01'

-- List all named queries
\n

-- Delete a named query
\nd active_users

Parameter Substitution

Named queries support flexible parameter substitution:

-- Single parameter
\ns user_by_id SELECT * FROM users WHERE id = $1;

-- Multiple parameters
\ns user_orders SELECT * FROM orders WHERE user_id = $1 AND status = '$2';

-- All remaining parameters
\ns search_users SELECT * FROM users WHERE name ILIKE '%$*%';

🎨 Customization

Display Preferences

-- Toggle various display options
\x              -- Expanded display
\pset border 1  -- Border style (0, 1, 2)
\pset null 'NULL'  -- How to display NULL values
\timing on      -- Show query execution time

Configuration

View and modify settings:

-- Show current configuration
\config

-- Configuration is stored in ~/.config/dbcrust/config.toml

Example configuration:

[database]
default_limit = 1000
expanded_display_default = false
show_execution_time = true

[display]
null_display = "NULL"
border_style = 1
date_format = "%Y-%m-%d"

[editor]
command = "code --wait"
temp_dir = "/tmp"

⌨️ Keyboard Shortcuts

Shortcut	Action
Ctrl+C	Cancel current input
Ctrl+D	Exit DBCrust
Ctrl+L	Clear screen
Ctrl+R	Search command history
Ctrl+A	Move to beginning of line
Ctrl+E	Move to end of line
Ctrl+U	Delete to beginning of line
Ctrl+K	Delete to end of line
Ctrl+W	Delete previous word
Tab	Autocomplete
Shift+Tab	Previous autocomplete suggestion
Up/Down	Navigate command history
Ctrl+Up/Down	Navigate multi-line input

🚪 Exiting DBCrust

-- Any of these will exit
\q
\quit
exit
-- Or press Ctrl+D

💡 Pro Tips

Startup Scripts

Create a startup script for common tasks:

-- ~/.config/dbcrust/startup.sql
\timing on
\x auto
SET search_path TO public, analytics;

Aliases

Create shell aliases for common connections:

# In ~/.bashrc or ~/.zshrc
alias dbp='dbcrust postgres://postgres@localhost/production'
alias dbd='dbcrust postgres://postgres@localhost/development'

Quick Data Exploration

-- Quick table overview
SELECT
  column_name,
  data_type,
  is_nullable
FROM information_schema.columns
WHERE table_name = 'users';

-- Row counts for all tables
SELECT
  schemaname,
  tablename,
  n_tup_ins as inserts,
  n_tup_upd as updates,
  n_tup_del as deletes
FROM pg_stat_user_tables;