Alex R.

Posted on Dec 16

From Query Chaos to API Bliss: Navigating pagination and data filtering with sqlx-paginated for PostgreSQL in Rust

#rust #api #learning #opensource

As web applications grow, developers often find themselves walking a tightrope between functionality and complexity.
What starts as a simple API endpoint can quickly transform into an unwieldy monster of nested conditions, parameter handling, and performance optimizations.

In this article, we'll explore how sqlx-paginated elegantly solves these challenges in Rust web applications, evolving from a basic implementation to a sophisticated, production-ready solution.

The library sqlx-paginated proposes the following tagline:

A flexible, type-safe SQLx query builder for dynamic web APIs, offering seamless pagination, searching, filtering, and sorting.

The Evolution of a Problem

Let's start with a scenario many developers encounter: building an e-commerce admin dashboard that displays user order history. We'll follow the journey from a basic implementation to a full-featured API, understanding the challenges and solutions at each step.

Stage 1: The Naive Approach

Initially, you might start with something simple:

async fn get_orders(pool: &PgPool) -> Result<Vec<Order>, sqlx::Error> {
    sqlx::query_as!(
        Order,
        "SELECT * FROM orders"
    )
    .fetch_all(pool)
    .await
}

This works fine during development with a handful of orders. But as your application grows to thousands of orders, problems emerge:

Memory usage spikes as all records are loaded
Response times increase
Network bandwidth is wasted
Frontend becomes sluggish rendering large datasets

Stage 2: Basic Pagination

To address these issues, you might implement basic pagination:

async fn get_orders(
    pool: &PgPool,
    page: i64,
    page_size: i64
) -> Result<(Vec<Order>, i64), sqlx::Error> {
    let offset = (page - 1) * page_size;

    let orders = sqlx::query_as!(
        Order,
        "SELECT * FROM orders 
         LIMIT $1 OFFSET $2",
        page_size, offset
    )
    .fetch_all(pool)
    .await?;

    let total = sqlx::query_scalar!(
        "SELECT COUNT(*) FROM orders"
    )
    .fetch_one(pool)
    .await?;

    Ok((orders, total.unwrap_or(0)))
}

Better, but new requirements inevitably arrive. The frontend team needs:

Sorting by different columns
Filtering by order status
Searching by customer name
Date range filtering

Stage 3: The Complex Query Nightmare

Attempting to handle all these requirements leads to increasingly complex code:

#[derive(Debug)]
struct OrderFilters {
    page: i64,
    page_size: i64,
    sort_column: Option<String>,
    sort_direction: Option<String>,
    status: Option<String>,
    search_term: Option<String>,
    date_from: Option<DateTime<Utc>>,
    date_to: Option<DateTime<Utc>>,
}

async fn get_filtered_orders(
    pool: &PgPool,
    filters: OrderFilters,
) -> Result<(Vec<Order>, i64), sqlx::Error> {
    let mut query = String::from("SELECT * FROM orders WHERE 1=1");
    let mut count_query = String::from("SELECT COUNT(*) FROM orders WHERE 1=1");
    let mut params: Vec<Box<dyn ToSql + Sync>> = vec![];
    let mut param_count = 1;

    if let Some(status) = filters.status {
        query.push_str(&format!(" AND status = ${}", param_count));
        count_query.push_str(&format!(" AND status = ${}", param_count));
        params.push(Box::new(status));
        param_count += 1;
    }

    if let Some(search) = filters.search_term {
        query.push_str(&format!(
            " AND (customer_name ILIKE ${0} OR email ILIKE ${0})",
            param_count
        ));
        count_query.push_str(&format!(
            " AND (customer_name ILIKE ${0} OR email ILIKE ${0})",
            param_count
        ));
        params.push(Box::new(format!("%{}%", search)));
        param_count += 1;
    }

    // Date range handling
    if let Some(date_from) = filters.date_from {
        query.push_str(&format!(" AND created_at >= ${}", param_count));
        count_query.push_str(&format!(" AND created_at >= ${}", param_count));
        params.push(Box::new(date_from));
        param_count += 1;
    }

    // ... more conditions ...

    // Sorting
    if let Some(sort_col) = filters.sort_column {
        // SECURITY RISK: Direct string interpolation!
        query.push_str(&format!(" ORDER BY {} {}", 
            sort_col,
            filters.sort_direction.unwrap_or("ASC".to_string())
        ));
    }

    // Pagination
    let offset = (filters.page - 1) * filters.page_size;
    query.push_str(&format!(" LIMIT {} OFFSET {}", filters.page_size, offset));

    // Execute queries...
}

