sitting_duck

Parse and analyze source code ASTs from 27 programming languages with tree-sitter grammars, pattern matching, and structural search

Maintainer(s): teaguesterling

Installing and Loading

INSTALL sitting_duck FROM community;
LOAD sitting_duck;

Example

-- Parse Python code and find function definitions
SELECT name, start_line, peek
FROM parse_ast('
def hello():
    return "hello world"
def greet(name):
    print(f"Hello, {name}!")
    return name
', 'python')
WHERE is_function_definition(semantic_type);

-- Pattern matching: find eval() calls and capture arguments
SELECT captures['X'].peek as dangerous_input, file_path, start_line
FROM ast_match('code', 'eval(__X__)', 'python');

-- Pattern matching with variadic wildcards
SELECT captures['F'].name as func_name, captures['Y'].peek as return_value
FROM ast_match('code',
    'def __F__(__):
    %__<BODY*>__%
    return __Y__', 'python');

-- Security audit: find dangerous function calls
SELECT * FROM ast_security_audit('code')
WHERE risk_level = 'high';

-- Function complexity metrics
SELECT name, cyclomatic, max_depth, lines
FROM ast_function_metrics('code')
WHERE cyclomatic > 10;

-- Cross-language analysis with semantic types
SELECT language, COUNT(*) as functions
FROM read_ast(['src/**/*.py', 'src/**/*.js', 'src/**/*.go'])
WHERE is_function_definition(semantic_type)
GROUP BY language;

About sitting_duck

Sitting Duck is a DuckDB extension for parsing source code into Abstract Syntax Trees (ASTs) using tree-sitter grammars. It provides a powerful SQL interface to analyze, search, and understand code structure across 25+ programming languages.

Documentation: https://sitting-duck.readthedocs.io/

Core Features

Pattern Matching: Find code structures using pattern-by-example matching with wildcards:

-- Recursive wildcards for deep matching
SELECT * FROM ast_match('code', 'class __C__: %__<**>__% def __M__(self): %__<BODY*>__%', 'python');

-- Optional and negation wildcards
SELECT * FROM ast_match('code', 'def __F__(__<?ARGS>__): %__<BODY*>__%', 'python');

See: https://sitting-duck.readthedocs.io/en/latest/guide/pattern-matching/

CSS Selector Queries (v1.6.0, enhanced in v1.7.0+): Query AST nodes using CSS selector syntax — bootstrapped with sitting duck’s own CSS grammar:

SELECT name FROM ast_select('src/*.py', '.func:has(.call#execute):not(:has(try_statement))');

-- v1.7.0: :match (current-node) vs :contains (subtree) structural patterns
SELECT name FROM ast_select('src/*.py', '.func:contains("db.execute()")');
SELECT name FROM ast_select('src/*.py', 'call:match("db.execute()")');

-- v1.7.2: parse once, query many — orders of magnitude faster for interactive use
CREATE TABLE my_ast AS SELECT * FROM read_ast('src/**/*.py');
SELECT * FROM ast_select_from('my_ast', '.class:named');
SELECT * FROM ast_select_from('my_ast', '.func:has(return_statement)');

Supports type selectors, #name, .semantic (~80 aliases), combinators, :has(), :not(), :match() / :contains() structural patterns, scope/call-graph pseudo-classes, and pseudo-element navigation. Bare type matching: if matches if + if_statement + if_clause.

v1.10.0 adds runtime-loadable tree-sitter grammars (register_language(), off by default behind sitting_duck_enable_runtime_grammars), ast_to_blocks (render code structure as duck_blocks documents), and ast_patch/ast_replace (AST-anchored source patching and selector-driven rewriting in SQL).

v1.9.0 hardens the selector engine: no more silent-empty results (selectors the engine can’t honor now raise a clear error instead of returning 0 rows), #name on call nodes binds uniformly across all 27 grammars, and NULL columns no longer over- or under-match.

Parse-time filtering (v1.8.0): max_depth := and prune := shrink the AST at parse time, with automatic tree healing:

SELECT * FROM read_ast('file.py', max_depth := 2);
SELECT * FROM read_ast('src/**/*.py', prune := ['syntax', 'comments', 'punctuation']);

Prune policies: syntax, comments, literals, imports, types, punctuation, unnamed, leaves, internal.

Call-graph macros (v1.8.0):

• ast_get_calls(source) - calls with caller attribution and type classification
• ast_call_graph(source) - aggregated caller→callee graph with call counts
• ast_find_references(source, name) - scope-chain-aware symbol reference resolution

Table Functions:

• read_ast(file_pattern, language := NULL) - Parse source files into AST rows (parallel)
• parse_ast(content, language) - Parse source code strings (table function)
• parse_ast_list(content, language) - Parse source code strings (scalar, returns LIST; v1.7.0)
• ast_match(source, pattern, lang) - Pattern matching for code search
• ast_select(source, css_selector) - CSS selector queries
• ast_select_from(table_name, selector) - CSS selectors on pre-parsed tables (v1.7.2)
• ast_type_map(language) - Node type discovery across languages

v1.7.0 extraction suffix: Any extraction parameter (context, source, structure, peek) can take a +schema suffix that keeps the full column set in the output schema as NULLs without computing them:

-- Keep peek column in schema but skip its computation
SELECT name, peek FROM read_ast('file.py', peek := 'none+schema');

Enables SQL macros to declare stable schemas regardless of which columns they populate.

Relational Predicates (v1.5.0):

• ast_has(source, parent_type, child_type) - Check containment relationships
• ast_inside(source, child_type, parent_type, parent_name) - Find nodes inside specific parents
• ast_precedes(source, before_type, after_type) - Ordering predicates
• ast_follows(source, after_type, before_type) - Ordering predicates
• ast_not_has(source, parent_type, child_type) - Negated containment

Analysis Macros (file-path based):

• ast_definitions(source) - All named definitions with categories
• ast_function_metrics(source) - Cyclomatic complexity, nesting depth, line counts
• ast_security_audit(source) - Detect dangerous function call patterns
• ast_dead_code(source) - Find potentially unused functions/classes
• ast_nesting_analysis(source) - Identify deeply nested code
• ast_definition_parent(table) - Resolve nearest definition ancestor per node

Tree Navigation:

• ast_descendants(table, node_id) - Get subtree (O(1) using descendant_count)
• ast_ancestors(table, node_id) - Path from node to root
• ast_children(table, node_id) - Immediate children
• ast_function_scope(table, node_id) - Function body excluding nested functions

Semantic Predicates: Cross-language filtering with normalized types:

• is_function_definition(st), is_class_definition(st), is_variable_definition(st)
• is_function_call(st), is_literal(st), is_conditional(st), is_loop(st)

Supported Languages (27)

AST Schema

Each parsed node includes:

• type, name - Node type and extracted identifier
• semantic_type - Normalized type for cross-language queries
• file_path, language - Source location
• start_line, end_line, depth - Position and nesting
• descendant_count - For O(1) subtree queries
• peek - Configurable source preview
• qualified_name - Scope-based path unique within a file (C[User] F[__init__] format; v1.7.0)
• signature_type, parameters, modifiers, annotations - Native extraction (v1.8.0: modifiers[] populated across Python, JS, TS, Rust, Kotlin, Swift, Dart, C#; IS_EXPORTED flag for file-level visibility)
• scope - STRUCT<current, function, class, module, stack> (v1.7.4); replaces the v1.7.2 scope_id / scope_stack columns. scope.function lets you answer “what function is this in?” as a single column read.

Full schema: https://sitting-duck.readthedocs.io/en/latest/api/output-schema/

Use Cases

• Security auditing - Find dangerous patterns (eval, exec, SQL injection)
• Code quality - Complexity metrics, dead code detection, nesting analysis
• Refactoring - Pattern-based code search across large codebases
• Documentation - Extract function signatures and structure
• AI workflows - Structured code understanding for LLM tools

GitHub: https://github.com/teaguesterling/sitting_duck

Added Functions

Overloaded Functions

This extension does not add any function overloads.

Added Types

This extension does not add any types.

Added Settings