How a better SQL validator could look. There are a plethora of SQL validators available, but almost all of them are incredibly simple, to the extent that they do not actually validate a given SQL query properly. The problem is not always just poor or inadequate validation of the SQL syntax, but also the user-friendliness of the SQL validator itself; it is difficult to see what exactly the syntax error is and where it is located.

A common example of this is when the SQL validator correctly locates a validation error in a SQL query, but fails to report that the SQL query contains 1-2-3 more validation errors. Another problem is how the validation error is displayed; often separately from the SQL query itself, so the user has to find the line and column number, if specified. A third problem is the validation error itself, which is often cryptic and unclear, e.g. “ERROR: syntax error at or near ”(…”.

Here are the biggest culprits among SQL validators:

• Only finds the first validation error
• Validation errors are not displayed directly in the SQL query
• Validation errors are cryptic and imprecise
• Often, a fixed version of the SQL query is not provided, and there is no indication of what has been corrected
• No option to compare the invalid SQL query and fixed SQL query (i.e., view diff) The shortcomings of the SQL validator also serve as a blueprint for a better SQL validator, which I have developed. With the use of AI, validation errors can be found relatively easily, described in clear and understandable language, and corrected (at least theoretically, but two SQL validators tested below were unable to fix the invalid SQL query correctly). The difficult part here is presenting the validation errors in a user-friendly and clear manner so that the user can easily see exactly where the validation error is in the SQL query.

Of course, one could let AI add the validation errors as inline comments, which would already be a significant improvement over existing SQL validators, but it is also possible to display highlighted code annotations on specific lines with the validation errors.

SQL validation example

Here is the invalid SQL query that I will use to test my own SQL validator and 3 existing SQL validators. It contains multiple SQL syntax errors and is for the PostgreSQL database engine (all common database engines are supported):

SELECT    employee_id,    fist_name || ' ' || last_name AS full_name,    'Employee' AS contact_type,    hire_date    COUNT(reports_to)FROM    employeesWHERE    salary > 50000ORDER BY full_nameUNIONSELECT    contact_name,    'Customer' AS contact_type,    last_order_dateFROM    'customers'WHERE    total_revenue > 10000 AND,    status = "Active"LIMIT '25';

When run through the SQL validator, it finds the SQL syntax errors, displays them as highlighted code annotations, provides a detailed explanation, and finally the corrected SQL query. The database engine used is PostgreSQL version 17:

SQL query with syntax errors

SELECT    employee_id,    fist_name || ' ' || last_name AS full_name,    'Employee' AS contact_type,    hire_date    COUNT(reports_to)FROM    employeesWHERE    salary > 50000ORDER BY full_nameUNIONSELECT    contact_name,    'Customer' AS contact_type,    last_order_dateFROM    'customers'WHERE    total_revenue > 10000 AND,    status = "Active"LIMIT '25';

#	Explanation
1	Missing comma after hire_date: I added a comma after hire_date so the next select item is properly separated.
2	Mixing aggregate with non-aggregate columns: COUNT(reports_to) appeared alongside non-aggregated columns without GROUP BY. I replaced the aggregate with a correlated subquery that returns the count per employee to avoid GROUP BY.
3	ORDER BY before UNION: ORDER BY cannot appear before a UNION. I moved the ORDER BY to after the final SELECT (after the UNION).
4	Column count mismatch in UNION: The two SELECTs returned different numbers of columns. I modified the second SELECT to return the same five columns (using NULL and a literal 0 where appropriate).
5	Quoted table name: ‘customers’ used single quotes (string) instead of an identifier. I removed the quotes and used the customers identifier.
6	Stray comma in WHERE: Removed the stray comma after AND in the second WHERE clause.
7	Wrong string quoting: “Active” used double quotes (identifier in SQL) instead of a string literal. I replaced it with ‘Active’.
8	Quoted LIMIT value: LIMIT ‘25’ used a quoted string; I changed it to the numeric literal 25.

Valid SQL query

Valid SQL query

SELECT    employee_id,    fist_name || ' ' || last_name AS full_name,    'Employee' AS contact_type,    hire_date,    (SELECT COUNT(*) FROM employees e2 WHERE e2.reports_to = e.employee_id) AS reports_countFROM    employees eWHERE    salary > 50000UNIONSELECT    NULL AS employee_id,    contact_name AS full_name,    'Customer' AS contact_type,    last_order_date AS hire_date,    0 AS reports_countFROM    customersWHERE    total_revenue > 10000 AND    status = 'Active'ORDER BY    full_nameLIMIT 25;

By clicking on “view diff,” you can compare the invalid SQL query with the corrected and valid SQL query:

The view diff is made with the Monaco Editor, which is the code editor that VS Code is based on.

Existing SQL validators

To provide an insight into what existing SQL validators are like and to show some of the shortcomings mentioned, I have selected a three of the highest-ranked SQL validators on Google. I am using the same invalid SQL query that I used for the above example. Here are the highest-ranked SQL validators on Google (when searching for “SQL validator” and being located in New York City):

1. Runsql

It validates the first syntax error and displays it separately from the actual SQL query.

You can visit it here.

2. Aiven

It validates the only first syntax error, displays it separately and discreetly inline.

You can correct the invalid SQL query, but the returned SQL query is still invalid:

-- Fixed: Several syntax errors corrected.-- Fixed: COUNT(reports_to) is not a valid expression in SELECT-- Fixed: Added missing alias for contact_name in first SELECT-- Fixed: Removed single quotes from table name 'customers'-- Fixed: Removed AND, after total_revenue > 10000-- Fixed: Status value should be enclosed in single quotes.-- Fixed: LIMIT should be an integer without quotes.-- Fixed: hire_date should have an aliasSELECT  employee_id,  first_name || ' ' || last_name AS full_name,  'Employee' AS contact_type,  hire_date AS hire_date,  (SELECT COUNT(*) FROM employees e2 WHERE e2.reports_to = e1.employee_id) AS report_countFROM  employees e1WHERE  salary > 50000ORDER BY  full_nameUNIONSELECT  NULL,  contact_name,  'Customer' AS contact_type,  last_order_dateFROM  customersWHERE  total_revenue > 10000  AND status = 'Active'LIMIT  25;

The “fixed” SQL query still contains two errors:

SELECT  employee_id,  first_name || ' ' || last_name AS full_name,  'Employee' AS contact_type,  hire_date AS hire_date,  (SELECT COUNT(*) FROM employees e2 WHERE e2.reports_to = e1.employee_id) AS report_countFROM  employees e1WHERE  salary > 50000ORDER BY  full_nameUNIONSELECT  NULL,  contact_name,  'Customer' AS contact_type,  last_order_dateFROM  customersWHERE  total_revenue > 10000  AND status = 'Active'LIMIT  25;

Another minor “correction error” was that it changed ‘fist_name’ to “first_name,” which is not technically an SQL syntax error.

You can visit it here.

3. Sqlvalidator

It is a super simple SQL validator where you can choose different database engine, but not version, and then either validate or fix the SQL query:

You can correct the invalid SQL query, but the returned SQL query is still invalid:

SELECT    employee_id,    first_name || ' ' || last_name AS full_name,    'Employee' AS contact_type,    hire_date,    COUNT(reports_to) OVER () AS report_countFROM    employeesWHERE    salary > 50000ORDER BY full_nameUNIONSELECT    contact_name,    'Customer' AS contact_type,    last_order_dateFROM    customersWHERE    total_revenue > 10000 AND    status = 'Active'LIMIT 25;

You can visit it here.