DEV Community: CodeWithDhanian

Middleware & Request-Response Pipeline

CodeWithDhanian — Sat, 15 Aug 2026 06:11:11 +0000

Middleware is one of the most important concepts in backend engineering because it controls what happens between receiving an HTTP request and sending the HTTP response.

In frameworks such as Express.js, middleware functions sit inside a request-processing pipeline. Each middleware can inspect the request, modify the request or response, perform business-independent work, terminate the request, or pass control forward with next(). ([Express.js][1])

A useful mental model is:

Client
  |
  | HTTP Request
  v
┌─────────────────────┐
│ Request Parsing     │
└──────────┬──────────┘
           v
┌─────────────────────┐
│ Logging Middleware  │
└──────────┬──────────┘
           v
┌─────────────────────┐
│ Authentication      │
└──────────┬──────────┘
           v
┌─────────────────────┐
│ Authorization        │
└──────────┬──────────┘
           v
┌─────────────────────┐
│ Validation          │
└──────────┬──────────┘
           v
┌─────────────────────┐
│ Route Handler       │
└──────────┬──────────┘
           v
┌─────────────────────┐
│ Database / Service  │
└──────────┬──────────┘
           v
┌─────────────────────┐
│ Error Middleware    │
└──────────┬──────────┘
           v
        Response

4.1 What Middleware Actually Is

An Express middleware function generally receives three important arguments:

(req, res, next)

req — the incoming HTTP request
res — the outgoing HTTP response
next — function used to transfer control to the next middleware

A minimal middleware looks like this:

function logger(req, res, next) {
  console.log(`${req.method} ${req.originalUrl}`);

  next();
}

app.use(logger);

The important operation is:

next();

If middleware does not send a response, end the request, or call next(), the request can remain unresolved. ([Express.js][1])

4.2 The Request-Response Lifecycle

Consider:

POST /api/users
Content-Type: application/json
Authorization: Bearer TOKEN

The backend can process it through multiple stages:

HTTP Request
     |
     v
Node.js HTTP Server
     |
     v
Express Application
     |
     v
Global Middleware
     |
     +---- Request ID
     |
     +---- Logging
     |
     +---- JSON Parsing
     |
     +---- CORS
     |
     +---- Security
     |
     v
Router
     |
     v
Authentication
     |
     v
Authorization
     |
     v
Validation
     |
     v
Controller
     |
     v
Service
     |
     v
Database
     |
     v
Controller
     |
     v
Response Middleware
     |
     v
HTTP Response

Middleware order matters. Express executes middleware in the order in which it is registered. ([Express.js][2])

For example:

app.use(authMiddleware);
app.use(validationMiddleware);

app.post("/users", createUser);

The request must pass through:

authMiddleware
      ↓
validationMiddleware
      ↓
createUser

Changing the order changes the behavior.

4.3 Middleware Can Do Four Fundamental Things

1. Execute logic

function logger(req, res, next) {
  console.log(req.method);
  next();
}

2. Modify the request

function attachRequestId(req, res, next) {
  req.requestId = crypto.randomUUID();
  next();
}

Later code can access:

console.log(req.requestId);

3. Modify the response

function securityHeaders(req, res, next) {
  res.setHeader("X-API-Version", "1");
  next();
}

4. End the request

function maintenanceMode(req, res, next) {
  return res.status(503).json({
    error: "Service temporarily unavailable"
  });
}

There is no next() because the middleware has already completed the request-response cycle. Express documents middleware as being able to execute code, modify req/res, end the cycle, or call next(). ([Express.js][1])

4.4 `next()` Is the Control Mechanism

Think of next() as:

"Middleware finished its job.
Continue processing this request."

Example:

function authenticate(req, res, next) {
  const token = req.headers.authorization;

  if (!token) {
    return res.status(401).json({
      error: "Authentication required"
    });
  }

  next();
}

The important distinction is:

next();

means:

Continue

while:

return res.status(401).json(...);

means:

Stop

A common mistake is accidentally doing both:

if (!token) {
  res.status(401).json({ error: "Unauthorized" });
}

next();

This can cause the pipeline to continue after a response has already been sent.

Prefer:

if (!token) {
  return res.status(401).json({
    error: "Unauthorized"
  });
}

next();

4.5 Application-Level Middleware

Application-level middleware applies to the entire application or a broad section of it.

app.use(express.json());

app.use((req, res, next) => {
  console.log(`${req.method} ${req.url}`);
  next();
});

For example:

Every Request
      |
      v
express.json()
      |
      v
Request Logger
      |
      v
Router

Express provides built-in middleware such as express.json(), express.urlencoded(), and express.static(). ([Express.js][1])

4.6 Router-Level Middleware

Large applications should not put every middleware function directly into app.js.

Use routers:

src/
├── app.js
├── middleware/
│   ├── auth.js
│   ├── validate.js
│   └── error.js
├── routes/
│   ├── users.js
│   └── orders.js
├── controllers/
│   ├── users.js
│   └── orders.js
└── services/
    ├── users.js
    └── orders.js

Example:

import express from "express";

const router = express.Router();

function authenticate(req, res, next) {
  const token = req.headers.authorization;

  if (!token) {
    return res.status(401).json({
      error: "Unauthorized"
    });
  }

  next();
}

router.use(authenticate);

router.get("/", (req, res) => {
  res.json({
    message: "Authenticated users"
  });
});

export default router;

Mount it:

app.use("/api/users", userRouter);

Now:

GET /api/users

passes through the router's authentication middleware before reaching the handler. Express supports both application-level and router-level middleware. ([Express.js][1])

4.7 Authentication Middleware

Authentication determines:

Who is making this request?

A simplified JWT middleware:

import jwt from "jsonwebtoken";

export function authenticate(req, res, next) {
  const header = req.headers.authorization;

  if (!header?.startsWith("Bearer ")) {
    return res.status(401).json({
      error: "Missing authentication token"
    });
  }

  const token = header.substring(7);

  try {
    const payload = jwt.verify(
      token,
      process.env.JWT_SECRET
    );

    req.user = payload;

    next();
  } catch {
    return res.status(401).json({
      error: "Invalid or expired token"
    });
  }
}

The pipeline becomes:

Request
   |
   v
Authorization Header
   |
   v
Extract Token
   |
   v
Verify Signature
   |
   +---- Invalid → 401
   |
   v
req.user = payload
   |
   v
next()
   |
   v
Controller

The middleware creates a controlled boundary between unauthenticated HTTP traffic and authenticated application logic.

4.8 Authorization Middleware

Authentication answers who you are.

Authorization answers what you are allowed to do.

export function requireRole(...allowedRoles) {
  return (req, res, next) => {
    if (!req.user) {
      return res.status(401).json({
        error: "Authentication required"
      });
    }

    if (!allowedRoles.includes(req.user.role)) {
      return res.status(403).json({
        error: "Forbidden"
      });
    }

    next();
  };
}

Use it:

router.delete(
  "/users/:id",
  authenticate,
  requireRole("admin"),
  deleteUser
);

The pipeline becomes:

Request
   ↓
authenticate
   ↓
requireRole("admin")
   ↓
deleteUser

4.9 Validation Middleware

Validation should happen before business logic.

function validateCreateUser(req, res, next) {
  const { name, email, password } = req.body;

  if (
    typeof name !== "string" ||
    typeof email !== "string" ||
    typeof password !== "string"
  ) {
    return res.status(400).json({
      error: "Invalid request body"
    });
  }

  if (password.length < 8) {
    return res.status(400).json({
      error: "Password must contain at least 8 characters"
    });
  }

  next();
}

Then:

router.post(
  "/",
  authenticate,
  validateCreateUser,
  createUser
);

This prevents the controller from becoming responsible for every HTTP-level validation rule.

4.10 Error-Handling Middleware

Errors should have a centralized path instead of every controller independently formatting errors.

Express identifies error-handling middleware through its four-argument signature:

(err, req, res, next)

([Express.js][1])

Example:

export function errorHandler(err, req, res, next) {
  console.error(err);

  const statusCode = err.statusCode || 500;

  res.status(statusCode).json({
    error: err.message || "Internal server error"
  });
}

app.use("/api/users", userRouter);
app.use("/api/orders", orderRouter);

app.use(errorHandler);

A typical structure is:

Request
   ↓
Middleware
   ↓
Router
   ↓
Controller
   ↓
Service
   ↓
Error
   ↓
next(error)
   ↓
Error Handler
   ↓
HTTP Response

4.11 Async Middleware

Modern backend applications perform many asynchronous operations:

Database queries
HTTP requests
File operations
Cache operations
Message queues
External APIs

Example:

async function loadUser(req, res, next) {
  try {
    const user = await userService.findById(req.params.id);

    if (!user) {
      return res.status(404).json({
        error: "User not found"
      });
    }

    req.userRecord = user;

    next();
  } catch (error) {
    next(error);
  }
}

The important pattern is:

await operation
      |
      +---- success → next()
      |
      +---- failure → next(error)

In current Express behavior, rejected promises from middleware are propagated to next() automatically in Express 5, while explicitly calling next(error) remains a clear pattern for asynchronous control flow. ([Express.js][2])

4.12 Complete Backend Pipeline

A clean production-oriented structure can look like:

import express from "express";
import crypto from "node:crypto";

const app = express();

app.use(express.json());

app.use((req, res, next) => {
  req.requestId = crypto.randomUUID();

  console.log({
    requestId: req.requestId,
    method: req.method,
    path: req.originalUrl
  });

  next();
});

function authenticate(req, res, next) {
  const token = req.headers.authorization;

  if (!token) {
    return res.status(401).json({
      error: "Authentication required"
    });
  }

  req.user = {
    id: "user-123",
    role: "admin"
  };

  next();
}

function requireRole(role) {
  return (req, res, next) => {
    if (req.user?.role !== role) {
      return res.status(403).json({
        error: "Forbidden"
      });
    }

    next();
  };
}

function validateUser(req, res, next) {
  const { name, email } = req.body;

  if (!name || !email) {
    return res.status(400).json({
      error: "name and email are required"
    });
  }

  next();
}

app.post(
  "/api/users",
  authenticate,
  requireRole("admin"),
  validateUser,
  async (req, res, next) => {
    try {
      const user = {
        id: crypto.randomUUID(),
        name: req.body.name,
        email: req.body.email
      };

      res.status(201).json(user);
    } catch (error) {
      next(error);
    }
  }
);

app.use((err, req, res, next) => {
  console.error({
    requestId: req.requestId,
    error: err.message
  });

  res.status(500).json({
    error: "Internal server error",
    requestId: req.requestId
  });
});

app.listen(3000, () => {
  console.log("Server running on port 3000");
});

The complete execution path is:

POST /api/users
       |
       v
express.json()
       |
       v
Request ID Middleware
       |
       v
Authentication
       |
       v
Authorization
       |
       v
Validation
       |
       v
Route Handler
       |
       v
Business Logic
       |
       v
Response

If something fails:

Any Middleware / Controller
          |
          v
     next(error)
          |
          v
   Error Middleware
          |
          v
    HTTP Error Response

4.13 Middleware Composition

Middleware becomes powerful when small functions are composed.

Instead of one enormous function:

function everything(req, res, next) {
  // authentication
  // authorization
  // validation
  // logging
  // caching
  // rate limiting
  // business logic
  // error handling
}

Prefer:

router.post(
  "/orders",
  requestLogger,
  rateLimiter,
  authenticate,
  requireRole("customer"),
  validateOrder,
  createOrder
);

This creates a readable pipeline of responsibilities.

Each middleware should ideally have one clear responsibility.

4.14 Route-Specific vs Global Middleware

Global middleware:

app.use(requestLogger);

is appropriate when almost every request needs the behavior.

Route-specific middleware:

app.post(
  "/payments",
  authenticate,
  requireRole("admin"),
  processPayment
);

is appropriate when the behavior only applies to particular endpoints.

A practical structure is:

Global
├── JSON parsing
├── Request ID
├── CORS
├── Security headers
└── General logging

Router
├── Authentication
├── Authorization
├── Validation
└── Resource-specific rules

Controller
└── Business operation

4.15 The Most Important Middleware Rules

Middleware order is execution order.

next() transfers control forward.

next(error) transfers control to error-handling middleware.

Sending a response should normally terminate the current path.

Authentication should occur before protected business logic.

