My SqLite

Rust library for ergonomic SQLite access with async Tokio, schema sync, and proc-macros that generate SQL models and table DDL. The design mirrors my-postgres — same derive surface (SelectDbEntity, InsertDbEntity, UpdateDbEntity, WhereDbModel, TableSchema) and the same call-sites on the connection (insert_db_entity, query_rows, …) — but adapted to SQLite's semantics (single writer, type affinity, file/:memory: paths, INSERT OR REPLACE upserts, no network).

Workspace crates

my-sqlite-core: async connection wrapper around async-sqlite, SQL builders, schema sync, error type, query streams.
my-sqlite: thin facade that re-exports core and (optionally) the proc-macros under my_sqlite::macros.
my-sqlite-macros: proc-macros for schema and CRUD model generation.
my-sqlite-tests: integration tests that double as runnable examples.

AI navigation cheat sheet

Connection / builder: my-sqlite-core/src/sql_lite_connection.rs, my-sqlite-core/src/sql_lite_connection_builder.rs.
SQL builders: my-sqlite-core/src/sql_select/, sql_insert/, sql_update/, sql_where/.
Table schema / DDL generation: my-sqlite-core/src/table_schema/, my-sqlite-core/src/crate_table/.
GroupBy helpers: my-sqlite-core/src/group_by_fields/.
Query streaming: my-sqlite-core/src/sqlite_query_stream.rs.
Attribute source: my-sqlite-macros/src/attributes/.
Tests / live examples: my-sqlite-tests/src/.

Install & features

[dependencies]
my-sqlite = { tag = "0.1.2", git = "https://github.com/MyJetTools/my-sqlite.git", features = [
    "macros",              # default — pulls in proc-macros for derives
    # "debug-table-schema", # prints generated schema during macro expansion
    # "with-telemetry",     # enables my-telemetry hooks in core
] }

The macros feature is on by default; disable it (default-features = false) only if you need core without derives.

Connecting

SQLite has no host/port/credentials — connection is a file path (or :memory:). There is also no concept of a multi-connection write pool — SQLite serializes writes on the file, so the library keeps a single shared async client.

use my_sqlite::SqlLiteConnectionBuilder;

#[tokio::main]
async fn main() {
    let connection = SqlLiteConnectionBuilder::new("./data.db")
        .build()
        .await
        .unwrap();
}

In-memory database for tests:

let connection = SqlLiteConnectionBuilder::new(":memory:").build().await.unwrap();

Debug printing of every generated SQL statement:

let connection = SqlLiteConnectionBuilder::new("./data.db")
    .debug(true)
    .build()
    .await
    .unwrap();

(Can also be toggled via the DEBUG environment variable.)

Sharing a connection between repositories

SqlLiteConnection is Clone — internally it just holds an Arc<Client> over the same SQLite file/worker, so cloning is cheap. Build the connection once, declare all your tables, then hand a clone to each repository:

let connection = SqlLiteConnectionBuilder::new("./data.db")
    .create_table_if_no_exists::<UserDto>("users")
    .create_table_if_no_exists::<OrderDto>("orders")
    .build()
    .await
    .unwrap();

let users_repo  = UsersRepo::new(connection.clone());
let orders_repo = OrdersRepo::new(connection); // same file, same worker

pub struct UsersRepo {
    connection: SqlLiteConnection,
}

impl UsersRepo {
    pub fn new(connection: SqlLiteConnection) -> Self {
        Self { connection }
    }

    pub async fn insert(&self, user: &UserDto) -> Result<usize, SqlLiteError> {
        self.connection.insert_db_entity(user, "users").await
    }
}

A repository can also provision its own tables lazily via connection.create_table_if_not_exists::<Dto>("name").await from its constructor — useful when tables belong logically to a single repository.

Table schema verification

Use TableSchema to describe a table and have it created (and indexes provisioned) on startup. Chain .create_table_if_no_exists::<T>(table_name) on the builder:

use my_sqlite::macros::*;
use rust_extensions::date_time::DateTimeAsMicroseconds;

#[derive(TableSchema, InsertDbEntity, SelectDbEntity)]
pub struct LogItemDto {
    #[primary_key(0)]
    #[sql_type("timestamp")]
    #[order_by_desc]
    pub moment: DateTimeAsMicroseconds,

    #[db_index(id:0, index_name:"id_idx", is_unique:true, order:"ASC")]
    pub id: String,

