Database & ORM

Use parameterized queries or ORM abstractions

Avoid raw string interpolation when building queries. Always use parameterized queries or trusted ORM query builders (like Prisma, Knex, Sequelize) to prevent SQL/NoSQL injection.

const query = `SELECT * FROM users WHERE email = '${email}'`;

const user = await db.user.findUnique({ where: { email } });

Centralize database access logic

Avoid scattered DB calls across components or business logic. Instead, group queries and transactions in reusable services or repositories to promote testability, encapsulation, and single responsibility.

• UserService.getByEmail(email)

• AuthRepository.saveToken(token)

Normalize data when appropriate

Avoid unnecessary duplication of data unless denormalization is deliberate for performance. Proper normalization makes updates safer and avoids inconsistencies. Use relations and joins instead of duplicating related fields in multiple tables.

Note: Denormalization can be used in specific read-heavy use cases (e.g., analytics), but should be well-documented and intentional.

Use migrations for schema changes

Always track schema changes through version-controlled migrations instead of modifying the database manually. This guarantees consistency across environments (dev, staging, prod) and simplifies rollbacks.

Tools:

• Prisma Migrate

• Knex.js Migrations

• Sequelize CLI

• TypeORM Migrations

npx prisma migrate dev --name add_user_profile

Wrap related operations in transactions

When multiple queries must succeed or fail together (e.g., creating user + profile), wrap them in a transaction to maintain consistency and avoid partial writes.

1await prisma.$transaction([
2  prisma.user.create({ data: ... }),
3  prisma.profile.create({ data: ... }),
4]);

Be careful with lazy loading

Lazy loading can cause unintended N+1 query problems if not used carefully. Always assess whether eager loading is more appropriate for performance and readability, especially in loops or nested object access.

N+1 Problem

1const posts = await prisma.post.findMany();
2for (const post of posts) {
3  const author = await prisma.user.findUnique({ where: { id: post.authorId } });
4}

Eager Loading

1const posts = await prisma.post.findMany({
2  include: { author: true }
3});

Prefer eager loading for known relations

When you know you'll need related data, fetch it eagerly in a single query. It improves performance and makes the code easier to follow, especially for APIs and rendering views.

Use Cases:

• Fetching post + author

• Order + line items

• User + roles

Validate incoming data before persisting

Never trust incoming data to be well-formed or safe. Always validate or sanitize input before persisting it to the database, even if the front end already performs validation.

• Use zod, yup, class-validator, etc.

• Add model-level constraints when supported (e.g. Prisma schema, DB constraints)

1const schema = z.object({
2  email: z.string().email(),
3  age: z.number().int().min(0),
4});
5schema.parse(req.body);

Prefer soft deletes for important data

Instead of permanently deleting important records, use a deletedAt timestamp or a isDeleted flag. This approach allows data recovery, audit trails, and safer business logic.

1model User {
2  id        Int       @id @default(autoincrement())
3  email     String    @unique
4  deletedAt DateTime? // null = active
5}

Always use pagination for large queries

Never return unbounded datasets in production. Always use pagination (offset/limit, cursor, or seek) to reduce server load, bandwidth usage, and improve performance.

1const users = await prisma.user.findMany({
2  take: 20,
3  skip: 0,
4  orderBy: { createdAt: "desc" }
5});

Index frequently queried columns

Use indexes on columns that are frequently used in WHERE, ORDER BY, or JOIN clauses. It significantly speeds up query execution time, especially in large datasets.

Avoid over-indexing — every index slows down INSERT/UPDATE/DELETE.

1model Post {
2  title     String
3  createdAt DateTime @default(now())
4  @@index([createdAt])
5}

Limit select fields when possible

Avoid selecting all columns unless necessary (SELECT *). Fetch only the required fields to reduce memory usage, improve performance, and protect sensitive data from accidental exposure.

1const user = await prisma.user.findUnique({
2  where: { id },
3  select: { id: true, email: true }
4});

Enforce uniqueness at the DB level

Application-level checks aren’t enough — always enforce uniqueness at the schema or database level. Use UNIQUE constraints or equivalent ORM features to prevent race conditions and inconsistent states.

1model User {
2  email String @unique
3}

Use aggregations carefully

Aggregation queries (e.g. COUNT, SUM, AVG) can be expensive — especially when used on large, unindexed datasets or in real-time APIs. Cache results or use background jobs where appropriate.

Good Practice:

• Index grouped fields

• Avoid aggregating frequently on write-heavy tables

• Cache analytics where possible

Apply role-based access at the query layer

Don’t rely solely on frontend or route-level checks. Enforce permissions directly in the data access layer to prevent unauthorized data exposure — especially in multi-tenant applications or shared-resource models.

1// Express + Prisma
2async function getInvoicesForUser(req, res) {
3  const { user } = req;
4
5  const whereClause =
6    user.role === "admin"
7      ? {}
8      : { organizationId: user.organizationId };
9
10  const invoices = await prisma.invoice.findMany({
11    where: whereClause,
12  });
13
14  res.json(invoices);
15}

This ensures that even if a user bypasses the frontend, they only get data allowed by their role or scope.