basil/docs/REMOTE_DATABASE.md

Remote Database Configuration

This guide explains how to configure Basil to use a remote PostgreSQL database instead of the local Docker container.

Overview

By default, Basil uses a local PostgreSQL database running in Docker. However, you can configure it to use a remote database service such as:

• AWS RDS (Relational Database Service)
• Google Cloud SQL
• Azure Database for PostgreSQL
• Digital Ocean Managed Databases
• Heroku Postgres
• Self-hosted PostgreSQL server

Configuration

1. Update Environment Variables

Edit packages/api/.env to point to your remote database:

DATABASE_URL="postgresql://username:password@remote-host:5432/basil?schema=public"

Connection String Format

postgresql://[username]:[password]@[host]:[port]/[database]?[options]

Components:

• username - Database user
• password - Database password
• host - Database hostname or IP
• port - Database port (default: 5432)
• database - Database name (usually "basil")
• options - Additional connection options (e.g., sslmode=require)

2. Provider-Specific Examples

AWS RDS

DATABASE_URL="postgresql://basiladmin:yourpassword@basil-db.abc123.us-east-1.rds.amazonaws.com:5432/basil?schema=public"

Additional SSL configuration may be required:

DATABASE_URL="postgresql://basiladmin:yourpassword@basil-db.abc123.us-east-1.rds.amazonaws.com:5432/basil?schema=public&sslmode=require"

Google Cloud SQL

DATABASE_URL="postgresql://postgres:yourpassword@127.0.0.1:5432/basil?host=/cloudsql/project:region:instance"

Or with public IP:

DATABASE_URL="postgresql://postgres:yourpassword@35.123.45.67:5432/basil?schema=public&sslmode=require"

Azure Database for PostgreSQL

DATABASE_URL="postgresql://basiladmin@servername:yourpassword@servername.postgres.database.azure.com:5432/basil?schema=public&sslmode=require"

Digital Ocean Managed Database

DATABASE_URL="postgresql://doadmin:yourpassword@basil-db-do-user-123456-0.b.db.ondigitalocean.com:25060/basil?sslmode=require"

Heroku Postgres

Heroku provides a DATABASE_URL automatically:

DATABASE_URL="postgres://user:password@ec2-123-45-67-89.compute-1.amazonaws.com:5432/d1234abcd5678ef"

Note: Heroku uses postgres:// instead of postgresql://, but both work with Prisma.

Self-Hosted PostgreSQL

DATABASE_URL="postgresql://basil:password@192.168.1.100:5432/basil?schema=public"

For SSL connections:

DATABASE_URL="postgresql://basil:password@postgres.example.com:5432/basil?schema=public&sslmode=require"

3. Docker Configuration

When using Docker with a remote database, you need to update the configuration:

Option A: Use Environment Variable (Recommended)

Create a .env file in the project root:

DATABASE_URL=postgresql://username:password@remote-host:5432/basil?schema=public

The docker-compose.yml is already configured to use this:

environment:
  DATABASE_URL: ${DATABASE_URL:-postgresql://basil:basil@postgres:5432/basil?schema=public}

Option B: Edit docker-compose.yml Directly

Edit the api service in docker-compose.yml:

api:
  environment:
    DATABASE_URL: postgresql://username:password@remote-host:5432/basil?schema=public

Option C: Disable Local PostgreSQL

If using only a remote database, you can disable the local postgres service:

1. Comment out or remove the postgres service in docker-compose.yml
2. Remove the depends_on condition from the api service
3. Remove the unused postgres_data volume

services:
  # postgres:
  #   image: postgres:16-alpine
  #   ... (commented out)

  api:
    # Remove depends_on if not using local postgres
    # depends_on:
    #   postgres:
    #     condition: service_healthy

4. Initialize Remote Database

Before first use, you need to initialize the database schema:

# From your development machine
cd packages/api
npm run prisma:migrate

# Or from Docker
docker exec basil-api npx prisma migrate deploy

This will create all necessary tables and relationships.

SSL/TLS Configuration

Enabling SSL

Most managed database services require SSL connections. Add sslmode to your connection string:

# Require SSL but don't verify certificate
DATABASE_URL="postgresql://user:password@host:5432/basil?sslmode=require"

# Require SSL and verify certificate
DATABASE_URL="postgresql://user:password@host:5432/basil?sslmode=verify-full"

# Disable SSL (only for development/testing)
DATABASE_URL="postgresql://user:password@host:5432/basil?sslmode=disable"

SSL Modes

• disable - No SSL (not recommended for production)
• allow - Try SSL, fall back to non-SSL
• prefer - Try SSL first (default)
• require - Require SSL, don't verify certificate
• verify-ca - Require SSL, verify certificate authority
• verify-full - Require SSL, verify certificate and hostname

Custom SSL Certificates

For custom CA certificates, you may need to configure additional options:

DATABASE_URL="postgresql://user:password@host:5432/basil?sslmode=require&sslcert=/path/to/client-cert.pem&sslkey=/path/to/client-key.pem&sslrootcert=/path/to/ca-cert.pem"

Connection Pooling

For production deployments with high traffic, consider using connection pooling.

PgBouncer

Example configuration with PgBouncer:

DATABASE_URL="postgresql://user:password@pgbouncer-host:6432/basil?schema=public"

Prisma Data Platform (Prisma Accelerate)

For advanced connection pooling and caching:

DATABASE_URL="prisma://accelerate.prisma-data.net/?api_key=your-api-key"

Security Best Practices

1. Use Environment Variables: Never commit credentials to git
2. Strong Passwords: Use generated passwords with high entropy
3. Restrict Access: Configure database firewall rules to only allow your application servers
4. SSL/TLS: Always use SSL in production
5. Read-Only Users: Consider using read-only database users for analytics
6. Regular Backups: Configure automated backups on your database service
7. Rotate Credentials: Periodically rotate database passwords

Performance Considerations

Network Latency

Remote databases add network latency. Consider:

• Choose a database region close to your application servers
• Use connection pooling to reduce connection overhead
• Enable query optimization in Prisma

Connection Limits

Managed databases often have connection limits:

• Check your plan's connection limit
• Configure appropriate connection pool size
• Use PgBouncer for connection multiplexing

Prisma Configuration

Optimize Prisma connection settings in packages/api/prisma/schema.prisma:

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

  // Optional: Configure connection pool
  // relationMode = "prisma"
}

