🔍 pgpeek

┌─────────────────────────────────────────────────────────────────┐
│                                                                 │
│   🔍  P G P E E K                                               │
│   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━    │
│                                                                 │
│   📊 Connect  •  🔍 Browse  •  ⚡ Query                         │
│                                                                 │
│   Database exploration that doesn't suck                       │
│   No complex setup, no bullshit - just peek into your data    │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

peek into postgres without the hassle

Why pgpeek?

• Zero ceremony - One command to peek into any database
• Beautiful TUI - Navigate schemas, tables, and data with ease
• Natural queries - Type what you mean, get what you want
• No setup hell - Add connections with simple URLs
• Fast exploration - Instantly browse schemas, tables, and sample data

Install

cargo install pgpeek

Quick Start

# Initialize pgpeek
pgpeek init

# Add your database
pgpeek add local postgres://user:pass@localhost:5432/mydb --default

# Start exploring
pgpeek explore

✨ Visual Demo

When you run pgpeek without arguments, you'll see:

🔍 pgpeek
   ┌─────────────────────────────────────────┐
   │  Database exploration that doesn't suck  │
   └─────────────────────────────────────────┘

   📊 Connect  •  🔍 Browse  •  ⚡ Query

   Quick start:
   • pgpeek init              → setup config
   • pgpeek add <name> <url>  → add database
   • pgpeek explore           → start explorer

When starting the explorer:

┌─ 🔍 pgpeek explorer ─┐
│                      │
│  🚀 Connecting to:   │
│  mydb                │
│                      │
│  ⏳ Loading schemas   │
└──────────────────────┘

   ⠋ Connecting to database...
   ✓ Connected successfully!

Inside the TUI, you'll see a clean interface:

┌─────────────────────── 🔍 pgpeek ────────────────────────┐
│ 📊 local → postgres://user@localhost:5432/mydb          │
└──────────────────────────────────────────────────────────┘
┌── 📈 Database Overview ────────────────────────────────┐
│ Database: mydb                                         │
│ Size: 125 MB                                           │
│ Tables: 47                                             │
│ Schemas: 3                                             │
│ User: postgres                                         │
└────────────────────────────────────────────────────────┘
┌─── 📂 Schemas ─────────────────────────────────────────┐
│ ▶ 📁 public (34 tables)                              │
│   📁 auth (8 tables)                                  │
│   📁 analytics (5 tables)                             │
└────────────────────────────────────────────────────────┘
┌────────────────────────────────────────────────────────┐
│ Status: Database overview loaded - 3 schemas          │
│ Help: 1=Overview 2=Schemas 3=Tables 4=Query q=Quit    │
└────────────────────────────────────────────────────────┘

Usage

Connection Management

# Add connections
pgpeek add prod postgres://user:pass@prod-db.com:5432/app
pgpeek add staging postgres://user:pass@staging.com:5432/app_staging

# List all connections
pgpeek list

# Test a connection
pgpeek test prod

# Remove a connection
pgpeek remove old_db

Database Exploration

# Start the interactive explorer
pgpeek explore                    # uses default connection
pgpeek explore prod               # use specific connection

🎮 Inside the TUI Interface

Once the explorer opens, you can navigate with these controls:

Main Navigation (available anywhere):

• 1 - Database overview (size, table count, connection info)
• 2 - Browse schemas (list all schemas with table counts)
• 3 - Browse all tables (across all schemas or within selected schema)
• 4 - Open SQL query editor
• 5 - Saved queries (manage your stored queries)
• r - Refresh current view/data
• q - Quit pgpeek
• Ctrl+C - Force quit

List Navigation:

• ↑/↓ Arrow keys - Navigate up/down in lists (schemas, tables)
• Enter - Select item and drill down (e.g., select schema → view its tables)
• Esc - Exit current mode and return to normal navigation

Query Editor Mode:

