Search Shortcut cmd + k | ctrl + k
llm
Downloads 7.6kthis week
GitHub stars 4
Extension descriptor (YAML)

Call LLM APIs (OpenAI, Gemini, Cloudflare) directly from SQL with structured output support.

Maintainer(s): onnimonni

Compatibility Matrix

This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.

Platform 1.4.4 1.4.5 1.5.5 1.5.6 2.0.0
Linux AMD64
Linux ARM64
macOS AMD64
macOS ARM64
Windows AMD64
Windows ARM64
WebAssembly

Installing and Loading 

INSTALL llm FROM community;
LOAD llm;

About llm

Call LLM APIs directly from SQL with support for OpenAI, Gemini, Cloudflare Workers AI, and local OpenAI-compatible servers.

Features

  • llm() table function with lateral join and named parameter support
  • prompt() scalar function with auto-detection of API keys
  • llm_and_cache() for immediate responses with caching
  • llm_batch_and_cache() for 50% cheaper batch API processing
  • llm_hash() for LEFT JOIN support with cached results
  • CREATE SECRET support for secure API key storage
  • Structured output with return_type and json_schema

Structured Output Examples

Integer output:

SELECT response FROM llm('What is 15 + 27?',
    provider := 'gemini', model := 'gemini-2.5-flash',
    return_type := 'INTEGER');
-- Returns: {"value":42}

Array output:

SELECT response FROM llm('List the first 5 prime numbers.',
    provider := 'gemini', model := 'gemini-2.5-flash',
    return_type := 'INTEGER[]');
-- Returns: {"value": [2, 3, 5, 7, 11]}

Struct output:

SELECT response FROM llm('Info about Paris.',
    provider := 'gemini', model := 'gemini-2.5-flash',
    json_schema := '{
        "type": "OBJECT",
        "properties": {
            "city": {"type": "STRING"},
            "country": {"type": "STRING"},
            "population": {"type": "INTEGER"}
        },
        "required": ["city", "country", "population"]
    }');
-- Returns: {"city": "Paris", "country": "France", "population": 2140000}

Array of structs:

SELECT response FROM llm('List 3 European capitals.',
    provider := 'gemini', model := 'gemini-2.5-flash',
    json_schema := '{
        "type": "OBJECT",
        "properties": {
            "cities": {
                "type": "ARRAY",
                "items": {
                    "type": "OBJECT",
                    "properties": {
                        "name": {"type": "STRING"},
                        "country": {"type": "STRING"}
                    }
                }
            }
        }
    }');
-- Returns: {"cities": [{"name": "Paris", "country": "France"}, ...]}

For more information, see the documentation.

Added Functions

function_name function_type description comment examples
byte_range scalar NULL NULL  
http_delete scalar NULL NULL  
http_delete table NULL NULL  
http_get scalar NULL NULL  
http_get table NULL NULL  
http_head scalar NULL NULL  
http_head table NULL NULL  
http_patch scalar NULL NULL  
http_patch table NULL NULL  
http_post scalar NULL NULL  
http_post table NULL NULL  
http_post_form scalar NULL NULL  
http_post_form table NULL NULL  
http_post_multipart scalar NULL NULL  
http_post_multipart table NULL NULL  
http_put scalar NULL NULL  
http_put table NULL NULL  
http_range_header scalar NULL NULL  
llm table NULL NULL  
llm_and_cache scalar NULL NULL  
llm_batch_and_cache scalar NULL NULL  
llm_hash scalar NULL NULL  
prompt scalar NULL NULL  

Overloaded Functions

function_name function_type description comment examples
map_extract_value scalar Returns the value for a given key or NULL if the key is not contained in the map. The type of the key provided in the second parameter must match the type of the map’s keys else an error is returned NULL [map_extract_value(map(['key'], ['val']), 'key')]

Added Types

type_name type_size logical_type type_category internal
HTTP_HEADERS 16 MAP COMPOSITE true

Added Settings

name description input_type scope aliases typed_value
http_follow_redirects Automatically follow HTTP redirects (default: true) BOOLEAN GLOBAL [] true
http_max_concurrency Maximum number of concurrent HTTP requests per scalar function call (default: 32) UBIGINT GLOBAL [] 32
http_request_cache Cache HTTP responses within query to prevent duplicate requests (default: true) BOOLEAN GLOBAL [] true
http_user_agent Custom User-Agent header for all HTTP requests (default: DuckDB) VARCHAR GLOBAL []  
llm_base_url Base URL for local LLM provider VARCHAR GLOBAL [] http://localhost:1234
llm_cloudflare_account_id Cloudflare account ID VARCHAR GLOBAL [] NULL
llm_cloudflare_api_key Cloudflare API token VARCHAR GLOBAL [] NULL
llm_default_max_tokens Default max tokens INTEGER GLOBAL [] 1024
llm_default_model Default model name VARCHAR GLOBAL [] NULL
llm_default_provider Default LLM provider (local, openai, gemini, cloudflare) VARCHAR GLOBAL [] local
llm_default_temperature Default temperature (0.0-1.0) DOUBLE GLOBAL [] 0.0
llm_gemini_api_key Gemini API key VARCHAR GLOBAL [] NULL
llm_openai_api_key OpenAI API key VARCHAR GLOBAL [] NULL
llm_openai_base_url OpenAI API base URL VARCHAR GLOBAL [] https://api.openai.com
temp_directory Set the directory to which temporary files are written. VARCHAR GLOBAL [] post.db.tmp