Prisma Orm

2. Singleton Pattern for Remix

In development, Remix reloads the server, which can exhaust database connections if you create a new PrismaClient on every reload. Use the singleton pattern.

// app/db.server.ts
import { PrismaClient } from "@prisma/client";

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

export const prisma = globalForPrisma.prisma || new PrismaClient();

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

3. Schema Design Best Practices

Use BigInt for Shopify IDs

Shopify IDs (Product ID, Order ID) exceed 32-bit integers.

• BAD: Int
• GOOD: BigInt (or String if you don't need math operations)

model Product {
  id          BigInt   @id // Matches Shopify's ID
  shop        String
  title       String
  // ...
  @@index([shop]) // Always index by shop for multi-tenancy
}

Multi-tenancy

Every query MUST filter by shop. This is non-negotiable for security.

• Tip: Use Prisma Middleware or Extensions to enforce this, or just be disciplined in your Service layer.

4. Migrations Workflow

NEVER use prisma db push in production.

• Development:

  npx prisma migrate dev --name init_tables
  

• Production (CI/CD):

  npx prisma migrate deploy
  

5. Performance Optimization

Select Specific Fields

Don't use findMany without select if you only need a few fields.

// BAD: Fetches massive JSON blobs
const products = await prisma.product.findMany();

// GOOD
const products = await prisma.product.findMany({
  select: { id: true, title: true }
});

Transactions

Use $transaction for dependent writes (e.g., creating an Order and its LineItems).

await prisma.$transaction(async (tx) => {
  const order = await tx.order.create({ ... });
  await tx.lineItem.create({ ... });
});

Connection Pooling

For serverless or high-concurrency environments (like Remix on Vercel/Fly), use a connection pooler (Supabase Pooler, PgBouncer, or Prisma Accelerate).

6. Common Recipes

Upsert (Create or Update)

Perfect for syncing data from Webhooks.

await prisma.user.upsert({
  where: { id: 1 },
  update: { email: "[email protected]" },
  create: { id: 1, email: "[email protected]" },
});