# SQLite connector > **CocoIndex v1, Rust SDK (experimental).** This page documents the CocoIndex **v1** Rust SDK (the `cocoindex` crate). The Rust SDK is experimental: any release may change or remove its API, and it is not yet published on crates.io. The Python SDK pages are at https://cocoindex.io/docs/. > > Source: https://cocoindex.io/docs/rust/connectors/sqlite/ · Rust docs index: https://cocoindex.io/docs/rust/llms.txt · Rust SDK tour: https://github.com/cocoindex-io/cocoindex/blob/main/rust/sdk/SHOWCASE.md · Rust examples: https://github.com/cocoindex-io/cocoindex/tree/main/examples/rust The `sqlite` connector provides utilities for writing rows to SQLite databases, with optional vector support via the sqlite-vec extension. ```rust use cocoindex::connectors::sqlite; ``` The module is compiled with the `sqlite` Cargo feature of the `cocoindex` crate. **Note — Vector Support** For vector operations, have the sqlite-vec loadable library (`vec0`) on disk and name it when you connect, with `Database::connect_with_vec`. ## Connection setup ### Database::connect `Database::connect()` opens a SQLite connection pool with sensible defaults. `connect_with_vec()` also loads sqlite-vec. ```rust impl Database { pub async fn connect(path: &str) -> Result pub async fn connect_with_vec(path: &str, vec_extension: &str) -> Result pub async fn connect_with_options(path: &str, options: ConnectOptions) -> Result pub fn from_pool(state_id: impl Into, pool: SqlitePool) -> Self pub fn pool(&self) -> &SqlitePool } pub struct ConnectOptions { pub busy_timeout: Duration, // default DEFAULT_BUSY_TIMEOUT (5 seconds) pub vec_extension: Option, // default None } ``` **Parameters:** - `path` — Path to the SQLite database file. The file is created when it is missing. - `vec_extension` (`connect_with_vec`, `ConnectOptions`) — The sqlite-vec loadable library, as a path with or without the platform suffix, or a bare name such as `"vec0"` to search the system library path. Connecting fails when the library cannot be loaded. - `busy_timeout` (`ConnectOptions`) — How long to wait for a lock held by another connection or process. The default is five seconds. **Returns:** A `Database`, an [`sqlx`](https://docs.rs/sqlx) pool of one connection. It is cheap to clone. There is no `load_vec="auto"`: a Rust build has no `sqlite-vec` package to find the library in, so you name the library. To set other connection options, build a `SqlitePool` yourself and wrap it with `Database::from_pool(state_id, pool)`. **Example:** ```rust let db = sqlite::Database::connect(&db_path).await?; // With vector support, naming the sqlite-vec library: let db = sqlite::Database::connect_with_vec("app.db", "/usr/local/lib/vec0").await?; // With a lock wait other than five seconds: let db = sqlite::Database::connect_with_options( "app.db", sqlite::ConnectOptions { busy_timeout: std::time::Duration::from_millis(1234), vec_extension: None, }, ) .await?; ``` ### Database `sqlite::Database` is a pool that holds one connection, because SQLite is a single-writer engine. The pool serializes the statements that run on it. To run your own SQL on the same database, use `db.pool()`, an `sqlx::SqlitePool`: - `db.pool().begin()` starts a transaction for writes that must be atomic. - `db.pool()` runs a read directly. Drop the last clone of the `Database` to close it. ## As target The `sqlite` connector provides target state APIs for writing rows to tables. With it, CocoIndex tracks what rows should exist and automatically handles upserts and deletions. ### Declaring target states #### Setting up a connection Declare a context key for a `sqlite::Database` to identify your SQLite connection, then provide the connection on the environment builder: **Note** The key name is load-bearing across runs — it's the stable identity CocoIndex uses to track managed rows. See [ContextKey as stable identity](/docs/rust/programming_guide/context/#contextkey-as-stable-identity) before renaming. ```rust use cocoindex::connectors::sqlite; cocoindex::context_key!(static DB: sqlite::Database); let db = sqlite::Database::connect(&db_path).await?; let app = cocoindex::Environment::builder() .db_path(".cocoindex_db") .provide_key(&DB, db) .build() .await? .app("FilesToSqlite") .await?; ``` `context_key!(static DB: sqlite::Database)` declares the key; its name is the stable identity. `provide_key(&DB, db)` provides the connection, in a `#[cocoindex::lifespan]` function or on `Environment::builder()` as here. #### Tables (parent state) Declares a table as a target state. Returns a `TableTarget` for declaring rows. ```rust pub fn declare_table_target( ctx: &Ctx, db: &ContextKey, table_name: impl Into, table_schema: TableSchema, ) -> Result pub fn declare_table_target_with_options( ctx: &Ctx, db: &ContextKey, table_name: impl Into, table_schema: TableSchema, options: SqliteTableOptions, ) -> Result pub async fn mount_table_target( ctx: &Ctx, db: &ContextKey, table_name: impl Into, table_schema: TableSchema, ) -> Result pub async fn mount_table_target_with_options( ctx: &Ctx, db: &ContextKey, table_name: impl Into, table_schema: TableSchema, options: SqliteTableOptions, ) -> Result pub struct SqliteTableOptions { pub managed_by: ManagedBy, pub virtual_table_def: Option, } ``` `declare_table_target*` returns a handle whose rows resolve when the current component commits. `mount_table_target*` mounts the table first, so you can declare rows at once. `table_target` and `table_target_with_options` build the table target state without declaring it, to compose with other target states. `managed_by` and `virtual_table_def` go in `SqliteTableOptions`. **Parameters:** - `db` — A `&ContextKey` identifying the connection to use. - `table_name` — Name of the table. - `table_schema` — Schema definition including columns and primary key (see [Table Schema](#table-schema-from-a-rust-struct)). - `managed_by` — Whether CocoIndex manages the table lifecycle (`ManagedBy::System`, the default) or assumes it exists (`ManagedBy::User`). - `virtual_table_def` — Optional `Vec0TableDef` to create a vec0 virtual table instead of a regular table. **Returns:** A `TableTarget`. As an argument of `mount_each!` or `use_mount!`, it fingerprints by its table name; the schema is not part of the component memo. ```rust let table = sqlite::mount_table_target_with_options( &ctx, &DB, "items", item_schema(), sqlite::SqliteTableOptions { managed_by: cocoindex::ManagedBy::User, ..Default::default() }, ) .await?; ``` **Caution — One table, one key** CocoIndex identifies a table by its key: the `db` key and `table_name`. SQLite ignores case in table names, so two names that differ only in case (`Docs` and `docs`) are two keys for one table. Declare each table through one name only. Changing a table name's case changes its key: CocoIndex then cleans up the table it tracked under the old name, which is the same table. See [ContextKey as stable identity](/docs/rust/programming_guide/context/#contextkey-as-stable-identity). #### Rows (child states) Once a `TableTarget` is resolved, declare rows to be upserted: ```rust impl TableTarget { pub fn declare_row(&self, ctx: &Ctx, row: &R) -> Result<()> } table.declare_row(ctx, &row)?; ``` **Parameters:** - `row` — Any `Serialize` value that serializes to an object whose entries are the table's columns. Extra entries are ignored, and missing ones are written as SQL `NULL`. Each value is bound by its column's declared type. A batch of row changes is written in one transaction, in a few statements: a regular table takes multi-row `VALUES` upserts and `IN` deletes with bound parameters, as many rows per statement as SQLite's bind limit allows. A `vec0` table takes one row per statement. ### Table schema: from a Rust struct Define the table structure using a Rust struct that derives `SchemaFields`: ```rust impl TableSchema { pub fn from_row( primary_key: impl IntoIterator>, ) -> Result pub fn with_vector_dim(self, field_name: &str, dim: usize) -> Result pub async fn with_vector_schema( self, field_name: &str, provider: &(impl VectorSchemaProvider + ?Sized), ) -> Result pub fn with_column_type(self, field_name: &str, sqlite_type: impl Into) -> Result } ``` The row type also derives `Serialize`, so that its values can be declared as rows. **Parameters and builder methods:** - `T` — A struct that derives `SchemaFields`; its fields define table columns. - `primary_key` — The column names that form the primary key. - `with_vector_dim(field, dim)` — Sets the dimension `N` of a `float[N]` vector column. - `with_vector_schema(field, provider)` — Takes the dimension of a vector column from a [`VectorSchemaProvider`](/docs/rust/common_resources/vector_schema/#vectorschemaprovider), typically the embedder that fills it. For an embedder held in the context, pass `ctx.get_key(&EMBEDDER)?`. - `with_column_type(field, sqlite_type)` — Replaces the SQLite type of one column and keeps its nullability. **Example:** ```rust /// One output row: the file path is the primary key. #[derive(Clone, Serialize, Deserialize, SchemaFields)] struct FileRow { path: String, word_count: i64, first_line: String, } fn files_schema() -> Result { sqlite::TableSchema::from_row::(["path"]) } // A vector column takes its dimension from the embedder: let schema = sqlite::TableSchema::from_row::(["id"])? .with_vector_schema("embedding", ctx.get_key(&EMBEDDER)?) .await?; ``` `#[coco(vector)]` marks a `Vec` field as a `float[N]` column whose `N` is set with `with_vector_dim` or `with_vector_schema`; `#[coco(vector = 384)]` fixes it in the struct. `Option` fields become nullable columns; all others are `NOT NULL`. Rust types are automatically mapped to SQLite type affinities: | Rust Type | SQLite Type | |-----------|-------------| | `bool` | `INTEGER` (0/1) | | `i16`, `i32`, `i64` | `INTEGER` | | `f32`, `f64` | `REAL` | | `Decimal` | `TEXT` | | `String` | `TEXT` | | `Vec` | `BLOB` | | `Uuid` | `TEXT` | | `NaiveDate` | `TEXT` (ISO format) | | `NaiveTime` | `TEXT` (ISO format) | | `DateTime`, `NaiveDateTime` | `TEXT` (ISO format) | | `Duration` | `REAL` (total seconds) | | other `Vec`, maps, nested structs, `#[coco(json)]` | `TEXT` (JSON) | | `Vec` with `#[coco(vector)]` | `float[N]` (sqlite-vec type, e.g., `float[384]`) | To override the default mapping, use: - **A field attribute** — `#[coco(type = "…")]` on the field - **A builder method** — `with_column_type` or `with_vector_schema` on the derived schema #### Custom column types Use `#[coco(type = "…")]` to specify a custom SQLite type: ```rust #[derive(Serialize, SchemaFields)] struct MyRow { id: i64, #[coco(type = "REAL")] value: f64, #[coco(json)] data: BTreeMap, } ``` Or on the derived schema, for this schema only: ```rust let schema = sqlite::TableSchema::from_row::(["id"])? .with_vector_dim("embedding", 384)? .with_column_type("text", "VARCHAR(2000)")?; ``` A row is encoded by its `Serialize` impl. The defaults of `uuid`, `chrono` and `rust_decimal` serialize to the text these columns take, and a collection serializes to JSON text. For another form, put `#[serde(serialize_with = "…")]` on the field. #### VectorSchemaProvider For a `Vec` field marked `#[coco(vector)]`, pass a [`VectorSchemaProvider`](/docs/rust/common_resources/vector_schema/#vectorschemaprovider) to `with_vector_schema` to set the vector dimension. sqlite-vec columns are `float[N]` whatever the element type. Vectors are stored as BLOBs in sqlite-vec compatible float32 format. ### Table schema: explicit column definitions Define columns directly using `ColumnDef`: ```rust impl TableSchema { pub fn new( columns: impl IntoIterator, ColumnDef)>, primary_key: impl IntoIterator>, ) -> Result } ``` **Example:** ```rust use cocoindex::connectors::sqlite::{ColumnDef, TableSchema}; TableSchema::new( [ ("id", ColumnDef::new("INTEGER").not_null()), ("name", ColumnDef::new("TEXT")), ("score", ColumnDef::new("REAL").not_null()), ("blob", ColumnDef::new("BLOB").not_null()), ("embedding", ColumnDef::new("float[3]").not_null()), ], ["id"], )? ``` `ColumnDef::new(sqlite_type)` is nullable; `.not_null()` opts out. ### Virtual tables SQLite virtual tables allow custom storage backends and specialized functionality. The `sqlite` connector supports creating virtual tables through the same `declare_table_target()` API used for regular tables. #### Vec0 virtual tables The `vec0` module from sqlite-vec provides optimized vector storage for similarity search. Use vec0 virtual tables when: - You need efficient vector similarity search with built-in indexing - You want to partition vectors by categories for faster queries - You're working with large vector datasets **Requirements:** - Exactly one `INTEGER` primary key column - At least one `float[N]` vector column - The sqlite-vec extension must be loaded (`Database::connect_with_vec`) #### Vec0TableDef Configure vec0-specific features using `Vec0TableDef`: ```rust pub struct Vec0TableDef { /// Columns used as vec0 partition keys (sharding). pub partition_key_columns: Vec, /// Auxiliary columns (stored, prefixed with `+`, not used in KNN filtering). pub auxiliary_columns: Vec, } ``` **Parameters:** - `partition_key_columns` — List of column names used to partition the vector index. Queries can filter by partition keys efficiently. Multiple partition keys create a composite partition. - `auxiliary_columns` — List of column names to mark as auxiliary (stored but not usable in KNN filters). Useful for metadata that doesn't need to participate in similarity search. #### Creating vec0 virtual tables Set `virtual_table_def` in the `SqliteTableOptions` of `declare_table_target_with_options()` or `mount_table_target_with_options()`: ```rust let db = sqlite::Database::connect_with_vec(path.to_str().unwrap(), &extension).await?; // In the app: let schema = sqlite::TableSchema::from_row::(["id"])? .with_vector_schema("embedding", ctx.get_key(&EMBEDDER)?) .await?; let table = sqlite::mount_table_target_with_options( &ctx, &DB, "vec_docs", schema, sqlite::SqliteTableOptions { virtual_table_def: Some(sqlite::Vec0TableDef { auxiliary_columns: vec!["title".to_string()], ..Default::default() }), ..Default::default() }, ) .await?; ``` The connection must come from `connect_with_vec`, so that the `vec0` module is loaded. **Warning — Current Limitations** Vec0 virtual tables have the following limitations: - **Schema changes are not supported incrementally**. When you modify the table schema (add/remove columns, change `virtual_table_def` settings), the table will be recreated and **existing data will be lost**. - **Switching between regular and virtual tables** will also recreate the table and clear existing data. A future schema versioning mechanism will allow preserving row data across table recreations. ### Example ```rust use std::path::PathBuf; use cocoindex::connectors::sqlite; use cocoindex::prelude::*; use serde::{Deserialize, Serialize}; cocoindex::context_key!(static DB: sqlite::Database); const TABLE: &str = "files"; /// One output row: the file path is the primary key. #[derive(Clone, Serialize, Deserialize, SchemaFields)] struct FileRow { path: String, word_count: i64, first_line: String, } fn files_schema() -> Result { sqlite::TableSchema::from_row::(["path"]) } /// Summarize one file. Logic-tracked, so editing it invalidates cached files. #[cocoindex::function] async fn summarize(_ctx: &Ctx, file: &FileEntry) -> Result { let text = file.content_str()?; let word_count = text.split_whitespace().count() as i64; let first_line = text.lines().next().unwrap_or_default().to_string(); Ok(FileRow { path: file.relative_path().to_string_lossy().into_owned(), word_count, first_line, }) } /// Summarize one file and declare its row. Mounted as a per-file processing /// component — the component-memo fast-path skips unchanged files on re-runs. #[cocoindex::function] async fn process_file(ctx: &Ctx, file: FileEntry, table: sqlite::TableTarget) -> Result<()> { let row = summarize(ctx, &file).await?; table.declare_row(ctx, &row)?; Ok(()) } async fn index(source_dir: PathBuf, db_path: String) -> Result<()> { let db = sqlite::Database::connect(&db_path).await?; let app = cocoindex::Environment::builder() .db_path(".cocoindex_db") .provide_key(&DB, db) .build() .await? .app("FilesToSqlite") .await?; let stats = app .run(move |ctx| { let source_dir = source_dir.clone(); async move { let table = sqlite::mount_table_target(&ctx, &DB, TABLE, files_schema()?).await?; let files = cocoindex::resources::fs::walk_items(&source_dir, &["**/*.md", "**/*.txt"])?; mount_each!(files, |file| process_file(ctx, file, table)).await?; Ok(()) } }) .await?; println!("{stats}"); Ok(()) } ``` See `examples/rust/files_to_sqlite` for the full program. ### Example: Vec0 Virtual Table See the `vec0` snippet under [Creating vec0 virtual tables](#creating-vec0-virtual-tables): `connect_with_vec`, then `mount_table_target_with_options` with a `Vec0TableDef`. `rust/sdk/cocoindex/tests/sqlite_target.rs` has the full program, which also searches the table.