Setting up Django to use Aiven for PostgreSQL®

Django is a fully-featured high-level Python web framework. Read on to see how to get it to store its data in an Aiven for PostgreSQL® database.

Subscribe to RSS

Developers love tools that make their lives easier, and so do I. In this post I'll demonstrate using Django, probably the best known Python web framework, with PostgreSQL®, one of my favourite databases.

As a realistic example of a Django application, I will use Oscar, an e-commerce framework with a ready-made sandbox.
By default Oscar uses SQLite for storage, but I will show how to adapt it to use PostgreSQL instead, using Aiven for PostgreSQL.

Install Oscar

Start by cloning the repository

Normally we'd just clone from Oscar's own repository, as indicated on the sandbox page. However, at the moment there's an issue with the Python requirements for psycopg2, so we'll use a fork where that's fixed. These instructions will be altered when the main repository has the fix.
git clone --branch sandbox-requirements-update \
    https://github.com/TibsAtWork/django-oscar.git

and then

cd django-oscar

Since I am installing new requirements for this project, I use a virtual environment. This stops the packages for this project from interfering with any other Python packages I might have installed.

python3 -m venv venv
source venv/bin/activate

Oscar's Makefile uses npm to pull in the dependencies it needs. On my Mac I install npm using homebrew:

brew install npm

Create the default sandbox installation using

make sandbox

which installs the project dependencies, and sets up a version of the sandbox with example data stored in a local SQLite database.

You may see various deprecation warnings from npm - these can be safely ignored in the context of a sandbox build for local use.

Use the default SQLite setup

With the sandbox set up, run the Django project locally using:

sandbox/manage.py runserver

This starts the development server and indicates its URL, http://127.0.0.1:8000/. When I open a web browser window at that address, I see the Oscar Sandbox store front.

Oscar sandbox screenshot

To prove that the system is working, try "buying" a couple of books, adding them to the basket, checking out, and seeing the order details.

Note

It's not possible to pay for the test purchase since we don't have a payment mechanism set up. Instead, the "Payment" page provides links to some possible implementation choices, and otherwise does nothing.

I can also add an account for myself, setting my address, and see that the account and purchase information persist when I restart the web server, since they are stored in the database.

Oscar database configuration

The sandbox/settings.py file is the central "settings" file for this Django application, and the value for DATABASES specifies how and where to access the application database.

The default is to use SQLite, but the use of os.environ.get allows me to override the default choices by setting environment variables. In each case, the first argument to os.environ.get is the name of an environment variable, and the second argument is the value to use if that environment variable is not set.

# Use a Sqlite database by default
DATABASES = {
    'default': {
        'ENGINE': os.environ.get('DATABASE_ENGINE', 'django.db.backends.sqlite3'),
        'NAME': os.environ.get('DATABASE_NAME', location('db.sqlite')),
        'USER': os.environ.get('DATABASE_USER', None),
        'PASSWORD': os.environ.get('DATABASE_PASSWORD', None),
        'HOST': os.environ.get('DATABASE_HOST', None),
        'PORT': os.environ.get('DATABASE_PORT', None),
        'ATOMIC_REQUESTS': True
    }
}

SQLite stores its database in a single file, in this case sandbox/db.sqlite. We want to use PostgreSQL®, so we will create a database and then configure Oscar to store its data there.

Create a PostgreSQL® database

To create the PostgreSQL database, follow the instructions at the beginning of Getting started with Aiven for PostgreSQL®. My setup uses the following choices:

  • The current default version of PostgreSQL, v14.
  • Google Cloud and location google-europe-north1.
  • For this demo, I choose Service Plan "Hobbyist". This is the minimal plan, which says it is suitable for "small test environments". For production databases you will probably want the more robust options available in the Business or Premium plans.
  • My service needs a name, and "tibs-django-pg" is fairly self-explanatory.

Use PostgreSQL as the backend database

