{"id":5365,"library":"pgsanity","title":"PgSanity","description":"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.","status":"active","version":"0.3.0","language":"en","source_language":"en","source_url":"https://github.com/markdrago/pgsanity","tags":["postgresql","sql","syntax check","quality assurance","testing","cli","database"],"install":[{"cmd":"pip install pgsanity","lang":"bash","label":"Install with pip"}],"dependencies":[{"reason":"PgSanity 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.","package":"PostgreSQL client development tools (providing 'ecpg' command)","optional":false}],"imports":[],"quickstart":{"code":"# Check a single SQL file\npip install pgsanity\n# Create a dummy SQL file\nwith open('test.sql', 'w') as f:\n    f.write('SELECT 1 FROM my_table;\\n')\n    f.write('INSERT INTO another_table (id) VALUES (10);\\n')\n\nimport subprocess\n\n# Run pgsanity on the file\nresult = subprocess.run(['pgsanity', 'test.sql'], capture_output=True, text=True)\nprint(f\"Exit Code: {result.returncode}\")\nprint(f\"STDOUT:\\n{result.stdout}\")\nprint(f\"STDERR:\\n{result.stderr}\")\n\n# Example of invalid SQL\nwith open('invalid.sql', 'w') as f:\n    f.write('SELECT * FROM non_existent_table WHERE bad_syntax;\\n')\n\nresult_invalid = subprocess.run(['pgsanity', 'invalid.sql'], capture_output=True, text=True)\nprint(f\"\\nExit Code (invalid): {result_invalid.returncode}\")\nprint(f\"STDOUT (invalid):\\n{result_invalid.stdout}\")\nprint(f\"STDERR (invalid):\\n{result_invalid.stderr}\")\n\n# Check SQL from stdin\nsql_to_check = 'SELECT current_timestamp;'\nresult_stdin = subprocess.run(['pgsanity'], input=sql_to_check, capture_output=True, text=True)\nprint(f\"\\nExit Code (stdin): {result_stdin.returncode}\")\nprint(f\"STDOUT (stdin):\\n{result_stdin.stdout}\")\nprint(f\"STDERR (stdin):\\n{result_stdin.stderr}\")","lang":"python","description":"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."},"warnings":[{"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.","message":"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.","severity":"gotcha","affected_versions":"All versions"},{"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.","message":"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.","severity":"gotcha","affected_versions":"All versions"}],"env_vars":null,"last_verified":"2026-04-13T00:00:00.000Z","next_check":"2026-07-12T00:00:00.000Z"}