duckdb-ai: LLMs and embeddings in DuckDB SQL

Call large language models from SQL. Summarize, classify and filter rows, extract typed fields, embed text for semantic search, and ask questions about your tables, using local models (Ollama, llama.cpp, any OpenAI-compatible server) or hosted providers (OpenAI, Anthropic Claude, Google Gemini, OpenRouter, Databricks, Snowflake Cortex and more than 30 others).

Documentation · SQL reference · Provider setup · Cookbooks · Agent guide

SELECT ticket_id,
       ai_classify(body, ['billing', 'performance', 'other']) AS category,
       ai_summarize(body) AS summary
FROM support_tickets;

Contents

• Names at a glance
• Quickstart with a local model
• Connect a hosted provider
• Choose a function
• Examples, from simple to advanced
• Using the extension from an agent
• Supported providers
• Privacy, security and limits
• Build from source
• Documentation

Names at a glance

What	Name
Repository and project	duckdb-ai
Extension (INSTALL / LOAD)	ai
SQL functions	ai_*
Settings	duckdb_ai_* (for example SET duckdb_ai_provider = 'ollama')
Secret type	TYPE duckdb_ai

The extension is published in the DuckDB community extension catalog and works anywhere DuckDB can load community extensions: the CLI, Python, and other clients.

Note

This README describes the source on main. The community package can lag behind it. Check your installed version and function list (see step 1 below) before relying on a newer function, and see the changelog for what changed when.

Quickstart with a local model

This path needs no API key and sends no data off your machine.

1. Install and check the installed API

INSTALL ai FROM community;
LOAD ai;

-- Installed version
SELECT extension_version
FROM duckdb_extensions()
WHERE extension_name = 'ai';

-- Available functions (makes no model calls)
SELECT function_name, function_type
FROM duckdb_functions()
WHERE starts_with(function_name, 'ai_')
ORDER BY function_name;

LOAD ai; is needed once per connection. Loading the extension never calls a model by itself.

main and releases from 0.9.0 target DuckDB 2.0. The 0.8.x releases are the DuckDB 1.5.6 line.

2. Start Ollama and pull a model

Install Ollama, then in a terminal:

ollama serve                    # skip if Ollama is already running
ollama pull qwen3.8:27b         # chat model
ollama pull nomic-embed-text    # embedding model (optional)

3. Call it from SQL

LOAD ai;

SET duckdb_ai_provider = 'ollama';
SET duckdb_ai_model = 'qwen3.8:27b';
SET duckdb_ai_embedding_model = 'nomic-embed-text';

SELECT ai_complete('Describe DuckDB in one sentence.');

Without a model setting, provider := 'ollama' uses llama3.2, so pull that model or set one as shown. For ai_decide, also run ollama pull nimble (Ollama 0.35 or later).

The extension calls Ollama at http://localhost:11434. Set OLLAMA_HOST, or pass base_url := ..., if it runs elsewhere. For llama.cpp, vLLM, LM Studio or LiteLLM, see local and self-hosted setup.

Connect a hosted provider

Keep API keys out of SQL. Put the key in the DuckDB process environment (or in a secret manager that injects it), then create a named secret that holds only the provider and model:

export OPENAI_API_KEY='...'

LOAD ai;

-- If you ran the Ollama quickstart in this session, clear its settings first:
-- session settings outrank the provider and model stored in a secret.
RESET duckdb_ai_provider;
RESET duckdb_ai_model;

CREATE OR REPLACE SECRET openai_ai (
    TYPE duckdb_ai,
    AI_PROVIDER 'openai',
    MODEL 'gpt-5.6-luna'
);

SELECT ai_complete(
    'Describe DuckDB in one sentence.',
    secret := 'openai_ai',
    max_tokens := 64
);

Every provider follows the same pattern: an environment variable for the key, a secret or session setting for provider and model. The provider guides list the variable name, base URL and model IDs for each one. Hosted calls send your input to that provider and may incur charges.

How settings combine

For each call, the extension picks the provider and model from the first place that sets them:

1. Named options on the call: provider := ..., model := ...
2. A CREATE EXTERNAL MODEL profile passed with profile := ...
3. Session settings: the family setting (duckdb_ai_completion_model, duckdb_ai_task_model, duckdb_ai_embedding_model, duckdb_ai_sql_assistant_model), then duckdb_ai_provider and duckdb_ai_model
4. The AI_PROVIDER and MODEL stored in a TYPE duckdb_ai secret
5. Provider environment variables, such as OPENAI_MODEL, then the provider default

