RustF Database & Model Layer Documentation
Overview
RustF provides a modern, ergonomic database layer with:
- Transparent database access - No need to pass database connections around
- Multi-database support - PostgreSQL, MySQL/MariaDB, SQLite (all on by default; opt out per driver via Cargo Features)
- Type-safe query builders - Compile-time query validation
- Laravel-style model queries - Chainable, intuitive API
- Schema-driven development - YAML schemas generate type-safe models
- Smart change tracking - Only update modified fields
Schema-Driven Workflow
In RustF the YAML schema is the single source of truth for your data model. One declarative schema drives both sides of the database:
┌─ schema generate models ──► src/models/base/*.inc.rs
schemas/*.yaml ────────┤
(source of truth) └─ schema generate sql ─────────► sql/schema.sql ──► DB
You version the schema in git, review changes in pull requests, and regenerate models and SQL from it. The database is never the master copy — it’s a target the schema produces, so new environments are reproducible and two developers can’t silently diverge.
Adopting an existing database (one-time bootstrap)
If you’re adding RustF to a project that already has a database, generate the initial schema from the live database, then switch to the schema-first loop above:
# Reverse-engineer YAML schemas from an existing database (one time).
# The connection is read from your project config (config.toml /
# config.dev.toml); DATABASE_URL overrides it. Use --connection <name> to
# pick a named connection.
rustf-cli db generate-schema --output schemas
This introspects every table (PostgreSQL, MySQL/MariaDB, or SQLite) and writes
schemas/*.yaml. It also writes schemas/_database_dump.sql — the raw DDL of
the live database (via pg_dump / mysqldump / sqlite3 .schema) — so the
original structure stays in source control as a safety reference. Pass
--no-sql to skip it.
There are two different SQL files in a RustF project; they never collide:
| File | Written by | Contents |
|---|---|---|
schemas/_database_dump.sql | db generate-schema | Raw dump of the live database (bootstrap safety reference). |
sql/schema.sql | schema generate sql | DDL generated from your YAML (experimental). |
After the bootstrap, the YAML schema becomes the source of truth: edit the YAML, regenerate models and SQL — don’t hand-edit the database.
Direction summary:
database → schemais a one-time bootstrap for existing databases.schema → modelsandschema → sqlare the steady-state loop you run from then on.
Quick Start
Basic Usage
#![allow(unused)]
fn main() {
use rustf::prelude::*;
// Find a user by ID
let user = Users::get_by_id(123).await?;
// Count all active users
let active_count = Users::query()?
.where_eq("is_active", true)
.count()
.await?;
// Get paginated results (simple)
let users = Users::paginate(1, 20).await?;
// Get paginated results with metadata (recommended)
let result = Users::query()?
.where_eq("is_active", true)
.get_paginated(1, 20)
.await?;
// result.rows - the data
// result.total_rows - total count
// result.total_pages - total pages
// Delete a record
if let Some(user) = Users::get_by_id(456).await? {
user.delete().await?;
}
}
Database Configuration
Initial Setup
Configure your database in config.toml:
[database]
url = "postgresql://user:password@localhost/mydb" # or mysql:// or sqlite://
max_connections = 10
timeout = 30
The database connection is automatically initialized when your app starts. No manual setup required!
Global Database Access
RustF uses a global DB singleton that’s initialized once at startup:
#![allow(unused)]
fn main() {
use rustf::database::DB;
// Check if database is initialized
if DB::is_initialized() {
// Database is ready
}
// Get the backend type
let backend = DB::backend(); // Some(DatabaseBackend::Postgres)
}
Model System
Generated Models
Models are generated from YAML schemas using the RustF CLI. Each model consists of:
- Base model (
base/*.inc.rs) - Auto-generated, never edit - Wrapper model (
*.rs) - Your business logic, safe to edit
Creating a Schema
Create a YAML schema in schemas/users.yaml. Fields are a map (keyed by
column name), not a list. See the Schema Format Reference
for the full specification.
Users:
table: users
version: 1
description: User accounts for the application
fields:
id:
type: integer
primary_key: true
auto: true
required: true
email:
type: string(255)
required: true
unique: true
description: User's email address
username:
type: string(100)
required: true
is_active:
type: boolean
default: true
status:
type: enum
values: ["active", "inactive", "pending"]
default: "pending"
description: Account status
role:
type: enum
values: ["admin", "user", "moderator"]
required: true
default: "user"
description: User role in the system
created_at:
type: datetime
updated_at:
type: datetime
indexes:
- email
- username
Generating Models
# Generate models from schemas
rustf-cli schema generate models
# Force regeneration (backs up existing files)
rustf-cli schema generate models --force
This creates:
src/models/base/users.inc.rs- Generated base modelsrc/models/users.rs- Wrapper for your business logic (if doesn’t exist)
Generating SQL (DDL)
Experimental.
schema generate sqlis verified on representative schemas across all three dialects, but has not yet been exercised at scale. Review the generated DDL before applying it to a database, and please report any mismatch.
The same schema also generates the SQL to create your tables:
# Generate the full SQL schema (DDL) from your schemas
rustf-cli schema generate sql --output sql
The target dialect is detected from the schema metadata, and the generated DDL is dialect-correct:
| Concern | PostgreSQL | MySQL | SQLite |
|---|---|---|---|
| Auto-increment PK | SERIAL | AUTO_INCREMENT | INTEGER PRIMARY KEY AUTOINCREMENT |
| Enum field | TEXT CHECK (col IN (…)) | native ENUM(…) | TEXT CHECK (col IN (…)) |
| JSON field | JSONB | JSON | TEXT |
| Foreign keys | ALTER TABLE … ADD FOREIGN KEY | same | declared inline (no ALTER ADD) |
Foreign keys are emitted as trailing statements so table creation order never matters.
CRUD Operations
Finding Records
#![allow(unused)]
fn main() {
// Find by ID - returns Option<Model>
let user = Users::get_by_id(123).await?;
// Find first record
let first_user = Users::first().await?;
// Find all records (use with caution!)
let all_users = Users::all().await?;
// Find with conditions
let active_users = Users::query()?
.where_eq("is_active", true)
.get()
.await?;
}
Creating Records
#![allow(unused)]
fn main() {
// Using the builder pattern (recommended)
let new_user = Users::builder()
.email("user@example.com")
.username("johndoe")
.is_active(true)
.save() // Saves directly to database
.await?;
// Manual creation (for complex cases)
let user = Users {
id: 0, // Will be set by database
email: "user@example.com".to_string(),
username: "johndoe".to_string(),
is_active: true,
created_at: Some(Utc::now()),
updated_at: None,
// ... other fields
};
// Then save using your custom method
}
Updating Records
Models track changes automatically - only modified fields are updated:
#![allow(unused)]
fn main() {
// Find and update
let mut user = Users::get_by_id(123).await?.unwrap();
// Setters automatically track changes
user.set_email("newemail@example.com");
user.set_is_active(false);
// Only updates email and is_active fields
user.update().await?;
// Check if there are changes
if user.has_changes() {
println!("User has unsaved changes");
}
}
Deleting Records
#![allow(unused)]
fn main() {
// Delete by finding first
if let Some(user) = Users::get_by_id(123).await? {
user.delete().await?;
}
// Delete with query
let deleted_count = Users::query()?
.where_eq("is_active", false)
.where_lt("last_login", "2024-01-01")
.delete()
.await?;
}
Query Builder
Basic Queries
#![allow(unused)]
fn main() {
// Simple equality check
let users = Users::query()?
.where_eq("role", "admin")
.get()
.await?;
// Multiple conditions (AND)
let active_admins = Users::query()?
.where_eq("role", "admin")
.where_eq("is_active", true)
.where_not_null("email_verified")
.get()
.await?;
// OR conditions
let users = Users::query()?
.where_eq("role", "admin")
.or_where_eq("role", "moderator")
.get()
.await?;
}
Available Query Methods
WHERE Conditions
where_eq(column, value)- Equal towhere_ne(column, value)- Not equal towhere_gt(column, value)- Greater thanwhere_lt(column, value)- Less thanwhere_gte(column, value)- Greater than or equalwhere_lte(column, value)- Less than or equalwhere_like(column, pattern)- LIKE pattern matchingwhere_not_like(column, pattern)- NOT LIKEwhere_in(column, vec![values])- IN listwhere_not_in(column, vec![values])- NOT INwhere_between(column, start, end)- BETWEEN rangewhere_null(column)- IS NULLwhere_not_null(column)- IS NOT NULL
OR Conditions
All WHERE methods have or_where_* variants:
or_where_eq(column, value)or_where_gt(column, value)or_where_null(column)- etc.
Modifiers
order_by(column, OrderDirection::Asc/Desc)limit(n)offset(n)paginate(page, per_page)
Field Selection
select(&[columns])- Select specific fields instead of SELECT *select_raw(&[expressions])- Select with SQL expressions and aggregationsalias("name")- Set a table alias for complex queries
Grouping
group_by(&[columns])- Group results by specified columns
Execution Methods
get()- Get all matching records as model instancesget_raw()- Get results as JSON (useful for aggregations and JOINs)first()- Get first recordcount()- Count matching recordsexists()- Check if any records existdelete()- Delete matching records
Complex Queries
#![allow(unused)]
fn main() {
// Pagination with conditions
let users = Users::query()?
.where_eq("is_active", true)
.where_like("email", "%@company.com")
.order_by("created_at", OrderDirection::Desc)
.paginate(2, 20) // Page 2, 20 per page
.get()
.await?;
// Count with conditions
let admin_count = Users::query()?
.where_eq("role", "admin")
.where_not_null("verified_at")
.count()
.await?;
// Check existence
let has_admins = Users::query()?
.where_eq("role", "admin")
.exists()
.await?;
// Complex date queries
let recent_users = Users::query()?
.where_gt("created_at", "2024-01-01")
.where_between("age", 18, 65)
.where_in("status", vec!["active", "pending"])
.order_by("created_at", OrderDirection::Desc)
.limit(100)
.get()
.await?;
}
Reusable Query Filters (ModelFilter)
ModelFilter allows you to build reusable query conditions that can be applied to multiple queries. This is especially useful for common filtering patterns across your application.
Basic Usage
#![allow(unused)]
fn main() {
use rustf::models::ModelFilter;
// Create a reusable filter
let active_users = ModelFilter::new()
.where_eq("is_active", true)
.where_not_null("verified_at");
// Apply the same filter to different queries
let count = Users::query()?
.apply_filter(&active_users)
.count()
.await?;
let users = Users::query()?
.apply_filter(&active_users)
.order_by("created_at", OrderDirection::Desc)
.limit(10)
.get()
.await?;
}
Conditional Filter Building
ModelFilter supports conditional building by reassigning the filter variable:
#![allow(unused)]
fn main() {
// Start with an empty filter
let mut filter = ModelFilter::new();
// Always apply base conditions
filter = filter.where_eq("is_active", true);
// Conditionally add filters based on user input
if let Ok(search) = ctx.query_str("search") {
filter = filter.where_like("name", &format!("%{}%", search));
}
if let Ok(role) = ctx.query_str("role") {
filter = filter.where_eq("role", role);
}
if let Ok(min_age) = ctx.query_int("min_age") {
filter = filter.where_gte("age", min_age);
}
// Use the conditionally built filter
let results = Users::query()?
.apply_filter(&filter)
.get()
.await?;
}
Combining Filters
You can combine multiple filters using the and() method:
#![allow(unused)]
fn main() {
// Create base filters
let active_filter = ModelFilter::new()
.where_eq("is_active", true);
let verified_filter = ModelFilter::new()
.where_not_null("email_verified_at")
.where_not_null("phone_verified_at");
// Combine filters
let combined = active_filter.and(verified_filter);
// Use combined filter
let users = Users::query()?
.apply_filter(&combined)
.get()
.await?;
}
Common Filter Patterns
#![allow(unused)]
fn main() {
// Pagination filter with search
let mut filter = ModelFilter::new();
if let Some(search) = search_term {
filter = filter.where_like("name", &format!("%{}%", search));
// Note: OR conditions not yet supported, use multiple queries if needed
}
filter = filter.where_eq("is_active", true);
// Date range filter
let date_filter = ModelFilter::new()
.where_gte("created_at", start_date)
.where_lte("created_at", end_date);
// Complex status filter
let status_filter = ModelFilter::new()
.where_in("status", vec!["active", "pending", "verified"])
.where_not_null("approved_at");
// Null checks filter
let complete_profile = ModelFilter::new()
.where_not_null("email")
.where_not_null("phone")
.where_not_null("address");
}
Reusable Application Filters
Create application-wide filters as functions:
#![allow(unused)]
fn main() {
// In your models or a filters module
impl Users {
pub fn active_filter() -> ModelFilter {
ModelFilter::new()
.where_eq("is_active", true)
.where_not_null("email_verified_at")
}
pub fn admin_filter() -> ModelFilter {
ModelFilter::new()
.where_eq("role", "admin")
.where_eq("is_active", true)
}
}
// Usage
let admins = Users::query()?
.apply_filter(&Users::admin_filter())
.get()
.await?;
}
Advanced Query Features
Selecting Specific Fields
Instead of fetching all columns with SELECT *, you can specify exactly which fields you need:
#![allow(unused)]
fn main() {
// Select specific fields only
let users = Users::query()?
.select(&["id", "name", "email"])
.where_eq("is_active", true)
.get()
.await?;
// With table prefixes (useful for JOINs)
let results = Posts::query()?
.alias("p")
.select(&["p.id", "p.title", "u.name as author_name"])
.join("users AS u", "u.id = p.user_id")
.get_raw()
.await?;
// Raw SQL expressions for aggregations
let stats = Users::query()?
.select_raw(&[
"department",
"COUNT(*) as user_count",
"AVG(salary) as avg_salary"
])
.group_by(&["department"])
.get_raw()
.await?;
}
Table Aliasing
Use alias() to set a table alias, essential for self-joins and improving query readability:
#![allow(unused)]
fn main() {
// Simple aliasing
let users = Users::query()?
.alias("u")
.select(&["u.id", "u.name", "u.email"])
.where_eq("u.is_active", true)
.get()
.await?;
// Self-join for hierarchical data (employee-manager)
let employees = Users::query()?
.alias("emp")
.select(&[
"emp.id as employee_id",
"emp.name as employee_name",
"mgr.id as manager_id",
"mgr.name as manager_name"
])
.left_join("users AS mgr", "mgr.id = emp.manager_id")
.get_raw()
.await?;
}
Advanced JOIN Queries
#![allow(unused)]
fn main() {
// Simple JOIN
let posts_with_authors = Posts::query()?
.join("users", "users.id = posts.user_id")
.where_eq("users.is_active", true)
.get()
.await?;
// LEFT JOIN with aliases to avoid column conflicts
let posts = Posts::query()?
.alias("p")
.select(&[
"p.id as post_id",
"p.title",
"p.created_at as post_date",
"u.id as user_id",
"u.name as author_name",
"COUNT(c.id) as comment_count"
])
.join("users AS u", "u.id = p.user_id")
.left_join("comments AS c", "c.post_id = p.id")
.where_eq("p.is_published", true)
.group_by(&["p.id", "p.title", "p.created_at", "u.id", "u.name"])
.get_raw()
.await?;
}
Handling Column Name Conflicts in JOINs
When joining tables with identical column names (like id, created_at), always use column aliases:
#![allow(unused)]
fn main() {
// ❌ BAD: Ambiguous column names
let results = Posts::query()?
.join("users", "users.id = posts.user_id")
.get_raw()
.await?;
// Which 'id' is which? posts.id or users.id?
// ✅ GOOD: Clear column aliases
let results = Posts::query()?
.alias("p")
.select(&[
"p.id as post_id",
"p.created_at as post_date",
"u.id as user_id",
"u.created_at as user_joined"
])
.join("users AS u", "u.id = p.user_id")
.get_raw()
.await?;
}
GROUP BY and Aggregations
Perform aggregate queries using group_by() with select_raw():
#![allow(unused)]
fn main() {
// Department statistics
let dept_stats = Users::query()?
.alias("u")
.select_raw(&[
"u.department",
"COUNT(*) as total_users",
"AVG(u.salary) as avg_salary",
"MAX(u.salary) as max_salary",
"MIN(u.salary) as min_salary"
])
.where_eq("u.is_active", true)
.group_by(&["u.department"])
.order_by("total_users", OrderDirection::Desc)
.get_raw()
.await?;
// Posts per user with filtering
let user_post_counts = Users::query()?
.alias("u")
.select_raw(&[
"u.id",
"u.name",
"COUNT(p.id) as post_count",
"MAX(p.created_at) as latest_post"
])
.left_join("posts AS p", "p.user_id = u.id AND p.is_published = true")
.group_by(&["u.id", "u.name"])
.order_by("post_count", OrderDirection::Desc)
.limit(10)
.get_raw()
.await?;
}
Working with Raw Query Results
The get_raw() method returns Vec<serde_json::Value> for flexible result handling:
#![allow(unused)]
fn main() {
// Execute raw query
let results = Users::query()?
.alias("u")
.select_raw(&[
"u.department",
"COUNT(*) as count",
"AVG(u.salary) as avg_salary"
])
.group_by(&["u.department"])
.get_raw()
.await?;
// Loop over results
for row in &results {
let dept = row["department"].as_str().unwrap_or("Unknown");
let count = row["count"].as_i64().unwrap_or(0);
let avg = row["avg_salary"].as_f64().unwrap_or(0.0);
println!("Department: {} - {} users, avg salary: ${:.2}", dept, count, avg);
}
// Transform for view consumption
let view_data = results.iter().map(|row| {
json!({
"department": row["department"],
"userCount": row["count"],
"avgSalary": format!("${:.2}", row["avg_salary"].as_f64().unwrap_or(0.0))
})
}).collect::<Vec<_>>();
// Pass to controller view
ctx.view("departments", json!({
"stats": view_data,
"total": results.len()
}))
}
Combining Filters with Complex Queries
ModelFilter works seamlessly with all the new query features:
#![allow(unused)]
fn main() {
// Create reusable filter
let active_filter = ModelFilter::new()
.where_eq("u.is_active", true)
.where_not_null("u.verified_at");
// Use with SELECT and JOIN
let users = Users::query()?
.alias("u")
.select(&["u.id", "u.name", "COUNT(p.id) as posts"])
.apply_filter(&active_filter)
.left_join("posts AS p", "p.user_id = u.id")
.group_by(&["u.id", "u.name"])
.get_raw()
.await?;
}
Working with Models
Field Access
Generated models provide getters and setters:
#![allow(unused)]
fn main() {
let user = Users::get_by_id(123).await?.unwrap();
// Getters
let email = user.email(); // &str for String fields
let id = user.id(); // i32/i64 for ID
let active = user.is_active(); // bool
let status = user.status(); // Option<&str> for enum
let created = user.created_at(); // Option<DateTime<Utc>>
// Setters (track changes automatically)
let mut user = user;
user.set_email("new@example.com");
user.set_is_active(false);
// Enum setters - PostgreSQL type casting is automatic
user.set_status(Some(Users::STATUS_ACTIVE)); // Uses constant
user.set_role("admin"); // Direct string also works
// Check what changed
if user.is_changed("email") {
println!("Email was modified");
}
let changed_fields = user.changed_fields(); // Vec<String>
}
Working with NULL Values
#![allow(unused)]
fn main() {
// Setting NULL values
user.set_email_verified(None); // Sets to NULL
user.set_email_verified(Some(Utc::now())); // Sets value
// Checking for NULL
let users = Users::query()?
.where_null("email_verified")
.get()
.await?;
}
Working with Enums
RustF provides intelligent enum handling with automatic PostgreSQL type casting:
#![allow(unused)]
fn main() {
// Enum constants are generated for each enum field
user.set_status(Some(Users::STATUS_ACTIVE)); // "active"
user.set_role(Users::ROLE_ADMIN); // "admin"
// PostgreSQL setters automatically handle type casting
// The setter adds ::enum_type_name suffix when needed
user.set_status(Some("active")); // Becomes "active::status_enum" for PostgreSQL
// For query builders, use field-specific converter methods
let active_users = Users::query()?
.where_eq("status", Users::as_status_enum("active"))
.get()
.await?;
// Converter methods handle database-specific requirements:
// PostgreSQL: Returns "active::status_enum"
// MySQL/SQLite: Returns "active" (pass-through)
let admins = Users::query()?
.where_eq("role", Users::as_role_enum("admin"))
.where_eq("status", Users::as_status_enum("active"))
.get()
.await?;
}
Enum Constants
Generated models include constants for all enum values:
#![allow(unused)]
fn main() {
// Generated constants in your model
pub const STATUS_ACTIVE: &'static str = "active";
pub const STATUS_INACTIVE: &'static str = "inactive";
pub const STATUS_PENDING: &'static str = "pending";
pub const ROLE_ADMIN: &'static str = "admin";
pub const ROLE_USER: &'static str = "user";
pub const ROLE_MODERATOR: &'static str = "moderator";
}
Enum Converter Methods
Each enum field gets a converter method for query builder compatibility:
#![allow(unused)]
fn main() {
// Generated converter methods
pub fn as_status_enum(value: &str) -> String {
// PostgreSQL: Adds type suffix
// MySQL/SQLite: Pass-through
}
pub fn as_role_enum(value: &str) -> String {
// Database-specific handling
}
}
Custom Business Logic
Add your methods to the wrapper model:
#![allow(unused)]
fn main() {
// src/models/users.rs
impl Users {
// Custom finder
pub async fn find_by_email(email: &str) -> Result<Option<Self>> {
Self::query()?
.where_eq("email", email)
.first()
.await
}
// Business logic
pub async fn verify_email(&mut self) -> Result<()> {
self.set_email_verified(Some(Utc::now()));
self.set_is_active(true);
self.update().await
}
// Complex queries
pub async fn find_inactive_users(days: i64) -> Result<Vec<Self>> {
let cutoff = Utc::now() - Duration::days(days);
Self::query()?
.where_eq("is_active", false)
.where_lt("last_login", cutoff.to_rfc3339())
.get()
.await
}
}
}
Type Safety
Using Type Constants
Instead of magic strings, use generated type constants:
#![allow(unused)]
fn main() {
// ❌ Avoid
let users = Users::query()?.where_eq("email", email).get().await?;
// ✅ Prefer (compile-time checked)
let users = Users::query()?
.where_eq(Users::columns::EMAIL, email)
.get()
.await?;
// For enums, use converter methods in queries
// ❌ Avoid - Won't work correctly with PostgreSQL
let users = Users::query()?
.where_eq("status", "active")
.get()
.await?;
// ✅ Correct - Handles database-specific enum types
let users = Users::query()?
.where_eq("status", Users::as_status_enum("active"))
.get()
.await?;
}
SqlValue Conversions
The query builder accepts many types through Into<SqlValue>:
#![allow(unused)]
fn main() {
// All of these work
query.where_eq("age", 25); // i32
query.where_eq("age", 25i64); // i64
query.where_eq("name", "John"); // &str
query.where_eq("name", String::from("John")); // String
query.where_eq("name", &name); // &String (reference)
query.where_eq("active", true); // bool
query.where_eq("created", Utc::now()); // DateTime
query.where_eq("data", json!({"key": "value"})); // JSON
}
Counting and Aggregation
Count Operations
#![allow(unused)]
fn main() {
// Count all records
let total = Users::count().await?;
// Count with conditions
let active_count = Users::query()?
.where_eq("is_active", true)
.count()
.await?;
// Count by group (using raw SQL for now)
// Future: Add group_by support
}
Existence Checks
#![allow(unused)]
fn main() {
// Check if any users exist
let has_users = Users::query()?.exists().await?;
// Check with conditions
let has_admins = Users::query()?
.where_eq("role", "admin")
.exists()
.await?;
}
Pagination
RustF provides two approaches to pagination:
1. Simple Pagination (Legacy)
#![allow(unused)]
fn main() {
// Simple pagination
let page = 2;
let per_page = 20;
let users = Users::paginate(page, per_page).await?;
// Pagination with conditions
let active_users = Users::query()?
.where_eq("is_active", true)
.order_by("created_at", OrderDirection::Desc)
.paginate(page, per_page)
.get_all()
.await?;
// Manual limit/offset
let users = Users::query()?
.limit(20)
.offset(40) // Skip first 40
.get_all()
.await?;
}
2. Pagination with Metadata (Recommended)
The .get_paginated() method returns a PagedResult<T> that includes both the data and pagination metadata:
#![allow(unused)]
fn main() {
use rustf::prelude::*;
// Get paginated results with metadata
let result = Users::query()?
.where_eq("is_active", true)
.order_by("created_at", OrderDirection::Desc)
.get_paginated(2, 20)
.await?;
// Access the data
for user in &result.rows {
println!("User: {}", user.name);
}
// Access pagination metadata
println!("Page {} of {}", result.page, result.total_pages);
println!("Total rows: {}", result.total_rows);
println!("Has next page: {}", result.has_next);
println!("Has previous page: {}", result.has_prev);
// Convert to Pagination for templates
let pagination = U::pagination_from_paged_result(&result, "/users?page={0}");
// Use in view
ctx.view("users/list", json!({
"users": result.rows,
"pagination": pagination.to_json()
}))
}
PagedResult Structure
#![allow(unused)]
fn main() {
pub struct PagedResult<T> {
pub rows: Vec<T>, // The actual data rows for the current page
pub page: u32, // Current page number (1-based)
pub per_page: u32, // Number of items per page
pub total_rows: i64, // Total number of rows matching the query
pub total_pages: u32, // Total number of pages
pub has_next: bool, // Whether there is a next page
pub has_prev: bool, // Whether there is a previous page
}
}
Helper Methods
#![allow(unused)]
fn main() {
// Check if first/last page
if result.is_first() { /* ... */ }
if result.is_last() { /* ... */ }
// Get next/previous page numbers
if let Some(next_page) = result.next_page() {
println!("Next page: {}", next_page);
}
if let Some(prev_page) = result.prev_page() {
println!("Previous page: {}", prev_page);
}
}
Converting to Template Pagination
To use with templates, convert PagedResult to Pagination using U::pagination_from_paged_result():
#![allow(unused)]
fn main() {
// Convert for template rendering
let pagination = U::pagination_from_paged_result(&result, "/users?page={0}");
// Or use the direct function
use rustf::utils::pagination;
let pagination = pagination::pagination_from_paged_result(&result, "/users?page={0}");
}
This separation keeps database concerns (PagedResult) separate from template concerns (Pagination).
Error Handling
All database operations return Result<T>:
#![allow(unused)]
fn main() {
use rustf::Result;
async fn get_user(id: i32) -> Result<Users> {
match Users::get_by_id(id).await? {
Some(user) => Ok(user),
None => Err(rustf::Error::ModelNotFound(format!("User {} not found", id))),
}
}
// Handle different cases
match Users::get_by_id(id).await {
Ok(Some(user)) => {
// User found
},
Ok(None) => {
// User not found
},
Err(e) => {
// Database error
log::error!("Database error: {}", e);
}
}
}
Best Practices
1. Use Query Builder Over Raw SQL
#![allow(unused)]
fn main() {
// ✅ Good - Type-safe, database-agnostic
let users = Users::query()?
.where_eq("is_active", true)
.get()
.await?;
// ⚠️ Escape hatch - only when the builder can't express the query.
// Always parameterize (never interpolate) to avoid SQL injection. The SQL is
// yours to keep portable: placeholder syntax is dialect-specific (`?` for
// MySQL/SQLite, `$1` for PostgreSQL).
let users = DB::fetch_all_with_params(
"SELECT * FROM users WHERE is_active = ?",
vec![true.into()],
).await?;
}
2. Always Check Option Returns
#![allow(unused)]
fn main() {
// ✅ Good - Handle None case
if let Some(user) = Users::get_by_id(id).await? {
// Work with user
} else {
// Handle not found
}
// ❌ Bad - Will panic on None
let user = Users::get_by_id(id).await?.unwrap();
}
3. Use Transactions for Multiple Operations
#![allow(unused)]
fn main() {
// Future: Transaction support planned
// For now, ensure operations are idempotent
}
4. Leverage Change Tracking
#![allow(unused)]
fn main() {
// ✅ Good - Only updates changed fields
let mut user = Users::get_by_id(id).await?.unwrap();
user.set_email("new@example.com");
user.update().await?; // Only updates email
// ❌ Less efficient - Updates all fields
// (Would update everything if using a different ORM)
}
5. Use Builder Pattern for Creation
#![allow(unused)]
fn main() {
// ✅ Good - Clear, validated
let user = Users::builder()
.email("user@example.com")
.username("johndoe")
.status(Some(Users::STATUS_ACTIVE)) // Use enum constants
.role(Users::ROLE_USER)
.save()
.await?;
// ❌ More verbose - Manual struct creation
let user = Users { /* all fields */ };
}
6. Use Enum Converter Methods in Queries
#![allow(unused)]
fn main() {
// ✅ Good - Works across all databases
let active_admins = Users::query()?
.where_eq("status", Users::as_status_enum("active"))
.where_eq("role", Users::as_role_enum("admin"))
.get()
.await?;
// ❌ Bad - May fail with PostgreSQL enums
let active_admins = Users::query()?
.where_eq("status", "active") // Missing type suffix for PostgreSQL
.where_eq("role", "admin")
.get()
.await?;
// ✅ Good - Using constants with converter
let pending = Users::query()?
.where_eq("status", Users::as_status_enum(Users::STATUS_PENDING))
.get()
.await?;
}
Configuration Access
Use the global CONF for configuration values:
#![allow(unused)]
fn main() {
use rustf::CONF;
// Get database URL
let db_url = CONF::get_string("database.url");
// Get with default
let max_conn = CONF::get_or("database.max_connections", 10);
// Check feature flags
if CONF::get_bool("features.advanced_search").unwrap_or(false) {
// Advanced search enabled
}
}
Performance Tips
-
Use
exists()instead ofcount() > 0#![allow(unused)] fn main() { // ✅ Faster let has_users = Users::query()?.exists().await?; // ❌ Slower let has_users = Users::query()?.count().await? > 0; } -
Select only needed columns
#![allow(unused)] fn main() { // ✅ Good - Only fetch what you need let users = Users::query()? .select(&["id", "name", "email"]) .where_eq("is_active", true) .get() .await?; // ❌ Avoid - Fetches all columns let users = Users::query()? .where_eq("is_active", true) .get() .await?; } -
Use pagination for large datasets
#![allow(unused)] fn main() { // ✅ Good let users = Users::paginate(1, 100).await?; // ❌ Bad for large tables let all_users = Users::all().await?; } -
Batch operations when possible
#![allow(unused)] fn main() { // Future: Bulk insert/update support }
Debugging
Enable SQL Logging
# In config.toml
[logging]
level = "debug" # Shows SQL queries
Inspect Generated SQL
#![allow(unused)]
fn main() {
// Get the SQL without executing
let (sql, params) = Users::query()?
.where_eq("email", "test@example.com")
.to_sql()?;
println!("SQL: {}", sql);
println!("Params: {:?}", params);
}
Multi-Database Support
RustF automatically handles dialect differences:
#![allow(unused)]
fn main() {
// This works on PostgreSQL, MySQL, and SQLite
let users = Users::query()?
.where_like("email", "%@example.com")
.limit(10)
.get()
.await?;
// Behind the scenes:
// PostgreSQL: "email LIKE $1 LIMIT 10"
// MySQL: "email LIKE ? LIMIT 10"
// SQLite: "email LIKE ? LIMIT 10"
}
Limitations & Future Features
Current Limitations
- No transaction support yet
- Raw parameterized SQL is available cross-dialect via
DB::execute_with_params/DB::fetch_all_with_params, but the SQL itself (placeholders, functions) is dialect-specific - No HAVING clause support (GROUP BY is supported)
- Limited aggregate function helpers (use select_raw() for now)
Coming Soon
- Transaction support
- Migrations integration
- Bulk insert/update
- Query caching
- Connection retry logic
Summary
RustF’s database layer provides:
- Zero boilerplate - No connection passing
- Type safety - Compile-time query validation
- Smart updates - Change tracking built-in
- Multi-database - Write once, run anywhere
- Developer friendly - Intuitive API inspired by Laravel
Start with schemas, generate models, and enjoy a modern database experience in Rust!# 🗄️ RustF Multi-Database Query Builder
A type-safe, AI-friendly SQL query builder for Rust that supports PostgreSQL, MySQL, MariaDB, and SQLite with a unified API. Part of the RustF web framework ecosystem.
📖 Complete Guide: For comprehensive documentation including model generation, CLI workflows, and AI agent guidance, see the CLI section and Schemas Guide
🌟 Features
- 🚀 Type-safe query building with compile-time validation
- 🔄 Multi-database support (PostgreSQL, MySQL, MariaDB, SQLite)
- 🤖 AI-friendly design with predictable method names and clear error messages
- 📝 Automatic SQL dialect handling for cross-database compatibility
- 🛡️ SQL injection protection through parameterized queries
- 📊 Schema builder with database-specific type mapping
- 🔍 Comprehensive WHERE clauses including LIKE, IN, BETWEEN, NULL checks
- 🔗 All JOIN types (INNER, LEFT, RIGHT, FULL, CROSS)
- 📄 Built-in pagination and aggregation helpers
- ⚡ Zero-cost abstractions leveraging Rust’s type system
- 🏗️ Framework integration with RustF model system and CLI tools
Table of Contents
- Installation
- Quick Start
- Database Connection
- Query Building
- Schema Builder
- Error Handling
- Database-Specific Features
- Examples
- API Reference
Installation
Add this to your Cargo.toml:
[dependencies]
query-builder = "0.1.0"
sqlx = { version = "0.8", features = ["postgres", "mysql", "sqlite", "runtime-tokio-rustls", "uuid", "chrono", "rust_decimal"] }
tokio = { version = "1", features = ["full"] }
thiserror = "1.0"
serde = { version = "1.0", features = ["derive"] }
serde_json = "1.0"
async-trait = "0.1"
⚡ Quick Start
1. Using with RustF Models (Recommended)
#![allow(unused)]
fn main() {
use rustf::models::Users;
// Simple model operations
let user = Users::get_by_id(123).await?;
let admins = Users::query()?.where_eq("role", "admin").get().await?;
// Model-scoped query builder
let active_users = Users::query()?
.where_eq("is_active", true)
.where_gt("age", 18)
.where_like("email", "%@gmail.com")
.order_by("created_at", OrderDirection::Desc)
.limit(10)
.get()
.await?;
}
2. Direct Query Builder Usage
use rustf::models::{AnyDatabase, QueryBuilder, OrderDirection};
#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
// Connect to any supported database
let db = AnyDatabase::connect("postgresql://localhost/myapp").await?;
// Build and execute a query
let (sql, params) = db.query()
.from("users")
.where_gt("age", 18)
.where_like("email", "%@gmail.com")
.order_by("created_at", OrderDirection::Desc)
.limit(10)
.build()?;
println!("Generated SQL: {}", sql);
println!("Parameters: {:?}", params);
Ok(())
}
3. Generate Models from Schema
# Create schema file
cat > schemas/users.yaml << EOF
name: users
table: users
fields:
id: { type: integer, primary_key: true, auto_increment: true }
name: { type: string, max_length: 100, required: true }
email: { type: string, max_length: 255, unique: true, required: true }
created_at: { type: timestamp, default: now }
EOF
# Generate models
rustf-cli schema generate models --schema-path schemas --output src/models
Database Connection
The library automatically detects the database type from the connection URL:
#![allow(unused)]
fn main() {
// PostgreSQL
let pg_db = AnyDatabase::connect("postgresql://user:pass@localhost/dbname").await?;
// MySQL
let mysql_db = AnyDatabase::connect("mysql://user:pass@localhost/dbname").await?;
// SQLite
let sqlite_db = AnyDatabase::connect("sqlite://path/to/database.db").await?;
// In-memory SQLite
let memory_db = AnyDatabase::connect("sqlite::memory:").await?;
}
Query Building
Basic Queries
#![allow(unused)]
fn main() {
// Select all columns
let query = db.query()
.from("users")
.build()?;
// SELECT * FROM "users" (PostgreSQL)
// SELECT * FROM `users` (MySQL)
// Select specific columns
let query = db.query()
.select(vec!["id", "name", "email"])
.from("users")
.build()?;
// SELECT id, name, email FROM "users"
// Count query
let count = db.query()
.from("users")
.count()
.build()?;
// SELECT COUNT(*) FROM "users"
}
WHERE Conditions
The library provides a comprehensive set of WHERE operations:
#![allow(unused)]
fn main() {
// Basic comparisons
query.where_eq("status", "active") // WHERE status = ?
query.where_ne("status", "deleted") // WHERE status <> ?
query.where_gt("age", 18) // WHERE age > ?
query.where_gte("age", 18) // WHERE age >= ?
query.where_lt("price", 100) // WHERE price < ?
query.where_lte("price", 100) // WHERE price <= ?
// Pattern matching
query.where_like("email", "%@gmail.com") // WHERE email LIKE ?
query.where_not_like("email", "%spam%") // WHERE email NOT LIKE ?
// IN clauses
query.where_in("status", vec!["active", "pending"]) // WHERE status IN (?, ?)
query.where_not_in("role", vec!["admin", "super"]) // WHERE role NOT IN (?, ?)
// NULL checks
query.where_null("deleted_at") // WHERE deleted_at IS NULL
query.where_not_null("verified_at") // WHERE verified_at IS NOT NULL
// Range queries
query.where_between("age", 18, 65) // WHERE age BETWEEN ? AND ?
// OR conditions
query.where_eq("status", "active")
.or_where_eq("role", "admin") // WHERE status = ? OR role = ?
// Raw SQL conditions
query.where_raw("(status = 'active' OR created_at > NOW() - INTERVAL '1 day')")
}
Combining Conditions
#![allow(unused)]
fn main() {
let users = db.query()
.from("users")
.where_eq("active", true)
.where_gte("age", 18)
.where_like("email", "%@%")
.where_not_null("verified_at")
.build()?;
// WHERE active = ? AND age >= ? AND email LIKE ? AND verified_at IS NOT NULL
}
JOIN Operations
All standard SQL joins are supported:
#![allow(unused)]
fn main() {
// INNER JOIN
query.join("posts", "posts.user_id = users.id")
// LEFT JOIN
query.left_join("posts", "posts.user_id = users.id")
// RIGHT JOIN (not supported in SQLite)
query.right_join("posts", "posts.user_id = users.id")?
// FULL JOIN (not supported in MySQL/MariaDB)
query.full_join("posts", "posts.user_id = users.id")?
// CROSS JOIN
query.cross_join("categories")
// Complex join example
let results = db.query()
.select(vec!["u.name", "u.email", "COUNT(p.id) as post_count"])
.from("users u")
.left_join("posts p", "p.user_id = u.id")
.where_eq("u.active", true)
.group_by(vec!["u.id", "u.name", "u.email"])
.having("COUNT(p.id)", ">", 5)
.order_by("post_count", OrderDirection::Desc)
.build()?;
}
Aggregations
Built-in aggregation helpers:
#![allow(unused)]
fn main() {
// Simple count
let count = db.query()
.from("users")
.count()
.where_eq("active", true)
.build()?;
// Count specific column
let count = db.query()
.from("orders")
.count_column("DISTINCT user_id")
.build()?;
// Multiple aggregations
let stats = db.query()
.from("orders")
.aggregate(vec![
("COUNT", "*"),
("SUM", "total"),
("AVG", "total"),
("MAX", "total"),
("MIN", "total")
])
.where_gte("created_at", "2024-01-01")
.group_by(vec!["product_id"])
.build()?;
}
Ordering and Limiting
#![allow(unused)]
fn main() {
// Simple ordering
query.order_by("created_at", OrderDirection::Desc)
// Multiple order by
query.order_by_multiple(vec![
("status", OrderDirection::Asc),
("created_at", OrderDirection::Desc)
])
// Limiting results
query.limit(10)
query.offset(20)
// Pagination helper
query.paginate(2, 20) // Page 2, 20 items per page
// Automatically calculates: LIMIT 20 OFFSET 20
}
Schema Builder
Create database tables with automatic type mapping:
#![allow(unused)]
fn main() {
use query_builder::{SchemaBuilder, DatabaseBackend};
let schema = SchemaBuilder::new(DatabaseBackend::Postgres);
let sql = schema.create_table("users")
.id() // SERIAL PRIMARY KEY
.string("email", Some(255))
.not_null()
.unique() // VARCHAR(255) NOT NULL UNIQUE
.string("name", Some(100)) // VARCHAR(100)
.boolean("active")
.default("TRUE") // BOOLEAN DEFAULT TRUE
.timestamp("created_at")
.default("CURRENT_TIMESTAMP") // TIMESTAMP DEFAULT CURRENT_TIMESTAMP
.build();
// Different databases get appropriate types:
// PostgreSQL: SERIAL, VARCHAR, BOOLEAN, TIMESTAMP WITH TIME ZONE
// MySQL: INT AUTO_INCREMENT, VARCHAR, TINYINT(1), DATETIME
// SQLite: INTEGER PRIMARY KEY AUTOINCREMENT, TEXT, INTEGER, TEXT
}
Available Column Types
#![allow(unused)]
fn main() {
.id() // Auto-incrementing primary key
.uuid("column_name") // UUID (or equivalent)
.string("column_name", Some(255)) // Variable-length string
.boolean("column_name") // Boolean type
.timestamp("column_name") // Timestamp/datetime
.integer("column_name") // Integer
.float("column_name") // Floating point
.text("column_name") // Long text
}
Column Modifiers
#![allow(unused)]
fn main() {
.not_null() // NOT NULL constraint
.unique() // UNIQUE constraint
.default("value") // DEFAULT value
.primary_key() // PRIMARY KEY constraint
}
Error Handling
The library provides AI-friendly error messages:
#![allow(unused)]
fn main() {
match result {
Err(QueryError::MissingClause { clause }) => {
// "Missing required clause: from. Add .from() to your query."
},
Err(QueryError::UnsupportedFeature { backend, feature }) => {
// "Feature not supported in PostgreSQL: RIGHT JOIN"
},
Err(QueryError::InvalidColumn { column, available }) => {
// "Invalid column name: 'usr_name'. Available columns: ['id', 'name', 'email']"
},
_ => {}
}
}
Database-Specific Features
The library automatically handles database differences:
Placeholders
- PostgreSQL:
$1, $2, $3 - MySQL/SQLite:
?, ?, ?
Identifier Quoting
- PostgreSQL/SQLite:
"column_name" - MySQL/MariaDB:
`column_name`
RETURNING Clause
- Supported: PostgreSQL, SQLite
- Not supported: MySQL/MariaDB
Boolean Types
- PostgreSQL:
BOOLEAN - MySQL/MariaDB:
TINYINT(1) - SQLite:
INTEGER
Examples
Example 1: User Authentication Query
#![allow(unused)]
fn main() {
let user = db.query()
.select(vec!["id", "email", "password_hash", "role"])
.from("users")
.where_eq("email", email)
.where_eq("active", true)
.where_null("deleted_at")
.build()?;
}
Example 2: Search with Pagination
#![allow(unused)]
fn main() {
let search_term = "%rust%";
let page = 2;
let posts = db.query()
.select(vec!["id", "title", "content", "author_id", "created_at"])
.from("posts")
.where_like("title", &search_term)
.or_where_like("content", &search_term)
.where_eq("published", true)
.order_by("created_at", OrderDirection::Desc)
.paginate(page, 20)
.build()?;
}
Example 3: Dashboard Statistics
#![allow(unused)]
fn main() {
let stats = db.query()
.from("orders")
.aggregate(vec![
("COUNT", "*"),
("SUM", "total_amount"),
("AVG", "total_amount")
])
.where_between("created_at", start_date, end_date)
.where_eq("status", "completed")
.group_by(vec!["DATE(created_at)"])
.order_by("DATE(created_at)", OrderDirection::Asc)
.build()?;
}
Example 4: Complex Report Query
#![allow(unused)]
fn main() {
let report = db.query()
.select(vec![
"c.name as category",
"p.name as product",
"SUM(oi.quantity) as total_sold",
"SUM(oi.quantity * oi.price) as revenue"
])
.from("order_items oi")
.join("orders o", "o.id = oi.order_id")
.join("products p", "p.id = oi.product_id")
.join("categories c", "c.id = p.category_id")
.where_eq("o.status", "completed")
.where_between("o.created_at", "2024-01-01", "2024-12-31")
.group_by(vec!["c.id", "c.name", "p.id", "p.name"])
.having("SUM(oi.quantity)", ">", 100)
.order_by("revenue", OrderDirection::Desc)
.limit(50)
.build()?;
}
API Reference
QueryBuilder Methods
| Method | Description | Example |
|---|---|---|
select(columns) | Select specific columns | .select(vec!["id", "name"]) |
from(table) | Specify table | .from("users") |
where_* | WHERE conditions | .where_eq("status", "active") |
or_where_* | OR WHERE conditions | .or_where_eq("role", "admin") |
join(table, on) | INNER JOIN | .join("posts", "posts.user_id = users.id") |
left_join(table, on) | LEFT JOIN | .left_join("posts", "posts.user_id = users.id") |
group_by(columns) | GROUP BY | .group_by(vec!["user_id"]) |
having(col, op, val) | HAVING clause | .having("COUNT(*)", ">", 5) |
order_by(col, dir) | ORDER BY | .order_by("created_at", OrderDirection::Desc) |
limit(n) | LIMIT results | .limit(10) |
offset(n) | OFFSET results | .offset(20) |
distinct() | SELECT DISTINCT | .distinct() |
count() | COUNT(*) | .count() |
paginate(page, per_page) | Pagination helper | .paginate(2, 20) |
WHERE Condition Methods
| Method | SQL Equivalent |
|---|---|
where_eq(col, val) | WHERE col = val |
where_ne(col, val) | WHERE col <> val |
where_gt(col, val) | WHERE col > val |
where_gte(col, val) | WHERE col >= val |
where_lt(col, val) | WHERE col < val |
where_lte(col, val) | WHERE col <= val |
where_like(col, pattern) | WHERE col LIKE pattern |
where_not_like(col, pattern) | WHERE col NOT LIKE pattern |
where_in(col, values) | WHERE col IN (values) |
where_not_in(col, values) | WHERE col NOT IN (values) |
where_between(col, start, end) | WHERE col BETWEEN start AND end |
where_null(col) | WHERE col IS NULL |
where_not_null(col) | WHERE col IS NOT NULL |
Best Practices
-
Use parameterized queries: The library automatically parameterizes all values to prevent SQL injection.
-
Check feature support: Some features like RIGHT JOIN (SQLite) or FULL JOIN (MySQL) aren’t universally supported. The library returns clear errors for unsupported features.
-
Use type-safe values: Pass proper Rust types that implement
Into<SqlValue>. -
Handle errors appropriately: Use the detailed error types to provide meaningful feedback to users.
-
Leverage the schema builder: Use it to ensure consistent table structures across different databases.
🤖 AI Agent Guidelines
When working with the RustF query builder:
✅ Recommended:
- Use model-scoped queries (
Users::query()) for type safety - Use type constants (
UsersBase::Types::email) instead of hardcoding types - Handle database-specific features gracefully with error checking
- Use the unified
AnyDatabaseAPI for cross-database compatibility
❌ Avoid:
- Hardcoding SQL strings when the query builder can generate them
- Ignoring database feature limitations (RIGHT JOIN in SQLite, etc.)
- Using raw parameters instead of type-safe SqlValue conversion
Example AI-Friendly Error Handling:
#![allow(unused)]
fn main() {
match query.right_join("posts", "posts.user_id = users.id") {
Ok(q) => q,
Err(QueryError::UnsupportedFeature { backend, feature }) => {
// Fallback to LEFT JOIN for compatibility
query.left_join("posts", "posts.user_id = users.id")?
}
}
}
🔗 Related Documentation
- CLI: Database & Schemas - Database tools and code generation
- Schema Format Reference - The full YAML schema specification
- Definitions System - Template helpers and validators
- Cargo Features - Per-driver multi-database setup
🤝 Contributing
Contributions are welcome! Please feel free to submit a Pull Request.
Development Setup
# Clone the repository
git clone https://github.com/rustf/rustf.git
cd rustf
# Run tests
cargo test
# Test with different databases
docker-compose up -d postgres mysql
cargo test --features="postgres,mysql,sqlite"
📄 License
This project is licensed under the MIT License - see the LICENSE file for details.
Pagination Helper Guide
RustF provides a built-in pagination helper through the U::paginate() function that makes it easy to implement pagination in your web applications.
Overview
The pagination helper creates a complete pagination object with:
- Page navigation (first, last, previous, next)
- Page number ranges
- URL generation with customizable patterns
- Template-friendly JSON output
Basic Usage
In Controllers (Legacy Approach)
#![allow(unused)]
fn main() {
use rustf::prelude::*;
async fn list_users(ctx: &mut Context) -> Result<()> {
// Parse page from query parameters
let page = ctx.query_as_or::<u32>("page", 1);
let per_page = 20;
// Get total count from database
let total_users = Users::count().await?;
// Fetch paginated data
let users = Users::paginate(page, per_page).await?;
// Create pagination object
let pagination = U::paginate(
total_users, // Total items
page, // Current page (1-based)
per_page, // Items per page
"/users?page={0}" // URL pattern with {0} placeholder
);
// Pass to view
ctx.view("users/list", json!({
"users": users,
"pagination": pagination.to_json()
}))
}
}
In Controllers (Recommended: Using PagedResult)
#![allow(unused)]
fn main() {
use rustf::prelude::*;
async fn list_users(ctx: &mut Context) -> Result<()> {
// Parse page from query parameters
let page = ctx.query_as_or::<u32>("page", 1);
let per_page = 20;
// Get paginated results with metadata (single method call)
let result = Users::query()?
.where_eq("is_active", true)
.order_by("created_at", OrderDirection::Desc)
.get_paginated(page, per_page)
.await?;
// Convert to Pagination for templates
let pagination = U::pagination_from_paged_result(&result, "/users?page={0}");
// Pass to view
ctx.view("users/list", json!({
"users": result.rows,
"pagination": pagination.to_json()
}))
}
}
Advantages of the recommended approach:
- Single method call (
.get_paginated()) instead of separate count + query - Automatic metadata calculation (total_rows, total_pages, has_next, has_prev)
- Better separation of concerns (database vs template)
- More efficient (parallel execution of COUNT and SELECT queries)
In Templates (Total.js Syntax)
<!-- Basic pagination controls -->
<div class="pagination">
@{if pagination.isPrev}
<a href="@{pagination.prev.url}">Previous</a>
@{fi}
@{foreach page in pagination.range}
@{if page.selected}
<span class="current">@{page.page}</span>
@{else}
<a href="@{page.url}">@{page.page}</a>
@{fi}
@{end}
@{if pagination.isNext}
<a href="@{pagination.next.url}">Next</a>
@{fi}
</div>
Complete Navigation Example
<!-- Full pagination with first/last links -->
<div class="pagination">
<!-- First & Previous -->
@{if !pagination.isFirst}
<a href="@{pagination.first.url}">« First</a>
@{fi}
@{if pagination.isPrev}
<a href="@{pagination.prev.url}">‹ Previous</a>
@{else}
<span class="disabled">‹ Previous</span>
@{fi}
<!-- Page Numbers -->
@{foreach page in pagination.range}
@{if page.selected}
<span class="current">@{page.page}</span>
@{else}
<a href="@{page.url}">@{page.page}</a>
@{fi}
@{end}
<!-- Next & Last -->
@{if pagination.isNext}
<a href="@{pagination.next.url}">Next ›</a>
@{else}
<span class="disabled">Next ›</span>
@{fi}
@{if !pagination.isLast}
<a href="@{pagination.last.url}">Last »</a>
@{fi}
</div>
<!-- Page info -->
<p>Page @{pagination.page} of @{pagination.count}
(@{pagination.items} total items)</p>
Pagination Object Structure
The pagination.to_json() method returns:
{
"items": 157, // Total number of items
"page": 5, // Current page
"count": 16, // Total pages
"per_page": 10, // Items per page
"isFirst": false, // Is first page?
"isLast": false, // Is last page?
"isPrev": true, // Has previous page?
"isNext": true, // Has next page?
"first": {
"url": "/users?page=1"
},
"last": {
"url": "/users?page=16"
},
"prev": {
"url": "/users?page=4"
},
"next": {
"url": "/users?page=6"
},
"range": [ // Page numbers for display
{
"page": 3,
"url": "/users?page=3",
"selected": false
},
{
"page": 4,
"url": "/users?page=4",
"selected": false
},
{
"page": 5,
"url": "/users?page=5",
"selected": true
},
// ... up to 7 pages by default
]
}
Advanced Usage
Custom URL Patterns
#![allow(unused)]
fn main() {
// Simple query parameter
let pagination = U::paginate(total, page, 20, "/posts?page={0}");
// With multiple parameters
let pagination = U::paginate(total, page, 20, "/posts?category=tech&page={0}");
// Path-based pagination
let pagination = U::paginate(total, page, 20, "/posts/page/{0}");
// With hash fragments
let pagination = U::paginate(total, page, 20, "/posts?page={0}#results");
}
Pagination with Filters
#![allow(unused)]
fn main() {
async fn search_posts(ctx: &mut Context) -> Result<()> {
let page = ctx.query_as_or::<u32>("page", 1);
let search = ctx.query_str_or("q", "");
let category = ctx.query_str_or("category", "all");
// Build query with filters
let total = Posts::query()?
.where_like("title", &format!("%{}%", search))
.where_eq("category", category)
.count()
.await?;
let posts = Posts::query()?
.where_like("title", &format!("%{}%", search))
.where_eq("category", category)
.paginate(page, 20)
.get()
.await?;
// Include filters in URL pattern
let url_pattern = format!(
"/search?q={}&category={}&page={{0}}",
U::encode(search),
U::encode(category)
);
let pagination = U::paginate(total, page, 20, &url_pattern);
ctx.view("search-results", json!({
"posts": posts,
"pagination": pagination.to_json(),
"search": search,
"category": category
}))
}
}
Styling Example
.pagination {
display: flex;
justify-content: center;
gap: 10px;
margin: 20px 0;
}
.pagination a,
.pagination span {
padding: 8px 12px;
border: 1px solid #ddd;
border-radius: 4px;
text-decoration: none;
}
.pagination a:hover {
background: #007bff;
color: white;
}
.pagination .current {
background: #007bff;
color: white;
font-weight: bold;
}
.pagination .disabled {
color: #999;
cursor: not-allowed;
}
API Reference
U::paginate()
#![allow(unused)]
fn main() {
pub fn paginate(
total: i64, // Total number of items
page: u32, // Current page (1-based)
per_page: u32, // Items per page
url_pattern: &str // URL pattern with {0} placeholder
) -> Pagination
}
U::pagination_from_paged_result()
Convert a PagedResult from database queries to a Pagination for template rendering:
#![allow(unused)]
fn main() {
pub fn pagination_from_paged_result<T>(
paged_result: &PagedResult<T>, // Paginated result from database query
url_pattern: &str // URL pattern with {0} placeholder
) -> Pagination
}
Example:
#![allow(unused)]
fn main() {
// Get paginated results from database
let result = Users::query()?
.where_eq("is_active", true)
.get_paginated(2, 20)
.await?;
// Convert to Pagination for templates
let pagination = U::pagination_from_paged_result(&result, "/users?page={0}");
// Use in view
ctx.view("users/list", json!({
"users": result.rows,
"pagination": pagination.to_json()
}))
}
This function separates database concerns (PagedResult) from template rendering concerns (Pagination).
Pagination Methods
to_json()- Convert to JSON for template useis_first()- Check if on first pageis_last()- Check if on last pagehas_prev()- Check if previous page existshas_next()- Check if next page existsfirst_url()- Get URL for first pagelast_url()- Get URL for last pageprev_url()- Get URL for previous pagenext_url()- Get URL for next pagerange(max_items)- Get page number range for display
PagedResult Methods
is_first()- Check if this is the first pageis_last()- Check if this is the last pagenext_page()- Get the next page number (if available)prev_page()- Get the previous page number (if available)
Best Practices
- Always validate page numbers - Ensure page is within valid range
- Use reasonable per_page limits - Typically 10-100 items
- Cache total counts - For large datasets, consider caching counts
- Include page info - Show “Page X of Y” for better UX
- Provide direct navigation - Include first/last links for long lists
- Make it accessible - Use proper ARIA labels and semantic HTML
- Handle edge cases - Empty results, single page, invalid page numbers