    pub message: String,

    #[sql_type("jsonb")]
    pub context: std::collections::BTreeMap<String, String>,
}

let connection = SqlLiteConnectionBuilder::new(":memory:")
    .create_table_if_no_exists::<LogItemDto>("logs")
    .build()
    .await
    .unwrap();

with_table_schema_verification::<T>("table") is an alias for create_table_if_no_exists with the same name as in my-postgres. You can also run the sync after the connection is built:

connection.create_table_if_not_exists::<LogItemDto>("logs").await.unwrap();

What the sync does at startup

On build() (and on create_table_if_not_exists), for every declared schema the library reads the real table state via PRAGMA table_info / PRAGMA index_list / PRAGMA index_xinfo and brings it forward:

table missing → CREATE TABLE (+ CREATE INDEX for every declared index);
columns missing → ALTER TABLE … ADD COLUMN … (using the column's #[default_value] / nullability);
indexes missing → CREATE INDEX;
index definition changed (unique flag or columns) → DROP INDEX + CREATE INDEX.

SQLite-specific limitations

SQLite's ALTER TABLE is far more restrictive than Postgres', so the sync cannot:

change an existing column's type (a mismatch is only logged in debug mode — migrate the table manually);
drop columns, or tighten/relax NULL constraints on existing columns;
change the primary key of an existing table.

A NOT NULL column added to a non-empty table must carry a #[default_value(...)], otherwise SQLite rejects the ALTER TABLE ADD COLUMN.

Proc-macros (schema & models)

Derives available from my-sqlite-macros (re-exported via my_sqlite::macros):

SelectDbEntity — maps query rows to structs. Field attributes: db_column_name, sql, sql_type, order_by, order_by_desc, group_by, default_if_null, wrap_column_name, json, inside_json.
InsertDbEntity — insert models. Attributes: db_column_name, ignore, ignore_if_none, sql_type, primary_key, e_tag, default_if_null, json, wrap_column_name.
UpdateDbEntity — update models. Attributes: db_column_name, primary_key, ignore, ignore_if_none, sql_type, e_tag, default_if_null, json, wrap_column_name.
WhereDbModel — WHERE / LIMIT / OFFSET. Attributes: db_column_name, operator, ignore_if_none, ignore, limit, offset, sql_type, default_if_null, wrap_column_name, inside_json.
TableSchema — DDL generation. Attributes: primary_key, db_index, sql_type, db_column_name, default_value, ignore_table_column, generate_select_model, generate_update_model, generate_where_model.
Enum helpers — DbEnumAsString, DbEnumAsU8/I8/U16/I16/U32/I32/U64/I64, plus the *WithModel variants for emitting an internal lookup model.
JSON helpers — DbJsonModel and DbJsonWhereModel for typed JSON columns and filters.
Raw WHERE — #[where_raw_model("col1=${a} AND col2 IN ${b}")] attribute macro for hand-rolled predicates with placeholders.

CRUD operations

All methods live on SqlLiteConnection (sql_lite_connection.rs):

Operation	Method
Insert one	`insert_db_entity(&entity, table)`
Insert one, skip on conflict	`insert_db_entity_if_not_exists(&entity, table)` (→ `INSERT OR IGNORE`)
Upsert one	`insert_or_update_db_entity(table, &entity)` (→ `INSERT OR REPLACE`)
Bulk insert	`bulk_insert_db_entities(&entities, table)`
Bulk insert, skip on conflict	`bulk_insert_db_entities_if_not_exists(&entities, table)`
Bulk upsert	`bulk_insert_or_update(&entities, table)`
Select many	`query_rows::<TEntity, TWhere>(table, Option<&TWhere>)`
Select one	`query_single_row::<TEntity, TWhere>(table, Option<&TWhere>)`
Streamed select	`query_rows_as_stream::<TEntity, TWhere>(table, where_model)`
Count	`get_count::<TWhere, TResult>(table, Option<&TWhere>)`
Delete	`delete_db_entity::<TWhere>(&where_model, table)`
Create table	`create_table_if_not_exists::<T: TableSchemaProvider>(table)`
Close	`close()`

SQLite supports upsert via INSERT OR REPLACE / INSERT OR IGNORE, so there is no separate constraint-name argument like in Postgres (ON CONFLICT ON CONSTRAINT …). Pass NoneWhereModel (or None::<&MyWhere>) when you want an unconditional select / count.

Examples

Insert

#[derive(InsertDbEntity)]
pub struct KeyValueDto {
    pub client_id: String,
    pub key: String,
    pub value: String,
}

connection.insert_db_entity(
    &KeyValueDto { client_id, key, value },
    "kv",
).await.unwrap();

Update by primary key

#[derive(UpdateDbEntity)]
pub struct KeyValueDto {
    #[primary_key] pub client_id: String,
    #[primary_key] pub key: String,
    pub value: String,
}
// Updates with primary-key WHERE are typically expressed as upserts:
connection.insert_or_update_db_entity("kv", &dto).await.unwrap();

Select (vector)

#[derive(WhereDbModel)]
pub struct GetInput {
    pub client_id: String,
    pub key: String,
}

#[derive(SelectDbEntity)]
pub struct KeyValueDto {
    pub client_id: String,
    pub key: String,
    pub value: String,
}

let rows: Vec<KeyValueDto> = connection
    .query_rows("kv", Some(&GetInput { client_id, key }))
    .await
    .unwrap();

Delete

#[derive(WhereDbModel)]
pub struct DeleteBy { pub id: i64 }

connection.delete_db_entity(&DeleteBy { id: 42 }, "kv").await.unwrap();

Streaming a large result set

let stream = connection
    .query_rows_as_stream::<LogItemDto, _>("logs", &where_model)
    .await;
// iterate rows without materializing the full Vec

Group-by with aggregations

use my_sqlite::{GroupByMin, GroupByMax};

#[derive(SelectDbEntity)]
pub struct MinMaxKeyDto {
    #[group_by] pub candle_type: i64,
    #[group_by] pub instrument_id: String,

    #[db_column_name("date")] pub min: GroupByMin<i64>,
    #[db_column_name("date")] pub max: GroupByMax<i64>,
}

Where Model

#[derive(WhereDbModel)] renders AND-composed predicates. Option<T> fields with #[ignore_if_none] are dropped when None; Vec<T> fields render as IN (...) (skipped when empty).

#[derive(WhereDbModel)]
pub struct WhereModel {
    #[sql_type("timestamp")]
    #[db_column_name("moment")]
    #[operator(">=")]
    pub from_date: DateTimeAsMicroseconds,

    #[ignore_if_none]
    #[db_column_name("moment")]
    #[operator("<=")]
    pub to_date: Option<DateTimeAsMicroseconds>,

    pub level: Vec<i32>,   // empty → omitted; otherwise IN (...)

    #[limit]  pub take: usize,
    #[offset] pub skip: Option<usize>,
}

For freeform predicates, use the attribute macro:

#[where_raw_model("content=${field_1} AND status IN ${field_2}")]
pub struct WhereRawModel {
    pub field_1: String,
    pub field_2: Vec<i32>,
}

Use NoneWhereModel (or None::<&MyWhere>) when you want no WHERE clause.

Other attributes

Attribute	Effect
`#[ignore]`	Skip field in SQL rendering entirely.
`#[ignore_if_none]`	Skip when `Option` is `None`.
`#[wrap_column_name]`	Quote the column name to avoid reserved-word clashes.
`#[json]`	(De)serialize cell as JSON into the field.
`#[sql("expr")]`	Substitute a column with a custom SQL expression.
`#[db_column_name("name")]`	Map field to a differently named DB column.
`#[inside_json("a.b.c")]`	Target nested JSON paths (uses SQLite's `json_extract`).
`#[sql_type("…")]`	Override column type in DDL / casts.

Types

DateTimeAsMicroseconds (from rust-extensions) is the canonical timestamp type. SQLite has no native timestamp, so values are stored as INTEGER microseconds; declare with #[sql_type("timestamp")] for readable DDL and the right casts.
Booleans go in INTEGER (0/1). JSON columns use TEXT / jsonb (the latter is just an alias in SQLite — useful as a schema hint).
Enums: pick a DbEnumAs… variant for the wire format you want (string, signed/unsigned 8/16/32/64-bit).

Generated models (`#[generate_*_model]`)

From a single TableSchema you can emit auxiliary models:

#[derive(TableSchema)]
pub struct MyTableModel {
    #[generate_update_model(name:"UpdateAcceptedDto", param_type:"where")]
    pub id: String,

    #[generate_update_model(name:"UpdateAcceptedDto", param_type:"update")]
    pub accepted: Option<DateTimeAsMicroseconds>,

    #[generate_where_model(name:"ByTraderAndDate", as_str=true, as_vec=true, limit:"limit_field")]
    #[db_column_name("my_trader_id")]
    pub trader_id: String,

    #[sql_type("timestamp")]
    #[generate_where_model(name:"ByTraderAndDate", operator_from:">", operator_to:"<")]
    pub date: DateTimeAsMicroseconds,

    #[generate_select_model("MySelectDto")]
    #[db_column_name("my_trader_id")]
    pub trader_id_for_select: String,
}

Full working example

use my_sqlite::{SqlLiteConnectionBuilder, NoneWhereModel};
use my_sqlite::macros::*;
use rust_extensions::date_time::DateTimeAsMicroseconds;

const TABLE: &str = "logs";

#[derive(DbEnumAsString, Debug, Clone)]
pub enum LogLevelDto { Info, Warning, Error }

#[derive(TableSchema, InsertDbEntity, SelectDbEntity, Debug)]
pub struct LogItemDto {
    #[primary_key(0)]
    #[sql_type("timestamp")]
    #[order_by_desc]
    pub moment: DateTimeAsMicroseconds,

    #[db_index(id:0, index_name:"id_idx", is_unique:true, order:"ASC")]
    pub id: String,

    pub level: LogLevelDto,
    pub message: String,
}

#[derive(WhereDbModel)]
pub struct WhereModel {
    #[sql_type("timestamp")]
    #[db_column_name("moment")]
    #[operator(">=")]
    pub from_date: DateTimeAsMicroseconds,

    #[limit] pub take: usize,
}

#[tokio::main]
async fn main() {
    let connection = SqlLiteConnectionBuilder::new(":memory:")
        .create_table_if_no_exists::<LogItemDto>(TABLE)
        .build()
        .await
        .unwrap();

    connection.insert_db_entity(&LogItemDto {
        moment: DateTimeAsMicroseconds::now(),
        id: "evt-1".into(),
        level: LogLevelDto::Info,
        message: "hello".into(),
    }, TABLE).await.unwrap();

    let from_date = DateTimeAsMicroseconds::now();
    let rows: Vec<LogItemDto> = connection
        .query_rows(TABLE, Some(&WhereModel { from_date, take: 100 }))
        .await
        .unwrap();

    println!("{} rows", rows.len());
}

Differences from `my-postgres`

If you already use my-postgres, the API is intentionally familiar. Watch out for these SQLite-specific points:

Connection — file path or :memory:; no host=… port=… user=… connection string, no TLS, no SSH tunneling.
No write pool — SQLite serializes writes; the library keeps a single shared async client.
Upsert — INSERT OR REPLACE / INSERT OR IGNORE; no ON CONFLICT ON CONSTRAINT <name> (so insert_or_update_db_entity takes only (table, entity), no PK constraint name).
Types — SQLite type affinity. INTEGER / REAL / TEXT / BLOB / NUMERIC; timestamps live as integer microseconds; JSON lives in TEXT (queried with json_extract).
Schema sync — creates missing tables, adds missing columns (ALTER TABLE ADD COLUMN), creates/recreates indexes. Unlike Postgres it cannot alter column types, change nullability, or change the primary key of an existing table.
Casts — no ::bigint / ::int Postgres casts; SQL uses SQLite's CAST/affinity rules.
No concurrent_insert_or_update_single_entity — the Postgres etag-gated helper is not present in this crate.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

My SqLite

Workspace crates

AI navigation cheat sheet

Install & features

Connecting

Sharing a connection between repositories

Table schema verification

What the sync does at startup

SQLite-specific limitations

Proc-macros (schema & models)

CRUD operations

Examples

Where Model

Other attributes

Types

Generated models (`#[generate_*_model]`)

Full working example

Differences from `my-postgres`

Links

Uh oh!

FilesExpand file tree

README.md

Latest commit

History

README.md

File metadata and controls

My SqLite

Workspace crates

AI navigation cheat sheet

Install & features

Connecting

Sharing a connection between repositories

Table schema verification

What the sync does at startup

SQLite-specific limitations

Proc-macros (schema & models)

CRUD operations

Examples

Where Model

Other attributes

Types

Generated models (#[generate_*_model])

Full working example

Differences from my-postgres

Links

Generated models (`#[generate_*_model]`)

Differences from `my-postgres`