Monitoring

Connection Status

Check database connectivity:

# Health check endpoint
curl http://localhost:3001/health

# Test database connection with Prisma
docker exec basil-api npx prisma db push --preview-feature

Query Performance

Monitor slow queries:

• Enable PostgreSQL query logging
• Use database monitoring tools (CloudWatch, Datadog, etc.)
• Analyze with EXPLAIN ANALYZE for slow queries

Troubleshooting

Connection Refused

Error: Connection refused

Possible causes:

• Incorrect host or port
• Database server not running
• Firewall blocking connection
• Security group not allowing your IP

Solutions:

• Verify database hostname and port
• Check database server status
• Update firewall rules to allow your application's IP
• Verify security group rules (AWS, Azure, GCP)

Authentication Failed

Error: Password authentication failed

Solutions:

• Double-check username and password
• Ensure special characters in password are URL-encoded
• Verify user has permission to access the database
• Check if IP allowlist includes your server

SSL Required

Error: SSL required or no pg_hba.conf entry

Solutions:

• Add sslmode=require to connection string
• Configure database to accept non-SSL connections (dev only)
• Install required SSL certificates

Connection Timeout

Error: Connection timeout

Solutions:

• Verify network connectivity
• Check if database server is accepting connections
• Increase timeout in Prisma configuration
• Verify DNS resolution of database hostname

Too Many Connections

Error: Too many connections

Solutions:

• Reduce connection pool size
• Use PgBouncer for connection pooling
• Upgrade database plan for more connections
• Check for connection leaks in application code

Migration from Local to Remote

To migrate from local Docker database to remote:

1. Create a backup of local data:

   curl -X POST http://localhost:3001/api/backup
   

2. Initialize remote database:

   # Update DATABASE_URL to point to remote
   cd packages/api
   npm run prisma:migrate deploy
   

3. Restore backup to remote database:

   curl -X POST \
     -F "backup=@basil-backup-2025-11-10.zip" \
     http://localhost:3001/api/backup/restore
   

4. Verify data migration:

   • Check recipe count: curl http://localhost:3001/api/recipes
   • Test recipe access and functionality

5. Update production configuration:

   • Update environment variables in production
   • Restart application services

Cost Optimization

Managed Database Pricing

Compare costs across providers:

• AWS RDS: Pay per instance hour + storage
• Google Cloud SQL: Pay per vCPU hour + storage
• Azure Database: Pay per vCore + storage
• Digital Ocean: Fixed monthly pricing by plan
• Heroku: Free tier available, then fixed monthly pricing

Tips to Reduce Costs

1. Right-size your instance: Start small, scale as needed
2. Use reserved instances: AWS/Azure offer discounts for 1-3 year commitments
3. Enable auto-scaling: Scale down during low traffic periods
4. Optimize storage: Use standard storage instead of provisioned IOPS if possible
5. Regular backups: Use built-in backup services (cheaper than manual snapshots)

Support

For database-specific configuration issues, consult:

• AWS RDS Documentation
• Google Cloud SQL Documentation
• Azure Database Documentation
• Digital Ocean Documentation
• Prisma Database Connectors