Backslash Commands Reference¶

DBCrust provides a comprehensive set of backslash commands (meta-commands) that help you navigate and manage your database sessions efficiently. These commands are inspired by PostgreSQL's psql but enhanced with modern features.

📚 Command Categories¶

🔍 Detailed Command Reference¶

Navigation Commands¶

\l - List Databases¶

Lists all databases on the current server.

\l

Output:

╭──────────────┬───────────┬──────────┬───────────────╮
│ Name         │ Owner     │ Encoding │ Description   │
├──────────────┼───────────┼──────────┼───────────────┤
│ myapp_dev    │ postgres  │ UTF8     │ Development   │
│ myapp_prod   │ postgres  │ UTF8     │ Production    │
│ analytics    │ analyst   │ UTF8     │ Data warehouse│
╰──────────────┴───────────┴──────────┴───────────────╯

\dt - List Tables¶

Lists all tables in the current database.

\dt

Output:

╭─────────────┬──────────┬──────────┬─────────────╮
│ Schema      │ Name     │ Type     │ Owner       │
├─────────────┼──────────┼──────────┼─────────────┤
│ public      │ users    │ table    │ postgres    │
│ public      │ orders   │ table    │ postgres    │
│ public      │ products │ table    │ postgres    │
╰─────────────┴──────────┴──────────┴─────────────╯

\d [table] - Describe Table¶

Without arguments, lists all tables. With a table name, shows detailed table structure.

-- List all tables
\d

-- Describe specific table
\d users

Output for \d users:

Table "public.users"
╭─────────────┬─────────────────────┬───────────┬─────────┬─────────────╮
│ Column      │ Type                │ Nullable  │ Default │ Description │
├─────────────┼─────────────────────┼───────────┼─────────┼─────────────┤
│ id          │ integer             │ not null  │ nextval │ Primary key │
│ name        │ character varying   │ not null  │         │ Full name   │
│ email       │ character varying   │ not null  │         │ Email addr  │
│ created_at  │ timestamp           │ not null  │ now()   │ Created     │
│ status      │ character varying   │           │ active  │ User status │
╰─────────────┴─────────────────────┴───────────┴─────────┴─────────────╯

Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)
    "idx_users_status" btree (status)

\c <database> - Connect to Database¶

Switches to a different database on the same server.

\c production_db

Output:

You are now connected to database "production_db" as user "postgres".

Display Commands¶

\x - Toggle Expanded Display¶

Switches between table and expanded (vertical) display formats.

\x

Before (table format):

╭────┬──────────┬──────────────────────╮
│ id │ name     │ email                │
├────┼──────────┼──────────────────────┤
│ 1  │ John Doe │ john@example.com     │
╰────┴──────────┴──────────────────────╯

After (expanded format):

-[ RECORD 1 ]----------
id    | 1
name  | John Doe
email | john@example.com

\e - Toggle EXPLAIN Mode¶

Enables or disables automatic EXPLAIN for all queries.

\e  -- Enable EXPLAIN mode

SELECT * FROM users WHERE email = 'john@example.com';

Output with EXPLAIN enabled:

○ 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

╭────┬──────────┬──────────────────────╮
│ id │ name     │ email                │
├────┼──────────┼──────────────────────┤
│ 1  │ John Doe │ john@example.com     │
╰────┴──────────┴──────────────────────╯

EXPLAIN modes:

\e on          -- Basic EXPLAIN
\e analyze     -- EXPLAIN ANALYZE
\e verbose     -- EXPLAIN VERBOSE
\e buffers     -- EXPLAIN (ANALYZE, BUFFERS)
\e off         -- Disable EXPLAIN

\ecopy - Copy EXPLAIN to Clipboard¶

Copies the last EXPLAIN plan in JSON format to your clipboard.

\ecopy

Output:

EXPLAIN plan copied to clipboard (JSON format)

\cs - Toggle Column Selection Mode¶

