psql-inspired PostgreSQL client

This tool was initially created by BES as a series of augments on top of the native psql client to increase our safety when interacting with PostgreSQL databases, especially in sensitive environments. It has since been rewritten entirely as a standalone tool, which maintains a similar syntax as native psql, but with a different feature set and in some cases a different syntax and behaviour.

The primary features are

• read/write modes, where a bestool-psql session will by default start in "read-only mode", which prohibits write operations;
• audit logging, which logs all queries and user information to a local database for auditing purposes (as well as providing cross-platform history);
• a simpler syntax and command set for variables;
• snippet management.

Install

Available on crates.io:

cargo install bestool-psql

There are no binary builds available at this time.

The crate also exposes a library interface which embeds the tool in another CLI application.

CLI Arguments

Interactive Commands

All commands and some of the SQL has extensive tab completion, give it a try!

Metacommands

Database Exploration

There's also two modifiers you can add:

• + toggles "detailed" mode, which shows more information
• ! toggles "same connection" mode; by default these commands are executed in a separate connection so they can be used to explore the database without affecting the current session. In some cases, you might want to execute them in the same connection as the session, so that you can see changes you've made to the database before committing them.

Look in EXAMPLES.md for more.

Query Modifiers

Query modifiers are used after a query to modify its execution behaviour.

Modifiers can be combined, e.g. \gxj for expanded JSON output.

Variable Interpolation

Variables can be used within SQL queries to dynamically substitute values. Note this is a different syntax from native psql.

Formats

Usage

Basic Connection

# Connect to local database
bestool-psql mydb

# Connect with full connection string
bestool-psql "postgresql://user:password@localhost:5432/mydb"

Write Mode

# Enable write mode (remember to COMMIT!)
bestool-psql -W mydb

Using Variables

-- Set a variable
\set table_name users

-- Set a variable with a default value (only if not already set)
\default schema_name public

-- Use in query
SELECT * FROM ${table_name};

-- Get a variable value
\get table_name

-- Escape variable syntax
SELECT '${{table_name}}' as literal_text;

-- Unset a variable
\unset table_name

-- List all variables
\vars

Snippets

-- Save current query as a snippet
\snip save my_query

-- Run the saved snippet later
\snip run my_query

-- Run snippet with variable override
\snip run my_query table_name=products

Output Modes

-- Expanded output
SELECT * FROM users \gx;

-- JSON output
SELECT * FROM users \gj;

-- Pretty-printed JSON
SELECT * FROM users \gxj;

-- Write to file
SELECT * FROM users \go /tmp/results.txt;

Executing from Files

-- Execute commands from a file
\i /path/to/script.sql

-- With variables (that only apply to this file execution)
\i /path/to/script.sql var1=value1 var2=value2

Output everything to a file

\o /tmp/output.txt

-- This will print to the file instead of the screen
SELECT * FROM users;

-- This will print in json format to the file
SELECT * FROM users \gj;

-- Toggle off (now things will print to the screen again)
\o

Make sure to check out the EXAMPLES.md for more.