query-lite

A convenient SQL query builder for rusqlite that makes it easy to build type-safe SQLite queries with parameter binding. Parse HTTP query parameters, build queries programmatically, or combine both approaches to create secure, parameterized SQL queries.

Why query-lite?

Building SQL queries manually is error-prone and tedious. query-lite provides a type-safe, builder-pattern API that:

• ✅ Prevents SQL Injection: All queries use parameterized placeholders
• ✅ Type-Safe: Compile-time guarantees for query structure
• ✅ Easy to Use: Builder pattern with fluent API
• ✅ rusqlite Ready: Direct integration with rusqlite's ToSql trait
• ✅ Flexible: Build queries programmatically or parse from HTTP

Example

use query_lite::{Query, Parameters, Order};
use rusqlite::Connection;

// Build a query
let mut params = Parameters::new();
params
    .contains("name".to_string(), vec!["john".to_string()])
    .between("age".to_string(), vec!["20".to_string(), "30".to_string()])
    .greater("price".to_string(), vec!["100".to_string()]);

let mut order = Order::new();
order.descending("date_created".to_string());

let query = Query::init(params, order, 25, 0);

// Generate SQL
let sql = query.to_sql();
// "WHERE name LIKE ? AND age BETWEEN ? AND ? AND price > ? ORDER BY date_created DESC LIMIT ? OFFSET ?"

// Get values for rusqlite
let values = query.to_values();

// Use with rusqlite
let conn = Connection::open("database.db")?;
let mut stmt = conn.prepare(&format!("SELECT * FROM users {}", sql))?;
let params: Vec<&dyn rusqlite::types::ToSql> = values
    .iter()
    .map(|v| v as &dyn rusqlite::types::ToSql)
    .collect();
let rows = stmt.query(params.as_slice())?;

Features

• 🗄️ SQL Query Builder: Build type-safe SQLite queries with automatic parameter binding for rusqlite
• 🔒 SQL Injection Safe: All queries use parameterized placeholders - never string concatenation
• 🎯 Rich Filtering: Support for equals, contains, starts-with, ends-with, between, greater, lesser, and more
• 📊 Sorting & Pagination: Built-in support for ORDER BY, LIMIT, and OFFSET clauses
• 🔍 HTTP Query Parsing: Optional support for parsing HTTP query parameters into SQL queries
• 🛡️ Type Safety: Full Rust type safety with comprehensive error handling
• ⚡ Zero-Copy Values: Direct integration with rusqlite's ToSql trait for efficient parameter binding
• 🧪 Well Tested: Comprehensive test suite with 240+ tests

Quick Start

Add this to your Cargo.toml:

[dependencies]
# SQL query builder (default - includes SQL generation)
query-lite = "0.11.0"

# With HTTP query parameter parsing (optional)
query-lite = { version = "0.11.0", features = ["http"] }

Basic Usage

Building SQL Queries Programmatically

The primary use case is building SQL queries for rusqlite:

use query_lite::{Query, Parameters, Order};
use rusqlite::Connection;

#[cfg(feature = "sql")]
fn example() -> Result<(), Box<dyn std::error::Error>> {
    // Build query programmatically
    let mut params = Parameters::new();
    params
        .contains("name".to_string(), vec!["john".to_string()])
        .between("age".to_string(), vec!["20".to_string(), "30".to_string()])
        .greater("price".to_string(), vec!["100".to_string()]);

    let mut order = Order::new();
    order.descending("date_created".to_string());

    let query = Query::init(params, order, 25, 0);

    // Generate SQL with parameter placeholders
    let sql = query.to_sql();
    // Result: "WHERE name LIKE ? AND age BETWEEN ? AND ? AND price > ? ORDER BY date_created DESC LIMIT ? OFFSET ?"

    // Get parameter values for rusqlite
    let values = query.to_values();
    // Result: [sql::Value::Text("%john%"), sql::Value::Integer(20), sql::Value::Integer(30), 
    //          sql::Value::Integer(100), sql::Value::Integer(25), sql::Value::Integer(0)]

    // Use with rusqlite
    let conn = Connection::open("database.db")?;
    let mut stmt = conn.prepare(&format!("SELECT * FROM users {}", sql))?;
    
    // sql::Value implements ToSql, so we can bind directly
    let params: Vec<&dyn rusqlite::types::ToSql> = values
        .iter()
        .map(|v| v as &dyn rusqlite::types::ToSql)
        .collect();
    
    let rows = stmt.query(params.as_slice())?;
    // Process rows...
    
    Ok(())
}

Parsing HTTP Query Parameters (Optional)

