Struct QueryBuilder 

pub struct QueryBuilder<'a, T, E> { /* private fields */ }

A fluent Query Builder for constructing SQL queries.

QueryBuilder provides a type-safe, ergonomic interface for building and executing SQL queries across different database backends. It supports filtering, ordering, pagination, and both SELECT and INSERT operations.

Type Parameter

• 'a - Lifetime of the database reference (used for PhantomData)
• T - The Model type this query operates on
• E - The connection type (Database or Transaction)

Fields

• db - Reference to the database connection pool or transaction
• table_name - Static string containing the table name
• columns_info - Metadata about each column in the table
• columns - List of column names in snake_case format
• select_columns - Specific columns to select (empty = SELECT *)
• where_clauses - List of filter functions to apply
• order_clauses - List of ORDER BY clauses
• limit - Maximum number of rows to return
• offset - Number of rows to skip (for pagination)
• _marker - PhantomData to bind the generic type T

Implementations

impl<'a, T, E> QueryBuilder<'a, T, E>

where T: Model + Send + Sync + Unpin, E: Connection + Send,

pub fn new( tx: E, driver: Drivers, table_name: &'static str, columns_info: Vec<ColumnInfo>, columns: Vec<String>, ) -> Self

Creates a new QueryBuilder instance.

This constructor is typically called internally via db.model::<T>(). You rarely need to call this directly.

Arguments

• db - Reference to the database connection
• table_name - Name of the table to query
• columns_info - Metadata about table columns
• columns - List of column names

Returns

A new QueryBuilder instance ready for query construction

Example

// Usually called via db.model::<User>()
let query = db.model::<User>();

pub fn filter<V>(self, col: &'static str, op: &'static str, value: V) -> Self

where V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,

Adds a WHERE clause to the query.

This method adds a filter condition to the query. Multiple filters can be chained and will be combined with AND operators. The value is bound as a parameter to prevent SQL injection.

Type Parameters

• V - The type of the value to filter by. Must be encodable for SQL queries.

Arguments

• col - The column name to filter on
• op - The comparison operator (e.g., “=”, “>”, “LIKE”, “IN”)
• value - The value to compare against

Supported Types

• Primitives: i32, i64, f64, bool, String
• UUID: Uuid (all versions 1-7)
• Date/Time: DateTime<Utc>, NaiveDateTime, NaiveDate, NaiveTime
• Options: Option<T> for any supported type T

Example

// Filter by integer
query.filter("age", ">=", 18)

// Filter by string
query.filter("username", "=", "john_doe")

// Filter by UUID
let user_id = Uuid::new_v4();
query.filter("id", "=", user_id)

// Filter with LIKE operator
query.filter("email", "LIKE", "%@example.com")

// Chain multiple filters
query
    .filter("age", ">=", 18)
    .filter("active", "=", true)
    .filter("role", "=", "admin")

pub fn equals<V>(self, col: &'static str, value: V) -> Self

where V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,

Adds an equality filter to the query.

This is a convenience wrapper around filter() for simple equality checks. It is equivalent to calling filter(col, "=", value).

Type Parameters

• V - The type of the value to compare against.

Arguments

• col - The column name to filter on.
• value - The value to match.

Example

// Equivalent to filter("age", "=", 18)
query.equals("age", 18)

pub fn order(self, order: &str) -> Self

Adds an ORDER BY clause to the query.

Specifies the sort order for the query results. Multiple order clauses can be added and will be applied in the order they were added.

Arguments

• order - The ORDER BY expression (e.g., “created_at DESC”, “age ASC, name DESC”)

Example

// Single column ascending (ASC is default)
query.order("age")

// Single column descending
query.order("created_at DESC")

// Multiple columns
query.order("age DESC, username ASC")

// Chain multiple order clauses
query
    .order("priority DESC")
    .order("created_at ASC")

pub fn debug(self) -> Self

Placeholder for eager loading relationships (preload).

This method is reserved for future implementation of relationship preloading. Currently, it returns self unchanged to maintain the fluent interface.

Future Implementation

Will support eager loading of related models to avoid N+1 query problems:

// Future usage example
query.preload("posts").preload("comments")

Activates debug mode for this query.

When enabled, the generated SQL query will be logged using the log crate at the DEBUG level before execution.

Note

To see the output, you must initialize a logger in your application (e.g., using env_logger) and configure it to display debug logs for bottle_orm.

Example

db.model::<User>()
    .filter("active", "=", true)
    .debug() // Logs SQL: SELECT * FROM "user" WHERE "active" = $1
    .scan()
    .await?;

pub fn join(self, table: &str, s_query: &str) -> Self

Placeholder for JOIN operations.

This method is reserved for future implementation of SQL JOINs. Currently, it returns self unchanged to maintain the fluent interface.

Future Implementation

Will support various types of JOINs (INNER, LEFT, RIGHT, FULL):

Adds a JOIN clause to the query.


* `table` - The name of the table to join.
* `s_query` - The ON clause condition (e.g., "users.id = posts.user_id").


```rust,ignore
query.join("posts", "users.id = posts.user_id")

pub fn left_join(self, table: &str, on: &str) -> Self

Adds a LEFT JOIN clause.

Performs a LEFT JOIN with another table. Returns all records from the left table, and the matched records from the right table (or NULL if no match).

Arguments

• table - The name of the table to join with
• on - The join condition (e.g., “users.id = posts.user_id”)

Example

// Get all users and their posts (if any)
let users_with_posts = db.model::<User>()
    .left_join("posts", "users.id = posts.user_id")
    .scan()
    .await?;

pub fn right_join(self, table: &str, on: &str) -> Self

Adds a RIGHT JOIN clause.

Performs a RIGHT JOIN with another table. Returns all records from the right table, and the matched records from the left table (or NULL if no match).

Arguments

• table - The name of the table to join with
• on - The join condition

Example

let posts_with_users = db.model::<Post>()
    .right_join("users", "posts.user_id = users.id")
    .scan()
    .await?;

pub fn inner_join(self, table: &str, on: &str) -> Self

Adds an INNER JOIN clause.

Performs an INNER JOIN with another table. Returns records that have matching values in both tables.

Arguments

• table - The name of the table to join with
• on - The join condition

Example

// Get only users who have posts
let active_users = db.model::<User>()
    .inner_join("posts", "users.id = posts.user_id")
    .scan()
    .await?;

pub fn full_join(self, table: &str, on: &str) -> Self

Adds a FULL JOIN clause.

Performs a FULL OUTER JOIN. Returns all records when there is a match in either left or right table.

Arguments

• table - The name of the table to join with
• on - The join condition

Note

Support for FULL JOIN depends on the underlying database engine (e.g., SQLite does not support FULL JOIN directly).

pub fn distinct(self) -> Self

Marks the query to return DISTINCT results.

Adds the DISTINCT keyword to the SELECT statement, ensuring that unique rows are returned.

Example

// Get unique ages of users
let unique_ages: Vec<i32> = db.model::<User>()
    .select("age")
    .distinct()
    .scan()
    .await?;

pub fn group_by(self, columns: &str) -> Self

Adds a GROUP BY clause to the query.

Groups rows that have the same values into summary rows. Often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG).

Arguments

• columns - Comma-separated list of columns to group by

Example

// Count users by age group
let stats: Vec<(i32, i64)> = db.model::<User>()
    .select("age, COUNT(*)")
    .group_by("age")
    .scan()
    .await?;

pub fn having<V>(self, col: &'static str, op: &'static str, value: V) -> Self

where V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,

Adds a HAVING clause to the query.

Used to filter groups created by group_by. Similar to filter (WHERE), but operates on grouped records and aggregate functions.

Arguments

• col - The column or aggregate function to filter on
• op - Comparison operator
• value - Value to compare against

Example

// Get ages with more than 5 users
let popular_ages = db.model::<User>()
    .select("age, COUNT(*)")
    .group_by("age")
    .having("COUNT(*)", ">", 5)
    .scan()
    .await?;

pub async fn count(self) -> Result<i64, Error>

Returns the COUNT of rows matching the query.

A convenience method that automatically sets SELECT COUNT(*) and returns the result as an i64.

Returns

• Ok(i64) - The count of rows
• Err(sqlx::Error) - Database error

Example

let user_count = db.model::<User>().count().await?;

pub async fn sum<N>(self, column: &str) -> Result<N, Error>

where N: for<'r> Decode<'r, Any> + Type<Any> + Send + Unpin,

Returns the SUM of the specified column.

Calculates the sum of a numeric column.

Arguments

• column - The column to sum

Example

let total_age: i64 = db.model::<User>().sum("age").await?;

pub async fn avg<N>(self, column: &str) -> Result<N, Error>

where N: for<'r> Decode<'r, Any> + Type<Any> + Send + Unpin,

Returns the AVG of the specified column.

Calculates the average value of a numeric column.

Arguments

• column - The column to average

Example

let avg_age: f64 = db.model::<User>().avg("age").await?;

pub async fn min<N>(self, column: &str) -> Result<N, Error>

where N: for<'r> Decode<'r, Any> + Type<Any> + Send + Unpin,

Returns the MIN of the specified column.

Finds the minimum value in a column.

Arguments

• column - The column to check

Example

let min_age: i32 = db.model::<User>().min("age").await?;

pub async fn max<N>(self, column: &str) -> Result<N, Error>

where N: for<'r> Decode<'r, Any> + Type<Any> + Send + Unpin,

Returns the MAX of the specified column.

Finds the maximum value in a column.

Arguments

• column - The column to check

Example

let max_age: i32 = db.model::<User>().max("age").await?;

pub fn pagination( self, max_value: usize, default: usize, page: usize, value: isize, ) -> Result<Self, Error>

Applies pagination with validation and limits.

This is a convenience method that combines limit() and offset() with built-in validation and maximum value enforcement for safer pagination.

Arguments

• max_value - Maximum allowed items per page
• default - Default value if value exceeds max_value
• page - Zero-based page number
• value - Requested items per page

Returns

• Ok(Self) - The updated QueryBuilder with pagination applied
• Err(Error) - If value is negative

Pagination Logic

1. Validates that value is non-negative
2. If value > max_value, uses default instead
3. Calculates offset as: value * page
4. Sets limit to value

Example

// Page 0 with 10 items (page 1 in 1-indexed systems)
query.pagination(100, 20, 0, 10)?  // LIMIT 10 OFFSET 0

// Page 2 with 25 items (page 3 in 1-indexed systems)
query.pagination(100, 20, 2, 25)?  // LIMIT 25 OFFSET 50

// Request too many items, falls back to default
query.pagination(100, 20, 0, 150)? // LIMIT 20 OFFSET 0 (150 > 100)

// Error: negative value
query.pagination(100, 20, 0, -10)? // Returns Error

pub fn select(self, columns: &str) -> Self

Selects specific columns to return.

By default, queries use SELECT * to return all columns. This method allows you to specify exactly which columns should be returned, which can improve performance for tables with many or large columns.

Arguments

• columns - Comma-separated list of column names to select

Example

// Select single column
query.select("id")

// Select multiple columns
query.select("id, username, email")

// Select with SQL functions
query.select("COUNT(*) as total")

// Chain multiple select calls (all will be included)
query
    .select("id, username")
    .select("created_at")

pub fn offset(self, offset: usize) -> Self

Sets the query offset (pagination).

Specifies the number of rows to skip before starting to return rows. Commonly used in combination with limit() for pagination.

Arguments

• offset - Number of rows to skip

Example

// Skip first 20 rows
query.offset(20)

// Pagination: page 3 with 10 items per page
query.limit(10).offset(20)  // Skip 2 pages = 20 items

pub fn limit(self, limit: usize) -> Self

Sets the maximum number of records to return.

Limits the number of rows returned by the query. Essential for pagination and preventing accidentally fetching large result sets.

Arguments

• limit - Maximum number of rows to return

Example

// Return at most 10 rows
query.limit(10)

// Pagination: 50 items per page
query.limit(50).offset(page * 50)

pub fn insert<'b>( &'b mut self, model: &'b T, ) -> BoxFuture<'b, Result<(), Error>>

Inserts a new record into the database based on the model instance.

This method serializes the model into a SQL INSERT statement with proper type handling for primitives, dates, UUIDs, and other supported types.

Type Binding Strategy

The method uses string parsing as a temporary solution for type binding. Values are converted to strings via the model’s to_map() method, then parsed back to their original types for proper SQL binding.

Supported Types for Insert

• Integers: i32, i64 (INTEGER, BIGINT)
• Boolean: bool (BOOLEAN)
• Float: f64 (DOUBLE PRECISION)
• Text: String (TEXT, VARCHAR)
• UUID: Uuid (UUID) - All versions 1-7 supported
• DateTime: DateTime<Utc> (TIMESTAMPTZ)
• NaiveDateTime: (TIMESTAMP)
• NaiveDate: (DATE)
• NaiveTime: (TIME)

Arguments

• model - Reference to the model instance to insert

Returns

• Ok(&Self) - Reference to self for method chaining
• Err(sqlx::Error) - Database error during insertion

Example

use uuid::Uuid;
use chrono::Utc;

let new_user = User {
    id: Uuid::new_v4(),
    username: "john_doe".to_string(),
    email: "john@example.com".to_string(),
    age: 25,
    active: true,
    created_at: Utc::now(),
};

db.model::<User>().insert(&new_user).await?;

pub fn to_sql(&self) -> String

Returns the generated SQL string for debugging purposes.

This method constructs the SQL query string without executing it. Useful for debugging and logging query construction. Note that this shows placeholders (?, $1, etc.) rather than actual bound values.

Returns

A String containing the SQL query that would be executed

Example

let query = db.model::<User>()
    .filter("age", ">=", 18)
    .order("created_at DESC")
    .limit(10);

println!("SQL: {}", query.to_sql());
// Output: SELECT * FROM "user" WHERE 1=1 AND "age" >= $1 ORDER BY created_at DESC

pub async fn scan<R>(self) -> Result<Vec<R>, Error>

where R: for<'r> FromRow<'r, AnyRow> + AnyImpl + Send + Unpin,

Executes the query and returns a list of results.

This method builds and executes a SELECT query with all accumulated filters, ordering, and pagination settings. It returns all matching rows as a vector.

Type Parameters

• R - The result type. Must implement FromRow for deserialization from database rows.

Returns

• Ok(Vec<R>) - Vector of results (empty if no matches)
• Err(sqlx::Error) - Database error during query execution

Example

// Get all adult users, ordered by age, limited to 10
let users: Vec<User> = db.model::<User>()
    .filter("age", ">=", 18)
    .order("age DESC")
    .limit(10)
    .scan()
    .await?;

// Get users by UUID
let user_id = Uuid::parse_str("550e8400-e29b-41d4-a716-446655440000")?;
let users: Vec<User> = db.model::<User>()
    .filter("id", "=", user_id)
    .scan()
    .await?;

// Empty result is Ok
let results: Vec<User> = db.model::<User>()
    .filter("age", ">", 200)
    .scan()
    .await?;  // Returns empty Vec, not an error

pub async fn first<R>(self) -> Result<R, Error>

where R: for<'r> FromRow<'r, AnyRow> + AnyImpl + Send + Unpin,

Executes the query and returns only the first result.

This method automatically adds LIMIT 1 and orders by the Primary Key (if available) to ensure consistent results. It’s optimized for fetching a single row and will return an error if no rows match.

Type Parameters

• R - The result type. Must implement FromRow for deserialization.

Returns

• Ok(R) - The first matching row
• Err(sqlx::Error) - No rows found or database error

Error Handling

Returns sqlx::Error::RowNotFound if no rows match the query. Use scan() instead if you want an empty Vec rather than an error.

Example

// Get a specific user by ID
let user: User = db.model::<User>()
    .filter("id", "=", 1)
    .first()
    .await?;

// Get user by UUID
let user_id = Uuid::new_v4();
let user: User = db.model::<User>()
    .filter("id", "=", user_id)
    .first()
    .await?;

// Get the oldest user
let oldest: User = db.model::<User>()
    .order("age DESC")
    .first()
    .await?;

// Error handling
match db.model::<User>().filter("id", "=", 999).first().await {
    Ok(user) => println!("Found: {:?}", user),
    Err(sqlx::Error::RowNotFound) => println!("User not found"),
    Err(e) => println!("Database error: {}", e),
}

pub async fn scalar<O>(self) -> Result<O, Error>

where O: for<'r> Decode<'r, Any> + Type<Any> + Send + Unpin,

Executes the query and returns a single scalar value.

This method is useful for fetching single values like counts, max/min values, or specific columns without mapping to a struct or tuple.

Type Parameters

• O - The output type. Must implement Decode and Type.

Example

// Get count of users
let count: i64 = db.model::<User>()
    .select("count(*)")
    .scalar()
    .await?;

// Get specific field
let username: String = db.model::<User>()
    .filter("id", "=", 1)
    .select("username")
    .scalar()
    .await?;

pub fn update<'b, V>( &'b mut self, col: &str, value: V, ) -> BoxFuture<'b, Result<u64, Error>>

where V: ToString + Send + Sync,

Updates a single column in the database.

Arguments

• col - The column name to update
• value - The new value

Returns

• Ok(u64) - The number of rows affected

pub fn updates<'b>(&'b mut self, model: &T) -> BoxFuture<'b, Result<u64, Error>>

Updates all columns based on the model instance.

This method updates all active columns of the table with values from the provided model.

Arguments

• model - The model instance containing new values

Returns

• Ok(u64) - The number of rows affected

pub fn update_partial<'b, P: AnyImpl>( &'b mut self, partial: &P, ) -> BoxFuture<'b, Result<u64, Error>>

Updates columns based on a partial model (struct implementing AnyImpl).

This allows updating a subset of columns using a custom struct. The struct must implement AnyImpl (usually via #[derive(FromAnyRow)]).

Arguments

• partial - The partial model containing new values

Returns

• Ok(u64) - The number of rows affected

pub async fn delete(self) -> Result<u64, Error>

Executes a DELETE query based on the current filters.

Returns

• Ok(u64) - The number of rows deleted
• Err(sqlx::Error) - Database error