Vector Search

Implement semantic search with sqlite-vec and embedding storage

Overview

TrailBase integrates sqlite-vec, a vector search extension for SQLite that enables semantic search, similarity matching, and recommendation systems. Store and query high-dimensional embeddings directly in your SQLite database.

What is Vector Search?

Vector search enables finding similar items based on semantic meaning rather than exact text matches. Common use cases include:

• Semantic search: Find documents similar in meaning
• Recommendation engines: Find similar products, articles, or users
• Image similarity: Match similar images
• Anomaly detection: Identify outliers in data
• Content deduplication: Find near-duplicate content

Setup

sqlite-vec is included with TrailBase by default. No additional installation is required.

Creating Tables with Vector Columns

Define tables with vector embedding columns:

CREATE TABLE documents (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  -- Store 384-dimensional embeddings (common for sentence transformers)
  embedding BLOB
);

-- Create a virtual table for faster vector search
CREATE VIRTUAL TABLE vec_documents USING vec0(
  id INTEGER PRIMARY KEY,
  embedding FLOAT[384]
);

Coffee Search Example

From the coffee vector search example:

CREATE TABLE coffee (
  id INTEGER PRIMARY KEY,
  Owner TEXT,
  Aroma REAL,
  Flavor REAL,
  Acidity REAL,
  Sweetness REAL,
  -- 4-dimensional embedding for taste profile
  embedding BLOB
);

Generating Embeddings

Using External APIs

Generate embeddings using OpenAI, Cohere, or other embedding APIs:

Rust

use trailbase_wasm::fetch::{fetch, Request};
use trailbase_wasm::db::{execute, Value};
use serde::{Deserialize, Serialize};

#[derive(Serialize)]
struct EmbeddingRequest {
    input: String,
    model: String,
}

#[derive(Deserialize)]
struct EmbeddingResponse {
    data: Vec<EmbeddingData>,
}

#[derive(Deserialize)]
struct EmbeddingData {
    embedding: Vec<f32>,
}

async fn generate_embedding(text: &str) -> Result<Vec<f32>, HttpError> {
    let request = Request::builder()
        .uri("https://api.openai.com/v1/embeddings")
        .method("POST")
        .header("Authorization", format!("Bearer {}", OPENAI_API_KEY))
        .header("Content-Type", "application/json")
        .body(
            serde_json::to_vec(&EmbeddingRequest {
                input: text.to_string(),
                model: "text-embedding-3-small".to_string(),
            })?
            .into_body()
        )?;
    
    let response = fetch(request).await?;
    let embedding_response: EmbeddingResponse = serde_json::from_slice(&response)?;
    
    Ok(embedding_response.data[0].embedding.clone())
}

async fn index_document(title: &str, content: &str) -> Result<(), HttpError> {
    let embedding = generate_embedding(content).await?;
    let embedding_bytes = embedding_to_bytes(&embedding);
    
    execute(
        "INSERT INTO documents (title, content, embedding) VALUES ($1, $2, $3)",
        [
            Value::Text(title.to_string()),
            Value::Text(content.to_string()),
            Value::Blob(embedding_bytes),
        ]
    ).await?;
    
    Ok(())
}

fn embedding_to_bytes(embedding: &[f32]) -> Vec<u8> {
    embedding
        .iter()
        .flat_map(|f| f.to_le_bytes())
        .collect()
}

import { execute } from 'trailbase-wasm/db';
import { fetch } from 'trailbase-wasm/fetch';

