Build a Product Recommendation Engine in Pure SQL

If you are building AI-powered applications, you are probably juggling multiple systems: your primary database, a separate vector store, maybe a caching layer in between. What if you could ditch that complexity?

With native VectorAI support, Actian Zen V17 AI lets you manage embeddings, perform similarity searches, and handle vector operations directly in SQL. No context switching, no external API calls, no data syncing between systems, Just SQL you already know, plus one new function.

The Problem

A customer on your e-commerce site opens the product page for a “Gaming Laptop Pro.” You want to show a “Customers also viewed” rail underneath it — powered by semantic similarity, not just “same category.”

The usual way to build that means standing up a separate vector database, writing a sync job to keep it current with your product table, and adding a network hop between your app and two data stores instead of one.

Actian Zen V17 AI folds the vector store into the SQL engine itself. Embeddings, similarity search, and CRUD operations on vector “collections” all run as ordinary SQL statements against the same connection you already use for the products table. This walkthrough builds the recommendation feature above, end to end, in about 20 minutes.

WHAT YOU’LL NEED: Actian Zen V17 AI, an ODBC connection (pyodbc works fine), and Python with sentence-transformers installed for generating embeddings locally, no OpenAI key required for this walkthrough.

Architecture Overview

Before the first query, here’s what’s actually happening underneath dbo.vector_collection_search(): your application talks to Zen V17’s SQL engine exactly as it always has, and the SQL engine talks to a new VDE (Vector Data Engine) layer on your behalf.

Actian Zen V17 AI VectorAI architecture,  the SQL engine bridges relational data with VDE vector search.

Every request, a search or a write makes the same round trip:

Data flow from application embedding generation through the SQL engine to VDE and back.

The credential handshake is worth calling out on its own, since it’s the one step that’s easy to get wrong the first time:

VDE credential flow, the token is stored as a session variable and filtered from all logs and audit trails.

STEP 1 – Connect and Authenticate

Connect over ODBC using the Zen driver. Some instances also require a VDE (Vector Data Engine) session credential before any vector_* call  set it once per connection if yours does.

import pyodbc




conn = pyodbc.connect(

"Driver={Pervasive ODBC Interface};serverName=localhost;DBQ=Demodata;"

)

cur = conn.cursor()




# Only needed if your instance requires it — if this raises

# "Invalid SET statement", your instance authenticates via the

# ODBC connection itself and you can skip this line.

# cur.execute("SET VDE_CREDENTIAL = 'vdai_your_token_here'")

WHY IT MATTERS: On instances that do use it, the token lives in a session variable instead of the query text, so it never shows up in the plan cache, slow-query log, or audit trail.STEP 2  Create a Vector Collection

A collection is where your product embeddings live. Pick a dimension that matches your embedding model and a distance metric that fits the data, cosine is the standard choice for text embeddings.

-- 384 dimensions matches the sentence-transformers model we'll use below

dbo.vector_collection_create ( 'product_embeddings',

'{

"dimension" : 384,

"distance_metric" : "cosine"

}' );

NOTE: Dimension is fixed at creation time. If you switch embedding models later, drop and recreate the collection — there’s no in-place resize.

STEP 3 – Generate and Load Product Embeddings

Here’s the catalog we’re indexing, five products, each with a name and description that gets embedded:

Encode each description with a local embedding model, then load it into the collection:

from sentence_transformers import SentenceTransformer

import json




model = SentenceTransformer('all-MiniLM-L6-v2')  # 384-dim, runs locally




products = [

(1, 'Gaming Laptop Pro', '32GB RAM, RTX graphics, 165Hz display'),

(2, 'Business Ultrabook', 'Lightweight, all-day battery, fingerprint reader'),

(3, 'Mechanical Keyboard', 'RGB backlit, hot-swappable switches'),

(4, '4K Monitor 27"', 'HDR, USB-C, height-adjustable stand'),

(5, 'Wireless Gaming Mouse', '16000 DPI, programmable buttons'),

]




# Batch-encode all descriptions in one model call…

texts = [f"{name}. {desc}" for _, name, desc in products]

vectors = model.encode(texts).tolist()




# …then load them all in a single dbo.vector_point_upsert_batch call.

ids = [pid for pid, _, _ in products]

payloads = [json.dumps({'name': name}) for _, name, _ in products]




# Escape single quotes in the assembled JSON — real product names/

# descriptions can contain apostrophes, which would otherwise break

# out of the SQL string literal below.

ids_json = json.dumps(ids).replace("'", "''")

vectors_json = json.dumps(vectors).replace("'", "''")

payloads_json = json.dumps(payloads).replace("'", "''")




cur.execute(f"""

dbo.vector_point_upsert_batch (

'product_embeddings',

'{ids_json}',

null,

'{vectors_json}',

'{payloads_json}',

{len(ids)},

384

)

""")

conn.commit()

NOTE: There’s also a dbo.vector_point_upsert_batch function that packs several vectors into one call.

STEP 4 – Run the Similarity Search

Now the payoff: a customer views the Gaming Laptop Pro. Embed the query the same way you embedded the catalog, then search:

query_vector = model.encode("Gaming Laptop Pro. 32GB RAM, RTX graphics, 165Hz display").tolist()




