PostgreSQL Introduction

🐘 What is PostgreSQL?

PostgreSQL is a powerful, open-source relational database system. It's highly reliable, feature-rich, and perfect for Django applications requiring robust data storage and complex queries.


# Django settings for PostgreSQL
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'mydb',
    }
}

Key PostgreSQL Features

🔒

ACID Compliant

Ensures data reliability and integrity

BEGIN;
UPDATE accounts SET balance = balance - 100;
COMMIT;

📊

Advanced Queries

Complex SQL operations supported

SELECT * FROM users 
WHERE age > 18 
ORDER BY name;

🔗

Relationships

Foreign keys and table relations

CREATE TABLE orders (
  user_id INT REFERENCES users(id)
);

⚡

Performance

Fast queries and indexing

CREATE INDEX idx_email 
ON users(email);

🔹 Why Use PostgreSQL with Django?

PostgreSQL offers several advantages for Django projects. It provides advanced features like JSON fields, full-text search, and array fields that Django can utilize, making it ideal for modern web applications.

Benefits:

Django Support: First-class integration with Django ORM
Data Types: JSON, arrays, and custom types
Scalability: Handles large datasets efficiently
Open Source: Free and community-supported
Security: Built-in authentication and encryption

🔹 PostgreSQL vs SQLite

Understanding the differences helps you choose the right database. SQLite is great for development and small projects, while PostgreSQL excels in production environments with multiple users and complex data requirements.

SQLite (Django Default):

File-based database
Good for development and testing
Limited concurrent users
No separate server needed

PostgreSQL (Production Ready):

Client-server database
Excellent for production
Handles many concurrent users
Advanced features and performance

🔹 Installing PostgreSQL

Install PostgreSQL on your local machine for development. The installation process varies by operating system, but all provide a database server and command-line tools for management.

# Ubuntu/Debian
sudo apt update
sudo apt install postgresql postgresql-contrib

# macOS (using Homebrew)
brew install postgresql

# Windows
# Download installer from postgresql.org

# Verify installation
psql --version

🔹 Basic PostgreSQL Commands

Learn essential commands to interact with PostgreSQL. These commands help you create databases, manage users, and perform basic operations through the PostgreSQL command-line interface.

# Access PostgreSQL shell
sudo -u postgres psql

# Create a database
CREATE DATABASE mydb;

# List all databases
\l

# Connect to a database
\c mydb

# List all tables
\dt

# Exit PostgreSQL shell
\q

🔹 Configure Django for PostgreSQL

Update your Django settings to use PostgreSQL instead of SQLite. You'll need to install the psycopg2 package and configure the database connection with your PostgreSQL credentials.

# Install PostgreSQL adapter for Python
pip install psycopg2-binary

# settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'mydb',
        'USER': 'myuser',
        'PASSWORD': 'mypassword',
        'HOST': 'localhost',
        'PORT': '5432',
    }
}

🔹 Create Your First Table

Django's ORM automatically creates PostgreSQL tables from your models. Define a model in Django, run migrations, and Django handles all the SQL commands to create the corresponding database table.

# models.py
from django.db import models

class User(models.Model):
    name = models.CharField(max_length=100)
    email = models.EmailField(unique=True)
    created_at = models.DateTimeField(auto_now_add=True)
    
    def __str__(self):
        return self.name

# Run migrations to create table
python manage.py makemigrations
python manage.py migrate