Basic API using Django REST and PostgreSQL

This will be a basic project following the tutorial on https://dev.to/jkaylight/django-rest-framework-with-postgresql-a-crud-tutorial-1l34. The end result is an API that has CRUD capabilities. The main purpose of this project is just the pedagogical benefits for myself of documenting my learning progress regarding these frameworks.

Contents

• Contents
• REST API
• CRUD Operations
• Installing PostgreSQL on Arch Linux
• Creating a Database
• Error I got when typing psql
• Creating a Database User
• Installing Django and Setting up a Django Project
• Setting up Django Database Configuration
• Creating a Django app
• Error I got during migration
• Create a REST API with Django Rest Framework
• Testing it out locally

REST API

Whereas an API is just a general term for how one abstraction of code communicates with another, a REST API is an API that does this through HTTP requests.

CRUD Operations

CRUD stands for four basic operations used in relational database systems.

• CREATE
• READ
• UPDATE
• DELETE

Installing PostgreSQL on Arch Linux

Since I’m using Arch linux I’ll use the pacman command.

pacman -S postgresql

Creating a Database

By default there is a user called ‘postgres’ so we’ll change to that.

sudo su - postgres

By typing

psql

we get an interactive terminal for PostGreSQL queries.

So now we can create a database for our project.

CREATE DATABASE mydb;

Error I got when typing psql

I got the error

psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: No such file or directory

Reading https://wiki.archlinux.org/title/PostgreSQL it mentions that the database cluster must be first initialized which can be done by running

initdb -D /var/lib/postgres/data

After that we can start and enable the systemd service

systemctl enable postgresql.service
systemctl start postgresql.service

Now if we switch to the postgres user and type psql it should work.

Creating a Database User

We will create the database user ‘myuser’ with the password ‘password’ (of course for a serious project you would want the password to be strong and not something like ‘password’).

CREATE USER myuser WITH PASSWORD 'password';

Then we will grant access rights to myuser:

GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

Then we can exit shell session and leave the interactive terminal with

\q
exit

Installing Django and Setting up a Django Project

Let us first setup a virtual environment (this repository is the environment so the following commands are executed in the directory above):

python -m venv django-rest-postgresql-basic-project
cd django-rest-postgresql-basic-project
source ./bin/activate

Now we’ll install three packages

• Django: A web framework
• Django Rest Framework: a toolkit for creating RESTful APIs with Django
• psycopg2: PostgreSQL package that connects our app to PostgreSQL

pip install django djangorestframework psycopg2

Now let’s create a django project with

django-admin startproject project

To add the REST framework to the project we add the string ‘rest_framework” to the list variable INSTALLED_APPS in settings.py.

Setting up Django Database Configuration

By default the project is setup for sqlite3. We will change it to PostgreSQL with our created user by specifying the DATABASES variable in settings.py as

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'mydb',
        'USER': 'myuser',
        'PASSWORD': 'password',
        'HOST': 'localhost',
        'PORT': '',
    }
}

Creating a Django app

To create an app with django we cd into ./project/ and run

python manage.py startapp customer

After that we add this app by adding ‘customer’ to the list variable INSTALLED_APPS in settings.py.

In the ./project/customer/ folder therer are the files models.py and views.py. In the models.py we shall add the following class that inherits from Django’s Model class:

from django.db import models

class Customer(models.Model):
    name = models.CharField("Name", max_length=240)
    email = models.EmailField()
    created = models.DateField(auto_now_add=True)

    def __str__(self):
        return self.name

We can then create database columns with the fields of our models with the following code:

python manage.py makemigrations
python manage.py migrate

Error I got during migration

When I ran

python manage.py migrate

in the last step I got the error

