Part 5: Advanced Querying, Aggregations, and Production Best Practices

Introduction

We've covered the fundamentals of Prisma ORM, but production applications require more sophisticated techniques. In this final part, I'll share advanced patterns I've learned from building and scaling production systems with TypeScript, Prisma, and PostgreSQL.

These aren't theoretical examples—every technique here comes from real challenges I've faced: optimizing slow queries, implementing search functionality, handling complex aggregations, and ensuring production reliability.

Advanced Filtering and Querying

Complex Nested Filters

In a content management system I built, I needed to find posts with specific criteria across multiple relationships:

// Find published posts by active authors who have verified emails
// and posts must have at least 2 approved comments with tags 'typescript' or 'database'
const posts = await prisma.post.findMany({
  where: {
    published: true,
    author: {
      isActive: true,
      emailVerified: true,
      role: {
        in: ['ADMIN', 'EDITOR']
      }
    },
    comments: {
      some: {
        approved: true,
        content: {
          contains: 'great',
          mode: 'insensitive'
        }
      }
    },
    tags: {
      some: {
        slug: {
          in: ['typescript', 'database']
        }
      }
    },
    viewCount: {
      gte: 100
    }
  },
  include: {
    author: {
      select: {
        id: true,
        name: true,
        email: true
      }
    },
    tags: true,
    _count: {
      select: {
        comments: true
      }
    }
  },
  orderBy: [
    { viewCount: 'desc' },
    { createdAt: 'desc' }
  ]
});

Using NOT, AND, OR

Combining logical operators for complex business logic:

// Find posts that are either:
// 1. Published and trending (high view count)
// 2. Featured by admins
// But NOT marked as archived
const posts = await prisma.post.findMany({
  where: {
    NOT: {
      status: 'ARCHIVED'
    },
    OR: [
      {
        AND: [
          { published: true },
          { viewCount: { gte: 1000 } },
          { 
            createdAt: { 
              gte: new Date(Date.now() - 7 * 24 * 60 * 60 * 1000) 
            } 
          }
        ]
      },
      {
        featured: true,
        author: {
          role: 'ADMIN'
        }
      }
    ]
  }
});

JSON Field Filtering (PostgreSQL)

Prisma supports PostgreSQL's JSON operations:

model User {
  id       Int    @id @default(autoincrement())
  email    String @unique
  metadata Json?  // JSON field for flexible data
}

// Store flexible metadata
await prisma.user.create({
  data: {
    email: '[email protected]',
    metadata: {
      preferences: {
        theme: 'dark',
        notifications: true
      },
      location: {
        country: 'US',
        timezone: 'America/New_York'
      }
    }
  }
});

// Query JSON fields
const usersInUS = await prisma.user.findMany({
  where: {
    metadata: {
      path: ['location', 'country'],
      equals: 'US'
    }
  }
});

Aggregations and Grouping

Count, Sum, Average, Min, Max

Real example from an analytics dashboard I built:

// Get statistics about posts
const postStats = await prisma.post.aggregate({
  _count: {
    id: true
  },
  _avg: {
    viewCount: true
  },
  _sum: {
    viewCount: true
  },
  _max: {
    viewCount: true,
    createdAt: true
  },
  _min: {
    createdAt: true
  },
  where: {
    published: true
  }
});

console.log('Post Statistics:', {
  totalPosts: postStats._count.id,
  averageViews: Math.round(postStats._avg.viewCount || 0),
  totalViews: postStats._sum.viewCount,
  mostViews: postStats._max.viewCount,
  oldestPost: postStats._min.createdAt,
  newestPost: postStats._max.createdAt
});

Group By

Prisma's groupBy for aggregating data:

// Group posts by author and count
const postsByAuthor = await prisma.post.groupBy({
  by: ['authorId'],
  _count: {
    id: true
  },
  _avg: {
    viewCount: true
  },
  having: {
    id: {
      _count: {
        gt: 5  // Authors with more than 5 posts
      }
    }
  },
  orderBy: {
    _count: {
      id: 'desc'
    }
  }
});

