ERROR: cannot execute <STATEMENT> in a read-only transaction

Svix is the enterprise ready webhooks sending service. With Svix, you can build a secure, reliable, and scalable webhook platform in minutes. Looking to send webhooks? Give it a try!

If you’ve worked with databases long enough, you’ve likely come across an error similar to the following:

ERROR: cannot execute <STATEMENT> in a read-only transaction

This particular error message comes from Postgres, and occurs when trying to execute a destructive or modifying operation (e.g. CREATE, UPDATE, DELETE) in a read-only context. Often, this happens because you tried to execute one of these operations on a read-only replica of your database.

Common industry practice, including at Svix, is to direct appropriate queries to a read-only replica if you can tolerate the eventual consistency. Database replicas already exists to support failovers and protect against data loss, so directing SELECT queries to the replica improves your apps ability to scale up read workloads, and frees up resources on the primary writer instance of your database. All without increasing infra costs.

The downside is that this leaves you open to database misuse, leading to the infamous

ERROR: cannot execute <STATEMENT> in a read-only transaction

that can break parts of your application.

To understand why this happens, it’s helpful to look at some code that actually uses the database primary and replica, side by side.

/// Shared application state, passed to each function handling an API call.
///
/// Here `sea_orm` is an ORM framework - https://www.sea-ql.org/SeaORM/, and
/// `sea_orm::DatabaseConnection` is a connection pool for Postgres
#[derive(Clone)]
pub struct AppState {
pub db: sea_orm::DatabaseConnection,
pub replica: sea_orm::DatabaseConnection,
}

/// Helper function to SELECT messages in a customer's environment
async fn list_msgs_in_env(
db: &sea_orm::DatabaseConnection,
env_id: EnvironmentId,
) -> Result<Vec<Message>> {
let messages = models::message::Entity::find()
.filter(Column::EnvId.eq(env_id))
.all(db)
.await?;
Ok(messages)
}

/// Helper function to INSERT a new message in a customer environment.
async fn create_msg(
db: &sea_orm::DatabaseConnection,
env_id: EnvironmentId,
payload: String,
) -> Result<()> {
let msg = models::message::ActiveModel {
env_id: Set(env_id),
payload: Set(payload)
};
msg.insert(db).await?;
Ok(())
}

This code seems straightforward enough - though if you’re a defensive programmer, you may already be shuddering at the risk this code presents.

Can you guess the problem?

This is perfectly legal, and will compile without a warning.

let state: AppState = init_app_state().await?;

let env_id = "env_1234".into();
let payload = json!({ "email": "bob@example.io" }).to_string();

// Attempting to INSERT using the replica 😱
create_msg(&state.replica, env_id, payload).await?;

Which, when run, will lead to the oft-encountered error message:

ERROR: cannot execute INSERT in a read-only transaction

db: sea_orm::DatabaseConnection and replica: sea_orm::DatabaseConnection are the same type, so there’s no hint to the compiler that using the replica on create_msg is invalid.

In this particular example, the error is easy to spot, and a reviewer would (hopefully) catch the bug.

But what about here?

async fn get_env_settings(
db: &sea_orm::DatabaseConnection,
id: EnvironmentId,
) -> Result<EnvSettings> {
let settings = models::env_settings::Entity::find_by_id(id).await?;
if settings.is_none() {
-       return Err(Error::new("missing settings"))
+       // autocreate the settings by default if not found
+       let settings = models::env_settings::Entity {
+           id: Set(id),
+           ..Default::default()
+       };
+       return settings.insert(db).await
}
Ok(settings.unwrap())
}

The diff here is fairly innocent, at least in isolation.

But what if code elsewhere is calling get_env_settings(...) using the database replica? Code that was valid before is now invalid, but the author, reviewer, and compiler have no hint that they’re introducing a serious runtime error.

Tests could potentially catch this bug - if the codepath is covered, and your testing infrastructure actually sets up a database replica. Both big “ifs”, and not assumptions you want to make.

Let’s see if we can do better.

Stop the Error Before it’s Introduced

At Svix, we’ve written before about the importance of strong, static type systems for building reliable software.

Ideally, APIs should be easy to use, and impossible to misuse. If we could leverage the type system to signal to the compiler that the replica can only be used for SELECT queries, we’d squash these bugs before they’re even hatched.

We can start with the simplest approach - define new types to wrap sea_orm::DatabaseConnection.

// in orm.rs

#[derive(Clone)]
pub struct PrimaryConnection {
db: sea_orm::DatabaseConnection,
}

#[derive(Clone)]
pub struct ReplicaConnection {
db: sea_orm::DatabaseConnection,
}

Then, we’ll rewrite our helper functions to only use the PrimaryConnection or ReplicaConnection. But this quickly runs into a problem.

