Database Architecture

Y3NKO uses PostgreSQL via Supabase with Prisma as the ORM.

Connection Setup

Prisma Client

// lib/db.ts
import { PrismaClient } from '@prisma/client'
 
const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined
}
 
export const prisma = globalForPrisma.prisma ?? new PrismaClient()
 
if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma
}

The singleton pattern prevents multiple Prisma Client instances during hot reload in development.

Connection Pooling

Supabase requires two connection strings:

# Pooled connection (port 6543) - for queries
DATABASE_URL="postgresql://...@aws-0-region.pooler.supabase.com:6543/postgres?pgbouncer=true"

# Direct connection (port 5432) - for migrations
DIRECT_URL="postgresql://...@aws-0-region.pooler.supabase.com:5432/postgres"

Configured in schema.prisma:

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
}

Query Patterns

Basic Queries

// Find all published listings
const listings = await prisma.listing.findMany({
  where: { status: 'PUBLISHED' }
})
 
// Find one listing by slug
const listing = await prisma.listing.findUnique({
  where: { slug: 'beach-villa-accra' }
})
 
// Find with relations
const listing = await prisma.listing.findUnique({
  where: { slug },
  include: {
    images: true,
    amenities: {
      include: { amenity: true }
    }
  }
})

Filtering and Pagination

const { listings, total } = await prisma.$transaction([
  prisma.listing.findMany({
    where: {
      type: 'ACCOMMODATION',
      status: 'PUBLISHED',
      region: region ?? undefined,
      priceGHS: {
        gte: minPrice,
        lte: maxPrice
      }
    },
    orderBy: { createdAt: 'desc' },
    skip: (page - 1) * limit,
    take: limit
  }),
  prisma.listing.count({ where: { /* same filters */ } })
])

Creating Records

const booking = await prisma.booking.create({
  data: {
    reference: generateReference('Y3K'),
    userId: user.id,
    listingId: listing.id,
    checkIn: new Date(checkIn),
    checkOut: new Date(checkOut),
    nights: calculateNights(checkIn, checkOut),
    adults,
    children,
    pricePerNight: listing.priceGHS,
    subtotal,
    serviceFee,
    taxes,
    total,
    guestName,
    guestEmail,
    guestPhone
  }
})

Updating Records

const updated = await prisma.listing.update({
  where: { id: listingId },
  data: {
    title,
    description,
    priceGHS
  }
})

Transactions

const [booking, payment] = await prisma.$transaction(async (tx) => {
  const booking = await tx.booking.create({ data: bookingData })
 
  const payment = await tx.payment.create({
    data: {
      bookingId: booking.id,
      amount: booking.total,
      status: 'PENDING'
    }
  })
 
  return [booking, payment]
})

Type Safety

Prisma generates TypeScript types from your schema:

import { Listing, Booking, User } from '@prisma/client'
 
// Full type
type ListingWithImages = Listing & {
  images: ListingImage[]
}
 
// Or use Prisma's utility types
import { Prisma } from '@prisma/client'
 
type ListingWithImages = Prisma.ListingGetPayload<{
  include: { images: true }
}>

Migrations

Creating Migrations

# After modifying schema.prisma
npx prisma migrate dev --name add_reviews_table

Applying in Production

npx prisma migrate deploy

Reset Database (Development Only)

npx prisma migrate reset

⚠️

migrate reset drops all data. Never run in production.

Seeding

// prisma/seed.ts
import { prisma } from '../lib/db'
 
async function main() {
  // Create amenities
  await prisma.amenity.createMany({
    data: [
      { name: 'WiFi', icon: 'wifi', category: 'Essentials' },
      { name: 'Air Conditioning', icon: 'snowflake', category: 'Essentials' },
      // ...
    ],
    skipDuplicates: true
  })
 
  // Create test listings
  if (process.env.NODE_ENV === 'development') {
    await prisma.listing.create({
      data: {
        title: 'Beach Villa',
        slug: 'beach-villa-accra',
        // ...
      }
    })
  }
}
 
main()
  .catch((e) => {
    console.error(e)
    process.exit(1)
  })
  .finally(() => prisma.$disconnect())

Run with:

npm run db:seed

Performance Tips

Select Only What You Need

// Bad - fetches all columns
const listings = await prisma.listing.findMany()
 
// Good - fetches only needed columns
const listings = await prisma.listing.findMany({
  select: {
    id: true,
    title: true,
    slug: true,
    priceGHS: true,
    images: {
      select: { url: true },
      where: { isPrimary: true },
      take: 1
    }
  }
})

Avoid N+1 Queries

// Bad - N+1 queries
const bookings = await prisma.booking.findMany()
for (const booking of bookings) {
  booking.listing = await prisma.listing.findUnique({
    where: { id: booking.listingId }
  })
}
 
// Good - single query with include
const bookings = await prisma.booking.findMany({
  include: { listing: true }
})

Use Indexes

Indexes are defined in schema.prisma:

model Listing {
  // ...
 
  @@index([type, status])
  @@index([region, city])
  @@index([slug])
}

Database Browser

Open Prisma Studio for visual database browsing:

npm run db:studio

Opens at http://localhost:5555.

Authentication Routing