Authorization should occur after authentication.

Validation should happen before expensive business operations.

Error middleware should be centralized.

Middleware should remain focused and composable.

Business logic should not be buried inside generic middleware.

A well-designed request-response pipeline makes the backend easier to secure, test, debug, observe, and maintain. Express's middleware model is explicitly built around this ordered chain of functions that can modify requests and responses, terminate processing, or delegate to subsequent middleware. ([Express.js][1])

Backend Engineering Ebook

Grab the Backend Engineering ebook:
https://codewithdhanian.gumroad.com/l/ungqng

" width="800" height="1200">

Authentication & Authorization (JWT, OAuth2, Sessions)

CodeWithDhanian — Sat, 15 Aug 2026 05:38:54 +0000

Authentication and authorization are the security foundation of a backend application.

Authentication (AuthN) answers:

Who are you?

Authorization (AuthZ) answers:

What are you allowed to do?

A production backend usually combines password authentication, sessions or tokens, OAuth 2.0 / OpenID Connect, role-based authorization, secure cookies, MFA, and audit logging.

A secure design should also treat authentication, session management, and authorization as separate responsibilities.

1. Authentication vs Authorization

Consider an API endpoint:

DELETE /api/users/42

The backend must answer two different questions:

Authentication
    ↓
Is this request associated with a valid authenticated user?
    ↓
Authorization
    ↓
Does that authenticated user have permission to delete user 42?

A valid login does not automatically grant permission to perform every operation.

For example:

User: alice@example.com
Authenticated: YES
Role: USER

GET /api/profile
→ ALLOWED

DELETE /api/users/42
→ DENIED

An administrator might have:

User: admin@example.com
Authenticated: YES
Role: ADMIN

GET /api/profile
→ ALLOWED

DELETE /api/users/42
→ ALLOWED

This separation prevents a common security mistake: treating identity as permission.

2. The Complete Authentication Architecture

A typical backend authentication system looks like this:

                    ┌──────────────────────┐
                    │      Browser / App   │
                    └──────────┬───────────┘
                               │
                         HTTPS Request
                               │
                               ▼
                    ┌──────────────────────┐
                    │    API Gateway /     │
                    │    Load Balancer     │
                    └──────────┬───────────┘
                               │
                               ▼
                    ┌──────────────────────┐
                    │ Authentication Layer │
                    │                      │
                    │ Session / JWT / OAuth│
                    └──────────┬───────────┘
                               │
                    Authenticated Identity
                               │
                               ▼
                    ┌──────────────────────┐
                    │ Authorization Layer  │
                    │                      │
                    │ RBAC / ABAC / Scopes │
                    └──────────┬───────────┘
                               │
                         Authorized Request
                               │
                               ▼
                    ┌──────────────────────┐
                    │ Application Services │
                    └──────────┬───────────┘
                               │
                               ▼
                    ┌──────────────────────┐
                    │ PostgreSQL / Redis   │
                    └──────────────────────┘

The client should never be trusted to determine whether a request is authorized. The backend must enforce authorization on protected operations.

3. Password Authentication

A traditional authentication flow is:

Registration
    ↓
User submits email + password
    ↓
Validate input
    ↓
Hash password
    ↓
Store password hash
    ↓
Login
    ↓
Find user
    ↓
Verify password against hash
    ↓
Create session/token
    ↓
Return authenticated response

Never store:

password = "MyPassword123"

Store a strong password hash instead.

Modern password hashing should use a dedicated password-hashing algorithm such as Argon2id, bcrypt, or another appropriately configured password hashing mechanism. OWASP recommends secure password storage rather than reversible encryption or plaintext storage.

Node.js Example

npm install express argon2 jsonwebtoken cookie-parser

import express from "express";
import argon2 from "argon2";
import jwt from "jsonwebtoken";
import cookieParser from "cookie-parser";

const app = express();

app.use(express.json());
app.use(cookieParser());

const users = new Map();

app.post("/register", async (req, res) => {
  const { email, password } = req.body;

  if (!email || !password) {
    return res.status(400).json({
      error: "Email and password are required"
    });
  }

  if (users.has(email)) {
    return res.status(409).json({
      error: "User already exists"
    });
  }

  const passwordHash = await argon2.hash(password);

  users.set(email, {
    email,
    passwordHash,
    role: "USER"
  });

  return res.status(201).json({
    message: "User created"
  });
});

app.listen(3000, () => {
  console.log("Server running on port 3000");
});

The important distinction is:

Plain password
     ↓
Argon2id
     ↓
Password hash
     ↓
Database

During login, the server does not decrypt a password. It verifies whether the submitted password matches the stored hash.

4. JWT Authentication

JSON Web Token (JWT) is a compact token format commonly used to represent claims between systems.

A JWT normally contains:

HEADER.PAYLOAD.SIGNATURE

Example structure:

eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9
.
eyJzdWIiOiIxMjMiLCJyb2xlIjoiVVNFUiJ9
.
signature

The payload can contain claims such as:

{
  "sub": "123",
  "role": "USER",
  "iss": "api.example.com",
  "aud": "web-app",
  "exp": 1786780000
}

Important JWT claims include:

sub — subject or user identifier
iss — token issuer
aud — intended audience
exp — expiration time
iat — issued-at time
scope — permitted OAuth scopes

A JWT is signed, not automatically encrypted. Therefore, sensitive secrets should not be placed inside its payload.

JWT Authentication Flow

Client
  │
  │ POST /login
  │ email + password
  ▼
Backend
  │
  ├── Find user
  ├── Verify password
  └── Issue access token
  │
  ▼
Client
  │
  │ Authorization: Bearer <token>
  ▼
Authentication Middleware
  │
  ├── Verify signature
  ├── Validate expiration
  ├── Validate issuer
  └── Validate audience
  │
  ▼
Authorization Middleware
  │
  ├── Check role
  └── Check permissions
  │
  ▼
Protected Controller

Complete JWT Middleware

import jwt from "jsonwebtoken";

const JWT_SECRET = process.env.JWT_SECRET;

export function authenticate(req, res, next) {
  const authorization = req.headers.authorization;

  if (!authorization) {
    return res.status(401).json({
      error: "Authentication required"
    });
  }

  const [scheme, token] = authorization.split(" ");

  if (scheme !== "Bearer" || !token) {
    return res.status(401).json({
      error: "Invalid authorization header"
    });
  }

  try {
    const payload = jwt.verify(token, JWT_SECRET, {
      issuer: "api.example.com",
      audience: "web-app"
    });

    req.user = {
      id: payload.sub,
      role: payload.role
    };

    next();
  } catch {
    return res.status(401).json({
      error: "Invalid or expired token"
    });
  }
}

The middleware converts:

HTTP Authorization Header
            ↓
Token Verification
            ↓
Validated Identity
            ↓
req.user

A Bearer token must be protected carefully because possession of the token is generally sufficient to use it.

5. JWT Access Tokens and Refresh Tokens

A common architecture separates short-lived access tokens from long-lived refresh tokens.

Login
  │
  ├──────────────► Access Token
  │                 Short lifetime
  │                 Used for APIs
  │
  └──────────────► Refresh Token
                    Longer lifetime
                    Used to obtain new access tokens

Example:

Access Token
    ↓
15 minutes

Refresh Token
    ↓
Days / weeks depending on security requirements

When the access token expires:

Client
  │
  │ refresh token
  ▼
Backend
  │
  ├── Validate refresh token
  ├── Check revocation/session state
  └── Rotate refresh token
  │
  ▼
New access token

For browser applications, authentication credentials should generally not be stored in localStorage or sessionStorage, because JavaScript running in the origin can access them. OWASP recommends appropriately protected cookies or architectures such as a Backend-for-Frontend (BFF).

6. Secure Cookie Configuration

A session or refresh token stored in a cookie should use appropriate security attributes:

res.cookie("refresh_token", refreshToken, {
  httpOnly: true,
  secure: true,
  sameSite: "lax",
  path: "/auth",
  maxAge: 7 * 24 * 60 * 60 * 1000
});

Important attributes:

HttpOnly
    ↓
JavaScript cannot directly read the cookie

Secure
    ↓
Cookie is sent only over HTTPS

SameSite
    ↓
Controls cross-site cookie transmission

Path
    ↓
Limits where the cookie is sent

Max-Age
    ↓
Controls cookie lifetime

Cookie-based authentication must also consider CSRF protection, particularly when cookies are automatically attached to cross-site requests.

7. Server-Side Sessions

A session-based authentication system stores authentication state on the server.

The browser receives only an opaque session identifier:

Browser
   │
   │ session_id=abc123
   ▼
Backend
   │
   ▼
Session Store
   │
   └── abc123
        ├── userId: 42
        ├── role: USER
        └── expiresAt: ...

The session identifier should be random, unpredictable, and contain no sensitive user information. OWASP recommends strong server-generated session identifiers and careful lifecycle management.

Redis Session Example

import crypto from "crypto";

function createSession(userId) {
  const sessionId = crypto.randomBytes(32).toString("hex");

  const session = {
    userId,
    createdAt: Date.now(),
    expiresAt: Date.now() + 1000 * 60 * 60 * 24
  };

  return {
    sessionId,
    session
  };
}

The architecture becomes:

Client
  │
  │ Cookie: session_id
  ▼
API
  │
  ▼
Redis
  │
  └── session_id → user identity

This makes logout, session revocation, and server-side session invalidation straightforward.

8. OAuth 2.0

OAuth 2.0 is primarily an authorization framework, not a password-login protocol.

It allows a client application to obtain limited access to protected resources without receiving the resource owner's password.

The important roles are:

Resource Owner
      │
      ▼
Authorization Server
      │
      ▼
Client Application
      │
      ▼
Resource Server

For example:

User
 │
 │ "Allow application to access profile"
 ▼
Google Authorization Server
 │
 │ authorization code
 ▼
Application
 │
 │ exchange code
 ▼
Access Token
 │
 ▼
Protected API

For modern applications, the Authorization Code flow with PKCE is the standard pattern to understand, especially for public clients where a client secret cannot safely be kept confidential.

9. OAuth 2.0 Authorization Code + PKCE

The architecture:

┌────────────┐
│   Client   │
└─────┬──────┘
      │
      │ 1. Generate code_verifier
      │
      │ 2. Create code_challenge
      ▼
┌─────────────────────┐
│ Authorization       │
│ Server              │
└──────────┬──────────┘
           │
           │ 3. User authenticates
           │
           │ 4. User grants access
           ▼
      Authorization Code
           │
           ▼
┌──────────┴──────────┐
│ Client              │
│                     │
│ code + verifier     │
└──────────┬──────────┘
           │
           │ 5. Token request
           ▼
┌─────────────────────┐
│ Authorization       │
│ Server              │
└──────────┬──────────┘
           │
           │ 6. Access Token
           ▼
┌─────────────────────┐
│ Resource Server     │
└─────────────────────┘

PKCE binds the authorization-code exchange to the client instance that initiated the flow.

Conceptually:

code_verifier
      │
      ▼
SHA-256
      │
      ▼
code_challenge

The client sends the code_challenge during authorization and later sends the original code_verifier during token exchange.

The authorization server verifies that they match.

10. OAuth 2.0 Scopes

OAuth permissions should be expressed using narrow scopes.

Instead of:

access = everything

use:

profile:read
orders:read
orders:write

Example:

Authorization: Bearer ACCESS_TOKEN

The backend validates:

Token valid?
      ↓
Scope present?
      ↓
orders:write
      ↓
Allow operation

A token with:

scope = "orders:read"

should not be accepted for:

DELETE /orders/123

because deletion requires a stronger permission such as:

orders:delete

11. OpenID Connect

OAuth 2.0 provides authorization.

OpenID Connect (OIDC) adds an identity layer on top of OAuth 2.0.

The conceptual difference is:

OAuth 2.0
    ↓
"What can this application access?"

OpenID Connect
    ↓
"Who authenticated this user?"

OIDC commonly introduces an ID Token, which contains identity-related claims intended for the client.

A backend integrating with an identity provider should validate tokens according to the provider's documented issuer, audience, signature, expiration, and other required claims.

12. Authorization with RBAC

Role-Based Access Control (RBAC) assigns permissions through roles.

Example:

USER
 ├── profile:read
 └── order:create

EDITOR
 ├── profile:read
 ├── article:create
 └── article:update

