> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Connecting MySQL to AI APIs

> How to connect MySQL to AI APIs using VillageSQL's vsql_ai extension — installing the extension, configuring API keys, and choosing a provider and model.

> [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md)
>
> 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

```sql theme={null}
INSTALL EXTENSION vsql_ai;
```

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

```sql theme={null}
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.

```sql theme={null}
-- 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:**

```sql theme={null}
-- 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.

```sql theme={null}
-- 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.

```sql theme={null}
-- 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.

```sql theme={null}
-- 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](https://ollama.com) running on `127.0.0.1:11434`. No API key needed — pass an empty string.

```sql theme={null}
-- 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:

```sql theme={null}
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](https://villagesql.com/docs/en/guides/ai-prompts-in-mysql.md) 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 |

## See also

* [Running AI Models from MySQL Queries](https://villagesql.com/docs/en/guides/ai-prompts-in-mysql.md) — using ai\_prompt() once the extension is configured
* [Generating Vector Embeddings in MySQL](https://villagesql.com/docs/en/guides/vector-embeddings.md) — using ai\_embedding() for semantic search


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.