Chapter 2: PostgreSQL

One of the most immediate differences between working on a “toy app” in Django and a production-ready one is the database. Django ships with SQLite as the default choice for local development because it is small, fast, and file-based which makes it easy to use. No additional installation or configuration is required.

However this convenience comes at a cost. Generally speaking SQLite is not a good database choice for professional websites. So while it is fine to use SQLite locally while prototyping an idea, it is rare to actually use SQLite as the database on a production project.

Django ships with built-in support for four databases: SQLite, PostgreSQL, MySQL, and Oracle. We’ll be using PostgreSQL in this book as it is the most popular choice for Django developers, however, the beauty of Django’s ORM is that even if we wanted to use MySQL or Oracle, the actual Django code we write will be almost identical. The Django ORM handles the translation from Python code to the databases for us which is quite amazing if you think about it.

The challenge of using these three databases is that each must be both installed and run locally if you want to faithfully mimic a production environment on your local computer. And we do want that! While Django handles the details of switching between databases for us there are inevitably small, hard-to-catch bugs that can crop up if you use SQLite for local development but a different database in production. Therefore a best practice is use the same database locally and in production.

In this chapter we’ll start a new Django project with a SQLite database and then switch over to both Docker and PostgreSQL.

Starting

On the command line make sure you’ve navigated back to the code folder on our desktop. You can do this two ways. Either type cd .. to move “up” a level so if you are currently in Desktop/code/hello you will move to Desktop/code. Or you can simply type cd ~/Desktop/code/ which will take you directly to the desired directory. Then create a new directory called postgresql for this chapter’s code.

$ cd ..
$ mkdir postgresql && cd postgresql

Now install Django, start the shell, and create a basic Django project called postgresql_project. Don’t forget the period . at the end of the command!

$ pipenv install django==2.2.3
$ pipenv shell
(postgresql) $ django-admin startproject postgresql_project .

So far so good. Now we can migrate our database to initialize it and use runserver to start the local server.

A> Normally I don’t recommend running migrate on new projects until after a custom user model has been configured. Otherwise Django will bind the database to the built-in User model which is difficult to modify later on in the project. We’ll cover this properly in Chapter 3 but since this chapter is primarily for demonstration purposes, using the default User model here is a one-time exception.

(postgresql) $ python manage.py migrate
(postgresql) $ python manage.py runserver

Confirm everything worked by navigating to http://127.0.0.1:8000/ in your web browser. You may need to refresh the page but should see the familiar Django welcome page.

Stop the local server with Control+c and then use the ls command to list all files and directories.

(postresql) $ ls
Pipfile     Pipfile.lock     db.sqlite3     manage.py     postgresql_project

Docker

To switch over to Docker first exit our virtual environment and then create Dockerfile and docker-compose.yml files which will control our Docker image and container respectively.

(postgresql) $ exit
$ touch Dockerfile
$ touch docker-compose.yml

The Dockerfile is the same as in Chapter 1.

# Dockerfile

# Pull base image
FROM python:3.7-slim

# Set environment varibles
ENV PYTHONDONTWRITEBYTECODE 1
ENV PYTHONUNBUFFERED 1

# Set work directory
WORKDIR /code

# Install dependencies
COPY Pipfile Pipfile.lock /code/
RUN pip install pipenv && pipenv install --system

# Copy project
COPY . /code/

Go ahead and build the initial image now using the docker build . command.

$ docker build .
Sending build context to Docker daemon  146.4kB
Step 1/7 : FROM python:3.7-slim
 ---> ba83e35afc23
Step 2/7 : ENV PYTHONDONTWRITEBYTECODE 1
 ---> Using cache
 ---> accdccfdb2ff
Step 3/7 : ENV PYTHONUNBUFFERED 1
 ---> Using cache
 ---> 6d16b0644a2a
Step 4/7 : WORKDIR /code
 ---> Using cache
 ---> 1e879264a847
Step 5/7 : COPY Pipfile Pipfile.lock /code/
 ---> Using cache
 ---> 72aa4b9e71c9
Step 6/7 : RUN pip install pipenv && pipenv install --system
 ---> Using cache
 ---> 92f3b2b2044a
Step 7/7 : COPY . /code/
 ---> 38e9b8855d9d
Successfully built 38e9b8855d9d

Your Docker image IDs might look slightly different than mine. For example the ID of the python:3.7-slim image is ba83e35afc23 at this moment in time, but that image will be updated at some point in the future and the corresponding ID change as well. But these are largely minor changes so as long as you see Successfully built on the last line you are in good shape.

Did you notice that the Dockerfile built an image much faster this time around? That’s because Docker looks locally on your computer first for a specific image. If it doesn’t find an image locally it will then download it. And since many of these images were already on the computer from the previous chapter, Docker didn’t need to download them all again!

Time now for the docker-compose.yml file which also matches what we saw previously in Chapter 1.

