Nuxt: Building a Similar Products Feature with Vector Search and SQLite

February 23, 2024

#nuxt ,
#ai

An underrated technological revolution amidst the excitement surrounding AI chatbots like ChatGPT is the application of Large Language Models (LLMs) for Vector Search using text embeddings.

Text embeddings and Vector Search help create text similarity search tools, develop recommendation systems (for example, suggesting related items with “If you’re looking for X, you might like Y”), and power classification engines.

Product page showing three similar products at the bottom. — Similar products at the bottom of the product page

In this article, we’ll explore using SQLite with the VSS plugin to build a “similar products” functionality with Nuxt.

Final Result: You can find the final result of this article on GitHub, too.

Embeddings and Vector Search

What are embeddings? In simple terms, embeddings give us text understanding capabilities of LLMs like GPT-4. When we combine embeddings with Vector Search algorithms, they enable us to build, among other things, product recommendation systems matching the complete description of a product rather than just looking for keywords and synonyms.

'sunset over mountains' = [0.2, -0.4, 0.9]
'evening at the sea' =  [0.1, -0.3, 0.1]
'sundown lake' =  [0, -0.2, 0.2]
'motorcycle' =  [-5, 3.1, -10.5]

This example shows a couple of phrases and how we might represent them as numerical vectors. These vectors represent the semantic meaning of the text. A numerical representation makes it straightforward to compare the similarity between texts. The first three phrases represent similar concepts. The distance between their vectors is short. On the other hand, the vector representing the word “motorcycle” is very far away from those phrases about nature.

Setting up SQLite

SQLite is an excellent choice for quickly initiating projects. Contrary to common belief, it’s also a viable option for production-level projects, provided they are within a certain size.

I often opt for SQLite to kick off new personal projects swiftly. The VSS plugin further simplifies the process, allowing for the rapid creation of a new vector-based database in mere minutes without the need for external tools or complex database configurations.

npm install better-sqlite3 sqlite-vss

// server/utils/db.ts
import Database from "better-sqlite3";
import * as sqlite_vss from "sqlite-vss";

export const init = () => {
  const db = new Database("./db/vector-database.db");
  db.pragma("journal_mode = WAL");
  sqlite_vss.load(db);

  return db;
};

Next, let’s create a migrations file to set up relevant database tables for us:

// db/migrate.mjs
import Database from "better-sqlite3";
import * as sqlite_vss from "sqlite-vss";

const migrate = () => {
  const db = new Database("./db/vector-database.db");
  db.pragma("journal_mode = WAL");
  sqlite_vss.load(db);

  db.exec(`
    CREATE TABLE IF NOT EXISTS products (
      id INTEGER PRIMARY KEY,
      name TEXT,
      description TEXT
    );
  `);
  db.exec(`
    CREATE VIRTUAL TABLE IF NOT EXISTS products_vss USING vss0(
      vector(1536)
    );
  `);
};

migrate();

We use .mjs here instead of .ts because we want to be able to run this migration file using node ./db/migrate.mjs. By running the migration script, we ensure our SQLite database and the two product tables are up and running.

Creating Embeddings with OpenAI

To create embeddings, let’s use the openai package and add a new getEmbedding() utility function:

npm install openai

Next, in your .env and nuxt.config.ts files, add your OpenAI API key:

NUXT_OPEN_AI_SECRET_KEY=1234

export default defineNuxtConfig({
  // ...
  runtimeConfig: {
    openAi: {
      secretKey: "",
    },
  },
});

Now we’re ready to use the OpenAI API to generate text embeddings:

// server/utils/embedding.ts
import OpenAI from "openai";

export const getEmbedding = async (text: string) => {
  const openai = new OpenAI({
    apiKey: useRuntimeConfig().openAi.secretKey,
  });
  const stringRaw = text.replace(/[\n\r]/g, " ");
  const response = await openai.embeddings.create({
    model: "text-embedding-3-small",
    input: stringRaw,
  });

  return response.data[0].embedding;
};

To simplify things, we will utilize the OpenAI text-embedding-3-small model. However, I want to note that there are open-source options available that can be operated locally.

Next, we’ll employ the getEmbeddings() utility to generate a text embedding anytime we add a new product, for instance:

// server/repositories/product.ts
import { init } from "../utils/db";
import { getEmbedding } from "../utils/embedding";

const db = init();

export const create = async (product: {
  name: string;
  description: string;
}) => {
  const vector = await getEmbedding(`${product.name} ${product.description}`);

  const insertProductAndVector = db.transaction(() => {
    const productQuery = db.prepare(`
      INSERT INTO products(name, description) VALUES (?, ?);
    `);
    const productResult = productQuery.run(product.name, product.description);
    const vssQuery = db.prepare(`
      INSERT INTO products_vss(rowid, vector) VALUES (?, ?);
    `);
    vssQuery.run(productResult.lastInsertRowid, JSON.stringify(vector));
  });

  // Execute the transaction
  insertProductAndVector();
};

