PgShell

Your friendly PostgreSQL companion for the terminal

Explore, modify, and monitor PostgreSQL databases — no GUI, no fuss.

---

🚀 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