PgSanity

0.3.0 · active · verified Mon Apr 13

PgSanity is a Python utility that checks the syntax of PostgreSQL SQL files. It operates by leveraging the `ecpg` command-line tool, which is part of the PostgreSQL client development tools. This allows `pgsanity` to use the exact same parser as PostgreSQL to identify SQL syntax errors, making it a reliable tool for quality assurance and testing of SQL scripts. The current version is 0.3.0, and it maintains a regular release cadence.

Warnings

gotcha PgSanity is a wrapper around the `ecpg` command, which is part of the PostgreSQL client development tools. These tools (specifically `ecpg`) must be installed on the system and accessible in the system's PATH for `pgsanity` to function correctly. This is an external system dependency, not a Python package dependency.
Fix: Install the PostgreSQL client development tools for your operating system (e.g., `postgresql-client-dev` on Debian/Ubuntu, `postgresql-devel` on RHEL/CentOS, or the full PostgreSQL installer on Windows/macOS) and ensure `ecpg` is in your system's PATH.
gotcha PgSanity is designed as a command-line utility rather than a Python library intended for direct programmatic import and manipulation within Python scripts. While it's a Python package, its primary interface is through executing the `pgsanity` command, typically via `subprocess` calls in Python or directly from the shell.
Fix: Interact with `pgsanity` by executing it as a separate process (e.g., using Python's `subprocess` module) rather than attempting to import and call functions directly from the `pgsanity` package.

Install

pip install pgsanity Install with pip

Quickstart

PgSanity is primarily a command-line tool. The quickstart demonstrates how to install it and then use the `pgsanity` command to check SQL syntax from a file or directly from standard input. A successful check returns an exit code of 0, while errors result in a non-zero exit code and error messages on stderr.

# Check a single SQL file
pip install pgsanity
# Create a dummy SQL file
with open('test.sql', 'w') as f:
    f.write('SELECT 1 FROM my_table;\n')
    f.write('INSERT INTO another_table (id) VALUES (10);\n')

import subprocess

# Run pgsanity on the file
result = subprocess.run(['pgsanity', 'test.sql'], capture_output=True, text=True)
print(f"Exit Code: {result.returncode}")
print(f"STDOUT:\n{result.stdout}")
print(f"STDERR:\n{result.stderr}")

# Example of invalid SQL
with open('invalid.sql', 'w') as f:
    f.write('SELECT * FROM non_existent_table WHERE bad_syntax;\n')

result_invalid = subprocess.run(['pgsanity', 'invalid.sql'], capture_output=True, text=True)
print(f"\nExit Code (invalid): {result_invalid.returncode}")
print(f"STDOUT (invalid):\n{result_invalid.stdout}")
print(f"STDERR (invalid):\n{result_invalid.stderr}")

# Check SQL from stdin
sql_to_check = 'SELECT current_timestamp;'
result_stdin = subprocess.run(['pgsanity'], input=sql_to_check, capture_output=True, text=True)
print(f"\nExit Code (stdin): {result_stdin.returncode}")
print(f"STDOUT (stdin):\n{result_stdin.stdout}")
print(f"STDERR (stdin):\n{result_stdin.stderr}")

view raw JSON →