Prisma best practices: type-safe database access in production SaaS

Why Prisma matters for SaaS

Prisma provides essential benefits for SaaS applications:

• Type-safe database queries with TypeScript
• Automatic type generation from schema
• Connection pooling for optimal performance
• Migration system for schema versioning
• Query optimization and relation handling

What you get with Fastack

Fastack includes a complete Prisma setup with singleton pattern and type-safe queries:

Prisma client singleton

Fastack uses a singleton pattern to prevent connection exhaustion in development:

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

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

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

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

export type {
  User,
  Account,
  Session,
  VerificationToken,
  Subscription,
} from '@prisma/client';

The singleton pattern ensures that only one Prisma client instance exists in development, preventing connection pool exhaustion. In production, a new instance is created for each serverless function invocation. The client is configured to log queries in development and only errors in production.

Type-safe queries

Fastack uses Prisma's type-safe queries throughout the codebase:

import { redirect } from 'next/navigation';
import { auth } from '@/lib/auth-config';
import { prisma } from '@/lib/db';
import { routing } from '@/i18n/routing';
import { AdminLayout } from './components/admin-layout';

export default async function AdminLayoutRoute({
  children,
  params,
}: {
  children: React.ReactNode;
  params: Promise<{ locale: string }>;
}) {
  const session = await auth();
  const { locale: localeParam } = await params;
  const locale = localeParam || routing.defaultLocale;

  if (!session?.user) {
    const signInPath = locale === routing.defaultLocale ? '/auth/signin' : `/${locale}/auth/signin`;
    redirect(signInPath);
  }

  // Check if user is admin
  const user = await prisma.user.findUnique({
    where: { email: session.user.email! },
    select: { role: true },
  });

  if (!user || user.role !== 'admin') {
    const notFoundPath = locale === routing.defaultLocale ? '/not-found' : `/${locale}/not-found`;
    redirect(notFoundPath);
  }

  return <AdminLayout>{children}</AdminLayout>;
}

Prisma queries are fully type-safe, with TypeScript providing autocomplete and type checking for all database operations. The select option allows you to fetch only the fields you need, improving query performance.

Query optimization

Fastack demonstrates query optimization with selective field fetching:

import { prisma } from '@/lib/db';
// ... other imports

export async function getPurchases(): Promise<PurchaseListItem[]> {
    // ... logger and auth logic
    const purchases = await prisma.purchase.findMany({
        where: {
            userId,
        },
        select: {
            id: true,
            productId: true,
            productName: true,
            amount: true,
            currency: true,
            status: true,
            createdAt: true,
            metadata: true,
        },
        orderBy: {
            createdAt: 'desc',
        },
    });
    // ... rest of the function
}

Using select instead of fetching entire records reduces data transfer and improves query performance. The orderBy clause ensures results are sorted efficiently at the database level.

Database updates

Fastack demonstrates type-safe database updates:

import { prisma } from '@/lib/db';
// ... other imports

async function updateSubscription(
    where: { id: string } | { userId: string },
    data: {
        stripeSubscriptionId: string;
        stripePriceId: string | null;
        status: SubscriptionStatus;
        plan: string | null;
        currentPeriodEnd: Date | null;
        cancelAtPeriodEnd: boolean;
    }
) {
    return prisma.subscription.update({
        where,
        data,
    });
}
// ... rest of the file

Prisma's update method provides type-safe updates with TypeScript ensuring that only valid fields can be updated. The where clause supports multiple unique identifiers, providing flexibility in querying.

Time saved: 15+ hours

Implementing Prisma best practices from scratch typically requires:

• Setting up Prisma client singleton (2-3 hours)
• Configuring database schema (3-4 hours)
• Creating migrations (2-3 hours)
• Implementing type-safe queries (2-3 hours)
• Optimizing query performance (2-3 hours)
• Testing database operations (2-3 hours)
• Documenting database architecture (1-2 hours)

Total: 14-21 hours of development time that you save with Fastack.

Best practices included

Fastack follows Prisma best practices:

• Singleton pattern - Prevents connection exhaustion
• Type-safe queries - Full TypeScript support
• Query optimization - Selective field fetching
• Migration system - Schema versioning
• Production logging - Error-only logging in production

Conclusion

Prisma provides type-safe database access that is essential for production SaaS applications. Fastack includes a complete Prisma setup that saves you weeks of development time while ensuring optimal performance and type safety.

With Fastack, you get:

• Prisma client singleton pattern for connection management
• Type-safe database queries with full TypeScript support
• Query optimization with selective field fetching
• Migration system for schema versioning
• Production-ready database architecture
• Automatic type generation from schema
• Production-ready Prisma setup built-in from day one