A Backend Developer's Guide to Databases using PostgreSQL

A Backend Developer's Guide to Databases: From Theory to Practice with PostgreSQL

Welcome! If you're a backend developer, you know that databases are not just a part of the job; they're the bedrock of almost every application you'll build. Understanding how to interact with them efficiently is crucial. This guide will walk you through the essential concepts, from the fundamentals to designing and querying a real-world database using PostgreSQL.

---

Why Do We Need Databases Anyway?

At its core, a database is a system for persisting information. Persistence means storing data so it survives even after the program that created it stops running.

Think about a simple to-do list app. You add tasks, check them off, and when you close and reopen the app, you expect everything to be exactly as you left it. Without persistence, you'd have to start from scratch every single time. That's the magic of a database: it remembers.

The Heart of the Matter: Disk-Based vs. In-Memory

In the context of backend systems, when we say "database," we're typically talking about disk-based databases. This means the data is stored on a hard disk (HDD) or a solid-state drive (SSD).

• Disk Storage (SSDs/HDDs): It's relatively cheap and offers massive capacity. Most laptops have 512GB to 2TB of disk space. This is where traditional databases like PostgreSQL and MongoDB store their data.

• RAM (Main Memory): It's incredibly fast but also much more expensive and has a limited capacity (think 8GB to 64GB in a typical machine). This is where caching systems like Redis store data for lightning-fast access.

The tradeoff is simple:

• Databases prioritize capacity and durability over raw speed.

• Caches prioritize speed over capacity and are used for temporary, frequently accessed data.

What is a DBMS?

A Database Management System (DBMS) is the software that allows us to interact with a database. Storing data isn't enough; we need an efficient way to manage it. A DBMS handles several key responsibilities:

• Data Organization: Structuring data for efficient storage and retrieval.

• Data Access (CRUD): Providing ways to Create, Read, Update, and Delete data.

• Data Integrity: Ensuring the data is accurate, valid, and consistent. For example, ensuring a payment amount is always a number, not a random string.

• Security: Protecting data from unauthorized access.

• Concurrency: Managing simultaneous access and modifications by multiple users without corrupting the data.

Using a text file fails on all these points, especially concurrency. Imagine two users trying to update the same value in a text file at the same time—the last one to save wins, and the other's change is lost. A DBMS solves this with sophisticated concurrency control mechanisms.

---

Choosing Your Weapon: Relational vs. Non-Relational (NoSQL)

Databases primarily fall into two major categories.

Relational Databases (SQL)

Relational databases organize data into tables, which are made up of rows and columns. They have a predefined schema, meaning you must define the structure of the table (its columns and their data types) before you can insert data.

• Key Features: Structured data, strong data integrity, and relationships between tables are defined using foreign keys.

• Language: SQL (Structured Query Language) is used to interact with them.

• Examples: PostgreSQL, MySQL, SQL Server.

• Best Use Case: Systems where data consistency and integrity are critical, like a Customer Relationship Management (CRM) or an e-commerce platform where order and payment details must be accurate.

Non-Relational Databases (NoSQL)

Non-relational databases are more flexible. They store data in various formats, such as documents (often JSON-like), key-value pairs, or graphs. They do not require a predefined schema.

• Key Features: Flexible schema, horizontal scalability, and speed for certain types of operations.

• Examples: MongoDB (document), Redis (key-value), Neo4j (graph).

• Best Use Case: Systems where the data structure is varied or evolves rapidly, like a Content Management System (CMS) where an article might contain text, images, videos, and code blocks in any combination.

The Verdict: Why PostgreSQL is a Great Default Choice 🚀

While NoSQL offers flexibility, it often pushes the responsibility of data integrity onto the application layer, which can add complexity and bugs.

PostgreSQL offers the best of both worlds. It's a powerful, open-source relational database that is:

1. SQL Compliant: The skills you learn are transferable.

2. Reliable & Scalable: Trusted by startups and large enterprises.

3. Extensible: It has a massive feature set and can be customized.

4. Excellent JSON Support: PostgreSQL has native JSON and JSONB (binary, indexed JSON) data types. This means you can have the reliability of a relational schema for your core data and the flexibility of a schemaless document for dynamic parts, all in the same database.

