A better SQL validator and comparison with existing SQL validators

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
    employees
WHERE
    salary > 50000
ORDER BY full_name
UNION
SELECT
    contact_name,
    'Customer' AS contact_type,
    last_order_date
FROM
    '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:

SELECT
    employee_id,
    fist_name || ' ' || last_name AS full_name,
    'Employee' AS contact_type,

    hire_date

    COUNT(reports_to)
FROM
    employees
WHERE
    salary > 50000

ORDER BY full_name

UNION
SELECT
    contact_name,
    'Customer' AS contact_type,
    last_order_date
FROM

    'customers'
WHERE

    total_revenue > 10000 AND,

    status = "Active"

LIMIT '25';

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_count
FROM
    employees e
WHERE
    salary > 50000

UNION

SELECT
    NULL AS employee_id,
    contact_name AS full_name,
    'Customer' AS contact_type,
    last_order_date AS hire_date,
    0 AS reports_count
FROM
    customers
WHERE
    total_revenue > 10000 AND
    status = 'Active'
ORDER BY
    full_name
LIMIT 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 alias
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_count
FROM
  employees e1
WHERE
  salary > 50000
ORDER BY
  full_name
UNION
SELECT
  NULL,
  contact_name,
  'Customer' AS contact_type,
  last_order_date
FROM
  customers
WHERE
  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_count
FROM
  employees e1
WHERE
  salary > 50000

ORDER BY
  full_name

UNION
SELECT
  NULL,
  contact_name,
  'Customer' AS contact_type,
  last_order_date
FROM
  customers
WHERE
  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_count
FROM
    employees
WHERE
    salary > 50000

ORDER BY full_name

UNION
SELECT
    contact_name,
    'Customer' AS contact_type,
    last_order_date
FROM
    customers
WHERE
    total_revenue > 10000 AND
    status = 'Active'
LIMIT 25;

You can visit it here.