> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/trailbaseio/trailbase/llms.txt
> Use this file to discover all available pages before exploring further.

# Vector Search

> Implement semantic search with sqlite-vec and embedding storage

## Overview

TrailBase integrates [sqlite-vec](https://github.com/asg017/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:

```sql theme={null}
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](/home/daytona/workspace/source/examples/coffee-vector-search):

```sql theme={null}
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:

<CodeGroup>
  ```rust Rust theme={null}
  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()
  }
  ```

  ```typescript TypeScript theme={null}
  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]
    );
  }
  ```
</CodeGroup>

### Pre-computed Embeddings

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

```sql theme={null}
-- 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

```sql theme={null}
-- 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](/home/daytona/workspace/source/examples/coffee-vector-search/guests/rust/src/lib.rs):

<CodeGroup>
  ```rust Rust theme={null}
  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))
  }
  ```

  ```typescript TypeScript theme={null}
  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;
  }
  ```
</CodeGroup>

## Semantic Search API

Build a complete semantic search endpoint:

```rust theme={null}
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:

```sql theme={null}
-- 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:

```sql theme={null}
-- 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:

```sql theme={null}
-- 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

```rust theme={null}
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:

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

### Batch Processing

```rust theme={null}
// 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

```rust theme={null}
// 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](https://github.com/trailbaseio/trailbase/tree/main/examples/coffee-vector-search) for a complete working implementation.

## Best Practices

<Steps>
  <Step title="Normalize embeddings">
    Use cosine distance for normalized embeddings, L2 for unnormalized
  </Step>

  <Step title="Cache embeddings">
    Store embeddings to avoid regenerating them for every query
  </Step>

  <Step title="Batch operations">
    Process multiple embeddings in batches for better performance
  </Step>

  <Step title="Monitor costs">
    Track API usage if using external embedding services
  </Step>

  <Step title="Version embeddings">
    Store the model version used to generate embeddings
  </Step>
</Steps>

## Next Steps

<CardGroup cols={2}>
  <Card title="Custom Endpoints" href="/advanced/custom-endpoints" icon="route">
    Build search APIs
  </Card>

  <Card title="Geospatial" href="/advanced/geospatial" icon="map">
    Location-based queries
  </Card>

  <Card title="Jobs Scheduler" href="/advanced/jobs-scheduler" icon="clock">
    Batch embedding generation
  </Card>

  <Card title="Object Storage" href="/advanced/object-storage" icon="database">
    Store large files
  </Card>
</CardGroup>