Design pattern to organize non-trivial ORM queries?

Question

I am developing a web API with 10 tables or so in the backend, with several one-to-many and many-to-many associations. The API essentially is a database wrapper that performs validated updates and conditional queries. It's written in Python, and I use SQLAlchemy for ORM and CherryPy for HTTP handling.

So far I have separated the 30-some queries the API performs into functions of their own, which look like this:

# in module "services.inventory"
def find_inventories(session, user_id, *inventory_ids, **kwargs):
    query = session.query(Inventory, Product)
    query = query.filter_by(user_id=user_id, deleted=False)
    ...
    return query.all()

def find_inventories_by(session, app_id, user_id, by_app_id, by_type, limit, page):
    ....

# in another service module
def remove_old_goodie(session, app_id, user_id):
    try:
        old = _current_goodie(session, app_id, user_id)
        services.inventory._remove(session, app_id, user_id, [old.id])
    except ServiceException, e:
        # log it and do stuff
....

The CherryPy request handler calls the query methods, which are scattered across several service modules, as needed. The rationale behind this solution is, since they need to access multiple model classes, they don't belong to individual models, and also these database queries should be separated out from direct handling of API accesses.

I realize that the above code might be called Foreign Methods in the realm of refactoring. I could well live with this way of organizing for a while, but as things are starting to look a little messy, I'm looking for a way to refactor this code.

• Since the queries are tied directly to the API and its business logic, they are hard to generalize like getters and setters.
• It smells to repeat the session argument like that, but as the current implementation of the API creates a new CherryPy handler instance for each API call and therefore the session object, there is no global way of getting at the current session.

Is there a well-established pattern to organize such queries? Should I stick with the Foreign Methods and just try to unify the function signature (argument ordering, naming conventions etc.)? What would you suggest?

Answer 1

SQLAlchemy strongly suggests that the session maker be part of some global configuration.

It is intended that the sessionmaker() function be called within the global scope of an application, and the returned class be made available to the rest of the application as the single class used to instantiate sessions.

Queries which are in separate modules isn't an interesting problem. The Django ORM works this way. A web site usually consists of multiple Django "applications", which sounds like your site that has many "service modules".

Knitting together multiple services is the point of an application. There aren't a lot of alternatives that are better.

Answer 2

The standard way to have global access to the current session in a threaded environment is ScopedSession. There are some important aspects to get right when integrating with your framework, mainly transaction control and clearing out sessions between requests. A common pattern is to have an autocommit=False (the default) ScopedSession in a module and wrap any business logic execution in a try-catch clause that rolls back in case of exception and commits if the method succeeded, then finally calls Session.remove(). The business logic would then import the Session object into global scope and use it like a regular session.

There seems to be an existing CherryPy-SQLAlchemy integration module, but as I'm not too familiar with CherryPy, I can't comment on its quality.

Having queries encapsulated as functions is just fine. Not everything needs to be in a class. If they get too numerous just split into separate modules by topic.

What I have found useful is too factor out common criteria fragments. They usually fit rather well as classmethods on model classes. Aside from increasing readability and reducing duplication, they work as implementation hiding abstractions up to some extent, making refactoring the database less painful. (Example: instead of (Foo.valid_from <= func.current_timestamp()) & (Foo.valid_until > func.current_timestamp()) you'd have Foo.is_valid())