Paginated queries for SQLx

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

Table of Contents

• Paginated queries for SQLx
  • Features
  • Core Capabilities
  • Technical Features
  • Query Features
  • Database Support
    • Current vs Planned Support
  • Market Analysis
    • Ecosystem Gaps
    • Unique Selling Points
    • Target Audience
  • Installation
  • Quick Start
    • Basic Usage
    • Response Example
  • API Reference
    • Pagination Parameters
    • Sort Parameters
    • Search Parameters
    • Date Range Parameters
    • Filtering Parameters
  • Query Examples
    • Combined search, sort, date range, pagination and filter
    • Date Range combined with two other filters
  • Performance Considerations
    • Query Pattern Optimization
    • Recommended Indexes
    • Pagination Performance
  • Security Features
    • Input Sanitization
    • Protected Patterns
  • Contributing
  • License

Features

Core Capabilities

• 🔍 Full-text search with column specification
• 📑 Smart pagination with customizable page size
• 🔄 Dynamic sorting on any column
• 🎯 Flexible filtering system
• 📅 Date range filtering
• 🔒 Type-safe operations
• ⚡ High performance
• 🛡️ SQL injection protection

Technical Features

• Builder patterns for query parameters and query construction
• Graceful error handling
• Logging with tracing (if enabled)
• Macro and function support

Query Features

• Case-insensitive search
• Multiple column search
• Complex filtering conditions
• Date-based filtering
• Dynamic sort direction
• Customizable page size
• Result count optimization

Database Support

Current vs Planned Support

⚠️ Note: This documentation covers PostgreSQL features only, as it's currently the only supported database.

Market Analysis

Ecosystem Gaps

1. Query builders

   • Diesel: Full ORM, can be heavyweight
   • SeaQuery: Generic and can be verbose
   • sqlbuilder: Basic SQL building without pagination or security

2. Missing features in existing solutions

   • Easy integration with web frameworks
   • Automatic type casting
   • Typesafe search/filter/sort/pagination capabilities

Unique Selling Points

1. Quick Web Framework Integration with minimal footprint

Actix Web handler example

use sqlx_paginated::{paginated_query_as, FlatQueryParams};
use actix_web::{web, Responder, HttpResponse};

async fn list_users(web::Query(params): web::Query<FlatQueryParams>) -> impl Responder {
    let paginated_users = paginated_query_as!(User, "SELECT * FROM users")
        .with_params(params)
        .fetch_paginated(&pool)
        .await
        .unwrap();
    
    HttpResponse::Ok().json(json!(paginated_users))
}

2. Type Safety & Ergonomics for parameter configuration

let params = QueryParamsBuilder::<User>::new()
    .with_pagination(1, 10)
    .with_sort("created_at", QuerySortDirection::Descending)
    .with_search("john", vec!["name", "email"])
    .build();

3. Advanced Builder Patterns

