Intermediate level · Module 5: Using Databases from Applications · Lesson 3 of 3
Transactions, Connections and ORMs
Use transactions from code, manage connections efficiently and understand what ORMs do.
In this lesson you will
- Commit and roll back transactions from code
- Compare raw SQL, query builders and ORMs
Key concepts
commit and rollback in codeconnection poolingORMN+1 query problem
The lesson
In code, a transaction is committed after all statements succeed and rolled back in an except block if any fails. Registering a household and its members must be one transaction.
Web applications use connection pools to reuse connections instead of opening a new one for every request.
Object–relational mappers (ORMs) such as SQLAlchemy or Django's ORM map tables to classes and write SQL for you. They speed up development, but can generate inefficient queries — such as one query per row (the N+1 problem). Know the SQL behind your ORM calls.
Step by step
- Register a household and members in one transaction.
- Force an error and confirm the rollback.
- Read a short ORM example and the SQL it generates.
- Identify an N+1 pattern and rewrite it as one query.
Practice activity
Complete the household registration script from the practical activity.
Expected result: Households and members are saved together or not at all, using parameterised queries.
Check your understanding
-
When should a transaction be rolled back in code?
Show answer
When any statement in it fails.
-
What is the N+1 query problem?
Show answer
Running one query for a list and then one more query for every item in it.
Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.