Guide to setting up and managing PostgreSQL databases for production deployment.

Database Hosting Guide

This guide covers setting up PostgreSQL databases for production deployment with various hosting providers.

Overview

Raypx uses PostgreSQL as the primary database with Drizzle ORM for type-safe database operations. The project is compatible with any PostgreSQL-compatible hosting service.

Recommended Providers

1. Neon (Recommended for Serverless)

Neon is a serverless PostgreSQL platform optimized for modern applications, perfect for Vercel deployments.

Setup Steps

Create a Neon account
- Visit neon.tech
- Sign up with GitHub or email
Create a new project
- Click "Create Project"
- Choose a project name and region (select closest to your deployment region)
- Select PostgreSQL version (15+ recommended)
Get connection string
- After project creation, copy the connection string from the dashboard
- Format: postgresql://user:password@host.neon.tech/dbname?sslmode=require

Configure environment variable

DATABASE_URL=postgresql://user:password@host.neon.tech/dbname?sslmode=require

Run migrations
```
pnpm --filter @raypx/db run db:migrate
```

Neon Features

✅ Serverless (auto-scaling)
✅ Branching (database branching for dev/staging)
✅ Point-in-time recovery
✅ Free tier available
✅ Built-in connection pooling
✅ Automatic backups

Connection Pooling

Neon provides built-in connection pooling. For production, use the pooled connection string:

postgresql://user:password@host.neon.tech/dbname?sslmode=require&pgbouncer=true

2. Supabase

Supabase provides PostgreSQL with additional features like real-time subscriptions and storage.

Setup Steps

Create a Supabase project
- Visit supabase.com
- Create a new project
Get connection string
- Go to Project Settings → Database
- Copy the connection string (use "Connection pooling" for production)
- Format: postgresql://postgres:[password]@[host]:5432/postgres

Configure environment variable

DATABASE_URL=postgresql://postgres:[password]@[host]:5432/postgres

Run migrations
```
pnpm --filter @raypx/db run db:migrate
```

Supabase Features

✅ PostgreSQL with extensions
✅ Real-time subscriptions
✅ Storage integration
✅ Auth integration (if needed)
✅ Free tier available

3. Railway

Railway offers simple PostgreSQL hosting with easy deployment integration.

Setup Steps

Create a Railway account
- Visit railway.app
- Sign up with GitHub
Create PostgreSQL service
- Click "New Project"
- Add "PostgreSQL" service
- Railway will automatically create the database
Get connection string
- Click on the PostgreSQL service
- Go to "Variables" tab
- Copy DATABASE_URL

Configure environment variable

DATABASE_URL=postgresql://postgres:[password]@[host]:5432/railway

Run migrations
```
pnpm --filter @raypx/db run db:migrate
```

4. Vercel Postgres

Vercel Postgres integrates seamlessly with Vercel deployments.

Setup Steps

Add Postgres to Vercel project
- In Vercel dashboard, go to your project
- Navigate to "Storage" tab
- Click "Create Database" → "Postgres"
Get connection string
- Vercel automatically provides POSTGRES_URL environment variable
- Use this in your .env file
Configure environment variable
```
DATABASE_URL=$POSTGRES_URL
```
Run migrations
```
pnpm --filter @raypx/db run db:migrate
```

5. Self-Hosted PostgreSQL

For self-hosted PostgreSQL (Docker, VPS, etc.):

Setup Steps

Install PostgreSQL

# Using Docker
docker run --name postgres \
  -e POSTGRES_PASSWORD=yourpassword \
  -e POSTGRES_DB=raypx \
  -p 5432:5432 \
  -d postgres:15

Configure connection string

DATABASE_URL=postgresql://postgres:yourpassword@localhost:5432/raypx

Run migrations
```
pnpm --filter @raypx/db run db:migrate
```

Environment Configuration

Development

For local development, use .env file:

DATABASE_URL=postgresql://postgres:password@localhost:5432/raypx_dev

Production

For production deployments:

Vercel

