Shatteredvoid/verify-db-connection.js

#!/usr/bin/env node

/**
 * PostgreSQL Connection Verification Script
 * This script verifies the database connection using the current environment configuration
 */

require('dotenv').config({ path: `.env.${process.env.NODE_ENV || 'development'}` });
const { Client } = require('pg');

async function verifyConnection() {
    console.log('🔍 PostgreSQL Connection Verification');
    console.log('=====================================');
    console.log(`Environment: ${process.env.NODE_ENV || 'development'}`);
    console.log(`Host: ${process.env.DB_HOST || 'localhost'}`);
    console.log(`Port: ${process.env.DB_PORT || 5432}`);
    console.log(`Database: ${process.env.DB_NAME || 'shattered_void_dev'}`);
    console.log(`User: ${process.env.DB_USER || 'postgres'}`);
    console.log(`Password: ${'*'.repeat((process.env.DB_PASSWORD || 'password').length)}`);
    console.log('=====================================\n');

    const client = new Client({
        host: process.env.DB_HOST || 'localhost',
        port: process.env.DB_PORT || 5432,
        database: process.env.DB_NAME || 'shattered_void_dev',
        user: process.env.DB_USER || 'postgres',
        password: process.env.DB_PASSWORD || 'password',
    });

    try {
        console.log('🔌 Attempting to connect...');
        await client.connect();
        console.log('✅ Connected successfully!');

        console.log('\n🔍 Testing basic queries...');
        
        // Test 1: Get PostgreSQL version
        const versionResult = await client.query('SELECT version()');
        console.log('✅ Version query successful');
        console.log(`   PostgreSQL Version: ${versionResult.rows[0].version.split(' ')[0]} ${versionResult.rows[0].version.split(' ')[1]}`);

        // Test 2: Check if we can create a test table (and clean it up)
        console.log('\n🧪 Testing table creation permissions...');
        await client.query(`
            CREATE TABLE IF NOT EXISTS connection_test_${Date.now()} (
                id SERIAL PRIMARY KEY,
                test_data VARCHAR(50)
            )
        `);
        console.log('✅ Table creation successful');

        // Test 3: Check database existence
        const dbCheckResult = await client.query(`
            SELECT datname FROM pg_database WHERE datname = $1
        `, [process.env.DB_NAME || 'shattered_void_dev']);
        
        if (dbCheckResult.rows.length > 0) {
            console.log('✅ Target database exists');
        } else {
            console.log('⚠️  Target database does not exist - you may need to create it');
        }

        // Test 4: Check for existing game tables
        console.log('\n🎮 Checking for existing game tables...');
        const tablesResult = await client.query(`
            SELECT table_name 
            FROM information_schema.tables 
            WHERE table_schema = 'public' 
            AND table_type = 'BASE TABLE'
            ORDER BY table_name
        `);

        if (tablesResult.rows.length > 0) {
            console.log('✅ Found existing tables:');
            tablesResult.rows.forEach(row => {
                console.log(`   - ${row.table_name}`);
            });
        } else {
            console.log('ℹ️  No game tables found - database may need to be migrated');
        }

        console.log('\n🎉 All connection tests passed!');
        console.log('🚀 Your PostgreSQL configuration is working correctly.');

    } catch (error) {
        console.error('\n❌ Connection failed!');
        console.error('Error details:');
        console.error(`   Type: ${error.code || 'Unknown'}`);
        console.error(`   Message: ${error.message}`);
        
        if (error.code === '28P01') {
            console.error('\n🔧 SOLUTION: Password authentication failed');
            console.error('   This means the password in your .env file doesn\'t match the PostgreSQL user password.');
            console.error('   Please run the sudo commands provided earlier to set the correct password.');
        } else if (error.code === 'ECONNREFUSED') {
            console.error('\n🔧 SOLUTION: Connection refused');
            console.error('   PostgreSQL service is not running. Try:');
            console.error('   sudo systemctl start postgresql');
        } else if (error.code === '3D000') {
            console.error('\n🔧 SOLUTION: Database does not exist');
            console.error('   Create the database with:');
            console.error(`   sudo -u postgres createdb ${process.env.DB_NAME || 'shattered_void_dev'}`);
        }
        
        process.exit(1);
    } finally {
        await client.end();
        console.log('\n🔌 Connection closed.');
    }
}

// Run the verification
if (require.main === module) {
    verifyConnection().catch(console.error);
}

module.exports = { verifyConnection };