Route SELECT Queries to Read Replicas in Python

A mock round-robin router that forwards SELECT queries to read replicas and sends writes to the primary.

Easy Python 3.9+ Aug 9, 2026 Database scaling & optimization 13 views 0 copies

database replication routing mock load-balancing

Python code

33 lines

Python 3.9+

import random

class ReadReplicaRouter:
    """Round-robin router that sends SELECT queries to read replicas."""
    
    def __init__(self, replicas):
        self.replicas = replicas
        self.counter = 0
    
    def route(self, sql):
        if sql.strip().upper().startswith("SELECT"):
            replica = self.replicas[self.counter % len(self.replicas)]
            self.counter += 1
            return replica
        return "primary"
    
    def execute_mock(self, sql):
        target = self.route(sql)
        return f"Executed on {target}: {sql}"

if __name__ == "__main__":
    router = ReadReplicaRouter(["replica-1", "replica-2", "replica-3"])
    
    queries = [
        "SELECT * FROM users;",
        "INSERT INTO logs VALUES (1);",
        "SELECT name FROM products;",
        "UPDATE orders SET status='shipped';",
        "SELECT count(*) FROM orders;"
    ]
    
    for query in queries:
        print(router.execute_mock(query))

Output

stdout

Executed on replica-1: SELECT * FROM users;
Executed on primary: INSERT INTO logs VALUES (1);
Executed on replica-2: SELECT name FROM products;
Executed on primary: UPDATE orders SET status='shipped';
Executed on replica-3: SELECT count(*) FROM orders;

How it works

The route method checks if the SQL statement starts with SELECT (case-insensitive) and uses a counter to round-robin across the replica list. This distributes read load evenly while routing all other statements to the primary database. The execute_mock method simulates execution by returning a string indicating the target. This pattern is useful for load balancing reads in a database replication setup.

Common mistakes

Forgetting to strip whitespace or upper() before checking the SQL prefix.
Assuming the counter resets; it should persist across calls in a real router.
Routing non-SELECT statements (like SHOW or WITH) to replicas by mistake.
Not handling the case where the replicas list is empty.

Variations

Use a random.choice() on the replicas list for random distribution instead of round-robin.
Implement a weighted router that sends more reads to larger replicas.

Real-world use cases

Django or SQLAlchemy database routers that direct ORM reads to replica databases.
Middleware in a web service that splits database connections based on query type.
Testing scripts that simulate replica behavior before deploying a real routing layer.

Route SELECT Queries to Read Replicas in Python

Python code

Output

How it works

Common mistakes

Variations

Real-world use cases

More from Database scaling & optimization

Tutorials

Quizzes

Python code

Output

How it works

Common mistakes

Variations

Real-world use cases

More from Database scaling & optimization

Keep learning

Tutorials

Quizzes