neon-postgresql.md

Using Neon for PostgreSQL Hosting

Creating a Database

1. Sign up at https://neon.tech/.

2. You get one free "project" which can contain multiple "branches" which can contain multiple databases. Name your project something like "sei" and stick with the default "main" branch.

3. Click the Database dropdown.

4. Enter the name of the database, e.g., "catcollector", then click the Create button.

Using the Database with Django

1. Select the newly created database in the dropdown, then click the "Code examples" link.

2. Select "Django", then copy the provided codeblock and replace the DATABASES dictionary in settings.py, e.g.:

   DATABASES = {
     'default': {
       'ENGINE': 'django.db.backends.postgresql',
       'NAME': 'catcollector',
       'USER': 'jim.clark',
       'PASSWORD': '<% PASSWORD %>',
       'HOST': 'ep-broad-pond-085511.us-west-2.aws.neon.tech',
       'PORT': '5432',
     }
   }

3. The value for the 'USER', 'PASSWORD' and 'HOST' items will likely come from a .env file and thus those entries would need to be updated to something like:

     'USER': os.environ['DB_USER'],
     'PASSWORD': os.environ['DB_PW'],
     'HOST': os.environ['DB_HOST'],

4. The actual password can be obtained by copying the “Direct connection” string and extracting just the password section between the : and the @ symbols, e.g.:

Using a .env File

Just like in Express, we will use a .env file to hold "secret" KEY=VALUE pairs.

Install and Configure django-environ

First, install django-environ:

pip3 install django-environ

Next,

We will add the code in settings.py that will "process" the .env file that we'll create coming up:

...

import os

from pathlib import Path

# Add these 3 lines of code
import environ
environ.Env()
environ.Env.read_env()

...

IMPORTANT Note Regarding Syncing With a Repo Such as catcollector

Since settings.py will be "reset" after syncing with a code-along repo, the above updates to settings.py will need to be made immediately after syncing.

Create the .env File

It's IMPORTANT to create the .env file within the nested project settings folder, not the root of the project.

For example, this is the path to the catcollector's .env file:

~/code/catcollector/catcollector/.env

Notice how it's inside of the second catcollector folder?

Go ahead and create the .env file in the correct folder!

Add the DB_USER, DB_PW & DB_HOST

These three lines within DATABASES in settings.py...

DATABASES = {
  ...
  'USER': os.environ['DB_USER'],
  'PASSWORD': os.environ['DB_PW'],
  'HOST': os.environ['DB_HOST'],
  ...
}

...rely on DB_USER, DB_PW & DB_HOST entries in the .env file, for example:

DB_USER=yourusername
DB_PW=yourpassword
DB_HOST='ep-broad-pond-086521.us-west-2.aws.neon.tech'

The three values to use come from Neon (see the "Using the Database with Django" section above).

Resetting a Neon Database for a Django Project

There may come a time when the database and Django migrations get so far out of sync that it's just best to start with a fresh database and re-create the migrations...

Important: Following the below instructions will result in all existing data being lost.

1. Delete the current database in Neon.
2. Re-create the database.
3. Delete ALL of the files and folders within the migrations folder EXCEPT for __init__.py.
4. Ensure that the models in models.py are defined as you want them at this point in time.
5. Make the migrations --> python3 manage.py makemigrations
6. Migrate all of the pending migrations --> python3 manage.py migrate
7. Create your super user --> python3 manage.py createsuperuser

You should be able to create some data now!

Note About Interacting with the Database in the Django Shell

Please note that when using a Neon hosted database within a Django shell, a connection timeout error may occur after 5 minutes of inactivity.

When this happens, running the query again may work, but if not, quitting and returning to the shell certainly will.

Note that this timeout issue will not occur in running Django projects due to the fact that Django automatically reconnects to closed database connections, whereas the shell does not.

Using the Database with psql

1. Copy the "Direct connection" string in Neon's dashboard.

2. In terminal...

   npx psql <paste the connection string>

If using npx didn't work out, the psql client can be installed locally as follows:

• MacOS

  brew install libpq
  brew link --force libpq        <-- Note: Ignore any error message

• WSL (Windows)

  sudo apt-get update
  sudo apt-get install postgresql-client