• 4 - Enter query editor mode
• In editing mode:
  • Esc - Exit editing mode (keeps your query text)
  • Enter - Execute the current query
  • ↑/↓ Arrow keys - Navigate through query history
  • Backspace - Delete characters
  • Type normally to write SQL queries

Sample Data & Results:

• Automatic scrolling - Use arrow keys to navigate through large result sets
• Column info - View data types, constraints, and defaults in table details

Quick Tips:

• Press any number key (1-5) from anywhere to jump directly to that section
• Use Esc to get out of any editing mode
• All data refreshes automatically, or press r to force refresh

Features

🏗️ Smart Connection Management

• URL-based connection strings
• Default connection support
• Connection testing and validation

🔍 Database Exploration

• Overview - Database size, table count, connection info
• Schema Browser - List all schemas with table counts
• Table Browser - Tables with row counts and sizes
• Column Inspector - Data types, constraints, defaults

⚡ Query Interface

• SQL Editor - Full SQL support with syntax awareness
• Query History - Navigate previous queries with arrow keys
• Result Display - Paginated results with column info
• Execution Stats - Query time and row counts

💾 Query Management

• Saved Queries - Store frequently used queries
• Query History - Automatic history tracking
• Quick Commands - Natural language shortcuts

🎨 Beautiful Interface

• Clean TUI - Built with ratatui for smooth navigation
• Status Messages - Clear feedback on all operations
• Error Handling - Helpful error messages and recovery
• Responsive Layout - Adapts to terminal size

Configuration

pgpeek stores configuration in ~/.config/pgpeek/config.toml:

[settings]
page_size = 100
auto_refresh = false
theme = "default"
show_row_numbers = true
query_history_size = 50

[connections]
[connections.local]
name = "Local Development"
host = "localhost"
port = 5432
database = "myapp_dev"
username = "postgres"
password = "secret"

[connections.prod]
name = "Production"
host = "prod-db.example.com"
port = 5432
database = "myapp"
username = "app_user"
ssl_mode = "require"

[saved_queries]
[saved_queries.active_users]
name = "Active Users"
description = "Users active in last 30 days"
sql = "SELECT COUNT(*) FROM users WHERE last_login > NOW() - INTERVAL '30 days'"
created_at = "2024-01-15T10:30:00Z"
connection = "prod"

Query Examples

Natural Language Support

-- Simple table exploration
SELECT * FROM users LIMIT 10;

-- Filter and search
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC;

-- Aggregations
SELECT country, COUNT(*) as user_count 
FROM users 
GROUP BY country 
ORDER BY user_count DESC;

Quick Commands

• SHOW TABLES - List all tables
• DESCRIBE users - Show table structure
• EXPLAIN SELECT * FROM orders - Query execution plan

Tips & Tricks

Keyboard Shortcuts

• Ctrl+C - Quick exit from anywhere
• Arrow keys - Navigate query history in editor
• Tab - Future autocomplete support
• Escape - Always gets you back to normal mode

Connection URLs

# Basic connection
postgres://username:password@hostname:port/database

# With SSL
postgres://user:pass@host:5432/db?sslmode=require

# Local development
postgres://postgres@localhost/myapp

# Production with SSL
postgres://app_user:secret@prod.db.com:5432/app?sslmode=require

Performance Tips

• Use LIMIT for large tables
• Enable auto-refresh for monitoring
• Save frequently used queries
• Use the overview to understand database size

Advanced Usage

Saved Queries

Create reusable queries for common operations:

# In the query editor, save useful queries
# They'll be stored in your config and accessible via the saved queries menu

Multiple Environments

# Set up different environments
pgpeek add local postgres://postgres@localhost/myapp_dev
pgpeek add staging postgres://user:pass@staging.db.com/myapp
pgpeek add prod postgres://user:pass@prod.db.com/myapp --default

# Quick switching
pgpeek explore local
pgpeek explore staging  
pgpeek explore prod

Batch Operations

# Test all connections
for conn in $(pgpeek list --names-only); do
    echo "Testing $conn..."
    pgpeek test "$conn"
done

