Skip to content

POSTGRES_IMPLEMENTATION.md

Latest commit

 

History

History
259 lines (189 loc) · 8.61 KB

POSTGRES_IMPLEMENTATION.md

File metadata and controls

259 lines (189 loc) · 8.61 KB
Raw

PostgreSQL Support Implementation - Complete

PostgreSQL support has been successfully implemented for TeamBeat. The application now supports both SQLite and PostgreSQL databases, selected automatically based on the DATABASE_URL environment variable.

Implementation Summary

Phase 1: Remove Transactions (better-sqlite3 Limitation) ✅

All database transactions have been removed due to better-sqlite3 driver limitations:

  • better-sqlite3 does not support async transactions - throws "Transaction function cannot return a promise"
  • PostgreSQL requires async transactions - incompatible with better-sqlite3's sync-only approach
  • Solution: Removed all transaction wrappers, rely on foreign key constraints and atomic operations

Files modified:

  • src/lib/server/repositories/board.ts - 3 transactions removed
  • src/lib/server/repositories/board-series.ts - 1 transaction removed
  • src/lib/server/repositories/scene.ts - 1 transaction removed
  • src/routes/api/boards/[id]/clone/+server.ts - 1 large transaction removed
  • src/routes/api/boards/[id]/setup-template/+server.ts - 1 transaction removed

Important: The application works correctly without explicit transactions because:

  1. Foreign key constraints with CASCADE DELETE protect data integrity
  2. Individual operations are atomic
  3. Most operations are single-row inserts/updates

Phase 2: Update Schema for PostgreSQL Compatibility ✅

Schema (src/lib/server/db/schema.ts) updated with:

  • Conditional type builders: Detects database type at module load time
  • Boolean fields: Uses native boolean for PostgreSQL, integer({ mode: 'boolean' }) for SQLite
  • Timestamps: Converted from sqlCURRENT_TIMESTAMP`` to .$defaultFn(() => new Date().toISOString())
  • ISO 8601 dates: All datetime fields use text with ISO 8601 format (compatible with both databases)
  • Added missing field: multipleVotesPerCard boolean field added to scenes table

Phase 3: Manual Testing Checkpoint ⏸️

Developer Action Required:

Before proceeding with production use, please test the application thoroughly:

  1. Test with SQLite (existing functionality):

    npm run db:push:sqlite
npm run dev
    • Verify all features work as before
    • Test card creation, voting, grouping
    • Test scene management and permissions
    • Test real-time SSE updates
    • Test board cloning

  2. Test with PostgreSQL:

    # Set up a PostgreSQL database
createdb teambeat_test

# Apply schema
DATABASE_URL="postgresql://localhost/teambeat_test" npm run db:push:postgres

# Run application
DATABASE_URL="postgresql://localhost/teambeat_test" npm run dev
    • Repeat all feature tests
    • Verify boolean fields work correctly
    • Verify timestamps display properly
    • Test transactions complete successfully

Phase 4: PostgreSQL Connection Support ✅

Database connection abstraction implemented:

Files Created:

  • drizzle.config.sqlite.ts - SQLite migration configuration
  • drizzle.config.postgres.ts - PostgreSQL migration configuration

Files Modified:

  • src/lib/server/db/index.ts - Multi-database connection with runtime detection
  • package.json - Added database-specific scripts

New Dependencies:

  • postgres@3.4.7 - PostgreSQL driver

Migration Directories:

  • /drizzle/sqlite/ - SQLite migrations
  • /drizzle/postgres/ - PostgreSQL migrations

Both migration sets have been generated and are ready for use.

Usage

Development with SQLite (Default)

npm run dev
# Uses ./teambeat.db by default

Development with PostgreSQL

export DATABASE_URL="postgresql://user:password@localhost/teambeat"
npm run dev

Production Deployment

SQLite:

DATABASE_URL="./teambeat.db" npm run build
npm run preview

PostgreSQL:

DATABASE_URL="postgresql://user:pass@host:5432/teambeat" npm run build
npm run preview

Key Implementation Details

Automatic Database Detection

