Connecting MySQL to AI APIs

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

VillageSQL’s vsql_ai extension lets you call AI language models directly from SQL queries. Before you can run your first ai_prompt(), you need the extension installed and an API key ready. This guide covers setup, provider options, and the security considerations that matter before you go to production.

Install the Extension

INSTALL EXTENSION vsql_ai;

Once an API key is set (see below), make a test call:

SET @anthropic_key = 'sk-ant-your-key-here';
SELECT ai_prompt('anthropic', 'claude-haiku-4-5', @anthropic_key, 'Say hello') AS test;

Setting Your API Key

The ai_prompt() and ai_embedding() functions take the API key as a parameter. The recommended pattern is a session variable set at connection time — never embed a key literal in a query that could end up in a log file. For the 'local' provider, pass an empty string — no API key is required.

-- Set once per connection, use everywhere
SET @anthropic_key = 'sk-ant-your-key-here';
SET @openai_key    = 'sk-your-openai-key';
SET @google_key    = 'your-google-ai-key';

-- Then use the variable
SELECT ai_prompt('anthropic', 'claude-haiku-4-5', @anthropic_key, 'Hello') AS response;

What to avoid:

-- Don't do this — key is visible in slow query logs and PROCESSLIST
SELECT ai_prompt('anthropic', 'claude-haiku-4-5', 'sk-ant-12345...', 'Hello');

Set the session variable in your application’s connection setup, not in SQL you write to a file. The SET @key = '...' statement itself is captured verbatim, key included, in performance_schema.events_statements_history — on by default in MySQL 8.4 — as well as any general or slow query log you enable. A session variable keeps the key out of the query that uses it, but not out of the statement that sets it. The only way to avoid this is to never send the key as literal SQL text at all: bind it as a prepared-statement parameter, or use a client-side substitution mechanism that never puts the literal in the SQL text the server parses.

Choosing a Provider and Model

Anthropic

Best for: complex reasoning, long documents, code generation, and instruction-following tasks.

-- Fast and cost-effective
SELECT ai_prompt('anthropic', 'claude-haiku-4-5', @anthropic_key, @prompt);

-- Balanced — speed and intelligence
SELECT ai_prompt('anthropic', 'claude-sonnet-5', @anthropic_key, @prompt);

-- Recommended default — complex agentic and enterprise work
SELECT ai_prompt('anthropic', 'claude-opus-5', @anthropic_key, @prompt);

Claude models:

claude-fable-5 — most capable, for demanding reasoning and long-horizon work
claude-opus-5 — recommended default; complex agentic coding and enterprise work
claude-sonnet-5 — best combination of speed and intelligence
claude-haiku-4-5 — fastest and most cost-effective

Previous generations remain supported: claude-opus-4-8, claude-opus-4-7, claude-opus-4-6, claude-opus-4-5-20251101, claude-sonnet-4-6, claude-sonnet-4-5-20250929. Anthropic does not support ai_embedding().

Google Gemini

Best for: embedding generation and multimodal tasks.

-- Prompting
SELECT ai_prompt('google', 'gemini-2.5-flash', @google_key, @prompt);

-- Embeddings (3072 dimensions)
SELECT ai_embedding('google', 'gemini-embedding-001', @google_key, @text);

OpenAI

Best for: embedding generation with the widely-adopted text-embedding-3 family.

-- Prompting
SELECT ai_prompt('openai', 'gpt-4o-mini', @openai_key, @prompt);

-- Embeddings (1536 dimensions)
SELECT ai_embedding('openai', 'text-embedding-3-small', @openai_key, @text);

-- Embeddings (3072 dimensions — higher accuracy)
SELECT ai_embedding('openai', 'text-embedding-3-large', @openai_key, @text);

Local (Ollama)

Best for: local development and private data that can’t leave your network. Requires Ollama running on 127.0.0.1:11434. No API key needed — pass an empty string.