django.db.migrations.exceptions.MigrationSchemaMissing: Unable to create the django_migrations table (permission denied for schema public
LINE 1: CREATE TABLE "django_migrations" ("id" bigint NOT NULL PRIMA...
                     ^
)

Reading netbox-community/netbox#11314 it seems to be that the previous step of granting permissions to myuser by typing

GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

in the PostgreSQL terminal was not enough, but could be solved by instead running

ALTER DATABASE mydb OWNER TO myuser;

After this

python manage.py migrate

ran succesfully.

Create a REST API with Django Rest Framework

For serialization we add the following code into the file serializers.py which will be located in the customer directory:

from rest_framework import serializers
from .models import Customer

class CustomerSerializer(serializers.ModelSerializer):

    class Meta:
        model = Customer 
        fields = ['pk', 'name', 'email', 'created']

We will extend the GenericAPIViews to create the following:

• CustomerCreate: to create a new customer,
• CustomerList: to list all the customers in the database,
• CustomerDetail: Checking a single customer,
• CustomerUpdate: for updating and
• CustomerDelete: for deleting.

Thus the following code is added to views.py inside the customer folder:

from django.shortcuts import render
from .models import Customer
from rest_framework import generics
from .serializers import CustomerSerializer


class CustomerCreate(generics.CreateAPIView):
    # API endpoint that allows creation of a new customer
    queryset = Customer.objects.all(),
    serializer_class = CustomerSerializer


class CustomerList(generics.ListAPIView):
    # API endpoint that allows customer to be viewed.
    queryset = Customer.objects.all()
    serializer_class = CustomerSerializer


class CustomerDetail(generics.RetrieveAPIView):
    # API endpoint that returns a single customer by pk.
    queryset = Customer.objects.all()
    serializer_class = CustomerSerializer


class CustomerUpdate(generics.RetrieveUpdateAPIView):
    # API endpoint that allows a customer record to be updated.
    queryset = Customer.objects.all()
    serializer_class = CustomerSerializer


class CustomerDelete(generics.RetrieveDestroyAPIView):
    # API endpoint that allows a customer record to be deleted.
    queryset = Customer.objects.all()
    serializer_class = CustomerSerializer

And the url patterns will be specified as thus in the urls.py for the customer app:

from django.urls import include, path
from .views import CustomerCreate, CustomerList, CustomerDetail, CustomerUpdate, CustomerDelete


urlpatterns = [
    path('create/', CustomerCreate.as_view(), name='create-customer'),
    path('', CustomerList.as_view()),
    path('<int:pk>/', CustomerDetail.as_view(), name='retrieve-customer'),
    path('update/<int:pk>/', CustomerUpdate.as_view(), name='update-customer'),
    path('delete/<int:pk>/', CustomerDelete.as_view(), name='delete-customer')
]

And then we point to the customer app in from the root with the following code in urls.py for the project app:

from django.contrib import admin
from django.urls import path, include #new

urlpatterns = [
    path('admin/', admin.site.urls),
    path('customer/', include('customer.urls')), #new
]

Testing it out locally

We can start the server with

python manage.py runserver

Then it runs locally on http://127.0.0.1:8000/customer/. Since we’re running on linux we can use cURL to interact with the API.

Let’s create a profile for “James Bond” (the primary key will start on 6 because I tested it earlier):

curl -i -X POST -H 'Content-Type: application/json' -d '{"name": "James Bond", "email": "james.bond@bondmail.com"}' http://127.0.0.1:8000/customer/create/

Output:

{"pk":6,"name":"James Bond","email":"james.bond@bondmail.com","created":"2023-03-19"}0

Let’s create another profile for “John Doe”:

curl -i -X POST -H 'Content-Type: application/json' -d '{"name": "John Doe", "email": "james.doe@doemail.com"}' http://127.0.0.1:8000/customer/create/

Output:

{"pk":7,"name":"John Doe","email":"james.doe@doemail.com","created":"2023-03-19"}0

Let’s now list all the customers:

curl -i -X GET http://127.0.0.1:8000/customer/

Output:

[{"pk":6,"name":"James Bond","email":"james.bond@bondmail.com","created":"2023-03-19"},{"pk":7,"name":"John Doe","email":"james.doe@doemail.com","created":"2023-03-19"}]0

Let’s delete the user with pk=6:

curl -i -X DELETE http://127.0.0.1:8000/customer/delete/6/

And let’s see how that affected the list:

curl -i -X GET http://127.0.0.1:8000/customer/

Output:

[{"pk":7,"name":"John Doe","email":"james.doe@doemail.com","created":"2023-03-19"}]0

Since we added views to these, it is also possible to interact with the API through the browser by going to the corresponding urls.