Routing to database based on user

[Tom Eastman]

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.

Cheers!

Tom

[akaariai]

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

[akaariai]

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
commit...

- Anssi

[Tom Eastman]

On 31/01/12 11:45, akaariai wrote:
> On Jan 31, 12:01 am, Tom Eastman <t...@catalyst.net.nz> 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 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.

Thanks Anssi!

Here is my solution, I wonder if you could just tell me if you think
there's a major problem with it.

In simplistic terms, the goal is "whenever a model from wxdatabase is
accessed, route the query to the database specified in the user's
organization's field".

It consists of a piece of django middleware and a db_router.

I guess my main question is: am I using threading.local() correctly?

Cheers!

Tom

##################################
##################################
import threading

_local = threading.local()

class WXDatabaseMiddleware(object):
def process_request(self, request):
_local.wx_database_label = None

try:
profile = request.user.get_profile()
_local.wx_database_label = profile.organization.database.label
except:
## This exception needs to be logged!
pass

class WxRouter(object):
def _get_database_label(self):
return _local.wx_database_label

def read_and_write(self, model, **hints):
if model._meta.app_label == "wxdatabase":
return self._get_database_label()
else:
return None

db_for_read = read_and_write
db_for_write = read_and_write

###################################
###################################

[akaariai]

On Feb 3, 1:30 am, Tom Eastman <t...@catalyst.net.nz> wrote:
> Here is my solution, I wonder if you could just tell me if you think
> there's a major problem with it.
>
> In simplistic terms, the goal is "whenever a model from wxdatabase is
> accessed, route the query to the database specified in the user's
> organization's field".
>
> It consists of a piece of django middleware and a db_router.

I must warn you that I don't know too much about db routers.

> I guess my main question is: am I using threading.local() correctly?

To me it seems correct.

> class WXDatabaseMiddleware(object):
>     def process_request(self, request):
>         _local.wx_database_label = None
>
>         try:
>             profile = request.user.get_profile()
>             _local.wx_database_label = profile.organization.database.label
>         except:
>             ## This exception needs to be logged!
>             pass

I think here you should make sure you always also clear the
wx_database_label variable, so that it won't be left pointing to a
user's DB in any case. That is, you would need a finally block, and a
process_response method.

Also, I think you are going to generate some more queries than needed
by the user.profile.organization.database.label. If I guess correctly,
that is going to be 5 DB calls for each request. .select_related()
could be handy here...

>
> class WxRouter(object):
>     def _get_database_label(self):
>         return _local.wx_database_label
>
>     def read_and_write(self, model, **hints):
>         if model._meta.app_label == "wxdatabase":
>             return self._get_database_label()
>         else:
>             return None
>
>     db_for_read  = read_and_write
>     db_for_write = read_and_write

Can't comment about this.

- Anssi

[Wilkinson Tavares]

Hi Tom,

Did the solution Ansii suggested work?

Have you got some scalability problem with this solution?