PostgreSQL & The Language of Data

Module 1: The World of Databases

Choosing the Right Tool for the Job

Why We Need a Database

Our in-memory array was a great learning tool, but it had a fatal flaw: the data was not persistent. When the server restarts, all data is lost.

A database provides persistent storage, ensuring our data survives restarts, crashes, and deployments.

The Great Divide: SQL vs. NoSQL

There are two major families of databases.

SQL (Relational Databases)

Data is stored in tables with rows and columns. A strict schema defines the structure. Relationships between tables are enforced with keys.
Analogy: A well-organized spreadsheet or filing cabinet.

Examples: PostgreSQL, MySQL, SQL Server

NoSQL (Non-relational Databases)

A broad category of databases that do not use the traditional table structure. Data can be stored as documents (JSON), key-value pairs, or graphs. Schemas are flexible or non-existent.
Analogy: A folder of miscellaneous documents or a flexible graph.

Examples: MongoDB, Redis, Cassandra

Why Start with SQL & PostgreSQL?

For most applications with structured data (users, products, orders), a relational database is the correct, reliable choice.

• Data Integrity: The strict schema ensures your data is clean and consistent.
• Powerful Queries: SQL (Structured Query Language) is an incredibly powerful and expressive language for complex data retrieval.
• Maturity: Decades of computer science have gone into making relational databases reliable and performant.

PostgreSQL is the world's most advanced open-source relational database. It is powerful, extensible, and trusted by companies of all sizes.

Setting Up PostgreSQL

We will install PostgreSQL and a GUI client to interact with it.

Installation:

                        # macOS (with Homebrew)
                        brew install postgresql

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

GUI Client:

We recommend DBeaver (cross-platform) or Postico (macOS). A GUI makes it much easier to visualize your tables and data.

Module 2: DDL - Architecting the Schema

Defining the Blueprint of Your Data

SQL Sublanguages

SQL is composed of several sublanguages. We will focus on two today:

• DDL (Data Definition Language): For defining and managing the database structure (the tables, columns, and constraints).
• DML (Data Manipulation Language): For creating, reading, updating, and deleting the data *within* the tables.

CREATE TABLE

This is the primary DDL command. It defines a new table, its columns, and their data types.

                        CREATE TABLE users (
                            id SERIAL PRIMARY KEY,
                            username VARCHAR(50) NOT NULL UNIQUE,
                            email VARCHAR(255) NOT NULL UNIQUE,
                            password_hash VARCHAR(255) NOT NULL,
                            created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
                        );
                    

Data Types & Constraints

Each column has a data type and can have constraints.

• `SERIAL`: An auto-incrementing integer. Perfect for primary keys.
• `VARCHAR(n)`: A variable-length string with a maximum length of `n`.
• `TIMESTAMP`: Stores a date and time.
• `PRIMARY KEY`: Uniquely identifies each row in the table. Must be unique and not null.
• `NOT NULL`: Ensures a column cannot have a null value.
• `UNIQUE`: Ensures that all values in a column are different from each other.
• `DEFAULT`: Provides a default value for a column when none is specified.

Establishing Relationships with FOREIGN KEY

Relational databases are powerful because they model relationships. A foreign key is a column (or columns) that refers to the primary key of another table.

                        CREATE TABLE posts (
                            id SERIAL PRIMARY KEY,
                            title VARCHAR(255) NOT NULL,
                            content TEXT,
                            -- This column will store the ID of the user who wrote the post
                            user_id INTEGER NOT NULL,

                            -- This constraint links user_id to the id column in the users table
                            FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
                        );
                    

`ON DELETE CASCADE` means that if a user is deleted, all of their posts will be automatically deleted too. This maintains data integrity.

ALTER TABLE

Used to modify an existing table structure.

                        -- Add a new column to the users table
                        ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT true;

                        -- Remove a column
                        ALTER TABLE users DROP COLUMN is_active;

                        -- Add a constraint
                        ALTER TABLE users ADD CONSTRAINT username_length_check CHECK (char_length(username) > 3);
                    

Module 3: DML - Manipulating the Data

Bringing Your Schema to Life

INSERT: Creating Data

The `INSERT INTO` statement is used to add new rows of data into a table.

                        INSERT INTO users (username, email, password_hash)
                        VALUES ('alex_architect', 'alex@example.com', 'some_bcrypt_hash_here');

                        INSERT INTO posts (title, content, user_id)
                        VALUES ('My First Post', 'This is the content of my first post.', 1);
                    

Note that we don't provide the `id` or `created_at` values; the database handles those automatically because of `SERIAL` and `DEFAULT`.

SELECT: Reading Data

The `SELECT` statement is used to query the database and retrieve data.

                        -- Select all columns from the users table
                        SELECT * FROM users;

                        -- Select only specific columns
                        SELECT username, email FROM users;

                        -- Use the WHERE clause to filter rows
                        SELECT * FROM users WHERE id = 1;

                        SELECT * FROM posts WHERE user_id = 1 AND title = 'My First Post';
                    

UPDATE: Modifying Data

The `UPDATE` statement is used to modify existing records in a table.

The `WHERE` clause is CRITICAL. If you forget it, you will update every single row in the table.

                        UPDATE users
                        SET email = 'alex.arch@newdomain.com'
                        WHERE id = 1;
                    

DELETE: Removing Data

The `DELETE` statement is used to remove existing records from a table.

Like `UPDATE`, the `WHERE` clause is CRITICAL. If you omit it, you will delete all data in the table.

                        DELETE FROM posts
                        WHERE id = 1;