PaperlayerView exact revision on arXiv

Source-linked AI summary

Automating Database-Native Function Code Synthesis with LLMs

Wei Zhou, Xuanhe Zhou, Qikang He, Guoliang Li, Bingsheng He, Quanqing Xu, Fan Wu

arXiv:2604.06231v1cs.DBcs.AIcs.CLcs.IRcs.SE

TL;DR

Database-native function synthesis is difficult because functions span declarations, multiple implementation units, and internal references that generic code-generation methods may mishandle. DBCooker characterizes these structures, applies database-aware planning, hybrid synthesis, progressive validation, and adaptive orchestration. It outperforms other methods across SQLite, PostgreSQL, and DuckDB and synthesizes functions absent from SQLite v3.50.

  • Problem

    Database-native functions are growing, while generic LLM-based methods can hallucinate or overlook context in multi-unit, dependency-rich synthesis.

  • Method

    DBCooker characterizes declarations, distinctive units, and cross-unit references, then combines pseudo-plans, hybrid fill-in-the-blank synthesis, progressive validation, and adaptive orchestration.

  • Results

    DBCooker outperforms state-of-the-art methods on SQLite, PostgreSQL, and DuckDB and supports synthesizing new functions absent from SQLite v3.50.

  • Takeaways & Limitations

    Database-aware characterization and synthesis operations support automatic implementation of native functions across multiple database systems.

  • Takeaways & Limitations

    Database codebases remain massive and fragmented, requiring identification of a small relevant set of scattered units rather than blindly including the entire codebase.

Abstract

from arXiv · show

Database systems incorporate an ever-growing number of functions in their kernels (a.k.a., database native functions) for scenarios like new application support and business migration. This growth causes an urgent demand for automatic database native function synthesis. While recent advances in LLM-based code generation (e.g., Claude Code) show promise, they are too generic for database-specific development. They often hallucinate or overlook critical context because database function synthesis is inherently complex and error-prone, where synthesizing a single function may involve registering multiple function units, linking internal references, and implementing logic correctly. To this end, we propose DBCooker, an LLM-based system for automatically synthesizing database native functions. It consists of three components. First, the function characterization module aggregates multi-source declarations, identifies function units that require specialized coding, and traces cross-unit dependencies. Second, we design operations to address the main synthesis challenges: (1) a pseudo-code-based coding plan generator that constructs structured implementation skeletons by identifying key elements such as reusable referenced functions; (2) a hybrid fill-in-the-blank model guided by probabilistic priors and component awareness to integrate core logic with reusable routines; and (3) three-level progressive validation, including syntax checking, standards compliance, and LLM-guided semantic verification. Finally, an adaptive orchestration strategy unifies these operations with existing tools and dynamically sequences them via the orchestration history of similar functions. Results show that DBCooker outperforms other methods on SQLite, PostgreSQL, and DuckDB (34.55% higher accuracy on average), and can synthesize new functions absent in the latest SQLite (v3.50).

1 Introduction

Database-native function synthesis is increasingly necessary but difficult because functions span multiple internal units, dependencies, and error-prone implementation steps.

  • Motivation: 237 to 630 PostgreSQL functions, 60 to 666 DuckDB functions, and 52 to 143 SQLite functions show rapid growth across versions.This expansion supports new scenarios and business migration, where code refactoring can consume 30%–60% of migration budgets.
  • Challenges: Database-native synthesis requires extensive expertise because SQL-level functions map to registered units, source files, and internal references.The date_trunc() example requires selecting units by argument types, locating implementations, and using correct references.
  • Challenges: A single SQL function may correspond to multiple underlying units with distinct names and responsibilities.This makes identifying the units requiring synthesis non-trivial.
  • Challenges: Simple functions may wrap standard libraries, whereas complex aggregates such as json_agg() require custom logic and adaptive synthesis strategies.The contrast motivates methods that handle varying function complexity.

2 Preliminary

The paper models a database-native function as a SQL-facing declaration implemented by new function units that may invoke existing reference units, then defines synthesis as producing compliant, test-passing kernel code.

  • Definitions: Database-native functions comprise a declaration, newly implemented units, and existing reference units integrated into the database kernel.Function units are self-contained executable components defined in database files.
  • Definitions: A function declaration specifies a native function’s name, description, argument types, and return type for registration and invocation.The declaration is typically stored in the system catalog.
  • Synthesis Problem: Synthesis aims to generate necessary function-unit code that satisfies a specification, integrates into the database, uses essential references, and passes database tests.The specification may include names, descriptions, input and output types, and SQL examples.
  • Pilot Study: Existing LLM and agent methods produce diverse errors, with declaration errors accounting for 81.76% of errors on average.Observed failures include redeclarations, mismatched function arguments, and incorrect test outputs.

