Running AI Models from MySQL Queries

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

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

MySQL doesn’t have a way to call an AI model. You process your data in SQL, then move it to application code for any AI step, then bring results back. VillageSQL’s vsql_ai extension adds ai_prompt() — a function that calls an AI model directly from a query, so enrichment, classification, and generation can happen where the data already lives.

The Problem: Context Switching Between SQL and AI

A typical pattern without VillageSQL:

# Pull rows from MySQL
rows = db.query("SELECT id, review_text FROM reviews WHERE ai_label IS NULL")

# Call AI for each row
for row in rows:
    label = openai.chat(model="gpt-4o-mini", prompt=row["review_text"])
    db.execute("UPDATE reviews SET ai_label = ? WHERE id = ?", label, row["id"])

This works but it’s three round-trips per row: SELECT, AI call, UPDATE. For large tables the coordination overhead adds up, and the enrichment logic lives in application code rather than in the schema where the data lives.

With VillageSQL: ai_prompt() in SQL

INSTALL EXTENSION vsql_ai;

ai_prompt(provider, model, api_key, prompt) makes an API call and returns the response as a string. Use it anywhere a scalar function works.

SET @key = 'sk-ant-your-api-key';

-- Enrich a single row
SELECT review_text,
       ai_prompt('anthropic', 'claude-haiku-4-5', @key,
                 CONCAT('Classify this review as positive, negative, or neutral: ', review_text)
       ) AS sentiment
FROM reviews
WHERE id = 1;

Updating rows in bulk

The most common use: backfill an enrichment column across existing rows.

ALTER TABLE reviews ADD COLUMN ai_label VARCHAR(50);

SET @key = 'sk-ant-your-api-key';
-- max_execution_time only bounds SELECT statements — it has no effect on
-- this UPDATE. To cap how long it runs, get SELECT CONNECTION_ID() beforehand
-- and run KILL QUERY <id> from another session if it runs too long.

UPDATE reviews
SET ai_label = ai_prompt(
    'anthropic',
    'claude-haiku-4-5',
    @key,
    CONCAT('Classify as positive, negative, or neutral. Reply with one word only: ', review_text)
)
WHERE ai_label IS NULL
LIMIT 100;  -- Process in batches to stay within rate limits

Run the UPDATE in a loop from your application until no more NULL rows remain.

Using prompt results in WHERE

You can filter on AI output, though this calls the model for every row in the scan:

-- Flag reviews that need human review
SELECT id, review_text FROM reviews
WHERE ai_prompt('anthropic', 'claude-haiku-4-5', @key,
    CONCAT('Does this review mention a refund request? Reply yes or no: ', review_text)
) = 'yes'
LIMIT 20;

For performance, prefer updating a stored column and querying that rather than calling ai_prompt() in a WHERE clause.

Building prompts from multiple columns

Concatenate columns into the prompt to give the model full context:

UPDATE support_tickets
SET ai_summary = ai_prompt(
    'anthropic',
    'claude-haiku-4-5',
    @key,
    CONCAT(
        'Summarize this support ticket in one sentence.\n',
        'Subject: ', subject, '\n',
        'Body: ', body
    )
)
WHERE ai_summary IS NULL
LIMIT 50;

Performance and Rate Limits

Each ai_prompt() call is an independent HTTPS request. For bulk operations:

Use LIMIT in UPDATE — AI calls take 5–30 seconds each, so a batch UPDATE over 100 rows takes minutes; process in batches of 50–100 rows to stay within provider rate limits. max_execution_time only bounds SELECT and does nothing here — to cap a running UPDATE, use KILL QUERY <connection_id> from another session.
Store results — Never call ai_prompt() in a SELECT without storing the result. Repeated SELECTs re-run the API call each time.

-- Batch loop pattern (run repeatedly until done)
UPDATE reviews
SET ai_label = ai_prompt('anthropic', 'claude-haiku-4-5', @key,
    CONCAT('Label as positive, negative, or neutral. One word: ', review_text))
WHERE ai_label IS NULL
LIMIT 100;

