Skip to main content

Edit this page

AI Functions

AI functions call an external model provider from SQL. Today this is ai_embed, which turns text into an embedding vector — the bridge between your text and SereneDB's vector search: embed documents once and store the vectors in an hnsw column to rank by semantic similarity, or pair that with full-text matching for hybrid search.

Providers​

ai_embed talks to any service that exposes an OpenAI-compatible embeddings API. The TYPE openai of the secret names that wire protocol — not a specific vendor — so a single mechanism reaches many providers:

  • OpenAI (hosted) — just supply an api_key, the default endpoint is used:

    CREATE SECRET openai (TYPE openai, api_key '⟨sk-...⟩');

  • Other hosted providers with an OpenAI-compatible endpoint — point base_url at it and supply that provider's api_key. For example Google Gemini through its OpenAI-compatibility layer:

    CREATE SECRET gemini (
        TYPE openai,
        api_key '⟨gemini-key⟩',
        base_url 'https://generativelanguage.googleapis.com/v1beta/openai'
    );

  • Locally-hosted models served by Ollama, vLLM, LM Studio or llama.cpp — point base_url at the local server; an api_key is needed only if the server enforces one.

The runnable examples below use a local Ollama server running the all-minilm model:

Query
CREATE SECRET local_ai (    TYPE openai,    base_url 'http://localhost:11434',    embeddings_path '/v1/embeddings');
Secret parameterDescription
api_keyAPI key for the provider (required by OpenAI; optional for open local endpoints).
base_urlBase URL of an OpenAI-compatible server. Omit for OpenAI itself.
embeddings_pathPath of the embeddings endpoint, if it differs from the default.

ai_embed​

ai_embed(text, model, secret_name) sends text to the embedding model of the provider named by secret_name and returns the embedding as a FLOAT[]. The vector's length is the model's embedding dimension — 384 for all-minilm:

Query
SELECT array_length(ai_embed('space exploration', 'all-minilm', 'local_ai'), 1) AS dims;
Result
 dims------  384

A NULL text returns NULL, so rows without text are simply skipped:

Query
SELECT ai_embed(NULL, 'all-minilm', 'local_ai') IS NULL AS is_null;
Result
 is_null--------- t
ArgumentDescription
textThe text to embed. NULL yields NULL.
modelThe provider's embedding model name, e.g. 'all-minilm' or 'text-embedding-3-small'.
secret_nameName of the openai-type secret holding the endpoint and/or API key.

Returns a variable-length FLOAT[]. To store embeddings in an HNSW vector column — which requires a fixed size — cast to FLOAT[N] with the model's dimension, e.g. ai_embed(...)::FLOAT[384]. Every stored row and the query vector must use the same model and dimension, or the index and the distance comparisons will not line up.

Choosing a model​

The embedding dimension N is fixed by the model. A few common ones:

ModelProviderDimension N
text-embedding-3-smallOpenAI1536
text-embedding-3-largeOpenAI3072
all-minilm (all-MiniLM-L6-v2)Ollama / local384

Check your provider's documentation for the exact dimension and use it as the N in the stored FLOAT[N] column. Match the index's distance metric to how the model's vectors are meant to be compared — most text-embedding models are tuned for cosine similarity.

Performance​

Each ai_embed call is a network request to the provider, so embed documents once at write time and store the vectors; only the query text is embedded at search time. A NULL input short-circuits to NULL without a request. Provider failures (authentication, rate limits, connectivity) surface as a query error.

Embedding a column is just a SELECT — NULLs pass through and are easy to count or filter:

Query
SELECT    count(*) FILTER (WHERE e IS NOT NULL) AS embedded,    count(*) FILTER (WHERE e IS NULL)     AS skippedFROM (    SELECT ai_embed(name, 'all-minilm', 'local_ai') AS e    FROM fruits) t;
Result
 embedded | skipped----------+---------        4 |       1

Embed each row once, store the vector in a fixed-size FLOAT[N] column and build an HNSW index over it:

Query
CREATE TABLE catalog (id INTEGER PRIMARY KEY, name VARCHAR, embedding FLOAT[384]);
INSERT INTO catalogSELECT id, name, ai_embed(name, 'all-minilm', 'local_ai')::FLOAT[384]FROM fruitsWHERE name IS NOT NULL;
CREATE INDEX catalog_idx ON catalog    USING inverted (id, name, embedding hnsw (metric = 'cosine'));

Then embed the query text at search time and rank by vector distance — the embedding model maps semantically related words close together:

SELECT id, name
FROM catalog_idx
ORDER BY embedding <-> ai_embed('tropical fruit', 'all-minilm', 'local_ai')::FLOAT[384]
LIMIT 3;

Because name is also full-text indexed in the same index, you can pair a lexical filter with semantic ranking — see Hybrid Search.

See also​