cur.execute(f"""

SELECT TOP (3)

a.u64_id, a."SIMILARITY_SCORE", a.payload

FROM dbo.vector_collection_search (

'product_embeddings', 'vector',

'{json.dumps(query_vector)}', 3, 384

) a

WHERE a.u64_id != 1                  -- exclude the product being viewed

ORDER BY a."SIMILARITY_SCORE" DESC

""")




for row in cur.fetchall():

# Positional indexing — unquoted identifiers get case-folded

# by the engine, so row.<name> attribute access isn't reliable.

print(row[0], row[1], row[2])   # u64_id, SIMILARITY_SCORE, payload

Output on this catalog (measured against a real all-MiniLM-L6-v2 run — your exact numbers may vary slightly by model version, but the ranking holds):

The gaming mouse tops the list, with the monitor and keyboard close behind — the model is picking up shared vocabulary around specs and electronics, not a hardcoded “gaming” category. Notice the scores themselves are modest: real cosine similarity on short technical phrases from a small model like this one typically lands well under 1.0 even for genuinely related items — don’t expect near-perfect scores outside of near-duplicate text.

NOTE: If you’re used to embedding demos that show 80–90% similarity for related items, that’s usually with longer, more semantically rich text. Short spec-sheet phrases like these compress less distinctively, so scores in the 0.2–0.5 range for genuinely related items are normal here.

STEP 5 – Add Business Rules with a JOIN

A raw similarity ranking isn’t a shippable recommendation yet, you still need to respect stock levels and price. Because results come back as a normal result set, you JOIN it against your products table like any other query:

cur.execute(f"""

SELECT TOP (3)

p.product_id, p.name, p.price, p.in_stock,

sr."SIMILARITY_SCORE"

FROM dbo.vector_collection_search (

'product_embeddings', 'vector',

'{json.dumps(query_vector)}', 10, 384

) sr

INNER JOIN products p ON CAST(sr.u64_id AS INTEGER) = p.product_id

WHERE sr."SIMILARITY_SCORE" > 0.15

AND p.in_stock = 1

AND p.product_id != 1

ORDER BY sr."SIMILARITY_SCORE" DESC

""")

Notice the search pulls 10 candidates instead of 3 — leave headroom before the relational filters run, so an out-of-stock item doesn’t shrink your final rail to two products. On this catalog the 4K Monitor actually has the second-highest raw similarity score (0.37, ahead of the keyboard’s 0.25), but it’s out of stock, so the JOIN correctly drops it and the keyboard and ultrabook take its place. That’s the entire point of layering SQL filters on top of the vector search: the ranking a model produces and the ranking you can actually ship are two different things.

STEP 6 – Pick a Similarity Threshold

The threshold above (0.15) is deliberately low — it’s there to demonstrate the JOIN mechanics, not as a production recommendation. Vector similarity scores only mean something relative to your use case, your embedding model, and your data’s typical phrasing. Treat the table below as a starting point to calibrate from, not a universal constant:

Don’t treat the specific numbers as portable — our own Step 4 results (0.44 down to 0.25 for genuinely related products) sit far below the 0.65–0.85 range you’ll see quoted in tutorials built on longer, more semantically rich text. Model, embedding dimension, and phrase length all shift the absolute scale. What transfers is the relative ordering: dedup > strict match > loose recommendation > broad topic match > outlier. Start permissive, log the scores your queries actually return in production, and tighten the threshold once you can see your own real distribution.

Where Else This Pattern Applies

The recommendation engine above is one shape of a more general pattern: embed → search → JOIN with relational rules. The same three steps carry over directly:

• Semantic document search — swap product descriptions for support articles or knowledge-base entries; same search call, higher top-N.
• Fraud detection — embed transaction patterns instead of product text, then bucket the returned SIMILARITY_SCORE into risk tiers with a CASE statement.
• Deduplication — same search call, threshold pushed up above 0.95, used to flag near-identical customer or catalog records before insert.

Quick Reference: The Functions You Used

Everything above the API is bare SQL, no stored-procedure wrapper. Keep this table nearby while you build:

What You Built

In six steps, and without leaving SQL, you: created a vector collection, loaded real product embeddings with a single batch call, ran a semantic similarity search, filtered it against live inventory and pricing with a plain JOIN, and picked a defensible threshold instead of guessing one.

From here, the same pattern scales to your actual catalog: swap the five-row example for a batch job over your full products table, point the search at the product a logged-in customer is currently viewing, and cache the top-N results for your “customers also viewed” rail.

TRY IT NEXT: Take the fraud-detection variant from “Where Else This Pattern Applies” and adapt Step 4–6 above to bucket SIMILARITY_SCORE into risk tiers with a CASE statement instead of a WHERE clause.

About Author

About Johnson Varughese

Johnson Varughese manages Support Engineering at Actian, assisting developers leveraging Actian data interfaces (ODBC, JDBC, ADO.NET, and more). As a Google Certified Data Practitioner and Generative AI Leader, he provides technical guidance and troubleshooting expertise to ensure robust application performance across different programming environments. Johnson's wealth of knowledge in data access interfaces has streamlined numerous development projects. His Actian blog entries detail best practices for integrating Actian’s database engines and interfaces. Explore his articles to optimize your database-driven applications.