How to fix: Data Modeling for Relational database, read-only access

🚨 Understanding the Error

In the context of Python-based data modeling (using ORMs like SQLAlchemy, Django ORM, or Peewee), a “Read-Only Access” error occurs when the application logic attempts to perform a Data Manipulation Language (DML) operation—such as INSERT, UPDATE, or DELETE—against a database connection that has been restricted to SELECT operations.

This is not typically a syntax error in your Python code, but rather a runtime constraint violation. It signifies a mismatch between the Data Model’s intent (to persist state) and the Database Session’s capabilities.

Common traceback signatures include:

• psycopg2.errors.ReadOnlySqlTransaction: cannot execute INSERT in a read-only transaction
• sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1290, 'The MySQL server is running with the --read-only option...')
• sqlite3.OperationalError: attempt to write a readonly database

🔍 Root Cause

Root Cause	Description	Technical Context
User Privileges	The DB user assigned to the application only has GRANT SELECT permissions.	PostgreSQL/MySQL ACLs
Replica Routing	The application is connected to a Read-Replica/Follower node instead of the Leader/Primary.	High Availability (HA) Clusters
Transaction Mode	The ORM session was explicitly initialized with read_only=True or autoflush=False.	SQLAlchemy Session Configuration
Filesystem Locks	The database file (e.g., SQLite) has OS-level read-only permissions or is locked by another process.	chmod 444 or locked .db files
Cloud Middleware	Cloud-native proxies (like AWS RDS Proxy) routing traffic to read-only endpoints.	Infrastructure Config

🛠️ Step-by-Step Solutions

1. Identify and Correct Connection String Routing

If you are using a cluster, ensure your write-heavy models are using the Primary endpoint. In Python, you can implement a router or manually switch engines.

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# WRONG: Pointing to the reader endpoint
# engine = create_engine("postgresql://user:pass@db-reader-instance:5432/dbname")

# RIGHT: Pointing to the writer endpoint for DML operations
engine = create_engine("postgresql://user:pass@db-writer-instance:5432/dbname")
Session = sessionmaker(bind=engine)
session = Session()

2. Adjusting SQLAlchemy Session State

If you are using SQLAlchemy 1.4+, verify that the execution options are not forcing a read-only state.

from sqlalchemy import select, update
from my_models import User

# Ensure the execution option is not setting the transaction to read-only
session.execute(
    update(User).where(User.id == 1).values(name="Elite Engineer"),
    execution_options={"read_only": False} # Ensure this isn't True
)
session.commit()

3. Fixing SQLite Filesystem Constraints

If working with SQLite locally, use ls -l to check permissions. If the file is owned by root but your app runs as www-data, writes will fail.

# Fix permissions in terminal
chmod 664 development.db
chown youruser:www-data development.db

4. Database Level Grant Fix (SQL)

If the Python code is correct but the database rejects the command, you must elevate the user privileges via your SQL console:

-- Connect as superuser
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO your_app_user;
-- For sequence-based IDs (PostgreSQL)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO your_app_user;

🛡️ Prevention and Best Practices

• Implement CQRS Pattern: Separate your data models into “Read Models” and “Write Models.” Use a dedicated connection pool for read-only queries to improve performance and safety.
• Environment Variable Validation: Use Pydantic or similar libraries to ensure your DATABASE_URL matches the expected environment (e.g., WRITER_DB_URL vs READER_DB_URL).
• Automated Testing: Include a “Write Canary” test in your CI/CD pipeline that attempts a simple INSERT to verify that the application user has sufficient permissions in the staging environment.
• Health Checks: Use a custom health check endpoint that verifies the transaction_read_only status of the current connection:

  # PostgreSQL specific check
  is_readonly = session.execute("SHOW transaction_read_only").scalar()
  if is_readonly == 'on':
      raise Exception("Critical: Connected to Read-Only node!")