async fn create_msg(
-   db: &sea_orm::DatabaseConnection,
+   db: &PrimaryConnection,
env_id: EnvironmentId,
payload: String,
) -> Result<()> {
let msg = models::message::ActiveModel { ... };

// DOES NOT WORK.
// `.insert(...)` is defined by `sea_orm`, and is expecting
// a `sea_orm::DatabaseConnection`, NOT a `PrimaryConnection`
msg.insert(db).await?;

Ok(())
}

msg.insert(db) is a method added by sea_orm’s ActiveModelTrait, and it’s expecting a sea_orm::DatabaseConnection, not a PrimaryConnection.

Thankfully, Rust lets us define new traits with analogous method definitions to the ones sea_orm gave us.

// also in orm.rs

pub trait ActiveModelTrait_ {
type Model;

async fn insert_(self, db: &PrimaryConnection) -> DbResult<Self::Model>;
}

impl ActiveModelTrait_ for models::message::ActiveModel
{
type Model = models::message::Model;

async fn insert_(self, db: &PrimaryConnection) -> DbResult<models::message::Model> {
sea_orm::ActiveModelTrait::insert(self, &db.db).await
}
}

If you’re unfamiliar with Rust, this is just defining our own trait that wraps sea_orm’s trait using nearly identical method names, but with the restricted type definitions that only accept our new database connection types.

Now, our code can fully be rewritten to take advantage of the new traits.

- use sea_orm::ActiveModelTrait;
+ use crate::orm::ActiveModelTrait_;

async fn create_msg(
-   db: &sea_orm::DatabaseConnection,
+   db: &PrimaryConnection,
env_id: EnvironmentId,
payload: String,
) -> Result<()> {
let msg = models::message::ActiveModel { ... };

-   msg.insert(db).await?;
+   msg.insert_(db).await?;

Ok(())
}

If we try to use a ReplicaConnection where a PrimaryConnection is expected, the compiler will stop us.

let state: AppState = init_app_state().await?;

let env_id = "env_1234".into();
let payload = json!({ "email": "bob@example.io" }).to_string();

// Will fail to compile since `replica` is a `ReplicaConnection`,
// and not a `PrimaryConnection`.
create_msg(&state.replica, env_id, payload).await?;

This is close to what we want, but what about SELECT queries?

Recall the function from before

use sea_orm::Select; // pulled in so we can call `.all(...).await` to fetch results

async fn list_msgs_in_env(
db: &sea_orm::DatabaseConnection,
env_id: EnvironmentId,
) -> Result<Vec<Message>> {
let messages = models::message::Entity::find()
.filter(Column::EnvId.eq(env_id))
.all(db)
.await?;
Ok(messages)
}

We’ll do something similar to what we did with the ActiveModelTrait - define traits for our model to use the replica...

// also in orm.rs

pub trait Select_: Clone {
type Model;

async fn all_(self, db: &ReplicaConnection) -> DbResult<Self::Model>>;
}

impl Select_ for models::message::Entity {
type Model = models::message::Model;

async fn all_(self, db: &ReplicaConnection) -> DbResult<Self::Model>> {
sea_orm::Select::all(self, &db.db).await
}
}

...and update the relevant helper functions.

- use sea_orm::Select;
+ use crate::orm::Select_;

async fn list_msgs_in_env(
-   db: &sea_orm::DatabaseConnection,
+   db: &ReplicaConnection,
env_id: EnvironmentId,
) -> Result<Vec<Message>> {
let messages = models::message::Entity::find()
.filter(Column::EnvId.eq(env_id))
-       .all(db)
+       .all_(db)
.await?;
Ok(messages)
}

But wait!

Code like this will fail to compile:

let state: AppState = init_app_state().await?;

let env_id = "env_1234".into();

// Will fail to compile since `db` is a `PrimaryConnection`,
// and not a `ReplicaConnection`.
list_msgs_in_env(&state.db, env_id, payload).await?;

We want to restrict the replica’s ability to call INSERT, UPDATE, DELETE, but it is perfectly valid for the database primary to support SELECT queries. It may even be necessary to use the primary in some situations, if we can’t tolerate replication lag.

Ideally, we’d be able to call list_msgs_in_env, or any SELECT query, using either the replica or the primary - only UPDATE, DELETE, and INSERT statements should be restricted to the primary.

PrimaryConnection and ReplicaConnection have shared behavior (can call SELECT queries), but PrimaryConnection has special behavior (can call INSERT, UPDATE, DELETE) that the replica does not share. Let’s rely on the type system to capture this.

We’ll start by defining new traits to capture the shared behavior.

// in orm.rs

/// Denotes a connection can be used for SELECT queries.
pub trait ReadConnection {
/// Get a handle to the raw database connection.
/// We hide this method using `doc(hidden)`, and prefix
/// with `dangerous_`, to discourage and flag it's use outside
/// of the orm module.
#[doc(hidden)]
fn dangerous_raw_db_handle(&self) -> &sea_orm::DatabaseConnection;
}

