How to build API with Django REST Framework and MySQL

First of all, I feel like I should clarify a potential confusion around Django and Django REST Framework that some people might have:

Django is a fully-featured web development framework, whereas Django REST Framework is a package for Django that makes the job of building RESTful APIs easier.

So if you’re wondering how to connect Django REST Framework to MySQL, your actual question should be – how to connect Django to MySQL and then build an API on top of it.

Let’s explore this topic and also build a simple API example to see MySQL in action.

What is MySQL?

MySQL is one of the most popular open-source relational database solutions. It’s particularly loved by the PHP community, though other developers often use it too.

While MySQL is not the most feature-rich solution (unlike, for example, PostgreSQL), it focuses on efficiently implementing the most commonly used functionality. That means MySQL can run on a pretty modest system while still providing you with all the features your project needs (unless you’re building a back-end for the Pentagon).

How to connect Django (and Django REST Framework) to MySQL?

Django has integrated support for MySQL, so making your project work with it is easy. You can do it in four steps:

1. Install and configure MySQL
2. Create a database with a user
3. Install Django with necessary dependencies and start a project
4. Specify database connection details in Django settings

Let’s review each step in detail.

Pre-requisites and assumptions

To keep this article concise and relevant, I’ll have to make a few assumptions:

• You have a basic knowledge of Django
• You’re setting up a dev environment rather than production
• You have Python 3 installed on your system (either 3.6, 3.7 or 3.8)
• You have pip installed and ready to use
• Optionally, you have virtualenv or venv on your system and you run all commands within a virtual environment (good practice)

Step 1 – Installing MySQL

Installing MySQL on Windows

Like most Windows software, you can download and run an installer to get MySQL set up.

Head to the official downloads page and select either the web or the offline installer. The page will ask you to log in or sign up, but you can skip that step.

Run the installer; it will ask you to choose the setup type. You can go with the developer setup – it has all you need (and probably even too much).

Personally, I don’t like installing the following:

• Router
• Integration for Visual Studio
• Documentation
• Examples and samples

But if you select the custom setup, you’ll be asked to grind through a big list of options to choose from. Instead, you can select the developer setup, click next, then back, then select the custom setup and click next again. That will pre-select all the developer components for you. Then you can remove whatever you don’t want.

The rest of the installation is straightforward – you can click through the remaining steps with default options.

Installing MySQL on Linux

On Linux, the installation process is even easier.

Open up the terminal and start with updating your packages index:

sudo apt update

Next, install the MySQL server:

sudo apt install mysql-server

The last step is to configure the server. The following command will initialize the configuration wizard, which will ask you a bunch of questions:

sudo mysql_secure_installation

Those questions are mainly around hardening your MySQL server, but since we’re setting up a dev environment, you don’t need to stress about them.

In short, you can choose the following answers and options:

• Would you like to setup VALIDATE PASSWORD component? – YES
• Select password validation policy (0, 1 or 2) – 0
• Password for root user – Enter password & YES
• Remove anonymous users? – YES
• Disallow root login remotely? – YES
• Remove test database and access to it? – YES
• Reload privilege tables now? – YES

Step 2 – Creating MySQL database and user

On Windows, open the start menu and look up MySQL Command Line Client. That should open the MySQL shell for you.

On Linux, the following command should log you into MySQL shell:

sudo mysql

Once you’re in the shell, the commands are the same for Windows and Linux. 

First, create a new database:

create database `database-name`;

Note that if you use hyphens in your database name, you need to wrap it in grave accent ` characters. Also, note the semicolon ; at the end – each line must end with it.

Next, create a database user like so:

create user `database-user`@localhost identified by 'password';

The @localhost here specifies the host from which the user will connect. Since we access the database locally, we specify localhost. 

Note also that the password has to be wrapped in single quotes ' rather than grave accents `, unlike the database and user names.

Lastly, give our user the privileges to work on the database:

grant all on `database-name`.* to `database-user`@localhost;

Note the .* following the database name – it implies granting permissions on all tables of the database.

Step 3 – Installing Django with dependencies

Now comes the part where we configure our virtual environment.

Django relies on the mysqlclient package for working with MySQL. So we need to install it along with Django (and Django REST Framework):

pip install -U django mysqlclient djangorestframework

If you’re on Linux, chances are you might run into the following error while trying to install mysqlclient:

ERROR: Could not find a version that satisfies the requirement mysqlclient (from versions: 1.3.0, 1.3.1, ...)
ERROR: No matching distribution found for mysqlclient

To solve it, you need to install some system packages required for mysqlclient compilation:

sudo apt install python3-dev libmysqlclient-dev

After that, try running the pip command above again.

Step 4 – Configuring database access in Django settings

Finally, we need to create a Django project and configure the settings.

I’ve created this installation cheat sheet for Django projects with DRF – it contains commands to create a new project as well as some handy DRF settings.