For these reasons, we'll use PostgreSQL for our practical example.

---

The Blueprint: Designing a Project Management Database

Let's design the database for a project management platform. We'll use database migrations to define and evolve our schema.

Database Migrations: Version Control for Your Schema

You should never change a production database schema by manually running ALTER TABLE queries. Migrations are files containing SQL statements that define changes to your database schema. A migration tool (like dbmate) runs these files in order to apply changes and keeps track of which ones have been run.

Each migration has two parts:

• Up: Applies the new change (e.g., CREATE TABLE ...).

• Down: Reverts the change (e.g., DROP TABLE ...). This allows you to roll back changes if something goes wrong.

The Schema

Here's the visual representation of our database schema, also known as an Entity-Relationship Diagram (ERD).

Code snippet

erDiagram
    users {
        uuid id PK
        text email UK
        text full_name
        text password_hash
        timestamptz created_at
        timestamptz updated_at
    }
    user_profiles {
        uuid user_id PK, FK
        text avatar_url
        text bio
        text phone
        timestamptz created_at
        timestamptz updated_at
    }
    projects {
        uuid id PK
        text name
        text description
        project_status status
        uuid owner_id FK
        timestamptz created_at
        timestamptz updated_at
    }
    tasks {
        uuid id PK
        uuid project_id FK
        text title
        text description
        integer priority
        task_status status
        date due_date
        uuid assigned_to FK
        timestamptz created_at
        timestamptz updated_at
    }
    project_members {
        uuid project_id PK, FK
        uuid user_id PK, FK
        member_role role
        timestamptz created_at
        timestamptz updated_at
    }

    users ||--o{ user_profiles : "has one"
    users ||--o{ projects : "owns"
    users ||--o{ tasks : "is assigned"
    users }o--o{ project_members : "is member of"
    projects ||--o{ tasks : "has many"
    projects }o--o{ project_members : "has many"

Here is the full SQL migration file to create this schema.

SQL

-- migrate:up

-- Create custom ENUM types for better data integrity and self-documentation.
CREATE TYPE "project_status" AS ENUM ('active', 'completed', 'archived');
CREATE TYPE "task_status" AS ENUM ('pending', 'in_progress', 'completed', 'cancelled');
CREATE TYPE "member_role" AS ENUM ('owner', 'admin', 'member');

-- Create a function and trigger to automatically update the 'updated_at' timestamp.
CREATE OR REPLACE FUNCTION "update_updated_at_column"()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- users table
CREATE TABLE "users" (
  "id" uuid PRIMARY KEY DEFAULT (gen_random_uuid()),
  "email" text NOT NULL UNIQUE,
  "full_name" text NOT NULL,
  "password_hash" text NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT (now()),
  "updated_at" timestamptz NOT NULL DEFAULT (now())
);

-- user_profiles table (One-to-One with users)
CREATE TABLE "user_profiles" (
  "user_id" uuid PRIMARY KEY REFERENCES "users"("id") ON DELETE CASCADE,
  "avatar_url" text,
  "bio" text,
  "phone" text,
  "created_at" timestamptz NOT NULL DEFAULT (now()),
  "updated_at" timestamptz NOT NULL DEFAULT (now())
);

-- projects table
CREATE TABLE "projects" (
  "id" uuid PRIMARY KEY DEFAULT (gen_random_uuid()),
  "name" text NOT NULL,
  "description" text,
  "status" project_status NOT NULL DEFAULT 'active',
  "owner_id" uuid NOT NULL REFERENCES "users"("id") ON DELETE RESTRICT,
  "created_at" timestamptz NOT NULL DEFAULT (now()),
  "updated_at" timestamptz NOT NULL DEFAULT (now())
);

-- tasks table (Many-to-One with projects)
CREATE TABLE "tasks" (
  "id" uuid PRIMARY KEY DEFAULT (gen_random_uuid()),
  "project_id" uuid NOT NULL REFERENCES "projects"("id") ON DELETE CASCADE,
  "title" text NOT NULL,
  "description" text,
  "priority" integer NOT NULL DEFAULT 1 CHECK (priority BETWEEN 1 AND 5),
  "status" task_status NOT NULL DEFAULT 'pending',
  "due_date" date,
  "assigned_to" uuid REFERENCES "users"("id") ON DELETE SET NULL,
  "created_at" timestamptz NOT NULL DEFAULT (now()),
  "updated_at" timestamptz NOT NULL DEFAULT (now())
);

-- project_members (Many-to-Many linking table between users and projects)
CREATE TABLE "project_members" (
  "project_id" uuid NOT NULL REFERENCES "projects"("id") ON DELETE CASCADE,
  "user_id" uuid NOT NULL REFERENCES "users"("id") ON DELETE CASCADE,
  "role" member_role NOT NULL DEFAULT 'member',
  "created_at" timestamptz NOT NULL DEFAULT (now()),
  "updated_at" timestamptz NOT NULL DEFAULT (now()),
  PRIMARY KEY ("project_id", "user_id") -- Composite Primary Key
);

-- Create Indexes for faster lookups on frequently queried columns
CREATE INDEX ON "users" ("created_at" DESC);
CREATE INDEX ON "tasks" ("project_id");
CREATE INDEX ON "tasks" ("assigned_to");
CREATE INDEX ON "tasks" ("status");
CREATE INDEX ON "project_members" ("user_id");

-- Apply the 'updated_at' trigger to all tables
CREATE TRIGGER "update_users_updated_at" BEFORE UPDATE ON "users" FOR EACH ROW EXECUTE PROCEDURE "update_updated_at_column"();
CREATE TRIGGER "update_user_profiles_updated_at" BEFORE UPDATE ON "user_profiles" FOR EACH ROW EXECUTE PROCEDURE "update_updated_at_column"();
CREATE TRIGGER "update_projects_updated_at" BEFORE UPDATE ON "projects" FOR EACH ROW EXECUTE PROCEDURE "update_updated_at_column"();
CREATE TRIGGER "update_tasks_updated_at" BEFORE UPDATE ON "tasks" FOR EACH ROW EXECUTE PROCEDURE "update_updated_at_column"();
CREATE TRIGGER "update_project_members_updated_at" BEFORE UPDATE ON "project_members" FOR EACH ROW EXECUTE PROCEDURE "update_updated_at_column"();


-- migrate:down
DROP TRIGGER IF EXISTS "update_project_members_updated_at" ON "project_members";
DROP TRIGGER IF EXISTS "update_tasks_updated_at" ON "tasks";
DROP TRIGGER IF EXISTS "update_projects_updated_at" ON "projects";
DROP TRIGGER IF EXISTS "update_user_profiles_updated_at" ON "user_profiles";
DROP TRIGGER IF EXISTS "update_users_updated_at" ON "users";

DROP TABLE IF EXISTS "project_members";
DROP TABLE IF EXISTS "tasks";
DROP TABLE IF EXISTS "projects";
DROP TABLE IF EXISTS "user_profiles";
DROP TABLE IF EXISTS "users";

DROP FUNCTION IF EXISTS "update_updated_at_column"();

DROP TYPE IF EXISTS "member_role";
DROP TYPE IF EXISTS "task_status";
DROP TYPE IF EXISTS "project_status";

Key Concepts from the Schema:

• Relationships:

  • One-to-One: users and user_profiles. The user_id in user_profiles is both a Primary Key and a Foreign Key, ensuring one user can only have one profile.

  • One-to-Many: projects and tasks. One project can have many tasks. This is implemented by a project_id foreign key in the tasks table.

  • Many-to-Many: users and projects. A user can be in many projects, and a project can have many users. This requires a linking table, project_members, with a composite primary key ("project_id", "user_id") to ensure each user-project pair is unique.

• Referential Integrity (ON DELETE):

  • ON DELETE CASCADE: If a project is deleted, all its associated tasks and memberships are also automatically deleted.

  • ON DELETE RESTRICT: You cannot delete a user who owns a project. You must reassign or delete the project first.

  • ON DELETE SET NULL: If a user assigned to a task is deleted, the assigned_to field for that task becomes NULL (the task becomes unassigned).

• Constraints:

  • NOT NULL: Ensures a column must have a value.

  • UNIQUE: Ensures all values in a column are unique across all rows (e.g., email).

  • CHECK: Enforces a custom rule (e.g., task priority must be between 1 and 5).

---

Bringing It to Life: Querying the Database

After creating the schema, you'd "seed" it with some initial test data. Now, let's write queries for common API endpoints.

GET /api/users (Fetch a list with joins)

We need to fetch a list of users, and for each user, we also want to include their profile information. We can use a LEFT JOIN and PostgreSQL's to_jsonb function to nest the profile data.

SQL

SELECT
  u.*,
  -- Nest the user profile as a JSON object.
  -- Use COALESCE to return an empty JSON object '{}' if the profile doesn't exist.
  COALESCE(to_jsonb(up.*) - 'user_id', '{}'::jsonb) AS profile
FROM
  users u
LEFT JOIN
  user_profiles up ON u.id = up.user_id
ORDER BY
  u.created_at DESC;

GET /api/users/{id} (Fetch a single item)

To fetch a single user, we add a WHERE clause. We use parameterized queries to safely insert user-provided values into our SQL. This is critical to prevent SQL Injection attacks, where a malicious user could otherwise inject destructive SQL commands. In this example, $1 is a placeholder for the user ID.

SQL

SELECT
  u.*,
  COALESCE(to_jsonb(up.*) - 'user_id', '{}'::jsonb) AS profile
FROM
  users u
LEFT JOIN
  user_profiles up ON u.id = up.user_id
WHERE
  u.id = $1; -- $1 is the placeholder for the user ID

POST /api/users (Create a new item)

To create a user, we use an INSERT statement. The RETURNING * clause is very useful as it returns the newly created row, including the database-generated values like id and created_at.

SQL

INSERT INTO users (full_name, email, password_hash)
VALUES ($1, $2, $3) -- Placeholders for name, email, and hashed password
RETURNING *;

PATCH /api/users/{id} (Update an item)

To update a user, we use the UPDATE statement. In a real application, your backend code would dynamically construct the SET clause based on the fields provided by the user.

SQL

UPDATE user_profiles
SET
  bio = $1,        -- New bio
  phone = $2       -- New phone number
WHERE
  user_id = $3     -- ID of the user to update
RETURNING *;

---

Tuning the Engine: Performance and Automation

Writing queries is just the start. You also need to ensure they run fast and that you can automate repetitive tasks.

Database Indexing: The Need for Speed ⚡

An index is a special lookup table that the database can use to speed up data retrieval, much like the index at the back of a book helps you find a topic without reading every page. Without an index, the database has to perform a "full table scan" (O(n) complexity), which is slow on large tables. With an index, it can find data much faster (often O(\log n)).

Rule of Thumb: Create an index on columns that are frequently used in:

• WHERE clauses

• JOIN conditions (foreign keys are prime candidates)

• ORDER BY clauses

In our migration, we created several indexes:

SQL

-- Example: Index on the `assigned_to` foreign key in the `tasks` table.
-- This will speed up queries that find all tasks assigned to a specific user.
CREATE INDEX ON "tasks" ("assigned_to");

-- Example: Index for sorting.
-- This will speed up fetching users sorted by their creation date.
CREATE INDEX ON "users" ("created_at" DESC);

Tradeoff: Indexes speed up reads (SELECT) but add a small overhead to writes (INSERT, UPDATE), as the index itself also needs to be updated. Use them judiciously.

Automating Timestamps with Triggers

Instead of manually setting the updated_at field in every UPDATE query, we can automate it with a trigger. A trigger is a function that automatically runs before or after a specific event (INSERT, UPDATE, DELETE) occurs on a table.

We created a single function and applied it to all our tables:

1. The Function: update_updated_at_column() sets the updated_at field of the row being modified to the current time.

2. The Trigger: We create a trigger for each table that calls this function BEFORE UPDATE.

SQL

-- This trigger on the `projects` table ensures `updated_at` is always current.
CREATE TRIGGER "update_projects_updated_at"
BEFORE UPDATE ON "projects"
FOR EACH ROW
EXECUTE PROCEDURE "update_updated_at_column"();

Now, you never have to worry about this field again; the database handles it for you.

Conclusion

We've covered a lot of ground, from the fundamental purpose of a database to designing a schema, writing queries, and optimizing performance with indexes and triggers. As a backend developer, mastering these concepts will make your applications more robust, efficient, and maintainable.