Building CRUD Applications with Django and PostgreSQL

When working with Django, there are a lot of database options you can choose. One of the best choices is PostgreSQL which is one of the officially supported databases. The Django ORM (Object-Relational Mapper) seamlessly integrates with PostgreSQL, allowing you to define database models using Python classes and perform database operations without writing raw SQL queries. Django is also able to support PostgreSQL-specific features directly through the models.

In this article, you will learn how to use PostgreSQL as the database for your Django applications by setting it up for common database operations such as creating, modifying, deleting, and searching data. In the process, you'll create a recipe application for managing food recipes in the browser.

Let's get started!

Prerequisites

To follow along with this tutorial, you only need basic familiarity with Python and Django.

Step 1 — Installing Python

If you're using Ubuntu, Python should already be installed. You can confirm this using the following command:

python3 --version

Python 3.10.6

If Python is not installed, you can install it by executing the following command:

sudo apt update && sudo apt install python3 python3-virtualenv

Please see the Python documentation for installation options if you're using a different operating system.

Once Python is installed successfully, the next step is for you to install PostgreSQL.

Step 2 — Installing PostgreSQL

Multiple download options for installing PostgreSQL are available on the PostgreSQL download page. If you're on Ubuntu, you may install PostgreSQL through the command below:

sudo apt install postgresql postgresql-contrib

The postgresql-contrib package adds a collection of useful modules to your PostgreSQL installation such as pgcrypto for generating hash values, uuid-ossp for generating Universally Unique Identifiers (UUIDs), and more.

Once the installation completes, you can check the version that was installed as follows:

psql --version

psql (PostgreSQL) 14.8 (Ubuntu 14.8-0ubuntu0.22.04.1)

You also need to ensure that the PostgreSQL server is up and running on your machine. Execute the command below to find out:

sudo systemctl status postgresql

● postgresql.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: disabled)
    Drop-In: /usr/lib/systemd/system/service.d
             └─10-timeout-abort.conf
     Active: active (running) since Tue 2023-07-11 20:23:23 CAT; 5 days ago
   Main PID: 5668 (postmaster)
      Tasks: 9 (limit: 38114)
     Memory: 20.0M

If the service is not started, then run the command below to start it and ensure that the status command above displays the service as "active".

sudo systemctl start postgresql

Once PostgreSQL is installed and running, you may proceed with setting up the database for your Django application next.

Step 3 — Setting up a PostgreSQL database

Before your Django application can persist data in PostgreSQL, you must create a user and password combination, as well as a database for your project. Without these details, you'll be unable to use PostgreSQL as a data store for your application.

We will perform these actions using psql, a command-line client for working with PostgreSQL. The first step is to initiate an interactive session as the default postgres user through the command below:

sudo -u postgres psql

Once you're in the psql interface, you can run SQL commands to create a new user and a database. Go ahead and create a new user/password combo with the following query:

CREATE USER <project_user> WITH PASSWORD '<secure_password>';

Before running the query, ensure to replace the <project_user> and <secure_password> placeholders above with your preferred values. You will see the following output if the query succeeds:

CREATE ROLE

Next, create a new database called recipes using the following query:

CREATE DATABASE recipes OWNER <project_user>;

CREATE DATABASE

In this case, the recipes database is created and the ownership of the database is assigned to the <project_user> created earlier.

At this point, you've created a user for your PostgreSQL database and assigned a password to it. You've also created a database called recipes which is owned by the aforementioned user.

Before you can log into psql using the new user, you must edit your pg_hba.conf file and change peer authentication to md5. This change is necessary because peer requires that the name of the PostgreSQL user and the name of the OS user must be the same.

Find out the location of your pg_hba.conf file by executing the following query in psql:

SHOW hba_file;

            hba_file
-------------------------------------
 /etc/postgresql/14/main/pg_hba.conf

After taking note of the file path, you may now quit the psql interface by typing \q in the prompt. You will be returned to your regular command prompt.

Next, open the pg_hba.conf file using your favorite text editor:

sudo nano /etc/postgresql/14/main/pg_hba.conf

Search for the following line in the file:

local   all             all                                     peer

And change it to:

local   all             all                                     md5

Save and close the file, then restart the PostgreSQL server:

sudo systemctl restart postgresql

You can now login to the psql interface using your new PostgreSQL user:

psql -U <project_user> -W -d recipes

You will be prompted to enter your password. If you enter it correctly, you should be successfully logged into psql as <project_user> and connected to the recipes database. Henceforth, you can interact with your PostgreSQL database using SQL commands within the psql environment.

Before proceeding to the next step, let's ensure that Django's specified optimizations for PostgreSQL are applied correctly so that query performance is not hampered.

Execute the following statements to set the parameters for the database user used by your Django project:

ALTER ROLE <project_user> SET client_encoding TO 'UTF8';
ALTER ROLE <project_user> SET default_transaction_isolation TO 'read committed';
ALTER ROLE <project_user> SET timezone TO 'UTC';

You should see the following output for each one:

ALTER ROLE

At this point, you're now ready to proceed with setting up your Django application and configuring it to interact with your PostgreSQL database.

Step 4 — Setting up your Django project

In this step, you will create a new Django application and connect it to your PostgreSQL database. Setting up the application involves installing Django and Psycopg2 (a Python library for interfacing with PostgreSQL), then configuring the Django to use PostgreSQL as its database backend.

Let's start by installing Django and Pyscopg2 first. The installation will be performed in a Python virtual environment to isolate your dependencies from other Python libraries already installed on your computer.

To create a virtual environment and activate it, open your terminal and run the following command:

virtualenv env && source env/bin/activate

Next, install Django and Pyscopg2 using pip:

pip install Django psycopg2-binary

Once both packages are installed, create a new Django project called recipe_project using the command below:

django-admin startproject recipe_project

The effect of running this command is that a new directory called recipe_project will be created in your current working directory. This directory contains the basic structure and files required for a Django project, including settings, URLs, and other necessary files.

Change into the recipe_project directory as follows:

cd recipe_project

Connecting to the database

The next step is to configure the database settings for your project so that you can connect to the recipes database created earlier. Instead of hard coding your database credentials in your application settings, create a .env file at the project root and add the following lines to it:

DB_ENGINE=django.db.backends.postgresql
DB_NAME=recipes
DB_USER=<project_user>
DB_PASSWORD=<secure_password>
DB_HOST=localhost
DB_PORT=5432

The above key/value pairs are the database credentials required by Django applications when using PostgreSQL. Next, you'll need to load these values from the .env file when your Django application is starting up. You can achieve this through the python-decouple package.

Return to your terminal and install this package with pip:

pip install python-decouple

Afterwards, open the recipe_project/settings.py file as follows:

code recipe_project/settings.py

Import the python-decouple package near the top of the file by adding the following highlighted line:

. . .
from pathlib import Path
from decouple import config

Locate the DATABASES section in the settings.py file, and update it as shown below, then save and close the file:

. . .
DATABASES = {
    'default': {
        'ENGINE': config('DB_ENGINE'),
        'NAME': config('DB_NAME'),
        'USER': config('DB_USER'),
        'PASSWORD': config('DB_PASSWORD'),
        'HOST': config('DB_HOST'),
        'PORT': config('DB_PORT'),
    }
}
. . .

By using the .env file and the python-decouple package, you can store sensitive information like database credentials outside of your codebase. The config() function from python-decouple retrieves the values from the .env file, allowing you to access them in your Django settings without compromising security.

At this stage, you are ready to test your Django application's connection to the database. You can do this by applying the default Django migrations which will create the tables required for Django's user authentication model:

python manage.py migrate

If everything works properly, you should see something like the output below:

Operations to perform:
Apply all migrations: admin, auth, contenttypes, sessions
Running migrations:
Applying content types.0001_initial... OK
Applying auth.0001_initial... OK
Applying admin.0001_initial... OK
Applying admin.0003_logentry_add_action_flag_choices... OK
Applying contenttypes.0002_remove_content_type_name... OK
Applying auth.0002_alter_permission_name_max_length... OK
Applying auth.0003_alter_user_email_max_length... OK
. . .

