Skip to main content

Backend Database Queries, Indexes, and Triggers (Structured Notes)

Fetching a Single User​

Goal​

  • Fetch one user and their profile information using a user ID.
  • Endpoint example:
GET /v1/users/:userId

Query Logic​

  • Use the same query used for fetching all users.
  • Add a WHERE clause to filter by user_id.

SQL Query​

SELECT 
  u.*, 
  to_jsonb(up.*) AS profile
FROM users u
LEFT JOIN user_profiles up
ON u.id = up.user_id
WHERE u.id = $userId;

Parameterized Query​

  • $userId is a parameter placeholder.
  • The actual value is passed later.

Example value:

'user_uuid_value'

Why Parameterized Queries​

  • Prevent SQL injection attacks.
  • User input is treated as data, not executable SQL.

Backend Flow​

  1. Frontend sends request:

    GET /v1/users/:userId

  2. Router extracts userId.

  3. Handler receives request.

  4. Service layer processes logic.

  5. Repository runs SQL query with parameter.

  6. Database returns result.

  7. Result serialized to JSON and sent to frontend.

Dynamic Filters and Sorting in APIs

Problem​

APIs returning lists must support:

  • Filtering
  • Sorting
  • Pagination

Example API:

GET /v1/users

Possible query parameters:

?page=1
&limit=10
&letter=j
&sort_by=email
&sort_order=asc

Query Parameters

Pagination Parameters​

Page​

  • Defines which page of results to return.

Limit​

  • Defines number of records per page.

Default example:

page = 1
limit = 10

Filtering Example

Requirement​

Filter users whose name starts with a given letter.

Query parameter:

letter=j

SQL Filter​

WHERE u.full_name ILIKE :letter || '%'

where :letter = 'J'

Explanation​

  • ILIKE → case-insensitive match.
  • % → wildcard representing any characters after the first letter.

Example:

J%

Matches:

  • John
  • Jane
  • Jack

Does not match:

  • Alice
  • Bob

Sorting Results

Users may choose sorting fields.

Allowed fields:

  • email
  • full_name
  • created_at

SQL Example​

ORDER BY u.email DESC

Dynamic Sorting​

Parameters:

sort_by=email
sort_order=desc

Example SQL structure:

ORDER BY :sort_by :sort_order

Default values if none provided:

sort_by = created_at
sort_order = desc

Pagination

When returning lists, pagination is required.

SQL uses:

  • OFFSET
  • LIMIT

Example Query​

LIMIT :limit
OFFSET :offset

Example​

If:

limit = 10
page = 2

Offset:

offset = (page - 1) * limit

So:

offset = 10

Complete Query Example

image 
SELECT 
  u.*, 
  to_jsonb(up.*) AS profile
FROM users u
LEFT JOIN user_profiles up
ON u.id = up.user_id
WHERE u.full_name ILIKE :letter || '%'
ORDER BY :sort_by :sort_order
LIMIT :limit
OFFSET :offset;

Creating a User (POST API)

Endpoint​

POST /api/users

SQL Query​

INSERT INTO users (
  email,
  full_name,
  password_hash
)
VALUES (
  :email,
  :full_name,
  :password_hash
)
RETURNING *;

Explanation​

  • Inserts new user.
  • Returns created row.

Example result:

id
email
full_name
password_hash
created_at
updated_at

Updating a User Profile

Endpoint​

PATCH /api/users/:userId

Partial Updates​

User may update:

  • bio
  • phone
  • avatar_url

Not all fields must be present.

Backend logic:

  • Only update fields provided.

Example SQL Update Query

UPDATE user_profiles
SET
  bio = :bio,
  phone = :phone
WHERE user_id = :userId
RETURNING *;

Result​

Updated row returned.

Updating updated_at Field

Problem​

Whenever a row is updated, updated_at should change.

Example:

created_at = first creation time
updated_at = last modification time

Manual Solution​

Update field manually in every query.

Example:

updated_at = CURRENT_TIMESTAMP

Problem​

  • Must remember in every update query.

Database Triggers