// Enrich with author details
const enrichedData = await Promise.all(
  postsByAuthor.map(async (item) => {
    const author = await prisma.user.findUnique({
      where: { id: item.authorId },
      select: { id: true, name: true, email: true }
    });
    
    return {
      author,
      postCount: item._count.id,
      avgViews: Math.round(item._avg.viewCount || 0)
    };
  })
);

Monthly Statistics

Grouping by date parts using raw queries:

interface MonthlyStats {
  year: number;
  month: number;
  post_count: bigint;
  total_views: bigint;
}

const monthlyStats = await prisma.$queryRaw<MonthlyStats[]>`
  SELECT 
    EXTRACT(YEAR FROM created_at)::int as year,
    EXTRACT(MONTH FROM created_at)::int as month,
    COUNT(*)::bigint as post_count,
    SUM(view_count)::bigint as total_views
  FROM "Post"
  WHERE published = true
  GROUP BY year, month
  ORDER BY year DESC, month DESC
  LIMIT 12
`;

// Convert BigInt to Number for JSON serialization
const formattedStats = monthlyStats.map(stat => ({
  year: stat.year,
  month: stat.month,
  postCount: Number(stat.post_count),
  totalViews: Number(stat.total_views)
}));

Full-Text Search

PostgreSQL Full-Text Search

For a knowledge base I built, I implemented search using PostgreSQL's capabilities:

// Using contains (case-insensitive)
const posts = await prisma.post.findMany({
  where: {
    OR: [
      {
        title: {
          contains: searchTerm,
          mode: 'insensitive'
        }
      },
      {
        content: {
          contains: searchTerm,
          mode: 'insensitive'
        }
      }
    ]
  }
});

Advanced Full-Text Search with Raw SQL

For better performance and features:

model Post {
  id              Int      @id @default(autoincrement())
  title           String
  content         String
  searchVector    Unsupported("tsvector")?  // PostgreSQL text search
  
  @@index([searchVector], type: Gin)
}

Create a migration to set up full-text search:

-- Add tsvector column
ALTER TABLE "Post" ADD COLUMN search_vector tsvector;

-- Create index for fast searching
CREATE INDEX post_search_idx ON "Post" USING GIN (search_vector);

-- Create trigger to auto-update search_vector
CREATE FUNCTION post_search_vector_update() RETURNS trigger AS $$
BEGIN
  NEW.search_vector := 
    setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(NEW.content, '')), 'B');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER post_search_vector_trigger
BEFORE INSERT OR UPDATE ON "Post"
FOR EACH ROW EXECUTE FUNCTION post_search_vector_update();

Use it:

interface SearchResult {
  id: number;
  title: string;
  excerpt: string;
  rank: number;
}

const searchResults = await prisma.$queryRaw<SearchResult[]>`
  SELECT 
    id,
    title,
    LEFT(content, 200) as excerpt,
    ts_rank(search_vector, plainto_tsquery('english', ${searchTerm})) as rank
  FROM "Post"
  WHERE search_vector @@ plainto_tsquery('english', ${searchTerm})
  ORDER BY rank DESC
  LIMIT 20
`;

Raw SQL Queries

When to Use Raw SQL

I use raw SQL for:

1. Complex analytical queries

2. Database-specific features

3. Performance-critical operations

4. Legacy database integration

Typed Raw Queries

// Define return type
interface UserPostCount {
  user_id: number;
  user_name: string;
  post_count: bigint;
  latest_post_date: Date;
}

const results = await prisma.$queryRaw<UserPostCount[]>`
  SELECT 
    u.id as user_id,
    u.name as user_name,
    COUNT(p.id)::bigint as post_count,
    MAX(p.created_at) as latest_post_date
  FROM "User" u
  LEFT JOIN "Post" p ON u.id = p.author_id
  GROUP BY u.id, u.name
  HAVING COUNT(p.id) > 0
  ORDER BY post_count DESC
`;

Safe Parameterization

// ✅ Safe - using template literals with Prisma.sql
const email = '[email protected]';
const users = await prisma.$queryRaw`
  SELECT * FROM "User"
  WHERE email = ${email}
`;

// ❌ NEVER - vulnerable to SQL injection
const unsafeEmail = userInput;
const users = await prisma.$queryRaw(`
  SELECT * FROM "User" WHERE email = '${unsafeEmail}'
`);

