Connecting Django to PostgreSQL on Heroku and Perform SQL Command

Stefanus Ndaru Wedhatama — Mon, 29 Nov 2021 13:40:50 +0000

IMPORTANT NOTICE

Update as per 10 October 2022

Since Heroku has changed their policy for free plans, some part of this tutorial might be obsolete since this tutorial relies on their free plan. Please proceed whilst making adjustment based on this information.

Introduction and Disclaimer

This simple tutorial is aimed mainly for people who wants to try to connect their deployed Django web application on Heroku to Heroku's Postgres Database add-ons, and to perform raw SQL commands.

The original aim was for Universitas Indonesia's Faculty of Computer Science students who are currently taking the Database (Basis Data) course, which, if nothing changed on the curriculum, has an assignment which requires you to create a web-app using Django on Heroku and perform raw SQL command without using ORM (Django's Models for example)

OS used will be Windows 10.
If you're using Linux or MacOS, I'm sorry since I don't have any of those devices to test the commands :(

The reader is expected to have some basic knowledge of GitHub/GitLab, Heroku, Django, PostgreSQL and Web Development (HTML, CSS, Deployment). The reader is also expected to have PostgreSQL, Python, and other necessary components such as Git and Heroku CLI installed on the computer to run the necessary commands.

This tutorial is in no way used for official web production and is aimed purely for educational or trying-out purposes.

There is NO guarantee that this is the correct way.

Expect some mistakes here and there and I do not take any responsibility for issues such as SQL injections

With that out of the way, let's begin the tutorial

Step One - Deployment

The main purpose of the tutorial is to connect and perform the SQL commands, not deploy the website, but the deployment is really important so I'll give one of my most recommended deployment guide I found by Gani Ilham Irsyadi. You can try them out yourself.

The rest of the guide will follow from the Deployment Guide given above

If you followed the tutorial above, your project structure should look like this