If you enable the http feature, you can also parse HTTP query strings:

use query_lite::Query;
use rusqlite::Connection;

#[cfg(all(feature = "sql", feature = "http"))]
fn example() -> Result<(), Box<dyn std::error::Error>> {
    // Parse HTTP query parameters
    let query = Query::from_http("name=contains:john&age=between:20,30&order=date_created:desc&limit=25".to_string())?;

    // Generate SQL
    let sql = query.to_sql();
    let values = query.to_values();

    // Use with rusqlite
    let conn = Connection::open("database.db")?;
    let mut stmt = conn.prepare(&format!("SELECT * FROM users {}", sql))?;
    
    let params: Vec<&dyn rusqlite::types::ToSql> = values
        .iter()
        .map(|v| v as &dyn rusqlite::types::ToSql)
        .collect();
    
    let rows = stmt.query(params.as_slice())?;
    // Process rows...
    
    Ok(())
}

Traditional Query Parameters

use query_lite::Query;

// Parse traditional HTTP query parameters
let query = Query::from_http("name=john&age=25&city=london".to_string())?;

// Access parameters
let name_param = query.parameters.inner().get("name").unwrap();
assert_eq!(*name_param.similarity(), query_lite::Similarity::Equals);
assert_eq!(name_param.values(), &vec!["john"]);

// Convert back to HTTP
let http_string = query.to_http();
// Result: "name=equals:john&age=equals:25&city=equals:london&limit=50&offset=0"

Advanced Similarity-based Parameters

use query_lite::Query;

// Parse advanced query parameters
let query = Query::from_http("name=contains:john&age=between:20,30&price=greater:100".to_string())?;

// Access parameters with different similarity types
let name_param = query.parameters.inner().get("name").unwrap();
assert_eq!(*name_param.similarity(), query_lite::Similarity::Contains);
assert_eq!(name_param.values(), &vec!["john"]);

let age_param = query.parameters.inner().get("age").unwrap();
assert_eq!(*age_param.similarity(), query_lite::Similarity::Between);
assert_eq!(age_param.values(), &vec!["20", "30"]);

Mixed Traditional and Advanced

use query_lite::Query;

// Mix traditional and advanced parameters
let query = Query::from_http("name=john&name=jane&age=contains:25&status=active".to_string())?;

// Traditional parameters (repeated values)
let name_param = query.parameters.inner().get("name").unwrap();
assert_eq!(*name_param.similarity(), query_lite::Similarity::Equals);
assert_eq!(name_param.values(), &vec!["john", "jane"]);

// Advanced parameters
let age_param = query.parameters.inner().get("age").unwrap();
assert_eq!(*age_param.similarity(), query_lite::Similarity::Contains);
assert_eq!(age_param.values(), &vec!["25"]);

Programmatic Query Building

You can also build queries programmatically using the builder pattern:

use query_lite::{Query, Parameters, Order};

// Build parameters using the builder pattern
let mut parameters = Parameters::new();
parameters
    .equals("name".to_string(), vec!["john".to_string(), "jane".to_string()])
    .contains("description".to_string(), vec!["rust".to_string()])
    .between("age".to_string(), vec!["18".to_string(), "65".to_string()])
    .greater("price".to_string(), vec!["100".to_string()]);

// Build sort fields using the builder pattern
let mut order = Order::new();
order
    .descending("date_created".to_string())
    .ascending("name".to_string());

// Create the query
let query = Query::init(parameters, order, 25, 0);

// Convert to HTTP string
let http_string = query.to_http();
// Result: "name=equals:john,jane&description=contains:rust&age=between:18,65&price=greater:100&order=date_created:desc,name:asc&limit=25&offset=0"

Enhanced Parameter Access

The library provides multiple ways to access parameter data for different use cases:

Semantic Access (Recommended)

use query_lite::Query;

let query = Query::from_http("name=contains:john&age=between:20,30".to_string())?;

// Access parameters using semantic methods
let name_param = query.parameters.inner().get("name").unwrap();
assert_eq!(*name_param.similarity(), Similarity::Contains);
assert_eq!(name_param.values(), &vec!["john".to_string()]);

let age_param = query.parameters.inner().get("age").unwrap();
assert_eq!(*age_param.similarity(), Similarity::Between);
assert_eq!(age_param.values(), &vec!["20".to_string(), "30".to_string()]);

Direct Collection Access

For advanced operations, you can access the underlying collections directly:

use query_lite::Query;

let mut query = Query::new();
query.parameters.equals("name".to_string(), vec!["john".to_string()]);
query.order.ascending("date_created".to_string());

