best practice for SqlAlchemy and webapps ?

[Jonathan Vanasco]

Mike blew my mind at the "intro to sqlalchemy" presentation this week.  I learned at least 5 things that I had missed.   I still can't believe that the connection itself is basically "lazy-loaded" and SqlAlchemy doesn't even connect to the DB until you do something.  I wrote a bunch of code to memoize connections ( duplicating this ) - expecting there to be some overhead per-request (even unused) like other libraries.  Thanks, Mike!

So this brings up my question , which I couldn't find in the docs or FAQ

Is there any best-practices you can recommend for using SqlAlchemy on web projects in general ?

For this purpose , l'll just define web projects as an application that has:

1. a startup phase

2. a unit-of-work phase that corresponds ( at most ) to a single page viewing (request/generation/whatever).  

right now, my implementation is gleaned from what other webapps are doing... but there doesn't seem to be anything official in the SqlAlchemy docs/FAQ saying what people *should* do.

# Application Startup

1 Create a global registry for engines ( ie: I have separate read & write connections for postgresql )

2 For each desired engine :

  * create a SqlAlchemy engine ( via `sqlalchemy.engine_from_config` )

  * save it to the registry:

    ** wrap it into an container object that has:

        1. the engine

        2. `sqlalchemy_orm.sessionmaker`

        3. `sqlalchemy_orm.scoped_session`

  * setup the table metadata for the engine

# Application PageView

1. [optional] grab the engine's `scoped_session` and do stuff

2. cleanup routine - call `scoped_session.close()` on every engine in the registry

# Concerns

This seems to work fine for 2 reasons ( all thanks to SqlAlchemy ):

1. SqlAlchemy is smart - the cleanup call to `scoped_session.close()` only does work when we actually used that session.  i can call close on 4 sessions, and we're never hitting the db

2. my call to `scoped_session.close()` in cleanup clears out the sessions for the new request. yay.

I kind of feel like this pattern works, but it "just barely works" and largely by luck.

The concerns that I had are:

1. Should I be creating a new `scoped_session` on every request ( or first usage per request ) ?

2. Should I be calling `scoped_session.close()` at the start of every request as well  [ `new` appears to mark the work as new, but I want to clear out the entire unit-of-work ] ?

3. Should there be anything in place to ensure that any work with a transaction via sqlalchemy's core would be `new` ( or would calls to `scoped_session.close` handle this as well ?  I'm not using any sqlalchemy core in page requests (though i do use it in maintenance scripts) -- this is purely theoretical 

A few other things popped in my head, but these seem to be the most pressing. 

[Michael Bayer]

On Jun 20, 2013, at 2:02 PM, Jonathan Vanasco <jvan...@gmail.com> wrote:

> Mike blew my mind at the "intro to sqlalchemy" presentation this week. I learned at least 5 things that I had missed. I still can't believe that the connection itself is basically "lazy-loaded" and SqlAlchemy doesn't even connect to the DB until you do something. I wrote a bunch of code to memoize connections ( duplicating this ) - expecting there to be some overhead per-request (even unused) like other libraries. Thanks, Mike!
>
> So this brings up my question , which I couldn't find in the docs or FAQ
>
> Is there any best-practices you can recommend for using SqlAlchemy on web projects in general ?

fairly recently i wrote up as much as I could come up with on this, which you can see first in the Session FAQ:

http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#session-frequently-asked-questions

and then regarding scoped_session in:

http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#using-thread-local-scope-with-web-applications

>
> # Application Startup
>
> 1 Create a global registry for engines ( ie: I have separate read & write connections for postgresql )
> 2 For each desired engine :
> * create a SqlAlchemy engine ( via `sqlalchemy.engine_from_config` )
> * save it to the registry:
> ** wrap it into an container object that has:
> 1. the engine
> 2. `sqlalchemy_orm.sessionmaker`
> 3. `sqlalchemy_orm.scoped_session`
> * setup the table metadata for the engine