You can check if the tables were created in the recipes database by logging into psql as before:

psql -U <project_user> -W -d recipes

Once you're logged in, use the command below to view the tables in the database:

\dt

You should observe the following output confirming that your Django application is able to connect and interact with your PostgreSQL database:

                    List of relations
 Schema |            Name            | Type  |    Owner
--------+----------------------------+-------+-------------
 public | auth_group                 | table | projectuser
 public | auth_group_permissions     | table | projectuser
 public | auth_permission            | table | projectuser
 public | auth_user                  | table | projectuser
 public | auth_user_groups           | table | projectuser
 public | auth_user_user_permissions | table | projectuser
 public | django_admin_log           | table | projectuser
 public | django_content_type        | table | projectuser
 public | django_migrations          | table | projectuser
 public | django_session             | table | projectuser

In the next section, you will start building the recipe application by creating the necessary models.

Step 5 — Creating the Django application models

In Django, models represent the structure and behavior of your data. They are Python classes that define the tables and fields of a database and provide a convenient way to interact with the database using Python code. In this section, we will start building a recipes application that can create, read, update, and delete food recipes.

Before creating the model to handle the recipe data, you need to first create a Django application inside the project. This helps with organizing the project by separating different functionalities or components into separate applications.

To do this, open a new terminal and navigate to the recipe_project directory, then run the following command to create a new Django app called recipe:

python manage.py startapp recipe

Next, open the project/settings.py file, and add the newly created app to the INSTALLED_APPS list as shown below:

. . .
INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'recipe',
]
. . .

Once registered, you can now create the model for a recipe by editing the recipe/models.py file using the code below. This model defines fields for the recipe name, ingredients, and instructions:

from django.db import models

class Recipe(models.Model):
    name = models.CharField(max_length=200)
    ingredients = models.TextField()
    instructions = models.TextField()

    def __str__(self):
        return self.name

In the above code, the Recipe model has three fields: name, ingredients, and instructions. The name field is a CharField type with a maximum length of 200 characters, while ingredients and instructions are TextField. The CharField type is used for shorter text fields with a maximum length, while TextField is suitable for longer text content without predefined limits.

After defining your models, run the following commands to create the necessary database tables and apply the initial migration for the recipes app:

python manage.py makemigrations recipe

The commands above will generate the migration files which should be named 0001_inititial.py:

# Generated by Django 4.2.1 on 2023-06-26 15:03

from django.db import migrations, models

