Basic Usage¶

-- Toggle server info on/off
\serverinfo

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

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

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

-- 🎯 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 [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)

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

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

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

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

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

-- 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)

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

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

-- 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.

\x  -- Toggle expanded display

SELECT * FROM users WHERE id = 1;

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

\e  -- Toggle EXPLAIN mode

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

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

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

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

-- List recent connections with full URLs
\r

-- Clear connection history
\rc

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

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

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

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

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

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

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

-- 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 '%$*%';

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

-- Show current configuration
\config

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

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