pgsanity

library0.3.0pypypiunverified

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.

database testing

pip install pgsanity

github ↗package ↗

INSTALL

IMPORT

SIG · PGSANITY

pgsanity

databasepythonv0.3.0

harness data pending

Install & Compatibility

Where this runs

No compatibility data collected yet for this library.

Code

Verified usage

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}")

pgsanity --version

Debug

Known issues

gotchaPgSanity 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.

affects: All versions

gotchaPgSanity 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.

affects: All versions

Errors

Common errors & fixes

Unable to execute 'ecpg', you likely need to install it

PgSanity relies on the `ecpg` command-line tool, which is part of the PostgreSQL client development tools. This error occurs if `ecpg` is not installed on the system or is not found in the system's PATH.

fix

Install the PostgreSQL client development tools for your operating system. For Debian/Ubuntu, use `sudo apt-get install libecpg-dev`. For RHEL/CentOS, use `sudo yum install postgresql-devel` or `dnf install postgresql12-devel`. After installation, ensure `ecpg` is accessible in your system's PATH.

ModuleNotFoundError: No module named 'pgsanity'

This error occurs when trying to `import pgsanity` directly into a Python script. PgSanity is primarily designed as a command-line utility, not a library for direct programmatic import and function calls within Python scripts.

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 package. Alternatively, run `pgsanity` directly from the shell.

line X: ERROR: syntax error at or near "..."

This indicates a PostgreSQL SQL syntax error detected by the underlying `ecpg` parser. The error message reports the line number and the token where the syntax violation was identified.

fix

Carefully review the SQL syntax in your file around the reported line number and the token in question. Be aware that `ecpg`'s error reporting can sometimes point to a location slightly after the actual fault (e.g., a missing semicolon in a preceding statement might cause an error on the next statement).

Upgrade

Version history

0.3.0latest on PyPI · released Sep 20, 2025

Audit

Dependencies

PostgreSQL client development tools (providing 'ecpg' command)requiredPgSanity is a wrapper around the `ecpg` command, which is not a Python package dependency but a system-level dependency. It must be installed for pgsanity to function.

Agent activity

7 hits · last 30 days

node

Resources

githubhttps://github.com/markdrago/pgsanity ↗

packagehttps://pypi.org/project/pgsanity/ ↗