Django MySQL REST CRUD API with Unit Testing - Complete Guide

This comprehensive guide will walk you through building a Django REST API with MySQL database from scratch, including complete CRUD operations and unit testing.

Project Setup
Database Configuration
Creating Models
Serializers
Views
URL Routing
Unit Testing
Running the Project

Project Setup

First, let's create a new Django project and install necessary packages.

# Create a virtual environment
python -m venv env
source env/bin/activate  # On Windows use `env\Scripts\activate`

# Install required packages
pip install django djangorestframework mysqlclient pytest pytest-django

# Create a new Django project
django-admin startproject core
cd core

# Create a new app for our API
python manage.py startapp api

Add the apps to INSTALLED_APPS in core/settings.py:

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'rest_framework',
    'api.apps.ApiConfig',
]

Database Configuration

Configure MySQL in core/settings.py:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'django_rest',       # Your database name
        'USER': 'root',              # Your database username
        'PASSWORD': 'password',      # Your database password
        'HOST': 'localhost',         # Your database host
        'PORT': '3306',              # Your database port
        'OPTIONS': {
            'init_command': "SET sql_mode='STRICT_TRANS_TABLES'",
        }
    }
}

Creating Models

Let's create a sample model in api/models.py:

from django.db import models

class Product(models.Model):
    name = models.CharField(max_length=100)
    description = models.TextField()
    price = models.DecimalField(max_digits=10, decimal_places=2)
    quantity = models.IntegerField()
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    def __str__(self):
        return self.name

After creating the model, run migrations:

python manage.py makemigrations
python manage.py migrate

Serializers

Create api/serializers.py:

from rest_framework import serializers
from .models import Product

class ProductSerializer(serializers.ModelSerializer):
    class Meta:
        model = Product
        fields = ['id', 'name', 'description', 'price', 'quantity', 'created_at', 'updated_at']
        read_only_fields = ['id', 'created_at', 'updated_at']

Views

Create views in api/views.py:

from rest_framework import status
from rest_framework.response import Response
from rest_framework.views import APIView
from .models import Product
from .serializers import ProductSerializer

class ProductListCreateView(APIView):
    def get(self, request):
        products = Product.objects.all()
        serializer = ProductSerializer(products, many=True)
        return Response(serializer.data)
    
    def post(self, request):
        serializer = ProductSerializer(data=request.data)
        if serializer.is_valid():
            serializer.save()
            return Response(serializer.data, status=status.HTTP_201_CREATED)
        return Response(serializer.errors, status=status.HTTP_400_BAD_REQUEST)

class ProductRetrieveUpdateDestroyView(APIView):
    def get_object(self, pk):
        try:
            return Product.objects.get(pk=pk)
        except Product.DoesNotExist:
            return None
    
    def get(self, request, pk):
        product = self.get_object(pk)
        if product is None:
            return Response(status=status.HTTP_404_NOT_FOUND)
        serializer = ProductSerializer(product)
        return Response(serializer.data)
    
    def put(self, request, pk):
        product = self.get_object(pk)
        if product is None:
            return Response(status=status.HTTP_404_NOT_FOUND)
        serializer = ProductSerializer(product, data=request.data)
        if serializer.is_valid():
            serializer.save()
            return Response(serializer.data)
        return Response(serializer.errors, status=status.HTTP_400_BAD_REQUEST)
    
    def delete(self, request, pk):
        product = self.get_object(pk)
        if product is None:
            return Response(status=status.HTTP_404_NOT_FOUND)
        product.delete()
        return Response(status=status.HTTP_204_NO_CONTENT)

URL Routing

Create api/urls.py:

from django.urls import path
from .views import ProductListCreateView, ProductRetrieveUpdateDestroyView

urlpatterns = [
    path('products/', ProductListCreateView.as_view(), name='product-list-create'),
    path('products/<int:pk>/', ProductRetrieveUpdateDestroyView.as_view(), name='product-retrieve-update-destroy'),
]

Update core/urls.py:

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

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

Unit Testing

Create api/tests.py:

from django.urls import reverse
from rest_framework import status
from rest_framework.test import APITestCase
from .models import Product

class ProductTests(APITestCase):
    def setUp(self):
        self.product = Product.objects.create(
            name='Test Product',
            description='Test Description',
            price=99.99,
            quantity=10
        )
    
    def test_get_products(self):
        url = reverse('product-list-create')
        response = self.client.get(url)
        self.assertEqual(response.status_code, status.HTTP_200_OK)
        self.assertEqual(len(response.data), 1)
    
    def test_create_product(self):
        url = reverse('product-list-create')
        data = {
            'name': 'New Product',
            'description': 'New Description',
            'price': 49.99,
            'quantity': 5
        }
        response = self.client.post(url, data, format='json')
        self.assertEqual(response.status_code, status.HTTP_201_CREATED)
        self.assertEqual(Product.objects.count(), 2)
    
    def test_get_single_product(self):
        url = reverse('product-retrieve-update-destroy', args=[self.product.id])
        response = self.client.get(url)
        self.assertEqual(response.status_code, status.HTTP_200_OK)
        self.assertEqual(response.data['name'], 'Test Product')
    
    def test_update_product(self):
        url = reverse('product-retrieve-update-destroy', args=[self.product.id])
        data = {
            'name': 'Updated Product',
            'description': 'Updated Description',
            'price': 109.99,
            'quantity': 15
        }
        response = self.client.put(url, data, format='json')
        self.assertEqual(response.status_code, status.HTTP_200_OK)
        self.product.refresh_from_db()
        self.assertEqual(self.product.name, 'Updated Product')
    
    def test_delete_product(self):
        url = reverse('product-retrieve-update-destroy', args=[self.product.id])
        response = self.client.delete(url)
        self.assertEqual(response.status_code, status.HTTP_204_NO_CONTENT)
        self.assertEqual(Product.objects.count(), 0)

Running the Project

First, create a superuser to access the admin panel:

python manage.py createsuperuser

Run the development server:

python manage.py runserver

Run the tests:

python manage.py test

API Endpoints

Product List/Create:
- GET /api/products/ - List all products
- POST /api/products/ - Create new product
Product Detail:
- GET /api/products/<id>/ - Get single product
- PUT /api/products/<id>/ - Update product
- DELETE /api/products/<id>/ - Delete product

Conclusion

This simplified guide has covered:

Setting up a Django project with MySQL
Creating models and migrations
Building RESTful CRUD endpoints without authentication
Writing comprehensive unit tests

You can now extend this foundation by:

Adding more models and relationships
Implementing pagination
Adding filtering and searching
Including more advanced features like caching
Adding basic authentication if needed later

CODE WITH SIBIN

Solving Real Problems with Real Code

Table of Contents