Database Documentation

This document explains how the PostgreSQL database is configured and used in the Hosted Components application.

Technology Stack

• PostgreSQL: A powerful, open-source relational database
• Drizzle ORM: Type-safe ORM for TypeScript

Database Architecture

The application uses PostgreSQL as its database:

1. Local development: Local PostgreSQL instance
2. Production: PostgreSQL on AWS RDS

This approach allows for:

1. Data persistence: Reliable storage of application data
2. Development flexibility: Easy local development with PostgreSQL

Database Schema

The database schema is defined using Drizzle ORM in TypeScript. This provides:

1. Type safety: Full TypeScript support for database operations
2. Migration management: Easy schema updates and versioning
3. Query building: Type-safe query builder

Environment Variables

The following environment variables are used for database configuration:

Local Development

• POSTGRES_LOCAL_HOST: The hostname of your PostgreSQL instance (typically 'localhost')
• POSTGRES_LOCAL_PORT: The port for your PostgreSQL connection (typically 5432)
• POSTGRES_LOCAL_USER: PostgreSQL username
• POSTGRES_LOCAL_PASSWORD: PostgreSQL password
• POSTGRES_LOCAL_DB: PostgreSQL database name

Production (AWS RDS)

• USE_RDS: Set to "true" to use AWS RDS PostgreSQL
• POSTGRES_HOST: The hostname of your PostgreSQL RDS instance
• POSTGRES_PORT: The port for your PostgreSQL connection (typically 5432)
• POSTGRES_USER: PostgreSQL username
• POSTGRES_PASSWORD: PostgreSQL password
• POSTGRES_DB: PostgreSQL database name
• POSTGRES_SSL: Set to "true" to use SSL for the connection
• CUSTOM_AWS_ACCESS_KEY_ID: AWS access key ID
• CUSTOM_AWS_SECRET_ACCESS_KEY: AWS secret access key
• SECURITY_GROUP_ID: The security group attached to AWS RDS PostgreSQL

You can retrieve the value for these environment variables from AWS Amplify console's "Secrets" page for the hosted-components project. Make sure your public local IP is added as an inbound rule to the security group attached to the RDS.

Testing Production Locally

If testing production environment locally, you'll also need:

• NODE_ENV: Set to "production"

Local Development

For local development:

1. Install PostgreSQL locally

2. Set up your local database:

   # Create the database
   createdb hosted_components
   
   # Apply migrations
   npm run db:migrate

3. No special environment variables are needed for local development as the application will connect to your local PostgreSQL instance by default. If needed to change the default local configuration though, change POSTGRES_LOCAL_... etc in the environment variables.

Any changes you make will update your local PostgreSQL database.

Production Setup

In production:

1. Set up a PostgreSQL database on AWS RDS
2. Configure environment variables:

   USE_RDS=true
   POSTGRES_HOST=your-rds-instance.region.rds.amazonaws.com
   POSTGRES_PORT=5432
   POSTGRES_USER=your_user
   POSTGRES_PASSWORD=your_password
   POSTGRES_DB=your_database

Viewing the Database

To view your local PostgreSQL database:

1. Using Drizzle Studio:

   npm run db:studio

2. Using PostgreSQL tools:

   • You can use tools like pgAdmin or DBeaver
   • Connect using your POSTGRES_ configurations from the environment variable

Schema Changes and Migrations

To make schema changes and create migrations:

1. Modify the schema:

   • Update the schema-pg.ts file with your schema changes

2. Generate migrations:

   npm run db:generate -- --name=add-css-column-to-styling

3. Apply migrations:

   npm run db:migrate

Troubleshooting

Common issues and solutions:

1. Connection issues:

   • Check your DATABASE_URL
   • Verify PostgreSQL is running
   • Check network/firewall settings

2. Migration failures:

   • Ensure migrations are in correct order
   • Check for conflicting changes
   • Review migration logs