Now that we can create new products, let’s also explore how to perform a similarity search:

// ...

export const search = async (text: string, { filterId }) => {
  if (!text) return [];

  const vector = await getEmbedding(text);
  const searchQuery = db.prepare(`
    WITH matching_products AS (
      SELECT rowid, distance
      FROM products_vss
      WHERE vss_search(vector, ?)
      ${filterId ? "AND rowid != ?" : ""}
      AND distance < 1.5
      ORDER BY distance ASC
      LIMIT 20
    )
    SELECT p.*, mp.distance
    FROM products p
    INNER JOIN matching_products mp ON p.id = mp.rowid;
  `);

  return searchQuery.all(JSON.stringify(vector), filterId);
};

In this SQL query, we identify the top 20 products that closely match based on vector similarity, using a distance threshold under 1.5 (where 0 indicates an exact match). We then correlate these results with the product details in the products table by joining the product id with the rowid of the temporary matching_products table.

API Endpoints for Creating and Searching Products

Now, we can use the create() and search() methods from our newly created product repository to provide the respective functionality through HTTP API endpoints:

// server/api/products/index.post.ts
import { create } from "~/server/repositories/product";

export default defineEventHandler(async (event) => {
  const { name, description } = await readBody(event);
  return create({ name, description });
});

Note that in a real-world production application, you probably only want to allow authenticated users to create new products and secure your endpoint accordingly.

// server/api/products/search.ts
import { search } from "~/server/repositories/product";

export default defineEventHandler(async (event) => {
  const { q, filterId } = getQuery<{ q: string; filterId: string }>(event);
  return search(q, { filterId });
});

With those two endpoints, we’re ready to build our Vue-based frontend.

Creating New Products

To test our vector search, we first need to fill the database with some products. So, let’s quickly create a simple pages/products/new.vue file to add new products.

<script setup lang="ts">
const productData = ref({
  name: "",
  description: "",
});
const add = async () => {
  await $fetch("/api/products", {
    method: "POST",
    body: productData.value,
  });
  productData.value = {
    name: "",
    description: "",
  };
};
</script>

<template>
  <div>
    <h1>Add new product</h1>
    <form @submit.prevent="add">
      <input v-model="productData.name" />
      <textarea v-model="productData.description" />
      <button type="submit">Add</button>
    </form>
  </div>
</template>

Now, we can open /products/new in our browsers and use the form to add a few products to the database.

Showing Similar Products

To demonstrate the basic concept, let’s add some logic to a new pages/products/[id].vue component to 1) load a product by its ID and 2) preview three similar products below.

<script setup lang="ts">
const idOrIds = useRoute().params.id;
const id = Array.isArray(idOrIds) ? idOrIds[0] : idOrIds;
const { data: product } = await useFetch(`/api/products/${id}`);
if (product.value === null) throw createError({ statusCode: 404 });

const { data: similarProducts } = await useFetch("/api/products/search", {
  query: {
    q: `${product.value.name} ${product.value.description}`,
    // We pass the `id` of the current product to
    // prevent it from showing up as a similar product.
    filterId: id,
  },
});
</script>

<template>
  <div>
    <div>
      <h1>{{ product.name }}</h1>
      <p>{{ product.description }}</p>
    </div>
    <template v-if="similarProducts?.length > 0">
      <h2>Similar products</h2>
      <ul>
        <li v-for="similarProduct in similarProducts" :key="similarProduct.id">
          <NuxtLink :to="`/products/${similarProduct.id}`">
            {{ similarProduct.name }}
          </NuxtLink>
        </li>
      </ul>
    </template>
  </div>
</template>

Here we first fetch the product matching the current id URL param and then use the /api/products/search endpoint with a query string based on the name and the description of the product to search for similar products.

Remember that making two API requests one after another can lead to a degraded loading experience. You might consider lazy loading similar products to speed up the overall loading time of the page.

 if (product.value === null) throw createError({ statusCode: 404 });

-const { data: similarProducts, status } = await useFetch('/api/products/search', {
+const { data: similarProducts, status } = useLazyFetch('/api/products/search', {
+  server: false,
   query: {

       </ul>
     </template>
+    <div v-else-if="status === 'pending'">Loading similar products...</div>
   </div>
 </template>

If you want to see the complete code or play around with it yourself, you can check out the following GitHub repo: https://github.com/maoberlehner/article-nuxt-sqlite-vector

Wrapping It Up

Integrating vector search into Nuxt applications with SQLite and the VSS plugin enables us to significantly enhance user experience by enabling intelligent, dynamic content suggestions. The example provided underscores the capability to improve user engagement through relevant recommendations.