# libSQL Is a Relational Database and a Vector Database at the Same Time

> libSQL has vector search built in: a vector column type, distance functions and an ANN index, no extension required. Here is how to keep rows and embeddings in one database, with the queries and the one gotcha to know.

Published: 2026-11-14
Canonical: https://layerbase.com/blog/libsql-relational-and-vector-database

Short version: libSQL can store your normal tables and your embeddings side by side, and it searches the embeddings with plain SQL. There is no extension to install. Postgres needs pgvector for this and stock SQLite needs `sqlite-vec`, but in libSQL the vector column type, the distance functions and an approximate nearest neighbor index are part of the engine. You can [create a libSQL database](/create/libsql) on Layerbase and run every query in this post against it.

The usual way people build semantic search or RAG is two databases: Postgres or SQLite for the actual records, and Pinecone, Qdrant or something similar for the vectors. Then they write the glue. Every insert goes to both places, every search returns IDs from the vector store that get looked up in the relational one, and every delete has to remember to clean up the other side. When the two drift, you get search results pointing at rows that no longer exist.

For a lot of apps that second database is overhead you do not need. This post walks through doing it all in libSQL instead.

## Contents

- [What libSQL Adds](#what-libsql-adds)
- [A Table With an Embedding Column](#a-table-with-an-embedding-column)
- [Exact Search With Ordinary WHERE Clauses](#exact-search-with-ordinary-where-clauses)
- [The Vector Index](#the-vector-index)
- [The Filtering Gotcha](#the-filtering-gotcha)
- [Rows and Vectors Commit Together](#rows-and-vectors-commit-together)
- [Hybrid Search With FTS5](#hybrid-search-with-fts5)
- [Smaller Vectors](#smaller-vectors)
- [When a Dedicated Vector Database Still Wins](#when-a-dedicated-vector-database-still-wins)
- [FAQ](#faq)

## What libSQL Adds

[libSQL](https://github.com/tursodatabase/libsql) is the open-source fork of SQLite that Turso started. It keeps SQLite's SQL dialect and file format, and adds a network server, replication, and vector search. The vector part is three pieces:

| Piece | What it is |
| --- | --- |
| Column types | `F32_BLOB(n)` for 32-bit float vectors, plus `F64_BLOB`, `F16_BLOB`, `FB16_BLOB`, `F8_BLOB` and `F1BIT_BLOB` for other precisions |
| Functions | `vector32()` to build a vector from a JSON-style array, `vector_extract()` to read one back, `vector_distance_cos()` and `vector_distance_l2()` for distance |
| Index | `libsql_vector_idx()` inside a normal `CREATE INDEX`, queried with the `vector_top_k()` table function |

Everything below was run against libSQL server 0.24, the same version Layerbase Cloud runs. I used three-dimensional vectors so the output stays readable. Real embeddings have hundreds or thousands of dimensions, and the SQL is identical.

## A Table With an Embedding Column

The embedding is just another column. The number in `F32_BLOB(3)` is the dimension count, so for OpenAI's `text-embedding-3-small` you would write `F32_BLOB(1536)`.

```sql
CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  price_cents INTEGER NOT NULL,
  in_stock INTEGER NOT NULL,
  embedding F32_BLOB(3)
);

INSERT INTO products VALUES
  (1, 'Trail runner', 'shoes',   12000, 1, vector32('[0.9, 0.1, 0.0]')),
  (2, 'Road runner',  'shoes',    9000, 0, vector32('[0.85, 0.15, 0.05]')),
  (3, 'Rain shell',   'jackets', 15000, 1, vector32('[0.1, 0.9, 0.2]')),
  (4, 'Hiking boot',  'shoes',   18000, 1, vector32('[0.7, 0.3, 0.1]')),
  (5, 'Wool beanie',  'hats',     2500, 1, vector32('[0.0, 0.2, 0.95]'));
```

The price, the stock flag and the vector all live in the same row. That turns out to matter more than anything else here.

## Exact Search With Ordinary WHERE Clauses

You do not need an index to search. `vector_distance_cos()` is a scalar function, so you can sort by it like any other expression, and filter with whatever SQL you already write:

```sql
SELECT name,
       vector_distance_cos(embedding, vector32('[0.88, 0.12, 0.02]')) AS distance
FROM products
WHERE category = 'shoes'
  AND in_stock = 1
  AND price_cents < 15000
ORDER BY distance
LIMIT 3;
```

```
Trail runner | 0.0006
```

The road runner is a closer match than the hiking boot, but it is out of stock, and the hiking boot is over budget. One query, and the answer is right. With a separate vector store you would either push the category, stock and price into its metadata (and keep them in sync forever) or fetch extra candidates and filter them in application code.

This is a full scan, so it reads every row that survives the `WHERE` clause. That is fine for thousands or tens of thousands of rows, especially when the filters cut the set down first. Past that, you want the index.

## The Vector Index

The index uses DiskANN, an approximate nearest neighbor algorithm. You create it with a normal `CREATE INDEX` statement, wrapping the column in `libsql_vector_idx()`:

```sql
CREATE INDEX products_vec
ON products (libsql_vector_idx(embedding, 'metric=cosine'));
```

Then query it through `vector_top_k()`, which returns the rowids of the nearest vectors. Join back to the table for the rest of the row:

```sql
SELECT p.id, p.name, p.price_cents
FROM vector_top_k('products_vec', vector32('[0.88, 0.12, 0.02]'), 3) AS v
JOIN products p ON p.rowid = v.id;
```

```
1 | Trail runner | 12000
2 | Road runner  |  9000
4 | Hiking boot  | 18000
```

The index stays current on its own. An `INSERT`, `UPDATE` or `DELETE` on the table updates it, so there is no reindex job. It also checks dimensions: inserting a two-dimensional vector into this table fails with `vector index(insert): dimensions are different: 2 != 3` instead of quietly storing junk.

Options go in as extra string arguments, for example `'metric=l2'`, `'max_neighbors=20'` or `'compress_neighbors=float8'` to make the index smaller at some cost to recall.

## The Filtering Gotcha

The thing to know is that `vector_top_k()` picks its candidates before your `WHERE` clause runs. Ask for the top 2 and then filter to in-stock items:

```sql
SELECT p.name
FROM vector_top_k('products_vec', vector32('[0.88, 0.12, 0.02]'), 2) AS v
JOIN products p ON p.rowid = v.id
WHERE p.in_stock = 1;
```

```
Trail runner
```

One row, not two. The index returned the trail runner and the road runner, then the filter dropped the road runner. Nothing went back to the index for a replacement.

Two ways to handle it:

1. **Over-fetch.** Ask `vector_top_k()` for more candidates than you need (say 5 or 10 times the page size), filter, then `LIMIT`. This works well when the filter keeps most rows.
2. **Skip the index for narrow filters.** If the `WHERE` clause cuts the table to a few thousand rows (one customer's documents, one category), the exact scan from the previous section is often faster and always correct. Put a regular index on the filter column and let SQLite narrow the set before it computes any distances.

Dedicated vector databases have spent a lot of effort on filtered search, and this is the place where that effort shows. For most apps one of the two patterns above covers it.

## Rows and Vectors Commit Together

Because the embedding is a column, it is covered by the same transaction as the rest of the row:

```sql
BEGIN;
INSERT INTO products VALUES (7, 'Temp', 'x', 1, 1, vector32('[0.5, 0.5, 0.5]'));
ROLLBACK;

SELECT count(*) FROM vector_top_k('products_vec', vector32('[0.5, 0.5, 0.5]'), 10);
-- 5
```

The rolled-back row never reaches the index. In a two-database setup this is exactly where bugs come from: the relational write succeeds, the vector upsert times out, and now search is wrong until someone notices. Here it cannot happen. Deleting a product deletes its embedding, and a backup of the database is a backup of both.

## Hybrid Search With FTS5

libSQL includes SQLite's FTS5 full-text search, so keyword search and vector search can run on the same data:

```sql
CREATE VIRTUAL TABLE products_fts
USING fts5(name, content='products', content_rowid='id');

INSERT INTO products_fts(products_fts) VALUES('rebuild');

SELECT rowid, name FROM products_fts WHERE products_fts MATCH 'runner';
```

```
1 | Trail runner
2 | Road runner
```

A common pattern is to run both searches, then merge the two ranked lists in your app with reciprocal rank fusion. Keyword search catches exact product names and SKUs that embeddings blur, and the vector side catches "lightweight shoe for muddy trails" when no product contains those words. I wrote more about when each one fits in [full-text search vs vector search](/blog/fulltext-search-vs-vector-search).

## Smaller Vectors

A 1536-dimension `F32_BLOB` is about 6 KB per row. If storage matters, the smaller types trade precision for size:

```sql
CREATE TABLE t8 (e F8_BLOB(3));
INSERT INTO t8 VALUES (vector8('[0.1, 0.2, 0.3]'));
SELECT vector_extract(e) FROM t8;
-- [0.1,0.199608,0.3]
```

`F8_BLOB` uses a quarter of the space and you can see the rounding. `F1BIT_BLOB` keeps only the sign of each dimension, which is a 32x reduction and is mostly useful as a fast first pass before re-ranking with full vectors. Measure recall on your own data before switching, because how much the rounding hurts depends on the embedding model.

## When a Dedicated Vector Database Still Wins

I would still reach for Qdrant or a similar engine when:

- **You have many millions of vectors and heavy filtered search.** Filtered approximate search is a core feature in dedicated engines. In libSQL you are working around it with the patterns above.
- **Vectors are the whole product.** If you are building a search service and not an app that happens to search, the tuning knobs, quantization options and sharding in a dedicated engine earn their place.
- **Several services write embeddings independently** and none of them owns the relational data.

If none of those describe you, one database is simpler to run, cheaper, and harder to get out of sync. The [vector databases compared](/blog/vector-databases-compared-2026) post covers the dedicated options if you do need one.

## FAQ

### Does libSQL need an extension for vector search?

No. The vector types, distance functions and the DiskANN index are compiled into libSQL. In stock SQLite you would load the `sqlite-vec` extension instead, and the SQL is different, so code written for one does not run on the other. There is a longer comparison in [SQLite vs libSQL](/blog/sqlite-vs-libsql).

### Which distance metrics does libSQL support?

Cosine and Euclidean (L2). Use `vector_distance_cos()` or `vector_distance_l2()` for exact search, and pass `'metric=cosine'` or `'metric=l2'` when you create the index. Cosine is the usual choice for text embeddings.

### Can I use the vector index with Drizzle or Prisma?

Yes, though the search itself is usually raw SQL. If your ORM has no libSQL vector type, declare the column as a custom type, let the ORM handle normal reads and writes, and run the `vector_top_k()` query through its raw SQL escape hatch. With `@libsql/client` you pass the query vector as a JSON string: `vector32(?)` with `args: [JSON.stringify(embedding)]`.

### Is the vector index exact?

No, DiskANN is approximate, so it can occasionally miss a true nearest neighbor in exchange for speed. If you need exact results, sort by `vector_distance_cos()` without the index.

### How do I try this?

Run `npm i -g layerbase`, then `lbase create libsql1 -e libsql --start` for a local libSQL server, or [create a managed libSQL database](/create/libsql) on Layerbase Cloud and connect with the URL and token from the Quick Connect panel. libSQL is included on the free tier, and the [libSQL page](/db/libsql) has the details.