DJ-Postgres-cheatsheet

In this Readme file , you will learn how to integrate a django server to a Postgresql database in the traditional and authentic way. For this we have to set the attributes of the POSTGRESQL database engine, so let's start

Installing psycopg ( an adapter to connect postgresql with any sql supported backend server )

This gives the updated version of Psycopg Driver (For Windows / For Linux - If You have Python3 (3.10<= Version <=3.11 , Python3.12 doesnot have the support)

  pip install psycopg[binary,pool]

For Linux Based Distros

  pip install psycopg2-binary

Now we have to create a DJANGO PROJECT/APP structure , let's do this by these following commands :

  django-admin startproject proj_name

For benificial purposes , keep the contents of the current project folder outside of it , then

  python manage.py startapp app_name

In PSQL shell------

❗ ❗ Under the postgres (parent database) database

  CREATE DATABASE db_name;

  CREATE USER username WITH PASSWORD 'password';

Now switch to the newly created database

  \c db_name

❗ ❗ Under the new database db_name

First of all , we have to create a new schema inside the new database

  CREATE SCHEMA schema_name AUTHORIZATION username

Then, we have to apply some commands to set the client-encoding parameter , timezone etc https://docs.djangoproject.com/en/4.2/ref/databases/#postgresql-notes

(In future this docs may be invalid deu to upgraded versions of django)

  ALTER ROLE username SET client_encoding TO 'utf8';

  ALTER ROLE username SET default_transaction_isolation TO 'read committed';

  ALTER ROLE username SET timezone TO 'UTC';

Now we have to provide the search_path for the new database

  ALTER ROLE username IN DATABASE db_name set search_path=schema_name ;

  \c postgres

Now we have to provide all priviledges to our new db (Under postgres)

  GRANT ALL PRIVILEGES ON DATABASE database TO username;

❗ ❗ Inside the SETTINGS.PY of the django PROJECT folder

    DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'db_name',
        'USER': 'username',
        'PASSWORD': 'password',
        'HOST': '127.0.0.1',
        'PORT': '5432',
    }
  }

Now we have to migrate all the tables to our postgres database

  python manage.py makemigrations

  python manage.py migrate

To see the changes and the default django tables inside the Postgre database , RESTART the Psql shell and Enter Into the new database from root (Not from parent database 'postgres' , just when you start - write db_name, username, password and enter)

For Linux Based Distros

  sudo -i -u postgres

  psql -h <host> -U <user> -d <database_name>

  \dt

For Linux Based Distros

  SELECT * FROM SELECT * FROM pg_catalog.pg_tables; (To see all tables in all databases under root user)

  SELECT table_name FROM information_schema.tables
  WHERE table_name = '<table_name>'; (To see if any table at that name exists)

  SELECT * FROM "table_name" ; (Sometimes without "" , data will not be shown)