Module F-7·19 min read

Translating a real-world problem into tables, connecting from application code, and basic backup and restore.

F-7 — Your First Real Application Schema

Q: A Senior Engineer is reviewing the `taskmanager` schema's foreign key `ON DELETE` actions. If a `users` record is attempted to be deleted, which of the following statements accurately describes the *immediate* production behavior based on the provided schema?

The deletion will fail if the user owns any `projects`, has created any `tasks`, or authored any `comments`, due to `ON DELETE RESTRICT` clauses. — Option B correctly identifies that the `ON DELETE RESTRICT` clauses on `projects.owner_id`, `tasks.created_by`, and `comments.author_id` will prevent the deletion of a user if they are linked to any existing projects, tasks they created, or comments they authored. This ensures data integrity by preventing orphaned records or unintended cascading deletions in critical ownership/authorship relationships. Option A is incorrect because `RESTRICT` prevents deletion, and `SET NULL` only applies to `assigned_to`. Option C is incorrect because `RESTRICT` applies to tasks/comments created/authored by the user, not `CASCADE`, and `project_members` are `CASCADE`d, not `SET NULL`. Option D is incorrect because it overlooks the `RESTRICT` clauses that would block the user deletion if they own projects, created tasks, or authored comments.

Q: A junior developer implements a user search feature using string concatenation for SQL queries, similar to the `❌ SQL INJECTION VULNERABILITY` example. During a security audit, it's identified that an attacker could input `' OR '1'='1` into the search field. What is the most critical implication of this vulnerability in a production environment?

An attacker could bypass authentication or retrieve unauthorized sensitive data by manipulating the query logic to return all records or execute arbitrary commands. — Option C accurately describes the most critical implication of SQL injection. By injecting `' OR '1'='1`, an attacker can make the `WHERE` clause always true, effectively bypassing filters and potentially retrieving all records from a table (e.g., all user data, including password hashes). Depending on the database permissions and the injected SQL, an attacker could also execute arbitrary database commands, leading to data modification, deletion, or even remote code execution. Option A is incorrect because the injected SQL is often syntactically valid and designed to manipulate logic, not crash the server. Option B is incorrect because `'1'='1'` makes the condition true, leading to *more* data being returned, not less. Option D is incorrect; databases do not automatically sanitize SQL input in this manner; parameterized queries are the mechanism to treat user input as data, not executable code.

Q: A team needs to add a new `priority_level` column to the `tasks` table in a production environment. They are debating between manually running `ALTER TABLE` commands directly on the production database or using a schema migration tool like `node-pg-migrate`. As a Senior Principal Software Engineer, what is the primary reason to strongly advocate for using a migration tool?

They ensure that schema changes are version-controlled, applied consistently across environments, and provide a clear audit trail and rollback capability. — Option B highlights the core benefits of schema migration tools for production environments. They provide a structured, version-controlled approach to schema evolution, ensuring that changes are applied deterministically and consistently across development, staging, and production. This includes tracking which migrations have been applied, preventing accidental re-application, and offering a mechanism for rollback if issues arise. This process is crucial for maintaining data integrity and application stability in production. Option A is incorrect because while some ORM-based migration tools can generate SQL, many (like `node-pg-migrate`) require developers to write the SQL scripts themselves; the tool's value is in managing their execution and state. Option C is incorrect; migration tools do not automatically optimize indexes; developers must explicitly define them within migration scripts. Option D is incorrect; schema migrations deal with DDL (Data Definition Language) and are unrelated to preventing SQL injection, which concerns DML (Data Manipulation Language) with user input.

Who this module is for: You have completed all of Phase 1. You can create tables, query, filter, aggregate, join, and enforce constraints. Now you need to put it all together: design a schema for a real application, connect to it from application code, and understand the tools that keep schemas manageable as they evolve. This module closes the Foundation phase and prepares you for the Practitioner topics ahead.

From Concepts to Real Design

A schema for a real application starts with questions, not SQL:

What are the entities? The "things" your application manages — users, tasks, projects, comments
What attributes does each entity have? The data that describes each thing
What are the relationships? How entities relate — a task belongs to a project, a user can have many tasks

This process is called data modelling. You do not need formal methodology — just answer these three questions before writing any SQL.

Example: A Task Management Application

Entities:

Users (who uses the app)
Projects (containers for tasks)
Tasks (the work items)
Comments (discussion on tasks)

Attributes:

User: username, email, password hash, created date
Project: name, description, owner user, created date
Task: title, description, status, assignee, project, due date, created date
Comment: content, author, task, created date

Relationships:

A user owns zero or more projects (one-to-many)
A project has zero or more tasks (one-to-many)
A task belongs to exactly one project (many-to-one)
A task can be assigned to zero or one user (optional many-to-one)
A task has zero or more comments (one-to-many)
A user can be a member of multiple projects, and a project has multiple members (many-to-many)

The Complete Schema

sql

CREATE DATABASE taskmanager;
\c taskmanager

-- Users of the application
CREATE TABLE users (
  id            BIGSERIAL     PRIMARY KEY,
  username      TEXT          NOT NULL UNIQUE,
  email         TEXT          NOT NULL UNIQUE,
  password_hash TEXT          NOT NULL,       -- never store plain passwords
  display_name  TEXT,
  created_at    TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
  last_login_at TIMESTAMPTZ
);

-- Projects group tasks together
CREATE TABLE projects (
  id          BIGSERIAL   PRIMARY KEY,
  name        TEXT        NOT NULL,
  description TEXT,
  owner_id    BIGINT      NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  -- Restrict: can't delete a user who owns projects
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  archived_at TIMESTAMPTZ           -- NULL = active, non-NULL = archived
);

-- Many-to-many: project membership (who can see and work in a project)
CREATE TABLE project_members (
  project_id BIGINT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  user_id    BIGINT NOT NULL REFERENCES users(id)    ON DELETE CASCADE,
  role       TEXT   NOT NULL DEFAULT 'member'
             CHECK (role IN ('owner', 'admin', 'member', 'viewer')),
  joined_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (project_id, user_id)  -- a user can only be in a project once
);

-- Tasks are the work items
CREATE TABLE tasks (
  id           BIGSERIAL   PRIMARY KEY,
  project_id   BIGINT      NOT NULL REFERENCES projects(id)  ON DELETE CASCADE,
  -- If a project is deleted, all its tasks are deleted too
  created_by   BIGINT      NOT NULL REFERENCES users(id)     ON DELETE RESTRICT,
  assigned_to  BIGINT               REFERENCES users(id)     ON DELETE SET NULL,
  -- assigned_to is optional; if the assignee is deleted, set to NULL
  title        TEXT        NOT NULL,
  description  TEXT,
  status       TEXT        NOT NULL DEFAULT 'todo'
               CHECK (status IN ('todo', 'in_progress', 'done', 'cancelled')),
  priority     INTEGER     NOT NULL DEFAULT 2
               CHECK (priority BETWEEN 1 AND 5),  -- 1=lowest, 5=highest
  due_date     DATE,
  completed_at TIMESTAMPTZ,          -- NULL until task is marked done
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Comments on tasks
CREATE TABLE comments (
  id         BIGSERIAL   PRIMARY KEY,
  task_id    BIGINT      NOT NULL REFERENCES tasks(id)  ON DELETE CASCADE,
  author_id  BIGINT      NOT NULL REFERENCES users(id)  ON DELETE RESTRICT,
  content    TEXT        NOT NULL CHECK (LENGTH(TRIM(content)) > 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  edited_at  TIMESTAMPTZ           -- NULL until edited
);

Verifying the Schema

sql

-- List all tables
\dt

-- Check the structure of each table
\d users
\d projects
\d tasks
\d comments
\d project_members

-- See all foreign key relationships
SELECT
  tc.table_name,
  kcu.column_name,
  ccu.table_name  AS foreign_table,
  ccu.column_name AS foreign_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
  ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
ORDER BY tc.table_name;

Seeding Test Data

sql

-- Add users
INSERT INTO users (username, email, password_hash, display_name) VALUES
  ('alice',   'alice@example.com',  'hash1', 'Alice Johnson'),
  ('bob',     'bob@example.com',    'hash2', 'Bob Smith'),
  ('carol',   'carol@example.com',  'hash3', 'Carol Davis');

-- Add a project
INSERT INTO projects (name, description, owner_id) VALUES
  ('Website Redesign', 'Redesign the company website', 1);
-- Project id = 1

-- Add project members
INSERT INTO project_members (project_id, user_id, role) VALUES
  (1, 1, 'owner'),   -- Alice is owner
  (1, 2, 'member'),  -- Bob is a member
  (1, 3, 'viewer');  -- Carol can view

-- Add tasks
INSERT INTO tasks (project_id, created_by, assigned_to, title, status, priority) VALUES
  (1, 1, 2, 'Design homepage mockup', 'in_progress', 4),
  (1, 1, 1, 'Set up CI/CD pipeline',  'todo',        3),
  (1, 2, 3, 'Write content copy',     'todo',        2),
  (1, 1, 2, 'Review SEO strategy',    'done',        2);

-- Add a comment
INSERT INTO comments (task_id, author_id, content) VALUES
  (1, 2, 'I will have the mockup ready by Friday.');

Querying the Schema

sql

-- All tasks in the project with assignee names
SELECT
  t.title,
  t.status,
  t.priority,
  u.display_name AS assigned_to
FROM tasks t
LEFT JOIN users u ON t.assigned_to = u.id
WHERE t.project_id = 1
ORDER BY t.priority DESC, t.created_at;

-- Project members and their roles
SELECT
  u.display_name,
  u.email,
  pm.role,
  pm.joined_at
FROM project_members pm
JOIN users u ON pm.user_id = u.id
WHERE pm.project_id = 1;

-- Tasks with comment count
SELECT
  t.title,
  t.status,
  COUNT(c.id) AS comment_count
FROM tasks t
LEFT JOIN comments c ON t.id = c.task_id
WHERE t.project_id = 1
GROUP BY t.id, t.title, t.status
ORDER BY t.created_at;

-- Summary: tasks per status in this project
SELECT status, COUNT(*) AS count
FROM tasks
WHERE project_id = 1
GROUP BY status;

Connecting From Application Code

PostgreSQL stores your data — but your application needs to connect to it. Here is the minimal setup for the two most common stacks.

Node.js with `pg`

bash

npm install pg

javascript

// db.js
import pg from 'pg';

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  // Or explicitly:
  // host: 'localhost',
  // port: 5432,
  // database: 'taskmanager',
  // user: 'postgres',
  // password: 'yourpassword',
  max: 10,                // connection pool size
  idleTimeoutMillis: 30000,
});