I don't generally bother trying to make a "container" for all these things, usually they're defined in some module like "myapp.meta". A module in Python is a namespaced object like any other, so there's no need for a MyApplication kind of class. Everyone seems to make those. Also the "sessionmaker" is an element of the scoped_session so I don't have it as it's own name; if I really want to make a new Session that isn't part of the usual scoped context I'll say my_scoped_session.session_factory().

>
> # Application PageView
>
> 1. [optional] grab the engine's `scoped_session` and do stuff

I have the ScopedSession as the facade to the database - the Engine itself is an element of the session and similarly I don't refer to it anywhere once it's been set up and assigned to a session. If I want it, I say my_scoped_session.bind. If I'm binding multiple engines to a single session maybe I'd have a lookup for those cases where i need it.

My current app has two databases that I refer to individually, so I have MySessionOne and MySessionTwo (better names in the real app).

> 2. cleanup routine - call `scoped_session.close()` on every engine in the registry

yes. Though I'll use remove() so that the current Session is discarded entirely. I use patterns where I attach extra things to a Session as the request proceeds.

>
> 1. Should I be creating a new `scoped_session` on every request ( or first usage per request ) ?

Just use the scoped_session as needed there's no setup to be done, the close() beforehand makes sure its clean for the next request.

> 2. Should I be calling `scoped_session.close()` at the start of every request as well

no need, sessions should always be clean when a request ends, that's when that thread/process/wahtever is about to go dormant and you don't want any garbage lying around.

> 3. Should there be anything in place to ensure that any work with a transaction via sqlalchemy's core would be `new` ( or would calls to `scoped_session.close` handle this as well ? I'm not using any sqlalchemy core in page requests (though i do use it in maintenance scripts) -- this is purely theoretical

Core API should be through Session.execute() in any case so the Session would have you covered.

[Jonathan Vanasco]

On Friday, June 21, 2013 2:23:54 PM UTC-4, Michael Bayer wrote:

fairly recently i wrote up as much as I could come up with on this, which you can see first in the Session FAQ:
http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#session-frequently-asked-questions

and then regarding scoped_session in:
http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#using-thread-local-scope-with-web-applications

oh this is great thanks.

i didn't see that, and then started looking through the wiki's FAQ

   http://www.sqlalchemy.org/trac/wiki/FAQ 

I'd add a link to the wiki, but I don't have edit privs.

I don't generally bother trying to make a "container" for all these things, usually they're defined in some module like "myapp.meta".  A module in Python is a namespaced object like any other, so there's no need for a MyApplication kind of class.  Everyone seems to make those.     Also the "sessionmaker" is an element of the scoped_session so I don't have it as it's own name; if I really want to make a new Session that isn't part of the usual scoped context I'll say my_scoped_session.session_factory().

re:container  -- I built out my SqlAlchemy management for Pyramid as a 3rd party library.  I just pass in a link to the package with my models and a db connection string; it does the rest for me, examining all the objects in the model for applicability and mapping them, setting up the cleanups, etc.  It makes rapid prototyping something really easy.

 

I have the ScopedSession as the facade to the database - the Engine itself is an element of the session and similarly I don't refer to it anywhere once it's been set up and assigned to a session.  If I want it, I say my_scoped_session.bind.     If I'm binding multiple engines to a single session maybe I'd have a lookup for those cases where i need it.
My current app has two databases that I refer to individually, so I have MySessionOne and MySessionTwo (better names in the real app).

... 

yes.  Though I'll use remove() so that the current Session is discarded entirely.   I use patterns where I attach extra things to a Session as the request proceeds.

...

Just use the scoped_session as needed there's no setup to be done, the close() beforehand makes sure its clean for the next request.

...

no need, sessions should always be clean when a request ends, that's when that thread/process/wahtever is about to go dormant and you don't want any garbage lying around.

...

Core API should be through Session.execute() in any case so the Session would have you covered.

thanks for all this.  I'm going to go through all the docs you pointed me to, and make sure everything is set nicely. 

[Jonathan Vanasco]

Mike, thanks again.  I finally found time to integrate your recommendations.   https://github.com/jvanasco/pyramid_sqlassist