The database type is detected in two places:

  1. Schema Definition (src/lib/server/db/schema.ts):

    • Checks DATABASE_URL at module load time
    • Selects appropriate table builders (SQLite vs PostgreSQL)
    • Uses conditional booleanField() helper for type-safe boolean fields

  2. Database Connection (src/lib/server/db/index.ts):

    • Checks DATABASE_URL at module load time
    • Creates appropriate connection (better-sqlite3 vs postgres)
    • Returns unified Drizzle database interface

Schema Compatibility Approach

Boolean Fields:

  • PostgreSQL: Native boolean type
  • SQLite: integer with { mode: 'boolean' } (0/1 values, typed as boolean in TypeScript)

Timestamps:

  • Both databases: text fields with ISO 8601 strings
  • Default function: .$defaultFn(() => new Date().toISOString())
  • Application layer handles parsing when needed

Text and Integer Fields:

  • Fully compatible between databases
  • No special handling required

Migration Management

Migrations are database-specific and stored separately:

Generating new migrations after schema changes:

# SQLite
npm run db:generate:sqlite

# PostgreSQL (requires DATABASE_URL set)
DATABASE_URL="postgresql://localhost/teambeat" npm run db:generate:postgres

Applying schema to database:

# SQLite - creates tables directly from schema
npm run db:push:sqlite

# PostgreSQL - creates tables directly from schema
DATABASE_URL="postgresql://user:pass@host/db" npm run db:push:postgres

Breaking Changes

For Existing Installations

Important: The schema has changed. Existing databases must be migrated or recreated:

  1. Backup existing data if needed
  2. Apply new schema: 
    # For SQLite
npm run db:push:sqlite

The main schema change is the addition of multipleVotesPerCard to the scenes table, which is required by existing code but was missing from the schema.

Code Changes

All transaction code updated:

  • Old synchronous pattern: db.transaction((tx) => { ... tx.run() })
  • New async pattern: await db.transaction(async (tx) => { ... await tx... })

This change maintains SQLite compatibility while enabling PostgreSQL support.

Documentation Updates

  • README.md - Updated with PostgreSQL quick start and database commands
  • DEVELOPMENT.md - Complete database architecture section with multi-database details
  • Both files include examples for SQLite and PostgreSQL usage

Next Steps

  1. Manual Testing (Phase 3) - Required before production use
  2. Production Deployment Planning:
    • Choose database based on scale requirements
    • SQLite: Simple, single-instance deployments
    • PostgreSQL: Multi-instance, high-availability deployments
  3. Database Backups:
    • SQLite: File-based backups
    • PostgreSQL: pg_dump or replication

Files Changed Summary

Modified (7 files):

  • src/lib/server/db/schema.ts - Multi-database schema with conditional types
  • src/lib/server/db/index.ts - Multi-database connection abstraction
  • src/lib/server/repositories/board.ts - Async transactions
  • src/lib/server/repositories/board-series.ts - Async transactions
  • src/lib/server/repositories/scene.ts - Async transactions
  • src/routes/api/boards/[id]/clone/+server.ts - Async transactions
  • src/routes/api/boards/[id]/setup-template/+server.ts - Async transactions
  • package.json - Database-specific scripts
  • README.md - PostgreSQL documentation
  • DEVELOPMENT.md - Multi-database architecture documentation

Created (3 files):

  • drizzle.config.sqlite.ts - SQLite Drizzle Kit configuration
  • drizzle.config.postgres.ts - PostgreSQL Drizzle Kit configuration
  • POSTGRES_IMPLEMENTATION.md - This file

Migrations Generated:

  • drizzle/sqlite/0000_curved_dragon_lord.sql - Fresh SQLite schema
  • drizzle/postgres/0000_mature_peter_parker.sql - Fresh PostgreSQL schema

Testing Checklist

Before marking this implementation complete, verify:

  • Application starts with SQLite (default)
  • Application starts with PostgreSQL (DATABASE_URL set)
  • All CRUD operations work in both databases
  • Boolean fields behave correctly in both databases
  • Timestamps display correctly in both databases
  • Transactions complete successfully in both databases
  • SSE events broadcast correctly in both databases
  • Board cloning works in both databases
  • No TypeScript errors in build
  • No runtime errors in either database mode

Support

For questions or issues related to PostgreSQL support:

  1. Check DEVELOPMENT.md for database architecture details
  2. Review migration files in drizzle/sqlite/ and drizzle/postgres/
  3. Verify DATABASE_URL is correctly formatted for your database choice