How to setup Postgres Database for Django projects?

Posted on Mon 02 September 2019 in Django

Postgres DB for Django projects

By default, a Django project is configured to use SQLite database at the backend. However, SQLite is not suitable for production. In this post, I am going to show you how to setup a Postgres database backend for your Django projects. I will build a demo app (mymoviesdb) and explain the steps involved in bringing up the database.

Steps to bring up the Postgres database

  1. Install Postgres server and enable it as a system service
  2. Create Database and User for Django project
  3. Enable Django to talk to Postgres
  4. Install Django and Python-Postgres Database Adapter
  5. Create the Django Project and mymoviesdb App
  6. Code app model and do the Django migration.

Install Postgres server and enable it as a system service

Following are the steps for installing the Postgres server on CentOS. However, these steps could be easily adapted for other Linux distributions as well.

Install Postgres server and dependencies

# sudo yum install postgresql-server postgresql-contrib

Initialize Postgres database and start the database server

# sudo postgresql-setup initdb
# sudo systemctl start postgresql

Configure Postgres server service to start on boot (Optional)

# sudo systemctl enable postgresql

Create Database and User for Django project

By default, Postgres installation will create a Linux user named postgres to access the database software. Using this Linux user, we will create a database and Django user for working with our app.

Note: The Linux user (postgres) and Django user are different.

Switch to postgres user

# sudo -u postgres -i

Note: The prompt might change as you execute the above command and that is okay.

Login to the Postgres client shell as the Linux user: postgres

-bash-4.2$ psql postgres
psql (9.2.24)
Type "help" for help.

postgres=#

The above command will give you a shell for executing database commands.

Execute the following commands on the client shell:

postgres=# CREATE DATABASE mymoviesdb;
postgres=# CREATE USER mymoviesdb; 
postgres=# GRANT ALL ON DATABASE mymoviesdb to "mymoviesdb"; 
postgres=# ALTER USER mymoviesdb PASSWORD 'development'; 
postgres=# ALTER USER mymoviesdb CREATEDB;
postgres=# \q
-bash-4.2$ exit

The above SQL statements will create the database (mymoviesdb) and user(mymoviesdb) for our Django project. The GRANT statement ensures that our mymoviesdb user will have access to the database. Then, we set a password on the mymoviesdb user. Finally, we give the mymoviesdb user permission to create new databases, which will be used by Django to create a test database when running tests.

Enable Django to talk to Postgres

Right now, Postgres is allowed to accept connections only from localhost and not from applications like Django. To allow Django to talk to Postgres, do the following:

Modify ident to md5 in /var/lib/pgsql/data/pg_hba.conf for ipv4 and ipv6 connections like so:

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5 
# IPv6 local connections:
host    all             all             ::1/128                 md5

Then restart the Postgres server service

# sudo systemctl restart postgresql

Install Django and Python-Postgres Database Adapter

Create a project directory for our app on desktop( or your desired location)

# mkdir ~/myMoviesDB
# cd ~/myMoviesDB

Install the requirements

# pipenv install django psycopg2

psycopg2 is Python’s Postgres Library for working with Postgres databases.

Activate the virtual environment

# pipenv shell

Create the Django Project and mymoviesdb App

Create the Django project

# django-admin startproject mymoviesdb_project .

Note: Don’t forget the dot(.) at the end of the above command

Configure database settings

Open up mymoviesdb_project/settings.py Find the line in settings.py that starts with DATABASES. By default, it will look like this:

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

To use Postgres, change the preceding code to the following one:

DATABASES = {  
  'default': {    
      'ENGINE': 'django.db.backends.postgresql',   
      'NAME': 'mymoviesdb', 
      'USER': 'mymoviesdb',
      'PASSWORD': 'development',
      'HOST': '127.0.0.1',
      'PORT': '5432',
  }
}

'ENGINE': 'django.db.backends.postgresql' – This tells Django to use the Postgres backend. This in turn uses psycopg2.

Note: Make sure the values of ‘NAME’, ‘USER’ and ‘PASSWORD’ matches with values we used while creating the database in step #2

Make mymoviesdb app

# python manage.py startapp mymoviesdb

Install mymoviesdb app, by updating INSTALLED_APP in settings.py

INSTALLED_APPS = [ 
   'django.contrib.admin',
   'django.contrib.auth',
   'django.contrib.contenttypes',
   'django.contrib.sessions', 
   'django.contrib.messages',
   'django.contrib.staticfiles',


   'mymoviesdb.apps.MymoviesdbConfig',  # our app
 ]

Code app model and do the Django migration.

Adding model - Movie

Edit mymoviesdb/models.py

from django.db import models


class Movie(models.Model):
    title = models.CharField(max_length=140) 
    plot = models.TextField()
    year = models.PositiveIntegerField()
    runtime = models.PositiveIntegerField() 

    def __str__(self): 
        return f'{self.title} - ({self.year})'

Let's see how the database looks like before migration. We can use the dbshell command to enter into the database shell using Django.

# python manage.py dbshell
psql (9.2.24)
Type "help" for help.

mymoviesdb=> \dt
No relations found.
mymoviesdb=> \q

As you can see, there are no tables created yet. Let's do the migrations to create the tables.

Migrating the database

# python manage.py makemigrations mymoviesdb
# python manage.py migrate

Postgres database after migration

# python manage.py dbshell
psql (9.2.24)
Type "help" for help.

mymoviesdb=> \dt
                    List of relations
 Schema |            Name            | Type  |   Owner    
--------+----------------------------+-------+------------
 public | auth_group                 | table | mymoviesdb
 public | auth_group_permissions     | table | mymoviesdb
 public | auth_permission            | table | mymoviesdb
 public | auth_user                  | table | mymoviesdb
 public | auth_user_groups           | table | mymoviesdb
 public | auth_user_user_permissions | table | mymoviesdb
 public | django_admin_log           | table | mymoviesdb
 public | django_content_type        | table | mymoviesdb
 public | django_migrations          | table | mymoviesdb
 public | django_session             | table | mymoviesdb
 public | mymoviesdb_movie           | table | mymoviesdb
(11 rows)

Now we see that the migrations have created the table mymoviesdb_movie for our app model(Movie). It has also created the tables for the Django's built-in apps.

This completes the Postgres setup for our Django app. Our app is now using the Postgres database for working with its database requirements. You could go ahead and create more models and do the database migrations as required.

Conclusion

Setting up Postgres database could be tricky and time consuming. In this post, I hope I have made it a little easy for you to setup your next Django project with Postgres as the database backend.

That's it readers, until next time. Happy coding Python and Django!

Django Python Postgres