Creating top-notch web applications requires knowing how to manage data efficiently. Two technologies that stand out in this regard are Django and PostgreSQL. Django is a high-level Python web framework known as “the framework for perfectionists with deadlines”. Pair it up with PostgreSQL, the boss of open-source databases, and you’ve got a power duo that takes app capabilities to the next level.

In this article, we will be going through how to perform basic CRUD operations in a Django application using PostgreSQL. We will explain why this is important in the financial sector. Get your seatbelts ready, let’s go for a ride!

Prerequisites

To follow along in this tutorial, you would need the following:

• A Django application
• PostgreSQL server
• A Windows operating system

What is CRUD?

CRUD is an acronym for Create, Read, Update, and Delete, representing the four basic functions of persistent storage in a database system. These operations are fundamental in manipulating data within an application. “Create” involves the action of adding new data records to the database, such as inserting a new user into a user database. “Read” refers to retrieving existing data from the database, allowing users to access and view information stored in the system, like displaying a list of all registered users. “Update” allows for modifying existing data in the database, enabling users to edit or change specific information, such as updating a user’s email address. Finally, “Delete” involves removing unwanted data records from the database, and effectively erasing them from the system, such as deleting a user account.

Installing PostgreSQL

Before we begin building the application, we would need to download and install a PostgreSQL server. Visit the official site to download one based on your operating system: https://www.postgresql.org/download/. During the installation process, you may be prompted to set a password for the default PostgreSQL user, “postgres”. Make sure to remember this password, as it will be needed later.

Creating a PostgreSQL Database

After installation, open the Command Line Interface (CLI) on your computer to access the postgres instance using the following command:

Now, you can create a database with the postgres user because every database must have a user attached to it:

Project Set-up

Create a Virtual Environment

Before starting your Django project, you should always create a virtual environment. A virtual environment isolates your project’s packages from your other system-wide packages. This helps prevent conflicts when you have multiple projects with different versions of the same package. On your command line, install and activate a virtual environment with the following commands:

Install Django and Create a Project

Install Django using pip — Python’s package manager:

Create a Django project using the django-admin command and navigate into it:

Create an app named core in your crud directory:

Install the core app in the settings.py file in your crud project directory:

Start The Development Server

Start the development server to make sure our installation was set up correctly. On the command line, start the server with the following command:

Once the server is up and running, open your browser and visit the url, http:127.0.0.1:8000. You should see a success web page similar to the one below:

Connecting To The Database

To connect Django to a PostgreSQL database, you need to configure the database settings in the settings.py file of your Django project. Here’s how you can do it:

1. Open the pyfile located in your Django project directory.
2. Find the DATABASESvariable, which is typically a dictionary containing configuration options for different databases.
3. Update the DATABASESsetting to configure PostgreSQL as the database backend. Below is an example configuration for connecting to a PostgreSQL database:

Replace NAME, USER, and PASSWORD with the actual name, user, and password of your PostgreSQL database, if you used different ones. You may also need to adjust the ‘HOST’ and ‘PORT’ settings if your PostgreSQL server is running on a different host or port.

If you run your server, you would see an error message that looks like, “Error loading psycopg2 or psycopg module”. This is because the psycopg2 module, which is a PostgreSQL adapter for Python is required if you’re using Django with a PostgreSQL database. To resolve this error, install the psycopg2 module in your virtual environment using the pip package manager:

Now, when you attempt to run your server, it should run successfully without any error.

Creating Database Models

Start by defining a Django model that represents the structure of the data you want to insert. This includes specifying fields and their corresponding data types. Create a User model which has a username and available_balance field. The available_balance field represents the balance of a particular user in our financial technology application.

Open the models.py file in the core directory and add the following model.

Make Database migrations

Migrations are Django’s way of propagating changes you make to your models (adding a field, deleting a model, etc.) into your database schema. They’re designed to be mostly automatic, but you’ll need to know when to make migrations, when to run them, and the common problems you might run into — Django’s documentation. To create a migration use the migrate command:

Creating Data

Creating data within the application involves adding new user records to the database. Let’s explore the process of inserting records into the User table using Django’s Object Relational Mapper (ORM),

Open the views.py file in the core app and add the following imports:

This imports necessary functions and classes from Django. render is used to render HTML templates, and User is the model we created to represent users in our application.