3 DBCooker Overview

DBCooker adapts LLM-based synthesis to database codebases by characterizing structural organization and combining database-aware planning, coding, validation, and orchestration.

  • System Overview: DBCooker uses database-specific structural templates and constraints instead of relying on general-purpose code-synthesis behavior.The design accounts for strict mappings between SQL-level catalog definitions and implementation units.
  • Function Characterization: Its characterization process uses graph-based analysis to locate registration points, traverse dependencies, and distinguish reusable auxiliary units from distinctive units.This decomposition targets essential implementation structure rather than raw repository snippets.

4 Function Characterization

Function Characterization combines declarations, graph traversal, grouping, pairwise pruning, and dependency analysis to identify distinctive implementation units and compact reusable context.

  • Declaration Collection: DBCooker collects textual and code-style declarations from official documentation and database-specific sources to represent SQL-level semantics and implementation metadata.The dual-source strategy systematically extracts function declarations.
  • Graph-based Unit Extraction: Graph-based extraction initializes dependencies from SQL keywords, recursively traverses references, and stops when no units remain or a reference threshold is exceeded.Nodes represent function units and edges encode invocations or inheritance relations.
  • Distinctive Function Unit Identification: The method excludes widely reused components while retaining distinctive units needed to implement a target function.For DuckDB date_part, ScalarFunctionSet is excluded as a shared reference unit while DatePartFun is retained.
  • Pairwise Unit Pruning: Units are grouped by declaration types and functional category, then compared along reference paths after identifier abstraction and pruning.Non-pruned blocks become placeholders representing implementation variability.
  • Refinement and Reference Analysis: Multi-round comparisons select frequently occurring pruned units as representative reusable structures for fill-in-the-blank synthesis.The process can expand references on demand and applies type-specific pruning to retain essential context.

5 Function Code Synthesis Operations

DBCooker addresses database-specific synthesis challenges with structured plans, reuse-aware progressive coding, and staged validation. These operations identify implementation units and references before incrementally generating and checking function code.

  • Motivation: Existing synthesis frameworks generate database functions from scratch and struggle to identify complex relations such as referenced macros and internal units.Database functions can be large and complex, making direct generic generation unsuitable for characterized database information.
  • 5.1.1 Candidate Plan Generation: Candidate coding plans decompose each input type into function units, structured processing logic, and potential referenced units supporting each code block.Plans enumerate required units with file paths and describe logical blocks, such as extracting arguments, alongside reusable references.
  • 5.1.2 Scoring-based Plan Filtering: Ensemble plan generation produces multiple pseudo-plans and filters them using deterministic scores for reference faithfulness and plan simplicity.The scoring process removes incorrect units and references, then filters plans below a predefined threshold such as 0.5.
  • 5.1.2 Scoring-based Plan Filtering: Plan scoring combines normalized counts of incorrect references, incorrect file locations, and listed function units with weights 0.4, 0.4, and 0.2.The first two criteria characterize faithfulness, while the final criterion captures simplicity.
  • 5.2 Progressive Code Synthesis: Progressive fill-in-the-blank synthesis reuses representative function units and can trigger semantic rollback from template-based to from-scratch synthesis after validation feedback.Multiple candidate units are generated in parallel and merged using self-consistency.
  • 5.3 Progressive Validation: Three-level validation checks syntax, build and standards compliance, and semantic behavior using SQL test cases informed by expertise, existing suites, and internal code blocks.Semantic testing targets expected outputs and contextual coverage for error-prone areas such as input types.

6 From Static Function Synthesis to Adaptive Tool Orchestration

