Bring AI to your SQL queries

Spice gives you two ways to use an LLM with your data. The ai() SQL function sends text to a model and returns the answer as a column. The /v1/nsql API turns a question in plain language into SQL and runs it. Both use the models in your spicepod.yaml file:

1. Add a model: list a chat model under models. The from value names the provider and the model, such as openai:gpt-4o-mini.
2. Add data: list the datasets that the model can use under datasets.
3. Call the model: use ai() or a decision function in a SQL query, or send a question to /v1/nsql.

The following Spicepod adds an OpenAI model and a table of support tickets from PostgreSQL:

version: v1
kind: Spicepod
name: support-insights

models:
  - from: openai:gpt-4o-mini
    name: gpt-4o-mini
    params:
      openai_api_key: ${secrets:OPENAI_API_KEY}

datasets:
  - from: postgres:public.tickets
    name: tickets
    params:
      pg_host: ${secrets:PG_HOST}
      pg_db: support
      pg_user: ${secrets:PG_USER}
      pg_pass: ${secrets:PG_PASS}

Start the runtime with spice run. Then summarize and label each new ticket in one query:

SELECT id,
       ai('Summarize this ticket in one sentence: ' || body) AS summary,
       ai_classify(body, ['billing', 'bug', 'question']) AS category
FROM tickets
WHERE created_at > now() - INTERVAL '1 day';

Or ask a question in plain language:

curl http://localhost:8090/v1/nsql \
  -H 'Content-Type: application/json' \
  -d '{"query": "How many tickets were opened each day last week?"}'

Read the ai() function reference and the /v1/nsql API reference for all options.

To get only the generated SQL from /v1/nsql, send the header Accept: application/sql.

You can call any model that you add to the models section of your Spicepod. The ai() function needs a chat model, and the decision functions take a chat model or a TypeSafe decision model. Set the provider with the prefix of the from value in each model:

To use an OpenAI-compatible API, set the endpoint parameter of an openai model to that API. Read the models guide for the parameters of each provider, and the AI model serving page.

Spice calls the model one time for each row that ai() gets with a prompt that is not NULL. It runs the calls in parallel and returns the answers in row order. Know these limits before you run ai() on a large table:

• Batches: Spice splits the input into batches of up to 1,000 rows, so a query can process any number of rows. A LIMIT stops model calls only between batches, so use a filter to limit the rows that a query sends.
• Message size: one message can be a maximum of 1 MB.
• NULL input: a NULL prompt returns NULL, and Spice does not call the model.
• Errors: if one model call fails, the full query fails. The decision functions accept on_error => 'null' to return NULL for that row instead.

A hosted provider, such as OpenAI, can charge for the tokens of each call. A local Hugging Face or file model uses the compute of the Spice runtime. Test a prompt on a small, filtered set of rows before you run it on a full table.