sqlx-pool-registry 0.3.0

Lightweight SQLx PostgreSQL connection pool routing for primary/replica separation
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83

#[cfg(all(feature = "with-sqlx-0_8", not(feature = "with-sqlx-0_9")))]
extern crate sqlx_0_8 as sqlx;
#[cfg(all(feature = "with-sqlx-0_9", not(feature = "with-sqlx-0_8")))]
extern crate sqlx_0_9 as sqlx;

use registry_sqlx::PgPool;
use sqlx_pool_registry::sqlx as registry_sqlx;
use sqlx_pool_registry::{PoolProvider, TestDbPools};

#[registry_sqlx::test]
#[ignore = "requires PostgreSQL via DATABASE_URL"]
async fn test_testdbpools_read_pool_rejects_writes(pool: PgPool) {
    let pools = TestDbPools::new(pool).await.unwrap();

    let write_pid: (i32,) = registry_sqlx::query_as("SELECT pg_backend_pid()")
        .fetch_one(pools.write())
        .await
        .expect("Write pool should provide a PostgreSQL connection");
    let read_pid: (i32,) = registry_sqlx::query_as("SELECT pg_backend_pid()")
        .fetch_one(pools.read())
        .await
        .expect("Read pool should provide a PostgreSQL connection");
    assert_ne!(
        write_pid.0, read_pid.0,
        "TestDbPools read and write pools must use separate connections"
    );

    // Write operations should work on the write pool
    registry_sqlx::query("CREATE TEMP TABLE test_write (id INT)")
        .execute(pools.write())
        .await
        .expect("Write pool should allow CREATE TABLE");

    // Write operations should FAIL on the read pool
    let result = registry_sqlx::query("CREATE TEMP TABLE test_read_reject (id INT)")
        .execute(pools.read())
        .await;

    assert!(result.is_err(), "Read pool should reject CREATE TABLE");
    let err = result.unwrap_err().to_string();
    assert!(
        err.contains("read-only") || err.contains("cannot execute"),
        "Error should mention read-only restriction, got: {}",
        err
    );
}

#[registry_sqlx::test]
#[ignore = "requires PostgreSQL via DATABASE_URL"]
async fn test_testdbpools_read_pool_allows_selects(pool: PgPool) {
    let pools = TestDbPools::new(pool).await.unwrap();

    // Read operations should work on the read pool
    let result: (i32,) = registry_sqlx::query_as("SELECT 1 + 1 as sum")
        .fetch_one(pools.read())
        .await
        .expect("Read pool should allow SELECT");

    assert_eq!(result.0, 2, "Should compute 1 + 1 = 2");
}

#[registry_sqlx::test]
#[ignore = "requires PostgreSQL via DATABASE_URL"]
async fn test_testdbpools_write_pool_allows_writes(pool: PgPool) {
    let pools = TestDbPools::new(pool).await.unwrap();

    registry_sqlx::query("CREATE TABLE test_write (id INT)")
        .execute(pools.write())
        .await
        .expect("Write pool should allow CREATE TABLE");

    registry_sqlx::query("INSERT INTO test_write VALUES (1)")
        .execute(pools.write())
        .await
        .expect("Write pool should allow INSERT");

    let count: (i64,) = registry_sqlx::query_as("SELECT COUNT(*) FROM test_write")
        .fetch_one(pools.write())
        .await
        .expect("Write pool should allow reading written data");

    assert_eq!(count.0, 1);
}