Database Best Practices Skill

Purpose

Provides best practices and patterns for database operations in Shopify apps using Prisma ORM, focusing on data isolation, query optimization, and safe migrations.

When This Skill Activates

• Working with Prisma schema or queries

• Creating database migrations

• Optimizing database performance

• Implementing multi-tenant data isolation

• Handling transactions

Critical: Multi-Tenant Data Isolation

ALWAYS filter by shopId - This prevents data leaks between shops.

// ✅ CORRECT - Always include shopId const products = await db.product.findMany({ where: { shopId: shop.id, status: "active", }, });

// ❌ WRONG - Missing shopId (data leak!) const products = await db.product.findMany({ where: { status: "active" }, });

Core Patterns

1. Safe Query Pattern

// Always filter by shopId for shop-specific data async function getShopProducts(shopId: string) { return db.product.findMany({ where: { shopId }, select: { id: true, title: true, vendor: true, // Only select needed fields }, take: 50, orderBy: { createdAt: "desc" }, }); }

2. Pagination Pattern

async function getPaginatedProducts(shopId: string, page: number = 1) { const pageSize = 50; const skip = (page - 1) * pageSize;

const [products, totalCount] = await Promise.all([ db.product.findMany({ where: { shopId }, skip, take: pageSize, orderBy: { createdAt: "desc" }, }), db.product.count({ where: { shopId }, }), ]);

return { products, totalCount, totalPages: Math.ceil(totalCount / pageSize), currentPage: page, }; }

3. Transaction Pattern

// Use transactions for operations that must succeed/fail together await db.$transaction(async (tx) => { // Update product await tx.product.update({ where: { id: productId }, data: { status: "synced" }, });

// Create audit log await tx.auditLog.create({ data: { shopId: shop.id, entityType: "product", entityId: productId, action: "update", changesSummary: "Product synced", }, }); });

4. Upsert Pattern

// Create or update based on existence await db.product.upsert({ where: { shopId_productId: { shopId: shop.id, productId: shopifyProductId, }, }, create: { shopId: shop.id, productId: shopifyProductId, title: "Product Title", }, update: { title: "Updated Title", updatedAt: new Date(), }, });

5. Bulk Operations

// Use createMany for bulk inserts await db.product.createMany({ data: products.map(p => ({ shopId: shop.id, productId: p.id, title: p.title, })), skipDuplicates: true, // Skip if unique constraint violated });

// Use updateMany for bulk updates await db.product.updateMany({ where: { shopId: shop.id, status: "pending", }, data: { status: "processed", updatedAt: new Date(), }, });

6. JSON Field Handling

// Store complex data as JSON const metadata = { tags: ["summer", "sale"], featured: true };

await db.product.create({ data: { shopId: shop.id, title: "Product", metadata: JSON.stringify(metadata), }, });

// Retrieve and parse JSON const product = await db.product.findUnique({ where: { id: productId }, });

const metadata = JSON.parse(product.metadata || "{}");

7. Error Handling

// Handle unique constraint violations try { await db.product.create({ data: { shopId, productId, title }, }); } catch (error) { if (error.code === "P2002") { // Unique constraint violation - update instead await db.product.update({ where: { shopId_productId: { shopId, productId } }, data: { title, updatedAt: new Date() }, }); } else { throw error; } }

8. N+1 Query Prevention

// ❌ BAD: N+1 query problem const products = await db.product.findMany(); for (const product of products) { const shop = await db.shop.findUnique({ where: { id: product.shopId }, }); console.log(shop.shopDomain); }

// ✅ GOOD: Use include const products = await db.product.findMany({ include: { shop: { select: { shopDomain: true, }, }, }, });

Schema Design Patterns

Multi-Tenant Schema

// Base Shop model - required model Shop { id String @id @default(cuid()) shopDomain String @unique accessToken String installedAt DateTime @default(now())

// All shop-specific data references this products Product[] orders Order[] }

// Shop-specific model model Product { id String @id @default(cuid()) shopId String shop Shop @relation(fields: [shopId], references: [id], onDelete: Cascade) productId String // Shopify product ID title String createdAt DateTime @default(now()) updatedAt DateTime @updatedAt

@@unique([shopId, productId]) @@index([shopId]) }

Common Indexes

// Essential indexes for performance @@index([shopId]) // Filter by shop @@index([shopId, status]) // Shop + status filter @@index([createdAt]) // Time-based queries @@index([updatedAt]) // Recently updated @@unique([shopId, externalId]) // Prevent duplicates per shop

Migration Best Practices

1. Safe Migrations

Development - generates migration

npx prisma migrate dev --name add_vendor_field

Production - applies migrations

npx prisma migrate deploy

2. Adding Fields Safely

// Step 1: Add as optional model Product { vendor String? // Optional first }

// Step 2: Backfill data // Run a script to populate vendor for existing records

// Step 3: Make required (in next migration) model Product { vendor String // Required after backfill }

3. Data Migration Script

// scripts/backfill-vendor.ts async function backfillVendor() { const products = await db.product.findMany({ where: { vendor: null }, });

for (const product of products) { await db.product.update({ where: { id: product.id }, data: { vendor: extractVendorFromTitle(product.title), }, }); }

console.log(Updated ${products.length} products); }

Performance Optimization

1. Select Only Needed Fields

// ❌ Inefficient - fetches all fields const products = await db.product.findMany();

// ✅ Efficient - only needed fields const products = await db.product.findMany({ select: { id: true, title: true, status: true, }, });

2. Use Appropriate Indexes

// Index frequently queried fields @@index([shopId, status]) // Composite index for common query @@index([createdAt]) // Time-based sorting @@index([vendor]) // Filtering by vendor

3. Batch Operations

// Process in batches to avoid memory issues const batchSize = 100; let skip = 0;

while (true) { const batch = await db.product.findMany({ where: { shopId: shop.id }, skip, take: batchSize, });

if (batch.length === 0) break;

await processBatch(batch); skip += batchSize; }

Common Prisma Errors

P2002 - Unique Constraint Violation

// Handle gracefully with upsert await db.product.upsert({ where: { shopId_productId: { shopId, productId } }, create: { shopId, productId, title }, update: { title, updatedAt: new Date() }, });

P2025 - Record Not Found

// Check existence first or use try/catch const product = await db.product.findUnique({ where: { id: productId }, });

if (!product) { throw new Response("Product not found", { status: 404 }); }

Best Practices Checklist

• Always filter shop-specific queries by shopId

• Use transactions for multi-step operations

• Select only needed fields in queries

• Add indexes for common query patterns

• Handle unique constraint violations gracefully

• Use upsert for create-or-update logic

• Validate JSON data before storing

• Test migrations in development first

• Use cascade delete appropriately

• Monitor query performance

Remember: Proper database practices prevent data leaks, improve performance, and ensure data integrity.