Your Own Vector Search in 5 Minutes with SQLite, OpenAI Embeddings, and Node.js

One thing that is often overlooked in the current AI chatbot hype is another use case for Large Language Models (LLMs): Vector Search with text embeddings.

Embeddings and Vector Search

I don’t want to waste much time explaining text embeddings and Vector Search. I’m sure you can find better information than I could ever provide about this topic elsewhere. But in short, text embeddings give us similar text understanding capabilities of LLMs like GPT-4, which we can combine with Vector Search algorithms to build, among other things, powerful search experiences that not merely look for matching keywords and synonyms but understand the meaning of what we search for.

'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]

Above, we see an example of how text embeddings translate sentences or phrases into numerical vectors. These vectors represent the semantic meaning of the text in a multi-dimensional space. This allows for comparing the similarity between phrases or finding related concepts despite the absence of exact word matches. The first three phrases represent similar concepts. The distance between their vectors is short. The vector representing the word “motorcycle,” on the other hand, is very far away from those phrases about nature.

Some prime use cases for text embeddings and Vector Search approaches are text similarity search tools, recommendation systems (e.g., “You’re looking for X, you might also be interested in Y.”), or classification engines.

Setting up SQLite with the Vector Search Plugin VSS and Node.js

SQLite is a fantastic technology to use when we want to get started quickly. But apart from general perception, it is also a perfectly valid technology for production-grade projects. At least up to a certain scale.

I love to use SQLite to start a new pet project rapidly, and thanks to the VSS plugin, it enables us to spin up a new vector-based database in minutes without any dependencies on third-party tools or complicated database setups.

npm install better-sqlite3 sqlite-vss

First, let’s set up SQLite:

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

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

  return db;
};

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

// migrate.mjs
import { init } from "./db";

const db = init();

const migrate = () => {
  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 create a table to store products and an additional products_vss virtual table where we’ll store matching vectors for every product we add to the database. 1536 is the vector size created by the OpenAI text-embedding-3-small model. You have to adjust the size according to the model you use.

Now, we can run node ./migrate.mjs to create our database and the specified tables.

Storing Data in the SQLite Vector Database

To quickly create embeddings, let’s install the openai dependency and add a new embedding.mjs utility file:

npm install openai

// utils/embedding.mjs
import OpenAI from "openai";

const openai = new OpenAI({
  apiKey: process.env.OPENAI_API_KEY,
});

export const getEmbedding = async (text: string) => {
  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 keep it simple, we’ll use the OpenAI text-embedding-3-small model. But be aware that there are open-source alternatives you can run locally.

Now, we can use the getEmbeddings() utility to create a text embedding whenever we create a new product, for example:

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

const db = init();

export const create = async (product) => {
  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));
  });
  insertProductAndVector();
};

So much for creating new products. But more importantly, how can we search for matching products using similarity search?

// ...

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

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

In the SQL query, we look for the top 20 closest matching products based on vector similarity and a distance threshold of less than 1.5 (0 would be an exact match; the higher the distance, the less similarity). We then match the result with detailed information from the products table by joining the product id on the rowid from the temporary matching_products result.

Using SQLite Vector Similarity Search for Matching and Recommendations

Apart from understanding meaning instead of only matching keywords, another significant advantage of a vector and text embedding-powered search is that we’re not limited to only a few words.

const similarProducts = search(`${product.name} ${product.description}`);

Imagine product.description is a 2,000-word essay. With a traditional keyword search-based approach, the above code will lead to appalling results. But using text embeddings and an algorithm comparing distances between vectors, finding products similar to a particular other product is as easy as shown above.

To see how to use this approach in practice, check out my other article, where we use Vector Search with SQLite in a Nuxt 3 application.

Wrapping It Up

In this guide, we’ve seen how text embeddings and vector search algorithms empower us to build systems that grasp user intent beyond mere keywords, paving the way for sophisticated recommendation systems and more.

SQLite, enhanced by the VSS vector search plugin, proves to be an invaluable tool for quickly launching vector-based databases, suitable for both pet projects and small to medium-scale production applications.

This guide aims to inspire and equip you with the knowledge to implement these advanced search features in your projects, highlighting the potential of AI for use cases different from chatbot-like tools.