ADMIN
 ├── user:read
 ├── user:update
 ├── user:delete
 └── system:manage

Authorization Middleware

function authorize(...allowedRoles) {
  return (req, res, next) => {
    if (!req.user) {
      return res.status(401).json({
        error: "Authentication required"
      });
    }

    if (!allowedRoles.includes(req.user.role)) {
      return res.status(403).json({
        error: "Forbidden"
      });
    }

    next();
  };
}

Usage:

app.delete(
  "/api/users/:id",
  authenticate,
  authorize("ADMIN"),
  deleteUser
);

The pipeline becomes:

Request
   ↓
authenticate()
   ↓
Identity established
   ↓
authorize("ADMIN")
   ↓
Permission verified
   ↓
deleteUser()

401 Unauthorized generally means the request lacks valid authentication.

403 Forbidden means the server understands the identity but refuses the requested operation.

13. Resource-Level Authorization

Checking only the user's role is not always sufficient.

Suppose:

GET /api/orders/500

The user may have:

role = USER

but the backend must additionally verify:

Does order 500 belong to this user?

Correct:

const order = await db.order.findUnique({
  where: {
    id: req.params.id
  }
});

if (!order) {
  return res.status(404).json({
    error: "Order not found"
  });
}

if (order.userId !== req.user.id && req.user.role !== "ADMIN") {
  return res.status(403).json({
    error: "Forbidden"
  });
}

This protects against broken object-level authorization, where an authenticated user attempts to access another user's resource by changing an identifier.

14. RBAC vs ABAC

RBAC evaluates roles:

role = ADMIN

Attribute-Based Access Control (ABAC) evaluates multiple attributes:

user.department
resource.owner
resource.classification
request.time
request.location
operation

Example policy:

ALLOW

IF
    user.role = MANAGER
AND
    user.department = resource.department
AND
    operation = "READ"

This becomes valuable in large systems where simple roles are insufficient.

15. Authentication Middleware Structure

A production backend can separate security responsibilities into layers:

HTTP Request
     │
     ▼
Rate Limiting
     │
     ▼
Authentication
     │
     ▼
Identity Context
     │
     ▼
Authorization
     │
     ▼
Validation
     │
     ▼
Business Logic
     │
     ▼
Database

A clean project structure might look like:

src/
├── auth/
│   ├── auth.controller.js
│   ├── auth.service.js
│   ├── auth.middleware.js
│   ├── token.service.js
│   ├── password.service.js
│   └── authorization.js
│
├── users/
│   ├── user.controller.js
│   ├── user.service.js
│   └── user.repository.js
│
├── middleware/
│   ├── rateLimit.js
│   ├── validation.js
│   └── errorHandler.js
│
├── routes/
│   ├── auth.routes.js
│   └── user.routes.js
│
├── database/
│   └── client.js
│
└── server.js

This prevents authentication logic from becoming mixed into every controller.

16. Complete Protected API Example

import express from "express";
import jwt from "jsonwebtoken";

const app = express();

app.use(express.json());

const JWT_SECRET = process.env.JWT_SECRET;

function authenticate(req, res, next) {
  const header = req.headers.authorization;

  if (!header) {
    return res.status(401).json({
      error: "Missing Authorization header"
    });
  }

  const [scheme, token] = header.split(" ");

  if (scheme !== "Bearer" || !token) {
    return res.status(401).json({
      error: "Invalid Authorization header"
    });
  }

  try {
    const payload = jwt.verify(token, JWT_SECRET, {
      issuer: "api.example.com",
      audience: "web-app"
    });

    req.user = {
      id: payload.sub,
      role: payload.role
    };

    next();
  } catch {
    return res.status(401).json({
      error: "Invalid or expired access token"
    });
  }
}

function authorize(...roles) {
  return (req, res, next) => {
    if (!roles.includes(req.user.role)) {
      return res.status(403).json({
        error: "Insufficient permissions"
      });
    }

    next();
  };
}

app.get("/api/profile", authenticate, (req, res) => {
  res.json({
    userId: req.user.id,
    role: req.user.role
  });
});

app.delete(
  "/api/users/:id",
  authenticate,
  authorize("ADMIN"),
  async (req, res) => {
    // Delete user from database here.

    res.json({
      message: "User deleted"
    });
  }
);

app.listen(3000, () => {
  console.log("API running on port 3000");
});

The important security boundary is:

authenticate()
      ↓
WHO IS THE USER?
      ↓
authorize()
      ↓
WHAT MAY THE USER DO?
      ↓
controller
      ↓
business operation

17. Multi-Factor Authentication

Multi-Factor Authentication (MFA) combines independent authentication factors.

Common factors include:

Something you know
    → Password

Something you have
    → Security key / authenticator device

Something you are
    → Biometric characteristic

A stronger login might therefore become:

Email + Password
       ↓
Password Verification
       ↓
MFA Challenge
       ↓
MFA Verification
       ↓
Authenticated Session

MFA is especially important for administrative accounts, financial operations, account recovery, and other high-impact actions. OWASP recommends stronger authentication and re-authentication for sensitive operations and risk events.

18. Session Lifecycle

Authentication is not complete when a user logs in.

A secure session has a lifecycle:

CREATE
  ↓
AUTHENTICATED
  ↓
ACTIVE
  ↓
RENEWED
  ↓
EXPIRED / REVOKED
  ↓
DESTROYED

Important events include:

login
logout
session_created
session_renewed
session_expired
session_revoked
password_changed
MFA_enabled
MFA_failed
authorization_denied

Session identifiers should be regenerated when appropriate, particularly across authentication state transitions, to reduce session fixation risks.

19. Logout and Token Revocation

With server-side sessions:

POST /logout
     ↓
Destroy session
     ↓
Clear cookie

With refresh-token systems:

POST /logout
     ↓
Revoke refresh token
     ↓
Clear authentication cookie

A simple cookie cleanup:

res.clearCookie("refresh_token", {
  httpOnly: true,
  secure: true,
  sameSite: "lax",
  path: "/auth"
});

res.status(204).send();

For high-security systems, maintain server-side refresh-token/session state so stolen credentials can be revoked before their natural expiration.

20. Authentication Security Controls

A production authentication system should include:

HTTPS / TLS
    +
Secure password hashing
    +
MFA
    +
Rate limiting
    +
Credential stuffing protection
    +
Account recovery protection
    +
Secure session management
    +
Short-lived access tokens
    +
Refresh-token rotation
    +
Token/session revocation
    +
Authorization checks
    +
Audit logging
    +
Re-authentication for sensitive operations

Authentication endpoints should also avoid revealing whether an account exists.

Instead of:

{
  "error": "Email does not exist"
}

prefer a generic response such as:

{
  "error": "Invalid email or password"
}

This reduces user enumeration.

Authentication systems should also log security-relevant events while avoiding passwords, access tokens, refresh tokens, and other secrets in logs. OWASP specifically recommends monitoring the lifecycle of sessions and authentication-related events.

21. Authentication Decision Structure

                         REQUEST
                            │
                            ▼
                    Is HTTPS enabled?
                       │          │
                      NO         YES
                       │          │
                    REJECT        ▼
                              Authenticate
                                   │
                          ┌────────┴────────┐
                          │                 │
                       INVALID            VALID
                          │                 │
                       401                  ▼
                                      Authorize
                                          │
                                 ┌────────┴────────┐
                                 │                 │
                              DENIED             ALLOWED
                                 │                 │
                                403                 ▼
                                                  API
                                                  │
                                                  ▼
                                               Database

The core security model is therefore:

IDENTITY
   ↓
AUTHENTICATION
   ↓
SESSION / TOKEN
   ↓
AUTHORIZATION
   ↓
PERMISSION
   ↓
RESOURCE

Grab the Backend Engineering ebook

CRUD Operations with a Relational Database (SQL)

CodeWithDhanian — Thu, 13 Aug 2026 13:21:04 +0000

CRUD stands for Create, Read, Update, and Delete—the four fundamental operations performed on data in almost every backend application. Whether building an e-commerce platform, banking system, hospital management system, social media application, or inventory management system, every backend revolves around storing, retrieving, modifying, and removing structured data efficiently.

A Relational Database Management System (RDBMS) stores information inside tables, where data is organized into rows and columns. Relationships between tables are established using Primary Keys and Foreign Keys, allowing complex applications to maintain data consistency, integrity, and normalization.

Core CRUD Operations

1. CREATE — Inserting New Records

The Create operation inserts new information into a database.

Example Users table:

id	name	email
1	Alice	alice@example.com

Adding another user:

INSERT INTO users (name, email)
VALUES ('John Doe', 'john@example.com');

Explanation

INSERT INTO specifies the target table.
(name, email) lists the columns receiving values.
VALUES contains the actual data.
The id is usually generated automatically using AUTO_INCREMENT, SERIAL, or IDENTITY depending on the database.

2. READ — Retrieving Data

The Read operation fetches stored information.

Retrieve every user:

SELECT *
FROM users;

Retrieve only specific columns:

SELECT id, name, email
FROM users;

Retrieve a single user:

SELECT *
FROM users
WHERE id = 1;

Retrieve users sorted alphabetically:

SELECT *
FROM users
ORDER BY name ASC;

Limit returned rows:

SELECT *
FROM users
LIMIT 10;

Important SQL keywords

SELECT chooses columns.
FROM specifies the table.
WHERE filters records.
ORDER BY sorts results.
LIMIT restricts the number of returned rows.

3. UPDATE — Modifying Existing Data

The Update operation changes existing records.

UPDATE users
SET email = 'newemail@example.com'
WHERE id = 1;

Explanation

UPDATE specifies the table.
SET assigns new values.
WHERE identifies which row to modify.

Without a WHERE clause:

UPDATE users
SET email = 'changed@example.com';

Every row in the table would be updated, making WHERE one of the most important clauses in SQL.

4. DELETE — Removing Records

Delete a specific record:

DELETE FROM users
WHERE id = 1;

Delete every record:

DELETE FROM users;

Completely remove the table:

DROP TABLE users;

These commands are very different:

DELETE removes rows.
DROP TABLE removes the entire table structure and its data.

Complete Database Structure

Database
│
├── users
│   ├── id (Primary Key)
│   ├── name
│   ├── email
│   └── created_at
│
├── products
│   ├── id (Primary Key)
│   ├── name
│   ├── price
│   └── stock
│
└── orders
    ├── id (Primary Key)
    ├── user_id (Foreign Key)
    ├── product_id (Foreign Key)
    ├── quantity
    └── order_date

Here:

users.id uniquely identifies every user.
products.id uniquely identifies every product.
orders.user_id references users.id.
orders.product_id references products.id.

This relationship prevents duplicate information and keeps data consistent across multiple tables.

Creating Tables

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Column breakdown

INT stores whole numbers.
PRIMARY KEY uniquely identifies each row.
AUTO_INCREMENT automatically generates IDs.
VARCHAR(100) stores variable-length text.
NOT NULL requires a value.
UNIQUE prevents duplicate emails.
TIMESTAMP records creation time automatically.

Typical Backend CRUD Flow

Client
   │
   ▼
HTTP Request
   │
   ▼
Backend API
   │
   ▼
Validation
   │
   ▼
Business Logic
   │
   ▼
SQL Query
   │
   ▼
Relational Database
   │
   ▼
Query Result
   │
   ▼
Backend Response
   │
   ▼
Client

Each request follows this sequence:

Client sends an HTTP request.
Backend API receives it.
Input is validated.
Business logic determines what should happen.
SQL performs the required CRUD operation.
Database executes the query.
Results are returned.
Backend formats the response as JSON.
Client displays the updated information.

Example CRUD API (Node.js + Express)

import express from "express";

const app = express();

app.use(express.json());

const users = [];

// CREATE
app.post("/users", (req, res) => {
  users.push(req.body);
  res.status(201).json(req.body);
});

// READ
app.get("/users", (req, res) => {
  res.json(users);
});

// UPDATE
app.put("/users/:id", (req, res) => {
  users[req.params.id] = req.body;
  res.json(users[req.params.id]);
});

// DELETE
app.delete("/users/:id", (req, res) => {
  users.splice(req.params.id, 1);
  res.status(204).send();
});

app.listen(3000);

This example demonstrates the four CRUD endpoints:

POST /users creates a new resource.
GET /users retrieves resources.
PUT /users/:id updates an existing resource.
DELETE /users/:id removes a resource.

