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 config
. Don’t forget the period .
at the end of the command!
$ pipenv install django~=3.1.0
$ pipenv shell
(postgresql) $ django-admin startproject config .
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 config db.sqlite3 manage.py
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.8
# Set environment variables
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 .
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.8'
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 -detach
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 [service]
where we specify the name of the service. For example, to create a superuser account instead of typing python manage.py createsuperuser
the updated command would now look like the line below, using the web
service.
$ 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
, start the server with python manage.py runserver
, 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 oursettings.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_d_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 that version in the future is 12, 13, or another number. It’s always good to pin to a specific version number, both for databases and packages.
The second part is adding the environment
variable setting for POSTGRES_HOST_AUTH_METHOD=trust
, which allows us to connect without a password. This is a convenience for local development.
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.8'
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
environment:
- "POSTGRES_HOST_AUTH_METHOD=trust"
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
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:
# config/settings.py
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.sqlite3',
'NAME': 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 config
, manage.py
, Pipfile
, Pipfile.lock
, and the db.slite3
file.
(postgresql) $ ls
Dockerfile Pipfile.lock docker-compose.yml config
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.
# config/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 at http://127.0.0.1:8000/
.
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.
Psycopg
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 Psycopg works here is a link to a fuller description on the official site.
We can install Psycopg 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.5
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.8
. 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 18 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
. 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 postgresqladmin
and for testing purposes set the email to postgresqladmin@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 15 more…
The complete book features 17 chapters and builds an online bookstore from scratch with robust user registration, environment variables, permissions, search, security and performance improvements, and deployment.