Database Setup

Open Sky Commerce uses PostgreSQL as its database, accessed through Prisma ORM. The schema is fully defined in prisma/schema.prisma and managed through Prisma migrations.

1. Create a PostgreSQL Database

You can use a local PostgreSQL instance, a managed database (DigitalOcean Managed Databases, Supabase, Neon, Railway), or any PostgreSQL-compatible service.

# Example: create a local database
createdb openskycommerce

2. Set the Connection String

Add your connection string to .env:

DATABASE_URL="postgresql://username:password@localhost:5432/openskycommerce"

For a managed database with SSL:

DATABASE_URL="postgresql://username:password@host:25060/openskycommerce?sslmode=require"

3. Run Migrations

npx prisma migrate dev --name init

This applies all migrations in prisma/migrations/ and creates the full schema in your database.

4. Generate the Prisma Client

npx prisma generate

This is also run automatically as part of npm run build.

5. Seed Demo Data (Optional)

npm run import-demo-data

This script reads JSON files from seed-data/ and populates the database with sample products, categories, blog posts, users, and settings — useful for evaluating the platform before entering your own data.

Prisma Studio

You can browse and edit your database records directly using Prisma Studio:

npx prisma studio

Key Models

Product / ProductVariant — product catalog with variants and a productType enum (PHYSICAL, DIGITAL, SUBSCRIPTION_PHYSICAL, SUBSCRIPTION_DIGITAL, SERVICE_APPOINTMENT)
SubscriptionPlan — billing interval and price attached to subscription products
Category — hierarchical product categories
Order — customer orders with line items; subscription orders include lifecycle fields (status, billing period, stored payment IDs)
User — customer and admin accounts
PaymentSettings — singleton row holding all payment processor keys (stored encrypted)
ShippingSettings — singleton row holding active shipping provider and credentials (stored encrypted)
SeoSetting — site-wide SEO and homepage variant configuration
BusinessInfo — company name, address, phone, email (used as shipping origin)
Post / PostAuthor / PostCategory — blog system
HeroBanner / HeroSlider — homepage hero configuration
Coupon — discount codes
Review — product reviews

Connection Pooling Note

On hosted platforms like DigitalOcean App Platform, Prisma's default connection pool can exhaust a managed database's connection limit (typically 25) when multiple app instances run. Append connection_limit=5 to your DATABASE_URL to cap the pool per instance:

DATABASE_URL="postgresql://...?sslmode=require&connection_limit=5"