Vector Search

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:

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()
}

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:

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))
}

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

Normalize embeddings

Use cosine distance for normalized embeddings, L2 for unnormalized

Cache embeddings

Store embeddings to avoid regenerating them for every query

Batch operations

Process multiple embeddings in batches for better performance

Monitor costs

Track API usage if using external embedding services

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

WebAssembly Runtime

Features

Integration

Overview

What is Vector Search?

Setup

Creating Tables with Vector Columns

Coffee Search Example

Generating Embeddings

Using External APIs

Pre-computed Embeddings

Similarity Search

Vector Distance Functions

Basic Similarity Query

Coffee Search Query

Semantic Search API

Filtering with Vector Search

Hybrid Search

Clustering and Grouping

Recommendation System

Performance Optimization

Indexing

Batch Processing

Embedding Storage

Choosing Dimensions

Storage Format

Complete Example: Document Search

Best Practices

Next Steps

Custom Endpoints

Geospatial

Jobs Scheduler

Object Storage

Build docs developers (and LLMs) love

WebAssembly Runtime

Features

Integration

​Overview

​What is Vector Search?

​Setup

​Creating Tables with Vector Columns

​Coffee Search Example

​Generating Embeddings

​Using External APIs

​Pre-computed Embeddings

​Similarity Search

​Vector Distance Functions

​Basic Similarity Query

​Coffee Search Query

​Semantic Search API

​Filtering with Vector Search

​Hybrid Search

​Clustering and Grouping

​Recommendation System

​Performance Optimization

​Indexing

​Batch Processing

​Embedding Storage

​Choosing Dimensions

​Storage Format

​Complete Example: Document Search

​Best Practices

​Next Steps

Custom Endpoints

Geospatial

Jobs Scheduler

Object Storage

Build docs developers (and LLMs) love

Overview

What is Vector Search?

Setup

Creating Tables with Vector Columns

Coffee Search Example

Generating Embeddings

Using External APIs

Pre-computed Embeddings

Similarity Search

Vector Distance Functions

Basic Similarity Query

Coffee Search Query

Semantic Search API

Filtering with Vector Search

Hybrid Search

Clustering and Grouping

Recommendation System

Performance Optimization

Indexing

Batch Processing

Embedding Storage

Choosing Dimensions

Storage Format

Complete Example: Document Search

Best Practices

Next Steps