// Access the underlying IndexMap for complex operations
let param_map = query.parameters.inner();
let order_map = query.order.inner();

// Iterate over all parameters
for (key, param) in param_map {
    println!("{}: {:?} = {:?}", key, param.similarity(), param.values());
}

// Perform bulk operations
let param_map_mut = query.parameters.inner_mut();
param_map_mut.insert("new_param".to_string(), Parameter::init(Similarity::Greater, vec!["100".to_string()]));

Parameter Access

The library provides semantic access methods for parameter data:

use query_lite::Query;

let query = Query::from_http("name=contains:john".to_string())?;
let param = query.parameters.inner().get("name").unwrap();

// Use semantic access methods
assert_eq!(*param.similarity(), Similarity::Contains);
assert_eq!(param.values(), &vec!["john".to_string()]);

// Create parameters using the init method
let new_param = Parameter::init(Similarity::Greater, vec!["100".to_string()]);

Similarity Types

The library supports various similarity types for advanced filtering:

Multiple Values

// Multiple values for equals (IN clause)
"?name=equals:john,jane,bob"
// → name IN ('john', 'jane', 'bob')

// Multiple ranges for between
"?age=between:18,25,30,40,50,65"
// → (age BETWEEN 18 AND 25) OR (age BETWEEN 30 AND 40) OR (age BETWEEN 50 AND 65)
// Note: Odd values (65) are ignored

Sorting and Pagination

use query_lite::Query;

let query = Query::from_http("name=john&order=date_created:desc,name:asc&limit=25&offset=10".to_string())?;

// Access sorting
assert_eq!(query.order.inner().len(), 2);
assert_eq!(query.order.inner().get("date_created"), Some(&query_lite::SortDirection::Descending));
assert_eq!(query.order.inner().get("name"), Some(&query_lite::SortDirection::Ascending));

// Access pagination
assert_eq!(query.limit, 25);
assert_eq!(query.offset, 10);

SQL Query Building

The core feature of query-lite is building SQL queries for rusqlite. All queries use parameterized placeholders to prevent SQL injection:

use query_lite::Query;

let query = Query::from_http("name=contains:john&age=between:20,30&order=date_created:desc&limit=10".to_string())?;

// Generate SQLite-compatible SQL with parameter placeholders
let sql = query.to_sql();
// Result: "WHERE name LIKE ? AND age BETWEEN ? AND ? ORDER BY date_created DESC LIMIT ? OFFSET ?"

// Get parameter values separately for more control
let param_values = query.parameter_values();
let pagination_values = query.pagination_values();
let total_params = query.total_parameters();

// Use with SQLite
// let stmt = conn.prepare(&format!("SELECT * FROM users {}", sql))?;
// let rows = stmt.query(param_values)?;

Advanced SQLite Clause Management

Version 0.8.0 introduces improved SQLite clause methods that return Option<String> for better semantic clarity:

use query_lite::Query;

let query = Query::from_http("name=contains:john&age=between:20,30&order=date_created:desc".to_string())?;

// Get WHERE clause (returns None if no conditions)
match query.where_clause() {
    Some(where_clause) => println!("WHERE {}", where_clause),
    None => println!("No WHERE conditions"),
}

// Get ORDER BY clause (returns None if no sorting)
match query.order_clause() {
    Some(order_clause) => println!("ORDER BY {}", order_clause),
    None => println!("No ORDER BY clause"),
}

// Build custom SQL with explicit handling
let sql = match (query.where_clause(), query.order_clause()) {
    (Some(where_clause), Some(order_clause)) => 
        format!("SELECT * FROM users WHERE {} ORDER BY {}", where_clause, order_clause),
    (Some(where_clause), None) => 
        format!("SELECT * FROM users WHERE {}", where_clause),
    (None, Some(order_clause)) => 
        format!("SELECT * FROM users ORDER BY {}", order_clause),
    (None, None) => 
        "SELECT * FROM users".to_string(),
};

Advanced SQLite Value Management

Version 0.6.0 introduces simplified SQLite value methods:

use query_lite::Query;

let query = Query::from_http("name=contains:john&age=between:20,30&price=greater:100&order=date_created:desc".to_string())?;

// Get only parameter values (without pagination)
let param_values = query.parameter_values();
// Result: [sql::Value::Text("%john%"), sql::Value::Text("20"), sql::Value::Text("30"), sql::Value::Text("100")]

// Get only order values (without pagination)
let param_values = query.parameter_values();
// Result: [sql::Value::Text("%john%"), sql::Value::Text("20"), sql::Value::Text("30"), sql::Value::Text("100")]