Next, create the view function create_user responsible for handling the creation of a new user record. Under this function, write the logic that creates a user record in the database::

This block of code checks if the request method is POST, indicating that the user has submitted a form to create a new user. It retrieves the username entered in the form using request.POST.get(). It then creates a new instance of the User model with the provided username.

The try block attempts to save the new user object to the database using the save() method. If the save operation is successful, it returns an HTTP response with a success message. If an exception occurs during the save operation (e.g., due to validation errors or database constraints), it catches the exception and returns an HTTP response with an error message. If the request method is not POST (e.g., GET), meaning the user is accessing the page to create a new user, this block renders the create_user.html template, which contains a form to input user details. This html file does not exist yet; we’ll soon create it.

Reading Data

Below is a Django view function that retrieves a user from the database:

In this code, the view function get_user retrieves a user from the database based on the provided username. Inside the function, we use a try-except block to handle the case where the user with the provided username does not exist. Inside the try block, we use the get() method of the User model to retrieve the user with the specified username. If the user is found, we render a template user_detail.html and pass the retrieved user object to the template context. If the user does not exist, we return an error message using HttpResponse.

Updating Data

Here’s a Django view function that retrieves a user from the database and allows for updating their available balance:

This view function user_detail retrieves a user from the database based on the provided username. It takes a request object and the username as parameters. It uses get_object_or_404 to retrieve the user object from the User model based on the provided username. If the user does not exist, it returns a 404 error.

Inside the function, it checks if the request method is POST, indicating that the form to update the user’s balance has been submitted. If the request method is POST, it retrieves the updated balance from the form and updates the user’s available_balance attribute accordingly. It then saves the changes to the database using the save() method.

Finally, it either redirects to a success page (/success/) or renders a template (user_detail.html) with the retrieved user object if the request method is not POST.

Handling Concurrent Database Requests

Ensuring data consistency and integrity is important, especially when dealing with concurrent data updates. Concurrent requests occur when multiple users or processes attempt to access or modify the same data simultaneously. This often happens during data updates, such as updating or deleting database records. Without proper strategies, such as database locking, concurrent updates can lead to data inconsistencies and integrity issues, especially in applications like financial technology applications where handling numbers are important. Let’s explore these strategies.

Using Database Locks With select_for_update method

 Django’s select_for_update method allows developers to lock selected database rows until the end of a “transaction”, ensuring exclusive access and preventing concurrent modifications. This helps maintain data consistency by controlling access to the selected rows, thereby avoiding race conditions.

For context, let’s look at the following example from a financial technology application:

Using the function above, a single user initiating a deposit to their account should encounter no issues. However, if two users attempt to simultaneously deposit funds to the same account and retrieve the recipient’s balance at precisely the same moment, a potential problem arises. Both transactions might add the deposit amount to the account’s previous balance concurrently, resulting in the balance being updated based on outdated information. This scenario could introduce inconsistencies in the balance updates, affecting the integrity of the user’s financial records. This brings us to the concept of database locks.

Database locks are mechanisms used to control access to data in a database and ensure data consistency, particularly in multi-user environments where multiple transactions may access or modify the same data concurrently. A lock prevents other transactions from accessing or modifying the locked resource until the lock is released.

To use database locks, we can mark the function as a transaction, then use the select_for_update method to lock the row for update so that no other database request can access the database row until the current transaction is complete.

Let’s update the code appropriately:

In the updated code, when two users simultaneously attempt to update the balance of a single user, one database request will be queued, waiting for the other to complete before proceeding. Utilizing this strategy allows applications, particularly those in financial technology, to effectively manage concurrent database requests while upholding data consistency.

Deleting Data

In our application, removing records from the database should be done with caution to prevent data loss and maintain data integrity. Let’s look at how to safely delete a user from the database.

In your views.py file, add the following view:

This view function delete_user deletes a user record from the database based on the provided username. It uses get_object_or_404 to retrieve the user object from the User model based on the provided username. If the user does not exist, it returns a 404 error.

Inside the function, it checks if the request method is POST, indicating that the delete confirmation form has been submitted. If the request method is POST, it calls the delete() method on the user object to delete the record from the database.

Finally, it redirects to a success page or renders a template (delete_confirmation.html) with the retrieved user object to confirm the deletion.

Creating URLS Endpoints

URL patterns in Django serve as a mapping between the URLs requested by users and the view functions that handle those requests. They define the structure of the URLs used to access different parts of a web application and specify which view function should be called to generate the appropriate response for each URL.

