Skip to main content

Database Setup

Learn how to set up and configure the dual-database architecture for both development and production environments.

Architecture Overview​

Gully Sports uses a dual-database architecture for optimal performance and feature separation:

CockroachDB (Primary Database)​

  • Purpose: All application data storage
  • Contains: Users, bookings, products, orders, lanes, payments
  • Why: Distributed SQL database with PostgreSQL compatibility
  • Access: Via Prisma ORM

Supabase (Authentication Only)​

  • Purpose: User authentication and session management
  • Contains: Auth tokens, sessions, OAuth providers
  • Why: Built-in auth features, email verification, OAuth integrations
  • Access: Via Supabase client SDK

User Synchronization​

The application automatically syncs user data between both databases:

  • When a user signs up in Supabase → User record created in CockroachDB
  • Supabase user ID is stored in CockroachDB for mapping
  • See User Sync Guide for details

Prerequisites​

Before setting up the database:

  1. CockroachDB Cluster

    • Create a free cluster at cockroachlabs.com
    • Note your connection string
    • Ensure SSL is enabled

  2. Supabase Project

    • Create a project at supabase.com
    • Get your project URL and anon key
    • Get your service role key (for admin operations)

Environment Variables​

Development (.env.local)​

# CockroachDB Database Connection
DATABASE_URL="postgresql://username:password@host:port/database?sslmode=require"
DIRECT_URL="postgresql://username:password@host:port/database?sslmode=require"

# Supabase Authentication
NEXT_PUBLIC_SUPABASE_URL="https://your-project-ref.supabase.co"
NEXT_PUBLIC_SUPABASE_ANON_KEY="your-supabase-anon-key"
SUPABASE_SERVICE_ROLE_KEY="your-service-role-key"
Connection Strings

Both DATABASE_URL and DIRECT_URL are typically the same for CockroachDB. Some platforms require separate URLs for connection pooling.

Production​

For production deployment (Vercel, Netlify, etc.):

# CockroachDB Database Connection (Production)
DATABASE_URL="postgresql://prod-username:prod-password@prod-host:port/database?sslmode=require"
DIRECT_URL="postgresql://prod-username:prod-password@prod-host:port/database?sslmode=require"

# Supabase Authentication (Production)
NEXT_PUBLIC_SUPABASE_URL="https://your-prod-project.supabase.co"
NEXT_PUBLIC_SUPABASE_ANON_KEY="your-prod-anon-key"
SUPABASE_SERVICE_ROLE_KEY="your-prod-service-role-key"
Security
  • Use different credentials for dev and production
  • Never commit credentials to version control
  • Use environment-specific keys in Supabase

Initial Setup​

Step 1: Generate Prisma Client​

Generate the Prisma client based on your schema:

npx prisma generate

This creates the TypeScript types for your database models.

Step 2: Initialize Database Schema​

Option A: Using Migrations (Recommended)

npx prisma migrate dev --name init

This will:

  • Create the database schema
  • Generate a migration file
  • Apply it to your database

Option B: Push Schema Without Migrations

npx prisma db push

Use this for rapid prototyping or when you don't need migration history.

Step 3: Seed Sample Data (Optional)​

Add sample products and lanes for testing:

node prisma/seed.js

This creates:

  • 2 cricket lanes (Premium and Standard)
  • 12 sample products (equipment and refreshments)

Step 4: Verify Installation​

Open Prisma Studio to view your data:

npx prisma studio

Access at http://localhost:5555

Production Deployment​

Step 1: Set Up Production Database​

  1. Create production CockroachDB cluster

    • Use a dedicated cluster for production
    • Configure appropriate backup settings
    • Note the connection string

  2. Configure environment variables

    • Add production DATABASE_URL to your hosting platform
    • Add production Supabase credentials

Step 2: Deploy Migrations​

npx prisma migrate deploy

This applies all pending migrations to production.

caution

Always test migrations locally before deploying to production!

Schema Management​

Development Workflow​

  1. Modify Schema

    // prisma/schema.prisma
    model Product {
      id          String   @id @default(cuid())
      name        String
      price       Decimal  @db.Decimal(10, 2)
      // Add new field
      discount    Decimal? @db.Decimal(5, 2)
    }

  2. Create Migration

    npx prisma migrate dev --name add_product_discount

  3. Test Locally

    • Verify schema changes
    • Test application functionality
    • Check data integrity

  4. Commit Changes

    git add prisma/
    git commit -m "Add product discount field"

Production Deployment​

  1. Push to Production

    git push origin main

  2. Apply Migrations

    # On your hosting platform or via CI/CD
    npx prisma migrate deploy

Useful Commands​

Development​

# Generate Prisma client
npx prisma generate

# Create migration
npx prisma migrate dev --name migration_name

# Push schema changes
npx prisma db push

# Open Prisma Studio
npx prisma studio

# Reset database (⚠️ deletes all data)
npx prisma migrate reset

Production​

# Deploy migrations
npx prisma migrate deploy

# Generate client
npx prisma generate

# Check migration status
npx prisma migrate status

Maintenance​

# Format schema file
npx prisma format

# Validate schema
npx prisma validate

# Pull schema from database
npx prisma db pull

Troubleshooting​

Connection Errors​

Issue: Cannot connect to CockroachDB

Error: Can't reach database server

Solutions:

  • Verify your DATABASE_URL is correct
  • Check that sslmode=require is included
  • Ensure your IP is whitelisted in CockroachDB dashboard
  • Verify the cluster is running

Authentication Errors​

Issue: Supabase authentication not working

Solutions:

  • Verify NEXT_PUBLIC_SUPABASE_URL is correct
  • Check that anon key is valid
  • Ensure redirect URLs are configured in Supabase dashboard
  • Verify service role key for server-side operations

Migration Errors​

Issue: Migration fails with constraint violations

Solutions:

  • Check existing data compatibility
  • Add data migration scripts if needed
  • Use prisma migrate resolve for stuck migrations
  • Consider prisma db push for development

Performance Issues​

Issue: Slow database queries

Solutions:

  • Add indexes to frequently queried fields
  • Use @@index in your Prisma schema
  • Check query performance in Prisma Studio
  • Consider connection pooling for production

Best Practices​

Development​

  • ✅ Use migrations for tracking schema changes
  • ✅ Test migrations locally before production
  • ✅ Keep DATABASE_URL in .env.local, never commit
  • ✅ Use Prisma Studio for data inspection
  • ✅ Run npx prisma format before committing

Production​

  • ✅ Use separate credentials for dev/prod
  • ✅ Enable connection pooling if available
  • ✅ Set up automated backups
  • ✅ Monitor database performance
  • ✅ Use prisma migrate deploy (never migrate dev)

Security​

  • ✅ Use strong, unique passwords
  • ✅ Rotate credentials periodically
  • ✅ Restrict database access by IP
  • ✅ Use SSL/TLS for all connections
  • ✅ Never expose service role keys client-side

Next Steps​

Need help? Check the Architecture Overview or create an issue on GitHub.