Add DATABASE_URL in Project Settings → Environment Variables
Use production database connection string

Netlify

Add DATABASE_URL in Site Settings → Environment Variables
Use production database connection string

Other Platforms

Set DATABASE_URL as an environment variable in your hosting platform
Ensure SSL is enabled (?sslmode=require)

Connection Pooling

For production, use connection pooling to manage database connections efficiently:

Neon

DATABASE_URL=postgresql://user:password@host.neon.tech/dbname?sslmode=require&pgbouncer=true

Supabase

Use the "Connection pooling" connection string from Supabase dashboard.

Custom Pooling (PgBouncer)

If using custom pooling:

DATABASE_URL=postgresql://user:password@pooler-host:6543/dbname?sslmode=require

Running Migrations

Development

pnpm --filter @raypx/db run db:migrate

Production

Option 1: Manual Migration

# Set DATABASE_URL environment variable
export DATABASE_URL=your_production_connection_string

# Run migrations
pnpm --filter @raypx/db run db:migrate

Option 2: CI/CD Migration

Add migration step to your deployment pipeline:

# Example GitHub Actions
- name: Run migrations
  env:
    DATABASE_URL: ${{ secrets.DATABASE_URL }}
  run: pnpm --filter @raypx/db run db:migrate

Option 3: Migration Script

Create a migration script for your hosting platform:

#!/bin/bash
# migrate.sh
export DATABASE_URL=$1
pnpm --filter @raypx/db run db:migrate

Backup Strategy

Automated Backups

Most providers offer automated backups:

Neon: Automatic daily backups with point-in-time recovery
Supabase: Daily backups (upgrade for more frequent)
Railway: Automatic backups (check plan details)
Vercel Postgres: Automatic backups

Manual Backups

For manual backups:

# Export database
pg_dump $DATABASE_URL > backup.sql

# Restore database
psql $DATABASE_URL < backup.sql

Backup Best Practices

Regular backups: Daily at minimum for production
Test restores: Periodically test backup restoration
Off-site storage: Store backups in separate location
Retention policy: Keep backups for at least 30 days
Point-in-time recovery: Use providers that support PITR

Security Considerations

SSL/TLS

Always use SSL connections in production:

DATABASE_URL=postgresql://user:password@host/dbname?sslmode=require

Connection Limits

Monitor connection usage
Use connection pooling for serverless environments
Configure appropriate connection limits based on your plan

Access Control

Use strong passwords
Limit database access to application servers only
Use IP whitelisting if supported
Rotate credentials regularly

Environment Variables

Never commit DATABASE_URL to version control
Use secure environment variable management
Rotate credentials if compromised

Monitoring

Database Metrics

Monitor these key metrics:

Connection count: Active database connections
Query performance: Slow query identification
Storage usage: Database size and growth
CPU/Memory: Resource utilization

Tools

Drizzle Studio: pnpm --filter @raypx/db run db:studio
Provider dashboards: Use hosting provider's monitoring tools
pgAdmin: PostgreSQL administration tool
PostgreSQL logs: Check application logs for database errors

Troubleshooting

Connection Issues

Error: Connection refused

Check if database is running
Verify connection string format
Check firewall/network settings

Error: SSL required

Add ?sslmode=require to connection string
Verify SSL certificate

Error: Too many connections

Use connection pooling
Check for connection leaks
Increase connection limit (if possible)

Migration Issues

Error: Migration already applied

Check migration status: pnpm --filter @raypx/db run db:migrate
Manually verify migration table

Error: Schema conflicts

Review migration files
Test migrations on staging first
Consider rollback strategy

Performance Issues

Slow queries

Add indexes for frequently queried columns
Use EXPLAIN ANALYZE to analyze queries
Consider query optimization

High connection usage

Implement connection pooling
Review connection lifecycle in code
Check for connection leaks

Migration Checklist

Before deploying to production:

Next Steps

Review Development Workflow for database development practices
Check Workspace Layout for database package structure
Set up Redis hosting for caching (optional)

Database Hosting

On this page