In the core app directory create a urls.py file and add the following content:

The path() function is used to define URL patterns. The first argument is the URL pattern (e.g., ‘create/’, ‘<str:username>/’, ‘<str:username>/delete/’), which specifies the URL endpoint.

The second argument is the corresponding view function (e.g., create_user, user_detail, delete_user), which will be called when the URL is accessed.

The third argument, name, provides a unique identifier for the URL pattern, which can be used to refer to it in templates or other parts of the code.

Finally, we need to register these URL patterns in our application. To do so, open the urls.py file in the crud project directory and include this URL configurations file in the urls.py file using the include function:

Creating HTML templates

Templates in Django are like blueprints for how your web pages should look. They are HTML files that provide a framework for the overall layout and structure of your website. Let’s create the HTML templates we specified for the views we wrote earlier.

In your core app directory, create a folder called templates and create a create_user.html file with the contents below:

The {% csrf_token %} is a template tag that automatically generates and includes a CSRF token in your HTML forms. By including this token, you’re helping to protect your web application from CSRF attacks by ensuring that only requests originating from your site with the correct token are accepted.

Imagine you’re logging into your bank’s website. You enter your username and password and click the login button. Behind the scenes, your browser sends a request to the bank’s server to log you in. Now, imagine if a malicious website you visited at the same time also tried to send a request to your bank’s server, pretending to be you. If the bank’s server didn’t have any protection in place, it might mistakenly think the malicious request was coming from you and perform actions like transferring money without your knowledge.

This is where the CSRF token comes in. It’s like a secret code that your web application gives to your browser when you visit a page with a form. When you submit the form, your browser sends the token along with the request. The web application then checks if the token matches what it expected. If it does, the request is considered legitimate, and the action is performed. If not, the request is rejected.

This template also use the {% url %} template tag to generate URLs for the form actions based on their names. This ensures that the correct URLs are used even if the URL patterns change in the future.

In the same templates directory, create a User Detail template (user_detail.html) file with the contents below:

This line, <p>Username: {{ user.username }}</p>, displays the username of the user, with {{ user.username }} being a placeholder for the actual username retrieved from the user object in the view we wrote earlier.

The available balance of the user is also displayed with, {{ user.available_balance }}, being a placeholder for the actual available balance retrieved from the user object.

Finally, create the Delete Confirmation Template (delete_confirmation.html) in the templates directory with the following content:

Testing Our Application

Let’s start the development server to confirm that our application works as expected. Open your terminal, navigate to the crud project directory, and write the following command:

python manage.py runserver

On your browser open http://127.0.0.1:8000/create/. to create a new user using the form provided.

To retrieve the details of a user visit http://127.0.0.1:8000/1/ to view the details of the first user you just created.

To delete the user, visit http://127.0.0.1:8000/1/delete/ and click on the delete button.

Improving Our Application

While the application provides a solid foundation for building a CRUD application with Django and PostgreSQL, there are several areas where it can be improved and features that can be added to enhance its functionality and user experience.

Implementing user authentication and authorization to secure the application and restrict access to certain functionalities based on user roles and permissions. This can be achieved using Django’s built-in authentication system or third-party packages like Django Allauth or Django REST Framework.

The endpoints could also be built as APIs (Application Programming Interface) to enable integration with external systems, third-party services, or mobile applications. Django REST Framework is a choice worth naming. It can be used to build a flexible and scalable API for interacting with the application’s data.

Conclusion

In conclusion, this article has provided a comprehensive guide to building a CRUD (Create, Read, Update, Delete) application with Django and PostgreSQL. By leveraging Django’s efficiency and ease of development and integrating it with PostgreSQL, one of the most advanced open-source relational database management systems, developers can create sophisticated applications that efficiently manage data operations.

Throughout the article, we have covered various aspects, starting from understanding the fundamental concepts of CRUD operations and their importance in database management. We have provided step-by-step instructions for setting up a PostgreSQL database, creating a Django project, configuring database settings, defining models, and performing database migrations.

Furthermore, we have explored the implementation of CRUD functionalities, including creating, reading, updating, and deleting data records, using Django’s Object Relational Mapper (ORM). We have demonstrated how to handle form submissions, retrieve and display data, update existing records, and delete records safely while maintaining data integrity.