-- Prompting
SELECT ai_prompt('local', 'llama3.2', '', @prompt);

-- Embeddings
SELECT ai_embedding('local', 'nomic-embed-text', '', @text);

Use any model you’ve pulled in Ollama. Common chat models: llama3.2, mistral, gemma2. Common embedding models: nomic-embed-text, mxbai-embed-large.

Provider comparison

Provider	Prompting	Embeddings	Best for
`'anthropic'`	Yes	No	Reasoning, instructions, code
`'google'`	Yes	Yes	Balanced; Gemini embeddings
`'openai'`	Yes	Yes	OpenAI ecosystem; text-embedding-3
`'local'`	Yes	Yes	Local Ollama; no API key; private data

Timeout and Rate Limit Considerations

AI API calls take 5–30 seconds. MySQL does not limit query duration by default (max_execution_time is 0, unlimited), but if your application or a connection pool sets it, a 30-second AI call can be cut off. For sessions that run AI queries, raise or confirm the limit explicitly:

SET SESSION max_execution_time = 60000;  -- 60 seconds

For batch operations over many rows, this matters — a 1000-row UPDATE calling ai_prompt() on each row can take 30+ minutes. All providers enforce rate limits on how many requests you can make per minute. Hitting a rate limit causes ai_prompt() to return NULL. For bulk operations, process in batches of 50–100 rows and add a pause between batches in your application loop. See Running AI Models from MySQL Queries for batch patterns.

Frequently Asked Questions

Can I store API keys in a MySQL table instead of session variables?

You can, but you’re trading one security risk for another. A table approach requires a SELECT to read the key — that query shows up in logs too. Session variables set programmatically by your application framework are the cleanest option.

What happens if the API call fails?

The function returns NULL. Check for NULL in your results when testing. Run SHOW WARNINGS immediately after the call — vsql_ai raises a Warning 3200 with the actual failure reason (invalid key, unknown provider, unsupported model, etc.), which is far more diagnostic than a bare NULL. Common causes: invalid API key, network timeout, rate limit hit, or an unsupported model name.

Can I use different providers in the same query?

Yes — ai_prompt() is a regular function. You can call it with different provider/model combinations in the same SELECT.

Are model names stable?

Providers change model names over time. The names listed here match the extension’s current tested models. Check your provider’s documentation for the latest available model identifiers.

Troubleshooting

Problem	Solution
`FUNCTION <database>.ai_prompt does not exist`	Run `INSTALL EXTENSION vsql_ai`
`ai_prompt()` returns NULL	Check API key is valid; check `max_execution_time` is set high enough; verify model name
Query times out	Set `SET SESSION max_execution_time = 60000`
Key shows up in slow query log	Use session variable set by application code, not a SQL string literal

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

Install the Extension

Setting Your API Key

Choosing a Provider and Model

Anthropic

Google Gemini

OpenAI

Local (Ollama)

Provider comparison

Timeout and Rate Limit Considerations

Frequently Asked Questions

Can I store API keys in a MySQL table instead of session variables?

What happens if the API call fails?

Can I use different providers in the same query?

Are model names stable?

Troubleshooting

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​Install the Extension

​Setting Your API Key

​Choosing a Provider and Model

​Anthropic

​Google Gemini

​OpenAI

​Local (Ollama)

​Provider comparison

​Timeout and Rate Limit Considerations

​Frequently Asked Questions

​Can I store API keys in a MySQL table instead of session variables?

​What happens if the API call fails?

​Can I use different providers in the same query?

​Are model names stable?

​Troubleshooting

​See also

Install the Extension

Setting Your API Key

Choosing a Provider and Model

Anthropic

Google Gemini

OpenAI

Local (Ollama)

Provider comparison

Timeout and Rate Limit Considerations

Frequently Asked Questions

Can I store API keys in a MySQL table instead of session variables?

What happens if the API call fails?

Can I use different providers in the same query?

Are model names stable?

Troubleshooting

See also