---
title: "Text-to-SurQL: agentic prompt engineering | SurrealDB University"
description: "Build a text-to-SurrealQL agent whose prompt the database writes itself: live schema from INFO FOR DB, grounded values, few-shot examples by vector search."
url: https://surrealdb.com/learn/ai/text-to-surrealql
---

![Course content preview](https://surrealdb.com/assets/static/course-ai.LEsq_J_G.avif)

Course chapters

[Back to courses](https://surrealdb.com/learn) [SurrealDB for AI Engineers](https://surrealdb.com/learn/ai) [AI foundations](https://surrealdb.com/learn/ai/ai-foundations) [Vector embeddings and search](https://surrealdb.com/learn/ai/vector-embeddings) [Full-text search and BM25](https://surrealdb.com/learn/ai/fulltext-search-bm25) [Building a RAG knowledge base](https://surrealdb.com/learn/ai/rag-knowledge-base) [Hybrid search and reranking](https://surrealdb.com/learn/ai/hybrid-search-reranking) [Building an agent memory store](https://surrealdb.com/learn/ai/agent-memory-store) [Text-to-SurQL: agentic prompt engineering](https://surrealdb.com/learn/ai/text-to-surrealql) [Many sources and agents, one context layer](https://surrealdb.com/learn/ai/multi-source-context-layer) [Chunking strategies](https://surrealdb.com/learn/ai/chunking-strategies) [Evaluating retrieval quality](https://surrealdb.com/learn/ai/evaluating-retrieval) [Graph RAG beyond one hop](https://surrealdb.com/learn/ai/graph-rag-multi-hop) Certificate Pending

# Text-to-SurQL: agentic prompt engineering

**SurrealQL functions used here for the first time** 

- [`encoding::json::decode`](https://surrealdb.com/docs/reference/query-language/functions/database-functions/encoding#encodingjsondecode) - parses a JSON string into an object
- [`object::values`](https://surrealdb.com/docs/reference/query-language/functions/database-functions/object#objectvalues) / `.values()` - an object's values as an array, with the keys dropped

## Why the database should write the prompt

The earlier lessons gave an agent retrieval you designed in advance: [lesson 04](https://surrealdb.com/learn/ai/rag-knowledge-base) fetched documents by meaning, [lesson 05](https://surrealdb.com/learn/ai/hybrid-search-reranking) fused two rankings, [lesson 06](https://surrealdb.com/learn/ai/agent-memory-store) recalled memories. All of those answer questions you may have already had in mind.

Now the harder case: what happens when a user asks *"which category earns us the most money"* that nobody wrote a retriever for? Answering it means the agent has to **write a query**, and that is a prompt-engineering problem before it's a database problem. It's also the distinction from [lesson 01](https://surrealdb.com/learn/ai/ai-foundations) in a sharper form: the prompt's *wording* you write once, but what goes *into* it has to be assembled fresh every time, and here the database assembles it.

Most text-to-SQL bugs come from the same place: **the prompt and the database disagree.** Someone pasted a schema into a system prompt a few months ago, a field was later renamed, and the model has been confidently generating queries against a database that no longer exists.

You can generate the prompt from the live schema instead. `INFO FOR DB` and `INFO FOR TABLE` return a running database's own `DEFINE` statements, which is the schema exactly as the engine holds it and what text-to-SQL work usually calls the DDL. `COMMENT` clauses let you write notes *for the model* directly into those statements, and `DEFINE FUNCTION` lets you assemble the finished prompt server-side. Then the prompt is a **query you run**, not a document you have to keep up to date. Rename a field and the next prompt says so.

There's a second reason SurrealQL is a good generation target. Compare the same question in both languages, *total revenue per product category*:

```sql
-- PostgreSQL: illustrative, not run here
SELECT p.category, SUM(oi.qty * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products    p  ON p.id = oi.product_id
WHERE o.status = 'shipped'
GROUP BY p.category
ORDER BY revenue DESC;
```

```surql
-- SurrealQL: the edge table already is the join
SELECT 
    out.category AS category, 
    math::sum(qty * unit_price) AS revenue
FROM contains
WHERE in.status = 'shipped'
GROUP BY category
ORDER BY revenue DESC;
```

Two joins disappear, because in a graph model the relationship *is* a table you can select from. Fewer joins means fewer places for a model to invent a wrong key, and join errors are the single largest category of text-to-SQL mistakes.

## Step 1 - start a server

As in the earlier lessons the embeddings are tiny and hand-written, with no embedding model or API key involved. **No LLM is needed to follow this lesson either:** the database builds the prompt and it is shown here in full, and the queries a model returns for it are run by hand. Swapping in a real model is a `fetch` call, covered at the end.

```bash
surreal start --user root --pass secret
```

As in [lesson 02](https://surrealdb.com/learn/ai/vector-embeddings), the database resets when you stop the process.

## Step 2 - write the schema for two audiences

A schema is normally written for the engine and for your colleagues. Here it has a third reader, the model, and `COMMENT` is where you write for it:

```surql
DEFINE TABLE OVERWRITE customer SCHEMAFULL
    COMMENT "A person who buys from the store. Their orders: customer->placed->purchase.";

DEFINE FIELD OVERWRITE country ON customer TYPE "DE" | "ES" | "GB" | "US"
    COMMENT "ISO-3166 alpha-2 country code.";
DEFINE FIELD OVERWRITE tier    ON customer TYPE "free" | "plus" | "pro"
    COMMENT "Subscription tier, where 'pro' is the highest.";
```

Those comments are doing real work. `'pro' is the highest` is the kind of domain knowledge that lives in a colleague's head and nowhere in the schema itself. Without it, "our best customers" is a coin flip. Notice what the comments no longer have to say, though. A literal union type already lists every value the field accepts, so the comment is left carrying only the part the type can't express, and there is no second copy of the list to drift out of step with the first.

The relationships get the same treatment, spelling out the traversal in the syntax the model must produce:

```surql
DEFINE TABLE OVERWRITE placed TYPE RELATION IN customer OUT purchase SCHEMAFULL
    COMMENT "Links a customer (in) to an order they placed (out). Traverse: customer->placed->purchase.";

DEFINE TABLE OVERWRITE contains TYPE RELATION IN purchase OUT product SCHEMAFULL
    COMMENT "One line of an order: links a purchase (in) to a product (out). Traverse: purchase->contains->product.";

DEFINE FIELD OVERWRITE qty        ON contains TYPE int
    COMMENT "Units of this product on the order.";
DEFINE FIELD OVERWRITE unit_price ON contains TYPE decimal
    COMMENT "Price per unit actually charged, in EUR. Line revenue is qty * unit_price.";
```

### A comment is only a string

`COMMENT` takes an ordinary string, so the usual limits do not apply. Non-ASCII text and emoji survive storage and come back out of `INFO` byte for byte:

```surql
DEFINE TABLE OVERWRITE produkt SCHEMAFULL
    COMMENT "Produktkatalog. Preise in €. Bezeichnungen auf Deutsch: Größe, Übersicht.";

DEFINE FIELD OVERWRITE naam ON produkt TYPE string
    COMMENT "商品名。日本語と中文の両方が使えます。";

DEFINE FIELD OVERWRITE status ON produkt TYPE string
    COMMENT "Fulfilment state. ✅ shipped, ⏳ pending, ❌ cancelled.";
```

For a model that is genuinely useful, since it means the schema can be annotated in whatever language your team actually writes, and a set of status values can carry the glyph an operator would recognise on a dashboard.

Nothing stops the string being structured either. A comment holding JSON is still just a comment to SurrealDB, but `encoding::json::decode` will turn it back into an object on the way out:

```surql
DEFINE FIELD OVERWRITE price ON produkt TYPE decimal
    COMMENT '{"unit":"EUR","precision":2,"note":"list price, not the price charged"}';

LET $ddl  = (INFO FOR TABLE produkt).fields.price;
LET $json = $ddl.replace(/^.*COMMENT '/, "").replace(/' PERMISSIONS.*$/, "");
RETURN encoding::json::decode($json).unit;
```

Output

```surql
'EUR'
```

That opens the door to one schema annotation serving two readers: prose for the model, machine-readable fields for a form generator or a validation layer that walks the same DDL. It comes with a cost, though. Every character of a comment is a character in the prompt, and JSON spends a good share of them on braces and quotes that the model has to read past. For the prompt itself, plain prose usually does the most efficient job.

`Line revenue is qty * unit_price` is a business rule. If you put it in the schema, every prompt inherits it. As in every lesson, save the finished schema as `schema.surql` and start the file with `OPTION IMPORT` so that `surreal import` will accept it.

![Schema diagram](https://surrealdb.com/assets/static/schema.full.D3XutI-8.avif)

Note the shape: the domain is one traversable chain, whereas `query_example`, the agent's own few-shot memory - a stock of worked question-and-query pairs for the prompt to include, filled in Step 5 - sits apart from it, connected to nothing. That separation is deliberate, and Step 4 enforces it.

The file loads with:

Bash

PowerShell

```bash
surreal import --endpoint http://localhost:8000 \
  --user root --pass secret --ns ai --db store schema.surql
```

## Step 3 - seed the store

Nine orders across six customers are connected with `RELATE`:

```surql
CREATE purchase:o1 SET placed_at = d"2025-04-02T10:15:00Z", status = "shipped";
RELATE customer:ana->placed->purchase:o1;
RELATE purchase:o1 ->contains->product:headphones SET qty = 1, unit_price = 249.00dec;
RELATE purchase:o1 ->contains->product:lamp       SET qty = 2, unit_price = 74.00dec;
```

A dataset this small pays for itself while you are tuning a prompt, because the agent's arithmetic can then be verified by hand, which is the only way to catch a query that runs cleanly and returns the wrong number.

**The complete `seed.surql` - six customers, eight products, nine orders and six verified examples** 

```surql
OPTION IMPORT;

-- ============================================================
--  A small online store: six customers, eight products,
--  nine orders. Small enough to check the agent's answers by
--  hand, which is exactly what you want while tuning a prompt.
-- ============================================================

CREATE customer:ana   SET name = "Ana Ferrer",    country = "ES", tier = "pro",  joined = d"2024-02-11T09:00:00Z";
CREATE customer:ben   SET name = "Ben Whitfield", country = "GB", tier = "plus", joined = d"2024-05-03T09:00:00Z";
CREATE customer:carla SET name = "Carla Ruiz",    country = "ES", tier = "free", joined = d"2025-01-20T09:00:00Z";
CREATE customer:dirk  SET name = "Dirk Hoffmann", country = "DE", tier = "pro",  joined = d"2023-11-08T09:00:00Z";
CREATE customer:elena SET name = "Elena Vogt",    country = "DE", tier = "plus", joined = d"2025-03-14T09:00:00Z";
CREATE customer:frank SET name = "Frank Osei",    country = "US", tier = "pro",  joined = d"2024-08-27T09:00:00Z";

CREATE product:headphones SET name = "Studio Headphones", category = "audio",     price = 249.00dec;
CREATE product:earbuds    SET name = "Commute Earbuds",   category = "audio",     price = 89.00dec;
CREATE product:speaker    SET name = "Shelf Speaker",     category = "audio",     price = 179.00dec;
CREATE product:watch      SET name = "Field Watch",       category = "wearables", price = 329.00dec;
CREATE product:tracker    SET name = "Sleep Tracker",     category = "wearables", price = 119.00dec;
CREATE product:ring       SET name = "Focus Ring",        category = "wearables", price = 199.00dec;
CREATE product:lamp       SET name = "Desk Lamp",         category = "home",      price = 74.00dec;
CREATE product:kettle     SET name = "Pour-over Kettle",  category = "home",      price = 96.00dec;

-- ------------------------------------------------------------
--  Orders. Each purchase is linked to its buyer with `placed`
--  and to its lines with `contains`.
-- ------------------------------------------------------------

CREATE purchase:o1 SET placed_at = d"2025-04-02T10:15:00Z", status = "shipped";
RELATE customer:ana->placed->purchase:o1;
RELATE purchase:o1 ->contains->product:headphones SET qty = 1, unit_price = 249.00dec;
RELATE purchase:o1 ->contains->product:lamp       SET qty = 2, unit_price = 74.00dec;

CREATE purchase:o2 SET placed_at = d"2025-04-11T16:40:00Z", status = "shipped";
RELATE customer:dirk->placed->purchase:o2;
RELATE purchase:o2  ->contains->product:watch   SET qty = 1, unit_price = 329.00dec;
RELATE purchase:o2  ->contains->product:tracker SET qty = 1, unit_price = 119.00dec;

CREATE purchase:o3 SET placed_at = d"2025-04-18T08:05:00Z", status = "shipped";
RELATE customer:ben->placed->purchase:o3;
RELATE purchase:o3 ->contains->product:earbuds SET qty = 3, unit_price = 79.00dec;

CREATE purchase:o4 SET placed_at = d"2025-05-06T12:30:00Z", status = "cancelled";
RELATE customer:carla->placed->purchase:o4;
RELATE purchase:o4   ->contains->product:speaker SET qty = 1, unit_price = 179.00dec;

CREATE purchase:o5 SET placed_at = d"2025-05-14T19:20:00Z", status = "shipped";
RELATE customer:frank->placed->purchase:o5;
RELATE purchase:o5   ->contains->product:ring   SET qty = 2, unit_price = 199.00dec;
RELATE purchase:o5   ->contains->product:kettle SET qty = 1, unit_price = 96.00dec;

CREATE purchase:o6 SET placed_at = d"2025-05-29T07:45:00Z", status = "shipped";
RELATE customer:dirk->placed->purchase:o6;
RELATE purchase:o6  ->contains->product:speaker SET qty = 2, unit_price = 169.00dec;

CREATE purchase:o7 SET placed_at = d"2025-06-08T14:00:00Z", status = "pending";
RELATE customer:elena->placed->purchase:o7;
RELATE purchase:o7   ->contains->product:headphones SET qty = 1, unit_price = 249.00dec;

CREATE purchase:o8 SET placed_at = d"2025-06-17T11:10:00Z", status = "shipped";
RELATE customer:ana->placed->purchase:o8;
RELATE purchase:o8 ->contains->product:kettle  SET qty = 1, unit_price = 96.00dec;
RELATE purchase:o8 ->contains->product:earbuds SET qty = 1, unit_price = 89.00dec;

CREATE purchase:o9 SET placed_at = d"2025-06-25T09:55:00Z", status = "shipped";
RELATE customer:frank->placed->purchase:o9;
RELATE purchase:o9   ->contains->product:watch SET qty = 1, unit_price = 309.00dec;

-- ============================================================
--  Few-shot pool.
--
--  As in the earlier lessons the embeddings are hand-written
--  and 4-dimensional so you can read them. Treat the axes as
--  what the question is *about*:
--
--      [ money, people, catalogue, time ]
--
--  Swap in a real embedding model and only DIMENSION changes.
-- ============================================================

CREATE query_example:ex_revenue_by_category SET
    question  = "What is the total revenue per product category?",
    surql     = "SELECT out.category AS category, math::sum(qty * unit_price) AS revenue FROM contains WHERE in.status = 'shipped' GROUP BY category ORDER BY revenue DESC LIMIT 50;",
    embedding = [0.95, 0.0, 0.35, 0.0];

CREATE query_example:ex_top_customers SET
    question  = "Who are our highest-spending customers?",
    surql     = "SELECT in<-placed<-customer[0].name AS customer, math::sum(qty * unit_price) AS spend FROM contains WHERE in.status = 'shipped' GROUP BY customer ORDER BY spend DESC LIMIT 50;",
    embedding = [0.75, 0.7, 0.0, 0.0];

CREATE query_example:ex_products_by_tier SET
    question  = "Which products has each pro-tier customer bought?",
    surql     = "SELECT name, array::distinct(->placed->purchase->contains->product.name) AS products FROM customer WHERE tier = 'pro' LIMIT 50;",
    embedding = [0.0, 0.7, 0.75, 0.0];

CREATE query_example:ex_orders_in_range SET
    question  = "How many orders were placed in May 2025?",
    surql     = "SELECT count() AS orders FROM purchase WHERE placed_at >= d'2025-05-01T00:00:00Z' AND placed_at < d'2025-06-01T00:00:00Z' GROUP ALL LIMIT 50;",
    embedding = [0.15, 0.0, 0.0, 0.98];

CREATE query_example:ex_units_per_product SET
    question  = "How many units of each product have we shipped?",
    surql     = "SELECT out.name AS product, math::sum(qty) AS units FROM contains WHERE in.status = 'shipped' GROUP BY product ORDER BY units DESC LIMIT 50;",
    embedding = [0.2, 0.0, 0.95, 0.1];

CREATE query_example:ex_customers_by_country SET
    question  = "List customers in Spain and their tier.",
    surql     = "SELECT name, tier FROM customer WHERE country = 'ES' LIMIT 50;",
    embedding = [0.0, 0.98, 0.0, 0.0];
```

Bash

PowerShell

```bash
surreal import --endpoint http://localhost:8000 \
  --user root --pass secret --ns ai --db store seed.surql
```

Then open a shell for the demos:

Bash

PowerShell

```bash
surreal sql --endpoint ws://localhost:8000 \
  --user root --pass secret --ns ai --db store --pretty < queries.surql
```

## Step 4 - the schema block, generated from the schema

Here's the core of it. `INFO FOR DB` lists every table's definition; `INFO FOR TABLE` lists its fields. A function walks both and renders the prompt's schema block:

```surql
-- The tables the agent is allowed to see. Anything absent here
-- is invisible to the model.
DEFINE FUNCTION OVERWRITE fn::exposed_tables() -> array<string> {
    RETURN ["customer", "placed", "purchase", "contains", "product"];
};

-- Renders the exposed tables' live DEFINE statements as the prompt's SCHEMA block.
DEFINE FUNCTION OVERWRITE fn::schema_context() -> string {
    LET $tables = fn::exposed_tables();
    LET $defs   = (INFO FOR DB).tables;

    RETURN $tables.map(|$t| {
        LET $fields = (INFO FOR TABLE $t).fields.values();
        RETURN [$defs[$t]].concat($fields)
            .map(|$d| $d.replace(" PERMISSIONS FULL", "")
                       .replace(" PERMISSIONS NONE", "") + ";")
            .join("\n");
    }).join("\n\n");
};
```

A few details:

- **`INFO FOR TABLE $t` takes a plain string**, so you can loop over table names. (`INFO FOR TABLE type::table($t)` does *not* work: it wants a string, not a table value.)
- **The allowlist is a function, not a wildcard.** `query_example` holds the agent's own few-shot pool; showing it to the model invites questions about the questions. Listing tables explicitly is also your access-control surface for the *prompt*, distinct from the one the server enforces in Step 8.
- **`PERMISSIONS` clauses are stripped.** They're enforced by the engine no matter what the prompt says, so including them just spends tokens and gives the model something irrelevant to reason about.

```surql
RETURN fn::schema_context();
```

```surql
DEFINE TABLE customer TYPE NORMAL SCHEMAFULL COMMENT 'A person who buys from the store. Their orders: customer->placed->purchase.';
DEFINE FIELD country ON customer TYPE 'DE' | 'ES' | 'GB' | 'US' COMMENT 'ISO-3166 alpha-2 country code.';
DEFINE FIELD joined ON customer TYPE datetime COMMENT 'When the account was created.';
DEFINE FIELD name ON customer TYPE string COMMENT 'Full name of the customer.';
DEFINE FIELD tier ON customer TYPE 'free' | 'plus' | 'pro' COMMENT "Subscription tier, where 'pro' is the highest.";

DEFINE TABLE placed TYPE RELATION IN customer OUT purchase SCHEMAFULL COMMENT 'Links a customer (in) to an order they placed (out). Traverse: customer->placed->purchase.';
DEFINE FIELD in ON placed TYPE record<customer>;
DEFINE FIELD out ON placed TYPE record<purchase>;

DEFINE TABLE purchase TYPE NORMAL SCHEMAFULL COMMENT 'One completed or pending order. Buyer: purchase<-placed<-customer. Lines: purchase->contains->product.';
DEFINE FIELD placed_at ON purchase TYPE datetime COMMENT 'When the order was submitted.';
DEFINE FIELD status ON purchase TYPE 'cancelled' | 'pending' | 'shipped' COMMENT "Fulfilment state. Revenue counts 'shipped' only.";

DEFINE TABLE contains TYPE RELATION IN purchase OUT product SCHEMAFULL COMMENT 'One line of an order: links a purchase (in) to a product (out). Traverse: purchase->contains->product.';
DEFINE FIELD in ON contains TYPE record<purchase>;
DEFINE FIELD out ON contains TYPE record<product>;
DEFINE FIELD qty ON contains TYPE int COMMENT 'Units of this product on the order.';
DEFINE FIELD unit_price ON contains TYPE decimal COMMENT 'Price per unit actually charged, in EUR. Line revenue is qty * unit_price.';

DEFINE TABLE product TYPE NORMAL SCHEMAFULL COMMENT 'An item in the catalogue. Orders containing it: product<-contains<-purchase.';
DEFINE FIELD category ON product TYPE 'audio' | 'home' | 'wearables' COMMENT 'Catalogue section.';
DEFINE FIELD name ON product TYPE string COMMENT 'Display name of the product.';
DEFINE FIELD price ON product TYPE decimal COMMENT 'Current list price in EUR. Historic prices live on the contains edge.';
```

That's a better schema description than most hand-written ones, and nobody had to maintain it. Look at what the engine added for free: `DEFINE FIELD in ON placed TYPE record<customer>` was never in our schema file; SurrealDB derives it from `TYPE RELATION IN customer OUT purchase`. The model is told what sits at each end of every edge, which is the information a JOIN-generating model normally has to guess.

The literal union types shape the token count too. `TYPE 'free' | 'plus' | 'pro'` states the complete set of tiers in the same line that says the field exists, and does it in 12 tokens against the 16 that `TYPE string ASSERT $value INSIDE ['free', 'plus', 'pro']` costs. On a prompt regenerated for every request, the schema block is the largest fixed cost in it, so the saving compounds.

### Grounding the values

Schema tells the model a field is a string. It doesn't tell it that the string is `'DE'` and not `'Germany'`. Low-cardinality fields are where models hallucinate most, so read the real values out of the data:

```surql
-- Lists the values actually present in the low-cardinality fields, so the model
-- cannot invent one.
DEFINE FUNCTION OVERWRITE fn::value_hints() -> string {
    LET $countries = (SELECT VALUE country  FROM customer GROUP BY country).sort();
    LET $tiers     = (SELECT VALUE tier     FROM customer GROUP BY tier).sort();
    LET $cats      = (SELECT VALUE category FROM product  GROUP BY category).sort();
    LET $statuses  = (SELECT VALUE status   FROM purchase GROUP BY status).sort();

    RETURN "customer.country: " + $countries.join(", ")
        + "\ncustomer.tier: "   + $tiers.join(", ")
        + "\nproduct.category: " + $cats.join(", ")
        + "\npurchase.status: "  + $statuses.join(", ");
};
```

```surql
RETURN fn::value_hints();
```

```text
customer.country: DE, ES, GB, US
customer.tier: free, plus, pro
product.category: audio, home, wearables
purchase.status: cancelled, pending, shipped
```

That pairing does the deduplicating. `SELECT VALUE country` returns a plain array of country values rather than an array of objects, and `GROUP BY country` collapses the repeats, so six customers across four countries come back as four strings. Note these are the values **actually present in the data**, not the values the type permits: if you've defined a status nobody has used yet, the model doesn't need to hear about it. This is for genuinely low-cardinality fields only, of course; `SELECT VALUE name FROM customer` over a million customers is not a prompt, it's a denial-of-service on your context window.

## Step 5 - dynamic few-shot examples, retrieved by meaning

A fixed set of examples in a system prompt has the same staleness problem as a fixed schema, plus a budget problem.

The six examples below cover this store's obvious questions: revenue by category, highest-spending customers, what pro-tier customers bought, orders in a date range, units shipped per product, and customers by country. If you pin all six into a system prompt, every request pays for all six. Ask *"which category earns us the most money?"* and three of them are dead weight, because the pattern for listing customers in Spain teaches nothing about summing revenue. A real store has fifty of these rather than six, and the proportion going to waste grows with the pool.

So they go in a table instead, and the relevant ones get retrieved, which is just [lesson 02](https://surrealdb.com/learn/ai/vector-embeddings)'s vector search pointed at a new target:

```surql
DEFINE TABLE OVERWRITE query_example SCHEMAFULL
    COMMENT "Verified natural-language question paired with the SurrealQL that answers it.";

DEFINE FIELD OVERWRITE question  ON query_example TYPE string;
DEFINE FIELD OVERWRITE surql     ON query_example TYPE string;
DEFINE FIELD OVERWRITE embedding ON query_example TYPE array<float, 4>;

DEFINE INDEX OVERWRITE query_example_vec ON query_example
    FIELDS embedding
    HNSW DIMENSION 4 DIST COSINE;
```

The embeddings are 4-dimensional and hand-written, as in the earlier lessons. Treat the axes as *what the question is about*: **`[ money, people, catalogue, time ]`**:

```surql
CREATE query_example:ex_revenue_by_category SET
    question  = "What is the total revenue per product category?",
    surql     = "SELECT out.category AS category, math::sum(qty * unit_price) AS revenue FROM contains WHERE in.status = 'shipped' GROUP BY category ORDER BY revenue DESC LIMIT 50;",
    embedding = [0.95, 0.0, 0.35, 0.0];

CREATE query_example:ex_customers_by_country SET
    question  = "List customers in Spain and their tier.",
    surql     = "SELECT name, tier FROM customer WHERE country = 'ES' LIMIT 50;",
    embedding = [0.0, 0.98, 0.0, 0.0];
-- ...and four more (see seed.surql)
```

Retrieval is a KNN query:

```surql
-- The few-shot examples closest to this question, by vector search over the
-- example store.
DEFINE FUNCTION OVERWRITE fn::similar_examples($qvec: array<float>) -> array<object> {
    RETURN SELECT question, surql, vector::distance::knn() AS dist
        FROM query_example
        WHERE embedding <|3, 40|> $qvec
        ORDER BY dist ASC;
};
```

> **`K` must be a literal.** `<|3, 40|>` works, while `<|$k, 40|>` is a parse error even inside a function, so the number of examples is fixed in the function body.

Ask a money question and the money examples come back:

```surql
LET $question = "Which category earns us the most money?";
LET $qvec = [0.92, 0.0, 0.4, 0.0];
RETURN fn::similar_examples($qvec);
```

Output

```surql
[
    { dist: 0.0016, question: 'What is the total revenue per product category?', surql: '...' },
    { dist: 0.3296, question: 'Who are our highest-spending customers?',         surql: '...' },
    { dist: 0.4239, question: 'How many units of each product have we shipped?', surql: '...' }
]
```

The three examples the model gets are the three that most resemble the question it's being asked: a retrieval problem solved with retrieval, in the same database that holds the data.

A second function renders those records as the prompt's examples block, with each question as a SurrealQL comment above the query that answers it:

```surql
-- Renders those examples as the prompt's EXAMPLES block, each question above
-- the query that answers it.
DEFINE FUNCTION OVERWRITE fn::example_context($qvec: array<float>) -> string {
    RETURN fn::similar_examples($qvec)
        .map(|$e| "-- " + $e.question + "\n" + $e.surql)
        .join("\n\n");
};
```

## Step 6 - assemble the prompt

The pieces stack into a single string. Every variable part is a function call, so the prompt is rebuilt on every request:

```surql
-- Assembles the whole prompt - rules, schema, values, examples - on every request.
DEFINE FUNCTION OVERWRITE fn::nl2surql_prompt($question: string, $qvec: array<float>) -> string {
    RETURN "You translate questions into SurrealQL for SurrealDB 3.x.\n"
        + "\n"
        + "Rules:\n"
        + "1. Reply with one SELECT statement and nothing else. No prose, no code fences.\n"
        + "2. Use only the tables and fields in the schema below.\n"
        + "3. Follow relationships with graph arrows (a->edge->b, b<-edge<-a), never a JOIN.\n"
        + "4. Use the literal field values listed under VALUES; do not invent your own.\n"
        + "5. Always end with a LIMIT of 50 or less.\n"
        + "\n"
        + "SCHEMA\n" + fn::schema_context() + "\n"
        + "\n"
        + "VALUES\n" + fn::value_hints() + "\n"
        + "\n"
        + "EXAMPLES\n" + fn::example_context($qvec) + "\n"
        + "\n"
        + "QUESTION\n" + $question + "\n"
        + "SURQL\n";
};
```

Each rule is there for a failure you'll see in Step 7. Rule 1 stops the model wrapping output in a code fence you then have to strip. Rule 3 is the single highest-value line in the prompt: it's used to break models away from their habits based on SQL training data. Rule 5 is a cost ceiling that survives even if your application forgets to impose one.

```surql
RETURN fn::nl2surql_prompt($question, $qvec);
```

The tail of the output (the schema and values blocks are exactly as shown above):

```text
...
EXAMPLES
-- What is the total revenue per product category?
SELECT out.category AS category, math::sum(qty * unit_price) AS revenue FROM contains WHERE in.status = 'shipped' GROUP BY category ORDER BY revenue DESC LIMIT 50;

-- Who are our highest-spending customers?
SELECT in<-placed<-customer[0].name AS customer, math::sum(qty * unit_price) AS spend FROM contains WHERE in.status = 'shipped' GROUP BY customer ORDER BY spend DESC LIMIT 50;

-- How many units of each product have we shipped?
SELECT out.name AS product, math::sum(qty) AS units FROM contains WHERE in.status = 'shipped' GROUP BY product ORDER BY units DESC LIMIT 50;

QUESTION
Which category earns us the most money?
SURQL
```

One database call produced a complete, current, question-specific prompt. Your application's job shrinks to: send this string to a model, run what comes back.

And what comes back for this one runs:

```surql
SELECT out.category AS category, math::sum(qty * unit_price) AS revenue
FROM contains
WHERE in.status = 'shipped'
GROUP BY category
ORDER BY revenue DESC
LIMIT 50;
```

Output

```surql
[
    { category: 'wearables', revenue: '1155' },
    { category: 'audio',     revenue: '913' },
    { category: 'home',      revenue: '340' }
]
```

It checks out by hand against `seed.surql`, which is what the small dataset is for. Wearables: 329 + 119 + (2 × 199) + 309 = 1155. The cancelled order and the pending one are correctly excluded, because the schema comment told the model that revenue counts `'shipped'` only.

> Revenue comes back quoted (`'1155'`) because the field is a `decimal`, which JSON has no native type for. That's a serialisation detail of the transport, not a string in the database: arithmetic and `ORDER BY` work on it as a number.

## Step 7 - the three ways this goes wrong

Generated queries fail in three ways, and **only one of them is loud**. Most write-ups skip the other two.

### Loud: a syntax error

The model reverts to its SQL training and emits a JOIN:

```surql
SELECT c.name FROM customer c JOIN purchase p ON p.customer = c.id;
```

```text
 --< Parse error: Unexpected token `an identifier`, expected Eof
 --> [1:29]
  |
1 | SELECT c.name FROM customer c JOIN purchase p ON p.customer = c.id;
  |                             ^
```

This is a relatively good failure. The parser points at the exact column, and that message is a high-quality repair signal: feed it back to the model with the original prompt and a line like *"That query failed. Fix it and reply with the corrected SurrealQL only."* One retry resolves the large majority of syntax errors, because the error names the problem precisely. A cap of two or three attempts, and a surfaced failure after that, keeps the loop from running forever.

> That statement needs running on its own. A parse error rejects the **entire batch**, since SurrealDB parses everything before executing anything, so a single malformed statement piped in with others stops all of them. Generated queries are best sent one at a time.

### Silent: a hallucinated field

```surql
SELECT name, lifetime_value FROM customer LIMIT 50;
```

Output

```surql
[
    { lifetime_value: NULL, name: 'Ana Ferrer' },
    { lifetime_value: NULL, name: 'Ben Whitfield' },
    { lifetime_value: NULL, name: 'Carla Ruiz' },
    ...
]
```

No error. `SCHEMAFULL` constrains what you can **write**, not what you can **select**: a field that doesn't exist reads as `NULL`. The query "succeeds", the agent summarises the result, and your user is told their customers have no lifetime value.

### Silent: a hallucinated value

```surql
SELECT name, tier FROM customer WHERE country = 'Germany' LIMIT 50;
```

Output

```surql
[]
```

That produces no error either. An empty result is indistinguishable from *"there genuinely are no German customers"*, and an agent will happily report exactly that. With the grounded value from `fn::value_hints()`:

```surql
SELECT name, tier FROM customer WHERE country = 'DE' LIMIT 50;
```

Output

```surql
[
    { name: 'Dirk Hoffmann', tier: 'pro' },
    { name: 'Elena Vogt',    tier: 'plus' }
]
```

You cannot catch the silent ones downstream: there is no error to catch, and a wrong-but-plausible answer is worse than a crash. The defence is upstream, by putting the schema and the real values in the prompt. A retry can repair a query that failed to parse, but only grounding stops the model inventing a field or a value in the first place.

## Step 8 - guardrails the model cannot talk its way past

Prompt rules are requests. The next three are not.

**A read-only user.** Rule 1 asks for a `SELECT`, and a `VIEWER` role is what enforces it:

```surql
DEFINE USER OVERWRITE agent ON DATABASE PASSWORD "agent-secret"
    ROLES VIEWER
    DURATION FOR TOKEN 1h, FOR SESSION 12h;
```

Bash

PowerShell

```bash
surreal sql --endpoint ws://localhost:8000 --auth-level database \
  --user agent --pass agent-secret --ns ai --db store --pretty
```

> Two flags matter here. `--auth-level database` is required: it defaults to `root`, and without it a database user gets `There was a problem with authentication`. And `DURATION FOR SESSION` needs setting explicitly, because the default of `NONE` will also fail to authenticate.

Reads work as before. Writes do not:

```surql
DEFINE FIELD hax ON customer TYPE string;
```

```text
'IAM error: Not enough permissions to perform this action'
```

A `DELETE customer;` from this session leaves all six records intact, but it returns an empty result `[]` rather than an IAM error. **What tells you a write was blocked is the role, not the error channel.** Regex-filtering the model's output for the word `DELETE` looks like the same protection and isn't: a pattern you didn't think to write lets the statement through, whereas the role refuses every statement it was not granted.

**Dry-run with `EXPLAIN`.** It parses and plans a query without reading any records, so it validates syntax for free and tells you what the query would cost:

```surql
EXPLAIN SELECT name FROM customer WHERE country = 'DE' LIMIT 50;
```

```text
SelectProject [ctx: Db] [projections: name]
    TableScan [ctx: Db] [table: customer, direction: Forward, predicate: country = 'DE', limit: 50, pre_decode_filter: yes]
```

`TableScan`: there's no index on `country`, so this generation reads the whole table. On six customers that's free; on six million, not so much. Checking the plan before executing is what lets you reject or re-route expensive generations, and it's a guardrail no prompt rule can give you.

**A bound on execution.** `TIMEOUT` is a hard stop the model cannot override:

```surql
SELECT count() FROM purchase GROUP ALL TIMEOUT 2s;
```

Output

```surql
[{ count: 9 }]
```

Together: the role bounds what a generated query can *do*, `EXPLAIN` bounds what you let it *cost*, and `TIMEOUT` bounds how long it *runs*. All three are enforced by the engine, so none of them depend on the model having read the prompt.

## Step 9 - close the loop

The few-shot pool doesn't have to stay as you seeded it. A query the user accepted is, by definition, a verified example, so the last piece is a function that writes one back into `query_example`. It takes the question, the SurrealQL that answered it and the question's vector, and returns the record it created:

```surql
-- Stores an accepted question and its query as a new few-shot example.
DEFINE FUNCTION OVERWRITE fn::remember_example(
    $question: string, $surql: string, $qvec: array<float>
) -> object {
    RETURN CREATE ONLY query_example SET
        question  = $question,
        surql     = $surql,
        embedding = $qvec;
};
```

Calling it closes the loop on the question this lesson has been carrying since Step 5. `$question` and `$qvec` are still the ones set there, and `$accepted` is the query the model produced in Step 6 and that we checked by hand:

```surql
LET $accepted = "SELECT out.category AS category, math::sum(qty * unit_price) AS revenue FROM contains WHERE in.status = 'shipped' GROUP BY category ORDER BY revenue DESC LIMIT 50;";

RETURN count(SELECT id FROM query_example);                        -- the pool before
RETURN fn::remember_example($question, $accepted, $qvec).question; -- what got written
RETURN count(SELECT id FROM query_example);                        -- the pool after
```

```text
6
'Which category earns us the most money?'
7
```

Reading the question back off the returned record is just proof the write landed. The pool has gone from the six you seeded to seven, and the next question phrased like this one retrieves a closer example than it would have a minute ago. The write goes through a privileged connection, not the agent's `VIEWER` session; accepting an example is your application's decision, not the model's.

That gate should be something real: an explicit thumbs-up, or a human review queue. A pool that accepts whatever ran without erroring will happily learn the silent failures from Step 7 and start teaching them to the model.

## From hand-written vectors to production

Four changes:

1. \1.

   **Real embeddings.** Pick a model, note its dimensionality, and change `DIMENSION 4` on `query_example_vec` to match. Embed each stored `question` at write time and the incoming question at query time, with the same model. Nothing else in the schema moves.
2. \2.

   **Call an actual LLM.** Your application does `RETURN fn::nl2surql_prompt($question, $qvec)`, posts the string to a model, and runs the reply on the `agent` connection. That's the entire integration: three steps, one of which is a database call you've already written.
3. \3.

   **Cache the schema block.** `fn::schema_context()` hits `INFO` on every call. Schemas change rarely, so cache the rendered string in your application and invalidate on deploy, or precompute it into a table with a scheduled job. Keep `fn::value_hints()` live if your enums actually change; cache it too if they don't.
4. \4.

   **Log every generation.** Question, prompt, generated SurrealQL, whether it parsed, whether the user accepted it. That log is how you find out which questions your prompt handles badly, and it's the raw material for the next round of few-shot examples.

The same idea works on other schemas. Any database whose structure you can query can describe itself to a model, hold the examples, run the retrieval that selects them, and assemble the prompt around them.

## Where this goes next

In this chapter we made a text-to-SurrealQL agent whose prompt is generated by the database it queries, grounded in live values, few-shot-primed by vector search, and fenced in by roles rather than regexes. It also assumes one database holding everything worth asking about. [Lesson 08](https://surrealdb.com/learn/ai/multi-source-context-layer) is the architecture lesson for the case where it doesn't, and where more than one agent is asking.

At a glance

**You are on**

Chapter 7 of 11

**Chapters**

11

**Format**

Text, with queries you can run

**Runs in**

SurrealDB Studio, in the browser

**Cost**

Free

**Certificate**

On completion

[Next: Many sources and agents, one context layer](https://surrealdb.com/learn/ai/multi-source-context-layer)

## Continue

### [Building an agent memory store](https://surrealdb.com/learn/ai/agent-memory-store)

Previous

### [Many sources and agents, one context layer](https://surrealdb.com/learn/ai/multi-source-context-layer)

Next lesson

THE PLATFORM

## Everything an application and its agents know. Five surfaces, one engine.

Database

Document, graph, vector, time-series and relational in one engine.

![Five data models as dotted tiles: documents, graph, vector, time-series and relational](https://surrealdb.com/assets/static/platform-database.DUdumYDz.avif)

Read more

[Database](https://surrealdb.com/surrealdb)

Agent Memory

What an agent learns, with its source and its time, in the same engine.

![A timeline of remembered facts, each with its source](https://surrealdb.com/assets/static/platform-agent-memory.B4RNjvbX.avif)

Read more

[Agent Memory](https://surrealdb.com/agent-memory)

Cloud

Managed clusters in the regions you choose, scaled on demand.

![Clusters in three regions on a world map, each running or scaling](https://surrealdb.com/assets/static/platform-cloud.--QZnaVi.avif)

Read more

[Cloud](https://surrealdb.com/cloud)

Studio

Query, explore and design the schema from the browser.

![A SurrealQL query in Studio and the schema graph under it](https://surrealdb.com/assets/static/platform-studio.7ykBFNLq.avif)

Read more

[Studio](https://surrealdb.com/studio)

MCP

Every model that speaks MCP reaches the database and the memory directly.

![Three models connected through MCP to the database and Agent Memory](https://surrealdb.com/assets/static/platform-mcp.D_oH0_wm.avif)

Read more

[MCP](https://surrealdb.com/mcp)

IN PRODUCTION

## Trusted at scale. Samsung, Nvidia, Verizon, Tencent, and Walmart run on SurrealDB.

14,000+

Developers building on SurrealDB Cloud

4M+

Developers building on SurrealDB worldwide

FROM THE TEAMS

> SurrealDB gives us a foundation where we can unify semantic search, knowledge graphs, and AI-driven decision making without stitching together multiple systems. Collapsing responsibility into SurrealDB has become our default engineering posture.

*Justin Foley*

VP of Engineering, Later

```json
{"@context":"https://schema.org","@type":"Course","name":"SurrealDB for AI Engineers","description":"An eleven-lesson course from calling an LLM API to an agent that retrieves by meaning, remembers what it learned, writes its own queries, and can prove its retrieval works - all against one database.","url":"https://surrealdb.com/learn/ai","inLanguage":"en","isAccessibleForFree":false,"provider":{"@type":"Organization","name":"SurrealDB","url":"https://surrealdb.com"},"hasPart":[{"@type":"LearningResource","name":"SurrealDB for AI Engineers","url":"https://surrealdb.com/learn/ai"},{"@type":"LearningResource","name":"AI foundations","url":"https://surrealdb.com/learn/ai/ai-foundations"},{"@type":"LearningResource","name":"Vector embeddings and search","url":"https://surrealdb.com/learn/ai/vector-embeddings"},{"@type":"LearningResource","name":"Full-text search and BM25","url":"https://surrealdb.com/learn/ai/fulltext-search-bm25"},{"@type":"LearningResource","name":"Building a RAG knowledge base","url":"https://surrealdb.com/learn/ai/rag-knowledge-base"},{"@type":"LearningResource","name":"Hybrid search and reranking","url":"https://surrealdb.com/learn/ai/hybrid-search-reranking"},{"@type":"LearningResource","name":"Building an agent memory store","url":"https://surrealdb.com/learn/ai/agent-memory-store"},{"@type":"LearningResource","name":"Text-to-SurQL: agentic prompt engineering","url":"https://surrealdb.com/learn/ai/text-to-surrealql"},{"@type":"LearningResource","name":"Many sources and agents, one context layer","url":"https://surrealdb.com/learn/ai/multi-source-context-layer"},{"@type":"LearningResource","name":"Chunking strategies","url":"https://surrealdb.com/learn/ai/chunking-strategies"},{"@type":"LearningResource","name":"Evaluating retrieval quality","url":"https://surrealdb.com/learn/ai/evaluating-retrieval"},{"@type":"LearningResource","name":"Graph RAG beyond one hop","url":"https://surrealdb.com/learn/ai/graph-rag-multi-hop"}]}
```

```json
{"@context":"https://schema.org","@type":"LearningResource","name":"Text-to-SurQL: agentic prompt engineering","description":"Build a text-to-SurrealQL agent whose prompt the database writes itself: live schema from INFO FOR DB, grounded values, few-shot examples by vector search.","url":"https://surrealdb.com/learn/ai/text-to-surrealql","learningResourceType":"lesson","isPartOf":{"@type":"Course","name":"SurrealDB for AI Engineers","url":"https://surrealdb.com/learn/ai"},"position":8}
```

```json
{"@context":"https://schema.org","@type":"Organization","@id":"https://surrealdb.com/#organization","name":"SurrealDB","url":"https://surrealdb.com","logo":"https://surrealdb.com/assets/static/logo.BG7_TG2b.svg","description":"SurrealDB is the context and memory layer for AI agents. A multi-model database for documents, graphs, vectors, and time-series.","foundingDate":"2022","legalName":"SurrealDB Ltd","identifier":{"@type":"PropertyValue","propertyID":"GB-COH","value":"13615201"},"address":{"@type":"PostalAddress","streetAddress":"3rd Floor, 1 Ashley Road","addressLocality":"Altrincham","addressRegion":"Cheshire","postalCode":"WA14 2DT","addressCountry":"GB"},"contactPoint":[{"@type":"ContactPoint","contactType":"customer support","email":"support@surrealdb.com","url":"https://surrealdb.com/contact","availableLanguage":"English"},{"@type":"ContactPoint","contactType":"sales","email":"info@surrealdb.com","url":"https://surrealdb.com/contact","availableLanguage":"English"},{"@type":"ContactPoint","contactType":"security","email":"security@surrealdb.com","url":"https://surrealdb.com/.well-known/security.txt","availableLanguage":"English"},{"@type":"ContactPoint","contactType":"legal","email":"legal@surrealdb.com","url":"https://surrealdb.com/legal","availableLanguage":"English"}],"hasCertification":[{"@type":"Certification","name":"SOC 2 Type 2"},{"@type":"Certification","name":"GDPR"},{"@type":"Certification","name":"Cyber Essentials Plus"},{"@type":"Certification","name":"ISO 27001"}],"owns":[{"@type":"SoftwareApplication","name":"SurrealDB","url":"https://surrealdb.com/surrealdb"},{"@type":"SoftwareApplication","name":"Agent Memory","url":"https://surrealdb.com/agent-memory"}],"knowsAbout":["multi-model databases","document databases","graph databases","vector search","time-series databases","SurrealQL","Agent Memory","real-time databases","embedded databases","context layer","graph ontology","distributed database","knowledge graphs","distributed transaction protocols","highly-scalable databases"],"sameAs":["https://www.wikidata.org/wiki/Q124316308","https://github.com/surrealdb/surrealdb","https://twitter.com/surrealdb","https://www.youtube.com/@surrealdb","https://www.linkedin.com/company/surrealdb","https://discord.gg/surrealdb","https://www.reddit.com/r/surrealdb","https://www.instagram.com/surrealdb","https://medium.com/surrealdb","https://dev.to/surrealdb"]}
```

```json
{"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://surrealdb.com"},{"@type":"ListItem","position":2,"name":"Learn","item":"https://surrealdb.com/learn"},{"@type":"ListItem","position":3,"name":"Text to surrealql","item":"https://surrealdb.com/learn/ai/text-to-surrealql"}]}
```