In production, the users array would be replaced by SQL queries against a relational database.

Best Practices

Always validate incoming data before executing SQL.
Use Primary Keys to uniquely identify records.
Enforce Foreign Keys to maintain relationships.
Use transactions when multiple related operations must either all succeed or all fail.
Avoid SELECT * when only specific columns are required.
Always include WHERE when updating or deleting individual records.
Parameterize queries to prevent SQL Injection attacks.
Create indexes on frequently searched columns to improve query performance.
Normalize tables to eliminate redundant data while preserving consistency.

Backend Engineering eBook

Build a stronger foundation in backend development with the complete Backend Engineering eBook:

https://codewithdhanian.gumroad.com/l/ungqng

Learn HTTP & RESTful API Design

CodeWithDhanian — Thu, 13 Aug 2026 13:05:28 +0000

HTTP (Hypertext Transfer Protocol) is the application-layer communication protocol that allows clients and servers to exchange information across a network. Every modern web application, mobile application, backend service, microservice, and cloud platform relies on HTTP to send requests and receive responses.

A RESTful API (Representational State Transfer Application Programming Interface) is a collection of HTTP endpoints that expose resources using predictable URLs, standard HTTP methods, meaningful status codes, and structured JSON responses.

Unlike direct database access, clients never communicate with databases. Every interaction passes through a backend API where validation, authentication, authorization, business logic, and security are enforced.

Client–Server Architecture

Every HTTP request follows a predictable path.

+-------------------+
| Client            |
| Browser / Mobile  |
+---------+---------+
          |
          | HTTP Request
          v
+---------+---------+
| Web Server        |
| Nginx / Apache    |
+---------+---------+
          |
          |
          v
+---------+---------+
| Backend API       |
| Express / Django  |
| Spring Boot       |
+---------+---------+
          |
          |
          v
+---------+---------+
| Business Logic    |
+---------+---------+
          |
          |
          v
+---------+---------+
| Database          |
| PostgreSQL/MySQL  |
+-------------------+

The client only knows how to call the API. The backend decides how data is processed, where it is stored, and what response should be returned.

HTTP Request Structure

Every HTTP request contains several important parts.

GET /users/15 HTTP/1.1
Host: api.example.com
Authorization: Bearer eyJhb...
Content-Type: application/json
Accept: application/json

Request Line

Defines:

HTTP Method
Resource Path
HTTP Version

Example

GET /users/15 HTTP/1.1

Headers

Headers provide additional information.

Authorization: Bearer TOKEN
Content-Type: application/json
Accept: application/json
User-Agent: Chrome

Examples:

Authorization → identifies the authenticated user.
Content-Type → tells the server what format the body uses.
Accept → tells the server which response formats are acceptable.

Body

Only requests like POST, PUT, and PATCH usually include a body.

{
    "name": "Alice",
    "email": "alice@example.com"
}

The backend parses this JSON before validating it.

HTTP Response Structure

Example response:

HTTP/1.1 200 OK

Content-Type: application/json

{
    "id": 15,
    "name": "Alice",
    "email": "alice@example.com"
}

A response contains:

Status Line
Headers
Body

HTTP Methods

REST maps different operations to different HTTP methods.

Method	Purpose
GET	Retrieve data
POST	Create a resource
PUT	Replace an entire resource
PATCH	Update specific fields
DELETE	Remove a resource

Example resource:

/users

Operations become

GET    /users
GET    /users/15

POST   /users

PUT    /users/15

PATCH  /users/15

DELETE /users/15

Each endpoint performs exactly one responsibility.

REST Resource Design

REST is resource-oriented, not action-oriented.

Good

/users
/products
/orders
/invoices

Poor

/createUser
/getUsers/deleteUser
/updateUser

Resources should be represented as nouns, while the HTTP method describes the action.

URL Hierarchy

Well-designed URLs represent relationships.

/users

/users/15

/users/15/orders

/users/15/orders/8

The URL itself explains the resource hierarchy.

Building a REST API with Express

const express = require("express");

const app = express();

app.use(express.json());

let users = [
    {
        id: 1,
        name: "Alice",
        email: "alice@example.com"
    }
];

The server:

enables JSON parsing
stores sample data
exposes REST endpoints

GET

app.get("/users", (req, res) => {
    res.json(users);
});

Returns every user.

GET by ID

app.get("/users/:id", (req, res) => {

    const user = users.find(
        u => u.id === Number(req.params.id)
    );

    if (!user) {
        return res.status(404).json({
            message: "User not found"
        });
    }

    res.json(user);
});

The route parameter

:id

captures the requested resource identifier.

POST

app.post("/users", (req, res) => {

    const user = {
        id: users.length + 1,
        name: req.body.name,
        email: req.body.email
    };

    users.push(user);

    res.status(201).json(user);

});

This endpoint:

receives JSON
creates a new resource
returns 201 Created

HTTP Status Codes

Every response should communicate what happened.

Code	Meaning
200 OK	Request succeeded
201 Created	Resource created
204 No Content	Success without response body
400 Bad Request	Invalid client input
401 Unauthorized	Authentication required
403 Forbidden	Permission denied
404 Not Found	Resource does not exist
409 Conflict	Duplicate or conflicting data
422 Unprocessable Entity	Validation failed
500 Internal Server Error	Unexpected server failure

Choosing the correct status code makes APIs easier to consume and debug.

JSON Response Standards

Successful responses should remain consistent.

{
    "success": true,
    "data": {
        "id": 15,
        "name": "Alice"
    }
}

Error responses should also follow a predictable structure.

{
    "success": false,
    "message": "Email already exists",
    "errors": [
        {
            "field": "email",
            "message": "Duplicate email"
        }
    ]
}

Consistent response formats simplify frontend development and automated API integrations.

REST Design Principles

A well-designed REST API follows several fundamental principles:

Stateless Communication — Every request contains all information required to process it. The server does not depend on previous requests.
Resource-Based URLs — Endpoints represent resources instead of actions.
Uniform Interface — Similar resources follow the same naming and behavior patterns.
Standard HTTP Methods — Use GET, POST, PUT, PATCH, and DELETE according to their intended purpose.
Meaningful Status Codes — Responses clearly indicate whether an operation succeeded or failed.
JSON Payloads — Structured, language-independent data format for requests and responses.
Idempotency — Repeating GET, PUT, or DELETE should produce the same result without unintended side effects.

Backend Engineering eBook

Master backend engineering from fundamentals to advanced concepts with practical projects and production-ready examples:

Backend Engineering eBook: https://codewithdhanian.gumroad.com/l/ungqng

How RAG actually works (clearly explained in under 2 mins):

CodeWithDhanian — Fri, 07 Aug 2026 08:55:49 +0000

RAG (Retrieval-Augmented Generation) is a system that retrieves relevant data and feeds it into an LLM before generating a response.

It lets models answer questions using external knowledge, not just what they were trained on.

If you’re building with these patterns, here's a great guide on scaling multi-agent RAG systems: https://codewithdhanian.gumroad.com/l/miwqgy

Here’s a simple mental model to understand it:

𝟭) 𝗗𝗮𝘁𝗮 𝗶𝘀 𝗶𝗻𝗴𝗲𝘀𝘁𝗲𝗱
↳ Documents (PDFs, docs, APIs) are collected and split into chunks
↳ Each chunk is cleaned and formatted ready for embedding

𝟮) 𝗘𝗺𝗯𝗲𝗱𝗱𝗶𝗻𝗴𝘀 𝗮𝗿𝗲 𝗰𝗿𝗲𝗮𝘁𝗲𝗱
↳ Each chunk is converted into a vector representation
↳ Similar meaning → closer vectors

𝟯) 𝗗𝗮𝘁𝗮 𝗶𝘀 𝘀𝘁𝗼𝗿𝗲𝗱
↳ Vectors are stored in a vector database
↳ Enables fast similarity search across large datasets

𝟰) 𝗥𝗲𝗹𝗲𝘃𝗮𝗻𝘁 𝗰𝗼𝗻𝘁𝗲𝘅𝘁 𝗶𝘀 𝗿𝗲𝘁𝗿𝗶𝗲𝘃𝗲𝗱
↳ The user's query is converted into an embedding (vector representation)
↳ The system compares it against stored vectors and retrieves the most relevant chunks

𝟱) 𝗧𝗵𝗲 𝗟𝗟𝗠 𝗴𝗲𝗻𝗲𝗿𝗮𝘁𝗲𝘀 𝘁𝗵𝗲 𝗮𝗻𝘀𝘄𝗲𝗿
↳ The query + retrieved context are combined into a prompt
↳ The model generates a grounded response

That's the foundation of RAG. There are several types of RAG, each designed for different use cases and levels of complexity.

If you’re curious what this actually looks like in practice (beyond diagrams), this repo is a great place to start: https://codewithdhanian.gumroad.com/l/miwqgy

It has:
↳ E2E implementations of RAG, AI applications, agents, and systems
↳ Resources covering AI agent architecture, reasoning strategies, and memory systems
↳ Hands-on workshops and guided learning

Star it to keep it bookmarked. This repo will keep growing, and you'll want it on hand as you build.

What else would you add?

——

♻️ Repost to help others learn AI engineering.
🙏 Remember to bookmark.
➕ Follow me ( @e_opore ) to improve at AI engineering.

Day 5: Inserting Data – INSERT INTO, Multiple Rows, and RETURNING

CodeWithDhanian — Sun, 24 May 2026 12:25:46 +0000

Introduction: Why Data Insertion Matters in Real-World Development

In any production database system, inserting data is the primary way information enters your application. Whether you are building a user registration flow, logging e-commerce orders, importing CSV analytics data, or syncing IoT sensor readings, the INSERT statement is the gateway between your application code and persistent storage.

Mastering insertion is not just about syntax — it directly impacts:

Application performance (single-row vs. bulk operations)
Data integrity (handling defaults, constraints, and generated values)
User experience (returning fresh IDs or computed fields instantly)
Scalability (avoiding costly round-trips between app and database)

By the end of this tutorial you will confidently insert single records, bulk-load hundreds or thousands of rows efficiently, retrieve auto-generated values in one atomic operation using the RETURNING clause (PostgreSQL), and apply production-grade best practices that senior engineers use daily.

We will use PostgreSQL for the main examples because the RETURNING clause is native and extremely powerful here. MySQL equivalents and differences are clearly noted so you can apply the same concepts regardless of your stack.

1. The INSERT INTO Statement – Core Concepts

The INSERT statement tells the database: “Here is new data — please store it according to the table’s structure.”

Basic Syntax

INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN);

You explicitly list the columns you want to populate.
The database fills any omitted columns with their DEFAULT value or NULL (if allowed).
Order of values must match the order of columns in the parentheses.

Why list columns explicitly?

It makes your code resilient to future schema changes (new columns added later) and improves readability/maintainability.

2. Inserting a Single Row – Step-by-Step

Assume we have this table (created in Day 4):

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,        -- Auto-incrementing ID (PostgreSQL)
    name TEXT NOT NULL,
    price NUMERIC(10,2) DEFAULT 0.00,
    stock INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Single-row insertion example

-- Example 1: Full column list (most explicit and safest)
INSERT INTO products (name, price, stock)
VALUES ('Wireless Bluetooth Headphones', 89.99, 150);

-- Example 2: Omit columns that have defaults or are auto-generated
INSERT INTO products (name, price)
VALUES ('USB-C Cable', 12.50);

What happens behind the scenes

Database validates constraints (NOT NULL, data types, CHECK constraints).
Generates default values where needed.
Writes the row to disk (within a transaction).
Returns success or raises an error.

MySQL note: Use the same syntax. The SERIAL type becomes AUTO_INCREMENT and the primary key.

3. Inserting Multiple Rows Efficiently

Inserting rows one by one in a loop is a performance anti-pattern. Databases are optimized for set-based operations.

Multi-row INSERT syntax

INSERT INTO products (name, price, stock)
VALUES 
    ('Smart Watch', 249.99, 75),
    ('Laptop Stand', 34.50, 200),
    ('Wireless Mouse', 29.99, 120);

Benefits

One network round-trip instead of dozens.
Single transaction → atomicity.
Database can optimize batch writing and indexing.

Production tip: For thousands of rows, use PostgreSQL’s COPY command or prepared statements with batching in your application language (e.g., pg driver in Node.js with multi-row mode).

