Word Count: ~1900 · Reading Time: 10 minutes

Databases and User Management with Python

How to connect your website to a real database and manage users and permissions professionally

Note to the reader: This article is completely self-contained, and you can apply everything in it without reading previous articles. However, if you haven’t built a website with Python yet, we recommend checking out our article: Converting Your Static HTML Website into a Dynamic One with Python and Flask.

So far, all the data we have dealt with in this series has been temporary—disappearing as soon as the script stops, or stored in simple CSV files. This is enough for experimentation and prototypes, but it is not sufficient for any real website you build for a client or yourself. A real website needs a permanent memory that remembers users, their requests, purchases, and comments, even after restarting the server a hundred times.

This permanent memory is called a Database. In this article from Zy Yazan Platform, we will learn how to connect our Python-built website to a real database, how to store and retrieve data using Django’s ORM engine, and finally, how to build a complete user system with login and permissions in just a few lines of code.

Why PostgreSQL Specifically?

When it comes to databases for serious web projects, a freelancer faces several choices: SQLite, MySQL, PostgreSQL, and others. Let’s clarify when to choose each one:

SQLite — A built-in database contained within a single file, excellent for local development and very small projects. Django uses it by default because it requires no installation. However, it is not suitable for projects used by more than one user at the exact same moment.

MySQL — Very common and supported by most hosting companies. A good choice, but it lacks some advanced features.

PostgreSQL — The most professional and complete database in the open-source world. It supports complex data, full-text search, advanced data types, and handles massive concurrent loads. Most major professional platforms run on it, which is why we choose it.

Practical advice: Develop your project locally using SQLite and deploy it to the production server with PostgreSQL. Django makes switching between databases very easy because it interacts with all of them using the exact same approach.

Installing PostgreSQL and Connecting It to Django

First, install PostgreSQL on your machine. If you are working on Windows or Mac, download the installer from the official website. On Linux:

sudo apt update
sudo apt install postgresql postgresql-contrib

Next, create a database and a user for your project:

# Access the PostgreSQL interface
sudo -u postgres psql

-- Create a database for the project
CREATE DATABASE myproject_db;

-- Create a user with a password
CREATE USER myproject_user WITH PASSWORD 'your_strong_password';

-- Grant full privileges
GRANT ALL PRIVILEGES ON DATABASE myproject_db TO myproject_user;

-- Exit
\q

Now install the connector library between Python and PostgreSQL:

pip install psycopg2-binary

And modify your Django settings file settings.py to connect to your database:

# mysite/settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'myproject_db',
        'USER': 'myproject_user',
        'PASSWORD': 'your_strong_password',
        'HOST': 'localhost',
        'PORT': '5432',
    }
}

Then run the initial migration command to create Django’s default tables:

python manage.py migrate

If you see the message “Operations to perform… Running migrations…” with no errors, the connection is working successfully.

Django ORM Engine: Talk to the Database with Python, Not SQL

The ORM engine, or Object Relational Mapper, is the beating heart of Django. Instead of writing complex SQL queries like SELECT * FROM projects WHERE client_id = 5, you write regular Python code, and Django automatically translates it into SQL. This means:

• Cleaner, easier-to-read code
• Automatic protection from dangerous SQL Injection vulnerabilities
• The ability to switch between databases without changing your code

Defining Data Models — The Core of Any Project

Let’s build a project management system for freelancers. Every Model represents a table in the database:

# mainapp/models.py
from django.db import models
from django.contrib.auth.models import User

class Client(models.Model):
    """Client model — every freelancer has a list of clients"""
    name = models.CharField(max_length=200, verbose_name="Client Name")
    email = models.EmailField(unique=True, verbose_name="Email Address")
    phone = models.CharField(max_length=20, blank=True, verbose_name="Phone Number")
    country = models.CharField(max_length=100, blank=True, verbose_name="Country")
    created_at = models.DateTimeField(auto_now_add=True)

    def __str__(self):
        return self.name

    class Meta:
        verbose_name = "Client"
        verbose_name_plural = "Clients"
        ordering = ['-created_at']