This approach has several problems:

SQL injection vulnerabilities through string interpolation
Complex parameter handling
No type safety for column names
Difficult to maintain and extend
Error-prone string manipulation
Performance issues with suboptimal queries
No validation of sort columns or directions

Stage 4: Enter sqlx-paginated

Let's see how sqlx-paginated transforms this complexity into clean, maintainable code:

#[derive(sqlx::FromRow, serde::Serialize)]
struct Order {
    id: Uuid,
    customer_name: String,
    email: String,
    status: String,
    total_amount: Decimal,
    created_at: DateTime<Utc>,
}

async fn get_orders(
    pool: &PgPool,
    params: web::Query<FlatQueryParams>,
) -> Result<HttpResponse, Error> {
    let paginated_orders = paginated_query_as!(Order, "SELECT * FROM orders")
        .with_params(params)
        .fetch_paginated(&pool)
        .await?;

    Ok(HttpResponse::Ok().json(paginated_orders))
}

This simple code handles all our requirements and more. Let's break down how it works and explore its advanced features.

Type-Safe Parameter Handling

sqlx-paginated automatically handles query parameters based on your struct definition:

// These query parameters are automatically type-checked against Order struct
let params = QueryParamsBuilder::<Order>::new()
    .with_pagination(1, 10)
    .with_sort("created_at", QuerySortDirection::Descending)
    .with_search("john", vec!["customer_name", "email"])
    .with_date_range("created_at", Some(start_date), Some(end_date))
    .build();

Automatic Query Building

// This single line generates an optimized query with proper indexing hints
let query = paginated_query_as!(Order, "SELECT * FROM orders")
    .with_params(params);

// Generated SQL (simplified):
/*
WITH counted_records AS (
    SELECT *,
           COUNT(*) OVER() as total_records
    FROM orders
    WHERE (
        customer_name ILIKE $1 
        OR email ILIKE $1
    )
    AND created_at BETWEEN $2 AND $3
    ORDER BY created_at DESC
    LIMIT $4 OFFSET $5
)
SELECT * FROM counted_records;
*/

Advanced Features

1. Custom Filter Combinations

let query = paginated_query_as!(Order, "SELECT * FROM orders")
    .with_query_builder(|params| {
        QueryBuilder::<Order, Postgres>::new()
            .with_search(params)
            .with_filters(params)
            .with_date_range(params)
            .with_combined_conditions(|builder| {
                builder
                    .add_condition("status = 'PENDING'")
                    .add_condition("total_amount > 100")
                    .combine_with("OR")
            })
            .build()
    });

2. Dynamic Column Selection

let query = paginated_query_as!(Order, 
    "SELECT 
        id, 
        customer_name,
        CASE 
            WHEN status = 'COMPLETED' THEN total_amount 
            ELSE 0 
        END as total_amount
     FROM orders"
)
.with_params(params);

3. Security Features

sqlx-paginated automatically protects against several security concerns. Here's how it handles potentially dangerous inputs:

// These are automatically sanitized and validated
let unsafe_params = QueryParamsBuilder::<Order>::new()
    .with_sort("created_at; DROP TABLE orders;", QuerySortDirection::Ascending)
    .with_search("'; SELECT * FROM users; --", vec!["customer_name"])
    .build();

// The library safely handles these attempts
let safe_query = paginated_query_as!(Order, "SELECT * FROM orders")
    .with_params(unsafe_params);

Real-World Usage Patterns

1. Admin Dashboard Implementation

