A Comprehensive Guide to creating an API Endpoint with Django Rest Framework and Advanced Django Filters with PostgreSQL

This guide will give you hints on how to create an API endpoint with django rest framework. We are also going to use django filters package on top of DRF to apply sorting, search, filter by name like filtering capabilities for our API by URL parameters like:

http://localhost:8000/api/sales?region=Europe&groupby=country&date_from=01.01.2015&date_to=31.12.2015&ordering=-revenue

cool, right! in this URL, we filtered our dataset for this query:

Show revenue, earned in 2015 in Europe region, broken down by country and sorted by revenue in descending order.

and SQL statement for the query would be:

SELECT country, SUM(quantity), SUM(price), SUM(cost), SUM(revenue), SUM(profit)
FROM public.app_sales 
WHERE region='Europe'
AND date >= '01.01.2015'
AND date <= '12.31.2015'
GROUP BY country
ORDER BY SUM(revenue) DESC;

anyways, I am sure you got the point. We will design and build our API in order to query our database with complex filters.

I assume that you have a basic understanding of Django and preparing a python application virtual environment with python virtualenv module. So, I will skip these steps in this tutorial. Please refer one of my older posts with Django to setup a virtual environment.

I have this project in github but it is a dockerized version with docker-compose. For the sake of simplicity, I will cover this tutorial as it is inside of a virtual environment. If you want to have a look at the complete solution, check my github repository:

talented/Sales-Records-API

If you are still here, let’s continue. I assume that you installed Django and DRF, also install Django filters with the following command:

pip install django-filter

After that start a Django project inside your environment and run the commands below:

django-admin startproject apitest
django-admin startapp app

Open settings.py and update Installed_Apps as shown below:

INSTALLED_APPS = [
'django.contrib.admin',
'django.contrib.auth',
'django.contrib.contenttypes',
'django.contrib.sessions',
'django.contrib.messages',
'django.contrib.staticfiles',
'app',
'rest_framework',
'django_filters',
]

We are going to use PostgreSQL as our database system. You can install pgAdmin4 for administration of your postgreSQL database.

Again in settings.py, update as below:

DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql',
'HOST': '',
'NAME': 'name of your database',
'USER': 'your postgresql username',
'PASSWORD': 'your postgresql password',
'PORT': 5432,
}
}

We have to install psycopg2 python library in order to connect with postgreSQL. It is a postgresql adapter for python. I had some incompatibilities with django’s current version when I installed 2.8 version so I suggest you to install an older version not to have any problem. Run command below:

pip install psycopg2==2.7.0

Basic setups are done. Let’s create our database model. But before, I would like to give some information for the data source to be used.

We are going to use an open dataset for 2000 Sales records of 12 product types. First 10 records from the dataset is shown below:

You can find it in my github repository under pgdata folder as ‘2000_Sales_Records.csv’. We will populate our postgreSQL database after we created our model in Django and migrate it so our fields will be ready.

let’s start creating our Django model with these field names. Update models.py as below:

models.py

our model is ready, it is time to migrate our fields into postgreSQL database, a table with a name app_sales will be created along with the fields we defined. Run commands below:

python manage.py makemigrations
python manage.py migrate

Now our database is ready, but we need to import the data from ‘2000_Sales_Records.csv’ into postgreSQL. If you already downloaded it, put in under your main project folder apitest with the same level of manage.py and run sql command below inside of pgadmin4 (easier) or from the command line (by reaching out to postgreSQL db with psql command). I will go with pgadmin4.

After you start postgreSQL server, open pgadmin4 and reach out to “postgres” database as we used it as a name for our db. Open schemas and then Tables, right click to app_sales table and open Query Tool. Paste the following SQL command and click Execute (or F5):

COPY app_sales(region,country,ptype,channel,date,quantity,price,cost,revenue,profit)
FROM
 '{exact location of your project folder}/2000_Sales_Records.csv' DELIMITER ';' CSV HEADER;

you can find the exact location of your project folder by typing pwd from command line.

Now it is time to test with Django interactive console that you have successfully populated the db. Enter commands below to activate the shell:

python manage.py shell

Query your db as below inside the shell:

from app.models import Sales
Sales.objects.all().count()

If it returns a count other than 0, it means that your data is successfully imported.

