README.md
1## PgSanity
2
3PgSanity checks the syntax of Postgresql SQL files.
4
5It does this by leveraging the ecpg command which is traditionally
6used for preparing C files with embedded SQL for compilation.
7However, as part of that preparation, ecpg checks the embedded SQL
8statements for syntax errors using the exact same parser that is
9in PostgreSQL.
10
11So the approach that PgSanity takes is to take a file that has a
12list of bare SQL in it, make that file look like a C file with
13embedded SQL, run it through ecpg and let ecpg report on the syntax
14errors of the SQL.
15
16[](https://travis-ci.org/markdrago/pgsanity)
17
18## Installation
19### Dependencies
20- Python >= 2.7
21 - May work with Python 2.6 if you install argparse (`sudo pip install argparse`)
22 - If you need support for Python < 2.6 let me know
23- ecpg
24 - Ubuntu/Debian: `sudo apt-get install libecpg-dev`
25 - RHEL/CentOS: `sudo yum install postgresql-devel`
26 - Arch: `sudo pacman -S postgresql-libs`
27
28### Getting PgSanity
29PgSanity is available in the Python Package Index, so you can install it with either easy_install or pip. Here's [PgSanity's page on PyPI](http://pypi.python.org/pypi/pgsanity).
30- `sudo pip install pgsanity` or `sudo easy_install pgsanity`
31 - If you don't have pip you can get it on Ubuntu/Debian by running: `sudo apt-get install python-pip`
32
33It is also available in the [FreeBSD ports](https://www.freebsd.org/ports/index.html) as [`databases/pgsanity`](https://www.freshports.org/databases/pgsanity/). You can install it with one of those commands:
34- `pkg install py36-pgsanity`
35- `pkg install py27-pgsanity`
36- `cd /usr/ports/databases/pgsanity && make install clean`
37
38## Usage
39PgSanity accepts filenames as parameters and it will report SQL syntax errors which exist in those files. PgSanity will exit with a status code of 0 if the syntax of the SQL looks good and a 1 if any errors were found.
40
41 $ pgsanity file_with_sql.sql
42 $ echo $?
43 0
44 $ pgsanity good1.sql good2.sql bad.sql
45 bad.sql: line 1: ERROR: syntax error at or near "bogus_token"
46 $ echo $?
47 1
48
49Since pgsanity can handle multiple filenames as parameters it is very comfortable to use with find & xargs.
50
51 $ find -name '*.sql' | xargs pgsanity
52 ./sql/bad1.sql: line 59: ERROR: syntax error at or near ";"
53 ./sql/bad2.sql: line 41: ERROR: syntax error at or near "insert"
54 ./sql/bad3.sql: line 57: ERROR: syntax error at or near "update"
55
56Additionally PgSanity will read SQL from stdin if it is not given any parameters. This way it can be used interactively or by piping SQL through it.
57
58 $ pgsanity
59 select column1 alias2 asdf from table3
60 line 1: ERROR: syntax error at or near "asdf"
61 $ echo $?
62 1
63 $ echo "select mycol from mytable;" | pgsanity
64 $ echo $?
65 0
66
67## Interpreting The Results
68The error messages pretty much come directly from ecpg. Something I have noticed while using pgsanity is that an error message on line X is probably more indicative of the statement right above X. For example:
69
70 $ echo "select a from b\ninsert into mytable values (1, 2, 3);" | pgsanity
71 line 2: ERROR: syntax error at or near "into"
72
73The real problem in that SQL is that there is no semicolon after the 'b' in the select statement. However, the SQL can not be determined to be invalid until the word "into" is encountered in the insert statement. When in doubt, look up to the previous statement.
74
75Another common error message that can be a little weird to interpret is illustrated here:
76
77 echo "select a from b" | pgsanity
78 line 2: ERROR: syntax error at or near ""
79
80The 'at or near ""' bit is trying to say that we got to the end of the file and no semicolon was found.
81
82## Reporting Problems
83If you encounter any problems with PgSanity, especially any issues where it incorrectly states that invalid SQL is valid or vice versa, please report the issue on [PgSanity's github page](http://github.com/markdrago/pgsanity). Thanks!
84
){kind=link}