> ## 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 title="VillageSQL is a drop-in replacement for MySQL with extensions." icon="database" href="/mysql-8.4/0.0.4/quickstart">
  All examples in this guide work on VillageSQL. Install Now →
</Card>

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-20251001', @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-20251001', @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-20251001', 'sk-ant-12345...', 'Hello');
```

Set the session variable in your application's connection setup, not in SQL you write to a file. If your MySQL client logs queries, the variable assignment itself is also logged — use your application layer to set the variable programmatically rather than via a SQL string.

## 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-20251001', @anthropic_key, @prompt);

-- Balanced — recommended for most use cases
SELECT ai_prompt('anthropic', 'claude-sonnet-4-5-20250929', @anthropic_key, @prompt);

-- Maximum capability
SELECT ai_prompt('anthropic', 'claude-opus-4-5-20251101', @anthropic_key, @prompt);
```

**Claude models:**

* `claude-haiku-4-5-20251001` — fast, cost-effective
* `claude-sonnet-4-5-20250929` — balanced capability
* `claude-sonnet-4-6` — latest Sonnet generation
* `claude-opus-4-5-20251101` — maximum capability
* `claude-opus-4-7` — latest Opus generation, enhanced reasoning
* `claude-opus-4-8` — state-of-the-art capability
* `claude-fable-5` — extended thinking with reasoning traces

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's default query timeout may cut them off. For sessions that run AI queries, raise the execution time limit:

```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](/guides/ai-prompts-in-mysql) 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. 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 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](/guides/ai-prompts-in-mysql) — using ai\_prompt() once the extension is configured
* [Generating Vector Embeddings in MySQL](/guides/vector-embeddings) — using ai\_embedding() for semantic search