We can continue with designing our application system structure. We are going to use DRF and we will deliver and API, so it is a good practice to collect all the related api files under the same folder. Create a folder called ‘api’ under ‘app’ folder and create the files inside that folder as shown below. I will explain each in detail when we are writing our code.

let’s start with serializers.py, open and update it with code below:

api/serializers.py

Even we used a ModelSerializer to create our SalesSerializer we need to define our models because when our api is filtered, some of the fields will not be visible so we need to set to default required value to False otherwise we get errors.

As you may notice, I have also added a calculated field called profit_percentage as a serialised field. We will need to extend our model to use this field. How can we achieve it?

Django managers come to the help when we need an extended queryset with aggregated and calculated fields. First add the following code inside of models.py

# models.py
from django.db import models
from app.managers import SalesQuerySet   # <-- Add This
.
.. 
...
objects = SalesQuerySet.as_manager()     # <-- Add This

and update managers.py as given below:

managers.py

We are able to kind of overwrite our objects attribute of our Sales model and we set SalesQuerySet class as our manager. Our models Manager’s default get_queryset method will return this class and let us call calculated_quantity method with our model’s object attribute.

We use django annotate filter in order to calculate the percentage of profit with profit and revenue fields. ExpressionWrapper helper class lets us output the result in a new field → DecimalField() in our case. we will call our extended queryset by Sales.objects.calculated_quantity()

let’s continue with views.py under api folder. Update it as given below:

api/views.py

We need to make an update for url routing so our api will be ready. Open urls.py inside apitest folder and update it as given below:

# /apitest/urls.py
from django.contrib import admin
from django.urls import path, include
urlpatterns = [
path('admin/', admin.site.urls),
path('', include('app.api.urls'))
]

and open urls.py inside of app/api folder that you created and update as given below:

# /app/api/urls.py
from django.urls import path
from .views import SalesListAPIView
urlpatterns = [
path('api/sales', SalesListAPIView.as_view())
]

we define the url path for our API endpoint and we tell that we will use SalesListAPIView as our view. Now, our application is half-ready but we can test it out in the browser. Run python manage.py runserver and open your browser and type localhost:8000/api/sales

You should be seeing DRF generic API list view screen with your data listed ascending. Let’s add some settings for DRF in settings.py. Add the given code to the end:

REST_FRAMEWORK = {
'DEFAULT_RENDERER_CLASSES': (
'rest_framework.renderers.JSONRenderer',
'rest_framework.renderers.BrowsableAPIRenderer',
),
'DEFAULT_PAGINATION_CLASS': 'rest_framework.pagination.LimitOffsetPagination',
'PAGE_SIZE': 20
}

Now, we have pagination as well. Refresh the page to see the changes. If you comment out rest_framework.renderers.BrowsableAPIRenderer and reload the page you will see that API is just available only in JSON format. You may use like that if you don’t need an interface. In most cases, you won’t need because your API endpoint will be used by a request from client side and required data format will be most likely JSON.

Applying Filters

So far we have worked to prepare our API endpoint to get a complete list of the sales records. The crucial thing is how to filter these records. Open filters.py under api folder and add the code below:

api/filters.py

We have defined a filter class as a Filterset and added all of the model fields and our pre-calculated profit field. Just to use ‘is_less_than_or_equal_to’ and ‘is_greater_than_or_equal_to’ filter lookups by using lte and gte respectively, we have defined a number filter for each field which has numeric values.

For filtering by date, we have defined method filters so we can parse the date first and apply django’s queryset filtering by lte and gte lookup expressions.

For more complex filters like groupby, we have also defined a custom method. We are not able to use numeric or date fields with this filter because it doesn’t make sense. We first get the given query params defined in self.request and use annotate function and get the total value of numeric fields in our database by using Sum expression. We use the same calculation for profit_percentage field but this time by using the total profit and revenue values.

So far, we have a fully functional API endpoint with some advanced filtering capabilities and you can even add more filters as I hope you have learned a lot from this post.

There will be issues like performance optimisation etc. to be taken care of when we are dealing with a large database. We should be implementing effective caching methodologies and try to optimise our queries and database for speed and lower memory, maintainability and scalability. So, This will be a topic of another post.

You can find the source code here on Github.

Thanks for reading! Please let me know if you have any questions and give me a Star on github if you find it useful for your projects!