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.

Links

• Source: https://github.com/MyJetTools/my-sqlite
• Related: my-postgres — same design, Postgres backend.