Execute Raw (for non-SELECT queries)

// Update with raw SQL
const result = await prisma.$executeRaw`
  UPDATE "Post"
  SET view_count = view_count + 1
  WHERE id = ${postId}
`;

console.log(`Updated ${result} rows`);

Optimistic Concurrency Control

Preventing race conditions with version fields:

model Post {
  id      Int    @id @default(autoincrement())
  title   String
  version Int    @default(0)
}

async function updatePostWithOptimisticLock(
  postId: number, 
  currentVersion: number,
  updates: { title: string }
) {
  try {
    const updatedPost = await prisma.post.updateMany({
      where: {
        id: postId,
        version: currentVersion  // Only update if version matches
      },
      data: {
        ...updates,
        version: {
          increment: 1  // Increment version
        }
      }
    });
    
    if (updatedPost.count === 0) {
      throw new Error('Post was modified by another user. Please refresh and try again.');
    }
    
    return updatedPost;
  } catch (error) {
    throw error;
  }
}

Soft Deletes

Instead of actually deleting records, mark them as deleted:

model Post {
  id        Int       @id @default(autoincrement())
  title     String
  deletedAt DateTime?
  
  @@index([deletedAt])
}

// Soft delete
async function softDelete(postId: number) {
  return await prisma.post.update({
    where: { id: postId },
    data: { deletedAt: new Date() }
  });
}

// Query only active records
const activePosts = await prisma.post.findMany({
  where: {
    deletedAt: null
  }
});

// Restore soft-deleted record
async function restore(postId: number) {
  return await prisma.post.update({
    where: { id: postId },
    data: { deletedAt: null }
  });
}

Connection Pooling and Performance

Database Connection Best Practices

// lib/prisma.ts - Production setup
import { PrismaClient } from '@prisma/client';

const globalForPrisma = global as unknown as { prisma: PrismaClient };

export const prisma =
  globalForPrisma.prisma ||
  new PrismaClient({
    log: process.env.NODE_ENV === 'development' 
      ? ['query', 'error', 'warn'] 
      : ['error'],
    datasources: {
      db: {
        url: process.env.DATABASE_URL,
      },
    },
  });

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;

// Graceful shutdown
async function shutdown() {
  await prisma.$disconnect();
  process.exit(0);
}

process.on('SIGINT', shutdown);
process.on('SIGTERM', shutdown);

Connection Pool Configuration

# .env
# Format: postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=SCHEMA&connection_limit=LIMIT
DATABASE_URL="postgresql://user:pass@localhost:5432/mydb?schema=public&connection_limit=10&pool_timeout=20"

Query Optimization

// ❌ N+1 Query Problem
const users = await prisma.user.findMany();
for (const user of users) {
  user.posts = await prisma.post.findMany({
    where: { authorId: user.id }
  });
}

// ✅ Solved with include
const users = await prisma.user.findMany({
  include: {
    posts: true
  }
});

// ✅ Or select only needed fields
const users = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    posts: {
      select: {
        id: true,
        title: true
      }
    }
  }
});

Data Seeding

Production-like seed script I use:

// prisma/seed.ts
import { PrismaClient } from '@prisma/client';
import * as bcrypt from 'bcrypt';

const prisma = new PrismaClient();

