Automating Database-Native Function Code Synthesis with LLMs

1. Problem Statement

Database systems (e.g., PostgreSQL, DuckDB, SQLite) continuously expand their kernels with new native functions to support emerging scenarios (e.g., BI analysis, geometric processing) and facilitate business migrations (e.g., Oracle to PostgreSQL). However, synthesizing these functions is a highly complex, error-prone task that currently relies on extensive human expertise.

Key Challenges:

• Complexity of Composition: A single SQL-level function often maps to multiple underlying "function units" (e.g., date_trunc requires separate implementations for timestamptz and interval types) that must be registered in system catalogs and implemented in specific source files.
• Dependency Density: Implementations rely heavily on internal references (macros, helper functions, data structures). A single function may depend on hundreds of lines of code across multiple files. Missing or incorrect references lead to compilation failures or semantic errors.
• Limitations of Existing LLMs: General-purpose code generation tools (e.g., Claude Code, Qwen Code) and agent-based systems fail in this domain because:
  • They hallucinate function units or miss critical registration steps.
  • They rely on inefficient, coarse-grained file searches rather than understanding database-specific structural conventions.
  • They lack adaptive strategies for varying function complexities (e.g., simple math wrappers vs. complex aggregate logic).
  • They often fail to validate against strict database kernel constraints (syntax, compliance, and semantics).

2. Methodology: DBCooker

The authors propose DBCooker, an LLM-based system designed specifically for the structural and semantic constraints of database kernels. It consists of three core modules:

A. Function Characterization Module

This module analyzes the database codebase to extract structural templates before synthesis begins, addressing the "what to implement" and "where to find references" problems.

• Multi-source Declaration Collection: Aggregates function definitions from official documentation (textual) and system catalogs (e.g., pg_proc.dat in PostgreSQL) to create a unified JSON specification.
• Distinctive Function Unit Identification: Uses graph-based static analysis to trace dependencies from a SQL function to its underlying C/C++ units. It employs pairwise unit pruning to distinguish between reusable auxiliary units (templates) and distinctive units requiring new implementation.
• Cross-Unit Reference Analysis: Extracts essential dependencies (e.g., macros like PG_GETARG_TEXT_PP) while pruning redundant details (e.g., full class definitions) to provide a concise context for the LLM.

B. Function Code Synthesis Operations

This module executes the actual code generation using three specialized operations:

1. Pseudo-based Coding Plan Generation: Instead of generating code directly, the system first generates a structured "pseudo-plan" (skeleton). This plan identifies required function units, file locations, and referenced macros. A scoring mechanism filters plans based on faithfulness (correct references) and simplicity.
2. Progressive Code Synthesis (Fill-in-the-Blank): The system treats synthesis as a "fill-in-the-blank" task. It retrieves representative code templates from similar functions and fills in the distinctive logic.
   • Adaptive Model: If the fill-in-the-blank approach fails (e.g., due to incorrect unit identification), a probabilistic mechanism triggers a "rollback" to a "from-scratch" generation mode, allowing the LLM to explore the repository dynamically.
3. Three-Stage Code Validation: A progressive verification loop ensures correctness:
   • Syntax Level: Checks parseability using language-specific parsers (e.g., ANTLR).
   • Compliance Level: Verifies adherence to database-specific conventions (e.g., correct registration in catalogs, proper file inclusion) using native build tools.
   • Semantic Level: Uses LLM-generated test cases to verify runtime behavior against expected outputs.

C. Adaptive Tool Orchestration

To handle functions of varying complexity, DBCooker abstracts synthesis steps (planning, coding, validation) and common utilities (bash, static analysis) into callable tools.

• Memory-Augmented Orchestration: An LLM controller dynamically sequences these tools based on the current context and a trajectory memory pool. This memory stores successful workflows from previous synthesis tasks, allowing the system to retrieve and adapt strategies for similar functions (e.g., using a specific workflow for "Date Functions" vs. "Math Functions").

3. Key Contributions

1. First Automated System: DBCooker is the first system to analyze function composition, generate internal references, and adaptively synthesize database native functions across different kernels.
2. Novel Characterization Module: Introduces a method to decompose SQL functions into distinctive units and prune reference graphs, solving the problem of identifying which units need implementation.
3. Hybrid Synthesis Strategy: Combines template-based "fill-in-the-blank" coding with probabilistic adaptation and a three-stage validation framework to ensure high reliability.
4. Adaptive Orchestration: Moves beyond static pipelines by using a memory-augmented, tool-based orchestration strategy that learns from historical synthesis trajectories.

4. Experimental Results

The system was evaluated on SQLite, PostgreSQL, and DuckDB against state-of-the-art methods (including GPT-5, Claude Opus, Claude Code, and TRAE).

• Accuracy: DBCooker achieved an average 34.55% higher accuracy than the best existing methods.
  • Compliance Accuracy (AccEXE): 78.90% (vs. ~36% for agents).
  • Result Accuracy (AccRES): 65.19% (vs. ~24% for agents).
• Robustness: It significantly outperformed others on Hard difficulty functions (68.97% accuracy vs. <30% for others) and across all function categories (Math, Date, String, JSON).
• Error Reduction: DBCooker reduced declaration-related errors (the most common failure mode) by effectively identifying correct file paths and macro references.
• New Function Synthesis: The system successfully synthesized four categories of new functions (e.g., covar_pop, century, gamma) for SQLite that do not exist in the latest version (v3.50), demonstrating its ability to extend database capabilities.

5. Significance and Future Outlook

• Bridging the Gap: DBCooker addresses the critical bottleneck in database migration and evolution, potentially reducing the 30–60% of migration budgets currently spent on manual code refactoring.
• Beyond Retrieval: The paper demonstrates that successful database synthesis requires more than just retrieving code snippets; it demands enforcing kernel-level constraints and adaptive logic.
• Scalability: The architecture is designed to handle massive codebases (millions of lines) by explicitly identifying relevant units rather than relying on LLMs' long-context windows alone.
• Deterministic Correctness: By integrating static analysis and multi-stage validation, DBCooker mitigates the probabilistic nature of LLMs, ensuring the generated code meets the strict correctness guarantees required for database kernels.

The source code is available at https://github.com/weAIDB/DBCooker.