Alopex DB v0.8.8 Tutorial, Part 4: Vector Search and DataFrames

Part 3 completed the SQL examples. Part 4 covers vector search and DataFrames.

The v0.8.8 release verification ran the same SQL corpus, including vector search, through the library, embedded, HTTP, gRPC, and cluster-aware paths and confirmed matching results.

Vector search finds articles similar to a query article. Alopex DB stores embedding vectors as columns in the article table, so similarity can be combined with SQL WHERE conditions. DataFrames then reshape the SQL result in Python.

1. Environment

Install the package with its polars extra for DataFrame support.

pip install --no-cache-dir "alopex[polars]==0.8.8"

2. Creating a table with a vector column

Add tags and an embedding vector to the articles table from Parts 2 and 3.

from alopex import Database

db = Database.new()
db.execute_sql("""CREATE TABLE articles (
  id INTEGER PRIMARY KEY,
  title TEXT,
  tags TEXT,
  views INTEGER,
  embedding VECTOR(3, L2)
)""")

rows = [
    "(1, 'Rustで書くLSMツリー', 'rust,storage', 1200, [1.0, 0.0, 0.0])",
    "(2, 'SQLパーサをNimで実装する', 'nim,sql', 800, [0.9, 0.2, 0.0])",
    "(3, 'ベクトル検索の基礎', 'vector,search', 1500, [0.0, 1.0, 0.0])",
    "(4, '組み込みDBの選び方', 'database,embedded', 600, [0.1, 0.9, 0.0])",
    "(5, 'WALとクラッシュ復旧', 'storage,recovery', 300, [0.8, 0.0, 0.3])",
]
for row in rows:
    db.execute_sql(f"INSERT INTO articles VALUES {row}")

VECTOR(3, L2) declares a three-dimensional vector column whose distance metric is L2, or Euclidean distance. A production application would store the hundreds of dimensions produced by an embedding model. Three dimensions keep this example easy to inspect by hand.

Treat the first axis as storage-related and the second as search-related. The vector [1.0, 0.0, 0.0] for id = 1 represents a storage article, while [0.0, 1.0, 0.0] for id = 3 represents a search article.

3. vector_distance

vector_distance() calculates the distance from a supplied vector.

print(db.execute_sql("""
SELECT id, title, vector_distance(embedding, [1.0, 0.0, 0.0], 'l2') AS d
FROM articles
ORDER BY d
LIMIT 3
"""))

[{'id': 1, 'title': 'Rustで書くLSMツリー', 'd': 0.0},
 {'id': 2, 'title': 'SQLパーサをNimで実装する', 'd': 0.22360680997371674},
 {'id': 5, 'title': 'WALとクラッシュ復旧', 'd': 0.36055511236190796}]

The query vector [1.0, 0.0, 0.0] points toward storage. The exact match, id = 1, comes first at distance 0.0, followed by id = 2 and id = 5.

The v0.8.8 recording runs the same vector_distance() query. It shows the exact match first at distance 0.0, followed by results in increasing distance order.

Smaller distances are closer, so keep ORDER BY d in ascending order. LIMIT sets the number of results. Without those clauses, the query only adds a calculated distance column to every row.

4. Combining vector_distance with WHERE

Add a WHERE condition to the vector query.

print(db.execute_sql("""
SELECT id, title, vector_distance(embedding, [1.0, 0.0, 0.0], 'l2') AS d
FROM articles
WHERE views >= 800
ORDER BY d
LIMIT 3
"""))

[{'id': 1, 'title': 'Rustで書くLSMツリー', 'd': 0.0},
 {'id': 2, 'title': 'SQLパーサをNimで実装する', 'd': 0.22360680997371674},
 {'id': 3, 'title': 'ベクトル検索の基礎', 'd': 1.4142135381698608}]

The third result changes. id = 5, with 300 views, is filtered out, and the more distant id = 3 takes its place.

With vector search in a separate product, the application must request extra candidates, query the database to remove those that fail the condition, and repeat if too few remain. Storing the vector in the same table reduces that process to one additional WHERE clause.

5. Moving SQL results into a DataFrame

execute_sql() returns a list of dictionaries. Regroup the values by column and create a DataFrame.

