AI functions for SQL — completions, classification, extraction, embeddings, and read-only SQL generation across local and hosted model providers
Maintainer(s):
leonardovida
Installing and Loading
INSTALL ai FROM community;
LOAD ai;
Example
-- Use a free local model via Ollama (https://ollama.com)
SET duckdb_ai_provider = 'ollama';
SET duckdb_ai_model = 'gemma4:e4b';
SELECT ai_summarize('DuckDB is an analytical database built for fast local queries.');
-- Or a hosted provider, with the key kept in a DuckDB secret
CREATE SECRET openai_ai (TYPE duckdb_ai, AI_PROVIDER 'openai', API_KEY '...', MODEL 'gpt-4o-mini');
SELECT ai_classify('invoice overdue', 'billing, support', secret := 'openai_ai');
About ai
The ai extension adds model-backed analytical functions to SQL: completions
(ai_complete), text tasks (ai_summarize, ai_classify, ai_extract,
ai_translate, ai_redact, ai_filter), structured output validated
against a JSON Schema (ai_complete_json, ai_complete_record,
ai_extract_record), embeddings and similarity (ai_embed,
ai_similarity, ai_rerank), aggregates (ai_agg, ai_summarize_agg),
and a SQL assistant that only returns parser-validated read-only SELECT
statements (ai_sql, ai_query_data).
It supports local Ollama, llama.cpp, and OpenAI-compatible servers as well as OpenAI,
Azure OpenAI, Anthropic, Gemini, Mistral, DeepSeek, OpenRouter, Databricks,
Snowflake Cortex, and Z.ai. Credentials come from environment variables or
DuckDB TYPE duckdb_ai secrets — never SQL arguments. The runtime includes
concurrency/rate controls, retries with backoff, opt-in response and
provider-side prompt caching, batched embedding requests, a network egress
allowlist, and local usage/cost tracking via ai_usage().
The extension performs no network calls at load time; providers are only
contacted when an ai_* function that needs one is executed. Full docs:
https://github.com/leonardovida/duckdb-ai
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| ai_agg | aggregate | Runs one completion over grouped text values and an instruction. | NULL | [SELECT ai_agg(review, 'List the top complaints') FROM reviews;] |
| ai_build_classifier | aggregate | Builds an experimental persisted classifier artifact using sampled LLM labels and batched embeddings. | NULL | [SELECT ai_build_classifier(review, ['positive', 'negative'], optimization := 'minimize_cost') FROM reviews;] |
| ai_classify | scalar | Chooses one label from a comma-separated VARCHAR or VARCHAR[] label list. | NULL | [SELECT ai_classify(review, ['positive', 'negative']) FROM reviews;, SELECT ai_classify(review, 'positive, negative, mixed') FROM reviews;] |
| ai_classify_labels | scalar | Chooses zero or more labels from a comma-separated VARCHAR or VARCHAR[] label list. | NULL | [SELECT ai_classify_labels(review, ['shipping', 'pricing', 'quality']) FROM reviews;] |
| ai_classify_optimized | scalar | Uses a persisted centroid classifier artifact and falls back to an LLM when local confidence is insufficient. | NULL | [SELECT ai_classify_optimized(review, classifier) FROM reviews;] |
| ai_classify_result | scalar | Classifies into zero or more labels and returns STRUCT(value, error, metadata). | NULL | [SELECT ai_classify_result(review, ['shipping', 'pricing', 'quality']) FROM reviews;] |
| ai_clear_cache | table | Clears per-database in-memory response and generated-SQL caches. | NULL | [SELECT * FROM ai_clear_cache();] |
| ai_clear_usage | table | Clears the per-database usage event buffer. | NULL | [SELECT * FROM ai_clear_usage();] |
| ai_complete | scalar | Calls a completion model and returns the response text. | NULL | [SELECT ai_complete('Say hello');, SELECT ai_complete('Say hello', provider := 'ollama', model := 'llama3.2');] |
| ai_complete_json | scalar | Calls a completion model and validates the response as a JSON object or array. | NULL | [SELECT ai_complete_json('Return a JSON object with one key named ok');] |
| ai_complete_record | table | Calls a completion model and projects a JSON object response into typed columns from a JSON Schema. | NULL | [SELECT * FROM ai_complete_record('Describe a duck', '{"type": "object", "properties": {"name": {"type": "string"}}}');] |
| ai_completion_request_json | scalar | Returns the completion request JSON without making a network call. | NULL | [SELECT ai_completion_request_json('Say hello');] |
| ai_count_tokens | scalar | Returns a local approximate token count for text. | NULL | [SELECT ai_count_tokens('hello world');] |
| ai_deprovision_endpoint | table | Explicitly submits endpoint deprovisioning through the control plane. | NULL | [SELECT * FROM ai_deprovision_endpoint('support_model');] |
| ai_embed | scalar | Calls an embedding model and returns the embedding as DOUBLE[]. | NULL | [SELECT ai_embed('duck');] |
| ai_embedding_request_json | scalar | Returns the embedding request JSON without making a network call. | NULL | [SELECT ai_embedding_request_json('duck');] |
| ai_endpoint_status | table | Returns the current state of an asynchronous endpoint operation. | NULL | [SELECT * FROM ai_endpoint_status('operation-id');] |
| ai_explain_sql | table | Explains one read-only DuckDB SELECT statement. | NULL | [SELECT * FROM ai_explain_sql('SELECT 42');] |
| ai_extract | scalar | Extracts requested information from text. | NULL | [SELECT ai_extract('Anna is 31', 'the age of the person');] |
| ai_extract_record | scalar | Extracts one typed STRUCT per row from text using a JSON Schema. | NULL | [SELECT ai_extract_record('Anna is 31', '{"type": "object", "properties": {"age": {"type": "integer"}}}');] |
| ai_filter | scalar | Evaluates a natural-language predicate against text and returns BOOLEAN. | NULL | [SELECT * FROM reviews WHERE ai_filter(review, 'mentions shipping problems');] |
| ai_fix_grammar | scalar | Rewrites text with corrected grammar, spelling, and punctuation. | NULL | [SELECT ai_fix_grammar('thes is a tst');] |
| ai_fix_sql | table | Rewrites a broken query as one corrected read-only DuckDB SELECT, or rewrites one line with mode := 'line'. | NULL | [SELECT * FROM ai_fix_sql('SELEC 42');, SELECT * FROM ai_fix_sql('SELECT amout FROM sales', error := 'column amout not found', fix_attempts := 2);] |
| ai_generate_chunks | table | Splits text into deterministic fixed or recursive Unicode-aware chunks. | NULL | [SELECT * FROM ai_generate_chunks('First paragraph. Second paragraph.');] |
| ai_is_read_only_sql | scalar | Returns whether SQL is one parser-valid read-only SELECT statement. | NULL | [SELECT ai_is_read_only_sql('SELECT 42');] |
| ai_model_prices | table | Returns the built-in provider/model pricing catalog. | NULL | [SELECT * FROM ai_model_prices();] |
| ai_models | table | Lists registered external model profiles without credential material. | NULL | [SELECT * FROM ai_models();] |
| ai_parse_document | table | Parses a BLOB through a normalized remote document-parser profile. | NULL | [SELECT * FROM ai_parse_document(read_blob('document.pdf').content, 'application/pdf', 'documents');] |
| ai_prep_search | table | Creates retrieval and context-enriched embedding chunks from text or Markdown. | NULL | [SELECT * FROM ai_prep_search('# Guide |
| DuckDB runs in process.', title := 'Guide');] | ||||
| ai_provider_base_url | scalar | Returns the default base URL for a supported provider. | NULL | [SELECT ai_provider_base_url('openai');] |
| ai_provider_protocol | scalar | Returns the internal protocol used for a supported provider. | NULL | [SELECT ai_provider_protocol('openai');] |
| ai_provision_endpoint | table | Plans or explicitly submits guarded endpoint provisioning through the control plane. | NULL | [SELECT * FROM ai_provision_endpoint('support_model');] |
| ai_query_data | table | Generates one read-only SELECT at bind time and executes it as a subquery. | NULL | [SELECT * FROM ai_query_data('total sales by region');, SELECT * FROM ai_query_data('total sales by region', include_tables := ['main.sales']);] |
| ai_recommended_batch_size | scalar | Returns a conservative row batch size for rate-limited AI jobs. | NULL | [SELECT ai_recommended_batch_size(200, 100, 100000);] |
| ai_redact | scalar | Masks direct personal data, credentials, secrets, and payment identifiers in text. | NULL | [SELECT ai_redact('Contact anna@example.com');] |
| ai_rerank | scalar | Uses a completion model to score candidate relevance to a query from 0 to 1. | NULL | [SELECT ai_rerank('best analytics database', 'DuckDB is an in-process analytics database');] |
| ai_schema_prompt | table | Returns deterministic local catalog context for prompting SQL models. | NULL | [SELECT * FROM ai_schema_prompt();] |
| ai_score | scalar | Uses a completion model to score how well input satisfies criteria from 0 to 1. | NULL | [SELECT ai_score('DuckDB runs in process', 'describes an embedded database');] |
| ai_secrets | table | Lists configured duckdb_ai secrets with credentials redacted. | NULL | [SELECT * FROM ai_secrets();] |
| ai_sentiment | scalar | Classifies text sentiment as positive, neutral, or negative. | NULL | [SELECT ai_sentiment(review) FROM reviews;] |
| ai_similarity | scalar | Embeds two strings and returns their cosine similarity. | NULL | [SELECT ai_similarity('duck', 'goose');] |
| ai_sql | scalar | Generates one read-only DuckDB SELECT statement from a natural-language question. | NULL | [SELECT ai_sql('total sales by region');, SELECT ai_sql('total sales by region', include_tables := ['main.sales'], fix_attempts := 2);] |
| ai_summarize | scalar | Summarizes text with a completion model. | NULL | [SELECT ai_summarize(review) FROM reviews;] |
| ai_summarize_agg | aggregate | Summarizes grouped text values with one completion call. | NULL | [SELECT ai_summarize_agg(review) FROM reviews;] |
| ai_translate | scalar | Translates text to the target language. | NULL | [SELECT ai_translate('Hello', 'French');] |
| ai_try_complete | scalar | Calls a completion model and returns STRUCT(response, error) so row-level failures can be captured. | NULL | [SELECT ai_try_complete('Say hello');] |
| ai_usage | table | Returns recent per-database AI usage events. | NULL | [SELECT * FROM ai_usage();] |
| ai_usage_summary | table | Returns query-level AI usage totals and bounded-buffer drop counters. | NULL | [SELECT * FROM ai_usage_summary();] |
| ai_validate_read_only_sql | scalar | Returns normalized SQL or raises an error if it is not one read-only SELECT statement. | NULL | [SELECT ai_validate_read_only_sql('SELECT 42');] |
Overloaded Functions
This extension does not add any function overloads.
Added Types
This extension does not add any types.
Added Settings
| name | description | input_type | scope | aliases |
|---|---|---|---|---|
| duckdb_ai_aggregate_model | Default AI model for aggregate functions | VARCHAR | GLOBAL | [] |
| duckdb_ai_allowed_hosts | Comma-separated AI provider host allowlist for duckdb_ai; empty allows all hosts | VARCHAR | GLOBAL | [] |
| duckdb_ai_base_url | Default AI provider base URL override for duckdb_ai | VARCHAR | GLOBAL | [] |
| duckdb_ai_cache | Cache successful AI provider responses in the current DuckDB instance | BOOLEAN | GLOBAL | [] |
| duckdb_ai_cache_max_entries | Maximum response-cache entries between 0 and 1000000; 0 disables response-cache storage, -1 uses default | BIGINT | GLOBAL | [] |
| duckdb_ai_cache_ttl_seconds | Maximum response-cache entry age in seconds between 0 and 31536000; 0 disables age expiry, -1 uses default | BIGINT | GLOBAL | [] |
| duckdb_ai_completion_model | Default AI model for completion functions | VARCHAR | GLOBAL | [] |
| duckdb_ai_connect_timeout_seconds | AI provider connection timeout in seconds between 1 and 31536000; -1 uses default | BIGINT | GLOBAL | [] |
| duckdb_ai_embedding_model | Default AI model for embedding functions | VARCHAR | GLOBAL | [] |
| duckdb_ai_input_token_price_per_million | Input token price per million tokens for estimated AI usage cost; -1 disables cost estimates | DOUBLE | GLOBAL | [] |
| duckdb_ai_log_endpoint | HTTP endpoint for privacy-minimized AI usage logs | VARCHAR | GLOBAL | [] |
| duckdb_ai_log_format | AI usage log payload format: generic_json or otlp_json | VARCHAR | GLOBAL | [] |
| duckdb_ai_log_include_text | Include prompt and response text in AI usage logs | BOOLEAN | GLOBAL | [] |
| duckdb_ai_log_sample_rate | AI usage log sampling rate between 0 and 1; -1 uses default | DOUBLE | GLOBAL | [] |
| duckdb_ai_log_strict | Fail SQL queries when AI usage log delivery fails | BOOLEAN | GLOBAL | [] |
| duckdb_ai_log_tags | Optional tag string included in AI usage logs | VARCHAR | GLOBAL | [] |
| duckdb_ai_max_concurrent_requests | Maximum concurrent AI provider requests between 0 and 64; 0 disables the limit, -1 uses default | BIGINT | GLOBAL | [] |
| duckdb_ai_min_request_interval_ms | Minimum milliseconds between AI provider request starts between 0 and 60000; -1 uses default | BIGINT | GLOBAL | [] |
| duckdb_ai_model | Default AI model for duckdb_ai | VARCHAR | GLOBAL | [] |
| duckdb_ai_on_error | AI error handling: fail, null, or capture | VARCHAR | GLOBAL | [] |
| duckdb_ai_output_token_price_per_million | Output token price per million tokens for estimated AI usage cost; -1 disables cost estimates | DOUBLE | GLOBAL | [] |
| duckdb_ai_prompt_cache | Enable provider-side prompt caching hints when supported | BOOLEAN | GLOBAL | [] |
| duckdb_ai_provider | Default AI provider for duckdb_ai | VARCHAR | GLOBAL | [] |
| duckdb_ai_response_format | Default AI response format: text, json_object, or json_schema | VARCHAR | GLOBAL | [] |
| duckdb_ai_response_schema | Default AI JSON schema object for structured responses | VARCHAR | GLOBAL | [] |
| duckdb_ai_retry_backoff_ms | AI provider retry backoff in milliseconds between 0 and 60000; -1 uses default | BIGINT | GLOBAL | [] |
| duckdb_ai_retry_count | AI provider retry count between 0 and 10; -1 uses default | BIGINT | GLOBAL | [] |
| duckdb_ai_sql_assistant_model | Default AI model for SQL assistant functions | VARCHAR | GLOBAL | [] |
| duckdb_ai_task_model | Default AI model for text task functions | VARCHAR | GLOBAL | [] |
| duckdb_ai_timeout_seconds | AI provider HTTP timeout in seconds; 0 uses the extension default | BIGINT | GLOBAL | [] |
| duckdb_ai_token_limit_per_minute | Maximum estimated AI provider tokens per rolling minute between 0 and 10000000000; 0 disables the limit, -1 uses default | BIGINT | GLOBAL | [] |
| duckdb_ai_use_builtin_model_prices | Use duckdb_ai built-in model price catalog for estimated AI usage cost | BOOLEAN | GLOBAL | [] |