async function generateEmbedding(text: string): Promise<number[]> {
  const response = await fetch('https://api.openai.com/v1/embeddings', {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${process.env.OPENAI_API_KEY}`,
      'Content-Type': 'application/json',
    },
    body: JSON.stringify({
      input: text,
      model: 'text-embedding-3-small',
    }),
  });
  
  const data = await response.json();
  return data.data[0].embedding;
}

async function indexDocument(title: string, content: string) {
  const embedding = await generateEmbedding(content);
  const embeddingBytes = new Float32Array(embedding).buffer;
  
  await execute(
    'INSERT INTO documents (title, content, embedding) VALUES ($1, $2, $3)',
    [title, content, embeddingBytes]
  );
}

Pre-computed Embeddings

For the coffee search example, embeddings are computed from numeric features:

-- Import with pre-computed embeddings
INSERT INTO coffee (Owner, Aroma, Flavor, Acidity, Sweetness, embedding)
VALUES (
  'Colombia',
  8.5,
  8.7,
  8.2,
  8.0,
  -- Embedding as JSON array format
  FORMAT('[%f, %f, %f, %f]', 8.5, 8.7, 8.2, 8.0)
);

Similarity Search

Vector Distance Functions

sqlite-vec provides multiple distance metrics:

• L2 (Euclidean): vec_distance_L2(a, b) - Traditional distance
• Cosine: vec_distance_cosine(a, b) - Best for normalized embeddings
• L1 (Manhattan): vec_distance_L1(a, b) - City block distance

Basic Similarity Query

-- Find documents similar to a query embedding
SELECT 
  id,
  title,
  vec_distance_cosine(embedding, $query_embedding) AS distance
FROM documents
ORDER BY distance
LIMIT 10;

Coffee Search Query

From the coffee search example:

Rust

use trailbase_wasm::db::{query, Value};
use trailbase_wasm::http::{HttpError, Json, Request, StatusCode};

type SearchResponse = (String, f64, f64, f64, f64);

async fn search_handler(req: Request) -> Result<Json<Vec<SearchResponse>>, HttpError> {
    let (aroma, flavor, acidity, sweetness) = (
        req.query_param("aroma").and_then(|v| v.parse().ok()).unwrap_or(8),
        req.query_param("flavor").and_then(|v| v.parse().ok()).unwrap_or(8),
        req.query_param("acidity").and_then(|v| v.parse().ok()).unwrap_or(8),
        req.query_param("sweetness").and_then(|v| v.parse().ok()).unwrap_or(8),
    );
    
    // Query the closest match using vector search
    let results: Vec<SearchResponse> = query(
        r#"
          SELECT Owner, Aroma, Flavor, Acidity, Sweetness
            FROM coffee
            ORDER BY vec_distance_L2(
              embedding, FORMAT("[%f, %f, %f, %f]", $1, $2, $3, $4))
            LIMIT 100
        "#,
        [
            Value::Integer(aroma),
            Value::Integer(flavor),
            Value::Integer(acidity),
            Value::Integer(sweetness),
        ],
    )
    .await
    .map_err(|err| HttpError::message(StatusCode::INTERNAL_SERVER_ERROR, err))?;
    
    Ok(Json(results))
}

import { query } from 'trailbase-wasm/db';

async function searchCoffee(aroma: number, flavor: number, acidity: number, sweetness: number) {
  const results = await query(
    `
      SELECT Owner, Aroma, Flavor, Acidity, Sweetness
      FROM coffee
      ORDER BY vec_distance_L2(
        embedding,
        FORMAT('[%f, %f, %f, %f]', $1, $2, $3, $4)
      )
      LIMIT 100
    `,
    [aroma, flavor, acidity, sweetness]
  );
  
  return results;
}

Semantic Search API

Build a complete semantic search endpoint:

use trailbase_wasm::http::{HttpRoute, Request, Json, routing};
use trailbase_wasm::db::{query, Value};
use trailbase_wasm::{Guest, export};
use serde::{Deserialize, Serialize};

#[derive(Deserialize)]
struct SearchQuery {
    q: String,
    limit: Option<i64>,
}

#[derive(Serialize)]
struct SearchResult {
    id: i64,
    title: String,
    content: String,
    similarity: f64,
}

struct SemanticSearch;

impl Guest for SemanticSearch {
    fn http_handlers() -> Vec<HttpRoute> {
        vec![
            routing::post("/search", search),
            routing::post("/index", index_document),
        ]
    }
}

async fn search(mut req: Request) -> Result<Json<Vec<SearchResult>>, HttpError> {
    let query_data: SearchQuery = req.body().json().await?;
    
    // Generate embedding for the search query
    let query_embedding = generate_embedding(&query_data.q).await?;
    let embedding_bytes = embedding_to_bytes(&query_embedding);
    
    let limit = query_data.limit.unwrap_or(10);
    
    // Search for similar documents
    let rows = query(
        r#"
        SELECT 
          id,
          title,
          content,
          vec_distance_cosine(embedding, $1) AS distance
        FROM documents
        ORDER BY distance ASC
        LIMIT $2
        "#,
        [Value::Blob(embedding_bytes), Value::Integer(limit)]
    ).await?;
    
    let results: Vec<SearchResult> = rows
        .into_iter()
        .map(|row| SearchResult {
            id: row[0].as_integer().unwrap(),
            title: row[1].as_text().unwrap().to_string(),
            content: row[2].as_text().unwrap().to_string(),
            similarity: 1.0 - row[3].as_real().unwrap(), // Convert distance to similarity
        })
        .collect();
    
    Ok(Json(results))
}

async fn index_document(mut req: Request) -> Result<Json<IndexResponse>, HttpError> {
    let doc: DocumentInput = req.body().json().await?;
    
    let embedding = generate_embedding(&doc.content).await?;
    let embedding_bytes = embedding_to_bytes(&embedding);
    
    let id = execute(
        "INSERT INTO documents (title, content, embedding) VALUES ($1, $2, $3) RETURNING id",
        [
            Value::Text(doc.title),
            Value::Text(doc.content),
            Value::Blob(embedding_bytes),
        ]
    ).await? as i64;
    
    Ok(Json(IndexResponse { id }))
}

export!(SemanticSearch);

Filtering with Vector Search

Combine vector similarity with traditional filters:

-- Find similar documents with specific tags
SELECT 
  d.id,
  d.title,
  vec_distance_cosine(d.embedding, $query_embedding) AS distance
FROM documents d
WHERE 
  d.category = 'technology'
  AND d.published_date > '2024-01-01'
  AND d.status = 'published'
ORDER BY distance
LIMIT 20;

Hybrid Search

Combine full-text search with vector similarity:

-- Weighted hybrid search
WITH keyword_results AS (
  SELECT 
    id,
    title,
    rank AS keyword_score
  FROM documents_fts
  WHERE documents_fts MATCH $query
),
vector_results AS (
  SELECT 
    id,
    title,
    (1.0 - vec_distance_cosine(embedding, $query_embedding)) AS vector_score
  FROM documents
)
SELECT 
  COALESCE(k.id, v.id) AS id,
  COALESCE(k.title, v.title) AS title,
  (COALESCE(k.keyword_score, 0) * 0.3 + COALESCE(v.vector_score, 0) * 0.7) AS combined_score
FROM keyword_results k
FULL OUTER JOIN vector_results v ON k.id = v.id
ORDER BY combined_score DESC
LIMIT 10;

Clustering and Grouping

Find clusters of similar items:

-- Find documents similar to a reference document
SELECT 
  d2.id,
  d2.title,
  vec_distance_cosine(d1.embedding, d2.embedding) AS similarity
FROM documents d1
CROSS JOIN documents d2
WHERE d1.id = $reference_id
  AND d2.id != $reference_id
ORDER BY similarity
LIMIT 10;

Recommendation System

use trailbase_wasm::db::{query, Value};

#[derive(Serialize)]
struct Recommendation {
    id: i64,
    title: String,
    similarity: f64,
}

// Recommend items based on user's interaction history
async fn recommend_for_user(user_id: i64) -> Result<Vec<Recommendation>, HttpError> {
    // Get user's average embedding from their liked items
    let user_embedding_rows = query(
        r#"
        SELECT AVG_VEC(i.embedding) as avg_embedding
        FROM user_likes ul
        JOIN items i ON ul.item_id = i.id
        WHERE ul.user_id = $1
        "#,
        [Value::Integer(user_id)]
    ).await?;
    
    if user_embedding_rows.is_empty() {
        return Ok(vec![]);
    }
    
    let user_embedding = user_embedding_rows[0][0].as_blob().unwrap();
    
    // Find similar items user hasn't interacted with
    let recommendation_rows = query(
        r#"
        SELECT 
          i.id,
          i.title,
          vec_distance_cosine(i.embedding, $1) AS distance
        FROM items i
        WHERE i.id NOT IN (
          SELECT item_id FROM user_likes WHERE user_id = $2
        )
        ORDER BY distance
        LIMIT 20
        "#,
        [Value::Blob(user_embedding.to_vec()), Value::Integer(user_id)]
    ).await?;
    
    let recommendations = recommendation_rows
        .into_iter()
        .map(|row| Recommendation {
            id: row[0].as_integer().unwrap(),
            title: row[1].as_text().unwrap().to_string(),
            similarity: 1.0 - row[2].as_real().unwrap(),
        })
        .collect();
    
    Ok(recommendations)
}

Performance Optimization

Indexing

For large datasets, create approximate nearest neighbor (ANN) indexes:

-- Create HNSW index for faster searches
CREATE VIRTUAL TABLE vec_index USING vec0(
  embedding FLOAT[384],
  +metric='cosine',
  +k=10
);

Batch Processing

// Process embeddings in batches
async fn batch_index_documents(documents: Vec<Document>) -> Result<(), HttpError> {
    const BATCH_SIZE: usize = 100;
    
    for batch in documents.chunks(BATCH_SIZE) {
        let embeddings = generate_embeddings_batch(
            &batch.iter().map(|d| d.content.as_str()).collect::<Vec<_>>()
        ).await?;
        
        for (doc, embedding) in batch.iter().zip(embeddings.iter()) {
            let embedding_bytes = embedding_to_bytes(embedding);
            
            execute(
                "INSERT INTO documents (title, content, embedding) VALUES ($1, $2, $3)",
                [
                    Value::Text(doc.title.clone()),
                    Value::Text(doc.content.clone()),
                    Value::Blob(embedding_bytes),
                ]
            ).await?;
        }
    }
    
    Ok(())
}

Embedding Storage

Choosing Dimensions

• 384 dimensions: sentence-transformers/all-MiniLM-L6-v2 (good balance)
• 768 dimensions: sentence-transformers/all-mpnet-base-v2 (higher quality)
• 1536 dimensions: OpenAI text-embedding-ada-002
• 3072 dimensions: OpenAI text-embedding-3-large

Storage Format

// Convert f32 array to bytes
fn embedding_to_bytes(embedding: &[f32]) -> Vec<u8> {
    embedding
        .iter()
        .flat_map(|f| f.to_le_bytes())
        .collect()
}

// Convert bytes back to f32 array
fn bytes_to_embedding(bytes: &[u8]) -> Vec<f32> {
    bytes
        .chunks_exact(4)
        .map(|chunk| f32::from_le_bytes([chunk[0], chunk[1], chunk[2], chunk[3]]))
        .collect()
}

Complete Example: Document Search

See the coffee vector search example for a complete working implementation.

Best Practices

1. Normalize embeddings

   Use cosine distance for normalized embeddings, L2 for unnormalized

2. Cache embeddings

   Store embeddings to avoid regenerating them for every query

3. Batch operations

   Process multiple embeddings in batches for better performance

4. Monitor costs

   Track API usage if using external embedding services

5. Version embeddings

   Store the model version used to generate embeddings

Next Steps

Custom Endpoints

Build search APIs

Geospatial

Location-based queries

Jobs Scheduler

Batch embedding generation

Object Storage

Store large files