from alopex import DataFrame

rows = db.execute_sql("SELECT id, title, tags, views FROM articles ORDER BY id")
df = DataFrame.from_columns({k: [r[k] for r in rows] for k in rows[0]})

print(df.height(), "行", df.width(), "列")

5 行 4 列

height() and width() are methods, so call them with parentheses.

This DataFrame supports integer, string, datetime, and list-of-string columns. It cannot create floating-point columns, so a column such as rating is omitted here.

6. Splitting strings with the str namespace

The tags column contains comma-separated strings such as rust,storage. Use the str namespace to split them.

tagged = df.str("tags").split(",", "tag_list")
print(tagged.to_dict()["tag_list"])

[['rust', 'storage'], ['nim', 'sql'], ['vector', 'search'],
 ['database', 'embedded'], ['storage', 'recovery']]

Pass the target column name to df.str("tags"). split(",", "tag_list") separates on commas and stores the result in a new tag_list column. Omitting the second argument overwrites the original column.

The return value is the complete DataFrame, not a single column, so further operations can be chained.

Other operations in the str namespace include to_lowercase, to_uppercase, contains, replace, extract, len_chars, and strip_chars.

7. explode

Multiple tags in one row are inconvenient for per-tag aggregation. explode() expands list elements into rows.

per_tag = tagged.explode("tag_list")
d = per_tag.to_dict()
print(d["tag_list"])
print(d["id"])

['rust', 'storage', 'nim', 'sql', 'vector', 'search', 'database', 'embedded', 'storage', 'recovery']
[1, 1, 2, 2, 3, 3, 4, 4, 5, 5]

Five rows become ten. Each original id appears twice. Only the specified list column is expanded; the other columns are duplicated for each resulting row.

The result is ready for calculations such as article counts per tag.

8. Filtering with a LazyFrame

Convert to a LazyFrame with lazy() and chain filter and select.

from alopex import col, lit

hot = (per_tag.lazy()
       .filter(col("views").gt(lit(800)))
       .select([col("tag_list"), col("views")])
       .collect())

print(hot.to_dict())

{'tag_list': ['rust', 'storage', 'vector', 'search'], 'views': [1200, 1200, 1500, 1500]}

Only tags from articles with more than 800 views remain, coming from id = 1 and id = 3.

Comparisons use methods such as col("views").gt(lit(800)). Operators such as views > 800 are unavailable; build expressions by combining gt, lt, eq, and_, or_, mul, and alias.

No calculation runs until collect() is called. The operations are assembled first and executed together, avoiding intermediate results.

9. Input format for the dt namespace

The dt namespace handles datetime values, but its input format is fixed.

DataFrames accept integer microseconds since the Unix epoch. ISO 8601 strings and Python datetime objects are not accepted.

df = DataFrame.from_columns(
    {"published": [1768473000000000]},
    {"published": "datetime"},
)

1768473000000000 represents 2026-01-15T10:30:00Z. Passing seconds or milliseconds does not raise an error; it represents a different time. A unit mismatch can produce a date in 1970 or the year 58010, so verify the unit before insertion.

10. Vector search and DataFrames from Rust

Vector search is an SQL function, so Rust also calls it through execute_sql(). DataFrame construction differs from Python.

The Rust example uses four dependencies.

[dependencies]
alopex-embedded = "=0.8.8"
alopex-dataframe = "0.8"
alopex-sql = "0.8"
arrow = "53"

alopex-embedded re-exports only DataFrame. Import Series, col, and lit from alopex-dataframe, and import SqlValue from alopex-sql. Series is constructed from Arrow arrays, which requires arrow as well.

The following examples assume these imports.

use alopex_dataframe::{col, lit, DataFrame, Series};
use alopex_embedded::{Database, SqlResult};
use alopex_sql::storage::value::SqlValue;
use arrow::array::{ArrayRef, Int64Array, StringArray};
use arrow::util::display::array_value_to_string;
use std::path::Path;
use std::sync::Arc;

They also assume that the articles table from Section 2 has been created with Database::open(Path::new("./notes-db")).

Run the vector query first.

