- Updated Prisma schema to use PostgreSQL provider
- Modified Dockerfile to bundle PostgreSQL 16 in the image
- Simplified init-database.js for PostgreSQL only
- Updated environment variables in .env and docker-compose.yml
- Created PostgreSQL migration files
- Configured automatic PostgreSQL initialization on container start
The application now includes a bundled PostgreSQL database that automatically
initializes on first run, eliminating the need for external database setup.
Root Cause Analysis:
The init-database.js script had a duplicate 'fs' constant declaration on line 78
within the testPostgreSQLConnection function. Since 'fs' was already imported
at the module level (line 3), this redeclaration caused a ReferenceError:
"Cannot access 'fs' before initialization" when attempting PostgreSQL connections.
Why PR #26 didn't suffice:
PR #26 successfully added PostgreSQL support and the connection logic was sound.
However, during subsequent refactoring to improve the connection test mechanism,
a duplicate 'const fs = require('fs')' was inadvertently added inside the
testPostgreSQLConnection function. This scoping issue prevented the PostgreSQL
connection test from executing properly, causing all PostgreSQL connections to
fail and fall back to SQLite.
Resolution:
Removed the duplicate fs declaration from line 78, allowing the function to use
the module-level fs import. This restores full PostgreSQL connectivity.
Fixes#30
Regenerate Prisma client immediately after updating schema provider to ensure
the generated client matches the database type being used. This resolves the
issue where PostgreSQL URLs were being used with a SQLite-generated client,
causing 'URL must start with protocol file:' errors.
Changes:
- Generate Prisma client right after updateSchemaProvider() in both SQLite and PostgreSQL initialization
- Update PostgreSQL connection test to use correct provider during testing
- Remove redundant prisma generate call at the end of initialization
This ensures the Prisma client binary always matches the active database provider
before any database operations are attempted.
Fixes#23
- Add comprehensive database detection and initialization system
- Support both SQLite (default) and PostgreSQL with automatic fallback
- Create database initialization script with connection testing
- Add SSL certificate handling for managed PostgreSQL databases (DigitalOcean)
- Update Dockerfile to use conditional database initialization at runtime
- Add database management scripts to package.json (db:init, db:migrate, etc.)
- Create comprehensive DATABASE.md documentation with setup examples
- Update README.md with database configuration information
- Add environment configuration examples for both database types
Key features:
- Automatic provider detection from DATABASE_URL environment variable
- Graceful fallback to SQLite when PostgreSQL connection fails
- Proper SSL handling for cloud PostgreSQL services
- Runtime schema provider switching for optimal compatibility
- Production-ready Docker deployment with external database support
- Maintains 100% backward compatibility with existing SQLite deployments
Technical implementation:
- Database detection utility functions with connection testing
- Dynamic Prisma schema provider updates based on detected database
- Separate initialization strategies for SQLite vs PostgreSQL
- Error handling and logging for troubleshooting connection issues
- Support for DigitalOcean managed databases with self-signed certificates
