# litedbmodel.rs

[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT)

> **⚠️ Under active development** — APIs may change without notice until v1.0. Not recommended for production use yet.

A lightweight, SQL-friendly ORM for Rust. Rust port of [litedbmodel](https://github.com/foo-ogawa/litedbmodel).

Supports PostgreSQL (via [tokio-postgres](https://github.com/sfackler/rust-postgres)), MySQL, and SQLite (via [sqlx](https://github.com/launchbadge/sqlx)). Designed for production systems where you care about **predictable SQL**, **explicit performance control**, and **operational safety** (replication lag, N+1, accidental full scans).

---

## Philosophy

### SQL is not the enemy — opacity is.
Most ORMs hide SQL behind abstractions that are hard to debug and hard to tune. litedbmodel.rs keeps SQL visible and controllable: generated queries are intentionally simple, and complex cases use real SQL via `query()` / `query_raw()`.

### Make performance the default, not a post-mortem
- **N+1 is structurally prevented** via context-aware lazy batch loading — `find()` attaches a shared batch context, and the first `load().await?` triggers a single query for all sibling records.
- Per-parent limiting is done at the **SQL level** (efficient "top-N per group" patterns).
- Write operations default to **no RETURNING** for throughput; request PKs via `InsertOptions { returning: true }` only when needed.

### Production safety over convenience magic
- Supports **reader/writer routing** via `DbHandlerBuilder::with_writer()` for read replicas.
- Write operations (`create`, `create_many`, `update`, `update_many`, `delete`) require an explicit **transaction boundary**.
- Configurable **hard limits** (`find_hard_limit`, `has_many_hard_limit`) detect accidental over-fetching early.

### Refactoring-friendly, without sacrificing SQL control
- Column references are **typed** (`Model::columns().field`) so IDE rename/find-references work.
- Conditions are **type-safe tuples** via `conds![(column, value)]` macro, with `sql!()` for operators. Compile-time validation via sqlparser-rs.

---

## Key Features

### SQL Control & Modeling
- Predictable generated SQL (readable, hand-written style)
- Raw SQL escape hatch: `query()` / `query_raw()`
- Query-based models for complex reads (`#[model(query = "...")]`)

### Performance by Default
- Transparent N+1 prevention (batch loading for `Relation<T>`)
- SQL-level per-parent limit for relations
- Subqueries: IN/EXISTS with correlated conditions

### Operational Readiness
- Reader/Writer separation with `DbHandlerBuilder::with_writer()`
- Transactions with retry options (`transaction_with_options`)
- Safety guards: `find_hard_limit` / `with_has_many_hard_limit`

### Developer Experience
- Typed columns + tuple conditions for refactoring safety
- Declarative `column.skip()` pattern for optional fields
- Middleware for cross-cutting concerns (logging, auth, tenant isolation)
- Multi-database support (portable tuple API; raw SQL is dialect-dependent)

---

## When litedbmodel.rs is a good fit

Choose litedbmodel.rs if you:
- Build **large or high-throughput** services where SQL tuning and explain plans matter
- Want ORM ergonomics, but refuse to lose the ability to write/own SQL
- Operate with **read replicas** and care about replication lag and routing rules
- Need safe defaults against "oops, loaded 10M rows" and N+1 regressions
- Prefer a model-centric approach with predictable behavior

## When it may NOT be a good fit

litedbmodel.rs may be a poor fit if you:
- Want a "fully abstracted" ORM that hides SQL entirely
- Prefer a query-builder DSL as the primary interface (rather than SQL/tuple conditions)
- Need database-agnostic portability for complex raw SQL (dialect differences are real)

## Non-goals

- **100% database-agnostic SQL**: complex queries use real SQL; dialect differences are real.
- **Migrations as a built-in feature**: schema migrations are out of scope (use your preferred migration tool).
- **Hiding SQL behind a large abstraction layer**: we prioritize predictable SQL over a fully abstracted API.
- **Automatic eager-loading everywhere**: relations use context-aware lazy batch loading; access via `load().await?`.

---

## Installation

Add to `Cargo.toml`:

```toml
[dependencies]
litedbmodel = { version = "0.1", features = ["postgres"] }
```

| Feature | Description |
|---------|-------------|
| `postgres` (default) | PostgreSQL support (tokio-postgres) |
| `mysql` | MySQL support (sqlx) |
| `sqlite` | SQLite support (sqlx) |
| `runtime-tokio` (default) | Tokio async runtime |

---

## Quick Start

```rust
use litedbmodel::prelude::*;

#[derive(Model, Debug, Clone, Default)]
#[model(table = "users")]
pub struct User {
    #[column(primary_key, auto_increment)]
    pub id: Option<i64>,
    #[column]
    pub name: String,
    #[column]
    pub email: String,
    #[column]
    pub is_active: bool,
}

async fn example(handler: &DbHandler) -> Result<()> {
    let db = handler.scope();
    let c = User::columns();

    // Find with tuple conditions (equality)
    let users = db.find::<User>(&conds![(c.is_active, true)], None).await?;

    // Find with sql!() for operators
    let users = db.find::<User>(&conds![
        (c.is_active, true),
        sql!(c.name LIKE {"%john%"}),
    ], None).await?;

    // Create inside transaction
    handler.transaction(|tx| async move {
        let c = User::columns();
        tx.create::<User>(&set![
            [c.name, "John"],
            [c.email, "john@example.com"],
            [c.is_active, true],
        ], None).await?;
        Ok(())
    }).await?;

    Ok(())
}
```

---

## Model Options

The `#[model(...)]` attribute accepts optional configuration:

```rust
#[derive(Model, Debug, Clone, Default)]
#[model(
    table = "entries",
    order = "created_at DESC",
    default_filter = "is_deleted = false",
    select_columns = "id, title, created_at",
    update_table_name = "entries_writable",
    query = "SELECT ... FROM ..."  // for query-based models
)]
pub struct Entry {
    // ...
}
```

| Option | Description |
|--------|-------------|
| `table` | Table name (required) |
| `order` | Default ORDER BY for `find()` |
| `default_filter` | Auto-applied WHERE conditions (raw SQL) |
| `select_columns` | Default SELECT columns |
| `update_table_name` | Table name for INSERT/UPDATE (e.g., writable view) |
| `query` | SQL query for query-based models (CTE) |

---

## Column Attributes

### `#[column]` options

```rust
#[column]                          // Basic column
pub name: String,

#[column(name = "db_column_name")]  // Custom column name
pub prop: String,

#[column(primary_key)]             // Part of primary key
pub id: i64,

#[column(primary_key, auto_increment)]
pub id: Option<i64>,

#[column(has_default)]              // DB default; skip() omits on INSERT
pub created_at: Option<chrono::NaiveDateTime>,
```

| Option | Description |
|--------|-------------|
| `name` | Database column name (defaults to field name) |
| `primary_key` | Mark as part of primary key |
| `auto_increment` | Auto-increment column |
| `has_default` | Column has DB default; SKIP uses it |

---

## CRUD Operations

All executors (`DbScope`, `TransactionExecutor`, `WriterExecutor`) provide convenience methods for CRUD operations. Free functions from `litedbmodel::prelude::*` are also available for advanced use cases.

### find / find_one / find_by_id

```rust
let db = handler.scope();
let c = User::columns();

// Find multiple (use opts! for limit/order)
let users: Vec<User> = db.find::<User>(&conds![(c.is_active, true)], Some(&opts!(limit: 100))).await?;

// Find one
let user: Option<User> = db.find_one::<User>(&conds![(c.email, "john@example.com")], None).await?;

// Find by primary key
let pk_values = vec![vec![Value::Int(1)], vec![Value::Int(2)]];
let users = db.find_by_id::<User>(&pk_values, None).await?;
```

### create / create_many

```rust
handler.transaction(|tx| async move {
    let c = User::columns();

    // Single insert (no RETURNING by default)
    tx.create::<User>(&set![
        [c.name, "John"],
        [c.email, "john@example.com"],
    ], None).await?;

    // With returning
    let opts = InsertOptions { returning: true, ..Default::default() };
    tx.create::<User>(&set![
        [c.name, "Alice"],
        [c.email, "alice@example.com"],
    ], Some(&opts)).await?;

    // Batch insert
    let rows = vec![
        set![[c.name, "A"], [c.email, "a@x.com"]],
        set![[c.name, "B"], [c.email, "b@x.com"]],
    ];
    tx.create_many::<User>(&rows, Some(&opts)).await?;
    Ok(())
}).await?;
```

### update / update_many / delete

```rust
handler.transaction(|tx| async move {
    let c = User::columns();

    // Update matching rows
    let conds = conds![(c.status, "pending")];
    tx.update::<User>(&conds, &set![[c.status, "active"]], None).await?;

    // Bulk update (different values per row)
    let rows = vec![
        set![[c.id, 1i64], [c.name, "John"]],
        set![[c.id, 2i64], [c.name, "Jane"]],
    ];
    let opts = UpdateManyOptions::new(&["id"], false);
    tx.update_many::<User>(&rows, &opts).await?;

    // Delete
    let conds = conds![(c.is_active, false)];
    tx.delete::<User>(&conds, None).await?;
    Ok(())
}).await?;
```

### count

```rust
let n = db.count::<User>(&conds).await?;
```

---

## Type-Safe Conditions

### Tuple conditions (equality)

```rust
let c = User::columns();
let conds = conds![
    (c.status, "active"),
    (c.is_active, true),
];
```

### `sql!()` macro (operators, LIKE, BETWEEN, IS NULL)

```rust
let conds = conds![
    (c.is_active, true),
    sql!(c.age > {18}),
    sql!(c.name LIKE {"%test%"}),
    sql!(c.deleted_at IS NULL),
];
```

### OR conditions

```rust
let conds = conds![
    (c.is_active, true),
    or![
        conds![(c.role, "admin")],
        conds![(c.role, "moderator")],
    ],
];
```

---

## Subquery Conditions

IN/NOT IN and EXISTS subqueries with composite key support.

```rust
use litedbmodel::prelude::{model_in_subquery, model_not_in_subquery, correlated_exists};

// IN subquery: users.id IN (SELECT user_id FROM orders WHERE status = 'paid')
let cond = model_in_subquery(
    &[("id", "user_id")],
    "orders",
    &[Condition::Eq { column: "status".into(), table: None, value: Value::Text("paid".into()) }],
);

// NOT IN subquery
let cond = model_not_in_subquery(&[("id", "user_id")], "banned_users", &[]);

// Correlated EXISTS: EXISTS (SELECT 1 FROM orders WHERE user_id = users.id AND ...)
let cond = correlated_exists(
    "users", "id",
    "user_id",
    "orders",
    &[Condition::Eq { column: "status".into(), table: None, value: Value::Text("completed".into()) }],
);
```

---

## Declarative SKIP Pattern

Use `?` prefix in `set![]` for Option values — set if Some, skip if None:

```rust
let c = User::columns();

// ? prefix: set if Some, skip (exclude from SQL) if None
let values = set![
    [c.updated_at, chrono::Utc::now().naive_utc()],
    [c.name, ?body.name],    // Option<String> → set or skip
    [c.email, ?body.email],  // Option<String> → set or skip
];

// Explicit skip (always exclude from SQL)
let values = set![
    [c.name, "Jane"],
    // c.email is not included at all
];
```

---

## Relation Attributes

Define relations with `#[has_many]`, `#[belongs_to]`, `#[has_one]`:

```rust
#[derive(Model, Debug, Clone, Default)]
#[model(table = "users")]
pub struct User {
    #[column(primary_key, auto_increment)]
    pub id: Option<i64>,
    #[column]
    pub name: String,

    #[has_many(source = "id", target = "author_id", order = "created_at DESC")]
    pub posts: Relation<Vec<Post>>,

    #[has_one(source = "id", target = "user_id")]
    pub profile: Relation<Option<Profile>>,
}

#[derive(Model, Debug, Clone, Default)]
#[model(table = "posts")]
pub struct Post {
    #[column(primary_key, auto_increment)]
    pub id: Option<i64>,
    #[column]
    pub author_id: Option<i64>,

    #[belongs_to(source = "author_id", target = "id")]
    pub author: Relation<Option<User>>,
}
```

### Lazy batch loading (N+1 prevention)

`find()` automatically attaches a shared batch context to all returned records. The first `load().await?` on any record triggers a single batch query for ALL sibling records; subsequent calls return cached data.

```rust
let users = db.find::<User>(&[], None).await?;
for user in &users {
    let posts = user.posts.load().await?;   // First call batch-loads for ALL users
    let dept = user.dept.load().await?;      // One query for all users' depts
}
// Total: 3 queries (users + posts + depts) regardless of record count
```

PostgreSQL uses optimized `= ANY($1::type[])` for single-key and `UNNEST` for composite-key batch queries.

### Composite key relations

```rust
#[has_many(keys = "tenant_id:tenant_id, id:author_id")]
pub posts: Relation<Vec<TenantPost>>,
```

---

## Query Limits (Safety Guards)

Configure hard limits via `DbHandlerBuilder`:

```rust
let handler = DbHandlerBuilder::new(config)
    .find_hard_limit(1000)       // find() throws if > 1000 records
    .with_has_many_hard_limit(10000)  // hasMany batch throws if > 10000 total
    .build()
    .await?;
```

When exceeded, `Error::LimitExceeded` is returned.

---

## Transactions

```rust
// Basic
handler.transaction(|tx| async move {
    tx.create::<User>(&values, None).await?;
    tx.update::<Account>(&conds, &vals, None).await?;
    Ok(())
}).await?;

// With return value
let user = handler.transaction(|tx| async move {
    tx.create::<User>(&values, None).await?;
    let u = tx.find_one::<User>(&conds, None).await?;
    Ok(u)
}).await?;

// Retry on deadlock
handler.transaction_with_options(
    |tx| async move { /* ... */ },
    TransactionOptions { retry_on_error: true, retry_limit: 3, ..Default::default() },
).await?;

// Rollback-only (preview mode)
handler.transaction_with_options(
    |tx| async move { /* ... */ },
    TransactionOptions { rollback_only: true, ..Default::default() },
).await?;

// Nested savepoints
handler.transaction(|tx| async move {
    tx.transaction(|tx2| async move {
        // SAVEPOINT sp_1
        Ok(())
    }).await
}).await?;
```

---

## Middleware

Chain middleware for logging, statistics, tenant isolation:

```rust
pub struct LoggerMiddleware;

impl Middleware for LoggerMiddleware {
    fn name(&self) -> &str { "logger" }

    fn on_execute<'a>(
        &'a self,
        ctx: &'a RequestContext,
        sql: &'a str,
        params: &'a [Value],
        next: NextExecute<'a>,
    ) -> Pin<Box<dyn Future<Output = Result<QueryResult>> + Send + 'a>> {
        Box::pin(async move {
            tracing::info!("SQL: {}", sql);
            next.run(sql, params).await
        })
    }
}
```

Hooks: `on_execute`, `on_find`, `on_create`, `on_update`, `on_delete`.

### Per-Request Context

`DbScope` holds a `RequestContext` (type-map) that flows to transactions and middleware automatically:

```rust
// Router middleware: create per-request scope
let db = DbHandler::global().scope();
db.ctx().set(TenantId(extract_tenant(&req)));

// Store in task_local for implicit access
db.run(|| async {
    let db = DbScope::current();
    db.transaction(|tx| async move {
        // middleware receives ctx automatically via on_execute(ctx, ...)
        tx.create::<User>(&values, None).await?;
        Ok(())
    }).await?;
    Ok(())
}).await;
```

---

## Raw SQL

```rust
// Typed results (model instances)
let users: Vec<User> = db.query::<User>("SELECT * FROM users WHERE status = ?", &[Value::Text("active".into())]).await?;

// Raw rows (no model mapping)
let rows = query_raw(db.handler(), "SELECT id, name FROM users", &[]).await?;
```

---

## Query-Based Models

Models backed by SQL queries instead of tables:

```rust
#[derive(Model, Debug, Clone, Default)]
#[model(table = "user_stats", query = "
    SELECT u.id, u.name, COUNT(p.id) as post_count
    FROM users u LEFT JOIN posts p ON u.id = p.user_id
    GROUP BY u.id, u.name
")]
pub struct UserStats {
    #[column(primary_key)]
    pub id: i64,
    #[column]
    pub name: String,
    #[column]
    pub post_count: i64,
}

// find() wraps query in CTE
let stats = db.find::<UserStats>(&[], None).await?;
```

For parameterized queries, use `with_query()`:

```rust
let opts = with_query(
    "SELECT user_id, SUM(amount) as total FROM orders WHERE created_at >= ? AND created_at < ? GROUP BY user_id",
    vec![Value::Text("2024-01-01".into()), Value::Text("2024-02-01".into())],
);
let reports = db.find::<MonthlyReport>(&[], Some(&opts)).await?;
```

---

## Reader/Writer Separation

Route reads to writer for replication-lag-aware access:

```rust
let handler = DbHandlerBuilder::new(reader_config)
    .with_writer(writer_config)
    .with_writer_sticky(5000)  // ms to stick to writer after transaction
    .build()
    .await?;

// Normal reads → reader pool
let users = handler.scope().find::<User>(&[], None).await?;

// Explicit writer read (avoid replication lag)
let user = handler.with_writer(|w| async move {
    w.find_one::<User>(&conds, None).await
}).await?;

// Writes require transaction (uses writer)
handler.transaction(|tx| async move {
    tx.create::<User>(&values, None).await
}).await?;
```

---

## Multi-Database

Use `DbScope` for multiple databases:

```rust
let main_handler = DbHandlerBuilder::new(main_config).build().await?;
let analytics_handler = DbHandlerBuilder::new(analytics_config).build().await?;

let main_db = main_handler.scope();
let analytics_db = analytics_handler.scope();

let users = main_db.find::<User>(&[], None).await?;
let reports = analytics_db.find::<Report>(&[], None).await?;
```

---

## Performance

Fastest Rust ORM in 15 of 19 benchmark categories against Diesel and SeaORM.

| Operation | litedbmodel.rs | Diesel | SeaORM |
|-----------|-----------------|--------|--------|
| Find unique | **0.34ms** | 0.40ms | 0.52ms |
| Create | **0.97ms** | 1.17ms | 1.11ms |
| Update Many (10) | **0.99ms** | 3.05ms | 3.37ms |
| Nested relations (10K) | 8.28ms | **9.01ms** | 12.61ms |

See [full benchmark results](docs/BENCHMARK.md) and [ORM comparison](docs/COMPARISON.md).

---

## Comparison

| Feature | litedbmodel.rs | Diesel | SeaORM |
|---------|----------------|--------|--------|
| **Relation loading** | Lazy batch loading | Eager/joins | Eager/loader |
| **Complex queries** | Real SQL | Query DSL | Query builder |
| **Query-based models** | ✅ CTE | ❌ | ❌ |
| **Tuple conditions** | ✅ | ❌ | ❌ |
| **SKIP pattern** | ✅ `column.skip()` | ❌ | ❌ |
| **Reader/Writer** | ✅ | Manual | Manual |
| **Transactions** | ✅ + retry | ✅ | ✅ |
| **Performance** | 🏆 15/19 wins | Fast | Medium |

---

## Roadmap

- **litedbmodel-gen-rs** — DDL-based code generation tool (separate repository, not yet started). The TS version has [litedbmodel-gen](https://github.com/foo-ogawa/litedbmodel-gen) for generating model definitions from DDL.

---

## License

MIT
