sqlmodel_query/
builder.rs

1//! Query builders for INSERT, UPDATE, DELETE operations.
2//!
3//! This module provides fluent builders for CRUD operations with support for:
4//! - RETURNING clause (PostgreSQL)
5//! - Bulk inserts
6//! - UPSERT (ON CONFLICT)
7//! - Explicit column SET for updates
8//! - Model-based deletes
9
10use crate::clause::Where;
11use crate::expr::{Dialect, Expr};
12use asupersync::{Cx, Outcome};
13use sqlmodel_core::{Connection, Model, Row, Value};
14use std::marker::PhantomData;
15
16/// Conflict resolution strategy for INSERT operations.
17///
18/// Used with PostgreSQL's ON CONFLICT clause for UPSERT operations.
19#[derive(Debug, Clone)]
20pub enum OnConflict {
21    /// Do nothing on conflict (INSERT ... ON CONFLICT DO NOTHING)
22    DoNothing,
23    /// Update specified columns on conflict (INSERT ... ON CONFLICT DO UPDATE SET ...)
24    DoUpdate {
25        /// The columns to update. If empty, all non-primary-key columns are updated.
26        columns: Vec<String>,
27        /// The conflict target (column names). If empty, uses primary key.
28        target: Vec<String>,
29    },
30}
31
32/// INSERT query builder.
33///
34/// # Example
35///
36/// ```ignore
37/// // Simple insert
38/// let id = insert!(hero).execute(cx, &conn).await?;
39///
40/// // Insert with RETURNING
41/// let row = insert!(hero).returning().execute_returning(cx, &conn).await?;
42///
43/// // Insert with UPSERT
44/// let id = insert!(hero)
45///     .on_conflict_do_nothing()
46///     .execute(cx, &conn).await?;
47/// ```
48#[derive(Debug)]
49pub struct InsertBuilder<'a, M: Model> {
50    model: &'a M,
51    returning: bool,
52    on_conflict: Option<OnConflict>,
53}
54
55impl<'a, M: Model> InsertBuilder<'a, M> {
56    /// Create a new INSERT builder for the given model instance.
57    pub fn new(model: &'a M) -> Self {
58        Self {
59            model,
60            returning: false,
61            on_conflict: None,
62        }
63    }
64
65    /// Add RETURNING * clause to return the inserted row.
66    ///
67    /// Use with `execute_returning()` to get the inserted row.
68    pub fn returning(mut self) -> Self {
69        self.returning = true;
70        self
71    }
72
73    /// Handle conflicts by doing nothing (PostgreSQL ON CONFLICT DO NOTHING).
74    ///
75    /// This allows the insert to silently succeed even if it would violate
76    /// a unique constraint.
77    pub fn on_conflict_do_nothing(mut self) -> Self {
78        self.on_conflict = Some(OnConflict::DoNothing);
79        self
80    }
81
82    /// Handle conflicts by updating specified columns (UPSERT).
83    ///
84    /// If `columns` is empty, all non-primary-key columns are updated.
85    /// The conflict target defaults to the primary key.
86    ///
87    /// # Example
88    ///
89    /// ```ignore
90    /// // Update name and age on conflict
91    /// insert!(hero)
92    ///     .on_conflict_do_update(&["name", "age"])
93    ///     .execute(cx, &conn).await?;
94    /// ```
95    pub fn on_conflict_do_update(mut self, columns: &[&str]) -> Self {
96        self.on_conflict = Some(OnConflict::DoUpdate {
97            columns: columns.iter().map(|s| s.to_string()).collect(),
98            target: Vec::new(), // Default to primary key
99        });
100        self
101    }
102
103    /// Handle conflicts by updating columns with a specific conflict target.
104    ///
105    /// # Arguments
106    ///
107    /// * `target` - The columns that form the unique constraint to match
108    /// * `columns` - The columns to update on conflict
109    pub fn on_conflict_target_do_update(mut self, target: &[&str], columns: &[&str]) -> Self {
110        self.on_conflict = Some(OnConflict::DoUpdate {
111            columns: columns.iter().map(|s| s.to_string()).collect(),
112            target: target.iter().map(|s| s.to_string()).collect(),
113        });
114        self
115    }
116
117    /// Build the INSERT SQL and parameters with default dialect (Postgres).
118    pub fn build(&self) -> (String, Vec<Value>) {
119        self.build_with_dialect(Dialect::default())
120    }
121
122    /// Build the INSERT SQL and parameters with specific dialect.
123    pub fn build_with_dialect(&self, dialect: Dialect) -> (String, Vec<Value>) {
124        let row = self.model.to_row();
125        let fields = M::fields();
126
127        let insert_fields: Vec<_> = row
128            .iter()
129            .map(|(name, value)| {
130                let field = fields.iter().find(|f| f.name == *name);
131                if let Some(f) = field {
132                    if f.auto_increment && matches!(value, Value::Null) {
133                        return (*name, Value::Default);
134                    }
135                }
136                (*name, value.clone())
137            })
138            .collect();
139
140        let columns: Vec<_> = insert_fields.iter().map(|(name, _)| *name).collect();
141
142        let mut placeholders = Vec::new();
143        let mut params = Vec::new();
144
145        for (_, value) in insert_fields {
146            if matches!(value, Value::Default) {
147                // SQLite doesn't support DEFAULT as a value in VALUES clause.
148                // Use NULL instead, which works the same for auto-increment columns.
149                if dialect == Dialect::Sqlite {
150                    params.push(Value::Null);
151                    placeholders.push(dialect.placeholder(params.len()));
152                } else {
153                    placeholders.push("DEFAULT".to_string());
154                }
155            } else {
156                params.push(value);
157                placeholders.push(dialect.placeholder(params.len()));
158            }
159        }
160
161        let mut sql = format!(
162            "INSERT INTO {} ({}) VALUES ({})",
163            M::TABLE_NAME,
164            columns.join(", "),
165            placeholders.join(", ")
166        );
167
168        // Add ON CONFLICT clause if specified
169        if let Some(on_conflict) = &self.on_conflict {
170            match on_conflict {
171                OnConflict::DoNothing => {
172                    sql.push_str(" ON CONFLICT DO NOTHING");
173                }
174                OnConflict::DoUpdate {
175                    columns: update_cols,
176                    target,
177                } => {
178                    sql.push_str(" ON CONFLICT");
179
180                    // Add target if specified, otherwise use primary key
181                    // PostgreSQL requires a conflict target for DO UPDATE
182                    let has_target = if !target.is_empty() {
183                        sql.push_str(" (");
184                        sql.push_str(&target.join(", "));
185                        sql.push(')');
186                        true
187                    } else if !M::PRIMARY_KEY.is_empty() {
188                        sql.push_str(" (");
189                        sql.push_str(&M::PRIMARY_KEY.join(", "));
190                        sql.push(')');
191                        true
192                    } else {
193                        // No conflict target available - fall back to DO NOTHING
194                        // since PostgreSQL requires a target for DO UPDATE
195                        tracing::warn!(
196                            table = M::TABLE_NAME,
197                            "ON CONFLICT DO UPDATE requires a conflict target (primary key or explicit target). \
198                             Falling back to DO NOTHING since no target is available."
199                        );
200                        sql.push_str(" DO NOTHING");
201                        false
202                    };
203
204                    // Only add DO UPDATE SET if we have a valid conflict target
205                    if has_target {
206                        sql.push_str(" DO UPDATE SET ");
207
208                        // If columns is empty, update all non-PK columns
209                        let update_set: Vec<String> = if update_cols.is_empty() {
210                            columns // Use full column list from explicit insert
211                                .iter()
212                                .filter(|name| !M::PRIMARY_KEY.contains(name)) // Don't update PK
213                                .map(|name| format!("{} = EXCLUDED.{}", name, name))
214                                .collect()
215                        } else {
216                            update_cols
217                                .iter()
218                                .map(|c| format!("{} = EXCLUDED.{}", c, c))
219                                .collect()
220                        };
221                        sql.push_str(&update_set.join(", "));
222                    }
223                }
224            }
225        }
226
227        // Add RETURNING clause if requested
228        if self.returning {
229            sql.push_str(" RETURNING *");
230        }
231
232        (sql, params)
233    }
234
235    /// Execute the INSERT and return the inserted ID.
236    pub async fn execute<C: Connection>(
237        self,
238        cx: &Cx,
239        conn: &C,
240    ) -> Outcome<i64, sqlmodel_core::Error> {
241        let (sql, params) = self.build_with_dialect(conn.dialect());
242        conn.insert(cx, &sql, &params).await
243    }
244
245    /// Execute the INSERT with RETURNING and get the inserted row.
246    ///
247    /// This automatically adds RETURNING * and returns the full row.
248    pub async fn execute_returning<C: Connection>(
249        mut self,
250        cx: &Cx,
251        conn: &C,
252    ) -> Outcome<Option<Row>, sqlmodel_core::Error> {
253        self.returning = true;
254        let (sql, params) = self.build_with_dialect(conn.dialect());
255        conn.query_one(cx, &sql, &params).await
256    }
257}
258
259/// Bulk INSERT query builder.
260///
261/// # Example
262///
263/// ```ignore
264/// let heroes = vec![hero1, hero2, hero3];
265/// let ids = insert_many!(heroes)
266///     .execute(cx, &conn).await?;
267/// ```
268#[derive(Debug)]
269pub struct InsertManyBuilder<'a, M: Model> {
270    models: &'a [M],
271    returning: bool,
272    on_conflict: Option<OnConflict>,
273}
274
275impl<'a, M: Model> InsertManyBuilder<'a, M> {
276    /// Create a new bulk INSERT builder for the given model instances.
277    pub fn new(models: &'a [M]) -> Self {
278        Self {
279            models,
280            returning: false,
281            on_conflict: None,
282        }
283    }
284
285    /// Add RETURNING * clause to return the inserted rows.
286    pub fn returning(mut self) -> Self {
287        self.returning = true;
288        self
289    }
290
291    /// Handle conflicts by doing nothing.
292    pub fn on_conflict_do_nothing(mut self) -> Self {
293        self.on_conflict = Some(OnConflict::DoNothing);
294        self
295    }
296
297    /// Handle conflicts by updating specified columns.
298    pub fn on_conflict_do_update(mut self, columns: &[&str]) -> Self {
299        self.on_conflict = Some(OnConflict::DoUpdate {
300            columns: columns.iter().map(|s| s.to_string()).collect(),
301            target: Vec::new(),
302        });
303        self
304    }
305
306    /// Build the bulk INSERT SQL and parameters with default dialect.
307    pub fn build(&self) -> (String, Vec<Value>) {
308        self.build_with_dialect(Dialect::default())
309    }
310
311    /// Build the bulk INSERT SQL and parameters with specific dialect.
312    pub fn build_with_dialect(&self, dialect: Dialect) -> (String, Vec<Value>) {
313        if self.models.is_empty() {
314            return (String::new(), Vec::new());
315        }
316
317        let fields = M::fields();
318        let first_row = self.models[0].to_row();
319
320        // Determine which columns to insert
321        // Always include auto-increment fields to handle mixed states (New vs Existing)
322        // For other fields, we still rely on first_row to filter Nulls (standard behavior)
323        let insert_columns: Vec<_> = first_row
324            .iter()
325            .filter(|(name, value)| {
326                let field = fields.iter().find(|f| f.name == *name);
327                if let Some(f) = field {
328                    if f.auto_increment {
329                        return true;
330                    }
331                }
332                !matches!(value, Value::Null)
333            })
334            .map(|(name, _)| *name)
335            .collect();
336
337        let mut all_values = Vec::new();
338        let mut value_groups = Vec::new();
339
340        for model in self.models {
341            let row = model.to_row();
342            let values: Vec<_> = insert_columns
343                .iter()
344                .map(|col| {
345                    let val = row
346                        .iter()
347                        .find(|(name, _)| name == col)
348                        .map_or(Value::Null, |(_, v)| v.clone());
349
350                    // Map Null auto-increment fields to DEFAULT
351                    let field = fields.iter().find(|f| f.name == *col);
352                    if let Some(f) = field {
353                        if f.auto_increment && matches!(val, Value::Null) {
354                            return Value::Default;
355                        }
356                    }
357                    val
358                })
359                .collect();
360
361            let mut placeholders = Vec::new();
362            for v in &values {
363                if matches!(v, Value::Default) {
364                    placeholders.push("DEFAULT".to_string());
365                } else {
366                    all_values.push(v.clone());
367                    placeholders.push(dialect.placeholder(all_values.len()));
368                }
369            }
370
371            value_groups.push(format!("({})", placeholders.join(", ")));
372        }
373
374        let mut sql = format!(
375            "INSERT INTO {} ({}) VALUES {}",
376            M::TABLE_NAME,
377            insert_columns.join(", "),
378            value_groups.join(", ")
379        );
380        // Add ON CONFLICT clause if specified
381        if let Some(on_conflict) = &self.on_conflict {
382            match on_conflict {
383                OnConflict::DoNothing => {
384                    sql.push_str(" ON CONFLICT DO NOTHING");
385                }
386                OnConflict::DoUpdate { columns, target } => {
387                    sql.push_str(" ON CONFLICT");
388
389                    // PostgreSQL requires a conflict target for DO UPDATE
390                    let has_target = if !target.is_empty() {
391                        sql.push_str(" (");
392                        sql.push_str(&target.join(", "));
393                        sql.push(')');
394                        true
395                    } else if !M::PRIMARY_KEY.is_empty() {
396                        sql.push_str(" (");
397                        sql.push_str(&M::PRIMARY_KEY.join(", "));
398                        sql.push(')');
399                        true
400                    } else {
401                        // No conflict target available - fall back to DO NOTHING
402                        tracing::warn!(
403                            table = M::TABLE_NAME,
404                            "ON CONFLICT DO UPDATE requires a conflict target (primary key or explicit target). \
405                             Falling back to DO NOTHING since no target is available."
406                        );
407                        sql.push_str(" DO NOTHING");
408                        false
409                    };
410
411                    // Only add DO UPDATE SET if we have a valid conflict target
412                    if has_target {
413                        sql.push_str(" DO UPDATE SET ");
414
415                        let update_cols: Vec<String> = if columns.is_empty() {
416                            insert_columns
417                                .iter()
418                                .filter(|name| !M::PRIMARY_KEY.contains(name))
419                                .map(|name| format!("{} = EXCLUDED.{}", name, name))
420                                .collect()
421                        } else {
422                            columns
423                                .iter()
424                                .map(|c| format!("{} = EXCLUDED.{}", c, c))
425                                .collect()
426                        };
427                        sql.push_str(&update_cols.join(", "));
428                    }
429                }
430            }
431        }
432
433        if self.returning {
434            sql.push_str(" RETURNING *");
435        }
436
437        (sql, all_values)
438    }
439
440    /// Execute the bulk INSERT and return rows affected.
441    pub async fn execute<C: Connection>(
442        self,
443        cx: &Cx,
444        conn: &C,
445    ) -> Outcome<u64, sqlmodel_core::Error> {
446        if self.models.is_empty() {
447            return Outcome::Ok(0);
448        }
449        let (sql, params) = self.build();
450        conn.execute(cx, &sql, &params).await
451    }
452
453    /// Execute the bulk INSERT with RETURNING and get the inserted rows.
454    pub async fn execute_returning<C: Connection>(
455        mut self,
456        cx: &Cx,
457        conn: &C,
458    ) -> Outcome<Vec<Row>, sqlmodel_core::Error> {
459        if self.models.is_empty() {
460            return Outcome::Ok(Vec::new());
461        }
462        self.returning = true;
463        let (sql, params) = self.build();
464        conn.query(cx, &sql, &params).await
465    }
466}
467
468/// A column-value pair for explicit UPDATE SET operations.
469#[derive(Debug, Clone)]
470pub struct SetClause {
471    column: String,
472    value: Value,
473}
474
475/// UPDATE query builder.
476///
477/// # Example
478///
479/// ```ignore
480/// // Update a model instance (uses primary key for WHERE)
481/// update!(hero).execute(cx, &conn).await?;
482///
483/// // Update with explicit SET
484/// UpdateBuilder::<Hero>::empty()
485///     .set("age", 26)
486///     .set("name", "New Name")
487///     .filter(Expr::col("id").eq(42))
488///     .execute(cx, &conn).await?;
489///
490/// // Update with RETURNING
491/// let row = update!(hero).returning().execute_returning(cx, &conn).await?;
492/// ```
493#[derive(Debug)]
494pub struct UpdateBuilder<'a, M: Model> {
495    model: Option<&'a M>,
496    where_clause: Option<Where>,
497    set_fields: Option<Vec<&'static str>>,
498    explicit_sets: Vec<SetClause>,
499    returning: bool,
500}
501
502impl<'a, M: Model> UpdateBuilder<'a, M> {
503    /// Create a new UPDATE builder for the given model instance.
504    pub fn new(model: &'a M) -> Self {
505        Self {
506            model: Some(model),
507            where_clause: None,
508            set_fields: None,
509            explicit_sets: Vec::new(),
510            returning: false,
511        }
512    }
513
514    /// Create an empty UPDATE builder for explicit SET operations.
515    ///
516    /// Use this when you want to update specific columns without a model instance.
517    pub fn empty() -> Self {
518        Self {
519            model: None,
520            where_clause: None,
521            set_fields: None,
522            explicit_sets: Vec::new(),
523            returning: false,
524        }
525    }
526
527    /// Set a column to a specific value.
528    ///
529    /// This can be used with or without a model instance.
530    /// When used with a model, these explicit sets override the model values.
531    pub fn set<V: Into<Value>>(mut self, column: &str, value: V) -> Self {
532        self.explicit_sets.push(SetClause {
533            column: column.to_string(),
534            value: value.into(),
535        });
536        self
537    }
538
539    /// Only update specific fields from the model.
540    pub fn set_only(mut self, fields: &[&'static str]) -> Self {
541        self.set_fields = Some(fields.to_vec());
542        self
543    }
544
545    /// Add a WHERE condition (defaults to primary key match).
546    pub fn filter(mut self, expr: Expr) -> Self {
547        self.where_clause = Some(match self.where_clause {
548            Some(existing) => existing.and(expr),
549            None => Where::new(expr),
550        });
551        self
552    }
553
554    /// Add RETURNING * clause to return the updated row(s).
555    pub fn returning(mut self) -> Self {
556        self.returning = true;
557        self
558    }
559
560    /// Build the UPDATE SQL and parameters with default dialect (Postgres).
561    pub fn build(&self) -> (String, Vec<Value>) {
562        self.build_with_dialect(Dialect::default())
563    }
564
565    /// Build the UPDATE SQL and parameters with specific dialect.
566    pub fn build_with_dialect(&self, dialect: Dialect) -> (String, Vec<Value>) {
567        let pk = M::PRIMARY_KEY;
568        let mut params = Vec::new();
569        let mut set_clauses = Vec::new();
570
571        // First, add explicit SET clauses
572        for set in &self.explicit_sets {
573            set_clauses.push(format!(
574                "{} = {}",
575                set.column,
576                dialect.placeholder(params.len() + 1)
577            ));
578            params.push(set.value.clone());
579        }
580
581        // Then, add model fields if we have a model
582        if let Some(model) = &self.model {
583            let row = model.to_row();
584
585            // Determine which fields to update
586            let update_fields: Vec<_> = row
587                .iter()
588                .filter(|(name, _)| {
589                    // Skip primary key fields
590                    if pk.contains(name) {
591                        return false;
592                    }
593                    // Skip columns that have explicit sets
594                    if self.explicit_sets.iter().any(|s| s.column == *name) {
595                        return false;
596                    }
597                    // If set_only specified, only include those fields
598                    if let Some(fields) = &self.set_fields {
599                        return fields.contains(name);
600                    }
601                    true
602                })
603                .collect();
604
605            for (name, value) in update_fields {
606                set_clauses.push(format!(
607                    "{} = {}",
608                    name,
609                    dialect.placeholder(params.len() + 1)
610                ));
611                params.push(value.clone());
612            }
613        }
614
615        if set_clauses.is_empty() {
616            // Nothing to update - return empty SQL
617            return (String::new(), Vec::new());
618        }
619
620        let mut sql = format!("UPDATE {} SET {}", M::TABLE_NAME, set_clauses.join(", "));
621
622        // Add WHERE clause
623        if let Some(where_clause) = &self.where_clause {
624            let (where_sql, where_params) = where_clause.build_with_dialect(dialect, params.len());
625            sql.push_str(" WHERE ");
626            sql.push_str(&where_sql);
627            params.extend(where_params);
628        } else if let Some(model) = &self.model {
629            // Default to primary key match
630            let pk_values = model.primary_key_value();
631            let pk_conditions: Vec<_> = pk
632                .iter()
633                .zip(pk_values.iter())
634                .enumerate()
635                .map(|(i, (col, _))| {
636                    format!("{} = {}", col, dialect.placeholder(params.len() + i + 1))
637                })
638                .collect();
639
640            if !pk_conditions.is_empty() {
641                sql.push_str(" WHERE ");
642                sql.push_str(&pk_conditions.join(" AND "));
643                params.extend(pk_values);
644            }
645        }
646
647        // Add RETURNING clause if requested
648        if self.returning {
649            sql.push_str(" RETURNING *");
650        }
651
652        (sql, params)
653    }
654
655    /// Execute the UPDATE and return rows affected.
656    pub async fn execute<C: Connection>(
657        self,
658        cx: &Cx,
659        conn: &C,
660    ) -> Outcome<u64, sqlmodel_core::Error> {
661        let (sql, params) = self.build();
662        if sql.is_empty() {
663            return Outcome::Ok(0);
664        }
665        conn.execute(cx, &sql, &params).await
666    }
667
668    /// Execute the UPDATE with RETURNING and get the updated rows.
669    pub async fn execute_returning<C: Connection>(
670        mut self,
671        cx: &Cx,
672        conn: &C,
673    ) -> Outcome<Vec<Row>, sqlmodel_core::Error> {
674        self.returning = true;
675        let (sql, params) = self.build();
676        if sql.is_empty() {
677            return Outcome::Ok(Vec::new());
678        }
679        conn.query(cx, &sql, &params).await
680    }
681}
682
683/// DELETE query builder.
684///
685/// # Example
686///
687/// ```ignore
688/// // Delete by filter
689/// delete!(Hero)
690///     .filter(Expr::col("age").lt(18))
691///     .execute(cx, &conn).await?;
692///
693/// // Delete a specific model instance
694/// DeleteBuilder::from_model(&hero)
695///     .execute(cx, &conn).await?;
696///
697/// // Delete with RETURNING
698/// let rows = delete!(Hero)
699///     .filter(Expr::col("status").eq("inactive"))
700///     .returning()
701///     .execute_returning(cx, &conn).await?;
702/// ```
703#[derive(Debug)]
704pub struct DeleteBuilder<'a, M: Model> {
705    model: Option<&'a M>,
706    where_clause: Option<Where>,
707    returning: bool,
708    _marker: PhantomData<M>,
709}
710
711impl<'a, M: Model> DeleteBuilder<'a, M> {
712    /// Create a new DELETE builder for the model type.
713    pub fn new() -> Self {
714        Self {
715            model: None,
716            where_clause: None,
717            returning: false,
718            _marker: PhantomData,
719        }
720    }
721
722    /// Create a DELETE builder for a specific model instance.
723    ///
724    /// This automatically adds a WHERE clause matching the primary key.
725    pub fn from_model(model: &'a M) -> Self {
726        Self {
727            model: Some(model),
728            where_clause: None,
729            returning: false,
730            _marker: PhantomData,
731        }
732    }
733
734    /// Add a WHERE condition.
735    pub fn filter(mut self, expr: Expr) -> Self {
736        self.where_clause = Some(match self.where_clause {
737            Some(existing) => existing.and(expr),
738            None => Where::new(expr),
739        });
740        self
741    }
742
743    /// Add RETURNING * clause to return the deleted row(s).
744    pub fn returning(mut self) -> Self {
745        self.returning = true;
746        self
747    }
748
749    /// Build the DELETE SQL and parameters with default dialect (Postgres).
750    pub fn build(&self) -> (String, Vec<Value>) {
751        self.build_with_dialect(Dialect::default())
752    }
753
754    /// Build the DELETE SQL and parameters with specific dialect.
755    pub fn build_with_dialect(&self, dialect: Dialect) -> (String, Vec<Value>) {
756        let mut sql = format!("DELETE FROM {}", M::TABLE_NAME);
757        let mut params = Vec::new();
758
759        if let Some(where_clause) = &self.where_clause {
760            let (where_sql, where_params) = where_clause.build_with_dialect(dialect, 0);
761            sql.push_str(" WHERE ");
762            sql.push_str(&where_sql);
763            params = where_params;
764        } else if let Some(model) = &self.model {
765            // Delete by primary key
766            let pk = M::PRIMARY_KEY;
767            let pk_values = model.primary_key_value();
768            let pk_conditions: Vec<_> = pk
769                .iter()
770                .zip(pk_values.iter())
771                .enumerate()
772                .map(|(i, (col, _))| format!("{} = {}", col, dialect.placeholder(i + 1)))
773                .collect();
774
775            if !pk_conditions.is_empty() {
776                sql.push_str(" WHERE ");
777                sql.push_str(&pk_conditions.join(" AND "));
778                params.extend(pk_values);
779            }
780        }
781
782        // Add RETURNING clause if requested
783        if self.returning {
784            sql.push_str(" RETURNING *");
785        }
786
787        (sql, params)
788    }
789
790    /// Execute the DELETE and return rows affected.
791    pub async fn execute<C: Connection>(
792        self,
793        cx: &Cx,
794        conn: &C,
795    ) -> Outcome<u64, sqlmodel_core::Error> {
796        let (sql, params) = self.build();
797        conn.execute(cx, &sql, &params).await
798    }
799
800    /// Execute the DELETE with RETURNING and get the deleted rows.
801    pub async fn execute_returning<C: Connection>(
802        mut self,
803        cx: &Cx,
804        conn: &C,
805    ) -> Outcome<Vec<Row>, sqlmodel_core::Error> {
806        self.returning = true;
807        let (sql, params) = self.build();
808        conn.query(cx, &sql, &params).await
809    }
810}
811
812impl<M: Model> Default for DeleteBuilder<'_, M> {
813    fn default() -> Self {
814        Self::new()
815    }
816}
817
818/// Query builder for raw SQL with type-safe parameter binding.
819#[derive(Debug)]
820pub struct QueryBuilder {
821    sql: String,
822    params: Vec<Value>,
823}
824
825impl QueryBuilder {
826    /// Create a new query builder with the given SQL.
827    pub fn new(sql: impl Into<String>) -> Self {
828        Self {
829            sql: sql.into(),
830            params: Vec::new(),
831        }
832    }
833
834    /// Bind a parameter value.
835    pub fn bind(mut self, value: impl Into<Value>) -> Self {
836        self.params.push(value.into());
837        self
838    }
839
840    /// Bind multiple parameter values.
841    pub fn bind_all(mut self, values: impl IntoIterator<Item = Value>) -> Self {
842        self.params.extend(values);
843        self
844    }
845
846    /// Get the SQL and parameters.
847    pub fn build(self) -> (String, Vec<Value>) {
848        (self.sql, self.params)
849    }
850}
851
852#[cfg(test)]
853mod tests {
854    use super::*;
855    use crate::expr::Dialect;
856    use sqlmodel_core::field::FieldInfo;
857    use sqlmodel_core::types::SqlType;
858
859    // Mock model for testing
860    struct TestHero {
861        id: Option<i64>,
862        name: String,
863        age: i32,
864    }
865
866    impl Model for TestHero {
867        const TABLE_NAME: &'static str = "heroes";
868        const PRIMARY_KEY: &'static [&'static str] = &["id"];
869
870        fn fields() -> &'static [FieldInfo] {
871            static FIELDS: &[FieldInfo] = &[
872                FieldInfo::new("id", "id", SqlType::BigInt)
873                    .primary_key(true)
874                    .auto_increment(true)
875                    .nullable(true),
876                FieldInfo::new("name", "name", SqlType::Text),
877                FieldInfo::new("age", "age", SqlType::Integer),
878            ];
879            FIELDS
880        }
881
882        fn to_row(&self) -> Vec<(&'static str, Value)> {
883            vec![
884                ("id", self.id.map_or(Value::Null, Value::BigInt)),
885                ("name", Value::Text(self.name.clone())),
886                ("age", Value::Int(self.age)),
887            ]
888        }
889
890        fn from_row(_row: &Row) -> sqlmodel_core::Result<Self> {
891            unimplemented!()
892        }
893
894        fn primary_key_value(&self) -> Vec<Value> {
895            vec![self.id.map_or(Value::Null, Value::BigInt)]
896        }
897
898        fn is_new(&self) -> bool {
899            self.id.is_none()
900        }
901    }
902
903    #[test]
904    fn test_insert_basic() {
905        let hero = TestHero {
906            id: None,
907            name: "Spider-Man".to_string(),
908            age: 25,
909        };
910        let (sql, params) = InsertBuilder::new(&hero).build();
911
912        // Auto-increment column with None gets DEFAULT, other columns get placeholders
913        assert_eq!(
914            sql,
915            "INSERT INTO heroes (id, name, age) VALUES (DEFAULT, $1, $2)"
916        );
917        assert_eq!(params.len(), 2);
918    }
919
920    #[test]
921    fn test_insert_returning() {
922        let hero = TestHero {
923            id: None,
924            name: "Spider-Man".to_string(),
925            age: 25,
926        };
927        let (sql, _) = InsertBuilder::new(&hero).returning().build();
928
929        assert!(sql.ends_with(" RETURNING *"));
930    }
931
932    #[test]
933    fn test_insert_on_conflict_do_nothing() {
934        let hero = TestHero {
935            id: None,
936            name: "Spider-Man".to_string(),
937            age: 25,
938        };
939        let (sql, _) = InsertBuilder::new(&hero).on_conflict_do_nothing().build();
940
941        assert!(sql.contains("ON CONFLICT DO NOTHING"));
942    }
943
944    #[test]
945    fn test_insert_on_conflict_do_update() {
946        let hero = TestHero {
947            id: None,
948            name: "Spider-Man".to_string(),
949            age: 25,
950        };
951        let (sql, _) = InsertBuilder::new(&hero)
952            .on_conflict_do_update(&["name", "age"])
953            .build();
954
955        assert!(sql.contains("ON CONFLICT (id) DO UPDATE SET"));
956        assert!(sql.contains("name = EXCLUDED.name"));
957        assert!(sql.contains("age = EXCLUDED.age"));
958    }
959
960    #[test]
961    fn test_insert_many() {
962        let heroes = vec![
963            TestHero {
964                id: None,
965                name: "Spider-Man".to_string(),
966                age: 25,
967            },
968            TestHero {
969                id: None,
970                name: "Iron Man".to_string(),
971                age: 45,
972            },
973        ];
974        let (sql, params) = InsertManyBuilder::new(&heroes).build();
975
976        // Auto-increment columns with None get DEFAULT, other columns get placeholders
977        assert!(sql.starts_with("INSERT INTO heroes (id, name, age) VALUES"));
978        assert!(sql.contains("(DEFAULT, $1, $2), (DEFAULT, $3, $4)"));
979        assert_eq!(params.len(), 4);
980    }
981
982    #[test]
983    fn test_update_basic() {
984        let hero = TestHero {
985            id: Some(1),
986            name: "Spider-Man".to_string(),
987            age: 26,
988        };
989        let (sql, params) = UpdateBuilder::new(&hero).build();
990
991        assert!(sql.starts_with("UPDATE heroes SET"));
992        assert!(sql.contains("WHERE id = "));
993        assert!(params.len() >= 2); // At least name, age, and id
994    }
995
996    #[test]
997    fn test_update_explicit_set() {
998        let (sql, params) = UpdateBuilder::<TestHero>::empty()
999            .set("age", 30)
1000            .filter(Expr::col("id").eq(1))
1001            .build_with_dialect(Dialect::Postgres);
1002
1003        assert_eq!(sql, "UPDATE heroes SET age = $1 WHERE \"id\" = $2");
1004        assert_eq!(params.len(), 2);
1005    }
1006
1007    #[test]
1008    fn test_update_returning() {
1009        let hero = TestHero {
1010            id: Some(1),
1011            name: "Spider-Man".to_string(),
1012            age: 26,
1013        };
1014        let (sql, _) = UpdateBuilder::new(&hero).returning().build();
1015
1016        assert!(sql.ends_with(" RETURNING *"));
1017    }
1018
1019    #[test]
1020    fn test_delete_basic() {
1021        let (sql, _) = DeleteBuilder::<TestHero>::new()
1022            .filter(Expr::col("age").lt(18))
1023            .build_with_dialect(Dialect::Postgres);
1024
1025        assert_eq!(sql, "DELETE FROM heroes WHERE \"age\" < $1");
1026    }
1027
1028    #[test]
1029    fn test_delete_from_model() {
1030        let hero = TestHero {
1031            id: Some(42),
1032            name: "Spider-Man".to_string(),
1033            age: 25,
1034        };
1035        let (sql, params) = DeleteBuilder::from_model(&hero).build();
1036
1037        assert!(sql.contains("WHERE id = $1"));
1038        assert_eq!(params.len(), 1);
1039    }
1040
1041    #[test]
1042    fn test_delete_returning() {
1043        let (sql, _) = DeleteBuilder::<TestHero>::new()
1044            .filter(Expr::col("status").eq("inactive"))
1045            .returning()
1046            .build_with_dialect(Dialect::Postgres);
1047
1048        assert!(sql.ends_with(" RETURNING *"));
1049    }
1050
1051    #[test]
1052    fn test_dialect_sqlite() {
1053        let hero = TestHero {
1054            id: None,
1055            name: "Spider-Man".to_string(),
1056            age: 25,
1057        };
1058        let (sql, _) = InsertBuilder::new(&hero).build_with_dialect(Dialect::Sqlite);
1059
1060        assert!(sql.contains("?1"));
1061        assert!(sql.contains("?2"));
1062    }
1063
1064    #[test]
1065    fn test_dialect_mysql() {
1066        let hero = TestHero {
1067            id: None,
1068            name: "Spider-Man".to_string(),
1069            age: 25,
1070        };
1071        let (sql, _) = InsertBuilder::new(&hero).build_with_dialect(Dialect::Mysql);
1072
1073        // MySQL uses ? without numbers
1074        assert!(sql.contains('?'));
1075        assert!(!sql.contains("$1"));
1076    }
1077}
sqlmodel_query/builder.rs

sqlmodel_query/
builder.rs
