9. Integration in Web Applications

Integration in Web Applications

You already know how to talk to a database using raw SQL and low-level drivers. In real web applications, developers rarely write raw SQL by hand. Instead, they use Object-Relational Mappers (ORMs) that bridge the gap between the relational world of SQL and the object-oriented world of your programming language.

Two concrete ORMs appear throughout this chapter side by side:

• Prisma — a modern ORM for Node.js / TypeScript, the standard choice in Next.js applications.
• Eloquent — the ORM built into the Laravel PHP framework; the most widely used PHP ORM.

Both solve the same problems but make different design choices — especially around how models relate to the database. Keeping both in view makes the underlying concepts much clearer than studying either one alone.

1. What Is an ORM?

An ORM (Object-Relational Mapper) is a library that lets you interact with a relational database using your programming language’s objects and types — without writing SQL by hand.

Instead of this:

SELECT * FROM users WHERE id = 42;

You write:

Prisma (Next.js)

const user = await prisma.user.findUnique({ where: { id: 42 } });

Eloquent (Laravel)

$user = User::find(42);

The ORM translates your code into the correct SQL, sends it to the database, and maps the result rows back to typed objects.

1.1 Why Use an ORM?

	Without ORM	With ORM
Query language	Raw SQL strings	Language-native method calls — readable, easy to refactor
Result mapping	Manual, per column	Auto-mapped to typed objects
Error feedback	SQL errors only at runtime	Type/syntax errors caught earlier
Schema tracking	Manual ALTER TABLE	Versioned migration files, committed to git
Database portability	DB-specific syntax per vendor	Switch DB by changing one config line
Performance control	Full — you write exactly what runs	Partial — ORM generates SQL; inspect it for slow queries
Complex queries	Straightforward	Sometimes requires dropping down to raw SQL
N+1 risk	Low — explicit in code	Higher — easy to trigger accidentally in loops

1.2 ORM Setup

Prisma (Next.js)

npm install prisma @prisma/client
npx prisma init

This creates a prisma/schema.prisma file where you define your data models in Prisma’s own schema language:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  posts     Post[]
  createdAt DateTime @default(now())
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}

The @relation decorator tells Prisma that Post.authorId is a foreign key pointing to User.id. Prisma uses this to generate typed query methods and to enforce referential integrity.

Eloquent (Laravel)

Laravel ships with Eloquent built in. Models are plain PHP classes that extend Model:

app/Models/User.php

<?php
class User extends Model
{
    protected $fillable = ['name', 'email'];

    public function posts()
    {
        return $this->hasMany(Post::class);
    }
}

app/Models/Post.php

<?php
class Post extends Model
{
    protected $fillable = ['title', 'content', 'published', 'user_id'];

    public function author()
    {
        return $this->belongsTo(User::class, 'user_id');
    }
}

Eloquent infers the table name (users, posts) and primary key (id) by convention. Relations are declared as methods returning hasMany, belongsTo, etc.

Video

YouTube: “Prisma Crash Course” — covers Prisma setup, models, and basic CRUD in about 30 minutes.

Play

1.3 When the ORM Is Not Enough

ORMs cover the everyday cases well — simple CRUD, filtering, sorting, loading related data. But there are situations where the ORM’s generated SQL is either too limited or too slow, and you need to write SQL directly.

Typical scenarios where raw SQL is the better choice:

• Window functions (RANK(), LAG(), LEAD(), running totals) — most ORMs have no API for these.
• Recursive queries (WITH RECURSIVE) for tree structures like categories, org charts, or thread hierarchies.
• Database-specific features — PostgreSQL’s full-text search (tsvector, @@), JSONB operators (@>, #>>), or LATERAL joins are not exposed by most ORMs.
• Highly optimised reporting queries — a hand-written query with carefully placed indexes, partial aggregates, and specific join order can be significantly faster than what the ORM generates.

Both Prisma and Eloquent provide an escape hatch to raw SQL for exactly these cases:

Prisma (Next.js)

$queryRaw returns typed results; $executeRaw is for statements that return no rows (INSERT, UPDATE, DELETE).

import { prisma } from '@/lib/prisma';

// Count posts per category using a window function
const result = await prisma.$queryRaw<
  { category: string; postCount: bigint; rank: bigint }[]
>`
  SELECT
    c.name                                    AS category,
    COUNT(p.id)                               AS "postCount",
    RANK() OVER (ORDER BY COUNT(p.id) DESC)   AS rank
  FROM "Category" c
  LEFT JOIN "Post" p ON p."categoryId" = c.id
  GROUP BY c.id, c.name
  ORDER BY rank;
`;

// Parameterized — Prisma.sql prevents SQL injection
import { Prisma } from '@prisma/client';

const minPosts = 2;
const popular = await prisma.$queryRaw<{ category: string }[]>`
  SELECT c.name AS category
  FROM "Category" c
  JOIN "Post" p ON p."categoryId" = c.id
  GROUP BY c.id, c.name
  HAVING COUNT(p.id) >= ${minPosts}
`;

Eloquent (Laravel)

DB::select() returns an array of plain objects; DB::statement() is for DDL or DML without a result set.

use Illuminate\Support\Facades\DB;

// Count posts per category using a window function
$result = DB::select("
    SELECT
        c.name                                   AS category,
        COUNT(p.id)                              AS post_count,
        RANK() OVER (ORDER BY COUNT(p.id) DESC)  AS rank
    FROM categories c
    LEFT JOIN posts p ON p.category_id = c.id
    GROUP BY c.id, c.name
    ORDER BY rank
");

// Parameterized — always use bindings, never string concatenation
$minPosts = 2;
$popular = DB::select("
    SELECT c.name AS category
    FROM categories c
    JOIN posts p ON p.category_id = c.id
    GROUP BY c.id, c.name
    HAVING COUNT(p.id) >= ?
", [$minPosts]);

SQL injection with raw queries

When writing raw SQL, never concatenate user input directly into the query string:

// DANGEROUS — SQL injection vulnerability
const name = userInput;
await prisma.$queryRawUnsafe(`SELECT * FROM "User" WHERE name = '${name}'`);

Always use parameterized queries. In Prisma, embed values directly in the tagged template literal (as shown above) — Prisma handles escaping automatically. In Eloquent, pass values as the second argument array to DB::select().

2. Migrations

A migration is a versioned, incremental change to your database schema. Every time you change a model (add a column, rename a table, add an index), you create a migration file that tracks that change.

2.1 Why Migrations Matter

Without migrations:

• Your local database is out of sync with your team’s databases.
• Deploying to production means manually running ALTER TABLE commands — error-prone and not reproducible.
• There is no history of what changed and when.

With migrations:

• Every schema change is a file committed to version control.
• A single command applies exactly the right changes in the right order on any environment.
• The history is explicit, so rollback is possible.

2.2 Creating and Applying a Migration

Prisma (Next.js)

Edit schema.prisma first — for example, add a bio field to User:

model User {
  // ...existing fields...
  bio String?
}

Then generate and apply the migration:

# Development: create migration file + apply to local DB immediately
npx prisma migrate dev --name add_user_bio

# Production (CI/CD pipeline): apply pending migrations
npx prisma migrate deploy

Prisma generates prisma/migrations/20240601120000_add_user_bio/migration.sql:

ALTER TABLE "User" ADD COLUMN "bio" TEXT;

Eloquent (Laravel)

Generate an empty migration file:

php artisan make:migration add_bio_to_users_table --table=users

Edit the generated file in database/migrations/:

public function up(): void
{
    Schema::table('users', function (Blueprint $table) {
        $table->text('bio')->nullable();
    });
}

public function down(): void
{
    Schema::table('users', function (Blueprint $table) {
        $table->dropColumn('bio');
    });
}

Apply the migration:

# Apply all pending migrations
php artisan migrate

# Roll back the last batch
php artisan migrate:rollback

You commit the migration file to git. Every developer and every deployment pipeline runs the same migrations in the correct order.

Never edit migration files manually

Once a migration file is committed and applied in any environment, treat it as immutable. Editing it will break checksums and cause deployment failures. If you need to fix something, create a new migration.

2.3 Migration Workflow in Practice

Prisma (Next.js)

1. Edit schema.prisma
       │
       ▼
2. npx prisma migrate dev --name <description>
       │  (creates migration SQL + applies to local DB)
       ▼
3. git add prisma/migrations/... && git commit
       │
       ▼
4. CI/CD runs: npx prisma migrate deploy
       │  (applies to staging / production)
       ▼
5. Done — all environments in sync

Eloquent (Laravel)

1. php artisan make:migration <name> --table=<table>
       │
       ▼
2. Edit up() and down() in the migration file
       │
       ▼
3. git add database/migrations/... && git commit
       │
       ▼
4. Deploy: php artisan migrate
       │  (applies to staging / production)
       ▼
5. Done — all environments in sync

Video

YouTube: “Prisma - The Easiest Way to Work with a Database in Next.js” — full setup including migrations in a real Next.js project.

Play

3. The N+1 Problem

The N+1 problem is the most common performance trap when using an ORM. It occurs when loading a list of records and then individually loading related data for each one — resulting in far more database queries than necessary.

3.1 Understanding the Problem

Imagine you want to display a list of blog posts together with each post’s author name.

Naive approach — broken:

Prisma (Next.js)

// 1 query: load all posts
const posts = await prisma.post.findMany();

for (const post of posts) {
  // N queries: one per post to load the author
  const author = await prisma.user.findUnique({ where: { id: post.authorId } });
  console.log(`${post.title} by ${author.name}`);
}

Eloquent (Laravel)

// 1 query: load all posts
$posts = Post::all();

foreach ($posts as $post) {
    // N queries: Eloquent fires one query per post automatically (lazy loading)
    echo $post->title . ' by ' . $post->author->name;
}

If you have 100 posts, both examples execute 101 queries:

SELECT * FROM posts;                -- 1 query
SELECT * FROM users WHERE id = 1;  -- for post 1
SELECT * FROM users WHERE id = 2;  -- for post 2
...                                 -- 98 more

At production scale this destroys performance. The Eloquent example is especially dangerous because the extra queries are invisible — they are triggered silently when you access $post->author.

3.2 The Fix: Eager Loading

Tell the ORM to load the related data together with the parent records in a JOIN:

Prisma (Next.js)

// 1 query: posts + authors in one JOIN
const posts = await prisma.post.findMany({
  include: {
    author: true,   // JOIN users onto posts
  },
});

for (const post of posts) {
  // author is already loaded — no extra query
  console.log(`${post.title} by ${post.author.name}`);
}

Eloquent (Laravel)

// 1 query: posts + authors in one JOIN
$posts = Post::with('author')->get();

foreach ($posts as $post) {
    // author is already loaded — no extra query
    echo $post->title . ' by ' . $post->author->name;
}

Generated SQL (both cases):

SELECT posts.*, users.id, users.name, users.email
FROM posts
LEFT JOIN users ON users.id = posts.user_id;

One query, all data retrieved. This is called eager loading.

Video

YouTube: “N+1 Queries: The Easiest Way To Improve Performance” — clear explanation with practical examples.

Play

Video

YouTube: “30 Days to Learn Laravel, Ep 13 - Eager Loading and the N+1 Problem” — Laravel-specific explanation of the N+1 problem and how to fix it with eager loading.

Play

4. Lazy Loading vs. Eager Loading

Loading strategies determine when related data is fetched from the database.

4.1 Lazy Loading

Lazy loading means related data is only loaded when you actually access it — not when the parent record is loaded.

Prisma (Next.js)

Prisma does not support automatic lazy loading. Related data is never loaded unless you explicitly ask for it with include or select.

const post = await prisma.post.findUnique({ where: { id: 1 } });

console.log(post.author); // undefined — not loaded, no automatic query fires

// You must issue a separate query manually:
const author = await prisma.user.findUnique({ where: { id: post.authorId } });

This is a deliberate design choice: Prisma forces you to be explicit, which prevents accidental N+1 problems.

Eloquent (Laravel)

Eloquent does support automatic lazy loading. Accessing a relation property that has not been loaded yet automatically fires a database query:

$post = Post::find(1);
// $post->author is not loaded yet — no query fired so far

echo $post->author->name;
// Eloquent detects that 'author' is missing and fires:
// SELECT * FROM users WHERE id = ?
// — automatic, transparent, but dangerous in loops (N+1)

Convenient for one-off access, but a serious performance trap inside loops — exactly the N+1 scenario from section 3.

Pros of lazy loading:

• Only loads what is actually accessed.
• Keeps the initial query fast and simple.

Cons of lazy loading:

• In a loop it silently triggers N queries.
• Queries are invisible when reading the code — hard to spot performance problems during review.

4.2 Eager Loading

Eager loading means related data is loaded together with the parent record in the same query — either a JOIN or a second batched query.

Prisma (Next.js)

const post = await prisma.post.findUnique({
  where: { id: 1 },
  include: { author: true },
});

console.log(post.author.name); // already loaded, no extra query

Eloquent (Laravel)

$post = Post::with('author')->find(1);

echo $post->author->name; // already loaded, no extra query

Pros of eager loading:

• Prevents N+1 problems.
• Explicit — a reader can see exactly what is being loaded.
• Fewer round-trips to the database.

Cons of eager loading:

• May load more data than needed.
• Deeply nested relations can produce large JOIN queries.

4.3 Selective Loading

Instead of loading a full related object, request only the specific fields you actually need:

Prisma (Next.js)

const posts = await prisma.post.findMany({
  select: {
    title: true,
    author: {
      select: { name: true },  // only the author's name
    },
  },
});

Eloquent (Laravel)

// Load posts with only the author's id and name
$posts = Post::with('author:id,name')->get();

Generated SQL (both cases):

SELECT posts.title, users.name
FROM posts
LEFT JOIN users ON users.id = posts.user_id;

This is the most efficient approach: load exactly what you need, nothing more.

Strategy	When to use
Eager loading	When you know you will always need the related data
Selective loading	When you only need specific fields from related records
Separate query	When the related data is rarely needed or optional

5. Design Patterns: Active Record vs. Repository

When structuring database access code in an application, two patterns dominate: Active Record and Repository. Prisma and Eloquent are built around different philosophies here — which is exactly why seeing them together is valuable.

5.1 Active Record Pattern

In the Active Record pattern, the model class is both the data container and responsible for its own persistence. The object knows how to save, update, and delete itself.

Eloquent (Laravel)

Eloquent is a textbook Active Record implementation. A model class extends Model and immediately gains save(), delete(), find(), and a fluent query builder — no repository needed.

// Create
$user = new User();
$user->name  = 'Anna';
$user->email = '[email protected]';
$user->save();                          // INSERT INTO users ...

// Find by primary key
$user = User::find(42);
echo $user->name;

// Update
$user->name = 'Anna Müller';
$user->save();                          // UPDATE users SET name = ? WHERE id = 42

// Delete
$user->delete();                        // DELETE FROM users WHERE id = 42

// Query builder — fluent chaining directly on the model
$activeUsers = User::where('active', true)
                   ->orderBy('name')
                   ->get();

Conceptual (TypeScript)

The same idea implemented manually in TypeScript — shown to illustrate the pattern. Prisma itself does not work this way (see section 5.2).

class User {
  id: number;
  name: string;
  email: string;

  async save() {
    if (this.id) {
      await db.query(
        'UPDATE users SET name=$1, email=$2 WHERE id=$3',
        [this.name, this.email, this.id]
      );
    } else {
      const result = await db.query(
        'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id',
        [this.name, this.email]
      );
      this.id = result.rows[0].id;
    }
  }

  async delete() {
    await db.query('DELETE FROM users WHERE id=$1', [this.id]);
  }

  static async findById(id: number): Promise<User> {
    const result = await db.query('SELECT * FROM users WHERE id=$1', [id]);
    return Object.assign(new User(), result.rows[0]);
  }
}

Characteristics:

• The model “knows” how to persist itself.
• Simple and intuitive — data and persistence logic live together.
• The canonical real-world example is Laravel Eloquent (PHP).

Drawbacks:

• Business logic (what an object does) gets mixed with persistence logic (how it is stored).
• Harder to test — you cannot use a model object without a real database connection.

5.2 Repository Pattern

In the Repository pattern, database access is separated into a dedicated repository class. The model is a plain data object with no database knowledge, and the repository handles all queries.

Prisma (Next.js)

Prisma is a Data Mapper ORM — models are plain TypeScript objects with no database methods. This naturally leads to the Repository pattern.

// Plain data object — no database logic
interface User {
  id?: number;
  name: string;
  email: string;
}

// Repository — all database access in one place
class UserRepository {
  async findById(id: number): Promise<User | null> {
    return prisma.user.findUnique({ where: { id } });
  }

  async findAll(): Promise<User[]> {
    return prisma.user.findMany();
  }

  async create(data: { name: string; email: string }): Promise<User> {
    return prisma.user.create({ data });
  }

  async update(id: number, data: Partial<User>): Promise<User> {
    return prisma.user.update({ where: { id }, data });
  }

  async delete(id: number): Promise<void> {
    await prisma.user.delete({ where: { id } });
  }
}

// Usage:
const userRepo = new UserRepository();
const user = await userRepo.create({ name: 'Anna', email: '[email protected]' });

A common convention is to place repositories in a lib/repositories/ directory:

lib/
  repositories/
    UserRepository.ts
    PostRepository.ts
  prisma.ts   ← shared Prisma client instance

Eloquent (Laravel)

Eloquent’s default is Active Record, but many Laravel projects add a repository layer on top for better testability and separation of concerns.

app/Repositories/UserRepository.php

class UserRepository
{
    public function findById(int $id): ?User
    {
        return User::find($id);
    }

    public function findAll(): Collection
    {
        return User::all();
    }

    public function create(array $data): User
    {
        return User::create($data);
    }

    public function update(int $id, array $data): bool
    {
        return User::where('id', $id)->update($data);
    }

    public function delete(int $id): bool
    {
        return User::destroy($id) > 0;
    }
}

// Usage via dependency injection in a controller:
class UserController extends Controller
{
    public function __construct(private UserRepository $users) {}

    public function index()
    {
        return response()->json($this->users->findAll());
    }
}

Characteristics:

• Clear separation between data shape and data access.
• Easy to test: replace the real repository with a fake one for unit tests — no database needed.
• Switching the underlying ORM only requires changing the repository, not the rest of the application.

5.3 Active Record vs. Repository at a Glance

	Active Record	Repository
Where is DB logic?	Inside the model class	In a separate repository class
Model knows about DB?	Yes	No
Testability	Harder — needs a real DB	Easy — repository can be mocked
Best for	Small apps, simple CRUD	Larger apps, complex business logic
Primary example	Eloquent (Laravel / PHP)	Prisma (Next.js / TypeScript)

Video

YouTube: “Active Record Design Pattern Explained” — explains the Active Record pattern with clear examples.

Play

Video

YouTube: “TypeORM Active Record vs Data Mapper” — practical comparison of both patterns in a TypeScript ORM, very close to how Prisma works.

Play

6. Database Connections and Connection Pooling

Every ORM query needs a database connection — a persistent TCP channel between the application and the database server. How those connections are managed has a direct impact on performance, especially under load.

6.1 The Cost of a Database Connection

Opening a new connection is expensive:

• The client performs a TCP handshake with the server.
• PostgreSQL authenticates the user and forks a dedicated backend process for the connection.
• This takes roughly 20–100 ms and allocates several megabytes of RAM on the server side.

Databases also have a hard limit on the number of simultaneous connections (max_connections = 100 in PostgreSQL by default). If every incoming web request opens its own connection, you quickly run out:

100 simultaneous users
  → 100 open connections             (already at the PostgreSQL default limit)
  → each taking 2–5 MB of RAM       (200–500 MB just for connections)
  → 50 ms connection overhead per request

6.2 How a Connection Pool Works

A connection pool is a cache of pre-opened database connections that requests borrow and return:

Without pool:
  Request 1 ──► open connection ──► query ──► close     (+50 ms overhead)
  Request 2 ──► open connection ──► query ──► close     (+50 ms overhead)
  Request 3 ──► open connection ──► query ──► close     (+50 ms overhead)

With pool (pool size = 5):
  ┌─────────────────────────────────────┐
  │            Connection Pool          │
  │  conn-1 ──────────────────────────► PostgreSQL
  │  conn-2 ──────────────────────────► PostgreSQL
  │  conn-3 ──────────────────────────► PostgreSQL
  │  conn-4  (idle, ready)             │
  │  conn-5  (idle, ready)             │
  └─────────────────────────────────────┘
  Request 1 ──► borrow conn-1 ──► query ──► return conn-1
  Request 2 ──► borrow conn-2 ──► query ──► return conn-2
  Request 3 ──► borrow conn-1 ──► query ──► return conn-1  (reused!)

Key behaviours:

• If a free connection is available, it is assigned immediately — no reconnect overhead.
• If all connections are busy, the request waits up to a configurable timeout, then fails with an error.
• The pool keeps connections alive and recycles them across many requests.

6.3 Configuration

Prisma (Next.js)

Prisma manages its own connection pool per PrismaClient instance. The pool size and timeout are set in the DATABASE_URL:

DATABASE_URL="postgresql://user:pass@host:5432/mydb?connection_limit=10&pool_timeout=10"

Parameter	Default	Meaning
connection_limit	num_cpus * 2 + 1	Max open connections in the pool
pool_timeout	10 (seconds)	How long to wait for a free connection before throwing an error

This is also why the global singleton from section 7 matters. Without it, a new PrismaClient (and a new pool) is created on every hot-reload in development — each pool opens its own connections and you exhaust the database limit very quickly:

// lib/prisma.ts — one pool for the entire process lifetime
import { PrismaClient } from '@prisma/client';
const globalForPrisma = globalThis as unknown as { prisma: PrismaClient };
export const prisma = globalForPrisma.prisma ?? new PrismaClient();
if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;

Serverless and Edge deployments

In serverless environments (Vercel Functions, AWS Lambda), each function invocation can spin up a fresh process — meaning a new PrismaClient and a new pool per invocation. Under load this creates hundreds of short-lived connections and exhausts max_connections instantly.

The standard solution is an external connection pooler like PgBouncer or Prisma Accelerate that sits between the functions and the database and multiplexes many short-lived connections into a smaller number of long-lived ones.

Eloquent (Laravel)

PHP-FPM runs a fixed number of worker processes, each of which handles one request at a time and maintains its own database connection. The “pool” in a traditional Laravel setup is therefore the pool of PHP-FPM workers.

Configure the connection in config/database.php:

'pgsql' => [
    'driver'   => 'pgsql',
    'host'     => env('DB_HOST', '127.0.0.1'),
    'port'     => env('DB_PORT', '5432'),
    'database' => env('DB_DATABASE'),
    'username' => env('DB_USERNAME'),
    'password' => env('DB_PASSWORD'),
    'options'  => [
        PDO::ATTR_PERSISTENT => true,   // reuse the connection across requests
    ],
],

PDO::ATTR_PERSISTENT tells PHP to keep the connection open and reuse it for the next request handled by the same worker process — avoiding the reconnect overhead.

PHP-FPM pool size (number of workers = max simultaneous DB connections) is set in php-fpm.conf:

; /etc/php/8.x/fpm/pool.d/www.conf
pm = dynamic
pm.max_children     = 20   ; max simultaneous workers → max DB connections
pm.start_servers    = 5
pm.min_spare_servers = 5
pm.max_spare_servers = 15

High-concurrency and PgBouncer

When pm.max_children is large (e.g., 100+) or when many separate PHP-FPM servers connect to one database, the number of direct connections can exceed PostgreSQL’s max_connections. In these cases, add PgBouncer as a connection pooler in front of PostgreSQL. The application connects to PgBouncer (typically on port 6432), and PgBouncer maintains a smaller, stable pool of real connections to PostgreSQL.

6.4 Pool Size and Performance

There is a common misconception that a larger pool is always better. In practice, more connections to PostgreSQL means more parallel backend processes competing for CPU and RAM on the database server.

The rule of thumb for pool size:

optimal pool size ≈ (number of DB server CPU cores) × 2

A database server with 4 cores handles roughly 8 parallel queries efficiently. Additional connections beyond that wait for CPU time and do not improve throughput — they only increase memory usage and scheduling overhead.

Pool too small	Pool too large
Requests queue up waiting for a free connection	DB server CPU and RAM under pressure
High latency spikes under load	More context switching, slower queries
Errors when pool_timeout is exceeded	May hit PostgreSQL max_connections limit

Monitoring active connections (PostgreSQL):

-- Current connections per application and state
SELECT application_name, state, count(*)
FROM pg_stat_activity
WHERE datname = 'your_database'
GROUP BY application_name, state
ORDER BY count DESC;

Watch for many connections stuck in idle in transaction — a sign that connections are not being returned to the pool promptly after a transaction.

7. Putting It All Together: A Practical Example

A complete database access layer for a blog application — migrations, schema, repository, eager loading, and a page/controller. All with no N+1 problem.

Schema

Prisma (Next.js)

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String
  posts Post[]
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  tags      Tag[]
  createdAt DateTime @default(now())
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]
}

Eloquent (Laravel)

database/migrations/xxxx_create_posts_table.php

Schema::create('posts', function (Blueprint $table) {
    $table->id();
    $table->string('title');
    $table->text('content')->nullable();
    $table->boolean('published')->default(false);
    $table->foreignId('user_id')->constrained()->cascadeOnDelete();
    $table->timestamps();
});

// database/migrations/xxxx_create_tags_table.php
Schema::create('tags', function (Blueprint $table) {
    $table->id();
    $table->string('name')->unique();
});

Schema::create('post_tag', function (Blueprint $table) {
    $table->foreignId('post_id')->constrained()->cascadeOnDelete();
    $table->foreignId('tag_id')->constrained()->cascadeOnDelete();
    $table->primary(['post_id', 'tag_id']);
});

app/Models/Post.php

class Post extends Model {
    protected $fillable = ['title', 'content', 'published', 'user_id'];

    public function author() { return $this->belongsTo(User::class, 'user_id'); }
    public function tags()   { return $this->belongsToMany(Tag::class); }
}

Repository

Prisma (Next.js)

// lib/prisma.ts — shared singleton (prevents connection exhaustion on hot-reload)
import { PrismaClient } from '@prisma/client';
const globalForPrisma = globalThis as unknown as { prisma: PrismaClient };
export const prisma = globalForPrisma.prisma ?? new PrismaClient();
if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;

lib/repositories/PostRepository.ts

import { prisma } from '../prisma';

export class PostRepository {
  async findAllPublished() {
    return prisma.post.findMany({
      where: { published: true },
      include: {
        author: { select: { name: true } },  // eager load — no N+1
        tags:   { select: { name: true } },
      },
      orderBy: { createdAt: 'desc' },
    });
  }

  async findById(id: number) {
    return prisma.post.findUnique({
      where: { id },
      include: { author: true, tags: true },
    });
  }

  async create(data: { title: string; content: string; authorId: number; tagNames: string[] }) {
    return prisma.post.create({
      data: {
        title:   data.title,
        content: data.content,
        author:  { connect: { id: data.authorId } },
        tags: {
          connectOrCreate: data.tagNames.map(name => ({
            where:  { name },
            create: { name },
          })),
        },
      },
      include: { author: true, tags: true },
    });
  }
}

Eloquent (Laravel)

app/Repositories/PostRepository.php

class PostRepository
{
    public function findAllPublished(): Collection
    {
        return Post::with(['author:id,name', 'tags:id,name'])  // eager load — no N+1
                   ->where('published', true)
                   ->orderByDesc('created_at')
                   ->get();
    }

    public function findById(int $id): ?Post
    {
        return Post::with(['author', 'tags'])->find($id);
    }

    public function create(array $data, array $tagNames): Post
    {
        $post = Post::create($data);
        $tagIds = Tag::whereIn('name', $tagNames)->pluck('id');
        $post->tags()->sync($tagIds);
        return $post->load(['author', 'tags']);
    }
}

Page / Controller

Prisma (Next.js)

// app/blog/page.tsx (Next.js App Router)
import { PostRepository } from '@/lib/repositories/PostRepository';

export default async function BlogPage() {
  const repo  = new PostRepository();
  const posts = await repo.findAllPublished();

  return (
    <main>
      {posts.map(post => (
        <article key={post.id}>
          <h2>{post.title}</h2>
          <p>by {post.author.name}</p>
          <p>{post.tags.map(t => t.name).join(', ')}</p>
        </article>
      ))}
    </main>
  );
}

Eloquent (Laravel)

app/Http/Controllers/BlogController.php

class BlogController extends Controller
{
    public function __construct(private PostRepository $posts) {}

    public function index()
    {
        $posts = $this->posts->findAllPublished();
        return view('blog.index', compact('posts'));
    }
}

{{-- resources/views/blog/index.blade.php --}}
@foreach ($posts as $post)
  <article>
    <h2>{{ $post->title }}</h2>
    <p>by {{ $post->author->name }}</p>
    <p>{{ $post->tags->pluck('name')->join(', ') }}</p>
  </article>
@endforeach

Both versions execute exactly one SQL query (with JOINs) regardless of how many posts exist — no N+1 problem.

Summary

Concept	Key point
ORM	Maps database rows to objects; generates SQL from code (Prisma, Eloquent)
Migration	Versioned, committed schema changes applied in order across environments
N+1 Problem	1 query for a list + N queries for related data; fix with eager loading
Lazy Loading	Related data loaded on first access — Eloquent supports it, Prisma does not
Eager Loading	Related data loaded upfront in a JOIN; explicit, safe, recommended
Active Record	Model handles its own persistence — the Eloquent approach
Repository	Dedicated class for all DB access — the natural Prisma approach

The combination of migrations + ORM + Repository pattern + eager loading is the standard architecture for production-quality web applications, whether you build with Next.js/Prisma or Laravel/Eloquent.