Crate sqlx_pool_router 

sqlx_pool_router

A lightweight library for routing database operations to different SQLx PostgreSQL connection pools based on whether they’re read or write operations.

This enables load distribution by routing read-heavy operations to read replicas while ensuring write operations always go to the primary database.

Features

• Zero-cost abstraction: Trait-based design with no runtime overhead
• Type-safe routing: Compile-time guarantees for read/write pool separation
• Backward compatible: PgPool implements PoolProvider for seamless integration
• Flexible: Use single pool or separate primary/replica pools
• Test helpers: TestDbPools for testing with #[sqlx::test]
• Well-tested: Comprehensive test suite with replica routing verification

Quick Start

Single Pool (Development)

use sqlx::PgPool;
use sqlx_pool_router::PoolProvider;

let pool = PgPool::connect("postgresql://localhost/mydb").await?;

// PgPool implements PoolProvider automatically
let result: (i32,) = sqlx::query_as("SELECT 1")
    .fetch_one(pool.read())
    .await?;

Read/Write Separation (Production)

use sqlx::postgres::PgPoolOptions;
use sqlx_pool_router::{DbPools, PoolProvider};

let primary = PgPoolOptions::new()
    .max_connections(5)
    .connect("postgresql://primary-host/mydb")
    .await?;

let replica = PgPoolOptions::new()
    .max_connections(10)
    .connect("postgresql://replica-host/mydb")
    .await?;

let pools = DbPools::with_replica(primary, replica);

// Reads go to replica
let users: Vec<(i32, String)> = sqlx::query_as("SELECT id, name FROM users")
    .fetch_all(pools.read())
    .await?;

// Writes go to primary
sqlx::query("INSERT INTO users (name) VALUES ($1)")
    .bind("Alice")
    .execute(pools.write())
    .await?;

Architecture

┌─────────────┐
│   DbPools   │
└──────┬──────┘
       │
  ┌────┴────┐
  ↓         ↓
┌─────┐  ┌─────────┐
│Primary│  │ Replica │ (optional)
└─────┘  └─────────┘

Generic Programming

Make your types generic over PoolProvider to support both single and multi-pool configurations:

use sqlx_pool_router::PoolProvider;

struct Repository<P: PoolProvider> {
    pools: P,
}

impl<P: PoolProvider> Repository<P> {
    async fn get_user(&self, id: i64) -> Result<String, sqlx::Error> {
        // Read from replica
        sqlx::query_scalar("SELECT name FROM users WHERE id = $1")
            .bind(id)
            .fetch_one(self.pools.read())
            .await
    }

    async fn create_user(&self, name: &str) -> Result<i64, sqlx::Error> {
        // Write to primary
        sqlx::query_scalar("INSERT INTO users (name) VALUES ($1) RETURNING id")
            .bind(name)
            .fetch_one(self.pools.write())
            .await
    }
}

Testing

Use TestDbPools with #[sqlx::test] to enforce read/write separation in tests:

use sqlx::PgPool;
use sqlx_pool_router::{TestDbPools, PoolProvider};

#[sqlx::test]
async fn test_repository(pool: PgPool) {
    let pools = TestDbPools::new(pool).await.unwrap();

    // Write operations through .read() will FAIL
    let result = sqlx::query("INSERT INTO users VALUES (1)")
        .execute(pools.read())
        .await;
    assert!(result.is_err());
}

This catches routing bugs immediately without needing a real replica database.

Structs

DbPools

Database pool abstraction supporting read replicas.

TestDbPools

Test pool provider with read-only replica enforcement.

Traits

PoolProvider

Trait for providing database pools with read/write routing.