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)
dbcrust parquet:///data/sales_2024.parquet
dbcrust csv:///logs/*.csv?header=true
dbcrust json:///api_responses.json

# 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

🧠 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;