bottle_orm/query_builder.rs
1//! # Query Builder Module
2//!
3//! This module provides a fluent interface for constructing and executing SQL queries.
4//! It handles SELECT, INSERT, filtering (WHERE), pagination (LIMIT/OFFSET), and ordering operations
5//! with type-safe parameter binding across different database drivers.
6//!
7//! ## Features
8//!
9//! - **Fluent API**: Chainable methods for building complex queries
10//! - **Type-Safe Binding**: Automatic parameter binding with support for multiple types
11//! - **Multi-Driver Support**: Works with PostgreSQL, MySQL, and SQLite
12//! - **UUID Support**: Full support for UUID versions 1-7
13//! - **Pagination**: Built-in LIMIT/OFFSET support with helper methods
14//! - **Custom Filters**: Support for manual SQL construction with closures
15//!
16//! ## Example Usage
17//!
18//! ```rust,ignore
19//! use bottle_orm::{Database, Model};
20//! use uuid::Uuid;
21//!
22//! // Simple query
23//! let users: Vec<User> = db.model::<User>()
24//! .filter("age", ">=", 18)
25//! .order("created_at DESC")
26//! .limit(10)
27//! .scan()
28//! .await?;
29//!
30//! // Query with UUID filter
31//! let user_id = Uuid::new_v4();
32//! let user: User = db.model::<User>()
33//! .filter("id", "=", user_id)
34//! .first()
35//! .await?;
36//!
37//! // Insert a new record
38//! let new_user = User {
39//! id: Uuid::new_v7(uuid::Timestamp::now(uuid::NoContext)),
40//! username: "john_doe".to_string(),
41//! age: 25,
42//! };
43//! db.model::<User>().insert(&new_user).await?;
44//! ```
45
46// ============================================================================
47// External Crate Imports
48// ============================================================================
49
50use heck::ToSnakeCase;
51use sqlx::{
52 any::{AnyArguments, AnyRow},
53 Any, Arguments, Decode, Encode, FromRow, Row, Type,
54};
55use std::marker::PhantomData;
56use uuid::Uuid;
57
58// ============================================================================
59// Internal Crate Imports
60// ============================================================================
61
62use crate::{
63 database::{Connection, Drivers},
64 model::{ColumnInfo, Model},
65 temporal::{self, is_temporal_type},
66 value_binding::ValueBinder,
67 AnyImpl, Error,
68};
69
70// ============================================================================
71// Type Aliases
72// ============================================================================
73
74/// A type alias for filter closures that support manual SQL construction and argument binding.
75///
76/// Filter functions receive the following parameters:
77/// 1. `&mut String` - The SQL query buffer being built
78/// 2. `&mut AnyArguments` - The argument container for binding values
79/// 3. `&Drivers` - The current database driver (determines placeholder syntax)
80/// 4. `&mut usize` - The argument counter (for PostgreSQL `$n` placeholders)
81///
82/// ## Example
83///
84/// ```rust,ignore
85/// let custom_filter: FilterFn = Box::new(|query, args, driver, counter| {
86/// query.push_str(" AND age > ");
87/// match driver {
88/// Drivers::Postgres => {
89/// query.push_str(&format!("${}", counter));
90/// *counter += 1;
91/// }
92/// _ => query.push('?'),
93/// }
94/// args.add(18);
95/// });
96/// ```
97pub type FilterFn = Box<dyn Fn(&mut String, &mut AnyArguments<'_>, &Drivers, &mut usize) + Send + Sync>;
98
99// ============================================================================
100// QueryBuilder Struct
101// ============================================================================
102
103/// A fluent Query Builder for constructing SQL queries.
104///
105/// `QueryBuilder` provides a type-safe, ergonomic interface for building and executing
106/// SQL queries across different database backends. It supports filtering, ordering,
107/// pagination, and both SELECT and INSERT operations.
108///
109/// ## Type Parameter
110///
111/// * `'a` - Lifetime of the database reference (used for PhantomData)
112/// * `T` - The Model type this query operates on
113/// * `E` - The connection type (Database or Transaction)
114///
115/// ## Fields
116///
117/// * `db` - Reference to the database connection pool or transaction
118/// * `table_name` - Static string containing the table name
119/// * `columns_info` - Metadata about each column in the table
120/// * `columns` - List of column names in snake_case format
121/// * `select_columns` - Specific columns to select (empty = SELECT *)
122/// * `where_clauses` - List of filter functions to apply
123/// * `order_clauses` - List of ORDER BY clauses
124/// * `limit` - Maximum number of rows to return
125/// * `offset` - Number of rows to skip (for pagination)
126/// * `_marker` - PhantomData to bind the generic type T
127pub struct QueryBuilder<'a, T, E>
128where
129 E: Connection,
130{
131 /// Reference to the database connection pool
132 pub(crate) tx: E,
133
134 /// Name of the database table (in original case)
135 pub(crate) table_name: &'static str,
136
137 /// Metadata information about each column
138 pub(crate) columns_info: Vec<ColumnInfo>,
139
140 /// List of column names (in snake_case)
141 pub(crate) columns: Vec<String>,
142
143 /// Specific columns to select (empty means SELECT *)
144 pub(crate) select_columns: Vec<String>,
145
146 /// Collection of WHERE clause filter functions
147 pub(crate) where_clauses: Vec<FilterFn>,
148
149 /// Collection of ORDER BY clauses
150 pub(crate) order_clauses: Vec<String>,
151
152 /// Collection of JOIN clause to filter entry tables
153 pub(crate) joins_clauses: Vec<String>,
154
155 /// Maximum number of rows to return (LIMIT)
156 pub(crate) limit: Option<usize>,
157
158 /// Number of rows to skip (OFFSET)
159 pub(crate) offset: Option<usize>,
160
161 /// Activate debug mode in query
162 pub(crate) debug_mode: bool,
163
164 /// PhantomData to bind the generic type T
165 pub(crate) _marker: PhantomData<&'a T>,
166}
167
168// ============================================================================
169// QueryBuilder Implementation
170// ============================================================================
171
172impl<'a, T, E> QueryBuilder<'a, T, E>
173where
174 T: Model + Send + Sync + Unpin,
175 E: Connection,
176{
177 // ========================================================================
178 // Constructor
179 // ========================================================================
180
181 /// Creates a new QueryBuilder instance.
182 ///
183 /// This constructor is typically called internally via `db.model::<T>()`.
184 /// You rarely need to call this directly.
185 ///
186 /// # Arguments
187 ///
188 /// * `db` - Reference to the database connection
189 /// * `table_name` - Name of the table to query
190 /// * `columns_info` - Metadata about table columns
191 /// * `columns` - List of column names
192 ///
193 /// # Returns
194 ///
195 /// A new `QueryBuilder` instance ready for query construction
196 ///
197 /// # Example
198 ///
199 /// ```rust,ignore
200 /// // Usually called via db.model::<User>()
201 /// let query = db.model::<User>();
202 /// ```
203 pub fn new(tx: E, table_name: &'static str, columns_info: Vec<ColumnInfo>, columns: Vec<String>) -> Self {
204 Self {
205 tx,
206 table_name,
207 columns_info,
208 columns,
209 debug_mode: false,
210 select_columns: Vec::new(),
211 where_clauses: Vec::new(),
212 order_clauses: Vec::new(),
213 joins_clauses: Vec::new(),
214 limit: None,
215 offset: None,
216 _marker: PhantomData,
217 }
218 }
219
220 // ========================================================================
221 // Query Building Methods
222 // ========================================================================
223
224 /// Adds a WHERE clause to the query.
225 ///
226 /// This method adds a filter condition to the query. Multiple filters can be chained
227 /// and will be combined with AND operators. The value is bound as a parameter to
228 /// prevent SQL injection.
229 ///
230 /// # Type Parameters
231 ///
232 /// * `V` - The type of the value to filter by. Must be encodable for SQL queries.
233 ///
234 /// # Arguments
235 ///
236 /// * `col` - The column name to filter on
237 /// * `op` - The comparison operator (e.g., "=", ">", "LIKE", "IN")
238 /// * `value` - The value to compare against
239 ///
240 /// # Supported Types
241 ///
242 /// - Primitives: `i32`, `i64`, `f64`, `bool`, `String`
243 /// - UUID: `Uuid` (all versions 1-7)
244 /// - Date/Time: `DateTime<Utc>`, `NaiveDateTime`, `NaiveDate`, `NaiveTime`
245 /// - Options: `Option<T>` for any supported type T
246 ///
247 /// # Example
248 ///
249 /// ```rust,ignore
250 /// // Filter by integer
251 /// query.filter("age", ">=", 18)
252 ///
253 /// // Filter by string
254 /// query.filter("username", "=", "john_doe")
255 ///
256 /// // Filter by UUID
257 /// let user_id = Uuid::new_v4();
258 /// query.filter("id", "=", user_id)
259 ///
260 /// // Filter with LIKE operator
261 /// query.filter("email", "LIKE", "%@example.com")
262 ///
263 /// // Chain multiple filters
264 /// query
265 /// .filter("age", ">=", 18)
266 /// .filter("active", "=", true)
267 /// .filter("role", "=", "admin")
268 /// ```
269 pub fn filter<V>(mut self, col: &'static str, op: &'static str, value: V) -> Self
270 where
271 V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,
272 {
273 let clause: FilterFn = Box::new(move |query, args, driver, arg_counter| {
274 query.push_str(" AND ");
275 if let Some((table, column)) = col.split_once(".") {
276 query.push_str(&format!("\"{}\".\"{}\"", table, column));
277 } else {
278 query.push_str(&format!("\"{}\"", col));
279 }
280 query.push(' ');
281 query.push_str(op);
282 query.push(' ');
283
284 // Handle different placeholder syntaxes based on database driver
285 match driver {
286 // PostgreSQL uses numbered placeholders: $1, $2, $3, ...
287 Drivers::Postgres => {
288 query.push_str(&format!("${}", arg_counter));
289 *arg_counter += 1;
290 }
291 // MySQL and SQLite use question mark placeholders: ?
292 _ => query.push('?'),
293 }
294
295 // Bind the value to the query
296 let _ = args.add(value.clone());
297 });
298
299 self.where_clauses.push(clause);
300 self
301 }
302
303 /// Adds an equality filter to the query.
304 ///
305 /// This is a convenience wrapper around `filter()` for simple equality checks.
306 /// It is equivalent to calling `filter(col, "=", value)`.
307 ///
308 /// # Type Parameters
309 ///
310 /// * `V` - The type of the value to compare against.
311 ///
312 /// # Arguments
313 ///
314 /// * `col` - The column name to filter on.
315 /// * `value` - The value to match.
316 ///
317 /// # Example
318 ///
319 /// ```rust,ignore
320 /// // Equivalent to filter("age", "=", 18)
321 /// query.equals("age", 18)
322 /// ```
323 pub fn equals<V>(self, col: &'static str, value: V) -> Self
324 where
325 V: 'static + for<'q> Encode<'q, Any> + Type<Any> + Send + Sync + Clone,
326 {
327 self.filter(col, "=", value)
328 }
329
330 /// Adds an ORDER BY clause to the query.
331 ///
332 /// Specifies the sort order for the query results. Multiple order clauses
333 /// can be added and will be applied in the order they were added.
334 ///
335 /// # Arguments
336 ///
337 /// * `order` - The ORDER BY expression (e.g., "created_at DESC", "age ASC, name DESC")
338 ///
339 /// # Example
340 ///
341 /// ```rust,ignore
342 /// // Single column ascending (ASC is default)
343 /// query.order("age")
344 ///
345 /// // Single column descending
346 /// query.order("created_at DESC")
347 ///
348 /// // Multiple columns
349 /// query.order("age DESC, username ASC")
350 ///
351 /// // Chain multiple order clauses
352 /// query
353 /// .order("priority DESC")
354 /// .order("created_at ASC")
355 /// ```
356 pub fn order(mut self, order: &str) -> Self {
357 self.order_clauses.push(order.to_string());
358 self
359 }
360
361 /// Placeholder for eager loading relationships (preload).
362 ///
363 /// This method is reserved for future implementation of relationship preloading.
364 /// Currently, it returns `self` unchanged to maintain the fluent interface.
365 ///
366 /// # Future Implementation
367 ///
368 /// Will support eager loading of related models to avoid N+1 query problems:
369 ///
370 /// ```rust,ignore
371 /// // Future usage example
372 /// query.preload("posts").preload("comments")
373 /// ```
374 // pub fn preload(self) -> Self {
375 // // TODO: Implement relationship preloading
376 // self
377 // }
378
379 /// Activates debug mode for this query.
380 ///
381 /// When enabled, the generated SQL query will be logged using the `log` crate
382 /// at the `DEBUG` level before execution.
383 ///
384 /// # Note
385 ///
386 /// To see the output, you must initialize a logger in your application (e.g., using `env_logger`)
387 /// and configure it to display `debug` logs for `bottle_orm`.
388 ///
389 /// # Example
390 ///
391 /// ```rust,ignore
392 /// db.model::<User>()
393 /// .filter("active", "=", true)
394 /// .debug() // Logs SQL: SELECT * FROM "user" WHERE "active" = $1
395 /// .scan()
396 /// .await?;
397 /// ```
398 pub fn debug(mut self) -> Self {
399 self.debug_mode = true;
400 self
401 }
402
403 /// Placeholder for JOIN operations.
404 ///
405 /// This method is reserved for future implementation of SQL JOINs.
406 /// Currently, it returns `self` unchanged to maintain the fluent interface.
407 ///
408 /// # Future Implementation
409 ///
410 /// Will support various types of JOINs (INNER, LEFT, RIGHT, FULL):
411 ///
412 /// ```rust,ignore
413 /// Adds a JOIN clause to the query.
414 ///
415 /// # Arguments
416 ///
417 /// * `table` - The name of the table to join.
418 /// * `s_query` - The ON clause condition (e.g., "users.id = posts.user_id").
419 ///
420 /// # Example
421 ///
422 /// ```rust,ignore
423 /// query.join("posts", "users.id = posts.user_id")
424 /// ```
425 pub fn join(mut self, table: &str, s_query: &str) -> Self {
426 self.joins_clauses.push(format!("JOIN {} ON {}", table, s_query));
427 self
428 }
429
430 /// Applies pagination with validation and limits.
431 ///
432 /// This is a convenience method that combines `limit()` and `offset()` with
433 /// built-in validation and maximum value enforcement for safer pagination.
434 ///
435 /// # Arguments
436 ///
437 /// * `max_value` - Maximum allowed items per page
438 /// * `default` - Default value if `value` exceeds `max_value`
439 /// * `page` - Zero-based page number
440 /// * `value` - Requested items per page
441 ///
442 /// # Returns
443 ///
444 /// * `Ok(Self)` - The updated QueryBuilder with pagination applied
445 /// * `Err(Error)` - If `value` is negative
446 ///
447 /// # Pagination Logic
448 ///
449 /// 1. Validates that `value` is non-negative
450 /// 2. If `value` > `max_value`, uses `default` instead
451 /// 3. Calculates offset as: `value * page`
452 /// 4. Sets limit to `value`
453 ///
454 /// # Example
455 ///
456 /// ```rust,ignore
457 /// // Page 0 with 10 items (page 1 in 1-indexed systems)
458 /// query.pagination(100, 20, 0, 10)? // LIMIT 10 OFFSET 0
459 ///
460 /// // Page 2 with 25 items (page 3 in 1-indexed systems)
461 /// query.pagination(100, 20, 2, 25)? // LIMIT 25 OFFSET 50
462 ///
463 /// // Request too many items, falls back to default
464 /// query.pagination(100, 20, 0, 150)? // LIMIT 20 OFFSET 0 (150 > 100)
465 ///
466 /// // Error: negative value
467 /// query.pagination(100, 20, 0, -10)? // Returns Error
468 /// ```
469 pub fn pagination(mut self, max_value: usize, default: usize, page: usize, value: isize) -> Result<Self, Error> {
470 // Validate that value is non-negative
471 if value < 0 {
472 return Err(Error::InvalidArgument("value cannot be negative".into()));
473 }
474
475 let mut f_value = value as usize;
476
477 // Enforce maximum value limit
478 if f_value > max_value {
479 f_value = default;
480 }
481
482 // Apply offset and limit
483 self = self.offset(f_value * page);
484 self = self.limit(f_value);
485
486 Ok(self)
487 }
488
489 /// Selects specific columns to return.
490 ///
491 /// By default, queries use `SELECT *` to return all columns. This method
492 /// allows you to specify exactly which columns should be returned, which can
493 /// improve performance for tables with many or large columns.
494 ///
495 /// # Arguments
496 ///
497 /// * `columns` - Comma-separated list of column names to select
498 ///
499 /// # Example
500 ///
501 /// ```rust,ignore
502 /// // Select single column
503 /// query.select("id")
504 ///
505 /// // Select multiple columns
506 /// query.select("id, username, email")
507 ///
508 /// // Select with SQL functions
509 /// query.select("COUNT(*) as total")
510 ///
511 /// // Chain multiple select calls (all will be included)
512 /// query
513 /// .select("id, username")
514 /// .select("created_at")
515 /// ```
516 pub fn select(mut self, columns: &str) -> Self {
517 self.select_columns.push(columns.to_string().to_snake_case());
518 self
519 }
520
521 /// Sets the query offset (pagination).
522 ///
523 /// Specifies the number of rows to skip before starting to return rows.
524 /// Commonly used in combination with `limit()` for pagination.
525 ///
526 /// # Arguments
527 ///
528 /// * `offset` - Number of rows to skip
529 ///
530 /// # Example
531 ///
532 /// ```rust,ignore
533 /// // Skip first 20 rows
534 /// query.offset(20)
535 ///
536 /// // Pagination: page 3 with 10 items per page
537 /// query.limit(10).offset(20) // Skip 2 pages = 20 items
538 /// ```
539 pub fn offset(mut self, offset: usize) -> Self {
540 self.offset = Some(offset);
541 self
542 }
543
544 /// Sets the maximum number of records to return.
545 ///
546 /// Limits the number of rows returned by the query. Essential for pagination
547 /// and preventing accidentally fetching large result sets.
548 ///
549 /// # Arguments
550 ///
551 /// * `limit` - Maximum number of rows to return
552 ///
553 /// # Example
554 ///
555 /// ```rust,ignore
556 /// // Return at most 10 rows
557 /// query.limit(10)
558 ///
559 /// // Pagination: 50 items per page
560 /// query.limit(50).offset(page * 50)
561 /// ```
562 pub fn limit(mut self, limit: usize) -> Self {
563 self.limit = Some(limit);
564 self
565 }
566
567 // ========================================================================
568 // Insert Operation
569 // ========================================================================
570
571 /// Inserts a new record into the database based on the model instance.
572 ///
573 /// This method serializes the model into a SQL INSERT statement with proper
574 /// type handling for primitives, dates, UUIDs, and other supported types.
575 ///
576 /// # Type Binding Strategy
577 ///
578 /// The method uses string parsing as a temporary solution for type binding.
579 /// Values are converted to strings via the model's `to_map()` method, then
580 /// parsed back to their original types for proper SQL binding.
581 ///
582 /// # Supported Types for Insert
583 ///
584 /// - **Integers**: `i32`, `i64` (INTEGER, BIGINT)
585 /// - **Boolean**: `bool` (BOOLEAN)
586 /// - **Float**: `f64` (DOUBLE PRECISION)
587 /// - **Text**: `String` (TEXT, VARCHAR)
588 /// - **UUID**: `Uuid` (UUID) - All versions 1-7 supported
589 /// - **DateTime**: `DateTime<Utc>` (TIMESTAMPTZ)
590 /// - **NaiveDateTime**: (TIMESTAMP)
591 /// - **NaiveDate**: (DATE)
592 /// - **NaiveTime**: (TIME)
593 ///
594 /// # Arguments
595 ///
596 /// * `model` - Reference to the model instance to insert
597 ///
598 /// # Returns
599 ///
600 /// * `Ok(&Self)` - Reference to self for method chaining
601 /// * `Err(sqlx::Error)` - Database error during insertion
602 ///
603 /// # Example
604 ///
605 /// ```rust,ignore
606 /// use uuid::Uuid;
607 /// use chrono::Utc;
608 ///
609 /// let new_user = User {
610 /// id: Uuid::new_v4(),
611 /// username: "john_doe".to_string(),
612 /// email: "john@example.com".to_string(),
613 /// age: 25,
614 /// active: true,
615 /// created_at: Utc::now(),
616 /// };
617 ///
618 /// db.model::<User>().insert(&new_user).await?;
619 /// ```
620 pub async fn insert(&mut self, model: &T) -> Result<(), sqlx::Error> {
621 // Serialize model to a HashMap of column_name -> string_value
622 let data_map = model.to_map();
623
624 // Early return if no data to insert
625 if data_map.is_empty() {
626 return Ok(());
627 }
628
629 let table_name = self.table_name.to_snake_case();
630 let columns_info = T::columns();
631
632 let mut target_columns = Vec::new();
633 let mut bindings: Vec<(String, &str)> = Vec::new();
634
635 // Build column list and collect values with their SQL types
636 for (col_name, value) in data_map {
637 // Strip the "r#" prefix if present (for Rust keywords used as field names)
638 let col_name_clean = col_name.strip_prefix("r#").unwrap_or(&col_name).to_snake_case();
639 target_columns.push(format!("\"{}\"", col_name_clean));
640
641 // Find the SQL type for this column
642 let sql_type = columns_info.iter().find(|c| c.name == col_name).map(|c| c.sql_type).unwrap_or("TEXT");
643
644 bindings.push((value, sql_type));
645 }
646
647 // Generate placeholders with proper type casting for PostgreSQL
648 let placeholders: Vec<String> = bindings
649 .iter()
650 .enumerate()
651 .map(|(i, (_, sql_type))| match self.tx.driver() {
652 Drivers::Postgres => {
653 let idx = i + 1;
654 // PostgreSQL requires explicit type casting for some types
655 if temporal::is_temporal_type(sql_type) {
656 // Use temporal module for type casting
657 format!("${}{}", idx, temporal::get_postgres_type_cast(sql_type))
658 } else {
659 match *sql_type {
660 "UUID" => format!("${}::UUID", idx),
661 _ => format!("${}", idx),
662 }
663 }
664 }
665 // MySQL and SQLite use simple ? placeholders
666 _ => "?".to_string(),
667 })
668 .collect();
669
670 // Construct the INSERT query
671 let query_str = format!(
672 "INSERT INTO \"{}\" ({}) VALUES ({})",
673 table_name,
674 target_columns.join(", "),
675 placeholders.join(", ")
676 );
677
678 // If debug mode is enabled, log the generated SQL query before execution
679 if self.debug_mode {
680 log::debug!("SQL: {}", query_str);
681 }
682
683 let mut query = sqlx::query::<sqlx::Any>(&query_str);
684
685 // Bind values using the optimized value_binding module
686 // This provides type-safe binding with driver-specific optimizations
687 for (val_str, sql_type) in bindings {
688 // Create temporary AnyArguments to collect the bound value
689 let mut temp_args = AnyArguments::default();
690
691 // Use the ValueBinder trait for type-safe binding
692 if temp_args.bind_value(&val_str, sql_type, &self.tx.driver()).is_ok() {
693 // For now, we need to convert back to individual bindings
694 // This is a workaround until we can better integrate AnyArguments
695 match sql_type {
696 "INTEGER" | "INT" | "SERIAL" | "serial" | "int4" => {
697 if let Ok(val) = val_str.parse::<i32>() {
698 query = query.bind(val);
699 } else {
700 query = query.bind(val_str);
701 }
702 }
703 "BIGINT" | "INT8" | "int8" | "BIGSERIAL" => {
704 if let Ok(val) = val_str.parse::<i64>() {
705 query = query.bind(val);
706 } else {
707 query = query.bind(val_str);
708 }
709 }
710 "BOOLEAN" | "BOOL" | "bool" => {
711 if let Ok(val) = val_str.parse::<bool>() {
712 query = query.bind(val);
713 } else {
714 query = query.bind(val_str);
715 }
716 }
717 "DOUBLE PRECISION" | "FLOAT" | "float8" | "REAL" | "NUMERIC" | "DECIMAL" => {
718 if let Ok(val) = val_str.parse::<f64>() {
719 query = query.bind(val);
720 } else {
721 query = query.bind(val_str);
722 }
723 }
724 "UUID" => {
725 if let Ok(val) = val_str.parse::<Uuid>() {
726 query = query.bind(val.hyphenated().to_string());
727 } else {
728 query = query.bind(val_str);
729 }
730 }
731 "TIMESTAMPTZ" | "DateTime" => {
732 if let Ok(val) = temporal::parse_datetime_utc(&val_str) {
733 let formatted = temporal::format_datetime_for_driver(&val, &self.tx.driver());
734 query = query.bind(formatted);
735 } else {
736 query = query.bind(val_str);
737 }
738 }
739 "TIMESTAMP" | "NaiveDateTime" => {
740 if let Ok(val) = temporal::parse_naive_datetime(&val_str) {
741 let formatted = temporal::format_naive_datetime_for_driver(&val, &self.tx.driver());
742 query = query.bind(formatted);
743 } else {
744 query = query.bind(val_str);
745 }
746 }
747 "DATE" | "NaiveDate" => {
748 if let Ok(val) = temporal::parse_naive_date(&val_str) {
749 let formatted = val.format("%Y-%m-%d").to_string();
750 query = query.bind(formatted);
751 } else {
752 query = query.bind(val_str);
753 }
754 }
755 "TIME" | "NaiveTime" => {
756 if let Ok(val) = temporal::parse_naive_time(&val_str) {
757 let formatted = val.format("%H:%M:%S%.6f").to_string();
758 query = query.bind(formatted);
759 } else {
760 query = query.bind(val_str);
761 }
762 }
763 _ => {
764 query = query.bind(val_str);
765 }
766 }
767 } else {
768 // Fallback: bind as string if type conversion fails
769 query = query.bind(val_str);
770 }
771 }
772
773 // Execute the INSERT query
774 query.execute(self.tx.executor()).await?;
775 Ok(())
776 }
777
778 // ========================================================================
779 // Query Execution Methods
780 // ========================================================================
781
782 /// Returns the generated SQL string for debugging purposes.
783 ///
784 /// This method constructs the SQL query string without executing it.
785 /// Useful for debugging and logging query construction. Note that this
786 /// shows placeholders (?, $1, etc.) rather than actual bound values.
787 ///
788 /// # Returns
789 ///
790 /// A `String` containing the SQL query that would be executed
791 ///
792 /// # Example
793 ///
794 /// ```rust,ignore
795 /// let query = db.model::<User>()
796 /// .filter("age", ">=", 18)
797 /// .order("created_at DESC")
798 /// .limit(10);
799 ///
800 /// println!("SQL: {}", query.to_sql());
801 /// // Output: SELECT * FROM "user" WHERE 1=1 AND "age" >= $1 ORDER BY created_at DESC
802 /// ```
803 pub fn to_sql(&self) -> String {
804 let mut query = String::from("SELECT ");
805
806 // Handle column selection
807 if self.select_columns.is_empty() {
808 query.push('*');
809 } else {
810 query.push_str(&self.select_columns.join(", "));
811 }
812
813 query.push_str(" FROM \"");
814 query.push_str(&self.table_name.to_snake_case());
815 query.push_str("\" WHERE 1=1");
816
817 // Apply WHERE clauses with dummy arguments
818 let mut dummy_args = AnyArguments::default();
819 let mut dummy_counter = 1;
820
821 for clause in &self.where_clauses {
822 clause(&mut query, &mut dummy_args, &self.tx.driver(), &mut dummy_counter);
823 }
824
825 // Apply ORDER BY if present
826 if !self.order_clauses.is_empty() {
827 query.push_str(&format!(" ORDER BY {}", &self.order_clauses.join(", ")));
828 }
829
830 query
831 }
832
833 /// Generates the list of column selection SQL arguments.
834 ///
835 /// This helper function constructs the column list for the SELECT statement.
836 /// It handles:
837 /// 1. Mapping specific columns if `select_columns` is set.
838 /// 2. Defaulting to all columns from the struct `R` if no columns are specified.
839 /// 3. applying `to_json(...)` casting for temporal types when using `AnyImpl` structs,
840 /// ensuring compatibility with the `FromAnyRow` deserialization logic.
841 fn select_args_sql<R: AnyImpl>(&self) -> Vec<String> {
842 let struct_cols = R::columns();
843
844 if !struct_cols.is_empty() {
845 if !self.select_columns.is_empty() {
846 let mut args = Vec::new();
847 for col_info in struct_cols {
848 let col_snake = col_info.column.to_snake_case();
849 let sql_type = col_info.sql_type;
850 if self.select_columns.contains(&col_snake) {
851 if is_temporal_type(sql_type) {
852 if !self.joins_clauses.is_empty() {
853 args.push(format!(
854 "to_json(\"{}\".\"{}\") #>> '{{}}' AS \"{}\"",
855 self.table_name.to_snake_case(),
856 col_snake,
857 col_snake
858 ));
859 } else {
860 args.push(format!("to_json(\"{}\") #>> '{{}}' AS \"{}\"", col_snake, col_snake));
861 }
862 } else if !self.joins_clauses.is_empty() {
863 args.push(format!("\"{}\".\"{}\"", self.table_name.to_snake_case(), col_snake));
864 } else {
865 args.push(format!("\"{}\"", col_snake));
866 }
867 }
868 }
869 return args;
870 } else {
871 return struct_cols
872 .iter()
873 .map(|c| {
874 let col_snake = c.column.to_snake_case();
875 if is_temporal_type(c.sql_type) {
876 if !self.joins_clauses.is_empty() {
877 format!(
878 "to_json(\"{}\".\"{}\") #>> '{{}}' AS \"{}\"",
879 self.table_name.to_snake_case(),
880 col_snake,
881 col_snake
882 )
883 } else {
884 format!("to_json(\"{}\") #>> '{{}}' AS \"{}\"", col_snake, col_snake)
885 }
886 } else if !self.joins_clauses.is_empty() {
887 format!("\"{}\".\"{}\"", self.table_name.to_snake_case(), col_snake)
888 } else {
889 format!("\"{}\"", col_snake)
890 }
891 })
892 .collect();
893 }
894 }
895
896 if !self.select_columns.is_empty() {
897 return self
898 .select_columns
899 .iter()
900 .map(|c| if c.contains('(') { c.clone() } else { format!("\"{}\"", c) })
901 .collect();
902 }
903
904 vec!["*".to_string()]
905 }
906
907 /// Executes the query and returns a list of results.
908 ///
909 /// This method builds and executes a SELECT query with all accumulated filters,
910 /// ordering, and pagination settings. It returns all matching rows as a vector.
911 ///
912 /// # Type Parameters
913 ///
914 /// * `R` - The result type. Must implement `FromRow` for deserialization from database rows.
915 ///
916 /// # Returns
917 ///
918 /// * `Ok(Vec<R>)` - Vector of results (empty if no matches)
919 /// * `Err(sqlx::Error)` - Database error during query execution
920 ///
921 /// # Example
922 ///
923 /// ```rust,ignore
924 /// // Get all adult users, ordered by age, limited to 10
925 /// let users: Vec<User> = db.model::<User>()
926 /// .filter("age", ">=", 18)
927 /// .order("age DESC")
928 /// .limit(10)
929 /// .scan()
930 /// .await?;
931 ///
932 /// // Get users by UUID
933 /// let user_id = Uuid::parse_str("550e8400-e29b-41d4-a716-446655440000")?;
934 /// let users: Vec<User> = db.model::<User>()
935 /// .filter("id", "=", user_id)
936 /// .scan()
937 /// .await?;
938 ///
939 /// // Empty result is Ok
940 /// let results: Vec<User> = db.model::<User>()
941 /// .filter("age", ">", 200)
942 /// .scan()
943 /// .await?; // Returns empty Vec, not an error
944 /// ```
945 pub async fn scan<R>(mut self) -> Result<Vec<R>, sqlx::Error>
946 where
947 R: for<'r> FromRow<'r, AnyRow> + AnyImpl + Send + Unpin,
948 {
949 // Build SELECT clause
950 let mut query = String::from("SELECT ");
951 query.push_str(&self.select_args_sql::<R>().join(", "));
952
953 // Build FROM clause
954 query.push_str(" FROM \"");
955 query.push_str(&self.table_name.to_snake_case());
956 query.push_str("\" ");
957 if !self.joins_clauses.is_empty() {
958 query.push_str(&self.joins_clauses.join(" "));
959 }
960
961 query.push_str(" WHERE 1=1");
962
963 // Apply WHERE clauses
964 let mut args = AnyArguments::default();
965 let mut arg_counter = 1;
966
967 for clause in &self.where_clauses {
968 clause(&mut query, &mut args, &self.tx.driver(), &mut arg_counter);
969 }
970
971 // Apply ORDER BY clauses
972 // We join multiple clauses with commas to form a valid SQL ORDER BY statement
973 if !self.order_clauses.is_empty() {
974 query.push_str(&format!(" ORDER BY {}", self.order_clauses.join(", ")));
975 }
976
977 // Apply LIMIT clause
978 if let Some(limit) = self.limit {
979 query.push_str(" LIMIT ");
980 match self.tx.driver() {
981 Drivers::Postgres => {
982 query.push_str(&format!("${}", arg_counter));
983 arg_counter += 1;
984 }
985 _ => query.push('?'),
986 }
987 let _ = args.add(limit as i64);
988 }
989
990 // Apply OFFSET clause
991 if let Some(offset) = self.offset {
992 query.push_str(" OFFSET ");
993 match self.tx.driver() {
994 Drivers::Postgres => {
995 query.push_str(&format!("${}", arg_counter));
996 // arg_counter += 1; // Not needed as this is the last clause
997 }
998 _ => query.push('?'),
999 }
1000 let _ = args.add(offset as i64);
1001 }
1002
1003 // Print SQL query to logs if debug mode is active
1004 if self.debug_mode {
1005 log::debug!("SQL: {}", query);
1006 }
1007
1008 // Execute query and fetch all results
1009 sqlx::query_as_with::<_, R, _>(&query, args).fetch_all(self.tx.executor()).await
1010 }
1011
1012 /// Executes the query and returns only the first result.
1013 ///
1014 /// This method automatically adds `LIMIT 1` and orders by the Primary Key
1015 /// (if available) to ensure consistent results. It's optimized for fetching
1016 /// a single row and will return an error if no rows match.
1017 ///
1018 /// # Type Parameters
1019 ///
1020 /// * `R` - The result type. Must implement `FromRow` for deserialization.
1021 ///
1022 /// # Returns
1023 ///
1024 /// * `Ok(R)` - The first matching row
1025 /// * `Err(sqlx::Error)` - No rows found or database error
1026 ///
1027 /// # Error Handling
1028 ///
1029 /// Returns `sqlx::Error::RowNotFound` if no rows match the query.
1030 /// Use `scan()` instead if you want an empty Vec rather than an error.
1031 ///
1032 /// # Example
1033 ///
1034 /// ```rust,ignore
1035 /// // Get a specific user by ID
1036 /// let user: User = db.model::<User>()
1037 /// .filter("id", "=", 1)
1038 /// .first()
1039 /// .await?;
1040 ///
1041 /// // Get user by UUID
1042 /// let user_id = Uuid::new_v4();
1043 /// let user: User = db.model::<User>()
1044 /// .filter("id", "=", user_id)
1045 /// .first()
1046 /// .await?;
1047 ///
1048 /// // Get the oldest user
1049 /// let oldest: User = db.model::<User>()
1050 /// .order("age DESC")
1051 /// .first()
1052 /// .await?;
1053 ///
1054 /// // Error handling
1055 /// match db.model::<User>().filter("id", "=", 999).first().await {
1056 /// Ok(user) => println!("Found: {:?}", user),
1057 /// Err(sqlx::Error::RowNotFound) => println!("User not found"),
1058 /// Err(e) => println!("Database error: {}", e),
1059 /// }
1060 /// ```
1061 pub async fn first<R>(mut self) -> Result<R, sqlx::Error>
1062 where
1063 R: for<'r> FromRow<'r, AnyRow> + AnyImpl + Send + Unpin,
1064 {
1065 // Build SELECT clause
1066 let mut query = String::from("SELECT ");
1067 query.push_str(&self.select_args_sql::<R>().join(", "));
1068
1069 // Build FROM clause
1070 query.push_str(" FROM \"");
1071 query.push_str(&self.table_name.to_snake_case());
1072 query.push_str("\" ");
1073 if !self.joins_clauses.is_empty() {
1074 query.push_str(&self.joins_clauses.join(" "));
1075 }
1076
1077 query.push_str(" WHERE 1=1");
1078
1079 // Apply WHERE clauses
1080 let mut args = AnyArguments::default();
1081 let mut arg_counter = 1;
1082
1083 for clause in &self.where_clauses {
1084 clause(&mut query, &mut args, &self.tx.driver(), &mut arg_counter);
1085 }
1086
1087 // Find primary key column for consistent ordering
1088 let pk_column = T::columns()
1089 .iter()
1090 .find(|c| c.is_primary_key)
1091 .map(|c| c.name.strip_prefix("r#").unwrap_or(c.name).to_snake_case());
1092
1093 // Apply ORDER BY clauses
1094 // We join multiple clauses with commas to form a valid SQL ORDER BY statement
1095 if !self.order_clauses.is_empty() {
1096 query.push_str(&format!(" ORDER BY {}", self.order_clauses.join(", ")));
1097 } else if let Some(pk) = pk_column {
1098 // Fallback to PK ordering if no custom order is specified (ensures deterministic results)
1099 query.push_str(" ORDER BY ");
1100 query.push_str(&format!("\"{}\".\"{}\"", self.table_name.to_snake_case(), pk));
1101 query.push_str(" ASC");
1102 }
1103
1104 // Always add LIMIT 1 for first() queries
1105 query.push_str(" LIMIT 1");
1106
1107 // Print SQL query to logs if debug mode is active
1108 if self.debug_mode {
1109 log::debug!("SQL: {}", query);
1110 }
1111
1112 // Execute query and fetch exactly one result
1113 sqlx::query_as_with::<_, R, _>(&query, args).fetch_one(self.tx.executor()).await
1114 }
1115
1116 /// Executes the query and returns a single scalar value.
1117 ///
1118 /// This method is useful for fetching single values like counts, max/min values,
1119 /// or specific columns without mapping to a struct or tuple.
1120 ///
1121 /// # Type Parameters
1122 ///
1123 /// * `O` - The output type. Must implement `Decode` and `Type`.
1124 ///
1125 /// # Example
1126 ///
1127 /// ```rust,ignore
1128 /// // Get count of users
1129 /// let count: i64 = db.model::<User>()
1130 /// .select("count(*)")
1131 /// .scalar()
1132 /// .await?;
1133 ///
1134 /// // Get specific field
1135 /// let username: String = db.model::<User>()
1136 /// .filter("id", "=", 1)
1137 /// .select("username")
1138 /// .scalar()
1139 /// .await?;
1140 /// ```
1141 pub async fn scalar<O>(mut self) -> Result<O, sqlx::Error>
1142 where
1143 O: for<'r> Decode<'r, Any> + Type<Any> + Send + Unpin,
1144 {
1145 // Build SELECT clause
1146 let mut query = String::from("SELECT ");
1147
1148 if self.select_columns.is_empty() {
1149 return Err(sqlx::Error::ColumnNotFound("is not possible get data without column".to_string()));
1150 }
1151
1152 let mut select_cols = Vec::with_capacity(self.select_columns.capacity());
1153 for col in self.select_columns {
1154 if !self.joins_clauses.is_empty() {
1155 if let Some((table, column)) = col.split_once(".") {
1156 select_cols.push(format!("\"{}\".\"{}\"", table, column));
1157 } else {
1158 select_cols.push(format!("\"{}\".\"{}\"", self.table_name.to_snake_case(), col));
1159 }
1160 continue;
1161 }
1162 select_cols.push(col);
1163 }
1164
1165 query.push_str(&select_cols.join(", "));
1166
1167 // Build FROM clause
1168 query.push_str(" FROM \"");
1169 query.push_str(&self.table_name.to_snake_case());
1170 query.push_str("\" ");
1171
1172 if !self.joins_clauses.is_empty() {
1173 query.push_str(&self.joins_clauses.join(" "));
1174 }
1175
1176 query.push_str(" WHERE 1=1");
1177
1178 // Apply WHERE clauses
1179 let mut args = AnyArguments::default();
1180 let mut arg_counter = 1;
1181
1182 for clause in &self.where_clauses {
1183 clause(&mut query, &mut args, &self.tx.driver(), &mut arg_counter);
1184 }
1185
1186 // Apply ORDER BY
1187 if !self.order_clauses.is_empty() {
1188 query.push_str(&format!(" ORDER BY {}", &self.order_clauses.join(", ")));
1189 }
1190
1191 // Always add LIMIT 1 for scalar queries
1192 query.push_str(" LIMIT 1");
1193
1194 // Print SQL query to logs if debug mode is active
1195 if self.debug_mode {
1196 log::debug!("SQL: {}", query);
1197 }
1198
1199 // Execute query and fetch one row
1200 let row = sqlx::query_with::<_, _>(&query, args).fetch_one(self.tx.executor()).await?;
1201
1202 // Get the first column
1203 row.try_get::<O, _>(0)
1204 }
1205
1206 /// Updates a single column in the database.
1207 ///
1208 /// # Arguments
1209 ///
1210 /// * `col` - The column name to update
1211 /// * `value` - The new value
1212 ///
1213 /// # Returns
1214 ///
1215 /// * `Ok(u64)` - The number of rows affected
1216 pub async fn update<V>(&mut self, col: &str, value: V) -> Result<u64, sqlx::Error>
1217 where
1218 V: ToString + Send + Sync,
1219 {
1220 let mut map = std::collections::HashMap::new();
1221 map.insert(col.to_string(), value.to_string());
1222 self.execute_update(map).await
1223 }
1224
1225 /// Updates all columns based on the model instance.
1226 ///
1227 /// This method updates all active columns of the table with values from the provided model.
1228 ///
1229 /// # Arguments
1230 ///
1231 /// * `model` - The model instance containing new values
1232 ///
1233 /// # Returns
1234 ///
1235 /// * `Ok(u64)` - The number of rows affected
1236 pub async fn updates(&mut self, model: &T) -> Result<u64, sqlx::Error> {
1237 self.execute_update(model.to_map()).await
1238 }
1239
1240 /// Updates columns based on a partial model (struct implementing AnyImpl).
1241 ///
1242 /// This allows updating a subset of columns using a custom struct.
1243 /// The struct must implement `AnyImpl` (usually via `#[derive(FromAnyRow)]`).
1244 ///
1245 /// # Arguments
1246 ///
1247 /// * `partial` - The partial model containing new values
1248 ///
1249 /// # Returns
1250 ///
1251 /// * `Ok(u64)` - The number of rows affected
1252 pub async fn update_partial<P: AnyImpl>(&mut self, partial: &P) -> Result<u64, sqlx::Error> {
1253 self.execute_update(partial.to_map()).await
1254 }
1255
1256 /// Internal helper to execute an UPDATE query from a map of values.
1257 async fn execute_update(
1258 &mut self,
1259 data_map: std::collections::HashMap<String, String>,
1260 ) -> Result<u64, sqlx::Error> {
1261 let table_name = self.table_name.to_snake_case();
1262 let mut query = format!("UPDATE \"{}\" SET ", table_name);
1263
1264 let mut bindings: Vec<(String, &str)> = Vec::new();
1265 let mut set_clauses = Vec::new();
1266
1267 // Maintain argument counter for PostgreSQL ($1, $2, ...)
1268 let mut arg_counter = 1;
1269
1270 // Build SET clause
1271 for (col_name, value) in data_map {
1272 // Strip the "r#" prefix if present
1273 let col_name_clean = col_name.strip_prefix("r#").unwrap_or(&col_name).to_snake_case();
1274
1275 // Find the SQL type for this column from the Model metadata
1276 let sql_type = self
1277 .columns_info
1278 .iter()
1279 .find(|c| c.name == col_name || c.name == col_name_clean)
1280 .map(|c| c.sql_type)
1281 .unwrap_or("TEXT");
1282
1283 // Generate placeholder
1284 let placeholder = match self.tx.driver() {
1285 Drivers::Postgres => {
1286 let idx = arg_counter;
1287 arg_counter += 1;
1288
1289 if temporal::is_temporal_type(sql_type) {
1290 format!("${}{}", idx, temporal::get_postgres_type_cast(sql_type))
1291 } else {
1292 match sql_type {
1293 "UUID" => format!("${}::UUID", idx),
1294 _ => format!("${}", idx),
1295 }
1296 }
1297 }
1298 _ => "?".to_string(),
1299 };
1300
1301 set_clauses.push(format!("\"{}\" = {}", col_name_clean, placeholder));
1302 bindings.push((value, sql_type));
1303 }
1304
1305 // If no fields to update, return 0
1306 if set_clauses.is_empty() {
1307 return Ok(0);
1308 }
1309
1310 query.push_str(&set_clauses.join(", "));
1311
1312 // Build WHERE clause
1313 query.push_str(" WHERE 1=1");
1314
1315 let mut args = AnyArguments::default();
1316
1317 // Bind SET values
1318 for (val_str, sql_type) in bindings {
1319 if args.bind_value(&val_str, sql_type, &self.tx.driver()).is_err() {
1320 let _ = args.add(val_str);
1321 }
1322 }
1323
1324 // Apply WHERE clauses (appending to args and query)
1325 for clause in &self.where_clauses {
1326 clause(&mut query, &mut args, &self.tx.driver(), &mut arg_counter);
1327 }
1328
1329 // Print SQL query to logs if debug mode is active
1330 if self.debug_mode {
1331 log::debug!("SQL: {}", query);
1332 }
1333
1334 // Execute the UPDATE query
1335 let result = sqlx::query_with(&query, args).execute(self.tx.executor()).await?;
1336
1337 Ok(result.rows_affected())
1338 }
1339
1340 /// Executes a DELETE query based on the current filters.
1341 ///
1342 /// # Returns
1343 ///
1344 /// * `Ok(u64)` - The number of rows deleted
1345 /// * `Err(sqlx::Error)` - Database error
1346 pub async fn delete(mut self) -> Result<u64, sqlx::Error> {
1347 let mut query = String::from("DELETE FROM \"");
1348 query.push_str(&self.table_name.to_snake_case());
1349 query.push_str("\" WHERE 1=1");
1350
1351 let mut args = AnyArguments::default();
1352 let mut arg_counter = 1;
1353
1354 for clause in &self.where_clauses {
1355 clause(&mut query, &mut args, &self.tx.driver(), &mut arg_counter);
1356 }
1357
1358 // Print SQL query to logs if debug mode is active
1359 if self.debug_mode {
1360 log::debug!("SQL: {}", query);
1361 }
1362
1363 let result = sqlx::query_with(&query, args).execute(self.tx.executor()).await?;
1364 Ok(result.rows_affected())
1365 }
1366}