// Get only pagination values
let pagination_values = query.pagination_values();
// Result: [sql::Value::Integer(50), sql::Value::Integer(0)]

// Get total parameter count
let total_params = query.total_parameters();
// Result: 6 (4 parameter values + 2 pagination values)

// Combine for complete SQL execution
let all_values = [param_values, pagination_values].concat();
// Use with SQLite
// let stmt = conn.prepare(&format!("SELECT * FROM users {}", query.to_sql()))?;
// let rows = stmt.query(all_values)?;

This granular approach allows for:

• Separate Parameter Handling: Process parameter values and pagination values independently
• Custom Value Processing: Apply different logic to parameters vs pagination
• Performance Optimization: Avoid unnecessary value processing when only certain parts are needed
• Debugging: Easily inspect parameter counts and values for troubleshooting

SQLite Examples

The generated SQL uses SQLite syntax with ? parameter placeholders:

// Traditional parameters
"?name=john&name=jane&age=25"
// → "WHERE name IN (?, ?) AND age = ? LIMIT ? OFFSET ?"

// Advanced parameters
"?name=contains:john&age=between:20,30&price=greater:100"
// → "WHERE name LIKE ? AND age BETWEEN ? AND ? AND price > ? LIMIT ? OFFSET ?"

// Complex mixed query
"?name=john&name=jane&age=contains:25&price=greater:100&order=date_created:desc&limit=20"
// → "WHERE name IN (?, ?) AND age LIKE ? AND price > ? ORDER BY date_created DESC LIMIT ? OFFSET ?"

rusqlite Integration

The sql::Value type (which is rusqlite::types::Value) implements rusqlite::types::ToSql, allowing direct parameter binding to rusqlite queries:

use query_lite::Query;
use rusqlite::Connection;

#[cfg(feature = "sql")]
fn example() -> Result<(), Box<dyn std::error::Error>> {
    let query = Query::from_http("name=contains:john&age=between:20,30".to_string())?;
    let sql = format!("SELECT * FROM users {}", query.to_sql());
    
    let conn = Connection::open_in_memory()?;
    
    // sql::Value implements ToSql, so you can bind directly
    let params: Vec<&dyn rusqlite::types::ToSql> = query.parameter_values()
        .iter()
        .map(|v| v as &dyn rusqlite::types::ToSql)
        .collect();
    
    let mut stmt = conn.prepare(&sql)?;
    let rows = stmt.query(params.as_slice())?;
    // Process rows...
    Ok(())
}

URL Encoding Support

The library automatically handles URL encoding and decoding:

use query_lite::Query;

// URL encoded parameters
let query = Query::from_http("name=john%20doe&email=test%40example.com".to_string())?;

let name_param = query.parameters.inner().get("name").unwrap();
assert_eq!(name_param.values(), &vec!["john doe"]); // Automatically decoded

let email_param = query.parameters.inner().get("email").unwrap();
assert_eq!(email_param.values(), &vec!["test@example.com"]); // Automatically decoded

Query Manipulation

use query_lite::Query;

let query = Query::from_http("name=john&age=25&email=john@example.com".to_string())?;

// Keep only specific parameters
let filtered_params = query.parameters.keep(vec!["name".to_string(), "age".to_string()]);
let filtered_query = Query::init(filtered_params, query.order, query.limit, query.offset);
// Result: Only name and age parameters remain

// Remove specific parameters
let filtered_params = query.parameters.remove(vec!["email".to_string()]);
let filtered_query = Query::init(filtered_params, query.order, query.limit, query.offset);
// Result: email parameter is removed, name and age remain

Error Handling

use query_lite::{Query, error::Error};

match Query::from_http("invalid=query".to_string()) {
    Ok(query) => {
        // Handle successful parsing
        println!("Query parsed successfully: {:?}", query);
    }
    Err(Error::InvalidParameter(msg)) => {
        // Handle invalid parameter format
        eprintln!("Invalid parameter: {}", msg);
    }
    Err(Error::InvalidOrderField(msg)) => {
        // Handle invalid order field
        eprintln!("Invalid order field: {}", msg);
    }
    Err(e) => {
        // Handle other errors
        eprintln!("Error: {}", e);
    }
}

Real-world Examples

E-commerce Product Search

use query_lite::Query;

// Complex product search with multiple filters
let query = Query::from_http(
    "category=electronics&brand=apple&brand=samsung&price=between:100,500&rating=greater-or-equal:4&order=price:asc&limit=20"
)?;

