# Model — Database Access Layer
A JPA/Hibernate-style ORM for `rust-web-server`. Struct annotations (via
proc-macro derives) replace Java's `@Entity`/`@Column`; a `Repository<T, ID>`
trait replaces `JpaRepository`; a fluent `QueryBuilder` replaces JPQL and the
Criteria API. Everything is synchronous and compile-time: no reflection,
no runtime schema scanning.
---
## Goals
- Zero-boilerplate CRUD via `#[derive(Model)]` — one struct, one derive, one repository
- Driver-agnostic: PostgreSQL, SQLite, MySQL behind a common `Connection` trait
- Connection pool built-in (`DbPool`) so handlers can grab a connection the same
way they grab application state
- Compile-time column mapping with no runtime overhead
- Fluent query builder for common queries; raw SQL escape hatch for everything else
- Migration runner baked in — `db.migrate()` on startup, files in `migrations/`
- Transaction scope via a closure (`db.transaction(|conn| { … })`)
## Non-goals
- No lazy-loading proxies (Rust has no transparent proxy objects)
- No JPQL/HQL string parsing — the query builder generates SQL directly
- No entity lifecycle callbacks (`@PrePersist` etc.) in v1 — use wrapper methods
---
## Target API — Phase by Phase
### Phase 1 — Connection and Pool
```toml
# Cargo.toml
rust-web-server = { version = "17", features = ["model-postgres"] }
# or "model-sqlite" / "model-mysql"
```
```rust
use rust_web_server::model::{DbConfig, DbPool};
// Built from env vars (RWS_DB_HOST, RWS_DB_PORT, RWS_DB_USER,
// RWS_DB_PASSWORD, RWS_DB_NAME, RWS_DB_POOL_SIZE) or explicit config:
let pool = DbPool::from_env()?;
// Or explicit:
let pool = DbPool::new(DbConfig {
host: "localhost".into(),
port: 5432,
user: "app".into(),
password: "secret".into(),
database: "myapp".into(),
pool_size: 10,
})?;
// Inject into application state so handlers can borrow it:
let app = App::with_state(pool)
.get("/users", list_users);
fn list_users(_req: &Request, _params: &PathParams,
_conn: &ConnectionInfo, pool: &DbPool) -> Response {
let mut db = pool.get()?; // DbConnection checked out from the pool
// …
}
```
---
### Phase 2 — Model Derive
```rust
use rust_web_server::model::Model;
#[derive(Model, Debug, Clone)]
#[table(name = "users")]
pub struct User {
#[primary_key(auto_increment)]
pub id: i64,
#[column(name = "first_name")]
pub name: String,
#[column(unique)]
pub email: String,
pub age: Option<i32>,
#[ignore] // not persisted — computed in application code
pub display_label: String,
}
```
**What `#[derive(Model)]` generates:**
| `User::table_name() -> &'static str` | `"users"` |
| `User::column_names() -> &'static [&'static str]` | `["id","first_name","email","age"]` |
| `User::from_row(row: &dyn Row) -> Result<Self, DbError>` | Deserialise one DB row |
| `User::to_params(&self) -> Vec<Box<dyn Param>>` | Serialise fields for INSERT/UPDATE |
| `User::primary_key_name() -> &'static str` | `"id"` |
| `User::primary_key_value(&self) -> Box<dyn Param>` | `self.id` |
**Supported field types** (Phase 2): `i16`, `i32`, `i64`, `f32`, `f64`, `bool`,
`String`, `Option<T>` for any supported `T`.
**Field attributes:**
| `#[primary_key]` | Maps to the table's PK; used by `save` to choose INSERT vs UPDATE |
| `#[primary_key(auto_increment)]` | PK is DB-generated; excluded from INSERT |
| `#[column(name = "col")]` | Override column name (default: field name) |
| `#[column(unique)]` | Informational; used by migration generator |
| `#[column(nullable)]` | Informational alias for `Option<T>` |
| `#[ignore]` | Field is not mapped to any column |
---
### Phase 3 — Repository
```rust
use rust_web_server::model::Repository;
// UserRepository is auto-generated by #[derive(Model)]:
let mut db = pool.get()?;
let repo = User::repository(&mut db);
// CRUD
let user: Option<User> = repo.find_by_id(1)?;
let all: Vec<User> = repo.find_all()?;
let saved: User = repo.save(&new_user)?; // INSERT if id==0, UPDATE otherwise
repo.delete_by_id(1)?;
// Convenience
let n: i64 = repo.count()?;
let exists: bool = repo.exists_by_id(42)?;
// Batch
repo.save_all(&[user_a, user_b, user_c])?;
repo.delete_all_by_id(&[1, 2, 3])?;
```
**`Repository<T, ID>` trait signature:**
```rust
pub trait Repository<T: Model, ID> {
fn find_by_id(&mut self, id: ID) -> Result<Option<T>, DbError>;
fn find_all(&mut self) -> Result<Vec<T>, DbError>;
fn save(&mut self, entity: &T) -> Result<T, DbError>;
fn save_all(&mut self, entities: &[T]) -> Result<Vec<T>, DbError>;
fn delete_by_id(&mut self, id: ID) -> Result<(), DbError>;
fn delete_all_by_id(&mut self, ids: &[ID]) -> Result<(), DbError>;
fn count(&mut self) -> Result<i64, DbError>;
fn exists_by_id(&mut self, id: ID) -> Result<bool, DbError>;
}
```
---
### Phase 4 — Query Builder
```rust
use rust_web_server::model::Order;
// Simple field equality
let admins: Vec<User> = User::query(&mut db)
.where_eq("role", "admin")
.fetch_all()?;
// Parameterised filter, ordering, pagination
let page: Vec<User> = User::query(&mut db)
.filter("age >= ?", &[&18_i32])
.filter("email LIKE ?", &[&"%@example.com"])
.order_by("name", Order::Asc)
.limit(20)
.offset(40)
.fetch_all()?;
// Single result
let user: Option<User> = User::query(&mut db)
.where_eq("email", "alice@example.com")
.fetch_one()?;
// Count with filter
let n: i64 = User::query(&mut db)
.filter("age > ?", &[&30_i32])
.count()?;
// Update matching rows (no SELECT round-trip)
User::query(&mut db)
.where_eq("active", false)
.update("deleted_at", chrono_ts)?;
// Delete matching rows
User::query(&mut db)
.where_eq("role", "temp")
.delete()?;
```
**`QueryBuilder<T>` fluent methods:**
| `.where_eq(col, val)` | `WHERE col = ?` |
| `.filter(expr, params)` | `WHERE <expr>` (raw fragment + bind params) |
| `.order_by(col, Order)` | `ORDER BY col ASC\|DESC` |
| `.limit(n)` | `LIMIT n` |
| `.offset(n)` | `OFFSET n` |
| `.fetch_all()` | `SELECT * … → Vec<T>` |
| `.fetch_one()` | `SELECT * … LIMIT 1 → Option<T>` |
| `.count()` | `SELECT COUNT(*) …` |
| `.update(col, val)` | `UPDATE … SET col = ?` |
| `.delete()` | `DELETE FROM … WHERE …` |
---
### Phase 5 — Raw SQL
```rust
// Query returning mapped structs
let users: Vec<User> = db.query(
"SELECT * FROM users WHERE age BETWEEN ? AND ?",
&[&18_i32, &65_i32],
)?;
// Query returning untyped rows
let rows: Vec<Row> = db.query_raw(
"SELECT u.name, COUNT(o.id) FROM users u JOIN orders o ON o.user_id = u.id GROUP BY u.id",
&[],
)?;
for row in &rows {
let name: String = row.get("name")?;
let count: i64 = row.get("count")?;
}
// Execute (INSERT / UPDATE / DELETE / DDL)
let affected: u64 = db.execute(
"UPDATE users SET last_login = NOW() WHERE id = ?",
&[&user_id],
)?;
```
---
### Phase 6 — Transactions
```rust
// Closure-based — rolls back automatically on Err
let saved_user = pool.get()?.transaction(|conn| {
let user = User::repository(conn).save(&new_user)?;
let profile = Profile::repository(conn).save(&Profile {
user_id: user.id,
bio: "".into(),
})?;
Ok(user)
})?;
// Manual — useful when the transaction spans multiple function calls
let mut conn = pool.get()?;
conn.begin()?;
// …
conn.commit()?;
// or conn.rollback()?;
```
---
### Phase 7 — Relationships
```rust
#[derive(Model)]
#[table(name = "posts")]
pub struct Post {
#[primary_key(auto_increment)]
pub id: i64,
pub title: String,
pub user_id: i64, // FK — declared as a plain column in Phase 2
}
// Phase 7 adds: #[belongs_to] / #[has_many] attributes and loader helpers
#[derive(Model)]
#[table(name = "users")]
pub struct User {
#[primary_key(auto_increment)]
pub id: i64,
pub name: String,
#[has_many(Post, foreign_key = "user_id")]
pub posts: HasMany<Post>, // not stored in DB; resolved on demand
}
// Loading
let mut db = pool.get()?;
let user = User::repository(&mut db).find_by_id(1)?.unwrap();
// Explicit load — no hidden N+1 queries
let posts: Vec<Post> = user.posts.load(&mut db)?;
// Eager load in a query
let users_with_posts: Vec<(User, Vec<Post>)> = User::query(&mut db)
.include::<Post>()
.fetch_all()?;
```
**Relationship attributes:**
| `#[belongs_to(Owner)]` | Adds FK column; generates `load_owner()` helper |
| `#[has_many(Child, foreign_key = "…")]` | Generates `HasMany<Child>`; no extra column |
| `#[has_one(Child, foreign_key = "…")]` | Generates `HasOne<Child>`; no extra column |
---
### Phase 8 — Migrations
```
migrations/
0001_create_users.sql
0002_add_age_to_users.sql
0003_create_posts.sql
```
```rust
// Run pending migrations on startup
let mut db = pool.get()?;
db.migrate("migrations/")?;
// Creates _schema_migrations(version, applied_at) if not present.
// Runs each .sql file in lexicographic order whose version is not yet recorded.
// Wraps each file in a transaction; aborts on first failure.
// Check status without applying
let status = db.migration_status("migrations/")?;
for m in &status {
println!("{} {}", m.version, if m.applied { "applied" } else { "pending" });
}
```
---
## Architecture
```
src/model/
mod.rs DbPool, DbConnection, DbConfig, DbError, Row, Param traits
repository.rs Repository<T, ID> trait + blanket impl
query.rs QueryBuilder<T> — builds SQL strings + param lists
transaction.rs Transaction wrapper, begin/commit/rollback
migration.rs migration runner + _schema_migrations table
relation.rs HasMany<T>, HasOne<T>, BelongsTo<T> (Phase 7)
rws-macros/src/
model.rs #[proc_macro_derive(Model, attributes(table, column, …))]
generates: table_name, column_names, from_row, to_params,
primary_key_name, primary_key_value, repository()
Feature flags (one DB driver per compilation):
model-postgres → wraps `postgres` crate (pure Rust, no libpq)
model-sqlite → wraps `rusqlite`
model-mysql → wraps `mysql` crate
```
---
## Database Support Matrix
| Connection pool | ✅ | ✅ | ✅ |
| `#[derive(Model)]` | ✅ | ✅ | ✅ |
| Repository CRUD | ✅ | ✅ | ✅ |
| QueryBuilder | ✅ | ✅ | ✅ |
| Raw SQL | ✅ | ✅ | ✅ |
| Transactions | ✅ | ✅ | ✅ |
| Migrations | ✅ | ✅ | ✅ |
| Relationships | ✅ | ✅ | ✅ |
| JSON column type | ✅ | ❌ | ✅ |
| RETURNING clause | ✅ | ✅ (3.35+) | ❌ |
| Auto-increment syntax | `SERIAL` | `INTEGER PRIMARY KEY` | `AUTO_INCREMENT` |
---
## Differences from JPA / Hibernate
| `@Entity` class annotation | `#[derive(Model)]` proc-macro |
| `@Column`, `@Id`, `@GeneratedValue` | `#[column]`, `#[primary_key]`, `#[primary_key(auto_increment)]` |
| `EntityManager` / `Session` | `DbConnection` (checked out from `DbPool`) |
| `JpaRepository<T, ID>` | `Repository<T, ID>` trait (same shape) |
| JPQL / Criteria API | `QueryBuilder<T>` fluent builder |
| `@Transactional` annotation | `conn.transaction(|c| { … })` closure |
| `FetchType.LAZY` proxy | Explicit `.load(&mut db)` call — no hidden queries |
| Flyway / Liquibase | Built-in `db.migrate("migrations/")` |
| Hibernate validator | Existing `#[derive(Validate)]` from `src/validate` |
| Second-level cache | Out of scope for v1 |
| Schema auto-generation | Out of scope for v1 — use migrations |
---
## Environment Variables
| `RWS_DB_HOST` | `localhost` | Database host |
| `RWS_DB_PORT` | `5432` | Database port |
| `RWS_DB_USER` | — | Database user |
| `RWS_DB_PASSWORD` | — | Database password |
| `RWS_DB_NAME` | — | Database name |
| `RWS_DB_POOL_SIZE` | `10` | Max connections in pool |
| `RWS_DB_CONNECT_TIMEOUT_MS` | `5000` | Connection acquisition timeout |
---
## Implementation Summary
| 1 | `DbPool`, `DbConnection`, `DbConfig`, feature flags | Pending |
| 2 | `#[derive(Model)]` proc-macro, column mapping | Pending |
| 3 | `Repository<T, ID>` trait, CRUD, batch ops | Pending |
| 4 | `QueryBuilder<T>` — filter, order, limit, offset, update, delete | Pending |
| 5 | Raw SQL (`query`, `query_raw`, `execute`) | Pending |
| 6 | Transactions (closure + manual begin/commit/rollback) | Pending |
| 7 | Relationships (`#[has_many]`, `#[belongs_to]`, eager load) | Pending |
| 8 | Migration runner, `_schema_migrations` tracking | Pending |