Shared Database

The shared-db package provides database connectivity and ORM functionality for the Substrate application using Prisma ORM with PostgreSQL.

Features

• Prisma ORM - Type-safe database access with auto-generated client
• PostgreSQL Support - Uses the @prisma/adapter-pg for connections
• Profile Management - Built-in Profile entity for user data
• Type Integration - ORM types conform to shared-types interfaces

Type Integration

The package demonstrates how to connect shared-types TypeScript interfaces with Prisma ORM models through compile-time verification that ensures Prisma-generated types are compatible with shared-types interfaces.

Environment Variables

Configure the database connection using environment variables:

Connection String

The DATABASE_URL environment variable is required and determines which database adapter to use:

# PostgreSQL connection (production)
DATABASE_URL=postgres://user:pass@host:5432/database

# SQLite connection (local development and testing)
DATABASE_URL=file:./dev.db
# or with absolute path
DATABASE_URL=file:/app/test.db

Database Selection

The package automatically selects the appropriate database adapter based on the DATABASE_URL protocol:

• file: protocol → SQLite adapter (@prisma/adapter-better-sqlite3)
• postgres:// or postgresql:// protocol → PostgreSQL adapter (@prisma/adapter-pg)

Individual Parameters (PostgreSQL Only)

When running in Docker or other environments with PostgreSQL, you can also configure using individual parameters:

Variable	Description	Default
DATABASE_URL	Full database connection string	Required
POSTGRES_HOST	PostgreSQL host	localhost
POSTGRES_PORT	PostgreSQL port	5432
POSTGRES_DB	Database name	substrate
POSTGRES_USER	Database user	postgres
POSTGRES_PASSWORD	Database password	-
POSTGRES_SSL	Enable SSL connection	false

Database Migrations

The shared-db package uses Prisma Migrate to manage database schema evolution with version-controlled migration files.

Dual-Schema Strategy

The package maintains two Prisma schemas for different database backends:

• prisma/schema.prisma - PostgreSQL schema (production)
• prisma/schema.sqlite.prisma - SQLite schema (local development and testing)

The prisma.config.ts configuration automatically selects the appropriate schema based on your DATABASE_URL:

• file: protocol → SQLite schema
• postgres:// or postgresql:// → PostgreSQL schema

Migration Workflow

PostgreSQL (Production)

Prisma migrations are version-controlled and committed to the repository for PostgreSQL:

1. Development: Create and apply migrations locally

   cd packages/shared-db
   DATABASE_URL="postgresql://user:pass@host:5432/db" yarn migrate:dev

2. CI/CD: Apply pending migrations before tests

   DATABASE_URL="postgresql://..." yarn migrate

3. Deployment: Apply migrations before starting the application

   DATABASE_URL="postgresql://..." yarn migrate

The migrate script uses prisma migrate deploy, which is idempotent and safe for production.

SQLite (Development and Testing)

For SQLite, use schema push rather than migrations (since SQLite databases are typically ephemeral in development):

DATABASE_URL="file:./dev.db" yarn prisma:push

Or with explicit schema:

yarn prisma db push --schema node_modules/shared-db/prisma/schema.sqlite.prisma --config node_modules/shared-db/prisma.config.ts

Migration Commands

Command	Description	Environment
yarn migrate	Apply pending migrations (production-safe, idempotent)	Production/CI
yarn migrate:dev	Create and apply new migrations (interactive)	Development
yarn prisma:push	Push schema changes without migrations (SQLite)	Development
yarn prisma:generate	Generate Prisma Client from schema	All

Best Practices

1. Never edit migration files manually - create new migrations instead
2. Always commit migration files to version control (prisma/migrations/)
3. Run migrations before deploying application code changes
4. Use migrate deploy in production - never migrate dev
5. Test migrations against a production-like database before deploying

API Reference

See the API Reference for detailed documentation on all exports.