takentui
Stage 05

SQL, PostgreSQL and data modeling

ResultMentorHub stores users, goals, and tasks in PostgreSQL; The student understands the schema and SQL under the application.Landmark4–5 weeks.
AI mentorGo through this stage with an agentOpen prompt

Copy the prompt to ChatGPT or coding agent. The agent will open this page, clarify your level and guide you through the stage without completing the project for you.

You are my personal Python backend tutor. Help me complete this roadmap stage independently.

Current stage: 05. SQL, PostgreSQL and data modeling
Stage page: https://takentui.ru/en/roadmap/postgresql/
Expected outcome: MentorHub stores users, goals, and tasks in PostgreSQL; The student understands the schema and SQL under the application.
MentorHub increment, or its equivalent in my chosen domain: PostgreSQL instead of JSON
Project deliverable: PostgreSQL schema, SQL queries, import from JSON and legacy scripts with new storage.
Estimated time: 4–5 weeks.

First, establish the context:
1. Open the stage page and read it fully: topics, practice, project increment, completion criteria and materials.
2. If you cannot open it, ask me to paste the relevant section. Do not pretend you have read it.
3. Use the page's requirements. Do not invent missing requirements.
4. If you can access my repository, read the README, structure, code, tests and history first. Make no changes.
5. Otherwise, ask for a repository link or only the files and command output needed for the next step.

Tutoring rules:
- Establish my skill level, chosen domain and project state, then adapt the route. I may use MentorHub or an alternative domain allowed by the roadmap. Respect my chosen product.
- Give one small assignment at a time. Stop and wait for my attempt.
- Before each assignment, explain the problem, how the principle works, why it matters in backend development, how it relates to my project and how to check completion. Use small examples from another domain, without giving away the assignment's implementation.
- Do not write the finished implementation, project files or homework for me. Explain the theory in as much depth as needed.
- Increase help gradually: guiding question → research direction → small hint → pseudocode → minimal example in another domain. Move to the next level only when necessary.
- Review my attempt: explain what works, then errors, risks and one next step. Do not rewrite the entire solution.
- Ask me to explain code, decisions and mistakes in my own words. If I cannot explain a solution, I have not mastered the topic yet.
- Avoid technologies from later stages and unnecessary architectural complexity.
- Diagnose problems using tracebacks, logs, tests and documentation.
- Track progress against the page's criteria. Require a working, verified deliverable before completing the stage.
- At the end of each session, suggest a short LEARNING.md entry covering what I did, learned, got wrong and should do next. This is optional.

Workflow:
1. Ask 3–5 short questions about my experience, available time, chosen domain, project state and difficulties.
2. After my answers, present an adapted plan with small checkpoints.
3. Explain the what, how and why of the first checkpoint and give the first assignment.
4. Wait for my attempt, review it and repeat.
5. Finish with the page's checklist and ask me to defend my decisions.

In your first reply, confirm whether you could read the page, name the final deliverable in one sentence and ask the diagnostic questions. Wait for my answers before teaching the stage or providing a solution.

Explore

  • table, row, column, data type, NULL;
  • PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL;
  • one-to-one, one-to-many, many-to-many connections;
  • SELECT, INSERT, UPDATE, DELETE;
  • WHERE, ORDER BY, LIMIT/OFFSET;
  • JOIN, GROUP BY, aggregate functions, HAVING;
  • subqueries and CTEs at the base level;
  • normalization to 3NF using a practical example;
  • transactions, ACID, COMMIT, ROLLBACK;
  • race condition, row locking and isolation levels with examples;
  • B-tree index: why, record price, composite index and field order;
  • EXPLAIN/EXPLAIN ANALYZE for one slow request;
  • PostgreSQL CLI (psql) and backup at an overview level;
  • SQL injection and parameterized queries.

MentorHub 0.5 increment - PostgreSQL instead of JSON

Project artifact: PostgreSQL schema, SQL queries, import from JSON and legacy scripts with new storage.

For the same application:

  1. Draw an ER diagram.
  2. Create tables users, goals, tasks, goal_members. While there is no registration, add two training users via a seed script and assign a target participant directly using an SQL command.
  3. Add integrity constraints.
  4. Write at least 20 queries, including JOINs and aggregates.
  5. Create a meaningful index and compare the query plan.
  6. Replay the transaction with rollback.
  7. Write a “weekly user progress” report using one SQL query.
  8. Write a one-time import of data from the old JSON format: the old targets belong to a predefined training user.
  9. Connect PostgreSQL storage to existing business logic and repeat the main CLI scripts.

For a separate workout, go through two SQL jobs: participants and training sessions. The first tests the design of relationships and constraints, the second tests JOIN, filtering and aggregations on ready-made data.

After this, JSON is no longer the primary storage, but remains in the project's history as a previous implementation of the same boundary.

ORM - only after SQL

After confident SQL learn:

  • model and mapping;
  • session/unit of work;
  • CRUD;
  • eager/lazy loading and the N+1 problem;
  • transaction;
  • Alembic migration;
  • View the actual generated SQL.

Check

  • I can write JOIN without ORM.
  • I explain what duplicates it prevents UNIQUE.
  • I know why the index does not speed up everything and has a cost.
  • I group several dependent changes into a transaction.
  • I don't build SQL by concatenating user input.
  • Migrations can be applied to an empty database from scratch.

Free materials

Practice for this stage