Database Setup

Overview

OpenInvoice uses PostgreSQL with Prisma ORM for database management. This guide covers database setup, migrations, and management.

Prerequisites

• PostgreSQL installed and running
• Database created
• Connection string available

Database Connection

Connection String Format

DATABASE_URL=postgresql://user:password@host:port/database

Example

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

Initial Setup

1. Generate Prisma Client

bun prisma generate

This generates the Prisma Client based on your schema.

2. Run Migrations

bun prisma migrate dev

This creates all database tables and relationships.

3. Seed Database (Optional)

If seed data is available:

bun prisma db seed

Database Schema

Key Models

• User - User accounts
• Organization - Workspaces/teams
• Customer - Customer records
• Product - Product catalog
• Invoice - Invoice records
• InvoiceItem - Invoice line items
• Payment - Payment records
• RecurringInvoiceTemplate - Recurring invoice templates
• TaxProfile - Tax configuration
• EmailLog - Email tracking

See prisma/schema.prisma for complete schema.

Migrations

Creating Migrations

When schema changes:

bun prisma migrate dev --name migration_name

Applying Migrations

In production:

bun prisma migrate deploy

Resetting Database

⚠️ Warning: This deletes all data!

bun prisma migrate reset

Prisma Studio

View and edit data visually:

bun prisma studio

Opens at http://localhost:5555

Database Management

Backup Database

pg_dump -U postgres openinvoice > backup.sql

Restore Database

psql -U postgres openinvoice < backup.sql

Production Considerations

Connection Pooling

Use connection pooling in production:

• PgBouncer
• Supabase connection pooling
• Railway/Heroku connection pooling

Migrations in Production

1. Test migrations locally
2. Backup database
3. Run migrations during maintenance window
4. Monitor for issues
5. Rollback if needed

Troubleshooting

Connection Errors

• Verify PostgreSQL is running
• Check connection string
• Verify user permissions
• Check firewall rules

Migration Errors

• Review migration files
• Check for conflicts
• Verify schema changes
• Rollback if needed

Next Steps

• Project Structure - Understand codebase structure
• API Routes - API route patterns