class Project(models.Model):
    """Project model — linked to a single client"""

    STATUS_CHOICES = [
        ('pending', 'Pending'),
        ('active', 'In Progress'),
        ('completed', 'Completed'),
        ('cancelled', 'Cancelled'),
    ]

    title = models.CharField(max_length=300, verbose_name="Project Title")
    client = models.ForeignKey(
        Client,
        on_delete=models.CASCADE,
        related_name='projects',
        verbose_name="Client"
    )
    description = models.TextField(verbose_name="Project Description")
    budget = models.DecimalField(
        max_digits=10, decimal_places=2,
        verbose_name="Budget in USD"
    )
    status = models.CharField(
        max_length=20,
        choices=STATUS_CHOICES,
        default='pending',
        verbose_name="Project Status"
    )
    deadline = models.DateField(null=True, blank=True, verbose_name="Deadline")
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    def __str__(self):
        return f"{self.title} — {self.client.name}"

    class Meta:
        verbose_name = "Project"
        verbose_name_plural = "Projects"
        ordering = ['-created_at']

Now create the database tables from these models:

python manage.py makemigrations
python manage.py migrate

Basic Database Operations: Create, Read, Update, and Delete

These four operations are commonly known as CRUD (Create, Read, Update, Delete), and they form the foundation of any web application. Here is how you perform them in Python using Django’s ORM:

Create — Adding a New Record

# Inside views.py or any Python file within your Django project
from .models import Client, Project

# Create a new client and save it to the database
new_client = Client.objects.create(
    name="Arab Tech Company",
    email="info@arabtech.com",
    country="Saudi Arabia"
)

# Create a project linked to this client
new_project = Project.objects.create(
    title="New Company Website",
    client=new_client,
    description="Design and development of a professional website",
    budget=2500.00,
    status='active'
)

Read — Retrieving Data

# Get all clients
all_clients = Client.objects.all()

# Get a single client by ID
client = Client.objects.get(id=1)

# Search and filter — active projects only
active_projects = Project.objects.filter(status='active')

# Multiple filters — projects for a specific client with a budget over $1000
big_projects = Project.objects.filter(
    client=client,
    budget__gt=1000
).order_by('-created_at')

# Get the first result only
latest_project = Project.objects.filter(
    client=client
).first()

Update — Modifying an Existing Record

# Update a single project
project = Project.objects.get(id=1)
project.status = 'completed'
project.save()

# Update multiple records at once
Project.objects.filter(status='pending').update(status='active')

Delete — Removing a Record

# Delete a single project
project = Project.objects.get(id=5)
project.delete()

# Delete all cancelled projects
Project.objects.filter(status='cancelled').delete()

Django’s Built-In User System: Complete Login Functionality in Minutes

This is where Django offers unparalleled value. Instead of building a login system from scratch—which takes days and requires attention to dozens of security details—Django provides it right out of the box.

Registering a New User

# mainapp/views.py
from django.shortcuts import render, redirect
from django.contrib.auth.forms import UserCreationForm
from django.contrib import messages

def register(request):
    if request.method == 'POST':
        form = UserCreationForm(request.POST)
        if form.is_valid():
            form.save()
            messages.success(request, 'Your account has been created successfully! You can log in now.')
            return redirect('login')
    else:
        form = UserCreationForm()

    return render(request, 'registration/register.html', {'form': form})

Logging In and Out — Django Handles It All

# mysite/urls.py
from django.urls import path, include
from mainapp import views

urlpatterns = [
    path('admin/', admin.site.urls),

    # Django provides built-in views for login, logout, and password resets
    path('accounts/', include('django.contrib.auth.urls')),

    # Our custom registration page
    path('accounts/register/', views.register, name='register'),

    path('', views.home, name='home'),
    path('dashboard/', views.dashboard, name='dashboard'),
]

Simply by adding django.contrib.auth.urls, you automatically get access to these ready-to-use pages:

Protecting Pages: Restricted Access for Authenticated Users Only

The most common use case for any user-based website is restricting specific pages to logged-in users only. In Django, a single line handles this:

# mainapp/views.py
from django.contrib.auth.decorators import login_required

@login_required  # This single line is enough to protect the entire page view
def dashboard(request):
    """User Dashboard — for authenticated users only"""
    # request.user always contains the authenticated user's data
    user_projects = Project.objects.filter(
        client__in=Client.objects.filter(created_by=request.user)
    )

    return render(request, 'dashboard.html', {
        'projects': user_projects,
        'user': request.user,
        'total_budget': sum(p.budget for p in user_projects),
        'active_count': user_projects.filter(status='active').count(),
    })

When an unauthenticated visitor tries to access this page, Django automatically redirects them to the login page, and then routes them back to their intended destination once they successfully log in. All of this is accomplished with one line.

Linking Projects to Users: Every Freelancer Only Sees Their Own Work

To secure the system completely, each user must only see their own clients and projects, rather than those belonging to others. We modify the model to link directly to Django’s built-in User model:

# mainapp/models.py — Updating the Client model
from django.contrib.auth.models import User

class Client(models.Model):
    owner = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        related_name='clients',
        verbose_name="Record Owner"
    )
    name = models.CharField(max_length=200)
    email = models.EmailField()
    # ... remaining fields

# Inside the view — always ensuring users only see their own data
@login_required
def clients_list(request):
    # Automatically filter by the currently logged-in user
    clients = Client.objects.filter(owner=request.user)
    return render(request, 'clients.html', {'clients': clients})

@login_required
def add_client(request):
    if request.method == 'POST':
        name = request.POST.get('name')
        email = request.POST.get('email')
        # Link the new client to the logged-in user automatically
        Client.objects.create(
            owner=request.user,
            name=name,
            email=email
        )
        return redirect('clients_list')
    return render(request, 'add_client.html')

Crucial Security Tips You Cannot Ignore

When you are handling real data for real clients, security is never optional:

• Never place passwords or database credentials directly in your code: Use environment variables via libraries like python-decouple or python-dotenv.
• Always enable HTTPS on your production server: Most hosting platforms offer this for free via Let’s Encrypt.
• CSRF protection is enabled by default in Django: Never disable it. Ensure that {% csrf_token %} is included inside every single HTML form.
• Never display detailed error messages to your visitors: Turn off debug mode by setting DEBUG = False when deploying to a live server.

# Example of using environment variables securely
# First: pip install python-decouple

# .env file (NEVER upload this to GitHub)
# SECRET_KEY=your-secret-key-here
# DB_PASSWORD=your-db-password

# settings.py
from decouple import config

SECRET_KEY = config('SECRET_KEY')

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': config('DB_NAME', default='myproject_db'),
        'USER': config('DB_USER', default='myproject_user'),
        'PASSWORD': config('DB_PASSWORD'),
        'HOST': config('DB_HOST', default='localhost'),
        'PORT': config('DB_PORT', default='5432'),
    }
}

A critical lesson developers learn the hard way: pushing a file containing database passwords to GitHub, even if the repository is private, is an unforgivable mistake. Make your .env file the very first entry in your .gitignore file.

Summary and Next Steps

Today, our website transitioned from temporary memory to permanent persistence. We learned how to set up a PostgreSQL database and link it to Django, how to define data Models and perform comprehensive CRUD operations on them, and how to build a fully secure user authentication system with roles and access management in just a few lines of code. Combined, these skills enable you to deliver a complete web solution that is ready for production environments.

Recommended Next Step:

Our website now accepts and stores data, but what if a client wants to pay for a service online? Or what if you want to sell a Python tool as a recurring monthly software-as-a-service (SaaS)? In the next article, we take it a step further and learn how to build a Python API to sell our services to developers and other external applications.

Join us in the twelfth article: Building a Simple API in Python to Sell Your Services.

References and Sources:

1. Official Django Database Documentation: Django Database Documentation
2. Official Django Authentication System Documentation: Django Authentication System
3. Official PostgreSQL Documentation: PostgreSQL Official Documentation
4. The python-decouple Environment Management Library: python-decouple on PyPI