4. Handling NULL, DEFAULT, and Constraints

-- Explicitly set NULL (only if column allows it)
INSERT INTO products (name, price, stock)
VALUES ('Test Product', NULL, NULL);

-- Let DEFAULT kick in
INSERT INTO products (name)
VALUES ('Free Sample Item');

Warning: Never rely on implicit column order for critical applications. Always name columns.

5. The RETURNING Clause – The Magic of Getting Data Back Instantly (PostgreSQL)

One of the most powerful features in PostgreSQL. After inserting, you can immediately retrieve any columns — especially auto-generated IDs, timestamps, or computed values — without a second SELECT.

Syntax

INSERT INTO products (name, price, stock)
VALUES ('4K Webcam', 129.99, 40)
RETURNING *;                    -- Return every column

Practical examples

-- Return only the new ID and creation timestamp
INSERT INTO products (name, price, stock)
VALUES ('Mechanical Keyboard', 79.99, 60)
RETURNING product_id, created_at;

-- Return multiple rows when inserting many
INSERT INTO products (name, price, stock)
VALUES 
    ('HDMI Cable', 8.99, 300),
    ('Monitor Arm', 45.00, 25)
RETURNING product_id, name, created_at;

Real-world use case: In a REST API, when a user creates a new order, you can return the full order object (including the generated order_id) in the same HTTP response. No extra query needed.

MySQL equivalent (versions 8.0.21+): MySQL now supports RETURNING in INSERT, but older versions require LAST_INSERT_ID() after the insert:

-- MySQL alternative
INSERT INTO products (name, price, stock) VALUES ('Example', 19.99, 10);
SELECT LAST_INSERT_ID() AS product_id;

6. Best Practices & Production Techniques

Always name columns — never use INSERT INTO table VALUES (...) in production code.
Use parameterized queries / prepared statements to prevent SQL injection.
Batch inserts for bulk data (100–10,000 rows).
Wrap in transactions when inserting related data across multiple tables.
Validate data in the application layer before hitting the database.
Monitor insert performance with EXPLAIN ANALYZE on large operations.
Consider ON CONFLICT (PostgreSQL) or INSERT ... ON DUPLICATE KEY UPDATE (MySQL) for upsert patterns.

Example of safe, parameterized insert (conceptual — shown in SQL for clarity)

-- In application code you would use placeholders
PREPARE insert_product AS
INSERT INTO products (name, price, stock)
VALUES ($1, $2, $3)
RETURNING product_id;

7. Common Pitfalls & Troubleshooting

Pitfall	Symptom	Fix
Forgetting column names	Data goes into wrong columns	Always list columns
Violating NOT NULL	Error: null value in column	Provide value or DEFAULT
Type mismatch	Error: invalid input syntax	Cast or format correctly
Forgetting RETURNING	Extra SELECT query needed	Add RETURNING when possible
Inserting in loop	Slow performance, high latency	Use multi-row or COPY

8. Real-World Project Example: E-Commerce Product Import

Imagine you receive a CSV file from your supplier with 5,000 new products.

Instead of looping 5,000 times:

-- PostgreSQL efficient bulk import (Day 5 level)
COPY products (name, price, stock)
FROM '/path/to/products.csv'
DELIMITER ','
CSV HEADER;

Then query the newly imported rows using RETURNING or a timestamp filter.

This pattern is used by Shopify, Amazon, and every serious e-commerce platform.

Conclusion

You now understand not just how to insert data, but why certain patterns matter in production systems. The combination of explicit column lists, multi-row inserts, and the RETURNING clause gives you both safety and speed — the hallmarks of professional database engineering.

Key Takeaways

Always specify column names in INSERT statements.
Prefer multi-row VALUES for performance.
Use RETURNING (PostgreSQL) to eliminate extra queries.
Validate early, insert atomically, and monitor with EXPLAIN.
Master these fundamentals before moving to advanced topics like triggers or stored procedures.

You have completed Day 5. Your database now has data — the foundation for every query, join, and report you will build in the coming days.

Day 4: Creating Tables – Data Types, NULL, and DEFAULT Constraints

CodeWithDhanian — Sun, 24 May 2026 02:46:06 +0000

The Foundation of Every Relational Database: Why Table Creation Matters

When you build any production application—whether it is a customer management system, an inventory tracker, or a full-scale e-commerce platform—the table becomes the core structural unit that holds your data. A table in a relational database is not just a spreadsheet; it is a precisely defined container that enforces rules about what data can live inside it, how that data is stored, and how the database engine will interact with it for years to come.

The CREATE TABLE statement is the very first place where you, as a developer or architect, make decisions that affect performance, data integrity, storage costs, query speed, and even the long-term maintainability of your entire system. Today we will explore this statement in exhaustive detail: from the underlying theory of data types to the subtle but critical behavior of NULL values and the practical power of DEFAULT constraints. We will examine real engineering trade-offs, internal database workflows, and the exact syntax you will use in both PostgreSQL and MySQL—the two databases you set up on Day 2.

The CREATE TABLE Statement: Syntax, Structure, and Execution Flow

At its core, the CREATE TABLE command instructs the database management system to allocate storage space, define column metadata, and register the table within the database’s system catalog. The engine parses your statement, validates data-type compatibility, applies any immediate constraints, and then creates the physical files (or pages) that will eventually hold rows.

The basic structure looks like this:

CREATE TABLE table_name (
    column_name1 data_type [constraints],
    column_name2 data_type [constraints],
    ...
);

Every column definition consists of three mandatory parts and one optional but extremely important part:

The column name (must be unique within the table)
The data type (determines storage format, range, and behavior)
Optional constraints such as NOT NULL or DEFAULT

When the database engine processes this statement, it performs several internal steps in sequence:

Acquires a schema lock to prevent concurrent modifications.
Validates that the chosen data types exist and are compatible with the storage engine.
Allocates space in the heap (the main table storage area).
Records the table definition in the system catalog tables (pg_class and pg_attribute in PostgreSQL, or information_schema.columns in both engines).
If the table is created successfully, it becomes immediately queryable.

Choosing the wrong data type or forgetting a constraint at this stage can force you to run expensive ALTER TABLE operations later—which lock the table and can cause downtime in high-traffic systems.

Data Types: The Language Your Database Speaks

Every value you store must be declared with an explicit data type. The database engine uses this declaration to decide how much disk space to reserve, how to encode the value in memory, how to compare values during queries, and how to index the column efficiently. Selecting the correct data type is both an art and a science; it directly impacts storage costs, query performance, and application correctness.

Numeric Data Types

Integer types are the workhorses of most applications. In PostgreSQL you have SMALLINT (2 bytes, ±32,767), INTEGER (4 bytes, ±2.1 billion), and BIGINT (8 bytes, ±9 quintillion). MySQL offers the nearly identical TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT.

For fractional numbers, use NUMERIC(precision, scale) (also called DECIMAL in MySQL). This type stores exact decimal values—critical for financial data—unlike REAL or DOUBLE PRECISION, which use floating-point approximation and can introduce rounding errors that accumulate over millions of transactions.

Real-world example: an order total column should never use DOUBLE PRECISION; always use NUMERIC(12,2) to guarantee penny-level accuracy across years of compounding calculations.

Character Data Types

Text storage comes in two families. VARCHAR(n) stores variable-length strings up to n characters and is the default choice in modern applications. TEXT (or LONGTEXT in MySQL) removes the length limit entirely and is ideal for product descriptions or user comments.

CHAR(n) is fixed-length and right-pads with spaces; it can be slightly faster for very short, consistently sized fields (e.g., country codes), but it wastes space otherwise. In high-scale systems, the performance difference between VARCHAR and TEXT is negligible because both are stored out-of-line when they grow large, but the choice still matters for index size and memory usage during sorting.

Date and Time Data Types

DATE, TIME, TIMESTAMP, and TIMESTAMPTZ (timestamp with time zone in PostgreSQL) are essential. PostgreSQL’s TIMESTAMPTZ automatically normalizes everything to UTC internally while preserving the original time zone for display—an architectural decision that prevents daylight-saving bugs that plague many legacy MySQL applications using plain DATETIME.

Always store timestamps with time zone awareness unless your entire system operates in a single fixed timezone. The internal workflow is simple: the engine converts the input to UTC on write and converts back on read based on the client’s session timezone.

Boolean and Specialized Types

The BOOLEAN type (or BOOL) stores true, false, or NULL. It occupies only one byte yet saves you from the classic “0/1 magic number” anti-pattern that leads to bugs when someone later interprets 2 as “maybe.”

PostgreSQL also offers JSONB for semi-structured data and UUID for globally unique identifiers—both of which we will use in production examples later in the series.

NULL: The Concept of “Value Unknown”

NULL is not a value; it is the explicit absence of a value. The SQL standard treats NULL through three-valued logic (true, false, unknown). This means that any comparison involving NULL—even NULL = NULL—evaluates to unknown, not true.

From an architectural standpoint, the database engine stores a special null bitmap for each row. When a column is NULL, the engine skips storing any data for that column, saving space. However, this bitmap adds a tiny overhead to every row, which becomes measurable only at billions of rows.

In real systems, NULL represents legitimate business states: “user has not yet set a middle name,” “order has not yet shipped,” or “product has no discount expiration date.” Misusing NULL for “zero” or “empty string” is one of the most common sources of incorrect analytics.

Enforcing Presence with NOT NULL Constraints

The NOT NULL constraint tells the database engine to reject any INSERT or UPDATE that would leave the column without a value. This is enforced at write time, before the row reaches disk.

Placing NOT NULL on columns that your application logic depends on (email, created_at, status) prevents entire classes of runtime errors. It also allows the query planner to generate more efficient execution plans because the engine knows the column will never be unknown.

Supplying Sensible Defaults with the DEFAULT Constraint

The DEFAULT constraint provides an automatic value when none is supplied during an INSERT. The engine evaluates the default expression at write time and substitutes it seamlessly.

Common realistic defaults include:

CURRENT_TIMESTAMP or NOW() for audit columns
A literal string such as 'active' for status columns
A numeric literal such as 0.00 for price fields
A complex expression such as gen_random_uuid() in PostgreSQL

MySQL and PostgreSQL both support DEFAULT on almost every data type, but PostgreSQL allows far richer expressions (including subqueries and functions) because its parser is more expressive.

Building a Production-Grade Table: A Complete Realistic Example

Let us create a users table that reflects real engineering decisions made every day in SaaS companies.

-- PostgreSQL version
CREATE TABLE users (
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email           VARCHAR(255) NOT NULL,
    full_name       VARCHAR(255),
    username        VARCHAR(100) UNIQUE,
    status          VARCHAR(20)  DEFAULT 'pending' NOT NULL,
    created_at      TIMESTAMPTZ  DEFAULT NOW() NOT NULL,
    last_login_at   TIMESTAMPTZ,
    preferences     JSONB        DEFAULT '{}'::JSONB,
    is_verified     BOOLEAN      DEFAULT FALSE NOT NULL
);

