linaldb quickstart (embedded, no server)¶
This notebook exercises the real linaldb package installed from PyPI in linal-hub/.venv — the embedded native PyO3 bindings (import linaldb; linaldb.Db()), not the HTTP client for linal serve. Everything here runs in-process, like SQLite.
cd linal-hub
python3 -m venv .venv && source .venv/bin/activate
pip install -r requirements.txt
python -m ipykernel install --user --name linal-hub --display-name "linal-hub (linaldb)"
Then pick the linal-hub (linaldb) kernel for this notebook.
import linaldb
print("linaldb", linaldb.__version__)
db = linaldb.Db(data_dir="./data") # persists to ./data, like the CLI/REPL
db.active_db(), db.data_dir()
linaldb 0.1.9
('default', './data')
1. A hybrid table: scalar columns + a Vector column¶
A small product catalog where each row carries a 4-dim embedding alongside ordinary columns — no separate vector DB needed.
db.execute("""
DATASET products COLUMNS (
id: Int,
name: String,
category: String,
price: Float,
embedding: Vector(4)
)
""".replace("\n", " "))
rows = [
(1, "wireless mouse", "electronics", 19.99, [0.9, 0.1, 0.0, 0.1]),
(2, "mechanical keyboard", "electronics", 89.99, [0.85, 0.2, 0.0, 0.15]),
(3, "usb-c hub", "electronics", 34.5, [0.8, 0.15, 0.05, 0.1]),
(4, "yoga mat", "fitness", 25.0, [0.1, 0.9, 0.2, 0.0]),
(5, "resistance bands", "fitness", 15.5, [0.05, 0.85, 0.25, 0.05]),
(6, "protein powder", "fitness", 39.99, [0.15, 0.8, 0.3, 0.1]),
]
for id_, name, cat, price, emb in rows:
db.execute(f'INSERT INTO products VALUES ({id_}, "{name}", "{cat}", {price}, {emb})')
db.query("SELECT * FROM products ORDER BY id")
| id | name | category | price | embedding | |
|---|---|---|---|---|---|
| 0 | 1 | wireless mouse | electronics | 19.990000 | [0.8999999761581421, 0.10000000149011612, 0.0,... |
| 1 | 2 | mechanical keyboard | electronics | 89.989998 | [0.8500000238418579, 0.20000000298023224, 0.0,... |
| 2 | 3 | usb-c hub | electronics | 34.500000 | [0.800000011920929, 0.15000000596046448, 0.050... |
| 3 | 4 | yoga mat | fitness | 25.000000 | [0.10000000149011612, 0.8999999761581421, 0.20... |
| 4 | 5 | resistance bands | fitness | 15.500000 | [0.05000000074505806, 0.8500000238418579, 0.25... |
| 5 | 6 | protein powder | fitness | 39.990002 | [0.15000000596046448, 0.800000011920929, 0.300... |
2. Similarity search with COSINE_SIM, inline in SQL¶
No separate index needed for ad hoc scoring — COSINE_SIM(col, [literal vector]) works directly in SELECT/WHERE/ORDER BY.
query_vec = [0.88, 0.12, 0.0, 0.1] # "looking for something like a mouse"
top_matches = db.execute(
f"SELECT name, category, COSINE_SIM(embedding, {query_vec}) AS score "
f"FROM products ORDER BY score DESC LIMIT 3"
)
top_matches.to_pandas()
| name | category | score | |
|---|---|---|---|
| 0 | wireless mouse | electronics | 0.999692 |
| 1 | usb-c hub | electronics | 0.996874 |
| 2 | mechanical keyboard | electronics | 0.993850 |
import matplotlib.pyplot as plt
df = top_matches.to_pandas()
fig, ax = plt.subplots(figsize=(5, 3))
ax.barh(df["name"], df["score"])
ax.set_xlabel("cosine similarity")
ax.set_xlim(0.99, 1.0)
ax.set_title("Top matches for the query embedding")
ax.invert_yaxis()
plt.tight_layout()
plt.show()
3. Per-category centroids (AVG_VEC) and window ranking¶
Vector aggregates (AVG_VEC/SUM_VEC) collapse embeddings the same way AVG/SUM collapse scalars, and window functions rank within each PARTITION BY group.
centroids = db.execute(
"SELECT category, AVG_VEC(embedding) AS centroid, AVG(price) AS avg_price "
"FROM products GROUP BY category ORDER BY category"
)
centroids.to_pandas()
| category | centroid | avg_price | |
|---|---|---|---|
| 0 | electronics | [0.8499999642372131, 0.15000000596046448, 0.01... | 48.160000 |
| 1 | fitness | [0.10000000149011612, 0.8499999642372131, 0.25... | 26.830002 |
ranked = db.execute(
"SELECT name, category, price, "
"RANK() OVER (PARTITION BY category ORDER BY price DESC) AS price_rank "
"FROM products ORDER BY category, price_rank"
)
ranked.to_pandas()
| name | category | price | price_rank | |
|---|---|---|---|---|
| 0 | mechanical keyboard | electronics | 89.989998 | 1 |
| 1 | usb-c hub | electronics | 34.500000 | 2 |
| 2 | wireless mouse | electronics | 19.990000 | 3 |
| 3 | protein powder | fitness | 39.990002 | 1 |
| 4 | yoga mat | fitness | 25.000000 | 2 |
| 5 | resistance bands | fitness | 15.500000 | 3 |
4. Persistence: SAVE DATASET, then export via Arrow/pandas¶
Db.dataset(name) reads a saved dataset's on-disk package (data.parquet + schema.json/stats.json/manifest.json) directly off disk — the embedded-mode counterpart of the HTTP client's /delivery endpoint, no network involved.
db.execute("SAVE DATASET products")
dataset = db.dataset("products")
print("on-disk package:", db.dataset_dir("products"))
dataset.stats()
on-disk package: ./data/default/datasets/products
{'row_count': 6,
'columns': {'price': {'min': 15.5,
'max': 89.99,
'mean': 37.495,
'sparsity': 1.0,
'null_count': 0,
'tensor_shape': None},
'category': {'min': None,
'max': None,
'mean': None,
'sparsity': 1.0,
'null_count': 0,
'tensor_shape': None},
'embedding': {'min': None,
'max': None,
'mean': None,
'sparsity': 1.0,
'null_count': 0,
'tensor_shape': None},
'id': {'min': 1.0,
'max': 6.0,
'mean': 3.5,
'sparsity': 1.0,
'null_count': 0,
'tensor_shape': None},
'name': {'min': None,
'max': None,
'mean': None,
'sparsity': 1.0,
'null_count': 0,
'tensor_shape': None}}}
table = dataset.to_arrow()
print(table.schema) # `embedding` round-trips as a native FixedSizeList<Float32>, not a JSON string
df = dataset.to_pandas()
df.dtypes
id: int64 not null name: string not null category: string not null price: float not null embedding: fixed_size_list<item: float not null>[4] not null child 0, item: float not null
id int64 name str category str price float32 embedding object dtype: object
5. Double/FLOAT64 for large-magnitude scalars¶
Float (FLOAT32) is what backs Vector/Matrix elements, but a plain scalar column can opt into real f64 precision with Double/FLOAT64 — useful for e.g. GPS/Unix timestamps that exceed Float's ~7 significant digits.
db.execute("DATASET events COLUMNS (id: Int, ts_f32: Float, ts_f64: Double)")
db.execute("INSERT INTO events VALUES (1, 1126259462.4146729, 1126259462.4146729)")
precision = db.execute("SELECT ts_f32, ts_f64 FROM events")
precision.to_pandas() # ts_f32 visibly loses precision; ts_f64 doesn't
| ts_f32 | ts_f64 | |
|---|---|---|
| 0 | 1.126259e+09 | 1.126259e+09 |
See ../tests/ for a broader, assertion-backed exploration of the package's surface (joins, CTEs/UNION, CASE/COALESCE/CAST, error handling, and a couple of non-obvious gotchas found while writing these tests, documented inline).