Managing connection creation in Python?

Question

Applications often need to connect to other services (a database, a cache, an API, etc). For sanity and DRY, we'd like to keep all of these connections in one module so the rest of our code base can share connections.

To reduce boilerplate, downstream usage should be simple:

# app/do_stuff.py
from .connections import AwesomeDB

db = AwesomeDB()

def get_stuff():
    return db.get('stuff')

And setting up the connection should also be simple:

# app/cli.py or some other main entry point
from .connections import AwesomeDB

db = AwesomeDB()
db.init(username='stuff admin')    # Or os.environ['DB_USER']

Web frameworks like Django and Flask do something like this, but it feels a bit clunky:

Connect to a Database in Flask, Which Approach is better? http://flask.pocoo.org/docs/0.10/tutorial/dbcon/

One big issue with this is that we want a reference to the actual connection object instead of a proxy, because we want to retain tab-completion in iPython and other dev environments.

So what's the Right Way (tm) to do it? After a few iterations, here's my idea:

#app/connections.py
from awesome_database import AwesomeDB as RealAwesomeDB
from horrible_database import HorribleDB as RealHorribleDB


class ConnectionMixin(object):
    __connection = None

    def __new__(cls):
        cls.__connection = cls.__connection or object.__new__(cls)
        return cls.__connection

    def __init__(self, real=False, **kwargs):
        if real:
            super().__init__(**kwargs)

    def init(self, **kwargs):
        kwargs['real'] = True
        self.__init__(**kwargs)


class AwesomeDB(ConnectionMixin, RealAwesomeDB):
    pass


class HorribleDB(ConnectionMixin, RealHorribleDB):
    pass

Room for improvement: Set initial __connection to a generic ConnectionProxy instead of None, which catches all attribute access and throws an exception.

I've done quite a bit of poking around here on SO and in various OSS projects and haven't seen anything like this. It feels pretty solid, though it does mean a bunch of modules will be instantiating connection objects as a side effect at import time. Will this blow up in my face? Are there any other negative consequences to this approach?

Answer 1

First, design-wise, I might be missing something, but I don't see why you need the heavy mixin+singleton machinery instead of just defining a helper like so:

_awesome_db = None
def awesome_db(**overrides):
    global _awesome_db
    if _awesome_db is None:
        # Read config/set defaults.
        # overrides.setdefault(...)
        _awesome_db = RealAwesomeDB(**overrides)
    return _awesome_db

Also, there is a bug that might not look like a supported use-case, but anyway: if you make the following 2 calls in a row, you would wrongly get the same connection object twice even though you passed different parameters:

db = AwesomeDB()
db.init(username='stuff admin')

db = AwesomeDB()
db.init(username='not-admin')    # You'll get admin connection here.

An easy fix for that would be to use a dict of connections keyed on the input parameters.

Now, on the essence of the question.

I think the answer depends on how your "connection" classes are actually implemented.

Potential downsides with your approach I see are:

• In a multithreaded environment you could get problems with unsychronized concurrent access to the global connection object from multiple threads, unless it is already thread-safe. If you care about that, you could change your code and interface a bit and use a thread-local variable.

• What if a process forks after creating the connection? Web application servers tend to do that and it might not be safe, again depending on the underlying connection.

• Does the connection object have state? What happens if the connection object becomes invalid (due to i.e. connection error/time out)? You might need to replace the broken connection with a new one to return the next time a connection is requested.

Connection management is often already efficiently and safely implemented through a connection pool in client libraries.

For example, the redis-py Redis client uses the following implementation:

https://github.com/andymccurdy/redis-py/blob/1c2071762ad9b9288e786665990083e61c1cf355/redis/connection.py#L974

The Redis client then uses the connection pool like so:

• Requests a connection from the connection pool.
• Tries to execute a command on the connection.
• If the connection fails, the client closes it.
• In any case, finaly it is returned to the connection pool so it can be reused by subsequent calls or other threads.

So since the Redis client handles all of that under the hood, you can safely do what you want directly. Connections will be lazily created until the connection pool reaches full capacity.

# app/connections.py
def redis_client(**kwargs):
    # Maybe read configuration/set default arguments
    # kwargs.setdefault()
    return redis.Redis(**kwargs)

Similarly, SQLAlchemy can use connection pooling as well.

To summarize, my understanding is that:

• If your client library supports connection pooling, you don't need to do anything special to share connections between modules and even threads. You could just define a helper similar to redis_client() that reads configuration, or specifies default parameters.

• If your client library provides only low-level connection objects, you will need to make sure access to them is thread-safe and fork-safe. Also, you need to make sure each time you return a valid connection (or raise an exception if you can't establish or reuse an existing one).