← All projects

poached

SQL parsing tools for building IDEs, editors, and query analyzers.

A DuckDB extension exposing SQL parsing functionality for building IDEs, syntax highlighters, query analyzers, and developer tools. Uses DuckDB's internal parser for accurate results.

Installation

INSTALL poached FROM community;
LOAD poached;

Syntax Validation

Check if SQL is syntactically valid

SELECT is_valid_sql('SELECT * FROM users WHERE id = 1');  -- true

SELECT is_valid_sql('SELECT * FROM');  -- false

Get error message for invalid SQL

SELECT sql_error_message('SELECT * FROM');
-- 'Parser Error: syntax error at or near ";"'

Tokenization

Returns tokens with byte positions and categories for syntax highlighting:

SELECT * FROM tokenize_sql('SELECT * FROM users');
┌───────────────┬──────────────────┐
│ byte_position │     category     │
├───────────────┼──────────────────┤
│             0 │ KEYWORD          │
│             7 │ OPERATOR         │
│             9 │ KEYWORD          │
│            14 │ IDENTIFIER       │
└───────────────┴──────────────────┘

Categories: KEYWORD, IDENTIFIER, OPERATOR, NUMERIC_CONSTANT, STRING_CONSTANT

Statement Analysis

Parse multi-statement SQL

SELECT * FROM parse_statements('SELECT 1; CREATE TABLE t(x INT)');
┌────────────┬───────────┬───────┬─────────────┐
│ stmt_index │ stmt_type │ error │ param_count │
├────────────┼───────────┼───────┼─────────────┤
│          0 │ SELECT    │ NULL  │           0 │
│          1 │ CREATE    │ NULL  │           0 │
└────────────┴───────────┴───────┴─────────────┘

Count statements

SELECT num_statements('SELECT 1; SELECT 2; SELECT 3');  -- 3

Query Introspection

Extract column names from SELECT

SELECT * FROM parse_columns('SELECT id, name AS username FROM users', 0);
┌───────────┬──────────┐
│ col_index │ col_name │
├───────────┼──────────┤
│         0 │ id       │
│         1 │ username │
└───────────┴──────────┘

Extract table references

SELECT * FROM parse_tables('SELECT * FROM users JOIN orders ON true');
┌─────────────┬────────────┬─────────┐
│ schema_name │ table_name │ context │
├─────────────┼────────────┼─────────┤
│ NULL        │ users      │ FROM    │
│ NULL        │ orders     │ JOIN    │
└─────────────┴────────────┴─────────┘

Get table names as array

SELECT parse_table_names('SELECT * FROM a JOIN b ON true');  -- [a, b]

Extract function calls

SELECT * FROM parse_functions('SELECT COUNT(*), UPPER(name) FROM t');
┌───────────────┬───────────────┐
│ function_name │ function_type │
├───────────────┼───────────────┤
│ count_star    │ aggregate     │
│ upper         │ scalar        │
└───────────────┴───────────────┘

Utilities

List all SQL keywords

SELECT * FROM sql_keywords() LIMIT 5;

Check if string is a keyword

SELECT is_keyword('SELECT');  -- true

SELECT is_keyword('mytable'); -- false

Strip comments from SQL

SELECT sql_strip_comments('SELECT 1 -- comment');  -- 'SELECT 1 '

Get parse info as JSON

SELECT sql_parse_json('SELECT 1 + 2 AS result');

Use Cases

• SQL IDEs: Syntax highlighting, real-time validation
• Query analyzers: Extract table/function dependencies
• Security auditing: Analyze WHERE clauses and access patterns
• Documentation: Auto-generate column lists from queries