Feature request: Support for transaction mode DB connections and/or separate DB endpoints for SELECT queries

Problem

In Kubernetes environments with PostgreSQL (e.g., CNPG), many deployments aim to optimize performance by routing SELECT queries to a read-only replica in the same availability zone as the Casdoor pod, while keeping INSERT/UPDATE/DELETE queries on the primary node.

Currently, Casdoor’s database access pattern makes this difficult because:

• The application reuses prepared statements across multiple transactions, which fails in transaction pooling mode with middleware like Pgcat or Odyssey (pq: prepared statement does not exist).
• In session pooling mode, Pgcat cannot reliably route queries based on type, causing DELETE/UPDATE to be sent to replicas, which results in read-only transaction errors.
• There is no built-in option in Casdoor to configure separate connections for reads and writes.

This makes it impossible to use effective read/write splitting at the proxy layer without modifying Casdoor’s source code.

Proposed Solution

Add support for:

1. Transaction pooling compatibility — ensure prepared statements are created per transaction so middleware like Pgcat in transaction mode works.

2. Optional separate read connection — allow specifying an additional READ_DB_DSN (or similar) so that SELECT queries can be routed to a read-only database connection, independent from writes.

This would enable Casdoor to scale more efficiently in production, reduce latency for read-heavy workloads, and better integrate with Postgres HA setups.