# docker-compose.yml
version: '3.7'

services:
  web:
    build: .
    command: python /code/manage.py runserver 0.0.0.0:8000
    volumes:
      - .:/code
    ports:
      - 8000:8000

Detached Mode

We’ll start up our container now but this time in detached mode which requires either the -d or -detached flag (they do the same thing).

$ docker-compose up -d

Detached mode runs containers in the background, which means we can use a single command line tab without needing a separate one open as well. This saves us from switching back and forth between two command line tabs constantly. The downside is that if/when there is an error, the output won’t always be visible. So if your screen does not match this book at some point, try typing docker-compose logs to see the current output and debug any issues.

You likely will see a “Warning: Image for service web was built because it did not already exist” message at the bottom of the command. Docker automatically created a new image for us within the container. As we’ll see later in the book, adding the --build flag to force an image build is necessary when software packages are updated because, by default, Docker will look for a local cached copy of software and use that which improves performance.

To confirm things are working properly go back to http://127.0.0.1:8000/ in your web browser. Refresh the page to see the Django welcome page again.

Since we’re working within Docker now as opposed to locally we must preface traditional commands with docker-compose exec web. For example, to create a superuser account instead of typing python manage.py createsuperuser the updated command would now look like the line below.

$ docker-compose exec web python manage.py createsuperuser

For the username choose sqliteadmin, sqliteadmin@email.com as the email address, and select the password of your choice. I often use testpass123.

Then navigate directly into the admin at http://127.0.0.1:8000/admin and log in.

You will be redirected to the admin homepage. Note in the upper right corner sqliteadmin is the username.

If you click on the Users button it takes us to the Users page where we can confirm only one user has been created.

It’s important to highlight another aspect of Docker at this point: so far we’ve been updating our database–currently represented by the db.sqlite3 file–within Docker. That means the actual db.sqlite3 file is changing each time. And thanks to the volumes mount in our docker-compose.yml config each file change has been copied over into a db.sqlite3 file on our local computer too. You could quit Docker, start the shell, and see the exact same admin login at this point because the underlying SQLite database is the same.

PostgreSQL

Now it’s time to switch over to PostgreSQL for our project which takes three additional steps:

• install a database adapter, psycopg2, so Python can talk to PostgreSQL
• update the DATABASE config in our settings.py file
• install and run PostgreSQL locally

Ready? Here we go. Stop the running Docker container with docker-compose down.

$ docker-compose down
Stopping postgresql_web_1 ... done
Removing postgresql_web_1 ... done
Removing network postgresql_default

Then within our docker-compose.yml file add a new service called db. This means there will be two separate services, each a container, running within our Docker host: web for the Django local server and db for our PostgreSQL database.

The PostgreSQL version will be pinned to the latest version, 11. If we had not specified a version number and instead used just postgres then the latest version of PostgreSQL would be downloaded even if at a later date that is Postgres 12 which will likely have different requirements.

Finally we add a depends_on line to our web service since it literally depends on the database to run. This means that db will be started up before web.

# docker-compose.yml
version: '3.7'

services:
  web:
    build: .
    command: python /code/manage.py runserver 0.0.0.0:8000
    volumes:
      - .:/code
    ports:
      - 8000:8000
    depends_on:
      - db
  db:
    image: postgres:11

Now run docker-compose up -d which will rebuild our image and spin up two containers, one running PostgreSQL within db and the other our Django web server.

$ docker-compose up -d
Creating network "postgresql_default" with the default driver
...
Creating postgresql_db_1 ... done
Creating postgresql_web_1 ... done

It’s important to note at this point that a production database like PostgreSQL is not file-based. It runs entirely within the db service and is ephemeral; when we execute docker-compose down all data within it will be lost. This is in contrast to our code in the web container which has a volumes mount to sync local and Docker code.

In the next chapter we’ll learn how to add a volumes mount for our db service to persist our database information.

Settings

With your text editor, open the posgresql_project/settings.py file and scroll down to the DATABASES config. The current setting is this:

# postgresql_project/settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    }
}

By default Django specifies sqlite3 as the database engine, gives it the name db.sqlite3, and places it at BASE_DIR which means in our project-level directory.

Since directory structure is often a point of confusion “project-level” means the top directory of our project which contains postgresql_project, manage.py, Pipfile, Pipfile.lock, and the db.slite3 file.

(postgresql) $ ls
Dockerfile   Pipfile.lock   docker-compose.yml   postgresql_project
Pipfile   db.sqlite3   manage.py

To switch over to PostgreSQL we will update the ENGINE configuration. PostgreSQL requires a NAME, USER, PASSWORD, HOST, and PORT.

For convenience we’ll set the first three to postgres, the HOST to db which is the name of our service set in docker-compose.yml, and the PORT to 5432 which is the default PostgreSQL port.

