[Twisted-web] database best practices

[Drew Smathers]

On Wed, Nov 26, 2008 at 5:12 AM, tarjei <tarjei at nu.no> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hi there,
> Michał Pasternak wrote:
>> Martin Bright wrote:
>>>
>>> Hi!
>>>
>>> What is the best way to implement synchronous DB access in a twisted
>>> application?
>>>
>> For newly created software I would use Divmod Axiom. It uses SQLite as a
>> backend.
>> For already existing databases (in MySQL or PostgreSQL) I would use
>> Canonical Storm. It even has a branch for integreation with Twisted,
>> which uses separate thread to access database.
> Has anyone tested this branch? I'd love to see a smallish usage example.
>

I've been using the twisted-integration branch on a project at work.
So far I have used with sqlite (for unit tests) and MySQL and have not
found any issues.

Per Duncan's comments, the unit tests tests/twistorm.py are the best
place to look now to see how to use the API.  I've been working on
adding some unit tests and minor features + more tests as well, in
hopes that we can get this branch merged into storm proper.  A small
howto might be in order as well - some external documentation doesn't
hurt.

>
>>> Do I have restructure my code such that everything that depends on
>>> results from a database query is in a callback to a deferred object?
>>>
>> I believe it was Glyph Lefkowitz who once said, that he finds
>> asynchronous data access APIs extremely painful (and that's why Divmod
>> Axiom has synchronous API). I could not agree more; I think this is also
>> a concern for you, that's why you asked this question. Even if using SQL
>> with Twisted is not a problem, basing on my experience I would rather
>> suggest one of mentioned approaches, than anything else.
>
> AFAIK some of the db interfaces (at least MysqlDB) will not block other
> threads when running in one so it makes a lot of sense to throw those
> operations into a separate thread.
>
> I usually try to separate out the code doing db operations into threads
> to keep the application responsive - instead of just the call to the db
> operation. This makes for easier coding but with more code in the thread.
>

twisted-integration uses threads of course - most importantly it
couples a single thread to a store (StoreThread), which is the only
safe way I know of to do transactions.