async function main() {
  console.log('🌱 Starting seed...');
  
  // Create users
  const users = await Promise.all([
    prisma.user.create({
      data: {
        email: '[email protected]',
        name: 'Admin User',
        password: await bcrypt.hash('admin123', 10),
        role: 'ADMIN',
        profile: {
          create: {
            bio: 'Platform administrator',
            avatar: 'https://i.pravatar.cc/150?img=1'
          }
        }
      }
    }),
    prisma.user.create({
      data: {
        email: '[email protected]',
        name: 'Hassan',
        password: await bcrypt.hash('password123', 10),
        role: 'USER',
        profile: {
          create: {
            bio: 'Backend developer passionate about TypeScript',
            avatar: 'https://i.pravatar.cc/150?img=2'
          }
        }
      }
    })
  ]);
  
  // Create tags
  const tags = await Promise.all([
    prisma.tag.create({ data: { name: 'TypeScript', slug: 'typescript' } }),
    prisma.tag.create({ data: { name: 'Database', slug: 'database' } }),
    prisma.tag.create({ data: { name: 'Prisma', slug: 'prisma' } })
  ]);
  
  // Create posts
  await Promise.all(
    users.flatMap((user, userIndex) => 
      Array.from({ length: 5 }).map((_, postIndex) =>
        prisma.post.create({
          data: {
            title: `Post ${postIndex + 1} by ${user.name}`,
            slug: `post-${userIndex}-${postIndex}`,
            content: 'Lorem ipsum dolor sit amet...',
            published: Math.random() > 0.3,
            authorId: user.id,
            viewCount: Math.floor(Math.random() * 1000),
            tags: {
              connect: [
                { id: tags[Math.floor(Math.random() * tags.length)].id }
              ]
            }
          }
        })
      )
    )
  );
  
  console.log('✅ Seed completed!');
}

main()
  .catch((e) => {
    console.error('❌ Seed failed:', e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

Add to package.json:

{
  "prisma": {
    "seed": "ts-node prisma/seed.ts"
  }
}

Run seed:

npx prisma db seed

Middleware and Logging

Query Logging Middleware

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

// Log slow queries
prisma.$use(async (params, next) => {
  const before = Date.now();
  const result = await next(params);
  const after = Date.now();
  const duration = after - before;
  
  if (duration > 100) {  // Log queries taking > 100ms
    console.warn(`Slow query detected: ${params.model}.${params.action} took ${duration}ms`);
  }
  
  return result;
});

Soft Delete Middleware

prisma.$use(async (params, next) => {
  // Automatically filter out soft-deleted records
  if (params.action === 'findUnique' || params.action === 'findFirst') {
    params.action = 'findFirst';
    params.args.where = {
      ...params.args.where,
      deletedAt: null
    };
  }
  
  if (params.action === 'findMany') {
    if (params.args.where) {
      if (!params.args.where.deletedAt) {
        params.args.where.deletedAt = null;
      }
    } else {
      params.args.where = { deletedAt: null };
    }
  }
  
  return next(params);
});

Production Deployment Checklist

Based on my production deployments:

1. Environment Variables

# Never commit this to Git
DATABASE_URL="postgresql://user:password@host:5432/db?schema=public&connection_limit=10"
NODE_ENV="production"

2. Migration Strategy

# Run migrations in production
npx prisma migrate deploy

# Generate Prisma Client
npx prisma generate

3. Health Check Endpoint

// src/health.ts
import { prisma } from './lib/prisma';

export async function healthCheck() {
  try {
    await prisma.$queryRaw`SELECT 1`;
    return { status: 'healthy', database: 'connected' };
  } catch (error) {
    return { status: 'unhealthy', database: 'disconnected' };
  }
}

4. Error Monitoring

import * as Sentry from '@sentry/node';

try {
  await prisma.user.create({ data: userData });
} catch (error) {
  Sentry.captureException(error);
  throw error;
}

5. Backup Strategy

# Automated daily backups
pg_dump $DATABASE_URL > backup_$(date +%Y%m%d).sql

# Restore from backup
psql $DATABASE_URL < backup_20240204.sql

Conclusion

This series covered everything from ORM fundamentals to production deployment. The techniques I've shared come from years of building and maintaining applications with Prisma, TypeScript, and PostgreSQL.

Key takeaways:

• ORMs like Prisma provide type safety and developer productivity

• Schema design impacts long-term maintainability

• Migrations enable safe schema evolution

• Advanced features like aggregations and full-text search unlock powerful capabilities

• Production requires careful attention to performance, monitoring, and error handling

The stack we've explored—TypeScript, Prisma, and PostgreSQL—powers applications I maintain that handle millions of requests monthly. It's production-proven, developer-friendly, and continues to improve.

Resources

• Prisma Documentation

• PostgreSQL Documentation

• TypeScript Handbook

---

This concludes the ORM 101 series. All examples are based on real projects I've built and deployed to production. I hope this series helps you build better database-backed applications.