Enables or disables interactive column selection for all queries. When enabled, all queries will prompt for column selection regardless of the number of columns.

\cs  -- Toggle column selection mode on/off

Output:

Column selection is now enabled.

\csthreshold <number> - Set Column Selection Threshold¶

Configures the number of columns that triggers automatic column selection. This setting is saved to your configuration file.

-- Set threshold to 15 columns
\csthreshold 15

-- Set threshold to 5 columns for detailed work
\csthreshold 5

Output:

Column selection threshold set to: 15

Default threshold: 10 columns

Interactive Column Selection Interface¶

When column selection is triggered (either automatically or via \cs mode), an interactive interface appears:

Features: - Visual Selection: Checkbox-style interface with arrow key navigation - Multi-Select: Use spacebar to select/deselect multiple columns - Keyboard Controls: - ↑/↓ Arrow keys: Navigate between columns - Space: Toggle column selection - Enter: Confirm selection and show results - Ctrl+C: Abort query and return to prompt (doesn't exit DBCrust)

Example Usage:

-- This query has 11 columns, exceeds default threshold of 10
SELECT * FROM users_detailed;

Interactive Interface:

? Select columns to display:
❯ ◯ id
  ◯ username
  ◯ email
  ◯ first_name
  ◯ last_name
  ◯ created_at
  ◯ updated_at
  ◯ last_login
  ◯ is_active
  ◯ phone
  ◯ address
[↑↓ to move, space to select, enter to confirm, ctrl+c to abort]

After selection (e.g., selecting id, username, email):

Showing 3 of 11 columns
╭────┬──────────┬──────────────────────╮
│ id │ username │ email                │
├────┼──────────┼──────────────────────┤
│ 1  │ john_doe │ john@example.com     │
│ 2  │ jane_doe │ jane@example.com     │
╰────┴──────────┴──────────────────────╯

Session Persistence¶

Column selections are automatically remembered during your session:

Behavior: - Selections saved per table structure (based on column names) - Subsequent queries on same table use saved selection automatically - Persists until you clear selections or reset views

Example:

-- First time: interactive selection appears
SELECT * FROM users_detailed;
-- [Select id, username, email]

-- Second time: uses saved selection automatically
SELECT * FROM users_detailed WHERE created_at > '2024-01-01';
-- Shows only id, username, email columns

\clrcs - Clear Column Selections¶

Removes all saved column selections, returning to fresh selection state for all tables.

\clrcs

Output:

Column views cleared.

After clearing, the next query on any table will prompt for column selection again.

\resetview - Reset All View Settings¶

Resets all display settings to defaults, including: - Column selections (clears all saved selections) - Expanded display mode (\x) - EXPLAIN mode (\e)

\resetview

Output:

View settings reset to defaults.

File Operations¶

\w <filename> - Write Script to File¶

Saves the last executed query or script to a file.

-- Execute a query
SELECT * FROM users WHERE created_at > '2024-01-01';

-- Save it to file
\w recent_users.sql

Output:

Script written to 'recent_users.sql' (156 bytes)

\i <filename> - Execute SQL File¶

Loads and executes SQL commands from a file.

\i setup_tables.sql

File contents (setup_tables.sql):

CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO users (name, email) VALUES
('Alice Johnson', 'alice@example.com'),
('Bob Smith', 'bob@example.com');

Output:

Executing setup_tables.sql...
CREATE TABLE
INSERT 0 2
Script execution completed successfully.

\ed - External Editor¶

Opens your default editor to write or edit a query.

\ed

Process: 1. Opens $EDITOR (vim, nano, code, etc.) 2. Edit your query 3. Save and close 4. Script is loaded and ready - press Enter to execute

Editor integration:

# Set preferred editor
export EDITOR="code --wait"  # VS Code
export EDITOR="vim"          # Vim
export EDITOR="nano"         # Nano

Workflow tip: After using \ed or \i, press Enter on an empty line to re-execute the last loaded script.

Named Queries¶

DBCrust provides a powerful scoped named query system that allows you to organize queries by visibility scope: global, database-type specific, or session-local.

Query Scopes¶

Global Scope - Available across all database connections and sessions Database-Type Scope - Available only for specific database types (PostgreSQL, MySQL, SQLite) Session-Local Scope - Available only for the current database session (host+port+database+user)

\n - List Named Queries¶

Shows all named queries available in the current context, with scope indicators.

\n

Output:

Named queries:
  active_users     [global]     - SELECT * FROM users WHERE status = 'active'
  pg_stats         [postgres]   - SELECT * FROM pg_stat_activity
  daily_summary    [session]    - SELECT DATE(created_at), COUNT(*) FROM orders
  user_report      [global]     - SELECT u.*, COUNT(o.id) FROM users u LEFT JOIN orders o ON u.id = o.user_id

Scope Priority: Session-local queries take precedence over database-type queries, which take precedence over global queries when names conflict.

\ns <name> <query> [--scope] - Save Named Query with Scope¶

Saves a query with a name and optional scope specification. Supports parameter substitution.

Scope Options: - --global - Available for all database connections - --postgres - Available only for PostgreSQL connections - --mysql - Available only for MySQL connections - --sqlite - Available only for SQLite connections - No flag (default) - Session-local scope (current database session only)

Basic Examples:

-- Session-local query (default)
\ns active_users SELECT * FROM users WHERE status = 'active'

-- Global query (all databases)
\ns count_all SELECT COUNT(*) FROM $1 --global

-- PostgreSQL-specific query
\ns pg_activity SELECT * FROM pg_stat_activity --postgres

-- MySQL-specific query
\ns mysql_status SHOW GLOBAL STATUS LIKE 'Connections' --mysql

-- SQLite-specific query
\ns sqlite_tables SELECT name FROM sqlite_master WHERE type='table' --sqlite

Parameter Substitution:

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

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

-- All remaining parameters (space-separated)
\ns search_users SELECT * FROM users WHERE name ILIKE '%$*%' --global

-- All remaining parameters (single string)
\ns full_search SELECT * FROM users WHERE CONCAT(first_name, ' ', last_name) ILIKE '%$@%'

Advanced Scope Examples:

-- Database-type specific reporting queries
\ns pg_table_sizes SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size FROM pg_tables --postgres

\ns mysql_table_info SELECT table_name, table_rows, data_length FROM information_schema.tables WHERE table_schema = DATABASE() --mysql

-- Global utility queries
\ns today_records SELECT * FROM $1 WHERE DATE(created_at) = CURRENT_DATE --global

-- Session-specific queries (no flag needed)
\ns my_analysis SELECT customer_id, SUM(amount) FROM local_sales_data GROUP BY customer_id

Query Execution:

-- Execute named queries with parameters
active_users
user_by_id 123
user_orders 123 completed
search_users John Doe
pg_table_sizes

Save Confirmation:

Named query 'active_users' saved successfully (scope: session-local).
Named query 'pg_activity' saved successfully (scope: postgres).
Named query 'count_all' saved successfully (scope: global).

\nd <name> - Delete Named Query¶

Removes a named query from the current context. Automatically detects the scope of the query to delete.

\nd active_users

Output:

Named query 'active_users' deleted successfully (scope: session-local).

Scope Resolution: When deleting, DBCrust follows the same priority order as execution - it will delete the session-local query first, then database-type, then global if multiple queries exist with the same name.

Practical Usage Patterns¶

Development Workflow:

-- Create session-specific analysis queries during development
\ns debug_orders SELECT * FROM orders WHERE created_at > '2024-01-01' AND status = 'pending'

-- Create global utilities for reuse across projects
\ns table_info SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = '$1' --global

-- Create database-specific maintenance queries
\ns pg_vacuum_analyze VACUUM ANALYZE $1 --postgres

Team Collaboration:

-- Global queries shared across team
\ns daily_metrics SELECT DATE(created_at), COUNT(*), AVG(amount) FROM orders WHERE created_at >= CURRENT_DATE - INTERVAL '7 days' GROUP BY DATE(created_at) --global

-- Database-specific performance queries
\ns pg_slow_queries SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10 --postgres

Multi-Database Projects:

-- PostgreSQL analytics
\ns user_engagement SELECT user_id, COUNT(*) as actions FROM user_events WHERE created_at > $1 GROUP BY user_id --postgres

-- MySQL equivalent
\ns user_engagement SELECT user_id, COUNT(*) as actions FROM user_events WHERE created_at > '$1' GROUP BY user_id --mysql

-- Global fallback
\ns simple_count SELECT COUNT(*) FROM $1 --global

Autocomplete Support¶

The named query system provides intelligent autocomplete:

Query Name Completion:

\n act[TAB]          -- Shows: active_users
\ns my_qu[TAB]       -- Shows existing query names for overwriting
\nd debug[TAB]       -- Shows: debug_orders

Scope Flag Completion:

\ns myquery SELECT 1 --glo[TAB]    -- Shows: --global
\ns test SELECT 1 --post[TAB]      -- Shows: --postgres

SQL Completion:

\ns myquery SELE[TAB]              -- Shows: SELECT, SELECT *, etc.
\ns myquery SELECT * FROM use[TAB]  -- Shows: users table

Storage and Migration¶

Named queries are stored separately by scope: - Global: Available across all sessions and database types - Database-type: Available for all sessions of that database type - Session-local: Available only for the specific database session

Storage Location: ~/.config/dbcrust/named_queries.toml

Migration: Existing named queries from older versions are automatically migrated to the new scoped system as global queries during the first run.

Session Management¶

\s [name] - List or Connect to Sessions¶

Without arguments, lists all saved sessions. With a session name, connects to that session.

-- List all saved sessions
\s

Output:

Saved Sessions:
  production - PostgreSQL postgres@prod.db.com:5432/myapp
  staging - PostgreSQL postgres@staging.db.com:5432/myapp_staging
  local_mysql - MySQL root@localhost:3306/testdb
  analytics - SQLite /data/analytics.db

Use 'session://<name>' to connect via command line

-- Connect to a saved session
\s production

Output:

Connecting to saved session 'production'...
✓ Successfully connected to database

\ss <name> - Save Session¶

Saves the current connection as a named session for quick reconnection.

\ss production

Output:

Session 'production' saved successfully

\sd <name> - Delete Session¶

Removes a saved session.

\sd old_staging

Output:

Deleted session 'old_staging'

Connection History¶

\r - List Recent Connections¶

Shows your recent connection history with full URLs (excluding passwords).

\r

Output:

Recent Connections:
  [1] ✓ docker://postgres@myapp-postgres/myapp_dev - 2024-01-15 14:22 (PostgreSQL)
  [2] ✓ postgres://user@localhost:5432/testdb - 2024-01-15 14:15 (PostgreSQL)
  [3] ✗ mysql://root@badhost:3306/db - 2024-01-15 14:10 (MySQL)
  [4] ✓ sqlite:///home/user/data.db - 2024-01-15 13:55 (SQLite)

Use 'recent://' to interactively select and connect to a recent connection

\rc - Clear Recent Connections¶

Clears all connection history.

\rc

Output:

Cleared all recent connections
Configuration saved

Vault Management¶

DBCrust provides intelligent caching for HashiCorp Vault dynamic credentials to improve performance and reduce Vault API calls.

\vc - Show Vault Credential Cache Status¶

Displays all cached Vault credentials with their expiration status and remaining TTL.

\vc

Output:

Vault credential cache status (showing 2 entries):
  database/myapp-prod/readonly (v-user-prod--ABC123-1234567890) - 0h58m remaining - VALID
  database/myapp-dev/admin (v-user-dev--XYZ789-9876543210) - 0h02m remaining - EXPIRES SOON

Status indicators: - VALID: Credentials have sufficient TTL remaining - EXPIRES SOON: Credentials below renewal threshold (default: 25% of original TTL) - EXPIRED: Credentials past expiration time (automatically cleaned up)

\vcc - Clear Vault Credential Cache¶

Removes all cached vault credentials, forcing fresh authentication on next vault:// connection.

\vcc

Output:

Cleared all cached vault credentials (2 entries removed)

\vcr [role] - Force Refresh Vault Credentials¶

Forces refresh of Vault credentials, either for all cached entries or a specific role.

-- Refresh all cached credentials
\vcr

-- Refresh specific role
\vcr readonly

Output:

Refreshed vault credentials for role 'readonly'
New credentials valid for 1h0m

Use cases: - Force credential renewal before long-running operations - Refresh credentials that are near expiration - Update credentials after Vault policy changes

\vce - Show Expired Vault Credentials¶

Lists vault credentials that have expired but haven't been cleaned up yet.

\vce

Output:

Expired vault credentials (1 entry):
  database/myapp-staging/readonly - expired 0h15m ago

Vault Credential Caching Behavior¶

Automatic Caching: - Credentials are automatically cached when using vault:// URLs - Cache persists between DBCrust sessions - Stored in encrypted file: ~/.config/dbcrust/vault_credentials.enc

Cache Validation: - Credentials are checked for expiration before use - TTL threshold prevents using credentials that expire soon (default: 5 minutes minimum) - Automatic cleanup removes expired credentials

Security Features: - All cached credentials are encrypted using AES-256-GCM - Encryption key derived from your Vault token - Cache automatically invalidated if Vault token changes

Configuration:

# ~/.config/dbcrust/config.toml
vault_credential_cache_enabled = true          # Enable/disable caching
vault_cache_renewal_threshold = 0.25           # Renew when 25% TTL remaining
vault_cache_min_ttl_seconds = 300              # Minimum 5 minutes required

Example Workflow¶

-- First connection: fetches and caches credentials
dbcrust vault://readonly@database/myapp-prod

-- Check cache status
\vc
-- Output: database/myapp-prod/readonly (v-user--ABC123-1234567890) - 0h59m remaining - VALID

-- Reconnect quickly using cached credentials
dbcrust vault://readonly@database/myapp-prod
-- Uses cached credentials, no Vault API call

-- Force refresh if needed
\vcr readonly

-- Clear cache when done
\vcc

MongoDB Operations¶

DBCrust provides comprehensive MongoDB support with both native MongoDB commands and familiar SQL-like syntax for database and collection management.

SQL Database Management Commands¶

MongoDB database and collection operations can be performed using familiar SQL syntax:

Database Operations:

-- Create a new database
CREATE DATABASE analytics;

-- Drop an existing database
DROP DATABASE old_analytics;

Collection Operations:

-- Create a new collection
CREATE COLLECTION user_profiles;

-- Drop an existing collection
DROP COLLECTION temp_data;

\collections - List Collections¶

Lists all collections in the current MongoDB database.

\collections

Output:

╭─────────────────┬──────────────╮
│ Collection      │ Type         │
├─────────────────┼──────────────┤
│ users           │ collection   │
│ orders          │ collection   │
│ products        │ collection   │
│ sessions        │ collection   │
╰─────────────────┴──────────────╯

\dc <collection> - Describe Collection¶

Shows the structure and sample documents from a MongoDB collection.

\dc users

Output:

Collection: users
Sample Documents: 3

Field Structure:
╭─────────────────┬──────────────┬──────────────╮
│ Field           │ Type         │ Sample Value │
├─────────────────┼──────────────┼──────────────┤
│ _id             │ ObjectId     │ 507f1f77... │
│ email           │ String       │ user@email.com │
│ name            │ String       │ John Doe     │
│ active          │ Boolean      │ true         │
│ created_at      │ DateTime     │ 2024-01-15   │
╰─────────────────┴──────────────┴──────────────╯

MongoDB-Specific Commands¶

\dmi - List MongoDB Indexes:

\dmi

Shows all indexes across collections in the current database.

\cmi <collection> <field> [type] - Create Index:

-- Create standard index
\cmi users email

-- Create text index for search
\cmi articles content text

\search <collection> <term> - Text Search:

-- Search for documents containing specific terms
\search articles "mongodb tutorial"

-- Search with multiple terms
\search products "wireless bluetooth"

Advanced MongoDB Queries¶

DBCrust supports advanced SQL-to-MongoDB translation with sophisticated filtering:

LIKE Pattern Matching:

-- SQL LIKE translates to MongoDB $regex
SELECT * FROM users WHERE name LIKE 'John%';
-- → MongoDB: {"name": {"$regex": "John.*", "$options": "i"}}

IN Operator:

-- SQL IN translates to MongoDB $in
SELECT * FROM orders WHERE status IN ('pending', 'processing');
-- → MongoDB: {"status": {"$in": ["pending", "processing"]}}

OR Conditions:

-- SQL OR translates to MongoDB $or
SELECT * FROM users WHERE age > 18 OR verified = true;
-- → MongoDB: {"$or": [{"age": {"$gt": 18}}, {"verified": true}]}

BETWEEN Ranges:

-- SQL BETWEEN translates to MongoDB $gte + $lte
SELECT * FROM products WHERE price BETWEEN 10 AND 100;
-- → MongoDB: {"price": {"$gte": 10, "$lte": 100}}

NULL Handling:

-- SQL IS NULL/IS NOT NULL translates to MongoDB $exists
SELECT * FROM users WHERE email IS NOT NULL;
-- → MongoDB: {"email": {"$exists": true, "$ne": null}}

MongoDB Workflow Example¶

-- Connect to MongoDB
dbcrust mongodb://localhost:27017/myapp

-- List collections
\collections

-- Examine a collection structure
\dc users

-- Query with advanced filtering
SELECT * FROM users
WHERE name LIKE 'John%'
  AND age BETWEEN 18 AND 65
  AND status IN ('active', 'verified');

-- Create collection with SQL syntax
CREATE COLLECTION user_sessions;

-- Drop collection when no longer needed
DROP COLLECTION temp_analytics;

-- Create index for better performance
\cmi users email

-- Perform text search
\search users "john developer"

💡 Advanced Usage Patterns¶

Combining Commands¶

-- Switch database and list tables
\c analytics
\dt

-- Enable EXPLAIN and run query
\e on
SELECT COUNT(*) FROM large_table;

-- Save result and write to file
\w large_table_count.sql

Scripting Workflows¶

-- Create a setup script
\ed

-- In editor, write:
-- \c development
-- \i create_tables.sql
-- \i seed_data.sql
-- \dt

-- Save and execute automatically

Query Development¶

-- Start with simple query
SELECT * FROM users LIMIT 5;

-- Refine in editor
\ed

-- Save final version
\w final_user_report.sql

-- Create named query for reuse
\ns user_report SELECT u.*, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id

🚀 Pro Tips¶

\d us[TAB]  -- Completes to table names starting with 'us'
\ns my[TAB] -- Completes to existing named query names

\q = \quit
\? = \h = \help

\i /home/user/scripts/setup.sql     -- Absolute
\i ../migrations/001_create.sql     -- Relative
\w ~/backups/current_query.sql      -- Home directory

-- Temporarily adjust threshold for current session
\csthreshold 5          -- Lower threshold for detailed analysis

-- Enable manual mode for exploration
\cs                     -- Now all queries show column selection

-- Clear and reset when done
\clrcs                  -- Clear saved selections
\cs                     -- Disable manual mode
\csthreshold 10         -- Reset to default threshold

\i nonexistent.sql
-- Error: File 'nonexistent.sql' not found
-- Did you mean: setup.sql, test.sql?