(except for the env which I've placed inside the project and I also changed the project name)

And the deployed website should look something like this

Step Two - Adding PostgreSQL Database to Heroku and Testing the Database

In this step, we're now going to enable the PostgreSQL on the Heroku App and check if we've managed to have it installed.

1. Go towards your app's dashboard and click on Resources and check if you have PostgreSQL or not

If you've followed the previous tutorial, you should have PostgreSQL already enabled like in the example below

But if you don't have it installed, proceed to number 2.

2. If you don't have PostgreSQL installed, install it first.

Click on the "Find more add-ons"

Select Heroku Postgres

and then Install Heroku Postgres

Select the plan which you want to use (should be using Hobby Dev as it is free) and select the app you want to choose.

(I'm using another app as an example here, hence why the app name is different).

After that, click on the Submit Order Form.

Now you should have Postgres attached like in the example above in number 1.

3. Open Command Prompt and Test the Database.

All commands shown below will be run in Command Prompt

On your command prompt, login using Heroku CLI

heroku login

After you've logged in, connect to your Heroku app using the command below

heroku git:remote -a herokuappname

In this case, my app name is vlnx-hobby

Open Postgres for your Heroku app

heroku psql

If all done correctly, you should be able to go into this screen

Whoops there I forgot to set my Windows code page, hence why the Warning appeared. If you also encountered this, when opening command prompt for the first time, type in the code below before doing anything since it will mess up your queries
chcp 1252

Let's test it out by checking our Django's public schema containing the necessary tables for Django to work

\d

If this appeared, everything's going well and you're good to go towards the next step. This terminal will be crucial later on. For now, let's exit the database.

exit

Step Three - Connecting Django with Database

With the Postgres database attached and enabled on Heroku, it's time to connect it with our Django app.

1. Open the Heroku Postgres database on your Heroku app Dashboard

2. Copy the Database URI

3. Open up your Django project's settings.py

4. Update Settings.py

Import dj_database_url so you can access database stuff.
(If you've followed the deployment guide, this should already be installed)

### ADD THIS ###
import dj_database_url

Database information. Add and or update this section on the DATABASE part of your settings.py.

### ADD THIS ###
DATABASE_URL = 'postgres://<insert your URI code>'

### CHANGE THIS ###
DATABASES = {
    'default': dj_database_url.config(),
}

### ADD THIS ###
DATABASES['default'] = dj_database_url.config()
DATABASES['default'] = dj_database_url.config(default=DATABASE_URL)

Your Settings.py should look something like this. Changes made from the original settings.py will be marked using triple-hashtag comments

### COMMENT ###

The settings.py is shown below.

"""
Django settings for vlnxHobby project.

Generated by 'django-admin startproject' using Django 3.2.7.

For more information on this file, see
https://docs.djangoproject.com/en/3.2/topics/settings/

For the full list of settings and their values, see
https://docs.djangoproject.com/en/3.2/ref/settings/
"""

from pathlib import Path

### ADD THIS ###
import dj_database_url

import os

# Build paths inside the project like this: BASE_DIR / 'subdir'.
BASE_DIR = Path(__file__).resolve().parent.parent

PRODUCTION = os.environ.get('DATABASE_URL') != None


# Quick-start development settings - unsuitable for production
# See https://docs.djangoproject.com/en/3.2/howto/deployment/checklist/

# SECURITY WARNING: keep the secret key used in production secret!
SECRET_KEY = 'secret'

# SECURITY WARNING: don't run with debug turned on in production!
DEBUG = True

ALLOWED_HOSTS = ['*']


# Application definition

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

MIDDLEWARE = [
    'django.middleware.security.SecurityMiddleware',
    'django.contrib.sessions.middleware.SessionMiddleware',
    'django.middleware.common.CommonMiddleware',
    'django.middleware.csrf.CsrfViewMiddleware',
    'django.contrib.auth.middleware.AuthenticationMiddleware',
    'django.contrib.messages.middleware.MessageMiddleware',
    'django.middleware.clickjacking.XFrameOptionsMiddleware',
    'whitenoise.middleware.WhiteNoiseMiddleware',
]

ROOT_URLCONF = 'vlnxHobby.urls'

TEMPLATES = [
    {
        'BACKEND': 'django.template.backends.django.DjangoTemplates',
        'DIRS': [],
        'APP_DIRS': True,
        'OPTIONS': {
            'context_processors': [
                'django.template.context_processors.debug',
                'django.template.context_processors.request',
                'django.contrib.auth.context_processors.auth',
                'django.contrib.messages.context_processors.messages',
            ],
        },
    },
]

WSGI_APPLICATION = 'vlnxHobby.wsgi.application'


# Database
# https://docs.djangoproject.com/en/3.2/ref/settings/#databases

### ADD THIS ###
DATABASE_URL = 'postgres://<insert your URI code>'

### CHANGE THIS ###
DATABASES = {
    'default': dj_database_url.config(),
}

### ADD THIS ###
DATABASES['default'] = dj_database_url.config()
DATABASES['default'] = dj_database_url.config(default=DATABASE_URL)

if PRODUCTION:
    DATABASES['default'] = dj_database_url.config()

# Password validation
# https://docs.djangoproject.com/en/3.2/ref/settings/#auth-password-validators

AUTH_PASSWORD_VALIDATORS = [
    {
        'NAME': 'django.contrib.auth.password_validation.UserAttributeSimilarityValidator',
    },
    {
        'NAME': 'django.contrib.auth.password_validation.MinimumLengthValidator',
    },
    {
        'NAME': 'django.contrib.auth.password_validation.CommonPasswordValidator',
    },
    {
        'NAME': 'django.contrib.auth.password_validation.NumericPasswordValidator',
    },
]


# Internationalization
# https://docs.djangoproject.com/en/3.2/topics/i18n/

LANGUAGE_CODE = 'en-us'

TIME_ZONE = 'UTC'

USE_I18N = True

USE_L10N = True

USE_TZ = True


# Static files (CSS, JavaScript, Images)
# https://docs.djangoproject.com/en/3.2/howto/static-files/

STATIC_URL = '/static/'

# Default primary key field type
# https://docs.djangoproject.com/en/3.2/ref/settings/#default-auto-field

DEFAULT_AUTO_FIELD = 'django.db.models.BigAutoField'

PROJECT_DIR = os.path.dirname(os.path.abspath(__file__))
STATIC_ROOT = os.path.join(PROJECT_DIR, 'static')

Now our Django app should be definitely connected to our Postgres database.

Some few important Notes

As seen from the Database Settings on Heroku, the Credentials are not permanent and from time-to-time will update on itself, changing the URI. My solution right now is static and does not auto-update the URI, or that's what I think. If you suddenly can't connect to the database, the first thing you should do is to check and match the DATABASE_URL on settings.py with the URI on Heroku

Step Four - Performing SQL Commands

Now that we've done our setup, now's the fun part. Performing the raw SQL commands. For real life usage, this is really inconvenient and you should be using Django's libraries of functions. But if you're one of those masochist or is currently a student that are forced to use raw SQL, let's get started. We will not be using any models.py at all for this.

Performing DDL

Remember when I said that connecting to the Postgres database using CLI is crucial? Well we're here now. If you've closed command prompt, connect back by logging in using Heroku CLI, connect to your Heroku app, and enter the database.

heroku login
heroku git:remote -a herokuappname
heroku psql

If you just exit and didn't close the command prompt window, simply do

heroku psql

Now we're connected to the Postgres database. To create the necessary Schemas, Tables, Triggers & Stored Procedures, and others, simply paste them here.

For the rest of the tutorial, I will be using the Schema provided here

The SQL Query is inside the document and below is the example of running the Queries.

(env) D:\Projects\heroku-hobby\vlnxHobby>heroku psql
 »   Warning: heroku update available from 7.53.0 to 7.59.2.
--> Connecting to postgresql-rectangular-05497
psql (14.1, server 13.5 (Ubuntu 13.5-1.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

vlnx-hobby::DATABASE=> CREATE SCHEMA UserSystem;
CREATE SCHEMA
vlnx-hobby::DATABASE=> SET search_path TO UserSystem;
SET
vlnx-hobby::DATABASE=>
vlnx-hobby::DATABASE=> CREATE TABLE USER_PROFILE(
vlnx-hobby::DATABASE(>     email VARCHAR(50),
vlnx-hobby::DATABASE(>     password VARCHAR(50) NOT NULL,
vlnx-hobby::DATABASE(>     fname VARCHAR(50) NOT NULL,
vlnx-hobby::DATABASE(>     PRIMARY KEY (email)
vlnx-hobby::DATABASE(> );
CREATE TABLE
vlnx-hobby::DATABASE=>
vlnx-hobby::DATABASE=> CREATE TABLE MESSAGES(
vlnx-hobby::DATABASE(>     id Varchar(50),
vlnx-hobby::DATABASE(>     email Varchar(50) NOT NULL,
vlnx-hobby::DATABASE(>     title Varchar(50) NOT NULL,
vlnx-hobby::DATABASE(>     content text NOT NULL,
vlnx-hobby::DATABASE(>     PRIMARY KEY (id),
vlnx-hobby::DATABASE(>     FOREIGN KEY (email) REFERENCES USER_PROFILE(email)
vlnx-hobby::DATABASE(>         ON UPDATE CASCADE ON DELETE CASCADE
vlnx-hobby::DATABASE(> );
CREATE TABLE
vlnx-hobby::DATABASE=>
vlnx-hobby::DATABASE=> INSERT INTO USER_PROFILE VALUES
vlnx-hobby::DATABASE->     ('email1@mail.com','123abc','Andi'),
vlnx-hobby::DATABASE->     ('email2@mail.com','456abc','Peko'),
vlnx-hobby::DATABASE->     ('email3@mail.com','789abc','Krow');
INSERT 0 3
vlnx-hobby::DATABASE=>
vlnx-hobby::DATABASE=> INSERT INTO MESSAGES VALUES
vlnx-hobby::DATABASE->     ('1','email1@mail.com','Hello World','This is my first message'),
vlnx-hobby::DATABASE->     ('2','email1@mail.com','Second Message','This is my second message'),
vlnx-hobby::DATABASE->     ('3','email1@mail.com','ATTN','Qapla'),
vlnx-hobby::DATABASE->     ('4','email2@mail.com','My Only Message','This is my only message');
INSERT 0 4
vlnx-hobby::DATABASE=>

We can test out the database by fetching the data from the table USER_PROFILE. Make sure you've set the search_path towards the schema by running the command below.

set search_path to userSystem

Let's try it out.

SELECT * FROM USER_PROFILE;

The result should be like this.

vlnx-hobby::DATABASE=> SELECT * FROM USER_PROFILE;
      email      | password | fname
-----------------+----------+-------
 email1@mail.com | 123abc   | Andi
 email2@mail.com | 456abc   | Peko
 email3@mail.com | 789abc   | Krow
(3 rows)


vlnx-hobby::DATABASE=>

You can test out other SQL commands here. Now let's start playing with the Django views.

Django Views

We will be messing around with the home app from the deployment guide. We'll try to fetch the data and show it into the page.

To do raw SQL query using Django, we will be using the connection cursor form Django. The documentation can be seen here. Let's start setting up the views.

First of all, import this two and add this function to the app's views.py. This function will fetch every row of the query result.

from django.db import connection
from collections import namedtuple

def namedtuplefetchall(cursor):
    "Return all rows from a cursor as a namedtuple"
    desc = cursor.description
    nt_result = namedtuple('Result', [col[0] for col in desc])
    return [nt_result(*row) for row in cursor.fetchall()]

To do the query, we will try to fetch all of the available users and show their names. Edit out the index function to the code below.

def index(request):
    cursor = connection.cursor()
    try:
        cursor.execute("SET SEARCH_PATH TO USERSYSTEM")
        cursor.execute("SELECT FNAME FROM USER_PROFILE")
        result = namedtuplefetchall(cursor)
    except Exception as e:
        print(e)
    finally:
        cursor.close()

    return render(request, 'home/index.html', {'result': result})

Let's take a small dive into the function. I will be explaining it as simple as I can and as far as my understandings go. I do not guarantee correctness.

The connection.cursor() will initiate the cursor and point it towards the database.

cursor = connection.cursor()

cursor.execute() will execute the SQL query that you inputted as the argument. Be sure to always set the search path first or type explicitly the schema of the table.

cursor.execute("SET SEARCH_PATH TO USERSYSTEM")
cursor.execute("SELECT * FROM USER_PROFILE")

The rows of data will then be stored into the result variable using the function we copied above.

result = namedtuplefetchall(cursor)

Personal tip:
You can try printing the result to see what it actually contains to give you a better understanding of the content

After all of the necessary query is done, be sure to close it. For best practices, use Try Except Finally blocks and put the close on the Finally block so it will always be run. Place the return after the Try Except Finally blocks.

cursor.close()

Now let's try printing the queries. Edit the index.html using the template below. We'll be looping the result that we fetched.

<!DOCTYPE html>
<html lang="en">

{% load static %}

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="stylesheet" href="{% static 'home/style.css' %}">
    <title>Hello</title>
</head>
<body>
    <h1>Hello World</h1>
    {% for res in result %}
        <p>Name: {{ res.fname }}</p>
    {% endfor %}
</body>
</html>

And the result can be seen below. We've managed to fetch the data from the database! Congratulations!

Notes:
Sometimes you'll be getting UnboundLocalError. This is because the result variable is inside the Try Except Finally blocks. You can solve this by initiating the variable first above the Try Except Finally blocks.

The finished views.py can be seen here, including the revision for solving the UnboundLocalError

from django.shortcuts import render
from django.db import connection
from collections import namedtuple

# Create your views here.

# Function to return every row of data from query
def namedtuplefetchall(cursor):
    "Return all rows from a cursor as a namedtuple"
    desc = cursor.description
    nt_result = namedtuple('Result', [col[0] for col in desc])
    return [nt_result(*row) for row in cursor.fetchall()]

def index(request):
    cursor = connection.cursor()
    result = []
    try:
        cursor.execute("SET SEARCH_PATH TO USERSYSTEM")
        cursor.execute("SELECT FNAME FROM USER_PROFILE")
        result = namedtuplefetchall(cursor)
    except Exception as e:
        print(e)
    finally:
        cursor.close()

    return render(request, 'home/index.html', {'result': result})

You've managed to setup views, fetch the result, and perform SQL queries. This tutorial only covers the basics, and you should find out more regarding this.

Bonus Materials

Authentication and User System

You might be wondering, if we're not allowed to use Django's Models, how are we going to do user authentication and authorization? Well, the answer is Django Sessions.

Imagine having a temporary key card for your locker at some local gym. That is what sessions are. They help identify who you are, and authorize what you see. Let's try to make a Login page and View Message page so the corresponding user can see their messages.

Let's modify our project. First of all, let's add in forms.py into our app and insert this code inside. This will create our login form.

from django import forms

class LoginForm(forms.Form):
    email = forms.CharField(label='E-Mail Address', max_length=50)
    password = forms.CharField(label='Password', max_length=50)

After that, let's update our app's urls.py

from django.urls import path

from . import views

app_name = 'home'

urlpatterns = [
    path('', views.index, name='index'),
    path('login', views.login, name='login'),
    path('viewMessage', views.loggedInView, name='login'),
    path('logout', views.logout, name='login'),
]

And finally, our views.py

from django.http.response import HttpResponseNotFound, HttpResponseRedirect
from django.shortcuts import render
from django.db import connection
from collections import namedtuple
from .forms import *

# Create your views here.

# Function to return every row of data from query
def namedtuplefetchall(cursor):
    "Return all rows from a cursor as a namedtuple"
    desc = cursor.description
    nt_result = namedtuple('Result', [col[0] for col in desc])
    return [nt_result(*row) for row in cursor.fetchall()]

def index(request):
    cursor = connection.cursor()
    result = []
    try:
        cursor.execute("SET SEARCH_PATH TO USERSYSTEM")
        cursor.execute("SELECT FNAME FROM USER_PROFILE")
        result = namedtuplefetchall(cursor)
    except Exception as e:
        print(e)
    finally:
        cursor.close()

    return render(request, 'home/index.html', {'result': result})

def login(request):

    result = []

    # Define login form
    MyLoginForm = LoginForm(request.POST)

    # Form submission
    if (MyLoginForm.is_valid() and request.method == 'POST'):
        # Get data from form
        email = MyLoginForm.cleaned_data['email']
        password = MyLoginForm.cleaned_data['password']

        # Run SQL QUERY
        try:
            cursor = connection.cursor()
            cursor.execute("SET SEARCH_PATH TO USERSYSTEM")
            cursor.execute("SELECT * FROM USER_PROFILE AS ADM WHERE ADM.email ='" + email + "' AND ADM.PASSWORD = '" + password + "'")
            result = cursor.fetchone()

            if(result == None): 
                return HttpResponseNotFound("The user does not exist")

            # Redirect the cursor towards public so it can access Django basic features
            cursor.execute("SET SEARCH_PATH TO public")
            request.session['email'] = [email, password, result]

        except Exception as e:
            print(e)
            cursor.close()

        finally:
            # Don't forget to close
            cursor.close()

        return HttpResponseRedirect('/viewMessage')

    else:
        MyLoginForm = LoginForm()

    return render(request, 'home/login.html', {'form' : MyLoginForm})

# Function to test logged in result
def loggedInView(request):
   if request.session.has_key('email'):
        cursor = connection.cursor()
        result = []
        try:
            cursor.execute("SET SEARCH_PATH TO USERSYSTEM")
            cursor.execute("SELECT * FROM MESSAGES WHERE EMAIL = '"+ request.session['email'][0] +"'")
            result = namedtuplefetchall(cursor)
        except Exception as e:
            print(e)
        finally:
            cursor.close()

        return render(request, 'home/loggedin.html', {"result" : result})
   else:
        return HttpResponseRedirect('/login')

# Function to log out the user
def logout(request):
   try:
        del request.session['email']
   except:
        pass
   return HttpResponseRedirect('/login')

Let's start breaking this down into pieces we need to explain, and we'll only be looking at the views.

For Login, we used Django's form. We grabbed the email and password, and then execute it as a SQL query to select the corresponding user by matching it with the email and password. We then grab only one result by using

cursor.fetchone()

If there are no corresponding user, then we will throw a HttpResponse indicating that the user does not exist. If the user exists, then we'll create a session for the user. The general format is below.

request.session['key'] = value

Most tutorial website only uses one value, but as you can see above, you can actually store multiple values.

IMPORTANT NOTE
Currently, our cursor is pointed towards our schema which is userSystem. This means that Django is unable to interact with its basic table, and that also means we can't really put anything such as user and session. That is why, after executing the SQL query, I pointed back the cursor towards the public schema so Django can access them back again.

cursor.execute("SET SEARCH_PATH TO public")

After the session is created, the user is then redirected towards viewMessages which triggers the view loggedInView. Here, we check if the user is logged in or not by checking if there exist a session which has the key 'email' which is the current user's email. If not, then we will redirect them to the login page. If there exist a logged in user, then we will fetch all messages that corresponds to the user. Here we again use the session's data to match the query.

cursor.execute("SELECT * FROM MESSAGES WHERE EMAIL = '"+ request.session['email'][0] +"'")

The result can be seen below, including the html template if you want to try them out.

Login Page

<!DOCTYPE html>
<html lang="en">

{% load static %}

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="stylesheet" href="{% static 'home/style.css' %}">
    <title>Hello</title>
</head>
<body>
    <h1>Login Form</h1>

    <form action="" method="post">
        {% csrf_token %}
        {{ form }}
        <input type="submit" value="Submit">
    </form>
</body>
</html>

ViewMessage, Logged in as email1@mail.com

<!DOCTYPE html>
<html lang="en">

{% load static %}

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="stylesheet" href="{% static 'home/style.css' %}">
    <title>Hello</title>
</head>
<body>
    <h1>Hello World</h1>
    {% for res in result %}
        <h3>Title: {{ res.title }}</h3>
        <p>Sender: {{ res.email }}</p>
        <p>Name: {{ res.content }}</p>
    {% endfor %}
</body>
</html>

After that, you can logout by calling the logout view.

del request.session['email']

The session is then deleted, and we will redirect them back to the login page.

That's it from me, hope you learn something and found something useful from here.

Bonus Materials

This tutorial was made by me to help others who are confused as to how to do the Database Course assignment. Created from hours of GSGS (Google Sana, Google Sini), I was also helped by several other guides, linked below if you want to check them out.
Python Error Handling with the Psycopg2 PostgreSQL by ObjectRocket
Django - Sessions by Tutorialspoint
Deploying Django to Heroku: Connecting Heroku Postgres by Bennett Garnet

Final words

Yeah I know, I feel like this is very rushed and scuffed. I did said a lot in the disclaimer though, so hopefully you've read that. If you have any comment or solution or would like to point out severe mistakes, feel free to comment. I'm still learning also, and if you found some wrong code, feel free to comment that also.

GitLab Repository

If you would like to check the code yourself, you can go here.

The link for the website is at http://vlnx-hobby.herokuapp.com/

Forem: Stefanus Ndaru Wedhatama

Connecting Django to PostgreSQL on Heroku and Perform SQL Command

IMPORTANT NOTICE

Update as per 10 October 2022

Introduction and Disclaimer

There is NO guarantee that this is the correct way.

Step One - Deployment

Step Two - Adding PostgreSQL Database to Heroku and Testing the Database

1. Go towards your app's dashboard and click on Resources and check if you have PostgreSQL or not

2. If you don't have PostgreSQL installed, install it first.

3. Open Command Prompt and Test the Database.

Step Three - Connecting Django with Database

1. Open the Heroku Postgres database on your Heroku app Dashboard

2. Copy the Database URI

3. Open up your Django project's settings.py

4. Update Settings.py

Some few important Notes

Step Four - Performing SQL Commands

Performing DDL

For the rest of the tutorial, I will be using the Schema provided here

Django Views

The finished views.py can be seen here, including the revision for solving the UnboundLocalError

Bonus Materials

Authentication and User System

Login Page

ViewMessage, Logged in as email1@mail.com

Bonus Materials

Final words

GitLab Repository