-- Check progress
SELECT COUNT(*) AS remaining FROM reviews WHERE ai_label IS NULL;

Choosing a Model

Model	Speed	Cost	Use when
`claude-haiku-4-5`	Fast	Low	Bulk classification, simple labeling
`claude-sonnet-5`	Medium	Medium	Complex reasoning, nuanced output
`claude-opus-5`	Slow	High	Recommended default — agentic and enterprise work
`claude-fable-5`	Slow	High	Most capable — demanding reasoning, long-horizon work
`gpt-4o-mini`	Fast	Low	OpenAI ecosystem, simple tasks
`gemini-2.5-flash`	Fast	Low	Google ecosystem, balanced tasks

For classification and labeling tasks, use the fastest/cheapest model that gives acceptable accuracy. Reserve more capable models for tasks where output quality matters more than throughput. For setup and provider configuration, see Connecting MySQL to AI APIs.

Frequently Asked Questions

Can I use ai_prompt() in a DEFAULT expression?

No — non-deterministic functions (which ai_prompt() is) can’t be used in DEFAULT expressions or generated columns in MySQL.

Can I use ai_prompt() in a stored procedure?

Yes. Stored procedures, triggers, and functions can all call ai_prompt(), subject to the same timeout constraints. Be cautious with triggers — an AI call on every INSERT will make every write slow.

How do I control the response format?

Through the prompt. “Reply with one word: positive, negative, or neutral” gets predictable output for classification. “Return valid JSON with keys: category, confidence” gets structured output. The model follows instructions reliably when they’re specific.

Does ai_prompt() retry on failure?

No. If the API call fails, it returns NULL. Run SHOW WARNINGS to see why — vsql_ai raises a Warning 3200 with the real failure reason — before implementing retry logic in your application’s batch loop.

Troubleshooting

Problem	Solution
`FUNCTION <database>.ai_prompt does not exist`	Run `INSTALL EXTENSION vsql_ai`
Returns NULL	Bad API key, rate limit hit, or timeout — check `max_execution_time`
Response stops mid-sentence	Run `SHOW WARNINGS` — a “Prompt response truncated to fit the result buffer” warning means the answer did not fit in the result; ask the model for a shorter answer
UPDATE runs too long	Use smaller `LIMIT` batches; `max_execution_time` does not apply to `UPDATE`, use `KILL QUERY <connection_id>` from another session instead
Model returns unexpected format	Tighten the prompt: specify exact output format and give examples
Rate limit errors	Reduce batch size; add `SELECT SLEEP(1);` between UPDATE batches, or add a sleep in your application loop

Getting Started

Server Development

Using Extensions

Creating Extensions

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

The Problem: Context Switching Between SQL and AI

With VillageSQL: ai_prompt() in SQL

Updating rows in bulk

Using prompt results in WHERE

Building prompts from multiple columns

Performance and Rate Limits

Choosing a Model

Frequently Asked Questions

Can I use ai_prompt() in a DEFAULT expression?

Can I use ai_prompt() in a stored procedure?

How do I control the response format?

Does ai_prompt() retry on failure?

Troubleshooting

See also

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

​The Problem: Context Switching Between SQL and AI

​With VillageSQL: ai_prompt() in SQL

​Updating rows in bulk

​Using prompt results in WHERE

​Building prompts from multiple columns

​Performance and Rate Limits

​Choosing a Model

​Frequently Asked Questions

​Can I use ai_prompt() in a DEFAULT expression?

​Can I use ai_prompt() in a stored procedure?

​How do I control the response format?

​Does ai_prompt() retry on failure?

​Troubleshooting

​See also

The Problem: Context Switching Between SQL and AI

With VillageSQL: ai_prompt() in SQL

Updating rows in bulk

Using prompt results in WHERE

Building prompts from multiple columns

Performance and Rate Limits

Choosing a Model

Frequently Asked Questions

Can I use ai_prompt() in a DEFAULT expression?

Can I use ai_prompt() in a stored procedure?

How do I control the response format?

Does ai_prompt() retry on failure?

Troubleshooting

See also