How to enforce reads from read replica in Postgres using django and or flask?

Question

Is enforcement of a reads from a read replica an application layer task?

i.e. I have a Postgres database and I have set up a read replica.

On the application side I have two connections one for the "write" database and one for the "read replica" database.

In my code if I do a "read" action I use the connection to the read replica. But when I go to insert or update I use the connection to the "write" database a.k.a. master.

Is there better with django or flask that this is managed automatically. i.e.

I would prefer to avoid specifying directly in code the connection to use and just have django or flask figure it out on their own.

Answer 1

Django

For this purpose django supports so called Database routers.

First create your custom router:

class CustomRouter:
    def db_for_read(self, model, **hints):
        return 'replica'

    def db_for_write(self, model, **hints):
        return 'master'

And configure django orm to use it like that.

DATABASES = {
    'default': {},
    'primary': {
        'NAME': 'master',
        'ENGINE': 'django.db.backends.mysql',
        'USER': 'mysql_user',
        'PASSWORD': 'spam',
    },
    'replica1': {
        'NAME': 'replica',
        'ENGINE': 'django.db.backends.mysql',
        'USER': 'mysql_user',
        'PASSWORD': 'eggs',
    },

}

DATABASE_ROUTERS = ['path.to.CustomRouter']

The sample code was taken from the docs (it is worth reading!) and slightly adjusted.

SQLAlchemy (flask)

I went through the SQLAlchemy docs and found a link to this article, which describes how to implement djangos database router approach with SQLAlchemy.

You can use a custom session here to implement this properly.

The following snippets are picked from the linked article and are slightly adjusted.

Create your engines:

engines = {
    'master': create_engine('postgresql://user:***@localhost:5432/master',
                            logging_name='master'),
    'replica': create_engine('postgresql://user:***@localhost:5432/replica',
                             logging_name='replica'),
}

Create a custom session class:

class RoutingSession(Session):

    def get_bind(self, mapper=None, clause=None):
        if self._flushing:
            return engines['master']
        else:
            return engines['replica']

And create your session like this:

Session = scoped_session(sessionmaker(class_=RoutingSession, autocommit=True))

Read the article for details and limitations.