26
Connecting Django to PostgreSQL on Heroku and Perform SQL Command
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.
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
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
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.
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.
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.
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
With the Postgres database attached and enabled on Heroku, it's time to connect it with our Django app.
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_urlDatabase 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
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.
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 psqlIf 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-> ('[email protected]','123abc','Andi'),
vlnx-hobby::DATABASE-> ('[email protected]','456abc','Peko'),
vlnx-hobby::DATABASE-> ('[email protected]','789abc','Krow');
INSERT 0 3
vlnx-hobby::DATABASE=>
vlnx-hobby::DATABASE=> INSERT INTO MESSAGES VALUES
vlnx-hobby::DATABASE-> ('1','[email protected]','Hello World','This is my first message'),
vlnx-hobby::DATABASE-> ('2','[email protected]','Second Message','This is my second message'),
vlnx-hobby::DATABASE-> ('3','[email protected]','ATTN','Qapla'),
vlnx-hobby::DATABASE-> ('4','[email protected]','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
----------------------+----------+-------
[email protected] | 123abc | Andi
[email protected] | 456abc | Peko
[email protected] | 789abc | Krow
(3 rows)
vlnx-hobby::DATABASE=>You can test out other SQL commands here. Now let's start playing with the 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.
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.
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'] = valueMost 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.
<!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 [email protected]
<!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.
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
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.
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/
26