Skip to main content

​
Overview

The SQL generation system converts filter expressions and ordering specifications into database-specific SQL queries. It supports both PostgreSQL and MySQL dialects with proper parameter binding to prevent SQL injection.

​
SqlFilterBuilder

​
Basic SQL Generation

use bomboni_request::sql::{SqlFilterBuilder, SqlDialect};
use bomboni_request::filter::Filter;
use bomboni_request::testing::schema::RequestItem;

let schema = RequestItem::get_schema();
let filter = Filter::parse(r#"NOT task.deleted AND user.age >= 30"#).unwrap();

// Generate PostgreSQL SQL
let (sql, args) = SqlFilterBuilder::new(SqlDialect::Postgres, &schema)
    .build(&filter)
    .unwrap();

assert_eq!(sql, r#"NOT ("task"."deleted") AND "user"."age" >= $1"#);
assert_eq!(args[0], bomboni_request::value::Value::Integer(30));

​
Supported Dialects

use bomboni_request::sql::SqlDialect;

let dialect = SqlDialect::Postgres;
// Uses $1, $2, $3... for parameters
// Uses double quotes for identifiers: "table"."column"

​
Field Mapping with SqlRenameMap

Map schema field names to database column names: 
use bomboni_request::sql::{SqlFilterBuilder, SqlDialect, SqlRenameMap};
use bomboni_request::filter::Filter;
use bomboni_request::testing::schema::RequestItem;
use bomboni_macros::btree_map_into;

let schema = RequestItem::get_schema();
let filter = Filter::parse(r#"NOT task.deleted AND user.age >= 30"#).unwrap();

let rename_map = SqlRenameMap {
    members: btree_map_into! {
        "user" => "u",                    // Alias user table to 'u'
        "task.userId" => "user_id",       // Map camelCase to snake_case
    },
    functions: std::collections::BTreeMap::new(),
};

let (sql, args) = SqlFilterBuilder::new(SqlDialect::Postgres, &schema)
    .set_rename_map(&rename_map)
    .build(&filter)
    .unwrap();

assert_eq!(sql, r#"NOT ("task"."deleted") AND "u"."age" >= $1"#);
assert_eq!(args[0], bomboni_request::value::Value::Integer(30));

​
Rename Map Structure

​
members
BTreeMap<String, String>
Maps schema field names to database column/table names. Supports both simple fields and nested paths.
​
functions
BTreeMap<String, String>
Maps function names to SQL function names (for custom filter functions).

​
Parameter Binding

All values are properly parameterized to prevent SQL injection: 
use bomboni_request::sql::{SqlFilterBuilder, SqlDialect};
use bomboni_request::filter::Filter;
use bomboni_request::testing::schema::UserItem;

let schema = UserItem::get_schema();
let filter = Filter::parse(r#"displayName = "Alice" AND age >= 18"#).unwrap();

let (sql, args) = SqlFilterBuilder::new(SqlDialect::Postgres, &schema)
    .build(&filter)
    .unwrap();

assert_eq!(sql, r#""displayName" = $1 AND "age" >= $2"#);
assert_eq!(args.len(), 2);
assert_eq!(args[0], bomboni_request::value::Value::String("Alice".into()));
assert_eq!(args[1], bomboni_request::value::Value::Integer(18));

​
Using Bound Parameters

use sqlx::PgPool;

let (sql, args) = builder.build(&filter).unwrap();

let query = sqlx::query(&format!("SELECT * FROM users WHERE {}", sql));
for arg in args {
    query = match arg {
        Value::Integer(i) => query.bind(i),
        Value::String(s) => query.bind(s),
        Value::Boolean(b) => query.bind(b),
        // ... handle other types
        _ => query,
    };
}
let users = query.fetch_all(&pool).await?;

​
SQL Ordering Builder

Convert ordering specifications to SQL ORDER BY clauses: 
use bomboni_request::sql::{SqlDialect, ordering::SqlOrderingBuilder};
use bomboni_request::ordering::Ordering;
use bomboni_request::testing::schema::UserItem;

let schema = UserItem::get_schema();
let ordering = Ordering::parse("displayName desc, age asc").unwrap();

let sql = SqlOrderingBuilder::new(SqlDialect::Postgres, &schema)
    .build(&ordering)
    .unwrap();

assert_eq!(sql, r#""displayName" DESC, "age" ASC"#);

​
With Rename Map

use bomboni_request::sql::SqlRenameMap;
use bomboni_macros::btree_map_into;

let rename_map = SqlRenameMap {
    members: btree_map_into! {
        "displayName" => "display_name",
    },
    functions: std::collections::BTreeMap::new(),
};

let sql = SqlOrderingBuilder::new(SqlDialect::Postgres, &schema)
    .set_rename_map(&rename_map)
    .build(&ordering)
    .unwrap();

assert_eq!(sql, r#""display_name" DESC, "age" ASC"#);

​
Complete SQL Query Example

use bomboni_request::{
    filter::Filter,
    ordering::Ordering,
    sql::{SqlFilterBuilder, SqlDialect, SqlRenameMap, ordering::SqlOrderingBuilder},
};
use bomboni_macros::btree_map_into;

// Define schema and rename map
let schema = UserItem::get_schema();
let rename_map = SqlRenameMap {
    members: btree_map_into! {
        "displayName" => "display_name",
        "userId" => "user_id",
    },
    functions: std::collections::BTreeMap::new(),
};

// Parse filter and ordering
let filter = Filter::parse(r#"displayName = "Alice" AND age >= 18"#).unwrap();
let ordering = Ordering::parse("age desc, displayName asc").unwrap();

// Generate SQL
let (where_clause, args) = SqlFilterBuilder::new(SqlDialect::Postgres, &schema)
    .set_rename_map(&rename_map)
    .build(&filter)
    .unwrap();

let order_clause = SqlOrderingBuilder::new(SqlDialect::Postgres, &schema)
    .set_rename_map(&rename_map)
    .build(&ordering)
    .unwrap();

let sql = format!(
    "SELECT * FROM users WHERE {} ORDER BY {} LIMIT $3",
    where_clause,
    order_clause
);

// sql = SELECT * FROM users WHERE "display_name" = $1 AND "age" >= $2 ORDER BY "age" DESC, "display_name" ASC LIMIT $3
// args = ["Alice", 18]

​
Advanced Features

​
Parameter Offset

When combining multiple SQL builders, adjust parameter numbering: 
let mut builder = SqlFilterBuilder::new(SqlDialect::Postgres, &schema);
builder.set_document_offset(5); // Start parameters at $6

let (sql, args) = builder.build(&filter).unwrap();
// Parameters will be $6, $7, $8... instead of $1, $2, $3...

​
Case-Insensitive Matching

Enable case-insensitive string comparisons: 
let mut builder = SqlFilterBuilder::new(SqlDialect::Postgres, &schema);
builder.case_insensitive_like();

let filter = Filter::parse(r#"name:"alice""#).unwrap();
let (sql, args) = builder.build(&filter).unwrap();
// Generates: LOWER("name") LIKE LOWER($1)

​
Custom Argument Style

use bomboni_request::sql::SqlArgumentStyle;

let mut builder = SqlFilterBuilder::new(SqlDialect::Postgres, &schema);
builder.set_argument_style(SqlArgumentStyle::Indexed { prefix: "?".into() });
// Use ? instead of $1, $2, $3...

​
Integration with List Queries

Combine SQL generation with pagination: 
use bomboni_request::query::list::ListQueryBuilder;

// Build list query (handles parsing and validation)
let query = list_builder.build(
    Some(20),
    page_token.as_deref(),
    filter.as_deref(),
    order_by.as_deref(),
).unwrap();

// Generate SQL
let (where_clause, args) = SqlFilterBuilder::new(SqlDialect::Postgres, &schema)
    .set_rename_map(&rename_map)
    .build(&query.filter)
    .unwrap();

let order_clause = SqlOrderingBuilder::new(SqlDialect::Postgres, &schema)
    .set_rename_map(&rename_map)
    .build(&query.ordering)
    .unwrap();

// Build complete query
let sql = format!(
    "SELECT * FROM users WHERE {} ORDER BY {} LIMIT {}",
    where_clause,
    order_clause,
    query.page_size + 1  // Fetch N+1 to detect next page
);

​
Type Conversions

The SQL builders handle type conversions automatically:
Value TypePostgreSQLMySQL
IntegerBIGINTBIGINT
FloatDOUBLE PRECISIONDOUBLE
StringTEXTVARCHAR
BooleanBOOLEANTINYINT(1)
TimestampTIMESTAMPTIMESTAMP

​
Error Handling

use bomboni_request::filter::error::FilterError;

match SqlFilterBuilder::new(SqlDialect::Postgres, &schema).build(&filter) {
    Ok((sql, args)) => {
        // Use SQL and args
    }
    Err(FilterError::UnknownMember(field)) => {
        eprintln!("Unknown field in filter: {}", field);
    }
    Err(FilterError::InvalidType { actual, expected }) => {
        eprintln!("Type mismatch: expected {:?}, got {:?}", expected, actual);
    }
    Err(e) => {
        eprintln!("SQL generation error: {:?}", e);
    }
}

​
Best Practices

1

Always Use Parameter Binding

Never interpolate values directly into SQL strings. The builders handle this automatically.
2

Define Rename Maps

Create rename maps to bridge schema field names (often camelCase) with database column names (often snake_case)
3

Validate Before SQL Generation

Use schema validation before SQL generation to catch errors early
4

Use Database-Specific Features

Take advantage of dialect-specific optimizations when available
The : (has/contains) operator is not fully supported for SQL generation as different databases handle array/JSON containment differently. Consider implementing custom handling for this operator.

​
Common Patterns

​
Table Joins

let rename_map = SqlRenameMap {
    members: btree_map_into! {
        "user" => "u",
        "task" => "t",
        "task.userId" => "t.user_id",
        "user.displayName" => "u.display_name",
    },
    functions: std::collections::BTreeMap::new(),
};

let filter = Filter::parse(r#"user.displayName = "Alice" AND task.deleted = false"#).unwrap();

let (sql, args) = SqlFilterBuilder::new(SqlDialect::Postgres, &schema)
    .set_rename_map(&rename_map)
    .build(&filter)
    .unwrap();

let query = format!(
    "SELECT t.* FROM tasks t JOIN users u ON t.user_id = u.id WHERE {}",
    sql
);

​
Pagination with Cursors

// Use page token to continue from last item
if let Some(page_token) = &query.page_token {
    // page_token.filter contains continuation condition
    let (cursor_sql, cursor_args) = SqlFilterBuilder::new(SqlDialect::Postgres, &schema)
        .set_rename_map(&rename_map)
        .set_document_offset(args.len()) // Continue parameter numbering
        .build(&page_token.filter)
        .unwrap();
    
    let sql = format!(
        "SELECT * FROM users WHERE ({}) AND ({}) ORDER BY {} LIMIT {}",
        where_clause,
        cursor_sql,
        order_clause,
        query.page_size + 1
    );
}

Filtering

Learn about filter expressions

Ordering

Understand ordering specifications

Pagination

Implement paginated queries

Build docs developers (and LLMs) love

Get started for freeTalk to us