/// Denotes a connection that can be used for UPDATE, INSERT, DELETE, etc.
///
/// The `: ReadConnection` syntax means that any struct implementing `WriteConnection`
/// must also implement `ReadConnection`, since we want the
/// database primary to also support SELECT queries.
pub trait WriteConnection: ReadConnection {}


// Only implement ReadConnection for ReplicaConnection.

impl ReadConnection for ReplicaConnection {
fn dangerous_raw_db_handle(&self) -> &sea_orm::DatabaseConnection {
&self.db
}
}

// Implement both ReadConnection and WriteConnection for PrimaryConnection

impl ReadConnection for PrimaryConnection {
fn dangerous_raw_db_handle(&self) -> &sea_orm::DatabaseConnection {
&self.db
}
}

impl WriteConnection for PrimaryConnection {}

We can now update our traits from earlier to accept ReadConnection and WriteConnection instead of ReplicaConnection and PrimaryConnection.

pub trait Select_: Clone {
type Model;

-   async fn all_(self, db: &ReplicaConnection) -> DbResult<Self::Model>>;
+   async fn all_(self, db: &impl ReadConnection) -> DbResult<Self::Model>>;
}

impl Select_ for models::message::Entity {
type Model = models::message::Model;

-   async fn all_(self, db: &ReplicaConnection) -> DbResult<Self::Model>> {
+   async fn all_(self, db: &impl ReadConnection) -> DbResult<Self::Model>> {
-       sea_orm::Select::all(self, &db.db).await
+       sea_orm::Select::all(self, db.dangerous_raw_db_handle()).await
}
}

pub trait ActiveModelTrait_ {
type Model;

-   async fn insert_(self, db: &PrimaryConnection) -> DbResult<Self::Model>;
+   async fn insert_(self, db: &impl WriteConnection) -> DbResult<Self::Model>;
}

impl ActiveModelTrait_ for models::message::ActiveModel
{
type Model = models::message::Model;

-   async fn insert_(self, db: &PrimaryConnection) -> DbResult<models::message::Model> {
+   async fn insert_(self, db: &impl WriteConnection) -> DbResult<models::message::Model> {
-       sea_orm::ActiveModelTrait::insert(self, &db.db).await
+       sea_orm::ActiveModelTrait::insert(self, db.dangerous_raw_db_handle()).await
}
}

Finally, we can update our helper functions to accept the traits, rather than the concrete structs.

async fn list_msgs_in_env(
-   db: &ReplicaConnection,
+   db: &impl ReplicaConnection,
env_id: EnvironmentId,
) -> Result<Vec<Message>> {
let messages = models::message::Entity::find()
.filter(Column::EnvId.eq(env_id))
.all_(db)
.await?;
Ok(messages)
}

async fn create_msg(
-   db: &PrimaryConnection,
+   db: &impl PrimaryConnection,
env_id: EnvironmentId,
payload: String,
) -> Result<()> {
let msg = models::message::ActiveModel { ... };

msg.insert_(db).await?;

Ok(())
}

Now, our code supports SELECT queries for both the primary and the replica, without preventing the primary from running SELECT queries.

let state: AppState = init_app_state().await?;

let env_id = "env_1234".into();
let payload = json!({ "email": "bob@example.io" }).to_string();

// ❌ Will fail to compile
create_msg(&state.replica, env_id, payload).await?;

// ✅ Will compile successfully
create_msg(&state.db, env_id, payload).await?;

// ✅ Will compile successfully
list_msgs_in_env(&state.replica, env_id, payload).await?;

// ✅ Will compile successfully
list_msgs_in_env(&state.db, env_id, payload).await?;

This achieves our original goal of making it (nearly) impossible to call destructive database operations using the database replica, without compromising our ability to make SELECT queries on the primary. We’ve stopped the infamous ERROR: cannot execute <STATEMENT> in a read-only transaction before the code even ran.

Beyond “Read Only” Databases

Making INSERT calls on a read-only replica is not the only way you can mess up database operations, but it is probably the most common and relatable.

Building reliable software often means anticipating where things can go wrong, and using the tools available to prevent those failures before they happen. By leveraging Rust’s type system to distinguish between database connections based on their intended use, we’ve eliminated a common runtime error before tests had a chance to run.

This approach isn’t unique to database replicas. The same pattern can be applied anywhere you have resources with different capabilities or constraints: read-only file handles versus writable ones, authenticated versus unauthenticated users, or connections with different permission levels. The key insight is that if your domain has meaningful distinctions in how resources can be used, you can leverage the type system to reflect those distinctions. When you do, impossible-to-misuse APIs aren’t just an ideal — they’re the default.

---

For more content like this, make sure to follow us on X, Github or RSS for the latest updates for the Svix webhook service, or join the discussion on our community Slack.