Otherwise, you can simply run the following:

python -m django startproject project-name .

Note the dot . at the end – it instructs Django to create project files in the current directory rather than a new one.

Now open the settings.py file and update the DATABASES section with your database and user details:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': '<db-name>',
        'USER': '<db-user>',
        'PASSWORD': '<db-password>',
        'HOST': '127.0.0.1',
        'PORT': '3306',
    }
}

Provided everything is correct, you should have access to the database now. One way to test it is to run Django’s initial migrations:

python manage.py migrate

If the migrations are applied, then you’re all set!

Building a simple API with Django REST Framework and MySQL

As I mentioned earlier, I’m going to build a very simple API example using Django REST Framework to show MySQL in action. It will have a single function – manage notes.

Keep in mind that at any time you can refer to the GitHub repo of this project for reference.

Start a new app to keep things organized:

python manage.py startapp notes

Then add it to the INSTALLED_APPS inside the settings.py file along with DRF:

INSTALLED_APPS = [
    ... # Django apps are here
    'rest_framework',
    'notes.apps.NotesConfig',
]

Creating a simple model

We will need the following model, which should go into the models.py file of your app:

from django.db import models

class Note(models.Model):
    text = models.TextField()

Generate and run migrations to update the database:

python manage.py makemigrations
python manage.py migrate

Creating DRF serializers

The next step is to create a model serializer. 

For those who don’t know, serializers are part of the Django REST Framework. They are a bunch of special classes used to convert model instances from Python objects into JSON and back.

Let’s create a separate file to store our serializers and call it serializers.py. Here is what it should contain:

from rest_framework import serializers
from .models import Note

class NoteSerializer(serializers.ModelSerializer):
    class Meta:
        model = Note
        fields = '__all__'

NOTE: Normally, I would create a separate app for the API because it's a good practice to keep an app for a single purpose only. But for the sake of simplicity, I'll keep API files in the same folder for this project.

Creating DRF API views

The next question is hot to pass some data to our serializer so it can convert it from and to JSON.

As you probably know, views in Django handle the incoming HTTP requests and generate responses for them. Hence they are also used for passing data to serializers.

However, API endpoints are somewhat different to regular web pages. They don’t need to render HTML code – they simply need to return raw data.

Django REST Framework implements a subset of views that make building API endpoints easier. They are called API views.

We want our API to be able to perform all of the CRUD operations (Create, Read, Update, Delete) on the notes. Each operation requires a separate view.

Luckily for us, DRF provides a boilerplate class for that called ModelViewSet. It packages four API views (one per each of the CRUD operations) into a single class.

NOTE: In fact, there are five views in a view set because DRF supports both full and partial updates. Partial updates are handy when you want to update one or a few fields on a big object. Instead of sending the full object, you'd only send the fields for change, which is faster.

Let’s create a new viewset inside the views.py file of our app to work with our notes serializer:

from rest_framework import viewsets
from .models import Note
from .serializers import NoteSerializer

class NoteViewSet(viewsets.ModelViewSet):
    serializer_class = NoteSerializer
    queryset = Note.objects.all()

It’s amazing how DRF allows us to build an entire set of views with just three lines of code.

Registering API URLs

We want all notes-related endpoints to be available via the following URL:

http://domain.name/api/notes/

As you might know, in Django, the file called urls.py is responsible for registering views and connecting them to URLs.

However, our views are “packaged” together inside the viewset. So we cannot register them the usual way. We need to “unpack” them first. 

There are a few classes called routers in DRF that can do this job for us.

The following snippet demonstrates how to create a router and add our viewset to it:

... # Other imports are here
from django.urls import path, include  # Ensure `include` is imported
from rest_framework import routers
from notes.views import NoteViewSet

# Notes router
notes_router = routers.SimpleRouter()
notes_router.register(
    r'notes',
    NoteViewSet,
    basename='note',
)

... # urlpatterns code is here

Lastly, we can add the URLs generated by our router to the list of urlpatterns:

urlpatterns = [
    ... # Admin routes are registered here

    # API
    path('api/', include(notes_router.urls))
]

Testing our API

Our API should be functional now. Let’s give it a quick test. Start the Django dev server:

python manage.py runserver

Navigate to http://127.0.0.1:8000/api/notes/. You should see Django REST Framework’s Browsable API. It would display an empty list of notes and a form to post a new note:

Conclusion

Django is a very versatile framework, and making it speak with MySQL doesn’t take much effort.

Django REST Framework, in turn, relies on Django’s ORM to work with database models. So regardless of which database you are using, your DRF code will be the same.

MySQL can be a great choice for smaller applications and microservices. But for bigger, monolith applications, you might want to consider using a database with a broader feature set, like PostgreSQL.

You can find the full source code of the little API we built in its GitHub repository.