A secret always supplies the API key, but its model only applies when nothing above sets one. ai_decide is the exception: it ignores duckdb_ai_model and uses a decision model (see typed decisions).

Completion and embedding models are configured separately, so set an embedding model before using ai_embed or ai_similarity. The full resolution order is in provider settings and secrets.

Choose a function

Task	Functions	Returns
Prompt a model	ai_complete, ai_try_complete	text; STRUCT(response, error)
Summarize, translate, fix grammar	ai_summarize, ai_translate, ai_fix_grammar	text
Classify	ai_classify, ai_classify_labels, ai_sentiment	one label; a list of labels; sentiment
Filter or score	ai_filter, ai_score	BOOLEAN; DOUBLE from 0 to 1
Extract structured data	ai_extract_record, ai_complete_json, ai_complete_record	typed STRUCT per row; validated JSON; a typed table
Redact personal data	ai_redact	text with direct identifiers masked
Embed and rank	ai_embed, ai_similarity, ai_rerank	DOUBLE[]; cosine similarity; LLM relevance score
Summarize groups	ai_agg, ai_summarize_agg (aggregates)	text per group
Prepare documents for RAG	ai_generate_chunks, ai_prep_search, ai_parse_document	chunk tables
Text-to-SQL	ai_sql, ai_query_data, ai_explain_sql, ai_fix_sql	SQL text; query results
Typed decisions with confidence	ai_decide (decision models: Jev, Clef, Perplexity, Ollama)	STRUCT of choices, scores and probabilities
Raw provider APIs	ai_provider_call	full provider JSON (tools, reasoning, streaming events)
Preview without calling a model	ai_completion_request_json, ai_embedding_request_json, ai_count_tokens	request JSON; approximate token count
Usage, cost and caches	ai_usage(), ai_usage_summary(), ai_usage_totals(), ai_query_cache_stats()	tables

The SQL reference documents every function, its named options (timeouts, retries, concurrency, caching, JSON Schemas, cost tracking, logging) and its result shape.

Examples, from simple to advanced

The examples assume the quickstart settings and this small table:

CREATE TABLE support_tickets AS
SELECT * FROM (VALUES
    (1, 'I was charged twice for the same invoice.'),
    (2, 'My query became slow after importing more data.'),
    (3, 'Please update the billing email on our account.')
) AS t(ticket_id, body);

Enrich rows

Each function runs once per row:

SELECT ticket_id,
       ai_classify(body, ['billing', 'performance', 'other']) AS category,
       ai_filter(body, 'the customer reports a bug or outage') AS is_incident,
       ai_translate(body, 'Italian') AS body_it
FROM support_tickets;

Typed decisions

ai_decide sends a row to a decision model and asks several fixed-choice questions in one request. Each answer comes back as a typed field with a confidence, so you choose the threshold:

-- Needs a decision model: Ollama 0.35 or later and `ollama pull nimble`,
-- or TypeSafe, Cloudflare, Perplexity or a systemone endpoint.
SELECT ticket_id, d.team, d.team_confidence, d.urgent
FROM (
    SELECT ticket_id,
           ai_decide(body, {
               team: MAP {'billing': 'Payments, invoices and refunds',
                          'performance': 'Slow queries or imports',
                          'other': 'Anything else'},
               urgent: MAP {'true': 'Blocks the customer now', 'false': 'Can wait'}
           }, provider := 'ollama') AS d
    FROM support_tickets
);

team is a VARCHAR label with team_confidence, and urgent is the probability of true. See ai_decide and the decision cookbook.

Extract typed fields

ai_extract_record returns a STRUCT whose fields come from a JSON Schema, so results can be filtered and joined like any other column:

SELECT ticket_id, r.product_area, r.urgency
FROM (
    SELECT ticket_id,
           ai_extract_record(body, '{
             "type": "object",
             "properties": {
               "product_area": {"type": "string"},
               "urgency": {"type": "integer"}
             },
             "required": ["product_area", "urgency"]
           }') AS r
    FROM support_tickets
);

Semantic search with embeddings

Embed once, store the vectors, and rank with DuckDB's own list functions:

CREATE TABLE ticket_vectors AS
SELECT ticket_id, body, ai_embed(body) AS embedding
FROM support_tickets;

-- Embed the search text once, not once per row
SET VARIABLE query_embedding = ai_embed('wrong charge on my bill');

SELECT ticket_id, body,
       list_cosine_similarity(embedding, getvariable('query_embedding')) AS score
FROM ticket_vectors
ORDER BY score DESC
LIMIT 5;

For larger corpora, see Lance-backed semantic search and the chunking helpers ai_generate_chunks and ai_prep_search.

Summarize groups

SELECT ai_classify(body, ['billing', 'performance', 'other']) AS category,
       ai_summarize_agg(body) AS themes
FROM support_tickets
GROUP BY ALL;

Ask questions about your tables

ai_sql returns the generated SQL for review. ai_query_data generates it and runs it. Both reject anything other than a single read-only SELECT:

SELECT ai_sql('how many tickets mention billing?', include_tables := ['support_tickets']);

SELECT *
FROM ai_query_data(
    'count tickets by first word of the body',
    include_tables := ['support_tickets'],
    fix_attempts := 2   -- feed bind errors back to the model up to twice
);

include_tables limits which tables are described to the model. Add sample_rows := N only when it is acceptable to send sample data to the provider.

Run large batch jobs

For production runs, keep failures per row instead of failing the query, bound the request rate, and save results before you read them:

CREATE TABLE ticket_summaries AS
SELECT ticket_id,
       ai_try_complete(
           'Summarize this support ticket in one sentence: ' || body,
           max_tokens := 128,
           retry_count := 3,
           max_concurrent_requests := 8
       ) AS result
FROM (SELECT * FROM support_tickets ORDER BY ticket_id LIMIT 1000);

-- Successes and failures, read from the saved table without new model calls
SELECT ticket_id, result.response FROM ticket_summaries WHERE result.error IS NULL;
SELECT ticket_id, result.error    FROM ticket_summaries WHERE result.error IS NOT NULL;

-- Calls, tokens, retries, cache hits and estimated cost
SELECT * FROM ai_usage_summary();

Related cookbooks: chaining several AI steps, production batch enrichment, resumable enrichment, enriching Postgres or MySQL rows, audited lakehouse output, and usage and cost monitoring.

Use provider-native features

request_options passes provider-specific fields (reasoning effort, thinking, top_p) to the plain completion functions. ai_provider_call sends a complete native request body and returns the raw response, including tool calls and reasoning output. The extension never executes tools; your code runs them and sends the results back.

SELECT ai_complete('Explain the proof.', provider := 'deepseek', model := 'deepseek-flash',
                   request_options := '{"reasoning_effort":"high"}');

See native provider JSON and native APIs for reasoning providers for what each provider supports.

Using the extension from an agent

Agents write most of the SQL that uses this extension. These rules avoid the common mistakes. The agent guide covers each one in more detail.

Before making a model call

• Check what is installed (duckdb_extensions(), duckdb_functions()). Do not assume a function from this README exists in the user's version.

• Preview the request offline. This needs no key and makes no network call:

  SELECT ai_completion_request_json('Reply OK', provider := 'openai',
                                    model := 'gpt-5.6-luna', max_tokens := 64);

  A correct preview proves the request shape, not authentication or model availability.

• Never put API keys in SQL, prompts, request_options or files. Use environment variables or TYPE duckdb_ai secrets, and pass secret := 'name' explicitly so the configuration in use is clear.

When writing queries

• Every model function call is one or more model requests per row. Limit the input in a subquery (FROM (SELECT ... LIMIT 100)) before scaling up. An outer OFFSET can still evaluate model calls for the skipped rows.
• Model functions are VOLATILE. Running the same query twice calls the model twice. Save results with CREATE TABLE ... AS before reading several fields or splitting successes from failures. cache := true enables an opt-in in-memory response cache.
• Table functions (ai_complete_record, ai_query_data, ai_usage, ai_schema_prompt) go in FROM. ai_extract_record and ai_decide are scalars that return a STRUCT, and their schema arguments must be constants.
• Use ai_try_complete or on_error := 'null' so that one bad row does not abort a batch.
• Set a bounded max_tokens on live checks. Reasoning models can spend the whole budget before writing visible text; use their reasoning controls instead of raising the cap repeatedly.

