10.16. Common Table Expressions
Table of Contents
Understanding Common Table Expressions
Common Table Expressions, usually called CTEs, let you write temporary, named result sets inside a query. You can think of a CTE as a query inside a query, with a name that you can reuse.
CTEs make complex SQL easier to read, debug, and maintain, especially compared to very deeply nested subqueries.
In this chapter we focus on how to write and use CTEs, not on basic SQL or joins, which are covered in other chapters.
Basic CTE Syntax
A CTE has a simple pattern:
WITH cte_name AS (
-- some SELECT query
SELECT ...
)
SELECT ...
FROM cte_name;You define the CTE first, then use it in a main query that follows.
A very small example:
WITH high_salary_employees AS (
SELECT id, name, salary
FROM employees
WHERE salary > 70000
)
SELECT name, salary
FROM high_salary_employees
ORDER BY salary DESC;Here:
high_salary_employeesis the CTE name.- It contains a
SELECTthat filters theemployeestable. - The main query selects from
high_salary_employeesas if it were a real table.
CTE rule:
Every CTE must start with WITH, have a name, and contain one SELECT query in parentheses, followed by a main query that uses it.
When to Use a CTE Instead of a Subquery
You could write the same logic with a subquery:
SELECT name, salary
FROM (
SELECT id, name, salary
FROM employees
WHERE salary > 70000
) AS high_salary_employees
ORDER BY salary DESC;Both versions work. You usually prefer a CTE when:
- The subquery is long or complex.
- You want to reuse the same subquery multiple times.
- You want to break a big problem into clear steps.
Naming Columns in a CTE
You can optionally list column names right after the CTE name:
WITH high_salary_employees (emp_id, emp_name, emp_salary) AS (
SELECT id, name, salary
FROM employees
WHERE salary > 70000
)
SELECT emp_name, emp_salary
FROM high_salary_employees;
If you do not list columns there, the names from the inner SELECT are used.
The number of column names must match the number of columns returned by the SELECT.
Important:
If you list column names after the CTE name, they replace the inner query column names for the outer query.
Multiple CTEs in One Query
You can define more than one CTE in a single WITH clause, separated by commas:
WITH recent_orders AS (
SELECT id, customer_id, total_amount, created_at
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
),
big_spenders AS (
SELECT customer_id, SUM(total_amount) AS total_spent
FROM recent_orders
GROUP BY customer_id
HAVING SUM(total_amount) > 1000
)
SELECT c.id, c.name, b.total_spent
FROM customers c
JOIN big_spenders b ON c.id = b.customer_id
ORDER BY b.total_spent DESC;Here:
- First CTE
recent_ordersselects orders from the last 30 days. - Second CTE
big_spendersaggregatesrecent_orders. - The final query joins
customerswithbig_spenders.
Order matters. Each CTE can use CTEs defined before it in the list, but not after.
Example: Step by Step Data Preparation
Assume you have:
orders(id, customer_id, total_amount, created_at)order_items(id, order_id, product_id, quantity, unit_price)
You want: total quantity and total revenue per product for the last month, but only for products with more than 50 units sold.
WITH last_month_orders AS (
SELECT id
FROM orders
WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
AND created_at < DATE_TRUNC('month', CURRENT_DATE)
),
last_month_items AS (
SELECT oi.product_id,
oi.quantity,
oi.quantity * oi.unit_price AS line_revenue
FROM order_items oi
JOIN last_month_orders o ON oi.order_id = o.id
),
product_totals AS (
SELECT product_id,
SUM(quantity) AS total_qty,
SUM(line_revenue) AS total_revenue
FROM last_month_items
GROUP BY product_id
)
SELECT product_id, total_qty, total_revenue
FROM product_totals
WHERE total_qty > 50
ORDER BY total_revenue DESC;Each CTE is a simple piece. Together they form a clear pipeline.
Using CTEs for Readable Stepwise Queries
CTEs shine when you want to:
- Apply filters in stages.
- Build aggregations step by step.
- Prepare intermediate results with clear names.
Example: Active Customers With Their Latest Order
Tables:
customers(id, name, status)orders(id, customer_id, total_amount, created_at)
Goal: list only active customers and their latest order total, if any.
Without CTEs, a typical solution uses a subquery inside a join:
SELECT c.id, c.name, o.total_amount, o.created_at
FROM customers c
LEFT JOIN orders o
ON o.id = (
SELECT id
FROM orders
WHERE customer_id = c.id
ORDER BY created_at DESC
LIMIT 1
)
WHERE c.status = 'active';With CTEs you can separate the steps:
WITH active_customers AS (
SELECT id, name
FROM customers
WHERE status = 'active'
),
latest_orders AS (
SELECT DISTINCT ON (customer_id)
customer_id,
total_amount,
created_at
FROM orders
ORDER BY customer_id, created_at DESC
)
SELECT ac.id, ac.name,
lo.total_amount,
lo.created_at
FROM active_customers ac
LEFT JOIN latest_orders lo
ON ac.id = lo.customer_id
ORDER BY ac.name;Here each CTE has a clear job:
active_customers: filter active customers.latest_orders: pick the latest order per customer.- Final query: join the two.
Example: Filtering Before Aggregation
Imagine page_views(user_id, page, viewed_at).
Goal: For each user, count views in the last 7 days, then only keep users with more than 100 views.
WITH last_week AS (
SELECT user_id, page, viewed_at
FROM page_views
WHERE viewed_at >= NOW() - INTERVAL '7 days'
),
user_view_counts AS (
SELECT user_id, COUNT(*) AS view_count
FROM last_week
GROUP BY user_id
)
SELECT user_id, view_count
FROM user_view_counts
WHERE view_count > 100
ORDER BY view_count DESC;This makes both the filter and aggregation steps obvious.
Recursive CTEs
A recursive CTE is a powerful feature that lets you repeat a query until a condition is met.
Use cases:
- Hierarchies, like companies and departments.
- Trees, like category / subcategory structures.
- Graph traversal, like friend-of-a-friend.
Recursive CTE Syntax
General pattern:
WITH RECURSIVE cte_name AS (
-- 1. Anchor member: base query, runs once
SELECT ...
FROM ...
WHERE ... -- base condition
UNION ALL
-- 2. Recursive member: refers to cte_name
SELECT ...
FROM ...
JOIN cte_name ON ... -- use previous results
WHERE ... -- stop condition
)
SELECT * FROM cte_name;Recursive CTE rules:
- Use
WITH RECURSIVEkeyword. - The CTE body has two parts joined by
UNIONorUNION ALL: - Anchor member (no recursion).
- Recursive member (refers to the CTE itself).
- The recursive part must have a condition that eventually stops, or the query can loop forever.
Example: Employee Hierarchy
Table employees:
| id | name | manager_id |
|---|---|---|
| 1 | CEO | NULL |
| 2 | VP Sales | 1 |
| 3 | VP Tech | 1 |
| 4 | Sales Rep A | 2 |
| 5 | Sales Rep B | 2 |
| 6 | Engineer A | 3 |
| 7 | Engineer B | 3 |
Goal: Find all employees under manager id = 1 with their levels in the hierarchy.
WITH RECURSIVE employee_hierarchy AS (
-- Anchor: start from the top manager
SELECT
id,
name,
manager_id,
0 AS level
FROM employees
WHERE id = 1
UNION ALL
-- Recursive: find direct reports of people already in the hierarchy
SELECT
e.id,
e.name,
e.manager_id,
eh.level + 1 AS level
FROM employees e
JOIN employee_hierarchy eh
ON e.manager_id = eh.id
)
SELECT id, name, manager_id, level
FROM employee_hierarchy
ORDER BY level, id;Output:
| id | name | manager_id | level |
|---|---|---|---|
| 1 | CEO | NULL | 0 |
| 2 | VP Sales | 1 | 1 |
| 3 | VP Tech | 1 | 1 |
| 4 | Sales Rep A | 2 | 2 |
| 5 | Sales Rep B | 2 | 2 |
| 6 | Engineer A | 3 | 2 |
| 7 | Engineer B | 3 | 2 |
You can change the starting manager by changing the WHERE id = 1 condition.
Example: Path and Depth
You can also track the path:
WITH RECURSIVE employee_hierarchy AS (
SELECT
id,
name,
manager_id,
0 AS level,
name::text AS path
FROM employees
WHERE id = 1
UNION ALL
SELECT
e.id,
e.name,
e.manager_id,
eh.level + 1 AS level,
eh.path || ' > ' || e.name AS path
FROM employees e
JOIN employee_hierarchy eh
ON e.manager_id = eh.id
)
SELECT id, name, level, path
FROM employee_hierarchy
ORDER BY level, id;
Now path shows the full chain, like CEO > VP Tech > Engineer A.
Example: Simple Number Series
You can generate sequences of numbers without a table.
Goal: numbers from 1 to 10.
WITH RECURSIVE numbers AS (
SELECT 1 AS n -- anchor
UNION ALL
SELECT n + 1 -- recursive
FROM numbers
WHERE n < 10 -- stop condition
)
SELECT n FROM numbers;Output: 1,2,3,4,5,6,7,8,9,10.
This is useful for date ranges, reports per day, filling gaps, and more.
Recursive CTE for Category Trees
Table categories:
| id | name | parent_id |
|---|---|---|
| 1 | Electronics | NULL |
| 2 | Phones | 1 |
| 3 | Laptops | 1 |
| 4 | Android | 2 |
| 5 | iOS | 2 |
| 6 | Gaming | 3 |
Goal: get a full tree under "Electronics" with depth and full path.
WITH RECURSIVE category_tree AS (
-- start from the root category
SELECT
id,
name,
parent_id,
0 AS depth,
name::text AS path
FROM categories
WHERE name = 'Electronics'
UNION ALL
-- find children of categories already in the tree
SELECT
c.id,
c.name,
c.parent_id,
ct.depth + 1 AS depth,
ct.path || ' > ' || c.name AS path
FROM categories c
JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT id, name, depth, path
FROM category_tree
ORDER BY depth, name;Avoiding Infinite Loops
If your data has a cycle (for example, an employee managed by their own subordinate), a recursive CTE can loop forever. Many databases have a recursion depth limit, but you still should protect yourself.
A common trick is to store visited IDs in a text or array column and check that you do not revisit them. Example with a path string:
WITH RECURSIVE safe_hierarchy AS (
SELECT
id,
name,
manager_id,
0 AS level,
id::text AS visited_ids
FROM employees
WHERE id = 1
UNION ALL
SELECT
e.id,
e.name,
e.manager_id,
sh.level + 1 AS level,
sh.visited_ids || ',' || e.id::text AS visited_ids
FROM employees e
JOIN safe_hierarchy sh ON e.manager_id = sh.id
WHERE sh.visited_ids NOT LIKE '%' || e.id::text || '%'
)
SELECT id, name, level
FROM safe_hierarchy;
The WHERE clause blocks revisiting any id that is already part of visited_ids.
Using CTEs With Aggregations and Window Functions
CTEs are very often combined with aggregations and window functions to structure complex analytical queries.
Example: Top N per Group
Goal: For each department, find the top 3 highest paid employees.
Tables:
departments(id, name)employees(id, name, department_id, salary)
WITH ranked_salaries AS (
SELECT
e.id,
e.name,
e.department_id,
e.salary,
RANK() OVER (
PARTITION BY e.department_id
ORDER BY e.salary DESC
) AS salary_rank
FROM employees e
)
SELECT
rs.id,
rs.name,
rs.department_id,
rs.salary
FROM ranked_salaries rs
WHERE rs.salary_rank <= 3
ORDER BY rs.department_id, rs.salary DESC;
The window function RANK() adds the ranking, and the outer query just filters.
Example: Monthly Revenue and Percentage Change
Tables:
orders(id, total_amount, created_at)
Goal: For each month, show total revenue and percentage change vs previous month.
WITH monthly_revenue AS (
SELECT
DATE_TRUNC('month', created_at) AS month,
SUM(total_amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
),
monthly_with_prev AS (
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_revenue
FROM monthly_revenue
)
SELECT
month,
revenue,
prev_revenue,
CASE
WHEN prev_revenue IS NULL OR prev_revenue = 0 THEN NULL
ELSE ROUND(
(revenue - prev_revenue) * 100.0 / prev_revenue,
2
)
END AS pct_change
FROM monthly_with_prev
ORDER BY month;Pipeline:
monthly_revenue: aggregate by month.monthly_with_prev: attach previous month revenue usingLAG.- Final
SELECT: calculate percentage change.
This approach is easier to understand than one giant query with nested subqueries.
CTEs vs Subqueries vs Views
CTEs are similar to subqueries and views, but they serve different purposes.
| Feature | CTE | Subquery | View |
|---|---|---|---|
| Scope | Single query | Single query | Database wide |
| Definition place | At top with WITH | Inline in FROM / WHERE | Created once, stored in DB |
| Reusable in SQL | Only in that main query | Only where written | Reusable across many queries |
| Good for | Complex one off reports | Simple in place logic | Shared common query patterns |
Rules of thumb:
- Use a CTE when:
- You want clear stepwise logic in one query.
- You may need the same intermediate result several times in that query.
- Use a subquery when:
- The logic is small and only used once.
- Use a view when:
- Many queries need the same complex logic.
Some databases treat CTEs as optimization barriers, which can affect performance compared to inline views or subqueries. Modern versions of PostgreSQL and others often optimize CTEs better, but you should still measure performance for very heavy queries.
Practical Tips and Patterns
Pattern: Cleaning and Reusing Filters
Instead of copying the same filter in different places, put it in a CTE.
Bad practice:
SELECT COUNT(*)
FROM orders
WHERE status = 'completed'
AND created_at >= NOW() - INTERVAL '7 days';
SELECT customer_id, SUM(total_amount)
FROM orders
WHERE status = 'completed'
AND created_at >= NOW() - INTERVAL '7 days'
GROUP BY customer_id;Better with a CTE:
WITH last_week_completed AS (
SELECT *
FROM orders
WHERE status = 'completed'
AND created_at >= NOW() - INTERVAL '7 days'
)
SELECT COUNT(*) FROM last_week_completed;
SELECT customer_id, SUM(total_amount)
FROM last_week_completed
GROUP BY customer_id;
You can even combine both into one query with multiple final SELECTs in some environments, but many frameworks expect a single result set. For clarity in application code you usually keep one SELECT per statement.
Pattern: Limiting Recursion Depth
Always consider a maximum depth in recursive CTEs to avoid pathological cases.
WITH RECURSIVE employee_hierarchy AS (
SELECT id, name, manager_id, 0 AS level
FROM employees
WHERE id = 1
UNION ALL
SELECT e.id, e.name, e.manager_id, eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.id
WHERE eh.level < 10 -- do not go deeper than 10 levels
)
SELECT * FROM employee_hierarchy;This gives you a safety net even if the data is not perfect.
Summary
- A CTE is a named temporary result set used only in the following query.
- Basic syntax uses
WITH cte_name AS (SELECT ...) SELECT ... FROM cte_name;. - You can define multiple CTEs in one
WITHclause and use them like building blocks. - CTEs improve readability and make complex queries easier to maintain.
- Recursive CTEs let you work with hierarchies and sequences by using
WITH RECURSIVE, an anchor member, and a recursive member. - CTEs work very well with aggregations and window functions to build stepwise analytical queries.
- Choose between CTEs, subqueries, and views based on scope and reuse needs.
With these patterns, you can write clearer, more maintainable SQL for complex reporting and hierarchical data, which is very useful in backend development.
Views: 0
Where to Move
Move chapter:
- ☰ 1. Introduction to Backend Development
- ☰ 1.1. What Is Backend Development?
- ☰ 1.2. Frontend vs Backend
- ☰ 1.3. How Web Applications Work
- ☰ 1.4. Client-Server Architecture
- ☰ 1.5. Request-Response Cycle
- ☰ 1.6. Static vs Dynamic Websites
- ☰ 1.7. Backend Developer Responsibilities
- ☰ 1.8. Backend Development Roadmap
-
- ☰ 2. How the Web Works
- ☰ 2.1. The Internet and the Web
- ☰ 2.2. IP Addresses
- ☰ 2.3. Domain Names
- ☰ 2.4. DNS
- ☰ 2.5. Ports
- ☰ 2.6. TCP/IP Basics
- ☰ 2.7. HTTP and HTTPS
- ☰ 2.8. HTTP Requests
- ☰ 2.9. HTTP Responses
- ☰ 2.10. HTTP Methods
- ☰ 2.11. HTTP Status Codes
- ☰ 2.12. HTTP Headers
- ☰ 2.13. Cookies
- ☰ 2.14. Sessions
- ☰ 2.15. What Happens When You Enter a URL?
-
- ☰ 3. Development Environment
- ☰ 3.1. Choosing an Operating System
- ☰ 3.2. Linux Basics for Backend Developers
- ☰ 3.3. Command Line Basics
- ☰ 3.4. Environment Variables
- ☰ 3.5. Installing Development Tools
- ☰ 3.6. Code Editors and IDEs
- ☰ 3.7. Git
- ☰ 3.8. GitHub and GitLab
- ☰ 3.9. Creating Your First Repository
- ☰ 3.10. Branches and Commits
- ☰ 3.11. Basic Git Workflow
-
- ☰ 4. Programming Fundamentals
- ☰ 4.1. Variables and Data Types
- ☰ 4.2. Operators
- ☰ 4.3. Conditions
- ☰ 4.4. Loops
- ☰ 4.5. Functions
- ☰ 4.6. Data Structures
- ☰ 4.7. Error Handling
- ☰ 4.8. Modules and Packages
- ☰ 4.9. File Handling
- ☰ 4.10. Object-Oriented Programming
- ☰ 4.11. Classes and Objects
- ☰ 4.12. Inheritance and Composition
- ☰ 4.13. Clean Code Principles
-
- ☰ 5. Python for Backend Development
- ☰ 5.1. Introduction to Python
- ☰ 5.2. Installing Python
- ☰ 5.3. Virtual Environments
- ☰ 5.4. pip and Package Management
- ☰ 5.5. Python Project Structure
- ☰ 5.6. Functions and Type Hints
- ☰ 5.7. Classes and Dataclasses
- ☰ 5.8. Exceptions
- ☰ 5.9. Working with Files
- ☰ 5.9.1. File Downloads
- ☰ 5.9.2. File Validation
- ☰ 5.9.3. Image Uploads
- ☰ 5.9.4. Local File Storage
- ☰ 5.9.5. Object Storage
- ☰ 5.9.6. S3-Compatible Storage
- ☰ 5.9.7. Presigned URLs
- ☰ 5.9.8. Secure File Handling
-
- ☰ 5.10. JSON
- ☰ 5.11. Date and Time
- ☰ 5.12. Logging
- ☰ 5.13. Configuration
- ☰ 5.14. Environment Variables
- ☰ 5.15. Async Programming
- ☰ 5.16. Python Backend Best Practices
-
- ☰ 6. Introduction to Web Backends
- ☰ 6.1. Building a Simple Web Server
- ☰ 6.2. Routing
- ☰ 6.3. URL Parameters
- ☰ 6.4. Query Parameters
- ☰ 6.5. Request Bodies
- ☰ 6.6. Form Data
- ☰ 6.7. JSON Requests
- ☰ 6.8. Returning Responses
- ☰ 6.9. Templates
- ☰ 6.10. Static Files
- ☰ 6.11. Middleware
- ☰ 6.12. Application Configuration
-
- ☰ 7. REST APIs
- ☰ 7.1. What Is an API?
- ☰ 7.2. What Is REST?
- ☰ 7.3. Resources and Endpoints
- ☰ 7.4. RESTful URL Design
- ☰ 7.5. HTTP Methods in REST
- ☰ 7.6. GET Requests
- ☰ 7.7. POST Requests
- ☰ 7.8. PUT Requests
- ☰ 7.9. PATCH Requests
- ☰ 7.10. DELETE Requests
- ☰ 7.11. Request Validation
- ☰ 7.12. Response Models
- ☰ 7.13. HTTP Status Codes
- ☰ 7.14. Error Responses
- ☰ 7.15. Pagination
- ☰ 7.16. Filtering
- ☰ 7.17. Sorting
- ☰ 7.18. Searching
- ☰ 7.19. API Versioning
- ☰ 7.20. API Documentation
- ☰ 7.21. OpenAPI and Swagger
-
- ☰ 8. FastAPI
- ☰ 8.1. Introduction to FastAPI
- ☰ 8.2. Routes and Path Operations
- ☰ 8.3. Path Parameters
- ☰ 8.4. Pydantic Models
- ☰ 8.5. Data Validation
- ☰ 8.6. Dependency Injection
- ☰ 8.7. Exception Handling
- ☰ 8.8. Background Tasks
- ☰ 8.9. File Uploads
- ☰ 8.10. Async Endpoints
- ☰ 8.11. FastAPI Project Structure
- ☰ 8.12. Building a Complete REST API
-
- ☰ 9. Databases
- ☰ 9.1. Introduction to Databases
- ☰ 9.2. Relational vs NoSQL Databases
- ☰ 9.3. Tables, Rows, and Columns
- ☰ 9.4. Primary Keys
- ☰ 9.5. Foreign Keys
- ☰ 9.6. Relationships
- ☰ 9.7. One-to-One Relationships
- ☰ 9.8. One-to-Many Relationships
- ☰ 9.9. Many-to-Many Relationships
- ☰ 9.10. Database Schema Design
- ☰ 9.11. Normalization
- ☰ 9.12. Indexes
- ☰ 9.13. Transactions
- ☰ 9.14. ACID Properties
-
- ☰ 10. SQL
- ☰ 10.1. Introduction to SQL
- ☰ 10.2. CREATE
- ☰ 10.3. INSERT
- ☰ 10.4. SELECT
- ☰ 10.5. UPDATE
- ☰ 10.6. DELETE
- ☰ 10.7. WHERE
- ☰ 10.8. ORDER BY
- ☰ 10.9. GROUP BY
- ☰ 10.10. Aggregate Functions
- ☰ 10.11. JOINs
- ☰ 10.12. Subqueries
- ☰ 10.13. Constraints
- ☰ 10.14. Indexes
- ☰ 10.15. Transactions
- ☰ 10.16. Common Table Expressions
- ☰ 10.17. SQL Performance Basics
-
- ☰ 11. PostgreSQL
- ☰ 11.1. Introduction to PostgreSQL
- ☰ 11.2. Installing PostgreSQL
- ☰ 11.3. Creating Databases
- ☰ 11.4. Users and Roles
- ☰ 11.5. Permissions
- ☰ 11.6. PostgreSQL Data Types
- ☰ 11.7. Constraints
- ☰ 11.8. Indexes
- ☰ 11.9. Transactions
- ☰ 11.10. JSON Data
- ☰ 11.11. Backup and Restore
- ☰ 11.12. PostgreSQL Performance Basics
-
- ☰ 12. ORM and Database Integration
- ☰ 12.1. What Is an ORM?
- ☰ 12.2. SQLAlchemy
- ☰ 12.3. Models
- ☰ 12.4. Database Sessions
- ☰ 12.5. Creating Records
- ☰ 12.6. Reading Records
- ☰ 12.7. Updating Records
- ☰ 12.8. Deleting Records
- ☰ 12.9. Relationships
- ☰ 12.10. Queries
- ☰ 12.11. Transactions
- ☰ 12.12. Connection Pooling
- ☰ 12.13. Database Migrations
- ☰ 12.14. Alembic
- ☰ 12.15. Repository Pattern
-
- ☰ 13. Authentication
- ☰ 13.1. Authentication vs Authorization
- ☰ 13.2. Password Storage
- ☰ 13.3. Password Hashing
- ☰ 13.4. Registration
- ☰ 13.5. Login
- ☰ 13.6. Tokens
- ☰ 13.7. JSON Web Tokens
- ☰ 13.8. Access Tokens
- ☰ 13.9. Refresh Tokens
- ☰ 13.10. OAuth 2.0
- ☰ 13.11. OpenID Connect
- ☰ 13.12. Social Login
- ☰ 13.13. Logout
- ☰ 13.14. Password Reset
- ☰ 13.15. Email Verification
- ☰ 13.16. Multi-Factor Authentication
-
- ☰ 14. Authorization
- ☰ 14.1. Users and Permissions
- ☰ 14.2. Role-Based Access Control
- ☰ 14.3. Permission-Based Access Control
- ☰ 14.4. Protecting API Endpoints
- ☰ 14.5. Admin Permissions
- ☰ 14.6. Resource Ownership
- ☰ 14.7. Authorization Best Practices
-
- ☰ 15. Backend Security
- ☰ 15.1. Introduction to Web Security
- ☰ 15.2. HTTPS and TLS
- ☰ 15.3. SQL Injection
- ☰ 15.4. Cross-Site Scripting
- ☰ 15.5. Cross-Site Request Forgery
- ☰ 15.6. CORS
- ☰ 15.7. Authentication Attacks
- ☰ 15.8. Brute-Force Protection
- ☰ 15.9. Rate Limiting
- ☰ 15.10. Input Validation
- ☰ 15.11. Secure Password Storage
- ☰ 15.12. Secrets Management
- ☰ 15.13. Secure Cookies
- ☰ 15.14. Security Headers
- ☰ 15.15. File Upload Security
- ☰ 15.16. Dependency Vulnerabilities
- ☰ 15.17. OWASP Top 10
-
- ☰ 16. Caching
- ☰ 16.1. Why Caching Matters
- ☰ 16.2. Application Caching
- ☰ 16.3. Redis
- ☰ 16.3.1. Introduction to Redis
- ☰ 16.3.2. Installing Redis
- ☰ 16.3.3. Keys and Values
- ☰ 16.3.4. Expiration
- ☰ 16.3.5. Distributed Locks
-
- ☰ 16.4. Cache Keys
- ☰ 16.5. Cache Invalidation
- ☰ 16.6. Caching Database Queries
- ☰ 16.7. Distributed Caching
-
- ☰ 17. Background Processing
- ☰ 17.1. Synchronous vs Asynchronous Tasks
- ☰ 17.2. Background Jobs
- ☰ 17.3. Message Queues
- ☰ 17.4. Celery
- ☰ 17.5. Redis as a Message Broker
- ☰ 17.6. Scheduled Tasks
- ☰ 17.7. Retry Strategies
- ☰ 17.8. Handling Failed Jobs
- ☰ 17.9. Email Jobs
- ☰ 17.10. Long-Running Tasks
-
- ☰ 18. Email
- ☰ 18.1. How Email Works
- ☰ 18.2. SMTP
- ☰ 18.3. Sending Emails
- ☰ 18.4. HTML Emails
- ☰ 18.5. Transactional Emails
- ☰ 18.6. Password Reset Emails
- ☰ 18.7. Background Email Delivery
- ☰ 18.8. Email Delivery Best Practices
-
- ☰ 19. Testing
- ☰ 19.1. Why Backend Testing Matters
- ☰ 19.2. Unit Testing
- ☰ 19.3. Integration Testing
- ☰ 19.4. API Testing
- ☰ 19.5. pytest
- ☰ 19.6. Fixtures
- ☰ 19.7. Mocking
- ☰ 19.8. Testing Databases
- ☰ 19.9. Testing Authentication
- ☰ 19.10. Testing FastAPI
- ☰ 19.11. Test Coverage
- ☰ 19.12. Automated Testing
-
- ☰ 20. Logging and Monitoring
- ☰ 20.1. Application Logging
- ☰ 20.2. Log Levels
- ☰ 20.3. Structured Logging
- ☰ 20.4. Request Logging
- ☰ 20.5. Error Logging
- ☰ 20.6. Metrics
- ☰ 20.7. Health Checks
- ☰ 20.8. Monitoring
- ☰ 20.9. Prometheus
- ☰ 20.10. Grafana
- ☰ 20.11. Alerting
- ☰ 20.12. Error Tracking
- ☰ 20.13. Observability Basics
-
- ☰ 21. Docker
- ☰ 21.1. Introduction to Containers
- ☰ 21.2. Docker Architecture
- ☰ 21.3. Images
- ☰ 21.4. Containers
- ☰ 21.5. Dockerfiles
- ☰ 21.6. Building Images
- ☰ 21.7. Running Containers
- ☰ 21.8. Volumes
- ☰ 21.9. Networks
- ☰ 21.10. Docker Compose
- ☰ 21.11. FastAPI with Docker
- ☰ 21.12. PostgreSQL with Docker
- ☰ 21.13. Redis with Docker
- ☰ 21.14. Multi-Container Applications
- ☰ 21.15. Production Docker Best Practices
-
- ☰ 22. Web Servers and Reverse Proxies
- ☰ 22.1. Application Servers
- ☰ 22.2. Uvicorn
- ☰ 22.3. Gunicorn
- ☰ 22.4. Reverse Proxies
- ☰ 22.5. Nginx
- ☰ 22.6. Traefik
- ☰ 22.7. HTTPS
- ☰ 22.8. TLS Certificates
- ☰ 22.9. Let's Encrypt
- ☰ 22.10. Load Balancing
-
- ☰ 23. Deployment
- ☰ 23.1. Development vs Production
- ☰ 23.2. Preparing an Application for Production
- ☰ 23.3. Linux Server Setup
- ☰ 23.4. Deploying with Docker
- ☰ 23.5. Environment Configuration
- ☰ 23.6. Managing Secrets
- ☰ 23.7. HTTPS Configuration
- ☰ 23.8. Production Logging
- ☰ 23.9. Backups
- ☰ 23.10. Zero-Downtime Deployment
- ☰ 23.11. Rollbacks
-
- ☰ 24. CI/CD
- ☰ 24.1. Introduction to CI/CD
- ☰ 24.2. Continuous Integration
- ☰ 24.3. Continuous Delivery
- ☰ 24.4. Continuous Deployment
- ☰ 24.5. CI/CD Pipelines
- ☰ 24.6. GitHub Actions
- ☰ 24.7. GitLab CI/CD
- ☰ 24.8. Building Docker Images
- ☰ 24.9. Container Registries
- ☰ 24.10. Deployment Pipelines
- ☰ 24.11. Staging Environments
- ☰ 24.12. Production Environments
- ☰ 24.13. Rollback Strategies
-
- ☰ 25. Backend Architecture
- ☰ 25.1. Monolithic Architecture
- ☰ 25.2. Modular Monoliths
- ☰ 25.3. Microservices
- ☰ 25.4. Layered Architecture
- ☰ 25.5. Service Layer
- ☰ 25.6. Separation of Concerns
- ☰ 25.7. Domain-Driven Design Basics
- ☰ 25.8. Event-Driven Architecture
- ☰ 25.9. Choosing an Architecture
-
- ☰ 26. Performance and Scalability
- ☰ 26.1. Understanding Backend Performance
- ☰ 26.2. CPU-Bound vs I/O-Bound Work
- ☰ 26.3. Asynchronous Programming
- ☰ 26.4. Database Optimization
- ☰ 26.5. Query Optimization
- ☰ 26.6. Indexing
- ☰ 26.7. Load Balancing
- ☰ 26.8. Horizontal Scaling
- ☰ 26.9. Vertical Scaling
- ☰ 26.10. Stateless Applications
- ☰ 26.11. Performance Testing
- ☰ 26.12. Load Testing
-
- ☰ 27. Advanced API Development
- ☰ 27.1. WebSockets
- ☰ 27.2. Server-Sent Events
- ☰ 27.3. GraphQL
- ☰ 27.4. Webhooks
- ☰ 27.5. Idempotency
- ☰ 27.6. API Rate Limiting
- ☰ 27.7. API Keys
- ☰ 27.8. API Gateways
- ☰ 27.9. Distributed APIs
- ☰ 27.10. Real-Time Applications
-
- ☰ 28. Production Backend Engineering
- ☰ 28.1. Configuration Management
- ☰ 28.2. Database Backups
- ☰ 28.3. Disaster Recovery
- ☰ 28.4. Graceful Shutdown
- ☰ 28.5. Fault Tolerance
- ☰ 28.6. Circuit Breakers
- ☰ 28.7. High Availability
- ☰ 28.8. Monitoring Production Systems
- ☰ 28.9. Incident Response
-
- ☰ 29. Project 1: Task Management API
- ☰ 29.1. Project Requirements
- ☰ 29.2. Project Structure
- ☰ 29.3. Database Design
- ☰ 29.4. CRUD Operations
- ☰ 29.5. Validation
-
- ☰ 30. Project 2: Authentication System
- ☰ 30.1. User Registration
- ☰ 30.2. JWT Authentication
- ☰ 30.3. Role-Based Authorization
- ☰ 30.4. Security Testing
-
- ☰ 31. Project 3: E-Commerce Backend
- ☰ 31.1. Architecture
- ☰ 31.2. User Management
- ☰ 31.3. Product Management
- ☰ 31.4. Categories
- ☰ 31.5. Shopping Cart
- ☰ 31.6. Orders
- ☰ 31.7. Inventory
- ☰ 31.8. Payments
- ☰ 31.9. Admin API
-
- ☰ 32. Final Project: Production Backend
- ☰ 32.1. Planning the Application
- ☰ 32.2. Designing the Architecture
- ☰ 32.3. Designing the Database
- ☰ 32.4. Building the REST API
- ☰ 32.5. Authentication and Authorization
- ☰ 32.6. PostgreSQL Integration
- ☰ 32.7. Redis Integration
- ☰ 32.8. Background Workers
- ☰ 32.9. File Storage
- ☰ 32.10. Dockerizing the Application
- ☰ 32.11. CI/CD Pipeline
- ☰ 32.12. Production Deployment
- ☰ 32.13. HTTPS and Domain Setup
- ☰ 32.14. Performance Optimization
- ☰ 32.15. Security Review
- ☰ 32.16. Documentation
-
- ☰ 33. Backend Developer Roadmap
- ☰ 33.1. What You Should Know Now
- ☰ 33.2. Building a Portfolio
- ☰ 33.3. Creating Backend Projects
- ☰ 33.4. Contributing to Open Source
- ☰ 33.5. Preparing for Backend Interviews
- ☰ 33.6. Backend Interview Questions
- ☰ 33.7. Database Interview Questions
- ☰ 33.8. API Interview Questions
- ☰ 33.9. Linux and Docker Interview Questions
- ☰ 33.10. Where to Go Next
-
Comments
Please login to add a comment.
Don't have an account? Register now!