// Generate SQLite query for product search
let sql = query.to_sql();
// "WHERE category = ? AND brand IN (?, ?) AND price BETWEEN ? AND ? AND rating >= ? ORDER BY price ASC LIMIT ? OFFSET ?"

User Management System

use query_lite::Query;

// User filtering and management
let query = Query::from_http(
    "name=contains:john&age=greater:18&status=active&role=admin&role=user&order=created_at:desc&limit=50"
)?;

// Generate SQLite query for user query
let sql = query.to_sql();
// "WHERE name LIKE ? AND age > ? AND status = ? AND role IN (?, ?) ORDER BY created_at DESC LIMIT ? OFFSET ?"

Content Management

use query_lite::Query;

// Content filtering with date ranges
let query = Query::from_http(
    "title=contains:rust&tags=programming&tags=web&date=between:2023-01-01,2023-12-31&published=true&order=date:desc&limit=25"
)?;

// Generate SQLite query for content query
let sql = query.to_sql();
// "WHERE title LIKE ? AND tags IN (?, ?) AND date BETWEEN ? AND ? AND published = ? ORDER BY date DESC LIMIT ? OFFSET ?"

Feature Flags

The library supports feature flags for optional functionality:

[dependencies]
# Default: SQL query builder (includes SQL generation)
query-lite = "0.11.0"

# With HTTP query parameter parsing (optional)
query-lite = { version = "0.11.0", features = ["http"] }

Feature Details

• sql (default): Enables SQL query generation methods (to_sql(), where_clause(), order_clause(), etc.) and re-exports rusqlite::types::Value as sql::Value. The sql::Value type implements rusqlite::types::ToSql, allowing direct parameter binding to rusqlite queries.
• http (optional): Enables HTTP query string parsing and generation methods (from_http(), to_http()).

API Reference

Core Types

• Query: Main query structure containing parameters, sorting, and pagination
• Parameters: Collection of query parameters with builder methods
• Parameter: Struct containing similarity and values with semantic access methods (fields are private)
• Order: Collection of sort fields with builder methods
• Similarity: Enum defining comparison types (equals, contains, between, etc.)
• SortDirection: Sort direction (ascending, descending)

Key Methods

Query Methods

• Query::new(): Create a new Query with default values (empty parameters, empty order, limit=50, offset=0)
• Query::init(): Create Query with custom parameters, order, limit, and offset
• Query::to_sql(): Generate SQLite-compatible query with parameter placeholders (default feature)
• Query::from_http(): Parse HTTP query string into Query struct (requires http feature)
• Query::to_http(): Convert Query struct back to HTTP query string (requires http feature)
• Query::where_clause(): Get WHERE clause as Option (feature-gated)
• Query::order_clause(): Get ORDER BY clause as Option (feature-gated)
• Query::to_values(): Get all SQLite values (parameters + pagination) (feature-gated)
• Query::parameter_values(): Get SQLite values for parameters only (feature-gated)
• Query::pagination_values(): Get SQLite values for pagination only (feature-gated)
• Query::total_parameters(): Get total number of SQLite parameter values (feature-gated)

Parameters Methods

• Parameters::new(): Create new Parameters collection
• Parameters::equals(), Parameters::contains(), etc.: Builder methods for adding parameters
• Parameters::inner(): Get immutable reference to underlying IndexMap
• Parameters::inner_mut(): Get mutable reference to underlying IndexMap
• Parameters::keep(): Filter parameters to keep only specified keys
• Parameters::remove(): Remove specified parameters

Parameter Methods

• Parameter::init(): Create a new Parameter with similarity and values
• Parameter::similarity(): Get reference to similarity type
• Parameter::values(): Get reference to parameter values
• Parameter::values_mut(): Get mutable reference to parameter values

Order Methods

• Order::new(): Create new Order collection
• Order::ascending(), Order::descending(): Builder methods for adding sort fields
• Order::inner(): Get immutable reference to underlying IndexMap
• Order::inner_mut(): Get mutable reference to underlying IndexMap
• Order::keep(): Filter sort fields to keep only specified keys
• Order::remove(): Remove specified sort fields

Contributing

Contributions are welcome! Please feel free to submit a Pull Request. For major changes, please open an issue first to discuss what you would like to change.

License

This project is licensed under either of

• Apache License, Version 2.0, (LICENSE-APACHE or http://www.apache.org/licenses/LICENSE-2.0)
• MIT license (LICENSE-MIT or http://opensource.org/licenses/MIT)

at your option.

Changelog

See CHANGELOG.md for a detailed list of changes.

Made with ❤️ in Rust