After the call

• Inspect ai_usage() for status, latency, token counts and errors. A missing-key or out-of-credit error is not a successful verification.
• Treat generated SQL as untrusted. Read-only validation is not a sandbox; rely on DuckDB permissions for real access control.

Supported providers

Model IDs are passed through to the provider unchanged. The provider matrix lists exact provider names, aliases, credentials, endpoints, default models and embedding support.

Family	Providers
Local and self-hosted	Ollama, llama.cpp, any OpenAI-compatible server (vLLM, LM Studio, LiteLLM), OpenAI Privacy Filter
Hosted models	OpenAI, Anthropic Claude, Google Gemini, Mistral, DeepSeek, xAI, Cohere, Perplexity, Groq, Cerebras, Fireworks AI, Together AI, DeepInfra, Hugging Face, NVIDIA NIM, Nebius, SambaNova, SiliconFlow
Cloud platforms and gateways	Azure OpenAI, Amazon Bedrock, Google Vertex AI, Cloudflare Workers AI, Databricks, Snowflake Cortex, OpenRouter, Vercel AI Gateway, Poe
Additional model platforms	Alibaba DashScope (Qwen), Moonshot (Kimi), MiniMax, Z.ai (GLM), Tencent Hunyuan, Baidu Qianfan (ERNIE), StepFun, Volcengine (Doubao), Xiaomi MiMo
Decision models, through ai_decide	TypeSafe Jev, Cloudflare Clef, Perplexity Decider, Ollama (Nimble, Tev1), any /v1/systemone endpoint

Provider capabilities differ: not every provider offers embeddings, JSON Schema enforcement, or every native API. Tests use local mocks of each provider's wire format; they do not prove that every provider and model combination supports every feature.

Privacy, security and limits

• Where data goes. Model functions send their inputs to the configured endpoint. With a local model server and outbound logging off, inference stays on your machine. ai_redact also sends the original text to its provider, so use a local endpoint when the raw text must not leave the machine.
• Credentials come only from environment variables or DuckDB secrets. ai_secrets() lists configured secrets with credentials redacted.
• Egress control. allowed_hosts (or SET duckdb_ai_allowed_hosts) restricts which hosts the extension may contact. The DUCKDB_AI_ALLOWED_HOSTS environment variable is a ceiling sessions cannot widen, and stored API keys only go to the origin they are configured for. SET enable_external_access = false blocks provider requests except to endpoints in allowed_directories or allowed_paths.
• Shared servers. To serve AI functions to remote DuckDB clients over Quack, see Run on a Quack server.
• Usage logs stay in memory unless you configure an external collector, and they omit prompt and response text by default. Cost figures are estimates, not provider billing.
• Retries and caching are off unless you turn them on.
• Generated SQL is checked to be one read-only SELECT. Review it and use DuckDB access controls before running it.
• Out of scope. The extension does not generate images, audio or video, and it does not run a tool-calling agent loop.

Read security and data flow, runtime behavior and best practices for timeouts, rate limits, concurrency, caching, logging and egress allowlists. Report vulnerabilities through the security policy.

Build from source

You need a C++ toolchain, CMake, Ninja and libcurl development headers.

git clone --recurse-submodules https://github.com/leonardovida/duckdb-ai.git
cd duckdb-ai
GEN=ninja make release
GEN=ninja make test
GEN=ninja make smoke
./build/release/duckdb

The tests use deterministic fixtures and local HTTP mocks, so they need no API keys. Contributors and coding agents working on the source should read AGENTS.md and CONTRIBUTING.md.

Documentation

• Agent guide: discover the installed API, configure providers, preview requests and verify calls.
• SQL reference: signatures, named options, examples and result shapes.
• Provider guides: setup for every local, hosted and gateway provider.
• Cookbooks: batch enrichment from Parquet or S3, Postgres and MySQL inputs, typed records, document intake, embeddings, semantic search with Lance, typed decisions, chained steps, evaluation and text-to-SQL.
• Best practices: choosing functions, controlling cost and running large jobs.
• Runtime behavior: caching, concurrency, retries, cancellation, token limits and context sizes.
• Security and data flow: what each function sends to a provider and how to restrict it.
• Run on a Quack server: serve AI functions to remote DuckDB clients with credentials kept on the server.
• Changelog and releases.

Licensed under the MIT license.