Provide object's primary key as a hint for database router method db_for_read() when filtering objects by primary key

[sandy@…]

Problem:

Setting up a custom database router with db_for_read() method to allow for sharding with MySQL. Need to be able to control what database an object is read from on a per-object basis.


Example Case:

I want to retrieve User objects from the database cluster as follows:

• If the username starts with a number, read from slave_db_1

• If the username starts with a letter, read from slave_db_2

Currently, the only way to achieve this behavior is to specify "using" on each condition, and per each call to the database.


Proposed Solution:


When filtering objects by primary key, pass the primary key to db_for_read() as a hint.


Example Code:

#View Code:
"""Assume username is pk for User model"""
users = User.objects.get(username="testuser")

#DB Router Code
class UserReadRouter(object):
    def db_for_write(self, model, **hints):
        return 'master'

    def db_for_read(self, model, **hints):
        """Assume pk of User object is a string"""
        if model.__name__ == "User" and 'object_pk' in hints:
	    if hints['object_pk'][0].isalpha():
                return 'slave_db_2'
            else:
                return 'slave_db_1'

The end result is that "testuser" is retrieved from slave_db_1.

[Russell Keith-Magee]

In principle, I have no objection to passing in more hint data to the router, and your use case is certainly compelling. However, I'm not sure what you're describing here is possible (in the general case, anyway).

User.objects.get(...) is literally a shortcut for User.objects.filter(...)[0]; so the arguments to get() can be any combination of filters that will result in the return of a single record. This could be the primary key, a non-primary key, a combination of fields that are unique together, or a field that isn't unique, but will only return a single result due to business logic. I've even seen get() used as a "check that there is exactly one record" check.

So -- no problem with the idea in principle, but it needs more elaboration.

[Alex Gaynor]

We discussed, before the multidb branch, what exact data a router should get. One of the suggestions was to pass the queryset itself, except for that data was in the query, which isn't a public API.

[Russell Keith-Magee]

The idea is sound, but needs more design work to make it viable for trunk.

[Aymeric Augustin]

Change UI/UX from NULL to False.

[Aymeric Augustin]

Change Easy pickings from NULL to False.

[Aymeric Augustin]

Even though the idea is sound, it appears that it cannot be implemented.