# postgresql_project/settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'postgres',
        'USER': 'postgres',
        'PASSWORD': 'postgres',
        'HOST': 'db',
        'PORT': 5432
    }
}

You will see an error now if your refresh the web page.

What’s happening? Since we’re running Docker in detached mode with the -d flag it’s not immediately clear. Time to check our logs.

$ docker-compose logs
...
web_1  | django.core.exceptions.ImproperlyConfigured: Error loading psycopg2
module: No module named 'psycopg2'

There will be a lot of output but at the bottom of the web_1 section you’ll see the above lines which tells us we haven’t installed the psycopg2 driver yet.

Pyscopg

PostgreSQL is a database that can be used by almost any programming language. But if you think about it, how does a programming language–and they all vary in some way or another–connect to the database itself?

The answer is via a database adapter! And that’s what Psycopg is, the most popular database adapter for Python. If you’d like to learn more about how Pyscopg works here is a link to a fuller description on the official site.

We can install Pyscopg with Pipenv. On the command line, enter the following command so it is installed within our Docker host.

$ docker-compose exec web pipenv install psycopg2-binary==2.8.3

Why install within Docker rather than locally I hope you’re asking? The short answer is that consistently installing new software packages within Docker and then rebuilding the image from scratch will save us from potential Pipfile.lock conflicts.

The Pipfile.lock generation depends heavily on the OS being used. We’ve specified our entire OS within Docker, including using Python 3.7-slim. But if you install psycopg2 locally on your computer, which has a different environment, the resulting Pipfile.lock file will also be different. But then the volumes mount in our docker-compose.yml file, which automatically syncs the local and Docker filesystems, will cause the local Pipfile.lock to overwrite the version within Docker. So now our Docker container is trying to run an incorrect Pipfile.lock file. Ack!

One way to avoid these issues is to consistently install new software packages within Docker rather than locally.

If you now refresh the webpage you will….still see an error. Ok, let’s check the logs.

$ docker-compose logs

It’s the same as before! Why does this happen? Docker automatically caches images unless something changes for performance reasons. We want it to automatically rebuild the image with our new Pipfile and Pipfile.lock but because the last line of our Dockerfile is COPY . /code/ only the files will copy; the underlying image won’t rebuild itself unless we force it too. This can be done by adding the --build flag.

So to review: whenever adding a new package first install it within Docker, stop the containers, force an image rebuild, and then start the containers up again. We’ll use this flow repeatedly throughout the book.

$ docker-compose down
$ docker-compose up -d --build

If you refresh the homepage again the Django welcome page at http://127.0.0.1:8000/ now works! That’s because Django has successfully connected to PostgreSQL via Docker.

Great, everything is working.

New Database

However since we are using PostgreSQL now, not SQLite, our database is empty. If you look at the current logs again by typing docker-compose logs you’ll see complaints like “You have 15 unapplied migrations(s)”.

To reinforce this point visit the Admin at http://127.0.0.1:8000/admin/ and log in. Will our previous superuser account of sqliteadmin and testpass123 work?

Nope! We see ProgrammingError at /admin which complains that auth_user doesn’t even exist because we have not done a migration yet! Also, we don’t have a superuser either on our PostgreSQL database.

To fix this situation we can both migrate and create a superuser within Docker that will access the PostgreSQL database.

$ docker-compose exec web python manage.py migrate
$ docker-compose exec web python manage.py createsuperuser

What should we call our superuser? Let’s use postgresadmin and for testing purposes set the email to postgresadmin@email.com and the password to testpass123.

In your web browser navigate to the admin page at http://127.0.0.1:8000/admin/ and enter in the new superuser log in information.

In the upper right corner it shows that we are logged in with postgresadmin now not sqliteadmin. Also you can click on the Users tab on the homepage and visit the Users section to see our one and only user is the new superuser account.

Remember to stop our running container with docker-compose down.

$ docker-compose down

Git

Let’s save our changes again by initializing Git for this new project, adding our changes, and including a commit message.

$ git init
$ git status
$ git add .
$ git commit -m 'ch2'

The official source code for Chapter 2 is available on Github.

Conclusion

The goal of this chapter was to demonstrate how Docker and PostgreSQL work together on a Django project. Switching between a SQLite database and a PostgreSQL is a mental leap for many developers initially.

The key point is that with Docker we don’t need to be in a local virtual environment anymore. Docker is our virtual environment…and our database and more if desired. The Docker host essentially replaces our local operating system and within it we can run multiple containers, such as for our web app and for our database, which can all be isolated and run separately.

In the next chapter we will start our online Bookstore project. Let’s begin!

This concludes the free sample chapters of the book. There are 16 more…

The complete book features 18 chapters and builds an online bookstore from scratch with robust user registration, environment variables, permissions, search, payments with Stripe, security and performance improvements, and deployment.

Want to learn more Django? I write a monthly newsletter with new tutorials on web development with Django, Django REST Framework, React, and more.