export async function query(text, params) {
  const result = await pool.query(text, params);
  return result;
}

javascript

// Using it in your application
import { query } from './db.js';

// Get all tasks for a project
const result = await query(
  'SELECT * FROM tasks WHERE project_id = $1 ORDER BY created_at',
  [projectId]
);
const tasks = result.rows;

// Insert a new task (use parameterised queries — never string concatenation)
const newTask = await query(
  `INSERT INTO tasks (project_id, created_by, title, status, priority)
   VALUES ($1, $2, $3, $4, $5)
   RETURNING *`,
  [projectId, userId, title, 'todo', 3]
);
const task = newTask.rows[0];

Never build SQL by string concatenation with user input:

javascript

// ❌ SQL INJECTION VULNERABILITY — never do this
const result = await query(
  `SELECT * FROM users WHERE email = '${userInputEmail}'`
);
// An attacker can input: ' OR '1'='1 and read all users

// ✅ ALWAYS use parameterised queries
const result = await query(
  'SELECT * FROM users WHERE email = $1',
  [userInputEmail]
);
// $1 is a placeholder — user input is always treated as data, never as SQL

Python with `psycopg2`

bash

pip install psycopg2-binary

python

import psycopg2
import psycopg2.extras

conn = psycopg2.connect(
    host="localhost",
    database="taskmanager",
    user="postgres",
    password="yourpassword"
)
conn.autocommit = True  # or manage transactions manually

with conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) as cur:
    # Parameterised query with %s placeholders
    cur.execute(
        "SELECT * FROM tasks WHERE project_id = %s ORDER BY created_at",
        (project_id,)
    )
    tasks = cur.fetchall()  # list of dict-like rows

Schema Migrations: Managing Change

Schemas change. You add columns, rename things, add constraints. In development, you can DROP DATABASE and start over. In production, you cannot — there is live data there.

Schema migrations are versioned SQL scripts that transform the schema from one state to another.

Manual Migrations (the concept)

sql

-- migrations/001_initial_schema.sql
-- Create the initial schema (the tables we defined above)

-- migrations/002_add_task_labels.sql
CREATE TABLE labels (
  id      BIGSERIAL PRIMARY KEY,
  name    TEXT      NOT NULL UNIQUE,
  color   TEXT      NOT NULL DEFAULT '#888888'
);

CREATE TABLE task_labels (
  task_id  BIGINT NOT NULL REFERENCES tasks(id)  ON DELETE CASCADE,
  label_id BIGINT NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
  PRIMARY KEY (task_id, label_id)
);