async fn admin_orders(
    pool: &PgPool,
    params: web::Query<FlatQueryParams>,
) -> Result<HttpResponse, Error> {
    // Create base query with joins
    let base_query = r#"
        SELECT 
            o.*, 
            u.email as customer_email,
            COUNT(i.id) as item_count
        FROM orders o
        LEFT JOIN users u ON o.user_id = u.id
        LEFT JOIN order_items i ON o.id = i.order_id
        GROUP BY o.id, u.email
    "#;

    let paginated_results = paginated_query_as!(OrderWithDetails, base_query)
        .with_params(params)
        .with_query_builder(|params| {
            QueryBuilder::<OrderWithDetails, Postgres>::new()
                .with_search(params)
                .with_filters(params)
                .with_date_range(params)
                .with_raw_condition("item_count > 0")
                .build()
        })
        .fetch_paginated(&pool)
        .await?;

    Ok(HttpResponse::Ok().json(paginated_results))
}

2. Analytics API with Dynamic Aggregation

async fn order_analytics(
    pool: &PgPool,
    params: web::Query<FlatQueryParams>,
) -> Result<HttpResponse, Error> {
    let base_query = r#"
        SELECT 
            DATE_TRUNC('day', created_at) as date,
            status,
            COUNT(*) as order_count,
            SUM(total_amount) as total_revenue
        FROM orders
        GROUP BY DATE_TRUNC('day', created_at), status
    "#;

    let analytics = paginated_query_as!(OrderAnalytics, base_query)
        .with_params(params)
        .with_query_builder(|params| {
            QueryBuilder::<OrderAnalytics, Postgres>::new()
                .with_date_range(params)
                .with_filters(params)
                .build()
        })
        .fetch_paginated(&pool)
        .await?;

    Ok(HttpResponse::Ok().json(analytics))
}

Best Practices and Tips

After extensive production use of sqlx-paginated, here are our recommended practices:

1. Define Base Queries Separately

Keep your base queries organized and maintainable:

const BASE_ORDER_QUERY: &str = r#"
    SELECT 
        o.*,
        u.email as customer_email
    FROM orders o
    LEFT JOIN users u ON o.user_id = u.id
"#;

2. Create Custom Query Builders for Complex Logic

fn build_order_query(params: &QueryParams) -> QueryBuilder<Order, Postgres> {
    QueryBuilder::<Order, Postgres>::new()
        .with_search(params)
        .with_filters(params)
        .with_date_range(params)
        .with_combined_conditions(|builder| {
            builder
                .add_condition("status IN ('PENDING', 'PROCESSING')")
                .add_condition("total_amount > 0")
                .combine_with("AND")
        })
}

3. Use Custom Response Types

#[derive(Serialize)]
struct PaginatedOrderResponse {
    orders: Vec<Order>,
    pagination: PaginationInfo,
    summary: OrderSummary,
}

async fn get_orders_with_summary(
    pool: &PgPool,
    params: web::Query<FlatQueryParams>,
) -> Result<HttpResponse, Error> {
    let paginated = paginated_query_as!(Order, BASE_ORDER_QUERY)
        .with_params(params)
        .fetch_paginated(&pool)
        .await?;

    let summary = calculate_order_summary(&paginated.records);

    Ok(HttpResponse::Ok().json(PaginatedOrderResponse {
        orders: paginated.records,
        pagination: paginated.pagination_info(),
        summary,
    }))
}

Conclusion

Throughout this journey, we've seen how sqlx-paginated transforms a common yet complex challenge in web development into a manageable, maintainable solution. There are even more examples of advanced configurations and patterns awaiting on the repo's readme.

The library's approach to data filtering and pagination isn't just about splitting data into pages – it's about providing a complete toolkit for building robust, performant APIs.

The benefits extend beyond just code cleanliness:

Dramatically reduced development time
Eliminated entire categories of potential bugs
Built-in security protections
Type-safe operations
Optimized query performance
Maintainable, predictable and extensible codebase with fluent APIs.

Getting Started

To begin using sqlx-paginated in your project:

Add to your Cargo.toml:

[dependencies]
sqlx-paginated = { version = "0.1.0", features = ["postgres"] }

Import and start using:

use sqlx_paginated::{paginated_query_as, FlatQueryParams};

Contributing

The sqlx-paginated library is open source and welcomes contributions. Whether it's:

Adding support for new databases
Implementing additional features
Improving documentation
Sharing real-world usage patterns
Reporting issues or suggesting improvements

Every contribution helps build a better tool for the entire Rust community.

With sqlx-paginated, you can achieve this while maintaining Rust's promises of safety and performance.

The future of web development in Rust looks a little brighter when we can focus on building features for our users rather than fighting with implementation details.

DEV Community