Postgresql and Django Quickstart Guide

While SQLite is great for getting a basic Django project up and running, it has some limitations when it comes to concurrency and user management. For example, only one process can make changes to the database at any given moment, and having multiple users with different permissions is impossible.  When SQLite is not enough, check out how to quickly get set up with PostgreSQL.

1. Download and install psql for your system

• For windows, download from the PostgreSQL website and follow wizard
  • Notes: you may need to add Postgres to environment variables
• On Ubuntu, use apt-get:

sudo apt update
sudo apt install postgresql postgresql-contrib

2. Create new user and database in the SQL Shell

• Make sure to include ";" at the end of commands
• PSQL comes with user "postgres" by default. However, we will need to change this to use the database since the default user is reserved for administrative privileges. Login as "postgres" user and create a new user with the following command (replacing "username" and "jws8s0F4" with the appropriate values":

psql -U postgres

# after logged in as postgres
CREATE USER username WITH PASSWORD 'jw8s0F4';

• create a new database(ex: "exampledb") and grant permission to the new user:

CREATE DATABASE exampledb;
GRANT ALL PRIVILEGES ON DATABASE "exampledb" TO username;

• Sign out by typing "exit" or entering the following command:

\q

3. Log in as a new user and check database connection

• Log in as the newly created user:

psql -U username

• Test Database with the "\c" command

\c exampledb

• You should see that you are connected to the database with your new user account

4. Install psycopg2-binary and update database settings

pip install psycopg2-binary

• In the example below, values are secured using python decouple:

# make sure to pip install psycopg2-binary
#in settings.py

DATABASES = {
     'default': {
        'ENGINE': 'django.db.backends.postgresql', #some guides include the psycog2 part in the name
        'NAME': config('DB_NAME'),  #includes python decouple
        'USER': config('DB_USER'),
        'PASSWORD': config('DB_PASSWORD'),
        'HOST':'localhost',
        'PORT': '5432',
    }
}

5.Migrate database

python3 manage.py makemigrations
python manage.py migrate

If you can successfully migrate your database, then setup is complete.  If unable to migrate, ensure all database configurations are correct and no typos exist.