mik_sql/
lib.rs

1// =============================================================================
2// CRATE-LEVEL QUALITY LINTS (following Tokio/Serde standards)
3// =============================================================================
4#![forbid(unsafe_code)]
5#![deny(unused_must_use)]
6#![warn(missing_debug_implementations)]
7#![warn(rust_2018_idioms)]
8#![warn(unreachable_pub)]
9// =============================================================================
10// CLIPPY CONFIGURATION
11// =============================================================================
12// Pedantic lints that are too verbose to fix individually in a DSL-heavy crate
13#![allow(clippy::doc_markdown)] // Code items in docs - extensive doc changes needed
14#![allow(clippy::missing_errors_doc)] // # Errors sections - doc-heavy
15#![allow(clippy::missing_panics_doc)] // # Panics sections - doc-heavy
16#![allow(clippy::items_after_statements)] // const in functions - intentional for locality
17#![allow(clippy::module_name_repetitions)] // Type names matching module - acceptable
18#![allow(clippy::return_self_not_must_use)] // Builder pattern methods return Self by design
19#![allow(clippy::must_use_candidate)] // Builder methods - fluent API doesn't need must_use
20#![allow(clippy::match_same_arms)] // Intentional for clarity in some match expressions
21#![allow(clippy::format_push_string)] // String building style preference
22#![allow(clippy::cast_possible_truncation)] // Intentional in SQL context
23#![allow(clippy::cast_sign_loss)] // Intentional in SQL context
24#![allow(clippy::cast_possible_wrap)] // Intentional in SQL context
25
26//! # mik-sql - SQL Query Builder with Mongo-style Filters
27//!
28//! A standalone SQL query builder with intuitive macro syntax for CRUD operations.
29//! Supports Postgres and `SQLite` dialects.
30//!
31//! ## Quick Start
32//!
33//! ```ignore
34//! use mik_sql::prelude::*;
35//!
36//! // SELECT - Read data
37//! let (sql, params) = sql_read!(users {
38//!     select: [id, name, email],
39//!     filter: { active: true },
40//!     order: name,
41//!     limit: 10,
42//! });
43//! // → "SELECT id, name, email FROM users WHERE active = $1 ORDER BY name LIMIT 10"
44//!
45//! // INSERT - Create data
46//! let (sql, params) = sql_create!(users {
47//!     name: str(name),
48//!     email: str(email),
49//!     returning: [id],
50//! });
51//! // → "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id"
52//!
53//! // UPDATE - Update data
54//! let (sql, params) = sql_update!(users {
55//!     set: { name: str(new_name) },
56//!     filter: { id: int(user_id) },
57//! });
58//! // → "UPDATE users SET name = $1 WHERE id = $2"
59//!
60//! // DELETE - Delete data
61//! let (sql, params) = sql_delete!(users {
62//!     filter: { id: int(user_id) },
63//! });
64//! // → "DELETE FROM users WHERE id = $1"
65//! ```
66//!
67//! ## `SQLite` Dialect
68//!
69//! Add `sqlite` as first parameter for `SQLite` syntax (?1, ?2 instead of $1, $2):
70//!
71//! ```ignore
72//! let (sql, params) = sql_read!(sqlite, users { ... });
73//! ```
74//!
75//! ## Supported Operators
76//!
77//! | Operator | SQL | Example |
78//! |----------|-----|---------|
79//! | `$eq` | `=` | `"status": { "$eq": "active" }` |
80//! | `$ne` | `!=` | `"status": { "$ne": "deleted" }` |
81//! | `$gt` | `>` | `"age": { "$gt": 18 }` |
82//! | `$gte` | `>=` | `"age": { "$gte": 21 }` |
83//! | `$lt` | `<` | `"price": { "$lt": 100 }` |
84//! | `$lte` | `<=` | `"price": { "$lte": 50 }` |
85//! | `$in` | `IN` | `"status": { "$in": ["a", "b"] }` |
86//! | `$nin` | `NOT IN` | `"status": { "$nin": ["x"] }` |
87//! | `$like` | `LIKE` | `"name": { "$like": "%test%" }` |
88//! | `$ilike` | `ILIKE` | `"name": { "$ilike": "%test%" }` |
89//! | `$starts_with` | `LIKE $1 \|\| '%'` | `"name": { "$starts_with": "John" }` |
90//! | `$ends_with` | `LIKE '%' \|\| $1` | `"email": { "$ends_with": "@example.com" }` |
91//! | `$contains` | `LIKE '%' \|\| $1 \|\| '%'` | `"bio": { "$contains": "developer" }` |
92//! | `$between` | `BETWEEN $1 AND $2` | `"age": { "$between": [18, 65] }` |
93//!
94//! ## Cursor Pagination
95//!
96//! ```ignore
97//! use mik_sql::prelude::*;
98//!
99//! let (sql, params) = sql_read!(posts {
100//!     select: [id, title, created_at],
101//!     filter: { published: true },
102//!     order: [-created_at, -id],
103//!     after: req.query("after"),
104//!     limit: 20,
105//! });
106//!
107//! // Create cursor for next page
108//! let next_cursor = Cursor::new()
109//!     .string("created_at", &last_item.created_at)
110//!     .int("id", last_item.id)
111//!     .encode();
112//! ```
113
114mod builder;
115mod dialect;
116mod pagination;
117mod validate;
118
119pub use builder::{
120    Aggregate, AggregateFunc, CompoundFilter, ComputedField, CursorDirection, DeleteBuilder,
121    Filter, FilterExpr, InsertBuilder, LogicalOp, Operator, QueryBuilder, QueryResult, SortDir,
122    SortField, UpdateBuilder, Value, and, delete, insert, not, or, simple, update,
123};
124
125// Internal functions used by macros - not part of public API
126#[doc(hidden)]
127pub use builder::{delete_sqlite, insert_sqlite, update_sqlite};
128pub use dialect::{Dialect, Postgres, Sqlite};
129pub use pagination::{Cursor, CursorError, IntoCursor, KeysetCondition, PageInfo};
130pub use validate::{
131    FilterValidator, ValidationError, assert_valid_sql_expression, assert_valid_sql_identifier,
132    is_valid_sql_expression, is_valid_sql_identifier, merge_filters,
133};
134
135// Re-export macros from mik-sql-macros
136pub use mik_sql_macros::{ids, sql_create, sql_delete, sql_read, sql_update};
137
138/// Build a query for Postgres.
139///
140/// Convenience function that creates a `QueryBuilder` with Postgres dialect.
141#[must_use]
142pub fn postgres(table: &str) -> QueryBuilder<Postgres> {
143    QueryBuilder::new(Postgres, table)
144}
145
146/// Build a query for `SQLite`.
147///
148/// Convenience function that creates a `QueryBuilder` with `SQLite` dialect.
149#[must_use]
150pub fn sqlite(table: &str) -> QueryBuilder<Sqlite> {
151    QueryBuilder::new(Sqlite, table)
152}
153
154/// Prelude module for convenient imports.
155///
156/// ```ignore
157/// use mik_sql::prelude::*;
158/// ```
159pub mod prelude {
160    pub use crate::{
161        Aggregate, AggregateFunc, CompoundFilter, ComputedField, Cursor, CursorDirection,
162        CursorError, DeleteBuilder, Dialect, Filter, FilterExpr, FilterValidator, InsertBuilder,
163        IntoCursor, KeysetCondition, LogicalOp, Operator, PageInfo, Postgres, QueryBuilder,
164        QueryResult, SortDir, SortField, Sqlite, UpdateBuilder, ValidationError, Value, and,
165        delete, insert, merge_filters, not, or, postgres, simple, sqlite, update,
166    };
167
168    // Re-export macros
169    pub use mik_sql_macros::{ids, sql_create, sql_delete, sql_read, sql_update};
170}
171
172#[cfg(test)]
173mod tests {
174    use super::*;
175
176    #[test]
177    fn test_simple_select() {
178        let result = postgres("users").fields(&["id", "name", "email"]).build();
179
180        assert_eq!(result.sql, "SELECT id, name, email FROM users");
181        assert!(result.params.is_empty());
182    }
183
184    #[test]
185    fn test_select_with_filter() {
186        let result = postgres("users")
187            .fields(&["id", "name"])
188            .filter("active", Operator::Eq, Value::Bool(true))
189            .build();
190
191        assert_eq!(result.sql, "SELECT id, name FROM users WHERE active = $1");
192        assert_eq!(result.params.len(), 1);
193    }
194
195    #[test]
196    fn test_sqlite_dialect() {
197        let result = sqlite("users")
198            .fields(&["id", "name"])
199            .filter("active", Operator::Eq, Value::Bool(true))
200            .build();
201
202        assert_eq!(result.sql, "SELECT id, name FROM users WHERE active = ?1");
203    }
204
205    #[test]
206    fn test_cursor_pagination() {
207        let cursor = Cursor::new().int("id", 100);
208
209        let result = postgres("users")
210            .fields(&["id", "name"])
211            .sort("id", SortDir::Asc)
212            .after_cursor(cursor.clone())
213            .limit(20)
214            .build();
215
216        assert_eq!(
217            result.sql,
218            "SELECT id, name FROM users WHERE id > $1 ORDER BY id ASC LIMIT 20"
219        );
220    }
221
222    // ═══════════════════════════════════════════════════════════════════════════
223    // EDGE CASE TESTS
224    // ═══════════════════════════════════════════════════════════════════════════
225
226    #[test]
227    fn test_multi_field_cursor_mixed_sort_directions() {
228        // Edge case: Multi-field cursor with mixed ASC/DESC sort directions
229        // DESC fields use < operator, ASC fields use > operator
230        let cursor = Cursor::new()
231            .string("created_at", "2024-01-15T10:00:00Z")
232            .int("id", 42);
233
234        let result = postgres("posts")
235            .fields(&["id", "title", "created_at"])
236            .sort("created_at", SortDir::Desc) // Newest first
237            .sort("id", SortDir::Asc) // Then by ID ascending
238            .after_cursor(cursor.clone())
239            .limit(20)
240            .build();
241
242        // Multi-field keyset uses tuple comparison: (created_at, id) < ($1, $2)
243        // For DESC primary, ASC secondary: items after cursor have (smaller created_at) OR (same created_at AND larger id)
244        assert!(result.sql.contains("ORDER BY created_at DESC, id ASC"));
245        assert_eq!(result.params.len(), 2);
246    }
247
248    #[test]
249    fn test_multi_field_cursor_all_desc() {
250        // Edge case: All fields descending
251        let cursor = Cursor::new()
252            .string("created_at", "2024-01-15T10:00:00Z")
253            .int("id", 42);
254
255        let result = postgres("posts")
256            .fields(&["id", "title"])
257            .sort("created_at", SortDir::Desc)
258            .sort("id", SortDir::Desc)
259            .after_cursor(cursor.clone())
260            .limit(10)
261            .build();
262
263        assert!(result.sql.contains("ORDER BY created_at DESC, id DESC"));
264    }
265
266    #[test]
267    fn test_sqlite_between_operator() {
268        // Edge case: SQLite BETWEEN handling with expanded parameters
269        let result = sqlite("products")
270            .fields(&["id", "name", "price"])
271            .filter(
272                "price",
273                Operator::Between,
274                Value::Array(vec![Value::Float(10.0), Value::Float(100.0)]),
275            )
276            .build();
277
278        // SQLite BETWEEN uses ?1 AND ?2 placeholders
279        assert!(result.sql.contains("BETWEEN"));
280        assert!(result.sql.contains("?1"));
281        assert!(result.sql.contains("?2"));
282        assert_eq!(result.params.len(), 2);
283    }
284
285    #[test]
286    fn test_postgres_between_operator() {
287        // Postgres BETWEEN for comparison
288        let result = postgres("products")
289            .fields(&["id", "name", "price"])
290            .filter(
291                "price",
292                Operator::Between,
293                Value::Array(vec![Value::Int(10), Value::Int(100)]),
294            )
295            .build();
296
297        assert!(result.sql.contains("BETWEEN"));
298        assert!(result.sql.contains("$1"));
299        assert!(result.sql.contains("$2"));
300        assert_eq!(result.params.len(), 2);
301    }
302
303    #[test]
304    fn test_compound_filter_nested() {
305        use builder::{CompoundFilter, FilterExpr, simple};
306
307        // Edge case: Nested compound filters (AND containing OR)
308        let nested_or = CompoundFilter::or(vec![
309            simple("status", Operator::Eq, Value::String("active".into())),
310            simple("status", Operator::Eq, Value::String("pending".into())),
311        ]);
312
313        let result = postgres("orders")
314            .fields(&["id", "status", "amount"])
315            .filter_expr(FilterExpr::Compound(CompoundFilter::and(vec![
316                simple("amount", Operator::Gte, Value::Int(100)),
317                FilterExpr::Compound(nested_or),
318            ])))
319            .build();
320
321        // Should produce: amount >= $1 AND (status = $2 OR status = $3)
322        assert!(result.sql.contains("AND"));
323        assert!(result.sql.contains("OR"));
324        assert_eq!(result.params.len(), 3);
325    }
326
327    #[test]
328    fn test_compound_filter_not() {
329        use builder::{CompoundFilter, FilterExpr, simple};
330
331        // Edge case: NOT compound filter
332        let result = postgres("users")
333            .fields(&["id", "name", "role"])
334            .filter_expr(FilterExpr::Compound(CompoundFilter::not(simple(
335                "role",
336                Operator::Eq,
337                Value::String("admin".into()),
338            ))))
339            .build();
340
341        assert!(result.sql.contains("NOT"));
342        assert_eq!(result.params.len(), 1);
343    }
344
345    #[test]
346    fn test_empty_cursor_ignored() {
347        // Edge case: Empty cursor should not add any keyset conditions
348        let cursor = Cursor::new();
349
350        let result = postgres("users")
351            .fields(&["id", "name"])
352            .sort("id", SortDir::Asc)
353            .after_cursor(cursor.clone())
354            .limit(20)
355            .build();
356
357        // Should not have WHERE clause from empty cursor
358        assert_eq!(
359            result.sql,
360            "SELECT id, name FROM users ORDER BY id ASC LIMIT 20"
361        );
362    }
363
364    #[test]
365    fn test_cursor_extra_fields_ignored() {
366        // Edge case: Cursor contains fields that are NOT in the sort specification.
367        // These extra fields should be silently ignored - only sort fields matter.
368        let cursor = Cursor::new()
369            .string("extra_field", "should_be_ignored")
370            .int("another_extra", 999)
371            .int("id", 42); // Only this matches sort
372
373        let result = postgres("users")
374            .fields(&["id", "name"])
375            .sort("id", SortDir::Asc) // Only sort by id
376            .after_cursor(cursor.clone())
377            .limit(20)
378            .build();
379
380        // Should only use 'id' from cursor, ignore extra_field and another_extra
381        assert!(result.sql.contains("id > $1"));
382        assert_eq!(result.params.len(), 1);
383    }
384
385    #[test]
386    fn test_sqlite_in_clause_expansion() {
387        // Edge case: SQLite IN clause expands to multiple placeholders
388        let result = sqlite("users")
389            .fields(&["id", "name"])
390            .filter(
391                "status",
392                Operator::In,
393                Value::Array(vec![
394                    Value::String("active".into()),
395                    Value::String("pending".into()),
396                    Value::String("review".into()),
397                ]),
398            )
399            .build();
400
401        // SQLite: status IN (?1, ?2, ?3)
402        assert!(result.sql.contains("IN (?1, ?2, ?3)"));
403        assert_eq!(result.params.len(), 3);
404    }
405
406    #[test]
407    fn test_postgres_in_clause_array() {
408        // Postgres uses ANY with array parameter
409        let result = postgres("users")
410            .fields(&["id", "name"])
411            .filter(
412                "status",
413                Operator::In,
414                Value::Array(vec![
415                    Value::String("active".into()),
416                    Value::String("pending".into()),
417                ]),
418            )
419            .build();
420
421        // Postgres: status = ANY($1)
422        assert!(result.sql.contains("= ANY($1)"));
423        assert_eq!(result.params.len(), 1); // Single array param
424    }
425
426    // =========================================================================
427    // BETWEEN OPERATOR EDGE CASE TESTS
428    // =========================================================================
429
430    #[test]
431    fn test_between_with_exactly_two_values() {
432        // BETWEEN with exactly 2 values should work
433        let result = postgres("products")
434            .fields(&["id", "price"])
435            .filter(
436                "price",
437                Operator::Between,
438                Value::Array(vec![Value::Int(10), Value::Int(100)]),
439            )
440            .build();
441
442        assert!(result.sql.contains("BETWEEN $1 AND $2"));
443        assert_eq!(result.params.len(), 2);
444    }
445
446    #[test]
447    fn test_between_with_one_value_fallback() {
448        // BETWEEN with 1 value returns impossible condition (consistent behavior)
449        let result = postgres("products")
450            .fields(&["id", "price"])
451            .filter(
452                "price",
453                Operator::Between,
454                Value::Array(vec![Value::Int(10)]),
455            )
456            .build();
457        assert!(
458            result.sql.contains("1=0"),
459            "Should return impossible condition"
460        );
461    }
462
463    #[test]
464    fn test_between_with_three_values_fallback() {
465        // BETWEEN with 3 values returns impossible condition (consistent behavior)
466        let result = postgres("products")
467            .fields(&["id", "price"])
468            .filter(
469                "price",
470                Operator::Between,
471                Value::Array(vec![Value::Int(10), Value::Int(50), Value::Int(100)]),
472            )
473            .build();
474        assert!(
475            result.sql.contains("1=0"),
476            "Should return impossible condition"
477        );
478    }
479
480    #[test]
481    fn test_between_with_empty_array_fallback() {
482        // BETWEEN with empty array returns impossible condition (consistent behavior)
483        let result = postgres("products")
484            .fields(&["id", "price"])
485            .filter("price", Operator::Between, Value::Array(vec![]))
486            .build();
487        assert!(
488            result.sql.contains("1=0"),
489            "Should return impossible condition"
490        );
491    }
492
493    #[test]
494    fn test_between_with_different_value_types() {
495        // BETWEEN with different value types (strings for date range)
496        let result = postgres("orders")
497            .fields(&["id", "created_at"])
498            .filter(
499                "created_at",
500                Operator::Between,
501                Value::Array(vec![
502                    Value::String("2024-01-01".into()),
503                    Value::String("2024-12-31".into()),
504                ]),
505            )
506            .build();
507
508        assert!(result.sql.contains("BETWEEN $1 AND $2"));
509        assert_eq!(result.params.len(), 2);
510    }
511
512    #[test]
513    fn test_between_sqlite_dialect() {
514        // BETWEEN with SQLite dialect
515        let result = sqlite("products")
516            .fields(&["id", "price"])
517            .filter(
518                "price",
519                Operator::Between,
520                Value::Array(vec![Value::Float(9.99), Value::Float(99.99)]),
521            )
522            .build();
523
524        assert!(result.sql.contains("BETWEEN ?1 AND ?2"));
525        assert_eq!(result.params.len(), 2);
526    }
527}
mik_sql/lib.rs

mik_sql/
lib.rs