-- Equivalent MySQL version (note AUTO_INCREMENT and slight syntax differences)
CREATE TABLE users (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    email           VARCHAR(255) NOT NULL,
    full_name       VARCHAR(255),
    username        VARCHAR(100) UNIQUE,
    status          VARCHAR(20)  DEFAULT 'pending' NOT NULL,
    created_at      DATETIME(3)  DEFAULT CURRENT_TIMESTAMP(3) NOT NULL,
    last_login_at   DATETIME(3),
    preferences     JSON         DEFAULT (JSON_OBJECT()) NOT NULL,
    is_verified     TINYINT(1)   DEFAULT 0 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Walk through the logic:

id uses an auto-incrementing or identity column so the engine guarantees uniqueness without application-level coordination.
email is NOT NULL because every user must have one; the database engine will reject any attempt to create a row without it.
status has a DEFAULT so new users start in a safe pending state even if the application forgets to specify it.
created_at defaults to the exact moment of insertion, providing a reliable audit trail without requiring application code to remember to set it.
preferences as JSONB (PostgreSQL) or JSON (MySQL) allows flexible storage of user settings without schema changes.

When you execute this statement, the database engine creates the table, builds the internal row header with the null bitmap, reserves space for the default values, and registers the column metadata so future queries can leverage type information for optimization.

Engineering Trade-offs, Performance Implications, and Best Practices

Choosing data types and constraints is never free.

Wider numeric types (BIGINT vs INTEGER) double storage and index size but prevent overflow bugs that crash production systems.
Variable-length types (VARCHAR, TEXT) are more space-efficient but require an extra length prefix and can fragment pages over time.
NOT NULL columns allow the query planner to skip null-check branches, improving index usage and reducing CPU cycles.
Over-use of DEFAULT expressions that call functions (e.g., NOW()) adds microseconds per row; in a table receiving 10,000 inserts per second, this becomes measurable.

Best practice: declare the narrowest, most restrictive data type and the strictest constraints that still match real business rules. Run EXPLAIN ANALYZE on representative queries after creation to validate that the engine is using the types and constraints you intended. In high-scale environments, these early decisions determine whether your database stays on a single node or needs sharding later.

For teams working in both PostgreSQL and MySQL, maintain separate migration scripts or use an abstraction layer that understands the subtle syntax differences—especially around default timestamps and identity columns.

If you want to take these concepts from theory to production-grade mastery with dozens of additional exercises, real schema evolution patterns, and battle-tested migration strategies, grab the comprehensive SQL Playbook at https://codewithdhanian.gumroad.com/l/hjmix —it was built exactly for engineers who want to move beyond tutorials and ship reliable systems.

Day 3: Creating and Dropping Databases – CREATE DATABASE, DROP DATABASE

CodeWithDhanian — Sat, 23 May 2026 12:38:47 +0000

Creating a database is one of the most fundamental operations you will perform as a SQL developer or database engineer. It marks the moment you move from simply connecting to a database management system to actually owning and organizing your own structured data environment. In this tutorial, we will explore exactly how databases are created and removed, why these operations behave the way they do under the hood, and how to handle them responsibly in both development and production environments.

We will work with the two most popular open-source relational database management systems introduced earlier: PostgreSQL and MySQL. Although both support the core SQL commands CREATE DATABASE and DROP DATABASE, their implementations differ in important architectural ways that affect performance, security, and scalability.

What a Database Actually Represents

Before writing any code, it is essential to understand what a database truly is from an architectural perspective. In a relational database management system, a database is a named container that holds multiple schemas, tables, indexes, views, functions, and other database objects. It acts as a logical boundary for data isolation, access control, and resource allocation.

Physically, a database is more than just a name. In PostgreSQL, each database corresponds to a separate set of files on disk, managed by the postmaster process. These files store the actual data pages, transaction logs (WAL), and catalog metadata. In MySQL (using the default InnoDB storage engine), each database typically maps to a subdirectory under the MySQL data directory, with table definitions and data stored in .frm, .ibd, or similar files depending on the engine.

This separation is deliberate. It allows a single database server instance to host dozens or even hundreds of independent databases, each with its own security context and performance characteristics. This architecture is what enables multi-tenant applications, separate development/staging/production environments, and efficient resource isolation.

The CREATE DATABASE Statement

The primary command to create a new database is CREATE DATABASE. The basic syntax is intentionally simple, yet the optional clauses reveal deep control over how the database will behave.

-- PostgreSQL version
CREATE DATABASE my_app_production
    WITH OWNER = postgres
         ENCODING = 'UTF8'
         LC_COLLATE = 'en_US.UTF-8'
         LC_CTYPE = 'en_US.UTF-8'
         TEMPLATE = template0;

-- MySQL version
CREATE DATABASE my_app_production
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

Let us break down every important part of these statements.

WITH (PostgreSQL only): Introduces a list of optional parameters that configure the new database at creation time.
OWNER: Specifies which role (user) will own the database. The owner has full rights to create objects, grant privileges, and drop the database later. In production systems, it is a best practice to create a dedicated application role with the least privileges necessary rather than using the default superuser.
ENCODING / CHARACTER SET: Defines how text data will be stored. UTF8 (PostgreSQL) or utf8mb4 (MySQL) is almost always the correct choice today because it properly supports international characters, emojis, and modern languages without data corruption.
LC_COLLATE and LC_CTYPE: Control how string sorting and character classification work. These settings affect query performance on text columns and the results of ORDER BY operations. Choosing the wrong collation can lead to unexpected sorting behavior or degraded index performance.
TEMPLATE (PostgreSQL): Every new database is cloned from a template. The default template1 includes standard objects and extensions. Using template0 gives you a completely clean slate, which is preferable when you want strict control over what exists in the new database.

In MySQL, the equivalent parameters are simpler because MySQL handles character sets at both the server and database level. The COLLATE clause determines the default sorting and comparison rules for all tables created inside that database unless overridden at the table or column level.

Why These Options Matter in Real Engineering

Choosing the right encoding and collation at creation time is not a cosmetic decision. Once a database is created, changing these properties is extremely difficult and often requires a full data export/import cycle. In large-scale systems, incorrect collation choices have caused production incidents where searches and sorts returned inconsistent results across environments.

Performance implications are also significant. A database with the wrong collation can prevent the query planner from using indexes efficiently during text comparisons, leading to full table scans even when indexes exist.

Creating Databases in Practice: Development Workflows

Professional developers rarely create databases manually in production. Instead, they follow repeatable workflows:

Use infrastructure-as-code tools or migration scripts.
Create separate databases for development, testing, and production.
Automate creation as part of CI/CD pipelines so every developer works with identical starting conditions.

Here is a realistic example of a script you might keep in your project repository:

-- create_dev_db.sql
DO $$
BEGIN
    IF NOT EXISTS (SELECT FROM pg_database WHERE datname = 'my_app_dev') THEN
        PERFORM dblink_exec('host=localhost user=postgres',
            'CREATE DATABASE my_app_dev WITH OWNER = app_user ENCODING = ''UTF8''');
    END IF;
END
$$;

This pattern uses a conditional check to make the script idempotent, meaning it can be run multiple times without error.

The DROP DATABASE Statement

Removing a database is the counterpart to creation and must be treated with respect. The syntax is:

-- PostgreSQL
DROP DATABASE my_app_production;

-- Safer version
DROP DATABASE IF EXISTS my_app_production;

-- MySQL
DROP DATABASE my_app_production;
DROP DATABASE IF EXISTS my_app_production;

The IF EXISTS clause prevents an error if the database does not exist. This is especially valuable in automated scripts.

Important behavior differences:

In PostgreSQL, you cannot drop a database while active connections exist. The server will reject the command unless you use the WITH FORCE option (available in newer versions) or first terminate connections manually.
In MySQL, dropping a database is more permissive but still requires appropriate privileges.

DROP DATABASE is a permanent, non-recoverable operation in most cases. The files on disk are removed, and all data disappears. This is why production systems almost never allow direct DROP DATABASE commands except during controlled maintenance windows.

Internal Workflows and System Behavior

When you execute CREATE DATABASE:

The database server validates your permissions.
It allocates a new database OID (object identifier).
It copies the template files (PostgreSQL) or creates a new subdirectory (MySQL).
It updates the system catalogs (pg_database in PostgreSQL, mysql system database in MySQL).
It initializes the default schema (public in PostgreSQL, the database itself in MySQL).

When you execute DROP DATABASE:

The server checks for active connections and may block or terminate them.
It removes all associated files from the filesystem.
It cleans up metadata entries across system catalogs.
Any open transactions involving that database are rolled back or invalidated.

These operations affect the entire server instance. On very large systems, creating or dropping databases can momentarily increase I/O load because of file system operations.

Best Practices and Scalability Considerations

Never create databases with generic names like "test" or "db1" in production. Use descriptive, versioned names that reflect purpose and environment.
Prefer schemas over multiple databases when you need logical separation within the same data set. This is more efficient because it avoids the overhead of managing separate file sets.
In multi-tenant SaaS applications, some teams create one database per customer for strong isolation, while others use a single database with tenant_id columns. Each approach has different scalability and backup implications.
Always back up critical databases before any DROP operation.
Grant the minimal privileges necessary. Application users should rarely have CREATE DATABASE or DROP DATABASE rights.

Real-World Engineering Example

Imagine you are building an e-commerce platform. You might maintain:

ecommerce_production – live customer data
ecommerce_staging – pre-production testing
ecommerce_dev – local development

You would create these using scripts that also initialize required extensions, default roles, and security policies. When a new feature requires schema changes that cannot be rolled back easily, you create a fresh database from a recent backup of production, test the migration thoroughly, then promote the change.

These patterns ensure consistency, reduce human error, and make your infrastructure reproducible.

For even more in-depth examples and exercises, consider purchasing this SQL Playbook: https://codewithdhanian.gumroad.com/l/hjmix

Day 2: Installing PostgreSQL/MySQL and Setting Up Your First Database

CodeWithDhanian — Sat, 23 May 2026 12:16:57 +0000

In database development, the quality of your local environment directly determines how effectively you will learn and how reliably you will build production systems later. Today we focus entirely on installing PostgreSQL and MySQL, understanding the client-server architecture that powers them, and creating your first functional database. We will examine every step from a professional engineering perspective so you can make informed decisions whether you are a complete beginner or an experienced developer setting up a new machine.

The Client-Server Architecture Behind Every Relational Database

A relational database management system (RDBMS) is never just a file on your computer. It follows a strict client-server model. The database server is a background process (daemon) that runs continuously, manages data files on disk, enforces referential integrity, handles concurrent connections, and executes SQL statements. The client is any tool or application that connects to this server over the network or locally via a socket.

PostgreSQL and MySQL both implement this architecture, but they differ in design philosophy. PostgreSQL is a fully object-relational system with strong emphasis on standards compliance, extensibility, and advanced features such as JSONB support and custom data types. MySQL is optimized for read-heavy web workloads and remains the default choice for many content-driven applications due to its speed and widespread ecosystem support.

When the server starts, it listens on a specific TCP port: 5432 for PostgreSQL and 3306 for MySQL. Clients connect using these ports, authenticate with a username and password, and then send SQL commands. The server parses the query, checks permissions, optimizes the execution plan using its internal query planner, and returns results. Understanding this flow helps you debug connection issues later and appreciate why proper installation and configuration matter for both development speed and long-term scalability.

Choosing Between PostgreSQL and MySQL

Both systems are excellent open-source RDBMS options, but they serve slightly different needs. PostgreSQL excels in complex queries, window functions, full ACID compliance out of the box, and handling large analytical workloads. MySQL is often lighter on resources and has tighter integration with popular web stacks such as PHP and certain cloud platforms. Many professional developers install both so they can work on different projects without friction. We will cover the complete installation process for each.

Installing PostgreSQL

Installation on Major Operating Systems

The installation process places the PostgreSQL server binaries, client tools, and default data directory on your system.

On macOS, the recommended developer approach uses Homebrew. Open your terminal and run:

brew install postgresql
brew services start postgresql

This command installs the latest stable version, creates the necessary directories, and registers PostgreSQL as a background service that starts automatically.

On Ubuntu/Debian Linux, use the package manager:

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql

On Windows, download the official EnterpriseDB installer from the PostgreSQL website and follow the wizard. Choose the default port 5432 and remember the password you set for the postgres superuser.

Post-Installation Verification and Initialization

After installation, PostgreSQL runs an initialization process called initdb (automatically handled by the installer or package manager). This creates the data directory (often /var/lib/postgresql/data on Linux or ~/Library/Application Support/Postgres on macOS) containing the system catalogs, template databases, and configuration files.

Verify everything is running by checking the service status or using the client tool:

psql --version

This confirms the psql command-line client is available and in your PATH.

Switch to the default postgres user (on Linux/macOS) or open pgAdmin on Windows to continue setup.

Setting Up Your First PostgreSQL Database

Create a dedicated database and user instead of using the default postgres superuser for daily work. This follows security best practices and prevents accidental destructive commands.

First, create a new user account:

CREATE USER myappuser WITH PASSWORD 'strong_password_here';

Then create the actual database:

CREATE DATABASE myfirstdb OWNER myappuser;

Grant necessary privileges:

GRANT ALL PRIVILEGES ON DATABASE myfirstdb TO myappuser;

Connect to your new database using the psql client:

psql -U myappuser -d myfirstdb -h localhost

The -U flag specifies the user, -d the database, and -h localhost forces a TCP connection so you can see network behavior. Once inside psql, you can run \l to list databases and confirm your new database exists.

For a graphical interface, install pgAdmin. It provides a visual browser of databases, schemas, tables, and query tools while still connecting to the same underlying server. This dual workflow (command line for speed, GUI for exploration) is standard in professional development environments.

Installing MySQL

Installation Approaches

MySQL installation follows a similar client-server pattern but uses different binaries and configuration files.

On macOS with Homebrew:

brew install mysql
brew services start mysql

On Ubuntu/Debian:

sudo apt update
sudo apt install mysql-server
sudo systemctl start mysql

During installation on Linux, the system creates the root user with a temporary password that you must secure immediately using the mysql_secure_installation script.

On Windows, use the official MySQL Installer and select the Developer Default setup type.

MySQL Post-Installation Steps

MySQL stores its data in a directory controlled by the mysqld daemon. The main configuration file is my.cnf (or my.ini on Windows), where you can later tune buffer sizes and connection limits.

Verify installation:

mysql --version

Secure the root user immediately and create a dedicated application user:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'strong_root_password';
CREATE USER 'myappuser'@'localhost' IDENTIFIED BY 'strong_password_here';
CREATE DATABASE myfirstdb;
GRANT ALL PRIVILEGES ON myfirstdb.* TO 'myappuser'@'localhost';
FLUSH PRIVILEGES;

Connect to your new database:

mysql -u myappuser -p myfirstdb

The -p flag prompts for the password, keeping it out of command history.

MySQL Workbench serves as the official graphical client, offering schema design, query execution, and server status monitoring.

Best Practices for Production-Ready Local Setup

Never leave default passwords or the root user exposed in development. Always create application-specific users with the minimum required privileges. On both systems, configure the server to listen only on localhost during development to reduce the attack surface.

Monitor the server logs early. PostgreSQL logs are in the data directory under log/, while MySQL uses the error log defined in the configuration file. These logs reveal connection attempts, slow queries, and authentication failures.

Consider using Docker for repeatable environments in larger projects. A simple Docker Compose setup can spin up isolated PostgreSQL or MySQL containers with version pinning, making your development environment identical across team members and machines.

Performance implications appear even at this stage: proper data directory placement on SSDs rather than HDDs dramatically affects query speed. Memory allocation in the configuration files determines how much caching the server can perform before hitting disk.

These foundational choices affect scalability later. A correctly installed and secured RDBMS will support everything from simple prototypes to high-traffic applications without requiring major rework.

If you want a complete, project-driven SQL learning experience that builds directly on these setup foundations with guided exercises and real application patterns, consider purchasing the SQL Playbook at https://codewithdhanian.gumroad.com/l/hjmix. It provides the structured depth many developers wish they had when starting out.

Day 1: What is SQL – Databases, Tables, and Relational Concepts

CodeWithDhanian — Sat, 23 May 2026 11:59:28 +0000

The Foundation of Modern Data Management

SQL stands as the universal language for interacting with relational data. Before writing a single query, it is essential to understand exactly what SQL is, why it exists, and how it sits at the heart of virtually every significant application you use today. SQL, which expands to Structured Query Language, is a declarative programming language designed specifically for managing, organizing, and retrieving data stored in relational databases.

At its core, SQL allows you to tell a database system what you want rather than how to retrieve it. This declarative nature distinguishes SQL from imperative languages where you must write step-by-step instructions. The database engine handles the optimization, execution plan, and data access internally, freeing developers to focus on business logic instead of low-level file operations.

What Is a Database?

A database is an organized collection of structured data that is stored and accessed electronically. Think of it as a highly efficient digital filing cabinet designed for speed, reliability, and concurrent access by thousands or even millions of users simultaneously.

Unlike a simple spreadsheet or text file, a production database must satisfy strict requirements: it must maintain data integrity, support atomic transactions, allow concurrent access without corruption, provide backup and recovery mechanisms, and scale as data volumes grow. These capabilities are delivered by specialized software known as a Relational Database Management System (RDBMS).

The RDBMS acts as the intermediary between your application and the raw data files on disk. It manages memory buffers, disk I/O, query optimization, concurrency control through locking and multiversion concurrency control (MVCC), and crash recovery. Popular RDBMS implementations include systems that follow the same fundamental principles you will master in this series.

The Relational Model: The Theoretical Backbone

The entire concept of SQL rests on the relational model, first formally defined by Edgar F. Codd. In this model, all data is represented in two-dimensional structures called tables (also known as relations). Each table represents a specific entity or concept in your domain.

The power of the relational model comes from its mathematical foundation in set theory and predicate logic. This foundation guarantees that data can be combined, filtered, and transformed in predictable, mathematically sound ways. Relationships between tables are expressed through shared values rather than physical pointers, providing flexibility and reducing data duplication.

Key Principles of Relational Design

Several core rules govern how data should be structured in a relational database:

Atomicity: Each cell in a table holds a single, indivisible value.
Uniqueness: Every row in a table must be uniquely identifiable.
Order independence: The sequence of rows or columns does not matter; the data is defined by its content and relationships.
Consistency: All values in a column must belong to the same data type and follow the same rules.

These principles lead to the practice of normalization, which organizes data to minimize redundancy while preserving relationships. Although we will explore normalization in greater depth later, understanding that it exists helps explain why SQL databases feel so structured and reliable.

Tables: The Fundamental Building Blocks

A table is the primary structure where data lives. Each table has a name that should clearly describe the entity it represents, such as users, orders, or products.

Inside a table you find:

Columns (also called fields or attributes): These define the structure and type of data the table can hold. Each column has a name and a specific data type that enforces what kind of information can be stored.
Rows (also called records or tuples): These are the actual data entries. Each row represents one complete instance of the entity described by the table.

For example, consider an e-commerce application. You might have a products table with columns like product_id, name, price, category, and stock_quantity. Each row would represent one specific product available for sale.

Visualizing Table Structure

A table can be imagined as a grid. The column headers define the blueprint, while each subsequent row fills in actual values according to that blueprint. The RDBMS enforces rules at the column level, such as requiring certain columns to be filled (NOT NULL) or ensuring values fall within acceptable ranges.

Rows, Columns, and Data Relationships

Every row in a table must be uniquely identifiable. This is typically achieved using a primary key — a column or combination of columns whose value is unique for every row. The primary key serves as the definitive address for that record.

Relationships between tables are established using foreign keys. A foreign key in one table references the primary key in another table, creating a logical link. This linkage is what makes the database relational — data in separate tables can be meaningfully connected without duplicating entire records.

For instance:

An orders table might contain a customer_id column that references the customer_id primary key in a customers table.
This design allows one customer to place many orders while storing customer details only once.

This approach dramatically reduces data redundancy, improves consistency, and makes complex queries possible.

Why SQL and Relational Databases Dominate

SQL combined with the relational model offers several powerful advantages:

Declarative power: You describe the desired result, and the query optimizer determines the most efficient way to retrieve it.
Data integrity: Built-in constraints protect against invalid data.
Standardization: SQL is an ANSI/ISO standard, meaning core concepts transfer across different RDBMS implementations.
Scalability: Well-designed relational databases can handle terabytes of data while maintaining performance through proper indexing and query design.
Transaction safety: Changes can be grouped into transactions that either fully succeed or fully roll back.

Of course, no technology is perfect. Relational databases can face challenges with extremely high-velocity write workloads or when dealing with highly unstructured data. These limitations have led to the rise of other database types, but for structured business data — finance, e-commerce, healthcare, inventory, user management — the relational model remains the gold standard.

Practical Developer Perspective

As a software engineer, you will interact with SQL daily. Whether building a new feature, debugging performance issues, or designing a new data model, a deep understanding of these foundational concepts prevents costly mistakes later.

When designing tables, always ask:

What entity does this table represent?
What are the natural relationships to other entities?
Which columns must be unique?
What constraints protect data quality?

These questions guide you toward clean, maintainable database schemas that scale gracefully as your application grows.

If you are serious about mastering SQL and want a complete hands-on playbook that follows this exact structured learning path with exercises, challenges, and advanced patterns, consider purchasing the SQL Playbook at https://codewithdhanian.gumroad.com/l/hjmix. It is designed to complement this series perfectly.

Day 1: What is Linux – History, Distributions, and Philosophy

CodeWithDhanian — Tue, 19 May 2026 04:32:02 +0000

Linux is a powerful, open-source operating system kernel that powers everything from smartphones and personal computers to the world's largest supercomputers and cloud servers. At its core, Linux refers specifically to the kernel originally developed by Linus Torvalds. When combined with the tools and utilities from the GNU Project, it forms a complete operating system commonly known as GNU/Linux.

The Birth of Linux: A Hobby That Changed the World

In 1991, Linus Torvalds, a Finnish computer science student at the University of Helsinki, grew frustrated with the limitations of existing systems like Minix, a small Unix-like operating system used for educational purposes. Torvalds wanted a free, modifiable system that could run on his Intel 386 personal computer.

On August 25, 1991, he posted a now-famous message to the comp.os.minix newsgroup:

"I'm doing a (free) operating system (just a hobby, won't be big and professional like gnu) for 386(486) AT clones."

He released the first version of the kernel, version 0.01, in September 1991. Initially, Torvalds distributed it under a license that restricted commercial use. In 1992, he switched to the GNU General Public License (GPL) version 2. This decision was pivotal: it allowed anyone to view, modify, and distribute the source code, provided that derivative works remained under the same license. This copyleft mechanism ensured that Linux remained free and open forever.

By combining the Linux kernel with the nearly complete GNU operating system tools (such as the GNU Compiler Collection, Bash shell, and core utilities), developers created a fully functional, free Unix-like operating system. Richard Stallman of the Free Software Foundation has long advocated referring to it as GNU/Linux to acknowledge the essential contributions of the GNU Project.

The GNU Project and the Philosophy of Free Software

The foundation of Linux's philosophy traces back to 1983, when Richard Stallman launched the GNU Project ("GNU's Not Unix"). Stallman, a programmer at MIT, witnessed the shift from collaborative software sharing in the 1970s to proprietary, closed-source software in the 1980s. Companies began restricting access to source code, preventing users from studying, modifying, or sharing programs.

Stallman defined free software through four essential freedoms (often called the "Four Freedoms"):

Freedom 0: The freedom to run the program as you wish, for any purpose.
Freedom 1: The freedom to study how the program works and change it to suit your needs (access to the source code is a precondition).
Freedom 2: The freedom to redistribute copies so you can help your neighbor.
Freedom 3: The freedom to improve the program and release your improvements to the public so that the whole community benefits (again, source code access is required).

These freedoms emphasize user liberty, not price. "Free" refers to freedom, like free speech, not free beer. The GNU General Public License (GPL) enforces these freedoms through copyleft, ensuring that modified versions remain free.

Linus Torvalds took a more pragmatic approach. He focused on technical excellence and collaboration, viewing open development as the best way to create high-quality software. This blend of Stallman's idealism and Torvalds' pragmatism fueled Linux's explosive growth. Today, thousands of developers worldwide contribute to the kernel through platforms like Git.

Linux embodies core principles:

Openness: Source code is publicly available.
Collaboration: Global community-driven development.
Stability and Security: Rigorous peer review reduces bugs and vulnerabilities.
Portability: Runs on diverse hardware architectures.
Efficiency: Lightweight and highly customizable.

Linux Distributions: Flavors for Every Need

A Linux distribution (or distro) is a complete operating system built around the Linux kernel, bundled with the GNU tools, a package manager, desktop environment (or lack thereof for servers), and additional software. Distributions make Linux accessible by handling installation, updates, and hardware support.

Major families of distributions include:

Debian-based distributions use the .deb package format and the APT package manager. They emphasize stability.

Debian: The parent distribution, known for rock-solid reliability and vast software repositories. It follows a strict free software philosophy.
Ubuntu: Created by Canonical in 2004, Ubuntu is user-friendly with regular releases (every six months, with Long Term Support versions every two years). It powers many desktops, servers, and cloud instances. Its philosophy prioritizes accessibility and community support.
Linux Mint: Built on Ubuntu, Mint focuses on a polished, Windows-like experience with the Cinnamon desktop. It is ideal for beginners transitioning from other operating systems.

Red Hat-based distributions use the .rpm package format and tools like YUM or DNF.

Fedora: Sponsored by Red Hat, Fedora serves as a testing ground for cutting-edge technologies that later appear in enterprise products. It targets developers and enthusiasts.
Red Hat Enterprise Linux (RHEL): A commercial distribution with paid support, widely used in business environments for its stability and long support cycles.
CentOS Stream (and community forks like AlmaLinux and Rocky Linux): Free alternatives providing binary compatibility with RHEL for production servers.

Other notable distributions:

Arch Linux: A rolling-release distro where users build the system from minimal components. It follows the "keep it simple" philosophy and appeals to advanced users who value customization.
Gentoo: Highly optimized; users compile software from source for maximum performance on their specific hardware.

Each distribution balances trade-offs between stability, latest features, ease of use, and target audience. Choosing one depends on your goals—beginner desktop, secure server, or development workstation.

Understanding the Linux Ecosystem in Practice

When you install a Linux distribution, you receive more than just the kernel. You get:

The kernel managing hardware, processes, and memory.
GNU tools for core functionality.
A shell (usually Bash) for command-line interaction.
A package manager for installing and updating software.
Optional graphical interfaces like GNOME, KDE Plasma, or XFCE.

Example of basic system identification commands (these will work on almost any Linux system):

# View kernel version and system information
uname -a

# Display detailed distribution information
cat /etc/os-release

# Check CPU and hardware details
lscpu
lsblk

These commands reveal the layered nature of Linux: the kernel at the base, user-space tools above it, and your chosen distribution providing the complete experience.

Linux's strength lies in its adaptability. You can strip it down to a minimal server or expand it into a full multimedia workstation. Its philosophy of freedom empowers users to own their computing experience rather than being locked into proprietary ecosystems.

This foundational understanding of Linux's origins, guiding principles, and variety of distributions sets the stage for mastering the system. The collaborative spirit that birthed Linux continues today through millions of users and developers worldwide.

If you are serious about achieving true Linux mastery—from beginner commands to advanced administration, security, scripting, and production server deployment—consider investing in a comprehensive resource. The Linux Mastery Ebook available at https://codewithdhanian.gumroad.com/l/hqtbxt provides structured, hands-on guidance that builds directly on these fundamentals and takes you far beyond them.

CONCEPT 1: Linux Installation Methods (Dual Boot, VM, Bare Metal)

CodeWithDhanian — Mon, 18 May 2026 13:20:14 +0000

Choosing how to install Linux is one of the most foundational decisions a system administrator, developer, or enthusiast will make. The installation method directly influences performance, isolation, hardware access, ease of experimentation, and long-term maintainability. The three primary approaches—bare metal, virtual machine (VM), and dual boot—each solve different problems and come with distinct trade-offs in architecture, workflow, and operational behavior.

This chapter explores each method in depth, from the underlying system architecture to practical implementation, real-world engineering considerations, and advanced usage patterns.

Understanding the Installation Landscape

At its core, installing Linux means placing a functional kernel, init system (most commonly systemd), root filesystem, and necessary userspace tools onto a storage medium that the bootloader can locate and execute.

The Linux kernel is a monolithic kernel that interacts directly with hardware through drivers compiled either statically or as loadable kernel modules. The bootloader (typically GRUB) is responsible for loading the kernel image (vmlinuz), initial ramdisk (initrd or initramfs), and passing kernel parameters.

Each installation method changes where and how this stack executes:

Bare metal runs the kernel directly on physical CPU, memory, and devices.
Virtual machine runs the kernel inside a hypervisor-managed environment.
Dual boot shares physical hardware between two operating systems.

Bare Metal Installation: Maximum Performance and Direct Hardware Control

Bare metal installation means Linux runs natively on the physical hardware without any abstraction layer between the kernel and the CPU, memory, storage controllers, or peripherals.

Why Choose Bare Metal?

Bare metal delivers the highest possible performance because there is zero hypervisor overhead. All CPU cycles, memory bandwidth, and I/O operations go directly to your Linux environment. This approach is preferred for production servers, high-performance computing, gaming desktops, audio/video workstations, and any workload where latency or throughput is critical.

Hardware Preparation and UEFI vs BIOS

Modern systems use UEFI (Unified Extensible Firmware Interface) with GPT partitioning. Legacy systems may still use BIOS with MBR. During installation, you must decide on an EFI System Partition (ESP) formatted as FAT32 (typically 512 MiB) mounted at /boot/efi.

A recommended bare metal partition layout for a production system might look like this:

/dev/nvme0n1p1     512M     FAT32     /boot/efi     (ESP)
/dev/nvme0n1p2     1G       ext4      /boot
/dev/nvme0n1p3     remaining space    LVM physical volume

LVM (Logical Volume Manager) is strongly recommended because it allows online resizing of logical volumes without downtime.

Step-by-Step Bare Metal Installation Process

Create bootable media using dd, Rufus, or Ventoy.
Boot from the media and enter the live environment.
Partition disks using gdisk, fdisk, or the distribution installer.
Format partitions (mkfs.ext4, mkfs.fat, etc.).
Mount the target filesystem hierarchy under /mnt.
Use debootstrap, pacstrap, or the graphical installer to populate the root filesystem.
Generate fstab, chroot into the new system, install the bootloader, and configure networking.
Reboot and remove installation media.

Advanced Considerations

On bare metal, you have full control over kernel parameters passed via GRUB (quiet splash, mitigations=off, iommu=pt). You can compile a custom kernel tailored to your hardware, enable specific CPU features (AVX512, huge pages), and optimize I/O schedulers (mq-deadline, bfq, or none for NVMe).

Limitations: No easy rollback. Hardware failures affect the entire system. Testing new distributions requires physical hardware or reinstallation.

Virtual Machine Installation: Isolation, Flexibility, and Rapid Experimentation

Virtual machines run Linux inside a hypervisor such as KVM/QEMU, VirtualBox, VMware, or Hyper-V. The hypervisor presents virtualized hardware (vCPU, vRAM, virtio devices) to the guest.

Architecture of Modern Virtualization

KVM (Kernel-based Virtual Machine) turns the Linux kernel into a hypervisor by loading the kvm and kvm-intel/kvm-amd modules. QEMU provides device emulation, while virtio drivers deliver near-native performance for block, network, and graphics devices.

libvirt acts as a management layer, providing a unified API for creating, starting, and monitoring virtual machines.

Creating a High-Performance Linux VM

Here is a complete example using virt-install (recommended for production-grade VMs):

virt-install \
  --name ubuntu-server-24 \
  --ram 8192 \
  --vcpus 8 \
  --cpu host-passthrough \
  --disk path=/var/lib/libvirt/images/ubuntu24.qcow2,size=80,format=qcow2,bus=virtio \
  --network network=default,model=virtio \
  --graphics none \
  --console pty,target_type=serial \
  --location https://releases.ubuntu.com/24.04/ubuntu-24.04-live-server-amd64.iso \
  --extra-args 'console=ttyS0,115200n8'

Key options explained:

--cpu host-passthrough: Exposes the full host CPU capabilities to the guest for maximum performance.
virtio drivers: Paravirtualized devices that bypass much of the emulation overhead.
qcow2 format: Supports snapshots, compression, and thin provisioning.

Advanced VM Techniques

PCI passthrough: Assign physical GPUs, NICs, or storage controllers directly to the VM using VFIO.
Nested virtualization: Run VMs inside VMs (useful for testing Kubernetes clusters).
Live migration: Move running VMs between hosts with zero downtime.

Advantages: Snapshots, easy cloning, hardware independence, and the ability to run multiple distributions simultaneously.

Limitations: Slight performance overhead (typically 2-10% for CPU-bound tasks, higher for I/O without virtio). Resource contention on the host.

Dual Boot: Sharing Physical Hardware Between Operating Systems

Dual boot allows both Linux and another OS (usually Windows) to coexist on the same physical machine, with the bootloader presenting a choice at startup.

Bootloader Architecture in Dual Boot

GRUB is installed to the EFI System Partition and configured to detect other operating systems via os-prober. The chainloading mechanism works as follows:

UEFI firmware loads GRUB.efi.
GRUB reads its configuration (/boot/grub/grub.cfg).
Menu is displayed with entries for Linux and Windows.
Selecting Windows chainloads the Windows Boot Manager.

Safe Dual Boot Installation Strategy

Always install Windows first. Windows will claim the entire disk and create its own partitions (EFI, MSR, Windows, Recovery). Then:

Shrink the Windows partition from within Windows using Disk Management or diskpart.
Boot from Linux media.
Install Linux into the unallocated space.
Let the Linux installer install GRUB to the EFI partition (it will automatically detect Windows).
Update GRUB configuration: sudo update-grub.

Common Challenges and Solutions

Fast Startup in Windows can leave NTFS partitions in an inconsistent state. Disable it.
Time synchronization: Windows uses local time, Linux uses UTC. Set Linux to use local time or Windows to use UTC.
Secure Boot: Sign your custom kernels or use distribution-provided signed bootloaders.
Shared data partition: Create an NTFS or exFAT partition accessible by both systems.

Advantages: Native performance for both OSes, access to hardware-specific applications.

Limitations: Risk of bootloader corruption, difficulty in resizing partitions later, and inability to run both OSes simultaneously.

Choosing the Right Method: Decision Framework

Consider these factors when deciding:

Performance needs: Bare metal for maximum speed, VM for acceptable performance with isolation.
Experimentation frequency: VM wins for rapid testing.
Hardware availability: Dual boot or bare metal when you have dedicated machines.
Production requirements: Bare metal or Type-1 hypervisor (KVM) for servers.
Development workflow: Many engineers maintain a bare metal workstation for daily work and multiple VMs for isolated testing environments.

Best practice: Start with a virtual machine to learn safely, move to dual boot for daily driver usage, and deploy to bare metal for production workloads.

Practical Engineering Workflows

Professional Linux users often combine methods. A common setup includes:

Bare metal host running KVM + libvirt.
Multiple specialized VMs (development, testing, CI/CD).
Physical dual-boot machine for graphics-intensive work.
Cloud instances for additional capacity.

This hybrid approach provides both performance and flexibility.

DEV Community: CodeWithDhanian

Middleware & Request-Response Pipeline

4.1 What Middleware Actually Is

4.2 The Request-Response Lifecycle

4.3 Middleware Can Do Four Fundamental Things

1. Execute logic

2. Modify the request

3. Modify the response

4. End the request

4.4 next() Is the Control Mechanism

4.5 Application-Level Middleware

4.6 Router-Level Middleware

4.7 Authentication Middleware

4.8 Authorization Middleware

4.9 Validation Middleware

4.10 Error-Handling Middleware

4.11 Async Middleware

4.12 Complete Backend Pipeline

4.13 Middleware Composition

4.14 Route-Specific vs Global Middleware

4.15 The Most Important Middleware Rules

Backend Engineering Ebook

Authentication & Authorization (JWT, OAuth2, Sessions)

1. Authentication vs Authorization

2. The Complete Authentication Architecture

3. Password Authentication

Node.js Example

4. JWT Authentication

JWT Authentication Flow

Complete JWT Middleware

5. JWT Access Tokens and Refresh Tokens

6. Secure Cookie Configuration

7. Server-Side Sessions

Redis Session Example

8. OAuth 2.0

9. OAuth 2.0 Authorization Code + PKCE

10. OAuth 2.0 Scopes

11. OpenID Connect

12. Authorization with RBAC

Authorization Middleware

13. Resource-Level Authorization

14. RBAC vs ABAC

15. Authentication Middleware Structure

16. Complete Protected API Example

17. Multi-Factor Authentication

18. Session Lifecycle

19. Logout and Token Revocation

20. Authentication Security Controls

21. Authentication Decision Structure

CRUD Operations with a Relational Database (SQL)

Core CRUD Operations

1. CREATE — Inserting New Records

2. READ — Retrieving Data

3. UPDATE — Modifying Existing Data

4. DELETE — Removing Records

Complete Database Structure

Creating Tables

Typical Backend CRUD Flow

Example CRUD API (Node.js + Express)

Best Practices

Backend Engineering eBook

Learn HTTP & RESTful API Design

Client–Server Architecture

HTTP Request Structure

Request Line

Headers

Body

HTTP Response Structure

HTTP Methods

REST Resource Design

URL Hierarchy

Building a REST API with Express

GET

GET by ID

POST

HTTP Status Codes

JSON Response Standards

REST Design Principles

Backend Engineering eBook

How RAG actually works (clearly explained in under 2 mins):

4.4 `next()` Is the Control Mechanism