🚀 Quick Start (30 seconds)

New here? Follow these three steps:

1. Install

   npm install -g pgshell

2. Configure — Create a .env file in your project folder (or where you'll run pgshell):

   DB_HOST=localhost
   DB_PORT=5432
   DB_USER=postgres
   DB_PASSWORD=your_password
   DB_NAME=your_database

   Or use a single URL: DATABASE_URL="postgresql://user:password@localhost:5432/dbname"

3. Run

   pgshell

That's it! PgShell will connect and show you an interactive menu. If you skip the .env, PgShell will ask for connection details the first time — and optionally save your password in your OS keychain (Windows Credential Manager, macOS Keychain, or Linux Secret Service) so you don't have to type it again.

✨ What is PgShell?

PgShell is a terminal-based tool that gives you full control over PostgreSQL. Use the interactive menu for guided tasks, or run direct commands for quick one-liners — no more opening a separate GUI.

| | | |---|---| | 🖥️ | Interactive UI — Guided menus for browsing, creating, and managing | | ⚡ | CLI commands — pgshell query "SELECT * FROM users" | | 🔐 | Flexible setup — .env, URI, or interactive prompts; password stored securely in your OS keychain | | 📊 | Formatted output — Clean tables with syntax highlighting |

📋 Table of Contents

• Quick Start
• Requirements
• Installation
• Configuration
• Usage
• Commands Reference
• Interactive UI
• Examples
• Project Structure
• Troubleshooting
• License

🛠 Requirements

• Node.js 18 or newer
• PostgreSQL server (local or remote)
• Your database credentials

📦 Installation

Global install (run from anywhere):

npm install -g pgshell

Then try:

pgshell
# or
pgshell query "SELECT 1"

From source (for development or contributions):

git clone https://github.com/Foisalislambd/pgshell
cd pgshell
npm install
npm run build

Run it:

node dist/index.js
# or with hot reload during development
npm run dev

Use locally without publishing:

npm link
pgshell

⚙️ Configuration

PgShell reads credentials from a .env file in the directory where you run it. Put your project or working folder in mind when creating it.

Option 1 — Individual variables

DB_HOST=localhost
DB_PORT=5432
DB_USER=postgres
DB_PASSWORD=your_password
DB_NAME=your_database

Option 2 — Connection URL

DATABASE_URL="postgresql://user:password@localhost:5432/dbname"

DATABASE_URL overrides individual variables if both exist.

Option 3 — Standard PostgreSQL vars

PGUSER=postgres
PGPASSWORD=yourpassword
PGHOST=localhost
PGPORT=5432
PGDATABASE=yourdatabase

Password storage (keychain)

When you connect interactively (without .env), PgShell can save your password in your OS keychain so you don't have to re-enter it. It uses Windows Credential Manager, macOS Keychain, or Linux Secret Service. Connection profiles (host, port, user) are stored in ~/.pgshell/config.json — passwords are never saved in plain text.

Cloud & SSL

SSL is enabled automatically when your connection string contains sslmode=require, amazonaws.com, or supabase.com.

🚀 Usage

1. Install: npm install -g pgshell
2. (Optional) Create a .env in your project directory — see Configuration
3. Run pgshell — if .env exists, it connects automatically; otherwise it prompts for connection details

📂 Commands Reference

All commands support .env credentials. If no .env is present, PgShell will prompt you when needed.

| Command | Description | |---------|-------------| | pgshell or pgshell ui or pgshell view | Launch the interactive menu | | pgshell query "<sql>" | Run a raw SQL query | | pgshell list | List all databases with sizes | | pgshell create <name> | Create a new database | | pgshell drop <name> | Drop a database (--yes to skip confirmation) | | pgshell table [dbName] | List all tables — specify dbName or use .env / select interactively | | pgshell delete [dbName] | Drop all tables in a database — with confirmation |

Examples:

# Query
pgshell query "SELECT * FROM users LIMIT 5"
pgshell query "SELECT COUNT(*) FROM orders"

# Databases
pgshell list
pgshell create my_app_db
pgshell drop old_db --yes

# Tables
pgshell table                    # Use .env or pick database
pgshell table my_database        # Direct database name

# Delete all tables (prompts for confirmation)
pgshell delete my_database

Results appear as formatted tables. On errors, PgShell exits with code 1.

📱 Interactive UI

Run pgshell or pgshell ui to open the interactive menu.

| Menu Option | What it does | |-------------|--------------| | 📂 List all databases | See all databases with sizes | | ➕ Create database | Create a new database | | 🗑️ Delete database | Drop a database (with confirmation) | | 🔄 Switch database | Reconnect to a different database | | 📋 List all tables | Tables in public schema with owner and row estimates | | 🔍 View table data | Browse rows with configurable limit | | 📖 Table structure | Columns, types, nullability, defaults | | ➕ Create new table | Define tables with column syntax | | 📥 Add new row | Insert with guided prompts per column | | 🗑️ Delete one table | Drop a single table (with confirmation) | | 🚨 Delete all tables | Drop all public tables (extra confirmation) | | ⚡ Run custom SQL | Execute any SQL command | | 📊 Monitor active queries | Live view of running queries | | ❌ Disconnect & Exit | Close connection and quit |

When you don't have a .env

1. Localhost — Enter host, port, user, password, database
2. External / URI — Paste the full postgresql:// connection string

Tips

• Ctrl+C — Safe exit
• Blank insert fields → use DEFAULT or NULL
• Table and database names: letters, numbers, underscores only
• Dangerous SQL is blocked in table creation
• Dropping the database you're connected to? PgShell reconnects to postgres automatically

📝 Examples

List tables from CLI

pgshell table
# or for a specific database
pgshell table my_database

Quick SQL

pgshell query "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'"

Create a table (interactive)

pgshell
# → Create new table
# Name: users
# Columns: id SERIAL PRIMARY KEY, email VARCHAR(255), created_at TIMESTAMP DEFAULT NOW()

Insert a row (interactive)

pgshell
# → Add new row → pick table → enter values

📂 Project Structure

pgshell/
├── src/
│   ├── index.ts                 # CLI entry, Commander
│   ├── commands/
│   │   ├── query.ts             # Direct query command
│   │   ├── database.ts         # list/create/drop DB commands
│   │   ├── table.ts            # List tables command
│   │   └── delete.ts           # Drop all tables command
│   ├── db/
│   │   ├── client.ts           # Connection pool, pg wrapper
│   │   ├── connectionResolver.ts # .env + keychain + prompt resolution
│   │   ├── credentials.ts     # Keychain + ~/.pgshell/config
│   │   ├── cliCredentials.ts  # Interactive credential prompts
│   │   └── env.ts             # .env hint printing
│   ├── ui/
│   │   ├── mainMenu.ts         # Interactive menu
│   │   ├── tableRenderer.ts   # cli-table3 output
│   │   └── fuzzySelect.ts     # Fuzzy search selection
│   └── utils/
│       ├── banner.ts           # ASCII banner
│       ├── sanitizeError.ts   # Error sanitization
│       ├── spinner.ts         # ora spinner wrapper
│       └── sqlHighlight.ts    # SQL syntax highlighting
├── .env.example
├── package.json
└── README.md

NPM Scripts

| Script | Description | |--------|-------------| | npm run dev | Run with hot reload | | npm run build | Build to dist/ | | npm start | Run built output | | npm run typecheck | TypeScript check without emit |

🔧 Troubleshooting

"Missing database credentials"
Create a .env in the folder where you run pgshell, or run from a terminal so PgShell can prompt you. For non-interactive use (scripts, CI), you must provide credentials via .env or DATABASE_URL.

Connection refused
Ensure PostgreSQL is running and the host/port in your config are correct. For remote servers, check firewall and SSH/network access.

SSL errors
For cloud providers (e.g. AWS RDS, Supabase), SSL is usually required. Use a URL with sslmode=require or the provider's recommended params.

📄 License

ISC