• Optional fluent API for query parameters (QueryParams) which allow defining search, search location, date filtering, ordering, and custom filtering.
• Fluent API for the entire supported feature set, more here: advanced example

    paginated_query_as!(UserExample, "SELECT * FROM users")
        .with_params(initial_params)
        .with_query_builder(|params| {
            // Can override the default query builder (build_query_with_safe_defaults) with a complete custom one:
            QueryBuilder::<UserExample, Postgres>::new()
                .with_search(params) // Add or remove search feature from the query;
                .with_filters(params) // Add or remove custom filters from the query;
                .with_date_range(params) // Add or remove data range;
                .with_raw_condition("") // Add raw condition, no checks.
                .disable_protection() // This removes all column safety checks.
                .with_combined_conditions(|builder| {
                   // ...
                .build()
        })
        .disable_totals_count() // Disables the calculation of total record count
        .fetch_paginated(&pool)
        .await
        .unwrap()

Target Audience

1. Primary users

   • Rust web developers
   • Teams needing secure query building
   • Projects requiring pagination APIs
   • SQLx users wanting higher-level abstractions

2. Use cases

   • REST APIs with pagination
   • Admin panels
   • Data exploration interfaces

Installation

Add to Cargo.toml:

[dependencies]
sqlx_paginated = { version = "0.1.0", features = ["postgres"] }

Quick Start

Basic Usage

#[derive(sqlx::FromRow, serde::Serialize)]
struct User {
    id: i64,
    first_name: String,
    last_name: String,
    email: String,
    confirmed: bool,
    created_at: Option<DateTime<Utc>>,
}

/// Macro usage example
async fn get_users(pool: &PgPool) -> Result<PaginatedResponse<User>, sqlx::Error> {
    let paginated_response = paginated_query_as!(User, "SELECT * FROM users")
        // Alternative function call example (if macros cannot be applied to your use case):
        // paginated_query_as::<User>("SELECT * FROM users")
        .with_params(params)
        .fetch_paginated(&pool)
        .await
        .unwrap();

    paginated_response
}

Response Example

{
  "records": [
    {
      "id": "409e3900-c190-4dad-882d-ec2d40245329",
      "first_name": "John",
      "last_name": "Smith",
      "email": "john@example.com",
      "confirmed": true,
      "created_at": "2024-01-01T00:00:00Z"
    }
  ],
  "total": 1,
  "page": 1,
  "page_size": 10,
  "total_pages": 1
}

API Reference

Pagination Parameters

Example:

GET /v1/internal/users?page=2&page_size=20

Sort Parameters

Example:

GET /v1/internal/users?sort_column=last_name&sort_direction=ascending

Search Parameters

Example:

GET /v1/internal/users?search=john&search_columns=first_name,last_name,email

Date Range Parameters

Example:

GET /v1/internal/users?date_column=created_at&date_after=2024-01-01T00:00:00Z

Filtering Parameters

Example:

GET /v1/internal/users?confirmed=true

Query Examples

• Given the following struct, we can then perform search and filtering against its own fields.
• We should also receive a paginated response back with the matching records.

#[derive(Serialize, Deserialize, FromRow, Default)]
pub struct User {
    pub id: Option<Uuid>,
    pub first_name: String,
    pub last_name: String,
    pub confirmed: Option<bool>,
    pub created_at: Option<DateTime<Utc>>,
    pub updated_at: Option<DateTime<Utc>>,
}

1. Combined search, sort, date range, pagination and custom filter

• Notice the confirmed=true filter.

Request:

GET /v1/internal/users
    ?search=john
    &search_columns=first_name,last_name,email
    &sort_column=created_at
    &sort_direction=descending
    &date_before=2024-11-03T12:30:12.081598Z
    &date_after=2024-11-02T12:30:12.081598Z
    &page=1
    &page_size=20
    &confirmed=true

Response:

{
  "page": 1,
  "page_size": 20,
  "total": 2,
  "total_pages": 1,
  "records": [
    {
      "id": "409e3900-c190-4dad-882d-ec2d40245329",
      "first_name": "John",
      "last_name": "Smith",
      "email": "john.smith@example.com",
      "confirmed": true,
      "created_at": "2024-11-03T12:30:12.081598Z",
      "updated_at": "2024-11-03T12:30:12.081598Z"
    },
    {
      "id": "9167d825-8944-4428-bf91-3c5531728b5e",
      "first_name": "Johnny",
      "last_name": "Doe",
      "email": "johnny.doe@example.com",
      "confirmed": true,
      "created_at": "2024-10-28T19:14:49.064626Z",
      "updated_at": "2024-10-28T19:14:49.064626Z"
    }
  ]
}

2. Date range filter combined with two other custom filters

• Notice the confirmed=true and first_name=Alex filters.
• For the first_name filter the value will be an exact match (case-sensitive).

Request:

GET /v1/internal/users
    ?date_before=2024-11-03T12:30:12.081598Z
    &date_after=2024-11-02T12:30:12.081598Z
    &confirmed=true
    &first_name=Alex

Response:

{
  "page": 1,
  "page_size": 20,
  "total": 1,
  "total_pages": 1,
  "records": [
    {
      "id": "509e3900-c190-4dad-882d-ec2d40245329",
      "first_name": "Alex",
      "last_name": "Johnson",
      "email": "alex.johnson@example.com",
      "confirmed": true,
      "created_at": "2024-11-02T12:30:12.081598Z"
    }
  ]
}

Performance Considerations

Query Pattern Optimization

Recommended Indexes

-- Text search
CREATE INDEX idx_users_name_gin ON users USING gin(to_tsvector('english', name));

-- Composite indexes for common queries
CREATE INDEX idx_users_confirmed_created ON users(confirmed, created_at);

-- JSON indexes
CREATE INDEX idx_users_metadata ON users USING gin(metadata);

Pagination Performance

Security Features

Input Sanitization

• Search terms are cleaned and normalized
• Parameter input values are trimmed and/or clamped against their defaults
• Column names are validated against an allowlist:
  • The struct itself first;
  • Database specific table names second;
• SQL injection patterns are blocked
• System table access is prevented

Protected Patterns

• System schemas (pg_, information_schema)
• System columns (oid, xmin, etc.)
• SQL injection attempts
• Invalid characters in identifiers

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

License

This project is licensed under the MIT License - see the LICENSE file for details.