class Migration(migrations.Migration):

    initial = True

    dependencies = [
    ]

    operations = [
        migrations.CreateModel(
            name='Recipe',
            fields=[
                ('id', models.BigAutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('name', models.CharField(max_length=200)),
                ('ingredients', models.TextField()),
                ('instructions', models.TextField()),
            ],
        ),
    ]

You can now apply the migrations to create the corresponding tables in the database.

python manage.py migrate

You should observe that the migrations all ran successfully:

Operations to perform:
  Apply all migrations: admin, auth, contenttypes, recipe, sessions
Running migrations:
  Applying recipe.0001_initial... OK

You can also check if the table was created through the psql interface. Connect to the recipes database as before:

psql -U <project_user> -W -d recipes

Then execute the \dt command

\dt

You should observe a new recipe_recipe table in the output:

. . .
public | recipe_recipe              | table | <project_user>

Note that these migration steps must always be repeated after a model is created or updated so that the changes are correctly propagated to the database. This keeps your application's database schema in sync with your Django models and allows for seamless data manipulation and retrieval.

Now that you've created the Recipe model, let's proceed to the next where you'll implement the functionality to add recipes to the database.

Step 6 — Adding recipes

In this section, you will handle recipe creation by setting up a form where users are able to input the recipe title, list of ingredients, and instructions. Upon submission, the recipe will be saved to the database.

Let's start by creating the form to handle recipe creation in a new recipe/forms.py file:

from django import forms
from .models import Recipe

class RecipeForm(forms.ModelForm):
    class Meta:
        model = Recipe
        fields = ['name', 'ingredients', 'instructions']

The RecipeForm class inherits from Django's ModelForm class which indicates that the form is based on a model. The model = Recipe line specifies the model that the form is based on, while fields specifies the fields from the Recipe model that should be included in the form.

Next, you will create a new view for processing the request to add a new recipe to the database. Open the recipe/views.py file and paste in the following code:

from django.shortcuts import render, redirect
from .forms import RecipeForm
from .models import Recipe

def add_recipe(request):
    if request.method == 'POST':
        form = RecipeForm(request.POST)
        if form.is_valid():
            form.save()
            return redirect('list_recipes')
    else:
        form = RecipeForm()
    return render(request, 'recipe/add_recipe.html', {'form': form})

The add_recipe function handles the addition of a recipe. If a POST request is received, a new form instance is created with the submitted data. The form instance is then validated and saved to the database, and then the user is redirected to a page that lists all the recipes (this page will be created in the next section). If the request method is not POST, an empty form is initialized and rendered using the recipe/add_recipe.html template (which you'll create shortly).

Next, set up an endpoint for the view you just created. Open the project/urls.py file, and add the highlighted lines below:

from django.contrib import admin
from django.urls import path
from recipe import views


urlpatterns = [
    path('admin/', admin.site.urls),
    path('recipes/add/', views.add_recipe, name='add_recipe'),
]

Finally, create the HTML file that will display the form. Django provides a convenient way to generate HTML dynamically through its templating system.

Go ahead and create the following two files: recipe/templates/recipe/base.html and recipe/templates/recipe/add_recipe.html:

code recipe/templates/recipe/base.html

Populate the file with the following code:

<!DOCTYPE html>
<html>
  <head>
    <title>Recipe Management System</title>
  </head>
  <body>
    <header>
      <h1>Recipe Management System</h1>
    </header>

    <main>
      {% block content %}
      <!-- This block will be overridden by content from other templates -->
      {% endblock %}
    </main>

    <footer></footer>
  </body>
</html>

Then do the same for add_recipe.html:

code recipe/templates/recipe/add_recipe.html

{% extends 'recipe/base.html' %}
{% block content %}
<h2>Add Recipe</h2>
<form method="POST">
  {% csrf_token %} {{ form.as_p }}
  <button type="submit">Add</button>
</form>
{% endblock %}

The above code extends the base template, and includes the form for adding new recipes. The {{ form.as_p }} string instructs Django to render the recipe form as a paragraph. See the form API for more details.

At this stage, you can start the development server to test out your changes:

python manage.py runserver

It should launch the server on port 8000:

. . .
Django version 4.1.1, using settings 'project.settings'
Starting development server at http://127.0.0.1:8000/
Quit the server with CONTROL-C.

Visit http://localhost:8000/recipes/add in your browser to view the recipe form:

Try to add a new recipe and click the Add button to submit the form.

You will observe an error stating that the list_recipes view was not found since it has not been created yet. We will solve this issue in next section, but the newly added recipe should already reflect in the database.

You can confirm this through the psql interface as follows:

psql -U <project_user> -W -d recipes

TABLE recipe_recipe

Now that you've confirmed that adding recipes work as expected, let's move on to the next step where you'll retrieve the recipes from the database and display them in a list format.

Step 7 — Listing recipes

In this step, you will define a view that retrieves all the recipes from the database and passes them to a template for rendering. Start by updating the code in your views.py file with the following function:

. . .

def list_recipes(request):
    recipes = Recipe.objects.all() # retrieve all the recipes
    return render(request, 'recipe/list_recipes.html', {'recipes': recipes})

Next, create the route that will render the view you just created. You can do this by including the following in urlpatterns list in the urls.py file:

. . .

urlpatterns = [
    path("admin/", admin.site.urls),
    path("recipes/add/", views.add_recipe, name="add_recipe"),
    path('recipes/', views.list_recipes, name='list_recipes'),
]

Finally, create the recipe/templates/recipe/list_recipes.html file and populate it with the following code:

code recipe/templates/recipe/list_recipes.html

{% extends 'recipe/base.html' %}
{% block content %}
<h2>Recipe List</h2>
<ul>
  {% for recipe in recipes %}
  <li>{{ recipe.name }}</li>
  {% empty %}
  <li>No recipes found.</li>
  {% endfor %}
</ul>
{% endblock %}

Head back to your browser and add a new recipe as before. This time, you will be redirected to a page containing the list of all recipes in the database:

Step 8 — Updating recipes

In this section, you will create an edit form so that the recipe name, ingredients, and instructions can be improved and updated over time. Start by creating a update_recipe handler in your views.py file with the following code:

. . .
def update_recipe(request, recipe_id):
    recipe = Recipe.objects.get(pk=recipe_id)
    if request.method == 'POST':
        form = RecipeForm(request.POST, instance=recipe)
        if form.is_valid():
            form.save()
            return redirect('list_recipes')
    else:
        form = RecipeForm(instance=recipe)
    return render(request, 'recipe/update_recipe.html', {'form': form})

The update_recipe handler retrieves the recipe object based on the provided recipe_id. When a POST request is received, the submitted form data is validated and the recipe is updated accordingly. If the request method is not POST, the update form is rendered with the pre-filled data for the specified recipe_id.

Go ahead and add the endpoint to render the update_recipe view in your urls.py file:

urlpatterns = [
    path("admin/", admin.site.urls),
    path("recipes/add/", views.add_recipe, name="add_recipe"),
    path('recipes/', views.list_recipes, name='list_recipes'),
    path('recipes/update/<int:recipe_id>/', views.update_recipe, name='update_recipe'),
]

When accessing this URL, you must include the recipe_id so that the form is prefilled with the appropriate data for editing.

Finally, add the Django template that displays the form for updating a recipe:

{% extends 'recipe/base.html' %}
{% block content %}
<h2>Update Recipe</h2>
<form method="POST">
  {% csrf_token %} {{ form.as_p }}
  <button type="submit">Update</button>
</form>
{% endblock %}

You can now head over to http://localhost:8000/recipes/update/1 to edit the first recipe you added earlier. Once you edit the form, and click the Update button, it should update successfully and redirect you to the recipe list page.

Step 9 — Deleting recipes

In this section, you'll implement functionality to delete a recipe from the database. Deleting data can be approached in two ways: soft delete and hard delete.

Soft delete involves marking an item as deleted without actually removing it from the database. This approach can be useful when retaining the deleted data is important, such as for auditing purposes. Hard delete, on the other hand, permanently removes the item from the database.

Choosing between soft and hard delete depends on the specific requirements of your application. For example, a soft delete might be preferred if you need to maintain a history of deleted recipes, while a hard delete is more appropriate if permanent removal is desired.

At the moment, Django doesn't have a built-in solution for soft deleting so you may have to come up with a solution or research other solutions. In this section, we will implement hard deleting. You can do this by updating the contents in your views.py file with the following code:

from django.contrib import messages

. . .

def delete_recipe(request, recipe_id):
    recipe = Recipe.objects.get(pk=recipe_id)
    recipe.delete()
    messages.success(request, 'Recipe deleted successfully.')
    return redirect('list_recipes')

The delete_recipe function takes a request and a recipe_id parameter. It retrieves the recipe object with the given ID from the database using Recipe.objects.get(), deletes it using recipe.delete(), adds a success message using messages.success(), and finally redirects the user to the list_recipes URL.

Next, add the endpoint for deleting a recipe as follows:

urlpatterns = [
    path("admin/", admin.site.urls),
    path("recipes/add/", views.add_recipe, name="add_recipe"),
    path("recipes/", views.list_recipes, name="list_recipes"),
    path("recipes/update/<int:recipe_id>/", views.update_recipe, name="update_recipe"),
    path("recipes/delete/<int:recipe_id>/", views.delete_recipe, name="delete_recipe"),
]

Once the server restarts, you can delete the first recipe through the following endpoint: http://localhost:8000/recipes/delete/1

Step 10 — Searching or filtering recipes

As the number of recipes grows, it becomes essential to implement a search or filter functionality. Django offers several techniques to achieve this efficiently. One crucial aspect is indexing, which enhances the speed of search operations. By adding appropriate indexes to the database fields involved in searching or filtering, we can optimize query performance.

Indexing involves creating data structures, called indexes, that contain references to the locations of specific data values in a database table. When a database index is created on a specific field or set of fields, it organizes the data in a way that allows for faster retrieval based on those fields. This is achieved by creating a separate data structure that maps the indexed values to the corresponding database records. During a search or filter operation, the database engine utilizes the index to quickly locate the relevant data without having to scan the entire table.

Django provides convenient ways to define indexes on model fields, ensuring efficient data retrieval. To implement this in Django, we will update the Recipe model with the following code:

. . .
class Recipe(models.Model):
   ...

    class Meta:
        indexes = [models.Index(fields=['name', 'ingredients','instructions'])]

The code above creates the index on the specified fields when the corresponding database table is generated. You can activate this by running the migration commands:

python manage.py makemigrations recipe

python manage.py migrate

Now we can develop the view, form, template and URL that will handle the search. The view will retrieve the search query from the request's GET parameters and filters the Recipe model based on the name field, matching it in a case-insensitive manner with the search query. Finally, it renders the recipe/search_recipes.html template, passing the search results (recipes) and the query itself (query) as context variables.

. . .

def search_recipes(request):
    query = request.GET.get('q')
    recipes = Recipe.objects.filter(name__icontains=query)
    return render(request, 'recipe/search_recipes.html', {'recipes': recipes, 'query': query})

For the form, create a new file recipe/templates/recipe/search_form.html and paste the following code.

<form method="GET" action="{% url 'search_recipes' %}">
  <input type="text" name="q" placeholder="Search" />
  <button type="submit">Search</button>
</form>

Now add this to base.html so that it can be viewed on all pages in the application:

[recipe/templates/recipe/base.html]
. . .
<main>
   {% include 'recipe/search_form.html' %}

   {% block content %}
  <!-- This block will be overridden by content from other templates -->
   {% endblock %}
</main>
. . .

Next, you will create the template to display the search results. Create a new file at recipe/templates/recipe/search_recipes.html and paste the following code in it:

{% extends 'recipe/base.html' %}
{% block content %}
<h2>Search Results for "{{ query }}"</h2>
<ul>
  {% for recipe in recipes %}
  <li>{{ recipe.name }}</li>
  {% empty %}
  <li>No recipes found.</li>
  {% endfor %}
</ul>
{% endblock %}

The code above displays the heading "Search Results for" followed by the value of the query variable. The query variable is passed from the view to the template context and represents the search query entered by the user. It then loops through the recipes variable and generates an <li> element containing the recipe's name using {{ recipe.name }}. If there are no recipes found , it displays the "No recipes found" message.

To round off this section, add the endpoint for handling search queries. You can do this by including the following in urlpatterns list in the urls.py file:

. . .

urlpatterns = [
    path("admin/", admin.site.urls),
    path("recipes/add/", views.add_recipe, name="add_recipe"),
    path("recipes/", views.list_recipes, name="list_recipes"),
    path("recipes/update/<int:recipe_id>/", views.update_recipe, name="update_recipe"),
    path("recipes/delete/<int:recipe_id>/", views.delete_recipe, name="delete_recipe"),
    path("recipes/search/", views.search_recipes, name="search_recipes"),
]

When you view the list of recipes, you should see the search bar at the top and you can utilize the search feature from there.

Final thoughts

In this tutorial, you successfully created a recipe application and learned the fundamentals of using PostgreSQL with Django in the process. By following the steps outlined in this tutorial, you can easily set up your own Django project backed by PostgreSQL, and take advantage of its many features. The final code for this tutorial can be viewed in this GitHub repo.

Thanks for reading, and happy coding!

Article by

Muhammed Ali

Muhammed is a Software Developer with a passion for technical writing and open-source contribution. His areas of expertise are full-stack web development and DevOps.

Got an article suggestion? Let us know

Next article

15 Common Errors in Python and How to Fix Them

Dealing with errors is a significant challenge for developers. This article looks at some of the most common Python errors and discusses how to fix them

→

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.