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