Query Objects across Multiple Databases – Django Tutorial

Query Objects across Multiple Databases – Django Tutorial

Query Multiple Databases - Django Tutorial
Query Objects from Multiple Databases - Django Tutorial

Let’s say you want to reference information across multiple databases, using foreign key relationships. This blog post will cover how to do that and how to implement them into your views and template files.

For this example, I’m working through creating specific user profile pages referencing multiple databases. Including the User database, a custom Profile database, a user levels database, and another database for profile pictures.

 

Source Code for this tutorial can be found on my GitHub account Here.  

Let's start with the Django model.

Firstly, we have to create the information that we’re wanting to gather from the user and store. This blog won’t cover the gathering of the information, nor really the storing. But to reference the data we first have to have it within the database. For now we’ll assume that you already have it. 

So, find the created models that you want to reference. Since you’re on this page, I’m going to assume that you’re wanting to reference a model which is related to another by a foreign key. 

Query multiple databases Django - model

We’ll use the one I have above. The above model is one which creates a table called userlevel in my databases and relates it to a specific User – which is stored in a different database table. 

  • The important step that we need to cover here is using the related_name to name the field. 

On your model, create a related_name identifier – this will allow us to us a reverse lookup in the next few steps to allow much easier querying of data. 

Below is my full model code, if you’re interested. I’m using it on my personal site as different category levels for each user. 

Query Multiple Database - Models.py
Giving our View file something to Query with:

Next, we’ll want to write our view to query the database. With my specific example, I’m gathering a lot of different information across multiple databases to build profile pages for each user uniquely. 

To create custom profile pages, we’ll need to create a unique url file for each user. To do this, you’ll need to create a urls.py file which takes in a unique identifier in the url. I’ve done this in the code below using the username, which in my model is identified as a unique string. 

Multiple Database Query - views.py
Writing the views.py file

The views.py file will need to take the information and query the database for the information needed. However, something unique to this example, is that I would like a different page for the user accessing their own page, and a user accessing another user’s page. In short, I want the user to view other’s pages as a ‘public’ page and view their own with more permissions. 

I’ve done this with the following code:

Now how do we query across multiple databases?

We’ll begin querying the database for user information in the templates. Now, we get to see the magic of using related names back in our models. 

We can reference data through a reverse lookup using the data’s related name fields. An example of this is:

Multiple Database Query - html

and it’s as easy as that! You can pull any data from any database that is referenced through the foreign keys. This is useful for a variety of applications where you have a lot of data related to one thing spread across a variety of different tables. 

Just remember to not go crazy separating your tables and have valid reasoning for doing so. Sometimes there’s more value in a larger table than splitting information up. 

Leave a Reply

Your email address will not be published. Required fields are marked *