Troubleshooting

Connection Issues

# Test your connection first
pgpeek test mydb

# Check connection details
pgpeek show mydb

# Common issues:
# - Wrong credentials
# - SSL configuration
# - Network connectivity
# - Database permissions

Performance Issues

• Large result sets: Use LIMIT clauses
• Slow queries: Check with EXPLAIN
• Network latency: Consider connection pooling
• Memory usage: Adjust page_size in config

Configuration Problems

# Reset configuration
rm ~/.config/pgpeek/config.toml
pgpeek init

# Backup your config
cp ~/.config/pgpeek/config.toml ~/pgpeek-backup.toml

Security

• Passwords are stored in plain text in config (use environment variables in production)
• SSL connections supported via connection string parameters
• Read-only mode by default (configurable)
• Local config stored in user's config directory

Contributing

1. Fork the repository
2. Create your feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

Development

# Clone and build
git clone https://github.com/simplysabir/pgpeek.git
cd pgpeek
cargo build

# Run tests
cargo test

# Run locally
cargo run -- init
cargo run -- add local postgres://postgres@localhost/test
cargo run -- explore

🚀 Development

Get pgpeek up and running locally with Docker in just a few commands:

Prerequisites

• Docker and Docker Compose
• Git

Quick Start with Docker

1. Clone and start the development environment:

git clone https://github.com/simplysabir/pgpeek.git
cd pgpeek

# Start PostgreSQL database with sample data
docker-compose up -d postgres

# Build and run pgpeek
docker-compose build pgpeek
docker-compose run --rm pgpeek

2. Set up test database connection:

# Initialize pgpeek configuration
docker-compose run --rm pgpeek init

# Add the test database connection
docker-compose run --rm pgpeek add testdb postgres://pgpeek_user:pgpeek_pass@postgres:5432/testdb --default

# Start exploring the test database
docker-compose run --rm pgpeek explore

Development Workflow

For active development with hot reload:

# Start development environment with source code mounted
docker-compose up -d postgres
docker-compose run --rm pgpeek-dev bash

# Inside the container:
cargo build                    # Build the project
cargo run                      # Run pgpeek
cargo test                     # Run tests
cargo watch -x run            # Auto-reload on changes

Available Services

The docker-compose setup includes:

• postgres: PostgreSQL 15 with sample data

  • Database: testdb
  • User: pgpeek_user / Password: pgpeek_pass
  • Port: 5432
  • Sample schemas: users, sales, inventory

• pgpeek: Production build of pgpeek

  • Clean runtime environment
  • Persistent config storage

• pgpeek-dev: Development environment

  • Full Rust toolchain
  • Source code mounted for live editing
  • Cargo cache for faster builds

Test Database Schema

The test database includes realistic sample data:

📂 Schemas:
├── users (profiles, sessions)
├── sales (customers, orders, order_items, order_summary view)
├── inventory (categories, products, stock)
└── public (default schema)

📊 Sample Data:
• 5 user profiles with recent login activity
• 4 customers from different countries  
• 5 orders with various statuses
• 5 products across different categories
• Stock data across multiple warehouses

Docker Commands Reference

# Build images
docker-compose build

# Start just the database
docker-compose up -d postgres

# Run pgpeek commands
docker-compose run --rm pgpeek [command]
docker-compose run --rm pgpeek init
docker-compose run --rm pgpeek list
docker-compose run --rm pgpeek explore testdb

# Development environment
docker-compose run --rm pgpeek-dev bash

# Clean up
docker-compose down
docker-compose down -v  # Remove volumes too

Native Development

If you prefer running without Docker:

# Install Rust (if not already installed)
curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh

# Build and run
cargo build --release
./target/release/pgpeek init
./target/release/pgpeek add local postgres://user:pass@localhost:5432/mydb
./target/release/pgpeek explore

License

MIT - Use it, abuse it, whatever. Just keep peeking! 🔍

Built with ❤️ in Rust

Because database exploration shouldn't be painful.