Monday, January 30, 2012

Re: Routing to database based on user

I forgot one thing: be cautious with transactions. The transaction
middleware will start a transaction only on 'default' alias. So, you
will probably need to use the transactions.commint_on_success /
commit_manually with the using parameter to make this work reliably.
Or write your own transaction middleware. If you need multidb-spanning
transactions you are in trouble. Or, at least if you need two phase

- Anssi

On Jan 31, 12:45 am, akaariai <> wrote:
> On Jan 31, 12:01 am, Tom Eastman <> wrote:
> > Hey guys,
> > I'm writing a django project that will require me to route queries to
> > certain large databases based on who the logged in user is.
> > So all the tables for django.contrib.auth and session and stuff will be
> > in the 'central' database, as well as a table that maps users to which
> > database they need to use for the main app.
> > Can you help me come up with a way of routing database queries this way
> > using a Django database router?
> > At the start of the view could I take the logged in user from
> > request.user or wherever it is, and some how provide that variable to my
> > database router for the rest of the request?
> > All suggestions welcome.
> I assume that settings.DATABASES have all the needed per-user
> databases defined.
> I think the best solution forward is to use threading.local to store
> the request.user. So, something like this should work:
>   - have a middleware that stores the request.user in threading.local
>   - database routers just fetch the request.user from the
> threading.local storage.
> You could also play with the connections dictionary, something like
> this should work in 1.4:
> Have a special database alias 'per_user_db'. Assuming alias 'thedb2'
> is the correct database to use for the user, you would then in a
> middleware do this:
> django.db.connections['per_user_db'] = django.db.connections['thedb2']
> This should work in the upcoming 1.4, but in 1.3 you will get problems
> due to threading.
> Now, you will just always route your queries to 'per_user_db'.
> For example:
> SomeModel.objects.create(field1=val1, ...)
> connections['default'] = connections['other']
> print SomeModel.objects.all()
> will print an empty list, but if you comment out the assignment, you
> will see the just created object in the DB.
> As said, the above should work correctly in multithreaded environment
> only in the upcoming 1.4. In 1.3 the connections assignment will be
> global, and you will get weird errors! Note that I haven't actually
> tested this in multithreaded environment.
>  - Anssi

You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to
To unsubscribe from this group, send email to
For more options, visit this group at

No comments:

Post a Comment