As we saw above, the DATABASES setup in the sandbox/settings.py file has been written to look values up from environment variables.

To change to using a PostgreSQL database, I set the appropriate environment variables in my shell (shown here for Bash):

DATABASE_ENGINE='django.db.backends.postgresql_psycopg2'
DATABASE_NAME='defaultdb'
DATABASE_USER='<USER>'
DATABASE_PASSWORD='<PASSWORD>'
DATABASE_HOST='<HOST>'
DATABASE_PORT='<PORT>'

where <USER>, <PASSWORD>, <HOST> and <PORT> are replaced with the values from the "Connection Information" on the Aiven Console service page for the PostgreSQL service.

I'm using the database name as the default provided by the service; in a real life application, it would be sensible to create a new database with a more appropriate name and different access credentials than the defaults.

Once the Aiven for PostgreSQL service is Running, rebuid the sandbox database with:

make build_sandbox

This runs the sandbox_clean, sandbox_load_user and sandbox_load_data targets:

  • sandbox_clean deletes locally cached items, and deletes the db.sqlite file
  • sandbox_load_user loads the user data into the database, as specified by the new settings - so the PostgreSQL database
  • sandbox_load_data loads the sample product information into the database

Note that it is not performing any migration from the SQLite database, it's just populating our new PostgreSQL database with the same example source data.

Restart the application server

sandbox/manage.py runserver

and again open a browser window at http://127.0.0.1:8000/.

The online shop looks identical to when SQLite was being used.

I can do the same actions as before:

  • buy a couple of books, add them to my basket, check out, and see the order details.
  • create an account, set my address, and then buy a book while logged in

My account information and order history are still remembered when I restart the server.

Inspect the PostgreSQL database

To see how the data is stored in the database, connect to it using the Django dbshell command:

sandbox/manage.py dbshell

which is a convenient way of opening the command line interface to the backing database - in this case, psql.

Typing \d lists the Oscar sandbox tables in defaultdb (with 185 rows in the response).

First I query the auth_users table to check my account details:

SELECT id, username, email FROM auth_user; gave me

 id |            username            |         email
----+--------------------------------+-----------------------
  1 | superuser                      | superuser@example.com
  2 | staff                          | staff@example.com
  3 | j4WAvjzRDGpGTrxnwM_awovzeoTLIL | tibs@example.com
(3 rows)

which shows that my user id is 3.

Looking around to see if I can find the record of my book purchase, I see that SELECT id, status, owner_id FROM basket_basket where owner_id = 3; returns

 id |  status   | owner_id
----+-----------+----------
  1 | Submitted |        3
  2 | Open      |        3
(2 rows)

which shows I "submitted" one checkout basket, with id of 1.

Looking up the basket with SELECT id, quantity, product_id FROM basket_line WHERE basket_id = 1; gives me

 id | quantity | product_id
----+----------+------------
  1 |        1 |        190
(1 row)

Finally,looking up that product by its id, SELECT title FROM catalogue_product WHERE id = 190; shows

           title
----------------------------
 The Cathedral & the Bazaar
(1 row)

which is the name of the book I bought.

Finally, exit psql using the \q' command.

dellstore-> \q

Oscar and Aiven for PostgreSQL

In this post, I explored how to use an Aiven service (Aiven for PostgreSQL) as a backend for a web platform I already knew, Django.
To avoid the lengthy process of setting up my own Django application, I decided to use an existing one, the Oscar sandbox

The setup was not too difficult and I'll definitely consider using this combination again.

Next steps

If you want to learn more about Django itself, the offical Django tutorial is a good place to start.

I also recommend the Django documentation as a reference for anyone working with Django or related projects.

The Oscar documentation has a lot more information about the project. I'm very grateful that they've made the sandbox application available as a quick way of getting a realistic Django project up and running.

Also, check out Aiven for PostgreSQL®, and
if you're not using Aiven services yet, go ahead and sign up for your free trial at https://console.aiven.io/signup.