🧠 sqlite-vectorx

VectorXLite — A lightweight SQLite extension for vector search with payload support.

This demonstrates how to use the vector_xlite crate to:

• Create a collection with vector embeddings and optional payload data.
• Insert and manage vectors along with associated metadata.
• Perform fast vector similarity search (e.g., Cosine, Dot, or L2 distance).
• Filter and query payloads using standard SQL alongside vector search.

🚀 Console Example — vector_xlite

A minimal Rust example showing how to use the vector_xlite crate via the included example binary.

The example opens an in-memory SQLite connection, registers the VectorXLite extension, creates a collection, inserts several vector points with payloads, and performs a vector search.

🧩 Prerequisites

• Rust (latest stable)
• SQLite (with extension loading enabled)

▶️ Running the Example

From the repository root:

cd example
cargo run

Or run the specific package directly:

cargo run -p example

📘 Full Example

This example corresponds to the contents of src/main.rs inside the example crate:

use vector_xlite::{
    types::{SearchPoint, CollectionConfigBuilder, InsertPoint, DistanceFunction},
    VectorXLite,
};
use rusqlite::Connection;

fn main() {
    // Step 1: Open SQLite in memory
    let sqlite_connection = Connection::open_in_memory().unwrap();

    // Step 2: Initialize VectorXLite
    let vs = VectorXLite::new(sqlite_connection).unwrap();

    // Step 3: Configure and create a collection
    let config = CollectionConfigBuilder::default()
        .collection_name("person")
        .distance(DistanceFunction::Cosine)
        .vector_dimension(4)
        .payload_table_schema("create table person (rowid integer primary key, name text)")
        .build()
        .unwrap();

    match vs.create_collection(config) {
        Ok(_) => {
            // Step 4: Prepare vector points with payloads
            let points = vec![
                InsertPoint::builder()
                    .collection_name("person")
                    .id(1)
                    .vector(vec![1.0, 2.0, 3.0, 4.0])
                    .payload_insert_query("insert into person(rowid, name) values (?1, 'Alice')")
                    .build()
                    .unwrap(),

                InsertPoint::builder()
                    .collection_name("person")
                    .id(2)
                    .vector(vec![4.0, 5.0, 6.0, 4.0])
                    .payload_insert_query("insert into person(name, rowid) values ('Bob', ?1)")
                    .build()
                    .unwrap(),

                InsertPoint::builder()
                    .collection_name("person")
                    .id(3)
                    .vector(vec![7.0, 8.0, 9.0, 4.0])
                    .payload_insert_query("insert into person(name) values ('Charlie')")
                    .build()
                    .unwrap(),

                InsertPoint::builder()
                    .collection_name("person")
                    .id(5)
                    .vector(vec![17.0, 11.0, 9.0, 4.0])
                    .payload_insert_query("insert into person(name) values ('David')")
                    .build()
                    .unwrap(),
            ];

            // Step 5: Insert the data points
            for point in points {
                vs.insert(point).unwrap();
            }

            // Step 6: Run a vector search
            let search_point = SearchPoint::builder()
                .collection_name("person")
                .vector(vec![7.0, 8.0, 9.0, 2.0])
                .top_k(10)
                .payload_search_query("select * from person")
                .build()
                .unwrap();

            let results = vs.search(search_point).unwrap();
            println!("🔍 Search results: {:?}", results);
        }
        Err(e) => println!("❌ Error creating collection: {:?}", e),
    }
}

🧱 Step-by-Step Breakdown

1. Create the Collection

let config = CollectionConfigBuilder::default()
    .collection_name("person")
    .distance(DistanceFunction::Cosine)
    .vector_dimension(4)
    .payload_table_schema("create table person (rowid integer primary key, name text)")
    .build()
    .unwrap();

vs.create_collection(config).unwrap();

This defines:

• collection_name — logical name for your vector data
• distance — similarity metric (Cosine, L2, or Dot)
• vector_dimension — length of the embedding vector
• payload_table_schema — SQL used to store associated metadata

2. Insert Vector Points

Each vector point includes an id, vector embedding, and an SQL payload insertion query.

let point = InsertPoint::builder()
    .collection_name("person")
    .id(1)
    .vector(vec![1.0, 2.0, 3.0, 4.0])
    .payload_insert_query("insert into person(rowid, name) values (?1, 'Alice')")
    .build()
    .unwrap();

vs.insert(point).unwrap();

Use ?1 as a placeholder to bind the vector ID in your SQL statement.

3. Search for Similar Vectors

Perform a similarity search with a given vector and get top matches:

