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:

PostgreSQL (Primary Database — via Prisma)​

  • Purpose: All application data storage
  • Contains: Users, bookings, products, orders, lanes, payments
  • Dev: Local Docker container (postgres:15) on port 54322 — never pauses
  • Prod: Hosted Supabase PostgreSQL instance
  • 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​

On sign-in, src/services/userSync.ts syncs the Supabase user into the User table in PostgreSQL so app data is always linked to a real DB record.

Development Setup​

Prerequisites​

  • Docker for local PostgreSQL
  • A Supabase project (auth only — create one)

Step 1: Start Local PostgreSQL​

npm run db:start

This creates (on first run) or starts a gully-local-db Docker container with:

  • Image: postgres:15
  • Port: 54322 (avoids conflicts with any local Homebrew PostgreSQL on 5432)
  • Database: gullysports
  • User/password: postgres/postgres

To stop it:

npm run db:stop

Step 2: Configure Environment Variables​

Copy .env.example to .env and set the local DB URLs:

# Local PostgreSQL (Docker)
DATABASE_URL="postgresql://postgres:postgres@localhost:54322/gullysports"
DIRECT_URL="postgresql://postgres:postgres@localhost:54322/gullysports"

# Supabase Auth (auth only — dev project)
NEXT_PUBLIC_SUPABASE_URL="https://your-dev-project.supabase.co"
NEXT_PUBLIC_SUPABASE_ANON_KEY="your-dev-anon-key"

Step 3: Apply Migrations​

npx prisma migrate deploy

This applies all existing migrations to your local database.

Step 4: Seed Sample Data​

npx prisma db seed

Creates sample products and cricket lanes for testing.

Step 5: Verify​

Open Prisma Studio to inspect your data:

npx prisma studio

Access at http://localhost:5555

Production Setup​

Production database is hosted on Supabase PostgreSQL. The vercel-build script runs prisma migrate deploy automatically on every deploy.

Environment Variables​

# Supabase PostgreSQL (Production)
DATABASE_URL="postgresql://postgres.[ref]:[password]@aws-0-us-west-1.pooler.supabase.com:6543/postgres"
DIRECT_URL="postgresql://postgres.[ref]:[password]@aws-0-us-west-1.pooler.supabase.com:5432/postgres"

# Supabase Auth (Production)
NEXT_PUBLIC_SUPABASE_URL="https://your-prod-project.supabase.co"
NEXT_PUBLIC_SUPABASE_ANON_KEY="your-prod-anon-key"
Connection pooling

Use the Supabase pooler URL (port 6543) for DATABASE_URL and the direct URL (port 5432) for DIRECT_URL. Prisma requires the direct URL for migrations.

Security
  • Use different credentials for dev and production
  • Never commit credentials to version control

Schema Management​

Create a New Migration​

npx prisma migrate dev --name add_product_discount

Deploy Migrations to Production​

npx prisma migrate deploy

The vercel-build npm script runs this automatically on Vercel deploys.

Useful Commands​

# Generate Prisma client (after schema change)
npx prisma generate

# Open browser-based DB viewer
npx prisma studio

# Format schema file
npx prisma format

# Check migration status
npx prisma migrate status

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

Troubleshooting​

Can't Connect to Database​

Error: Can't reach database server at localhost:54322

Fix: The Docker container isn't running. Start it:

npm run db:start
docker ps | grep gully-local-db  # verify it's running

Port Conflict (54322 Already in Use)​

Another process is using port 54322. Find and stop it:

lsof -i :54322

Migration Errors​

  • Constraint violations: Check existing data compatibility before adding NOT NULL columns
  • Stuck migration: Use npx prisma migrate resolve

Prisma Client Out of Date​

After changing schema.prisma, regenerate the client:

npx prisma generate

Best Practices​

Development​

  • ✅ Use npm run db:start / npm run db:stop to manage the local container
  • ✅ Use migrations (migrate dev) not db push — keeps history
  • ✅ Keep DATABASE_URL in .env, never commit it
  • ✅ Use Prisma Studio for data inspection during development

Production​

  • ✅ Use separate Supabase projects for dev and prod
  • ✅ Use connection pooler URL for DATABASE_URL
  • ✅ Always run prisma migrate deploy, never migrate dev in production
  • ✅ Supabase handles automated backups on the hosted plan

Next Steps​