Skip to content

Latest commit

 

History

History
330 lines (231 loc) · 10.6 KB

README.md

File metadata and controls

330 lines (231 loc) · 10.6 KB

PGconnect (pgfz) — FZF-powered PostgreSQL service picker for zsh

PGconnect is a small zsh plugin/utility that lets you:

  • Browse services from your pg_service.conf with fzf
  • See live preview of the service parameters and the first N tables
  • Connect with pgcli or psql
  • Dump the database (pg_dump)
  • Run a .sql file against the selected service
  • Copy a best‑effort PostgreSQL URL or PGSERVICE=… to your clipboard

All actions are available as hotkeys inside fzf—no need to leave the picker.

Watch the show-case on youtube:

pgfz show-case

Demo (what you get)

  • Left: list of services from your pg_service.conf
  • Right (preview): selected service parameters + a query listing the first N tables
  • Keys (customizable):
  • Enter: connect with pgcli
  • Ctrl-P: connect with psql
  • Alt-D: pg_dump to a timestamped file/dir
  • Alt-E: pick a .sql file and run it with psql -f
  • Alt-C: copy a PostgreSQL URL (no password)
  • Ctrl-Y: copy PGSERVICE=… for quick exports

Note: fzf supports a single preview pane; PGconnect renders both “parameters” and “tables” stacked in that preview.

Requirements

  • zsh (tested on 5.9+)
  • fzf (tested on 0.54.2)
  • PostgreSQL client tools:
  • psql (for preview, connect, execute)
  • pg_dump (for dumps; optional)
  • pgcli (optional; used for the Enter action by default)
  • Optional helpers:
  • column (for pretty tabular preview)
  • fd (faster .sql file search; else falls back to find)
  • Clipboard: pbcopy (macOS) or wl-copy (Wayland) or xclip (X11)

Installation

To install the PostgreSQL Connection Plugin, follow these steps:

  1. Clone the repository into your custom Oh My Zsh plugins directory:

    git clone git@github.com:ruslan-korneev/pgconnect-zsh.git ${ZSH_CUSTOM:-~/.oh-my-zsh/custom}/plugins/pgconnect

  2. Add pgconnect to the list of plugins in your .zshrc file:

    plugins=(... pgconnect)

  3. Reload your Zsh configuration:

    source ~/.zshrc

Quick start

  1. Ensure you have a pg_service.conf (default: ~/.pg_service.conf). Example:
[dev]
host=localhost
port=5432
user=devuser
dbname=devdb

[prod]
host=prod-db.example.com
port=5432
user=app
dbname=appdb
sslmode=require

For more detailed information on configuring these files, refer to the PostgreSQL documentation on connection service files and password file.

  1. Optional: set up ~/.pgpass for passwordless connections in preview/exec:
hostname:port:database:username:password
  1. Launch pgfz (pgconnect command is obsolete, but you can still run it):
  • If sourced as a plugin: run pgfz
  • If executable: run ./pgcli-connect-db.zsh

Pick a service, then press a hotkey for the desired action.

Key bindings (inside fzf)

Key Action
Enter Connect with pgcli
Alt-P Connect with psql
Alt-D Dump with pg_dump
Alt-E Pick a .sql file and run it
Alt-C Copy a PostgreSQL URL
Ctrl-Y Copy PGSERVICE=…

Notes:

  • Alt may be labeled Meta; in many terminals it’s the Option key on macOS.
  • You can still press Enter with no modifier to select + connect (pgcli by default).

Environment variables

All are optional; sensible defaults are provided.

Variable Description Default
PGFZ_SERVICE_CONF Path to pg_service.conf $HOME/.pg_service.conf
PGFZ_PGCLI_CMD pgcli-compatible client for Enter action pgcli
PGFZ_PGCLI_ARGS Extra args passed to pgcli after service=… (empty)
PGFZ_PSQL_CMD psql binary psql
PGFZ_PGDUMP_CMD pg_dump binary pg_dump
PGFZ_FZF_PROMPT fzf prompt text Select a database service:
PGFZ_FZF_COMMON_OPTS Baseline fzf options (prepended; can be overridden later) (empty)
PGFZ_FZF_OPTS Additional fzf options (appended) (empty)
PGFZ_PREVIEW_TABLE_LIMIT How many tables to list in preview 50
PGFZ_PREVIEW_TIMEOUT PGCONNECT_TIMEOUT in seconds for preview query 2
PGFZ_DUMP_DIR Where dumps are written .
PGFZ_DUMP_FORMAT pg_dump format: c (custom), p (plain), d (directory), t (tar) c
PGFZ_CLIP_CMD Clipboard command override (e.g., pbcopy, "xclip -selection clipboard") auto-detect

Tips:

  • To speed up previews on slow links, lower PGFZ_PREVIEW_TIMEOUT and PGFZ_PREVIEW_TABLE_LIMIT.
  • To change UI layout/size, use PGFZ_FZF_OPTS to adjust the preview window (examples below).

