[PR #5995] perf(backend): add indexes and optimize database queries #5451

New issue

Open

opened 2026-03-17 02:53:39 +03:00 by kerem · 0 comments

kerem commented

2026-03-17 02:53:39 +03:00

Owner

📋 Pull Request Information

Original PR: https://github.com/hoppscotch/hoppscotch/pull/5995
Author: @tembo[bot]
Created: 3/16/2026
Status: 🔄 Open

Base: next ← Head: tembo/optimize-performance-db-queries

📝 Commits (10+)

feb23df perf(db): add indexes and optimize collection/request tree queries for better performance
e3a46a3 refactor(team-collection,user-collection): optimize export by bulk-fetching and in-memory tree building; fix prisma error handling in team service
3a02f57 chore(deps): update @types/node to 25.2.3 in pnpm-lock.yaml
ce6c7ab perf(db): remove unused indexes and add updatedOn to request reordering queries
0b23ed4 fix(backend): address review comments and refine db query optimizations
df0e81e perf(backend): add functional email index and fix raw SQL usage in orderIndex updates
75d64ee refactor(user-collection): improve buildUserCollectionTree calls and add comments
35ed66a perf(backend): add comments and fix null assertion for better code clarity
28a31e3 perf(db): add indexes and optimize batch update queries with safer SQL cases
004021b refactor(admin,user-collection): improve type safety by explicit mappings

📊 Changes

18 files changed (+463 additions, -457 deletions)

View changed files

➕ packages/hoppscotch-backend/prisma/migrations/20260316060000_add_performance_indexes/migration.sql (+48 -0)
📝 packages/hoppscotch-backend/prisma/schema.prisma (+20 -0)
📝 packages/hoppscotch-backend/src/admin/admin.service.spec.ts (+5 -27)
📝 packages/hoppscotch-backend/src/admin/admin.service.ts (+28 -27)
📝 packages/hoppscotch-backend/src/mock-server/mock-server.service.ts (+34 -24)
📝 packages/hoppscotch-backend/src/shortcode/shortcode.service.spec.ts (+6 -6)
📝 packages/hoppscotch-backend/src/shortcode/shortcode.service.ts (+1 -1)
📝 packages/hoppscotch-backend/src/team-collection/team-collection.service.ts (+64 -59)
📝 packages/hoppscotch-backend/src/team-request/team-request.service.spec.ts (+13 -10)
📝 packages/hoppscotch-backend/src/team-request/team-request.service.ts (+14 -8)
📝 packages/hoppscotch-backend/src/team/team.service.spec.ts (+14 -21)
📝 packages/hoppscotch-backend/src/team/team.service.ts (+58 -95)
📝 packages/hoppscotch-backend/src/user-collection/user-collection.service.ts (+108 -94)
📝 packages/hoppscotch-backend/src/user-history/user-history.service.spec.ts (+3 -3)
📝 packages/hoppscotch-backend/src/user-history/user-history.service.ts (+2 -4)
📝 packages/hoppscotch-backend/src/user-request/user-request.service.ts (+12 -7)
📝 packages/hoppscotch-backend/src/user/user.service.ts (+18 -17)
📝 pnpm-lock.yaml (+15 -54)

📄 Description

Summary

Optimizes database performance across the Hoppscotch backend by adding missing indexes, eliminating N+1 query patterns, and reducing round-trips for batch operations.

Changes

Database Indexes (12 new indexes)

TeamMember(userUid) — listing teams for a user
TeamRequest(collectionID) — listing requests in a collection
TeamEnvironment(teamID) — listing environments in a team
UserHistory(userUid, reqType, executedOn DESC) — paginated history with ordering
UserEnvironment(userUid) — listing user environments
UserRequest(collectionID) — listing requests in a collection
PersonalAccessToken(userUid) — listing user tokens
Shortcode(creatorUid) — listing user's shortcodes
MockServer(workspaceType, workspaceID, deletedAt) — filtered mock server listing
MockServer(creatorUid, deletedAt) — user's mock servers
Account(userId) — user's provider accounts
VerificationToken(userUid) — user's verification tokens

Query Optimizations

Admin service — Replace loading ALL user emails into memory + IN clause with NOT EXISTS subquery for fetchInvitedUsers
Team/User collection export — Replace recursive per-collection queries with 2 bulk queries + in-memory tree building (O(D) queries instead of O(B^D))
Mock server — Change recursive collection traversal to breadth-first with batched queries per level
Team/User request sorting — Replace N individual UPDATE queries with single CASE WHEN statement; preserve updatedOn timestamps
Team service — Remove redundant deleteMany before team.delete (cascade handles it); batch user lookups via findUsersByIds; single groupBy for sole-owner check; simplified cursor-based pagination
User service — Single-pass member counting instead of 4 separate .filter() calls for workspace stats
Shortcode/History — findFirst → findUnique for primary key lookups
Team request search — Add mode: 'insensitive' for case-insensitive search

Test Updates

Updated test mocks across 5 spec files to match new query patterns

Test plan

All 685 existing tests pass (18 suites)
Verify index creation on staging database
Load test collection export with deep hierarchies to confirm no N+1 regressions

_{🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.}

## 📋 Pull Request Information **Original PR:** https://github.com/hoppscotch/hoppscotch/pull/5995 **Author:** [@tembo[bot]](https://github.com/apps/tembo) **Created:** 3/16/2026 **Status:** 🔄 Open **Base:** `next` ← **Head:** `tembo/optimize-performance-db-queries` --- ### 📝 Commits (10+) - [`feb23df`](https://github.com/hoppscotch/hoppscotch/commit/feb23dff6cb4daca64af132269fb315802776246) perf(db): add indexes and optimize collection/request tree queries for better performance - [`e3a46a3`](https://github.com/hoppscotch/hoppscotch/commit/e3a46a3fedbbb73a1c4ae76fac20fbcac2ecdeba) refactor(team-collection,user-collection): optimize export by bulk-fetching and in-memory tree building; fix prisma error handling in team service - [`3a02f57`](https://github.com/hoppscotch/hoppscotch/commit/3a02f5775c26bb5267dd59a8be26cd6f9c9aae02) chore(deps): update @types/node to 25.2.3 in pnpm-lock.yaml - [`ce6c7ab`](https://github.com/hoppscotch/hoppscotch/commit/ce6c7ab983d4207d5ab37348cb014de5c4851bd2) perf(db): remove unused indexes and add updatedOn to request reordering queries - [`0b23ed4`](https://github.com/hoppscotch/hoppscotch/commit/0b23ed41c390efe433be6a7c304ae9e0f18887b3) fix(backend): address review comments and refine db query optimizations - [`df0e81e`](https://github.com/hoppscotch/hoppscotch/commit/df0e81ef20f7ebe8f08583b3a8484d2cbf39d62b) perf(backend): add functional email index and fix raw SQL usage in orderIndex updates - [`75d64ee`](https://github.com/hoppscotch/hoppscotch/commit/75d64eeb7162b1339fd3073b92cc282122711b6d) refactor(user-collection): improve buildUserCollectionTree calls and add comments - [`35ed66a`](https://github.com/hoppscotch/hoppscotch/commit/35ed66a3bf26fdecacd5df4655870dd1a943e96c) perf(backend): add comments and fix null assertion for better code clarity - [`28a31e3`](https://github.com/hoppscotch/hoppscotch/commit/28a31e3d5ff0a13a6bcaaaef2f2e6b36c71fac1f) perf(db): add indexes and optimize batch update queries with safer SQL cases - [`004021b`](https://github.com/hoppscotch/hoppscotch/commit/004021b40afd4dd293049065602a5792ad75286a) refactor(admin,user-collection): improve type safety by explicit mappings ### 📊 Changes **18 files changed** (+463 additions, -457 deletions) <details> <summary>View changed files</summary> ➕ `packages/hoppscotch-backend/prisma/migrations/20260316060000_add_performance_indexes/migration.sql` (+48 -0) 📝 `packages/hoppscotch-backend/prisma/schema.prisma` (+20 -0) 📝 `packages/hoppscotch-backend/src/admin/admin.service.spec.ts` (+5 -27) 📝 `packages/hoppscotch-backend/src/admin/admin.service.ts` (+28 -27) 📝 `packages/hoppscotch-backend/src/mock-server/mock-server.service.ts` (+34 -24) 📝 `packages/hoppscotch-backend/src/shortcode/shortcode.service.spec.ts` (+6 -6) 📝 `packages/hoppscotch-backend/src/shortcode/shortcode.service.ts` (+1 -1) 📝 `packages/hoppscotch-backend/src/team-collection/team-collection.service.ts` (+64 -59) 📝 `packages/hoppscotch-backend/src/team-request/team-request.service.spec.ts` (+13 -10) 📝 `packages/hoppscotch-backend/src/team-request/team-request.service.ts` (+14 -8) 📝 `packages/hoppscotch-backend/src/team/team.service.spec.ts` (+14 -21) 📝 `packages/hoppscotch-backend/src/team/team.service.ts` (+58 -95) 📝 `packages/hoppscotch-backend/src/user-collection/user-collection.service.ts` (+108 -94) 📝 `packages/hoppscotch-backend/src/user-history/user-history.service.spec.ts` (+3 -3) 📝 `packages/hoppscotch-backend/src/user-history/user-history.service.ts` (+2 -4) 📝 `packages/hoppscotch-backend/src/user-request/user-request.service.ts` (+12 -7) 📝 `packages/hoppscotch-backend/src/user/user.service.ts` (+18 -17) 📝 `pnpm-lock.yaml` (+15 -54) </details> ### 📄 Description ## Summary Optimizes database performance across the Hoppscotch backend by adding missing indexes, eliminating N+1 query patterns, and reducing round-trips for batch operations. ## Changes ### Database Indexes (12 new indexes) - **`TeamMember(userUid)`** — listing teams for a user - **`TeamRequest(collectionID)`** — listing requests in a collection - **`TeamEnvironment(teamID)`** — listing environments in a team - **`UserHistory(userUid, reqType, executedOn DESC)`** — paginated history with ordering - **`UserEnvironment(userUid)`** — listing user environments - **`UserRequest(collectionID)`** — listing requests in a collection - **`PersonalAccessToken(userUid)`** — listing user tokens - **`Shortcode(creatorUid)`** — listing user's shortcodes - **`MockServer(workspaceType, workspaceID, deletedAt)`** — filtered mock server listing - **`MockServer(creatorUid, deletedAt)`** — user's mock servers - **`Account(userId)`** — user's provider accounts - **`VerificationToken(userUid)`** — user's verification tokens ### Query Optimizations - **Admin service** — Replace loading ALL user emails into memory + `IN` clause with `NOT EXISTS` subquery for `fetchInvitedUsers` - **Team/User collection export** — Replace recursive per-collection queries with 2 bulk queries + in-memory tree building (O(D) queries instead of O(B^D)) - **Mock server** — Change recursive collection traversal to breadth-first with batched queries per level - **Team/User request sorting** — Replace N individual `UPDATE` queries with single `CASE WHEN` statement; preserve `updatedOn` timestamps - **Team service** — Remove redundant `deleteMany` before `team.delete` (cascade handles it); batch user lookups via `findUsersByIds`; single `groupBy` for sole-owner check; simplified cursor-based pagination - **User service** — Single-pass member counting instead of 4 separate `.filter()` calls for workspace stats - **Shortcode/History** — `findFirst` → `findUnique` for primary key lookups - **Team request search** — Add `mode: 'insensitive'` for case-insensitive search ### Test Updates - Updated test mocks across 5 spec files to match new query patterns ## Test plan - [x] All 685 existing tests pass (18 suites) - [ ] Verify index creation on staging database - [ ] Load test collection export with deep hierarchies to confirm no N+1 regressions --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>