let result = db.execute_sql(
    "SELECT id, title, tags, views, vector_distance(embedding, [1.0, 0.0, 0.0], 'l2') AS d
     FROM articles ORDER BY d LIMIT 3")?;

let SqlResult::Query(q) = result else { return Ok(()) };
for row in &q.rows {
    println!("{}", row.iter().map(|v| format!("{v:?}")).collect::<Vec<_>>().join(" | "));
}

Integer(1) | Text("Rustで書くLSMツリー") | Text("rust,storage") | Integer(1200) | Double(0.0)
Integer(2) | Text("SQLパーサをNimで実装する") | Text("nim,sql") | Integer(800) | Double(0.22360680997371674)
Integer(5) | Text("WALとクラッシュ復旧") | Text("storage,recovery") | Integer(300) | Double(0.36055511236190796)

The order and distances match the Python result from Section 3. Because vector_distance() is an SQL function, it returns the same result regardless of the calling language.

Move the result into a DataFrame. Python passed a dictionary to DataFrame.from_columns(). Rust creates an Arrow array for each column, wraps each one in a Series, and passes them to DataFrame::new().

let mut ids = Vec::new();
let mut tags = Vec::new();
let mut views = Vec::new();
for row in &q.rows {
    if let SqlValue::Integer(v) = &row[0] { ids.push(*v as i64) }
    if let SqlValue::Text(v) = &row[2] { tags.push(v.clone()) }
    if let SqlValue::Integer(v) = &row[3] { views.push(*v as i64) }
}

let id_col: ArrayRef = Arc::new(Int64Array::from(ids));
let tag_col: ArrayRef = Arc::new(StringArray::from(tags));
let view_col: ArrayRef = Arc::new(Int64Array::from(views));

let df = DataFrame::new(vec![
    Series::from_arrow("id", vec![id_col])?,
    Series::from_arrow("tags", vec![tag_col])?,
    Series::from_arrow("views", vec![view_col])?,
])?;
println!("{} 行 {} 列", df.height(), df.width());

3 行 3 列

Extracting values from SqlValue is an extra step that Python does not need. Python’s dictionaries expose a value by key. Rust returns a typed enum, so each if let extracts only the expected variant. Integer columns return Integer(i32) and are widened with as i64 for an Arrow Int64Array.

In Rust, splitting and expanding tags turns Python’s df.str("tags").split(",", "tag_list") into an expression.

let per_tag = df.lazy()
    .with_columns(vec![col("tags").str().split(",").alias("tag_list")])
    .explode("tag_list")
    .collect()?;
println!("--- explode: {} 行", per_tag.height());

--- explode: 6 行

Python invokes the str namespace as a DataFrame method and passes the column name. Rust creates a column expression with col("tags"), chains .str().split(","), and names the result with .alias("tag_list"). Passing the expression to with_columns adds the column.

explode has the same name in Python and Rust. Each of the three input rows has two tags, so three rows become six.

filter and select can also be chained.

let hot = per_tag.lazy()
    .filter(col("views").gt(lit(700i64)))
    .select(vec![col("id"), col("tag_list"), col("views")])
    .collect()?;

Convert each column back to an Arrow array for display.

for s in hot.columns() {
    let arr = &s.to_arrow()[0];
    let cells: Vec<String> = (0..arr.len())
        .map(|i| array_value_to_string(arr, i).unwrap())
        .collect();
    println!("{}: {}", s.name(), cells.join(", "));
}

id: 1, 1, 2, 2
tag_list: rust, storage, nim, sql
views: 1200, 1200, 800, 800

The id = 5 row with 300 views is filtered out, leaving four rows.

The type passed to lit() must match the comparison column. The example writes 700i64 because views was constructed as an Int64Array. A bare lit(700) is also inferred as i64, so comparing it to a 32-bit integer column fails at runtime with an Int32 > Int64 type mismatch. Building the column as Int64Array prevents that mismatch.

The DataFrame features are the same in Python and Rust. Rust additionally requires selecting Arrow types when columns are constructed, and column operations are expressed as expressions.

11. API summary

Part 5 opens the data directory with a server and runs SQL over HTTP.

References

• v0.8.8 release verification report