🔧 From Query Chaos to API Bliss: Navigating pagination and data filtering with sqlx-paginated for PostgreSQL in Rust
Nachrichtenbereich: 🔧 Programmierung
🔗 Quelle: dev.to
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.
...
🔧 Diesel vs SQLx in Raw and ORM Modes
📈 32.8 Punkte
🔧 Programmierung
🔧 Rusty Recipies: sqlx + Uuid
📈 31.44 Punkte
🔧 Programmierung
🔧 sqlite / sqlx + async
📈 31.44 Punkte
🔧 Programmierung
🔧 Navigating PostgreSQL - Query Optimizations
📈 29.49 Punkte
🔧 Programmierung
🔧 CodeSOD: Query Query Query
📈 29.45 Punkte
🔧 Programmierung
🔧 Index Filtering in PostgreSQL and YugabyteDB
📈 24.99 Punkte
🔧 Programmierung