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 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:

...

from pathlib import Path
# Add these 4 lines of code...
import os

import environ
environ.Env()
environ.Env.read_env()

...

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 secrets in the .env:

DB_USER=neon_username
DB_PW=neon_password
DB_HOST=neon_host

The three values to use come from Neon connection string...

Using the Database with Django

Copy the provided codeblock and replace the DATABASES dictionary in settings.py, e.g.:

DATABASES = {
    'default': {
      'ENGINE': 'django.db.backends.postgresql',
      'NAME': 'catcollector',
      'USER': os.environ['DB_USER'],
      'PASSWORD': os.environ['DB_PW'],
      'HOST': os.environ['DB_HOST'],
      'PORT': '5432',
    }
}

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>"

   Be sure to surround the connection string with double-quotes

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

• MacOS or WSL/Linux with Homebrew installed:

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

  If you see a message like "If you need to have libpq first in your PATH, run:", run the given command, e.g.:

  Example (use your actual cmd):  echo 'export PATH="/opt/homebrew/opt/libpq/bin:$PATH"' >> ~/.zshrc
  

• WSL (Windows) without Homebrew:

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