Multi-db: is database routing per request possible?

906 views
Skip to first unread message

johan de taeye

unread,
Jun 15, 2010, 7:12:53 AM6/15/10
to Django users
Hello,

In my application a number of databases are configured with identical
schemas.
From a dropdown box on the screen, the user selects the database he/
she wants to work with. The selected database is stored on the
cookie.
An object with the same primary key can be created in each schema by
the user.

The above means objects can't be routed to the correct database based
on their primary key.
Only by looking at the request I know what database is required.

From the documentation (and after digging a bit in the code) I think
the database router concept doesn't support this type of usage.
Is this possible somehow?
Is it feasible to pass the request as a hint to the router somehow?

All feedback appreciated...

Johan

Alexander Jeliuc

unread,
Jun 15, 2010, 8:15:31 AM6/15/10
to django...@googlegroups.com
I didn't work with multidb but I think it is possible anyway... try it using additional middleware... for example.. change dynamically db settings... etc 


--
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django...@googlegroups.com.
To unsubscribe from this group, send email to django-users...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.


Russell Keith-Magee

unread,
Jun 15, 2010, 8:17:55 AM6/15/10
to django...@googlegroups.com

You can't do this with a router; as you've noted, the router doesn't
have any information about the request in which it is being used.

However, you could do it by manually requesting your database
connection whenever you use the database. For example:

Author.objects.using('otherdb').filter(...)

will perform a query that is guaranteed to operate on the 'otherdb',
regardless of what the router says.

The parameter 'otherdb' is just a string, so it could be determined
using some logic based on the request. This will require that you pass
the database assignment information around your project -- but you
would have needed to do this anyway if you wanted to pass hints to the
router.

Yours,
Russ Magee %-)

johan de taeye

unread,
Jun 15, 2010, 9:11:47 AM6/15/10
to Django users

> You can't do this with a router; as you've noted, the router doesn't
> have any information about the request in which it is being used.
>
> However, you could do it by manually requesting your database
> connection whenever you use the database. For example:
>
> Author.objects.using('otherdb').filter(...)
>
> will perform a query that is guaranteed to operate on the 'otherdb',
> regardless of what the router says.

I was able to get this part working fine. Since most views are based
on a reuable generic one this was pretty easy to do anyway.

Getting ModelForms (or ModelAdmin) to work with foreign keys is a
different story: When the form/model is validated, the field value is
searched in the source table to verify the key exists. This lookup
uses the _default_manager (see code below), which isn't aware of the
request any more and always checks on the default database (unless I
can route it). As a result I always get a validation error on the
form.

Looks like I need a patched/smarter RelatedField that is aware of the
database to look in?

Regards,

Johan



Snippet from django/db/models/fields/related.py:
class ForeignKey(RelatedField, Field):
...
def validate(self, value, model_instance):
if self.rel.parent_link:
return
super(ForeignKey, self).validate(value, model_instance)
if value is None:
return

qs =
self.rel.to._default_manager.filter(**{self.rel.field_name:value})
#### ALWAYS CHECKS IN DEFAULT DATABASE
qs = qs.complex_filter(self.rel.limit_choices_to)
if not qs.exists():
raise
exceptions.ValidationError(self.error_messages['invalid'] % {
'model': self.rel.to._meta.verbose_name, 'pk': value})

Russell Keith-Magee

unread,
Jun 15, 2010, 8:25:15 PM6/15/10
to django...@googlegroups.com
On Tue, Jun 15, 2010 at 9:11 PM, johan de taeye
<johan.d...@gmail.com> wrote:
>
>> You can't do this with a router; as you've noted, the router doesn't
>> have any information about the request in which it is being used.
>>
>> However, you could do it by manually requesting your database
>> connection whenever you use the database. For example:
>>
>> Author.objects.using('otherdb').filter(...)
>>
>> will perform a query that is guaranteed to operate on the 'otherdb',
>> regardless of what the router says.
>
> I was able to get this part working fine.  Since most views are based
> on a reuable generic one this was pretty easy to do anyway.
>
> Getting ModelForms (or ModelAdmin) to work with foreign keys is a
> different story:  When the form/model is validated, the field value is
> searched in the source table to verify the key exists. This lookup
> uses the _default_manager (see code below), which isn't aware of the
> request any more and always checks on the default database (unless I
> can route it).  As a result I always get a validation error on the
> form.
>
> Looks like I need a patched/smarter RelatedField that is aware of the
> database to look in?

You're in somewhat uncharted territory here, so I can't give you a
simple prepared answer. However, you certainly appear to be on the
right track.

It's also possible that you've discovered an edge case bug -- looking
at the code, my gut tells me that the _default_manager call should be
forced onto the same database as model_instance - i.e., the query
should be:

db = router.db_for_read(self.rel.to, instance=model_instance)
qs = self.re.to._default_manager.using(db).filter(**self.rel.field_name:value})

or similar. I'd need to do more tests to confirm this, though. Feel
free to log a bug if you can reduce this to a test case that
demonstrates that this is indeed a bug.

Yours,
Russ Magee %-)

johan de taeye

unread,
Jun 16, 2010, 3:01:01 AM6/16/10
to Django users
Russ,

Your gut feeling confirms my suspicion.

Changing the lines to the following fixes my problem:
db = model_instance._state.db
if db is None:
db = router.db_for_write(model_instance.__class__,
instance=model_instance)
qs =
self.rel.to._default_manager.using(db).filter(**{self.rel.field_name:value})

The validation of many2many fields (further down in the same file) has
a similar issue and would need fixing too.

I'll file a bug on the topic.

I somehow feel a bit uncomfortable with this approach: the formfield
constructor accepts the "using" argument to direct the queries to a
database, but since the validation is model-based (not form-based)
that info is not used in validation. You rely on guess-work like the
above or on the fact that the routing logic is in sync with the
"using" argument you provided. This can't be DRY...

Regards,

Johan

On Jun 16, 2:25 am, Russell Keith-Magee <russ...@keith-magee.com>
wrote:
Reply all
Reply to author
Forward
0 new messages