DBCooker replaces fixed synthesis pipelines with adaptive tool orchestration. It standardizes synthesis operations as callable tools and uses prior trajectories to select subsequent operations during stepwise implementation.

  • 6 From Static Function Synthesis to Adaptive Tool Orchestration: Fixed coding-to-testing pipelines cannot flexibly accommodate database functions whose synthesis complexity varies from concise wrappers to specialized processing logic.The paper motivates adaptive orchestration because different functions require different synthesis workflows.
  • 6.1 Operation-as-Tool Abstraction: Operation-as-tool abstraction encapsulates each synthesis operation as a callable tool with standardized invocation and result handling.The abstraction is intended to unify synthesis operations with common tools such as file search.
  • 6.1 Operation-as-Tool Abstraction: Each tool contains metadata, core logic, and routing modules that define its interface, execute the operation, and return standardized outputs for LLM context.The example plan_agent tool includes a name, arguments such as plan_num, and functionality descriptions.
  • 6.1 Operation-as-Tool Abstraction: The hybrid tool set combines bash and static-analysis utilities with LLM-based operations including pseudo-based planning, coding, and validation.This combination integrates existing tools with the proposed synthesis operations.
  • 6.2 Tool-based Stepwise Function Synthesis: Tool-based stepwise synthesis takes a function declaration, tool set, and synthesis memory pool, then returns synthesized function units.The algorithm initializes function units and a trajectory while selecting among planning, coding, and validation tools.
  • 6.2 Tool-based Stepwise Function Synthesis: The orchestration memory stores function metadata, prior tool trajectories, and synthesis state to guide adaptive invocation of tools for similar functions.The LLM can select a new plan after validation identifies errors and saves the resulting trajectory.

7 Experiments

Across SQLite, PostgreSQL, and DuckDB, DBCooker achieves the strongest synthesis accuracy and remains effective as functions become more difficult. Ablation and new-function experiments show that its database-aware characterization, planning, validation, and orchestration components address declaration, reference, and semantic errors.

  • Overall Performance: DBCooker achieves the highest synthesis accuracy across SQLite, PostgreSQL, and DuckDB, with Acc_EXE of 78.90% and Acc_RES of 65.19%.It outperforms other methods by average margins of 124.37% and 149.68% on these two metrics, respectively.
  • Overall Performance: CodeRAG improves LLM-based methods by an average of 50.56% accuracy by retrieving relevant function units from large database repositories.Retrieved context supports integrating multiple references, such as 4 structs and 18 functions for DuckDB’s array_cross_product.
  • Fine-Grained Analysis: DBCooker consistently outperforms other methods across EASY, MEDIUM, and HARD functions, reaching 68.97% accuracy for HARD functions.Other LLM-based and RAG-based methods decline from 35.30% Acc_EXE and 32.16% Acc_RES on EASY functions to 22.02% and 18.90% on HARD functions.
  • Ablation Study: Removing three-stage validation lowers PostgreSQL Acc_EXE from 78.62% to 6.9%, demonstrating its importance for resolving cross-file dependencies and semantic failures.Validation identifies syntax, compliance, and logic problems, including incorrect macros, missing registrations, and negative-interval handling errors in date_trunc().
  • Ablation Study: Planning and validation work together on complex logic: disabling planning changes PostgreSQL Acc_RES from 5.52% to 7.59%, but still misses branches such as interval handling.The absolute difference is 2.07%, compared with DBCooker’s 37.50% improvement.
  • New Native Function Synthesis: DBCooker synthesizes new SQLite functions by implementing every required function unit, declaring the SQL interface correctly, reusing repository references, and progressively refining code.For covar_pop(), it implements covarPopStep, covarPopFinalize, and covarPopInverse and registers them with WAGGREGATE.

8 Related Work

Existing code-generation methods include prompt-based, agent-based, and training-based approaches. These categories differ in how they use prompts, planning and tools, or model training to generate code.

  • Prompt-based methods treat code generation as conditional text generation from natural-language or code prompts.
  • Agent-based systems enhance LLMs with planning and tool usage for multi-step reasoning and code debugging.
  • Training-based models use model training as the basis for code generation.

9 Conclusion and Future Work

DBCooker combines database-specific characterization, synthesis operations, validation, and adaptive orchestration to address fragmented codebases and probabilistic correctness limits. The system is designed to remain useful across evolving database versions and future LLM improvements.

  • Conclusion: DBCooker characterizes function declarations, distinctive units, and cross-unit references before applying database-aware synthesis operations.Its operations include pseudo-code plan generation, progressive code synthesis, and three-stage code validation.
  • Conclusion: DBCooker outperforms state-of-the-art methods across three databases and supports synthesis of new functions.The experiments cover SQLite, PostgreSQL, and DuckDB.
  • Future Work: Future LLMs still need explicit function characterization because massive, fragmented codebases can hide relevant units even within long contexts.Blindly including entire codebases also incurs high inference costs.
  • Future Work: Database correctness remains deterministic while LLM generation is probabilistic, so structural templates and correctness constraints remain necessary.The paper identifies this mismatch as a continuing challenge for database-native function synthesis.
  • Future Work: Version-specific retrieval and orchestration help adapt synthesis to changing signatures, macros, and system catalog definitions without retraining.Database functions evolve across versions, creating conflicts with mixed or deprecated training conventions.
Loading 2604.06231v1…