Question: I am using SqlAlchemy, a python ORM library. And I used to access database directly from business layer directly by calling SqlAlchemy API.

But then I found that would cause too much time to run all my test cases and now I think maybe I should create a DB access layer, so I can use mock objects during test instead of access database directly.

I think there are 2 choices to do that :

1. use a single class which contains a DB connection and many methods like addUser/delUser/updateUser, addBook/delBook/updateBook. But this means this *class* will be very large.
2. Another approach is create different manager classes like "UserManager", "BookManager". But that means I have to pass a list of managers to Business layer, which seems a little Cumbersome.

How will you organize a database layer?

Answer: 
One way to capture modifications to the database, is to use the SQLAlchemy session extension mechanism and intercept flushes to the database using something like this:

```
from sqlalchemy.orm.attributes import instance_state
from sqlalchemy.orm import SessionExtension

class MockExtension(SessionExtension):
    def __init__(self):
        self.clear()

    def clear(self):
        self.updates = set()
        self.inserts = set()
        self.deletes = set()

    def before_flush(self, session, flush_context, instances):
        for obj in session.dirty:
            self.updates.add(obj)
            state = instance_state(obj)
            state.commit_all({})
            session.identity_map._mutable_attrs.discard(state)
            session.identity_map._modified.discard(state)

        for obj in session.deleted:
            self.deletes.add(obj)
            session.expunge(obj)

        self.inserts.update(session.new)
        session._new = {}

```

Then for tests you can configure your session with that mock and see if it matches your expectations.

```
mock = MockExtension()
Session = sessionmaker(extension=[mock], expire_on_commit=False)

def do_something(attr):
    session = Session()
    obj = session.query(Cls).first()
    obj.attr = attr
    session.commit()

def test_something():
    mock.clear()
    do_something('foobar')
    assert len(mock.updates) == 1
    updated_obj = mock.updates.pop()
    assert updated_obj.attr == 'foobar'

```

But you'll want to do at least some tests with a database anyway because you'll atleast want to know if your queries work as expected. And keep in mind that you can also have modifications to the database via `session.update()`, `.delete()` and `.execute()`.
SQLAlchemy has some facilities for [making mocking easier](http://svn.sqlalchemy.org/sqlalchemy/trunk/lib/sqlalchemy/engine/strategies.py) -- maybe that would be easier than trying to rewrite whole sections of your project?