Solution​

Use triggers.

What is a Trigger​

A trigger is a database function that runs automatically when:

  • INSERT
  • UPDATE
  • DELETE

events occur.

Trigger Function Example

Function updates updated_at.

Pseudo SQL:

NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;

Meaning:

  • Whenever row updates
  • Set updated_at to current timestamp.

Trigger Setup

Create trigger on tables:

  • users
  • user_profiles
  • projects
  • tasks

Trigger runs:

BEFORE UPDATE

Purpose:

  • Automatically update timestamp.

Result

Whenever a row is updated:

updated_at automatically changes

No application code required.

Database Indexes

What is an Index​

Indexes improve query performance.

Analogy​

Like index section of a book.

Example:

ChapterPage
Chapter 110
Chapter 225

Instead of reading whole book, jump directly to page.

Problem Without Index

When querying:

SELECT * FROM tasks WHERE id = '123'

Database performs:

Sequential Scan​

Process:

  1. Check first row
  2. Check second row
  3. Check third row
  4. Continue until match found

If table has:

1 million rows

Query becomes slow.

How Index Works

Index creates lookup table. Stored in one place and give direct access.

Example structure:

idrow_location
1disk_address
2disk_address
3disk_address

Database can:

  1. Find id instantly
  2. Jump to row location
  3. Return data

Much faster.

Indexed Fields

Indexes are useful when fields appear in:

  1. WHERE clauses
  2. JOIN conditions
  3. ORDER BY clauses

Example Indexes

Email Index​

CREATE INDEX idx_users_email
ON users(email);

Used when searching user by email.

Created At Index​

CREATE INDEX idx_users_created_at
ON users(created_at DESC);

Optimizes sorting users by creation time.

Project Tasks Query​

Fetching tasks for a project:

SELECT * FROM tasks
WHERE project_id = ?

Index required:

CREATE INDEX idx_tasks_project_id
ON tasks(project_id);

Assigned Tasks​

Fetching tasks assigned to a user.

SELECT * FROM tasks
WHERE assigned_to = ?

Index:

CREATE INDEX idx_tasks_assigned_to
ON tasks(assigned_to);

Index Tradeoffs

Indexes improve read performance.

But add overhead for:

  • INSERT
  • UPDATE
  • DELETE

Because index must also update.

Example:

Insert row
Update index

When to Create Indexes

Create index when:

  • Field appears in JOIN
  • Field appears in WHERE
  • Field appears in ORDER BY
  • Query runs frequently

Migration for Indexes and Triggers

New migration file created to:

  1. Add indexes
  2. Add triggers

Migration includes:

Index creation​

image

Example:

CREATE INDEX idx_tasks_status
ON tasks(status);

Index sometimes creates an pverhead to store the latest data into lookup.

Trigger creation​

Example:

CREATE TRIGGER update_timestamp
BEFORE UPDATE
ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();

Applying Migration

Migration tool command:

dbmate up

Result:

  • Indexes created
  • Triggers installed

Trigger Test

Function​

image image

Update profile:

UPDATE user_profiles
SET bio = 'updated bio'
WHERE user_id = 'uuid';

Result:

updated_at automatically changes

Trigger works correctly.

Real Backend Workflow with Databases

Backend engineers typically:

  1. Design API
  2. Define database schema
  3. Write migrations
  4. Seed data
  5. Write queries
  6. Use parameterized queries
  7. Add indexes for performance
  8. Use triggers for automation

Key Responsibilities of Backend Engineers

Backend engineers mainly:

  • Analyze API payloads
  • Build dynamic SQL queries
  • Use parameterized queries
  • Optimize queries with indexes
  • Manage migrations
  • Maintain database performance

Practical Reality

This covers roughly:

~80% of database work for backend engineers

Remaining topics include:

  • transactions
  • ACID transactions
  • query optimization
  • distributed databases
  • caching
  • replication
  • sharding

If you want, I can also give you a super condensed 10-minute backend database revision guide (joins + indexes + migrations + triggers) that is perfect for backend interviews and system design rounds.