Usage examples

  • Basic

  • pgfz

  • Select service → Enter to open pgcli

  • Connect with psql

  • Select service → press Ctrl-P

  • Dump a database

  • export PGFZ_DUMP_DIR=~/backups

  • export PGFZ_DUMP_FORMAT=t

  • pgfz → pick service → Alt-D

  • Result file: backups/dump--YYYYMMDD-HHMMSS.tar

  • Execute a .sql file

  • pgfz → pick service → Alt-E → choose file via fzf

  • Uses psql -v ON_ERROR_STOP=1 -f file

  • Copy connection info

  • Alt-C: copies a best‑effort URL like postgresql://user@host:port/dbname

  • Ctrl-Y: copies PGSERVICE= (easy to paste into env or shell)

Customization

  • Change preview window position/size

For a bottom preview at 40% height:

export PGFZ_FZF_OPTS="--preview-window=down,40%,border-top"
  • Show more tables in preview
export PGFZ_PREVIEW_TABLE_LIMIT=200
  • Make previews snappier
export PGFZ_PREVIEW_TABLE_LIMIT=20
export PGFZ_PREVIEW_TIMEOUT=1
  • Change default client (Enter action) to psql
export PGFZ_PGCLI_CMD=psql
export PGFZ_PGCLI_ARGS=""
  • Tweak color theme

If you already use FZF_DEFAULT_OPTS, leave it as is. Add a small baseline with PGFZ_FZF_COMMON_OPTS and fine-tune with PGFZ_FZF_OPTS:

export PGFZ_FZF_COMMON_OPTS="--ansi --color=16"
export PGFZ_FZF_OPTS="--border=rounded --layout=reverse"
  • Clipboard override
# macOS
export PGFZ_CLIP_CMD=pbcopy

# Wayland
export PGFZ_CLIP_CMD=wl-copy

# X11
export PGFZ_CLIP_CMD="xclip -selection clipboard"

How it works

  • Services are parsed from pg_service.conf and listed in fzf.
  • Preview pane shows:
  • “Parameters” parsed from the same service entry
  • “Tables”: a read-only psql query listing the first N user tables
  • Preview is non-blocking:
  • Uses PGCONNECT_TIMEOUT and psql -w to avoid password prompts
  • If a password is required and not available via .pgpass, preview prints a friendly hint
  • Actions are chosen via fzf’s --expect keys; you press a shortcut to run the action immediately.

Troubleshooting

  • “No services found in ~/.pg_service.conf”

  • Ensure the file exists and has sections like [myservice]

  • Comments start with # or ;, keys are key=value

  • Preview says “cannot connect or password needed”

  • Add credentials to ~/.pgpass

  • Or set PGFZ_PREVIEW_TIMEOUT to a larger value for slow networks

  • You can still connect with Enter/Ctrl-P to be prompted interactively

  • psql not found

  • Install PostgreSQL client tools; preview and some actions need psql

  • column not found

  • Preview will still work; it just won’t be pretty-aligned

  • Key doesn’t seem to trigger inside fzf

  • Ensure your terminal sends Alt/Meta sequences (common in macOS/iTerm2/Kitty/Alacritty)

  • Inside fzf, the bindings listed in the header should work as-is

Security notes

  • pgfz never reads or stores passwords itself.
  • Preview runs with psql -w, so it won’t prompt. If .pgpass contains credentials, preview will connect and list tables.
  • The copied URL (Alt-C) does not include a password; it’s a best‑effort URL built from service fields.
  • If you set PGFZ_CLIP_CMD, pgfz will pipe to it. This is trusted user input; avoid setting it to unsafe shell fragments.

FAQ

  • Can I have two separate preview panes?

  • fzf shows only a single preview pane. pgfz renders both “Parameters” and “Tables” stacked in that single pane.

  • Can I multi-select services?

  • Not yet. The current script acts on a single selection. If you need batch operations (e.g., dump all selected), open an issue.

  • Will it work without pgcli?

  • Yes. Enter uses pgcli by default, but you can set PGFZ_PGCLI_CMD=psql or just use Ctrl-P.

  • Does it support tmux popups?

  • Not built-in, but easy to wrap pgfz in tmux display-popup if you prefer.

Contributing

  • Issues and PRs are welcome: bug reports, feature ideas, docs, portability fixes.
  • If you propose keybinds, note potential conflicts and terminal behaviors.
  • Please keep quoting robust and preview portable (avoid shell-specific quirks in the preview body).

License

  • Apache License — © Ruslan Korneev

Appendix: one-liners and power tips

  • Run with a different service file for a one-off:
PGFZ_SERVICE_CONF=./pg_service.conf pgfz
  • Favor small/fast previews over completeness:
PGFZ_PREVIEW_TABLE_LIMIT=10 PGFZ_PREVIEW_TIMEOUT=1 pgfz
  • Change the header text and preview layout:
export PGFZ_FZF_OPTS="--header='enter: pgcli | C-p: psql | M-d: dump | M-e: run .sql | M-c: copy URL | C-y: copy env' --preview-window=down,50%,border-top"

That’s it—enjoy fast, discoverable PostgreSQL connections from your shell!