-- migrations/003_add_task_position.sql
ALTER TABLE tasks ADD COLUMN position INTEGER NOT NULL DEFAULT 0;
CREATE INDEX idx_tasks_project_position ON tasks (project_id, position);

Migration Tools

In practice, teams use migration tools that:

Track which migrations have been applied
Run new migrations automatically on deploy
Allow rollback if something goes wrong

Popular options by stack:

Stack	Tool
Node.js	Drizzle ORM, Prisma Migrate, node-pg-migrate, Flyway
Python	Alembic (SQLAlchemy), Flyway
Ruby	ActiveRecord Migrations
Java	Flyway, Liquibase
Any	Flyway (JVM-based, works with any stack)

For a beginner Node.js project, node-pg-migrate is simple:

bash

npm install node-pg-migrate

bash

# Create a new migration
node-pg-migrate create add-task-labels

# Run all pending migrations
node-pg-migrate up

# Roll back the last migration
node-pg-migrate down

`pg_dump` — Your Safety Net

Before any schema change on a real database, take a backup:

bash

# Backup the entire database
pg_dump taskmanager > backup_$(date +%Y%m%d_%H%M%S).sql

# Restore from a backup
psql taskmanager < backup_20260517_103045.sql

# Backup with compression (for large databases)
pg_dump -Fc taskmanager > backup.dump
pg_restore -d taskmanager backup.dump

Get in the habit of pg_dump before anything that modifies schema structure.

What Phase 1 Has Given You

By completing Modules F-1 through F-7, you can:

✅ Install PostgreSQL and use psql confidently
✅ Design a schema from a real-world problem
✅ Use correct data types for every situation
✅ Write every fundamental SQL operation
✅ Filter, sort, and aggregate data
✅ Join data across multiple tables
✅ Enforce data integrity with constraints
✅ Connect from application code safely (parameterised queries)
✅ Understand schema migrations conceptually

This is the foundation that every PostgreSQL engineer builds on. Phase 2 — The Practitioner takes you from isolated scripts to real production application patterns: advanced SQL, indexing, transactions, JSON, full-text search, access control, and deployment.

Summary

Concept	Key Takeaway
Data modelling	Identify entities, attributes, and relationships before writing SQL
Junction tables	Model many-to-many relationships with a third table
Consistent timestamps	`created_at`, `updated_at`, soft-delete with `archived_at` / `deleted_at`
Parameterised queries	Always use `$1`, `$2` placeholders — never string concatenation with user input
Schema migrations	Version your schema changes; never manually ALTER in production without a migration
`pg_dump`	Backup before any structural change

Phase 1 is complete. Phase 2 — The Practitioner — begins with Module P-1: Advanced SQL, covering CTEs, window functions, upserts, and the patterns every production engineer uses weekly.

Next: P-1 — Advanced SQL — The Patterns You Will Use Every Week →

Knowledge Check

A Senior Engineer is reviewing the taskmanager schema's foreign key ON DELETE actions. If a users record is attempted to be deleted, which of the following statements accurately describes the *immediate* production behavior based on the provided schema?

A junior developer implements a user search feature using string concatenation for SQL queries, similar to the ❌ SQL INJECTION VULNERABILITY example. During a security audit, it's identified that an attacker could input ' OR '1'='1 into the search field. What is the most critical implication of this vulnerability in a production environment?

A team needs to add a new priority_level column to the tasks table in a production environment. They are debating between manually running ALTER TABLE commands directly on the production database or using a schema migration tool like node-pg-migrate. As a Senior Principal Software Engineer, what is the primary reason to strongly advocate for using a migration tool?

Test your knowledge with more question sets

PreviousModule F-6: Constraints and Data Integrity Next Module P-1: Advanced SQL — The Patterns You Will Use Every Week

Discussion

Join the discussion

Loading comments...

F-7 — Your First Real Application Schema

From Concepts to Real Design

Example: A Task Management Application

The Complete Schema

Verifying the Schema

Seeding Test Data

Querying the Schema

Connecting From Application Code

Node.js with pg

Python with psycopg2

Schema Migrations: Managing Change

Manual Migrations (the concept)

Migration Tools

pg_dump — Your Safety Net

What Phase 1 Has Given You

Summary

Test your knowledge with more question sets

Discussion

Node.js with `pg`

Python with `psycopg2`

`pg_dump` — Your Safety Net