Opened 5 years ago

Closed 2 years ago

#14121 closed New feature (wontfix)

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

Reported by: sandy@… Owned by: nobody
Component: Database layer (models, ORM) Version: 1.2
Severity: Normal Keywords: router, hints
Cc: noah@… Triage Stage: Design decision needed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (7)

comment:1 Changed 5 years ago by russellm

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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.

comment:2 Changed 5 years ago by Alex

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.

comment:3 Changed 5 years ago by russellm

  • Triage Stage changed from Unreviewed to Design decision needed

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

comment:4 Changed 4 years ago by julien

  • Severity set to Normal
  • Type set to New feature

comment:5 Changed 3 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:6 Changed 3 years ago by aaugustin

  • Easy pickings unset

Change Easy pickings from NULL to False.

comment:7 Changed 2 years ago by aaugustin

  • Resolution set to wontfix
  • Status changed from new to closed

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

Note: See TracTickets for help on using tickets.
Back to Top