Software Engineering Roadmap

Learn how to build, test, deploy, and maintain robust software systems.

Overall Progress0%

Databases & ORMs

Explanation

A backend application needs to store and retrieve data, and most of the time that data lives in a relational database. In Computer Science you learned SQL, normalization, and ACID. This lesson focuses on the application side: how a backend talks to a database, what an Object-Relational Mapper (ORM) is and why it is useful, how migrations version your schema, and how to avoid common performance traps like the N+1 query problem. The examples use Prisma and SQLAlchemy, two popular ORMs, but the ideas — models, relationships, migrations, connection pooling, and transactions — apply to any ORM or query builder.

Why Applications Use Databases

Application

↓

Data access layer

↓

Database

↓

Persistent data

Databases provide durable storage so data survives restarts and can be queried efficiently.

What Is an ORM?

An Object-Relational Mapper maps between application objects (classes/records) and database tables, translating your code into SQL.

Application object

↓

ORM

↓

SQL

↓

Database table

ORM Benefits and Tradeoffs

Benefits: productivity, type safety, relationships, migrations, readable queries.
Tradeoffs: abstraction hides SQL; complex queries may need raw SQL; generated SQL must be understood for performance.

ORM vs Query Builder vs Raw SQL

Approach	What it is	When useful
ORM	Maps objects to tables	Typical CRUD and relationships
Query builder	Builds SQL programmatically	When you want control without raw strings
Raw SQL	Write SQL directly	Complex or highly-tuned queries

Models and Relationships

A model represents a table. Relationships (one-to-one, one-to-many, many-to-many) are expressed as references between models.

prisma

1model User {2  id    Int    @id @default(autoincrement())3  name  String4  posts Post[]5}6 7model Post {8  id      Int    @id @default(autoincrement())9  title   String10  userId  Int11  user    User   @relation(fields: [userId], references: [id])12}

CRUD Through an ORM

prisma

1// create2await prisma.user.create({ data: { name: "Ada" } })3 4// find5const user = await prisma.user.findUnique({ where: { id: 1 } })6 7// update8await prisma.user.update({ where: { id: 1 }, data: { name: "Grace" } })9 10// delete11await prisma.user.delete({ where: { id: 1 } })

Database Connections and Pooling

Opening a database connection is expensive. A connection pool keeps a set of reusable connections so the application can borrow and return them quickly, which matters when many requests arrive at once.

Migrations

A migration is a versioned, ordered change to the database schema. Migrations let you evolve the schema safely across environments instead of editing production by hand.

Change schema

↓

Generate migration

↓

Apply to database

↓

Deploy

Seeding

Seeding inserts initial or sample data, useful for development and tests.

The N+1 Query Problem

If you load a list of users, then query each user’s posts one at a time, you make 1 query for the users plus N queries for their posts — N+1 queries. This is slow.

text

11 query for users2+ N queries for posts (one per user)3= N+1 queries

The fix is to fetch related data in one go with a join or eager loading.

Transactions in Application Code

When several writes must succeed or fail together, wrap them in a transaction. For example, creating an order, decreasing inventory, and recording a payment must be atomic.

Syntax

Prisma schema and queryprisma

1model User {2  id    Int    @id @default(autoincrement())3  name  String4  posts Post[]5}6 7model Post {8  id     Int    @id @default(autoincrement())9  title  String10  userId Int11  user   User   @relation(fields: [userId], references: [id])12}13 14// Load a user and all their posts in one query (avoids N+1)15const user = await prisma.user.findUnique({16  where: { id: 1 },17  include: { posts: true }18});

The include statement eager-loads related posts, avoiding the N+1 problem.

Examples

One-to-many relationship

One user has many posts.

text

1User (1) ──< Post (many)

Output

(a foreign key on Post references User)

N+1 vs eager loading

Eager loading fetches related data in fewer queries.

text

1Bad: 1 + N queries2Good: 1 query with a join/include

Output

(eager loading is dramatically faster)

Key Takeaways

An ORM maps application objects to database tables.
ORMs help with CRUD, relationships, and migrations; raw SQL still has its place.
Connection pools reuse connections for performance.
Migrations version schema changes safely.
The N+1 problem comes from one query per related item; eager-load instead.
Use transactions when multiple writes must succeed or fail together.

← PreviousRESTful APIs Next →Unit Testing