2

In my database, I have a Customer table defined in my database that all other tables are foreign keyed on.

class Customer(models.Model):
    ...

class TableA(models.Model):
    Customer = models.ForeignKey(Customer)
    ...

class TableB(models.Model):
    Customer = models.ForeignKey(Customer)
    ...

I'm trying to implement a database router that determines the database to connect to based on the primary key of the Customer table. For instance, ids in the range 1 - 100 will connect to Database A, ids in the range 101 - 200 will connect to Database B.

I've read through the Django documentation on routers but I'm unsure if what I'm asking is possible. Specifically, the methods db_for_read(model, **hints) and db_for_write(model, **hints) work on the type of the object. This is useless for me as I need routing to be based on the contents of the instance of the object. The documentation further states that the only **hints provided at this moment are an instance object where applicable and in some cases no instance is provided at all. This doesn't inspire me with confidence as it does not explicitly state the cases when no instance is provided.

I'm essentially attempting to implement application level sharding of the database. Is this possible in Django?

CadentOrange
  • 3,263
  • 1
  • 34
  • 52
  • @ChrisWesseling Nothing. I have read the documentation and am trying to determine whether what I'm attempting is possible. I've updated the question to elaborate on my intended approach and what causes me worry. – CadentOrange Mar 12 '14 at 21:41

1 Answers1

2

Solve Chicken and egg

You'll have to solve the chicken and egg problem when saving a new Customer. You have to save to get an id, but you have to know the id to know where to save.

You can solve that by saving all Customers in DatabaseA first and then check the id and save it in the target db too. See Django multidb: write to multiple databases. If you do it consequently, you won't run into these problems. But make sure to pay attention to deleting Customers.

Then route using **hints

The routing problem that's left is pretty straight forward if an instance is in the hints. Either it is a Customer and you'll return 'DatabaseA' or it has a customer and you'll decide on its customer_id or customer.id.

Try and remember, there is no spoon.

When there is no instance in the hints, but it is a model from your app, raise an error, so you can change the code that created the Queryset. You should always provide hints, when they aren't added automatically.

What will really bake your cookie

If for most queries you have a know Customer, this is ok. But think about queries like TableA.objects.filter(customer__name__startswith='foo')

Community
  • 1
  • 1
Chris Wesseling
  • 6,226
  • 2
  • 36
  • 72
  • This looks very good and promising. I'll give that a go and see what I end up with. – CadentOrange Mar 13 '14 at 07:37
  • If you're sharding on Customer, and everything has a Customer. Why not make a settings-file and SITE_ID per customer? And perhaps even remove Customer from the app? – Chris Wesseling Mar 13 '14 at 11:55
  • While I'm sharding on Customer, I don't want each and every customer to be in their own shard. Many customers share the same shard and only the large ones get allocated to their own shard. We're currently all on one database, but we'll be sharding in the next 12 months. I've managed to get routing working based on your post. Thanks again. – CadentOrange Mar 17 '14 at 08:19