let search_point = SearchPoint::builder()
    .collection_name("person")
    .vector(vec![7.0, 8.0, 9.0, 2.0])
    .top_k(10)
    .payload_search_query("select * from person")
    .build()
    .unwrap();

let results = vs.search(search_point).unwrap();

This fetches the top-K most similar vectors from the collection, along with their payloads.

Complex Example

use std::sync::Arc;

use rusqlite::Connection;
use vector_xlite::{VectorXLite, types::*};

pub fn run_complex_example(vlite: &VectorXLite, sqlite_conn: Arc<Connection>) {
    let create_authors_table = r#"
    create table authors (
            id integer primary key,
            name text not null,
            bio text
        );
        "#;

    sqlite_conn
        .execute(create_authors_table, [])
        .expect("Failed to create authors table");

    let author_inserts = vec![
        "insert into authors(id, name, bio) values (1, 'Alice', 'Writer of whimsical fantasy worlds')",
        "insert into authors(id, name, bio) values (2, 'Bob', 'Short story enthusiast and poet')",
        "insert into authors(id, name, bio) values (3, 'Carol', 'Sci-fi novelist exploring deep space themes')",
    ];

    for q in author_inserts {
        sqlite_conn.execute(q, []).unwrap();
    }

    let story_collection_config = CollectionConfigBuilder::default()
        .collection_name("story_advanced")
        .distance(DistanceFunction::Cosine)
        .vector_dimension(8)
        .payload_table_schema(
            r#"
        create table story_advanced (
            rowid integer primary key,
            author_id integer,
            title text,
            content text,
            tags json,
            published_at text default (datetime('now')),
            rating real
        );
        "#,
        )
        .build()
        .unwrap();

    match vlite.create_collection(story_collection_config) {
        Ok(_) => {
            let points = vec![
            InsertPoint::builder()
                .collection_name("story_advanced")
                .id(101)
                .vector(vec![0.11, 0.22, 0.33, 0.44, 0.55, 0.66, 0.77, 0.88])
                .payload_insert_query(r#"
                    insert into story_advanced(rowid, author_id, title, content, tags, rating)
                    values (?1, 1, 'Dreaming in Colors', 'Once upon a vibrant night...', '["fantasy","dreams"]', 4.8)
                "#)
                .build()
                .unwrap(),
            InsertPoint::builder()
                .collection_name("story_advanced")
                .id(102)
                .vector(vec![0.9, 0.8, 0.1, 0.2, 0.3, 0.4, 0.5, 0.6])
                .payload_insert_query(r#"
                    insert into story_advanced(rowid, author_id, title, content, tags, rating)
                    values (?1, 2, 'The Quiet Storm', 'Thunder rolled over the valley...', '["drama","short","weather"]', 4.2)
                "#)
                .build()
                .unwrap(),
            InsertPoint::builder()
                .collection_name("story_advanced")
                .id(103)
                .vector(vec![0.05, 0.25, 0.45, 0.65, 0.85, 0.15, 0.35, 0.55])
                .payload_insert_query(r#"
                    insert into story_advanced(rowid, author_id, title, content, tags, rating)
                    values (?1, 3, 'Stars Beneath the Waves', 'A galaxy reflected in the ocean depths...', '["sci-fi","ocean","space"]', 4.9)
                "#)
                .build()
                .unwrap(),
        ];

            for point in points {
                vlite.insert(point.clone()).unwrap();
            }

            println!("✅ Inserted complex story points into 'story_advanced' collection.");

            // Create a complex search point
            let search_point = SearchPoint::builder()
                .collection_name("story_advanced")
                .vector(vec![0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8])
                .top_k(5)
                .payload_search_query(
                    r#"
                select 
                    s.rowid, 
                    s.title, 
                    s.rating, 
                    a.name as author, 
                    s.tags, 
                    s.published_at
                from story_advanced s
                join authors a on a.id = s.author_id
                where s.rating > 4.0
                  and json_extract(s.tags, '$[0]') != 'drama'
                order by s.rating desc
                "#,
                )
                .build()
                .unwrap();

            // Perform the vector + SQL hybrid search
            let results = vlite.search(search_point).unwrap();

            println!("\n🚀 Advanced Story Search Results:\n{:#?}", results);
        }
        Err(e) => println!("❌ Error creating advanced story collection: {:?}", e),
    }
}

🧩 API Reference (Summary)

VectorXLite::new(conn: rusqlite::Connection)

Initializes VectorXLite on the given SQLite connection.

CollectionConfigBuilder

InsertPoint Builder

SearchPoint Builder

🛠 Troubleshooting

Persistent storage

Replace Connection::open_in_memory() with a file-backed connection:

let conn = Connection::open("vectors.db")?;