fletch_orm/
query_builder.rs

1//! Type-safe SQL query builder with dialect support.
2//!
3//! The [`QueryBuilder`] generates parameterized SQL statements for SELECT,
4//! INSERT, UPDATE, and DELETE operations across SQLite, PostgreSQL, and MySQL.
5
6use crate::built_query::BuiltQuery;
7use crate::dialect::Dialect;
8use crate::filter::{Filter, Op};
9use crate::value::Value;
10
11/// The sort direction for ORDER BY clauses.
12#[derive(Debug, Clone, Copy, PartialEq, Eq)]
13pub enum Order {
14    Asc,
15    Desc,
16}
17
18/// Builds parameterized SQL statements for a specific dialect.
19///
20/// # Usage
21///
22/// ```
23/// use fletch_orm::query_builder::{QueryBuilder, Order};
24/// use fletch_orm::dialect::PostgresDialect;
25/// use fletch_orm::filter::{Filter, Op};
26///
27/// let dialect = PostgresDialect;
28/// let query = QueryBuilder::select(&dialect, "users")
29///     .columns(&["id", "name", "email"])
30///     .filter(Filter::new("name", Op::Eq, "Alice"))
31///     .order_by("id", Order::Asc)
32///     .limit(10)
33///     .build();
34///
35/// assert_eq!(
36///     query.sql,
37///     "SELECT \"id\", \"name\", \"email\" FROM \"users\" WHERE \"name\" = $1 ORDER BY \"id\" ASC LIMIT 10"
38/// );
39/// ```
40pub struct QueryBuilder<'d> {
41    dialect: &'d dyn Dialect,
42    table: String,
43    kind: QueryKind,
44    columns: Vec<String>,
45    filters: Vec<Filter>,
46    order_by: Vec<(String, Order)>,
47    limit: Option<u64>,
48    offset: Option<u64>,
49    /// Column-value pairs for INSERT/UPDATE SET clauses.
50    set_values: Vec<(String, Value)>,
51    /// Columns to return (for RETURNING clause).
52    returning: Vec<String>,
53}
54
55#[derive(Debug, Clone, Copy)]
56enum QueryKind {
57    Select,
58    Insert,
59    Update,
60    Delete,
61}
62
63impl<'d> QueryBuilder<'d> {
64    /// Begin a SELECT query.
65    pub fn select(dialect: &'d dyn Dialect, table: &str) -> Self {
66        Self::new(dialect, table, QueryKind::Select)
67    }
68
69    /// Begin an INSERT query.
70    pub fn insert(dialect: &'d dyn Dialect, table: &str) -> Self {
71        Self::new(dialect, table, QueryKind::Insert)
72    }
73
74    /// Begin an UPDATE query.
75    pub fn update(dialect: &'d dyn Dialect, table: &str) -> Self {
76        Self::new(dialect, table, QueryKind::Update)
77    }
78
79    /// Begin a DELETE query.
80    pub fn delete(dialect: &'d dyn Dialect, table: &str) -> Self {
81        Self::new(dialect, table, QueryKind::Delete)
82    }
83
84    fn new(dialect: &'d dyn Dialect, table: &str, kind: QueryKind) -> Self {
85        Self {
86            dialect,
87            table: table.to_owned(),
88            kind,
89            columns: Vec::new(),
90            filters: Vec::new(),
91            order_by: Vec::new(),
92            limit: None,
93            offset: None,
94            set_values: Vec::new(),
95            returning: Vec::new(),
96        }
97    }
98
99    /// Set the columns to select (SELECT) or insert into (INSERT).
100    pub fn columns(mut self, cols: &[&str]) -> Self {
101        self.columns = cols.iter().map(|c| (*c).to_owned()).collect();
102        self
103    }
104
105    /// Add a filter condition to the WHERE clause.
106    pub fn filter(mut self, f: Filter) -> Self {
107        self.filters.push(f);
108        self
109    }
110
111    /// Add an ORDER BY clause.
112    pub fn order_by(mut self, column: &str, order: Order) -> Self {
113        self.order_by.push((column.to_owned(), order));
114        self
115    }
116
117    /// Set the LIMIT.
118    pub fn limit(mut self, n: u64) -> Self {
119        self.limit = Some(n);
120        self
121    }
122
123    /// Set the OFFSET.
124    pub fn offset(mut self, n: u64) -> Self {
125        self.offset = Some(n);
126        self
127    }
128
129    /// Set a column value for INSERT or UPDATE operations.
130    pub fn set(mut self, column: &str, value: impl Into<Value>) -> Self {
131        self.set_values.push((column.to_owned(), value.into()));
132        self
133    }
134
135    /// Specify columns to return via `RETURNING` (Postgres/SQLite).
136    /// On MySQL, this is ignored; use `LAST_INSERT_ID()` instead.
137    pub fn returning(mut self, cols: &[&str]) -> Self {
138        self.returning = cols.iter().map(|c| (*c).to_owned()).collect();
139        self
140    }
141
142    /// Build the SQL query and return the SQL string with bind values.
143    pub fn build(self) -> BuiltQuery {
144        match self.kind {
145            QueryKind::Select => self.build_select(),
146            QueryKind::Insert => self.build_insert(),
147            QueryKind::Update => self.build_update(),
148            QueryKind::Delete => self.build_delete(),
149        }
150    }
151
152    fn build_select(self) -> BuiltQuery {
153        let mut values = Vec::new();
154        let mut param_index = 1usize;
155
156        let cols = if self.columns.is_empty() {
157            "*".to_owned()
158        } else {
159            self.columns
160                .iter()
161                .map(|c| self.dialect.quote_identifier(c))
162                .collect::<Vec<_>>()
163                .join(", ")
164        };
165
166        let table = self.dialect.quote_identifier(&self.table);
167        let mut sql = format!("SELECT {cols} FROM {table}");
168
169        let where_clause = self.build_where(&mut values, &mut param_index);
170        if !where_clause.is_empty() {
171            sql.push_str(" WHERE ");
172            sql.push_str(&where_clause);
173        }
174
175        if !self.order_by.is_empty() {
176            sql.push_str(" ORDER BY ");
177            let orders: Vec<String> = self
178                .order_by
179                .iter()
180                .map(|(col, ord)| {
181                    let dir = match ord {
182                        Order::Asc => "ASC",
183                        Order::Desc => "DESC",
184                    };
185                    format!("{} {}", self.dialect.quote_identifier(col), dir)
186                })
187                .collect();
188            sql.push_str(&orders.join(", "));
189        }
190
191        if let Some(limit) = self.limit {
192            use std::fmt::Write;
193            let _ = write!(sql, " LIMIT {limit}");
194        }
195
196        if let Some(offset) = self.offset {
197            use std::fmt::Write;
198            let _ = write!(sql, " OFFSET {offset}");
199        }
200
201        BuiltQuery { sql, values }
202    }
203
204    fn build_insert(self) -> BuiltQuery {
205        let mut values = Vec::new();
206
207        let table = self.dialect.quote_identifier(&self.table);
208
209        let mut col_names = Vec::new();
210        let mut placeholders = Vec::new();
211
212        for (param_index, (col, val)) in (1usize..).zip(self.set_values.iter()) {
213            col_names.push(self.dialect.quote_identifier(col));
214            placeholders.push(self.dialect.bind_parameter(param_index));
215            values.push(val.clone());
216        }
217
218        let mut sql = format!(
219            "INSERT INTO {} ({}) VALUES ({})",
220            table,
221            col_names.join(", "),
222            placeholders.join(", "),
223        );
224
225        if self.dialect.supports_returning() && !self.returning.is_empty() {
226            let ret_cols: Vec<String> = self
227                .returning
228                .iter()
229                .map(|c| self.dialect.quote_identifier(c))
230                .collect();
231            sql.push_str(" RETURNING ");
232            sql.push_str(&ret_cols.join(", "));
233        }
234
235        BuiltQuery { sql, values }
236    }
237
238    fn build_update(self) -> BuiltQuery {
239        let mut values = Vec::new();
240        let mut param_index = 1usize;
241
242        let table = self.dialect.quote_identifier(&self.table);
243
244        let mut assignments = Vec::new();
245        for (col, val) in &self.set_values {
246            let placeholder = self.dialect.bind_parameter(param_index);
247            assignments.push(format!(
248                "{} = {}",
249                self.dialect.quote_identifier(col),
250                placeholder
251            ));
252            values.push(val.clone());
253            param_index += 1;
254        }
255
256        let mut sql = format!("UPDATE {} SET {}", table, assignments.join(", "));
257
258        let where_clause = self.build_where(&mut values, &mut param_index);
259        if !where_clause.is_empty() {
260            sql.push_str(" WHERE ");
261            sql.push_str(&where_clause);
262        }
263
264        if self.dialect.supports_returning() && !self.returning.is_empty() {
265            let ret_cols: Vec<String> = self
266                .returning
267                .iter()
268                .map(|c| self.dialect.quote_identifier(c))
269                .collect();
270            sql.push_str(" RETURNING ");
271            sql.push_str(&ret_cols.join(", "));
272        }
273
274        BuiltQuery { sql, values }
275    }
276
277    fn build_delete(self) -> BuiltQuery {
278        let mut values = Vec::new();
279        let mut param_index = 1usize;
280
281        let table = self.dialect.quote_identifier(&self.table);
282        let mut sql = format!("DELETE FROM {table}");
283
284        let where_clause = self.build_where(&mut values, &mut param_index);
285        if !where_clause.is_empty() {
286            sql.push_str(" WHERE ");
287            sql.push_str(&where_clause);
288        }
289
290        if self.dialect.supports_returning() && !self.returning.is_empty() {
291            let ret_cols: Vec<String> = self
292                .returning
293                .iter()
294                .map(|c| self.dialect.quote_identifier(c))
295                .collect();
296            sql.push_str(" RETURNING ");
297            sql.push_str(&ret_cols.join(", "));
298        }
299
300        BuiltQuery { sql, values }
301    }
302
303    /// Build the WHERE clause fragments from filters, appending values and
304    /// advancing `param_index`. Returns the clause string (without "WHERE").
305    #[expect(
306        clippy::unreachable,
307        reason = "In/NotIn/IsNull/IsNotNull variants are handled by arms above; remaining ops are exhaustive"
308    )]
309    fn build_where(&self, values: &mut Vec<Value>, param_index: &mut usize) -> String {
310        if self.filters.is_empty() {
311            return String::new();
312        }
313
314        let mut parts = Vec::new();
315
316        for filter in &self.filters {
317            let quoted_col = self.dialect.quote_identifier(&filter.column);
318
319            let part = match filter.op {
320                Op::IsNull => format!("{quoted_col} IS NULL"),
321                Op::IsNotNull => format!("{quoted_col} IS NOT NULL"),
322                Op::In => {
323                    let placeholders: Vec<String> = filter
324                        .values
325                        .iter()
326                        .map(|v| {
327                            let p = self.dialect.bind_parameter(*param_index);
328                            values.push(v.clone());
329                            *param_index += 1;
330                            p
331                        })
332                        .collect();
333                    format!("{} IN ({})", quoted_col, placeholders.join(", "))
334                }
335                op => {
336                    let sql_op = match op {
337                        Op::Eq => "=",
338                        Op::Ne => "<>",
339                        Op::Lt => "<",
340                        Op::Le => "<=",
341                        Op::Gt => ">",
342                        Op::Ge => ">=",
343                        Op::Like => "LIKE",
344                        _ => unreachable!(),
345                    };
346                    let placeholder = self.dialect.bind_parameter(*param_index);
347                    if let Some(v) = filter.values.first() {
348                        values.push(v.clone());
349                    }
350                    *param_index += 1;
351                    format!("{quoted_col} {sql_op} {placeholder}")
352                }
353            };
354
355            parts.push(part);
356        }
357
358        parts.join(" AND ")
359    }
360}
361
362#[cfg(test)]
363mod tests {
364    use super::*;
365    use crate::dialect::{MySqlDialect, PostgresDialect, SqliteDialect};
366
367    // ─── SELECT ──────────────────────────────────────────────
368
369    #[test]
370    fn select_all_columns() {
371        let d = SqliteDialect;
372        let q = QueryBuilder::select(&d, "users").build();
373        assert_eq!(q.sql, "SELECT * FROM \"users\"");
374        assert!(q.values.is_empty());
375    }
376
377    #[test]
378    fn select_specific_columns() {
379        let d = PostgresDialect;
380        let q = QueryBuilder::select(&d, "users")
381            .columns(&["id", "name"])
382            .build();
383        assert_eq!(q.sql, "SELECT \"id\", \"name\" FROM \"users\"");
384    }
385
386    #[test]
387    fn select_with_eq_filter_postgres() {
388        let d = PostgresDialect;
389        let q = QueryBuilder::select(&d, "users")
390            .columns(&["id", "name"])
391            .filter(Filter::new("name", Op::Eq, "Alice"))
392            .build();
393        assert_eq!(
394            q.sql,
395            "SELECT \"id\", \"name\" FROM \"users\" WHERE \"name\" = $1"
396        );
397        assert_eq!(q.values, vec![Value::Text("Alice".into())]);
398    }
399
400    #[test]
401    fn select_with_eq_filter_sqlite() {
402        let d = SqliteDialect;
403        let q = QueryBuilder::select(&d, "users")
404            .columns(&["id", "name"])
405            .filter(Filter::new("name", Op::Eq, "Alice"))
406            .build();
407        assert_eq!(
408            q.sql,
409            "SELECT \"id\", \"name\" FROM \"users\" WHERE \"name\" = ?"
410        );
411        assert_eq!(q.values, vec![Value::Text("Alice".into())]);
412    }
413
414    #[test]
415    fn select_with_eq_filter_mysql() {
416        let d = MySqlDialect;
417        let q = QueryBuilder::select(&d, "users")
418            .columns(&["id", "name"])
419            .filter(Filter::new("name", Op::Eq, "Alice"))
420            .build();
421        assert_eq!(q.sql, "SELECT `id`, `name` FROM `users` WHERE `name` = ?");
422    }
423
424    #[test]
425    fn select_with_multiple_filters() {
426        let d = PostgresDialect;
427        let q = QueryBuilder::select(&d, "users")
428            .filter(Filter::new("age", Op::Ge, 18))
429            .filter(Filter::new("active", Op::Eq, true))
430            .build();
431        assert_eq!(
432            q.sql,
433            "SELECT * FROM \"users\" WHERE \"age\" >= $1 AND \"active\" = $2"
434        );
435        assert_eq!(q.values, vec![Value::I32(18), Value::Bool(true)]);
436    }
437
438    #[test]
439    fn select_with_in_filter() {
440        let d = PostgresDialect;
441        let q = QueryBuilder::select(&d, "users")
442            .filter(Filter::in_list(
443                "id",
444                vec![Value::I64(1), Value::I64(2), Value::I64(3)],
445            ))
446            .build();
447        assert_eq!(
448            q.sql,
449            "SELECT * FROM \"users\" WHERE \"id\" IN ($1, $2, $3)"
450        );
451        assert_eq!(q.values, vec![Value::I64(1), Value::I64(2), Value::I64(3)]);
452    }
453
454    #[test]
455    fn select_with_is_null() {
456        let d = SqliteDialect;
457        let q = QueryBuilder::select(&d, "users")
458            .filter(Filter::is_null("deleted_at"))
459            .build();
460        assert_eq!(
461            q.sql,
462            "SELECT * FROM \"users\" WHERE \"deleted_at\" IS NULL"
463        );
464        assert!(q.values.is_empty());
465    }
466
467    #[test]
468    fn select_with_is_not_null() {
469        let d = PostgresDialect;
470        let q = QueryBuilder::select(&d, "users")
471            .filter(Filter::is_not_null("email"))
472            .build();
473        assert_eq!(q.sql, "SELECT * FROM \"users\" WHERE \"email\" IS NOT NULL");
474    }
475
476    #[test]
477    fn select_with_like() {
478        let d = SqliteDialect;
479        let q = QueryBuilder::select(&d, "users")
480            .filter(Filter::new("name", Op::Like, "%alice%"))
481            .build();
482        assert_eq!(q.sql, "SELECT * FROM \"users\" WHERE \"name\" LIKE ?");
483        assert_eq!(q.values, vec![Value::Text("%alice%".into())]);
484    }
485
486    #[test]
487    fn select_with_order_by() {
488        let d = PostgresDialect;
489        let q = QueryBuilder::select(&d, "users")
490            .order_by("name", Order::Asc)
491            .order_by("id", Order::Desc)
492            .build();
493        assert_eq!(
494            q.sql,
495            "SELECT * FROM \"users\" ORDER BY \"name\" ASC, \"id\" DESC"
496        );
497    }
498
499    #[test]
500    fn select_with_limit_offset() {
501        let d = SqliteDialect;
502        let q = QueryBuilder::select(&d, "posts")
503            .limit(10)
504            .offset(20)
505            .build();
506        assert_eq!(q.sql, "SELECT * FROM \"posts\" LIMIT 10 OFFSET 20");
507    }
508
509    #[test]
510    fn select_full_query_postgres() {
511        let d = PostgresDialect;
512        let q = QueryBuilder::select(&d, "users")
513            .columns(&["id", "name", "email"])
514            .filter(Filter::new("active", Op::Eq, true))
515            .filter(Filter::new("age", Op::Gt, 18))
516            .order_by("name", Order::Asc)
517            .limit(25)
518            .offset(50)
519            .build();
520        assert_eq!(
521            q.sql,
522            "SELECT \"id\", \"name\", \"email\" FROM \"users\" WHERE \"active\" = $1 AND \"age\" > $2 ORDER BY \"name\" ASC LIMIT 25 OFFSET 50"
523        );
524        assert_eq!(q.values, vec![Value::Bool(true), Value::I32(18)]);
525    }
526
527    // ─── INSERT ──────────────────────────────────────────────
528
529    #[test]
530    fn insert_sqlite() {
531        let d = SqliteDialect;
532        let q = QueryBuilder::insert(&d, "users")
533            .set("name", "Alice")
534            .set("email", "alice@example.com")
535            .returning(&["id"])
536            .build();
537        assert_eq!(
538            q.sql,
539            "INSERT INTO \"users\" (\"name\", \"email\") VALUES (?, ?) RETURNING \"id\""
540        );
541        assert_eq!(
542            q.values,
543            vec![
544                Value::Text("Alice".into()),
545                Value::Text("alice@example.com".into()),
546            ]
547        );
548    }
549
550    #[test]
551    fn insert_postgres() {
552        let d = PostgresDialect;
553        let q = QueryBuilder::insert(&d, "users")
554            .set("name", "Bob")
555            .set("age", 30)
556            .returning(&["id"])
557            .build();
558        assert_eq!(
559            q.sql,
560            "INSERT INTO \"users\" (\"name\", \"age\") VALUES ($1, $2) RETURNING \"id\""
561        );
562        assert_eq!(q.values, vec![Value::Text("Bob".into()), Value::I32(30)]);
563    }
564
565    #[test]
566    fn insert_mysql_no_returning() {
567        let d = MySqlDialect;
568        let q = QueryBuilder::insert(&d, "users")
569            .set("name", "Charlie")
570            .returning(&["id"])
571            .build();
572        // MySQL does not support RETURNING
573        assert_eq!(q.sql, "INSERT INTO `users` (`name`) VALUES (?)");
574        assert_eq!(q.values, vec![Value::Text("Charlie".into())]);
575    }
576
577    // ─── UPDATE ──────────────────────────────────────────────
578
579    #[test]
580    fn update_postgres() {
581        let d = PostgresDialect;
582        let q = QueryBuilder::update(&d, "users")
583            .set("name", "Updated")
584            .set("age", 25)
585            .filter(Filter::new("id", Op::Eq, 1i64))
586            .build();
587        assert_eq!(
588            q.sql,
589            "UPDATE \"users\" SET \"name\" = $1, \"age\" = $2 WHERE \"id\" = $3"
590        );
591        assert_eq!(
592            q.values,
593            vec![Value::Text("Updated".into()), Value::I32(25), Value::I64(1),]
594        );
595    }
596
597    #[test]
598    fn update_sqlite() {
599        let d = SqliteDialect;
600        let q = QueryBuilder::update(&d, "users")
601            .set("name", "Updated")
602            .filter(Filter::new("id", Op::Eq, 1i64))
603            .returning(&["id", "name"])
604            .build();
605        assert_eq!(
606            q.sql,
607            "UPDATE \"users\" SET \"name\" = ? WHERE \"id\" = ? RETURNING \"id\", \"name\""
608        );
609    }
610
611    #[test]
612    fn update_mysql() {
613        let d = MySqlDialect;
614        let q = QueryBuilder::update(&d, "users")
615            .set("active", false)
616            .filter(Filter::new("id", Op::Eq, 42i64))
617            .build();
618        assert_eq!(q.sql, "UPDATE `users` SET `active` = ? WHERE `id` = ?");
619        assert_eq!(q.values, vec![Value::Bool(false), Value::I64(42)]);
620    }
621
622    // ─── DELETE ──────────────────────────────────────────────
623
624    #[test]
625    fn delete_postgres() {
626        let d = PostgresDialect;
627        let q = QueryBuilder::delete(&d, "users")
628            .filter(Filter::new("id", Op::Eq, 1i64))
629            .build();
630        assert_eq!(q.sql, "DELETE FROM \"users\" WHERE \"id\" = $1");
631        assert_eq!(q.values, vec![Value::I64(1)]);
632    }
633
634    #[test]
635    fn delete_sqlite() {
636        let d = SqliteDialect;
637        let q = QueryBuilder::delete(&d, "posts")
638            .filter(Filter::new("id", Op::Eq, 5i64))
639            .returning(&["id"])
640            .build();
641        assert_eq!(
642            q.sql,
643            "DELETE FROM \"posts\" WHERE \"id\" = ? RETURNING \"id\""
644        );
645    }
646
647    #[test]
648    fn delete_mysql_no_returning() {
649        let d = MySqlDialect;
650        let q = QueryBuilder::delete(&d, "posts")
651            .filter(Filter::new("id", Op::Eq, 5i64))
652            .returning(&["id"])
653            .build();
654        // MySQL doesn't support RETURNING
655        assert_eq!(q.sql, "DELETE FROM `posts` WHERE `id` = ?");
656    }
657
658    #[test]
659    fn delete_all_rows() {
660        let d = SqliteDialect;
661        let q = QueryBuilder::delete(&d, "logs").build();
662        assert_eq!(q.sql, "DELETE FROM \"logs\"");
663        assert!(q.values.is_empty());
664    }
665
666    // ─── BIND PARAMETER NUMBERING ───────────────────────────
667
668    #[test]
669    fn postgres_bind_params_numbered_correctly() {
670        let d = PostgresDialect;
671        let q = QueryBuilder::update(&d, "users")
672            .set("name", "A")
673            .set("email", "a@b.com")
674            .set("age", 30)
675            .filter(Filter::new("id", Op::Eq, 1i64))
676            .filter(Filter::new("active", Op::Eq, true))
677            .build();
678        assert_eq!(
679            q.sql,
680            "UPDATE \"users\" SET \"name\" = $1, \"email\" = $2, \"age\" = $3 WHERE \"id\" = $4 AND \"active\" = $5"
681        );
682        assert_eq!(q.values.len(), 5);
683    }
684
685    // ─── COMPARISON OPERATORS ───────────────────────────────
686
687    #[test]
688    fn ne_operator() {
689        let d = PostgresDialect;
690        let q = QueryBuilder::select(&d, "users")
691            .filter(Filter::new("status", Op::Ne, "banned"))
692            .build();
693        assert_eq!(q.sql, "SELECT * FROM \"users\" WHERE \"status\" <> $1");
694    }
695
696    #[test]
697    fn lt_le_gt_ge_operators() {
698        let d = PostgresDialect;
699        let q = QueryBuilder::select(&d, "products")
700            .filter(Filter::new("price", Op::Lt, 100i32))
701            .filter(Filter::new("stock", Op::Le, 50i32))
702            .filter(Filter::new("rating", Op::Gt, 3i32))
703            .filter(Filter::new("reviews", Op::Ge, 10i32))
704            .build();
705        assert_eq!(
706            q.sql,
707            "SELECT * FROM \"products\" WHERE \"price\" < $1 AND \"stock\" <= $2 AND \"rating\" > $3 AND \"reviews\" >= $4"
708        );
709    }
710}
fletch_orm/query_builder.rs

fletch_orm/
query_builder.rs
