Painless PostgreSQL + Django

Edit (24 June 2017): This post has a part 2, 9 Straightforward Steps for Deploying your Django App with Heroku!

If you’ve used the Django web framework before, you might have noticed that it comes with a SQLite database right out of the box and ready to go. So, why would you ever change that?

The most compelling reason for me to switch to Postgres is that SQLite, while awesome for basic development, doesn’t work with Heroku and other platforms for deploying and managing web applications (more on than in a future post). PostgreSQL (henceforward Postgres) isn’t the only official option here — Oracle and MySQL are also supported in Django — but it will be the focus of this post.

Figuring out the mechanics of each of the steps required to swap a the SQLite database for a Postgres one was pretty confusing the first couple of times I did it, trying to stitch together different sources of documentation and Stack Overflow comments. This blog post outlines the basics to save you some Googling.

Prerequisites

This post assumes you are familiar with the basics of Django — if you don’t know how to start a project yet, I recommend the Django Girls and Django Software Foundation tutorials. You should also know how to set up a virtual environment and add environment variables.

You should have Postgres installed as well.

The project

There are 4 steps involved in adding a Postgres database to your app:

1. Create a new user in Postgres (recommended)
2. Create a new database and give the new user access
3. Update your settings.py file to connect to the new database
4. Migrate & test!

Using the sample app

I’ve created a sample app in this repo that will cover both parts of this post. The app uses Django 1.11, Python 3.5, and PostgreSQL 9.6.

Cloning the repo will only help you skip step 3 before running the app locally. To do that, you’ll need to:

• Create and activate a virtual environment
• Run pip install -r requirements.txt
• Complete steps 1 & 2 below
• Create environment variables for the database credentials and source them correctly in the settings.py file
• Migrate as shown in step 4.

Let’s get started, step-by-step.

Step 1: Create a new user in Postgres

This isn’t strictly required, but it is recommended — using a different user for each app will help keep data more secure and limit the amount of damage that can be done to your databases.

To do this, open Postgres in the terminal by typing:

> psql

Create your user (with the username and password of your choice):

> CREATE USER sample_user WITH PASSWORD 'sample_password';

Make sure you put the password in single quotes — Postgres is expecting a string there.

2. Create a new database and give the new user access

Now you need to create the database your app will be connecting to. All we’re doing in this step is creating the data structure to link to — the actual tables will be created by Django when you migrate in step 4.

With Postgres running in your terminal, use the CREATE DATABASE command — to make your life easier, I recommend making your new, dedicated user the owner of the database (more on that in a moment):

> CREATE DATABASE sample_database WITH OWNER sample_user;

In Postgres, the owner of the database has full control over it, to perform any operation on current or future tables.

However, there are a couple of alternatives, depending on your situation:

• If for any reason you wanted to grant specific permissions to your user (if you’re storing data in the database that you don’t want to have the app access but you do want the user to access, for example), use GRANT to give the user access to specific tables after migrating in step 4. Beware, however that this can produce errors down the road if the user doesn’t have the right access specifically assigned.
• Postgres has the concept of a schema, which in this context means a collection of tables. It also has an automatically created schema called public that includes all tables. So, you could write a command like this to give the user “all access” to the public schema:

> GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO sample_user;

3. Update your settings.py file to connect to the new database

As you probably guessed from the name of this step, we are going to leave the terminal for a moment and go into your project’s settings.py file to give our Django app new instructions about where to look for the database.

When you initiate a project, Django automatically generates a SQLite database, called db.sqlite3 in your project root, and connects it to your app, with code in settings.py that looks something like this.

Django is using a dictionary called DATABASES to store information about how to connect.

We need to replace the values in the dictionary stored under the default key on line 78.

The value for ENGINEwill change to django.db.backends.postgresql_psycopg2 .

The NAME , USER , and PASSWORD are all for the Postgres database we created in steps 1 and 2. In the example below I’ve added these values to a bash file and sourced them into my virtual environment, mainly for security reasons to ensure that I do not accidentally commit them to Github. If you add them to settings directly, they need to be in quotes — Django is expecting strings for all three fields.

HOST will be localhost during development, and Postgres will run by default on PORT 5432 — if you’ve changed that, substitute in the appropriate Port number. Note that this is a string, not a number, and needs to be in quotes.

At this point, I’d also recommend deleting the old database, db.sqlite3, from your project root.

Step 4: Migrate and test!

Make sure you’ve saved your settings.py file and then head back to your terminal. If needed, exit Postgres (with\q ).

Then, from the terminal, same directory as your manage.py file:

> python manage.py makemigrations

> python manage.py migrate

You should see output in the terminal after the second command that looks something like this:

At this point, I recommend creating a superuser to make sure that you can write to the database and access the Django admin site:

> python manage.py createsuperuser

…and fill in the prompts.

If you run into permissions errors like this:

psycopg2.ProgrammingError: permission denied for relation django_migrations

It’s because the user you’ve associated with your app doesn’t have access or the right type of access to the tables in the database. To fix this you can go back into your psql terminal and grant the appropriate permissions.

There is an important difference between the two users you have created in this post:

• The user you created in Postgres is a set of credentials to access the database on behalf of your application
• The Django superuser you created in the terminal is an entry in the database; the permissions associated with that user enable you to access the Django admin site

Once your superuser is created, try running your site to make sure the updates worked.

Start your server:

python manage.py runserver

Open the site in your browser:

http://localhost:8000

Navigate to the admin panel and sign in with your superuser credentials:

http://localhost:8000/admin

Try reading user data and creating a new user.

THAT’S IT! 🎉

Thanks for reading! If you have any questions or corrections, please leave them in the comments below or add an issue in the Github repo.

Edit (24 June 2017): If you want to take the next step and deploy your app, check out the next post: 9 Straightforward Steps for Deploying your Django App with Heroku!