← back to Jill Website

src/__tests__/database.test.ts

326 lines

import { Pool } from 'pg';
import { testConnection, closePool } from '../config/database';
import { getTestPool } from './setup';

describe('Database Connection Tests', () => {
  describe('testConnection', () => {
    it('should successfully connect to test database', async () => {
      const result = await testConnection();
      expect(result).toBe(true);
    });

    it('should handle connection timeout gracefully', async () => {
      // Create a pool with invalid connection settings
      const invalidPool = new Pool({
        host: 'invalid-host-that-does-not-exist.example.com',
        port: 5432,
        database: 'test_db',
        user: 'test_user',
        password: 'test_pass',
        connectionTimeoutMillis: 1000,
      });

      try {
        await invalidPool.query('SELECT NOW()');
        fail('Should have thrown an error');
      } catch (error) {
        expect(error).toBeDefined();
        // Connection errors are always Error objects
        expect(error instanceof Error || typeof error === 'object').toBe(true);
      } finally {
        await invalidPool.end();
      }
    });

    it('should handle authentication errors', async () => {
      // Create a pool with invalid credentials
      const invalidPool = new Pool({
        host: process.env.TEST_DB_HOST || 'localhost',
        port: parseInt(process.env.TEST_DB_PORT || '5432'),
        database: process.env.TEST_DB_NAME || 'jill_website_test',
        user: 'invalid_user',
        password: 'invalid_password',
        connectionTimeoutMillis: 2000,
      });

      try {
        await invalidPool.query('SELECT NOW()');
        fail('Should have thrown an authentication error');
      } catch (error) {
        expect(error).toBeDefined();
        expect(error instanceof Error).toBe(true);
      } finally {
        await invalidPool.end();
      }
    });

    it('should handle database does not exist error', async () => {
      // Create a pool with non-existent database
      const invalidPool = new Pool({
        host: process.env.TEST_DB_HOST || 'localhost',
        port: parseInt(process.env.TEST_DB_PORT || '5432'),
        database: 'non_existent_database_12345',
        user: process.env.TEST_DB_USER || 'postgres',
        password: process.env.TEST_DB_PASSWORD || '',
        connectionTimeoutMillis: 2000,
      });

      try {
        await invalidPool.query('SELECT NOW()');
        fail('Should have thrown a database error');
      } catch (error) {
        expect(error).toBeDefined();
        expect(error instanceof Error).toBe(true);
      } finally {
        await invalidPool.end();
      }
    });
  });

  describe('closePool', () => {
    it('should close database connection pool', async () => {
      await closePool();
      // After closing, the pool should be able to be recreated
      const result = await testConnection();
      expect(result).toBe(true);
    });

    it('should handle closing when no pool exists', async () => {
      // Close once
      await closePool();
      // Close again should not throw error
      await expect(closePool()).resolves.not.toThrow();
    });
  });

  describe('Query Error Handling', () => {
    it('should handle invalid SQL query', async () => {
      const pool = getTestPool();

      try {
        await pool.query('INVALID SQL QUERY');
        fail('Should have thrown a SQL syntax error');
      } catch (error) {
        expect(error).toBeDefined();
        expect(error instanceof Error).toBe(true);
        expect((error as Error).message).toContain('syntax error');
      }
    });

    it('should handle query on non-existent table', async () => {
      const pool = getTestPool();

      try {
        await pool.query('SELECT * FROM non_existent_table_12345');
        fail('Should have thrown a table does not exist error');
      } catch (error) {
        expect(error).toBeDefined();
        expect(error instanceof Error).toBe(true);
        expect((error as Error).message).toContain('does not exist');
      }
    });

    it('should handle query with invalid column', async () => {
      const pool = getTestPool();

      try {
        await pool.query('SELECT non_existent_column FROM properties');
        fail('Should have thrown a column does not exist error');
      } catch (error) {
        expect(error).toBeDefined();
        expect(error instanceof Error).toBe(true);
        expect((error as Error).message).toContain('does not exist');
      }
    });

    it('should handle parameterized query with missing parameters', async () => {
      const pool = getTestPool();

      try {
        await pool.query('SELECT * FROM properties WHERE id = $1');
        fail('Should have thrown a parameter error');
      } catch (error) {
        expect(error).toBeDefined();
        expect(error instanceof Error).toBe(true);
      }
    });

    it('should handle type mismatch in query parameters', async () => {
      const pool = getTestPool();

      try {
        // Try to insert invalid data type
        await pool.query(
          'INSERT INTO properties (name, description, amenities, images, airbnb_url, capacity) VALUES ($1, $2, $3, $4, $5, $6)',
          ['Test', 'Description', '["WiFi"]', '["image.jpg"]', 'https://airbnb.com/test', 'not-a-number']
        );
        fail('Should have thrown a type error');
      } catch (error) {
        expect(error).toBeDefined();
        expect(error instanceof Error).toBe(true);
      }
    });
  });

  describe('Transaction Error Handling', () => {
    it('should handle transaction rollback on error', async () => {
      const pool = getTestPool();
      const client = await pool.connect();

      try {
        await client.query('BEGIN');

        // Insert valid property
        await client.query(
          'INSERT INTO properties (name, description, amenities, images, airbnb_url) VALUES ($1, $2, $3, $4, $5)',
          ['Test Property', 'Description', '["WiFi"]', '["https://example.com/img.jpg"]', 'https://airbnb.com/test']
        );

        // Force an error with invalid SQL
        await client.query('INVALID SQL');

        await client.query('COMMIT');
        fail('Should have thrown an error');
      } catch (error) {
        await client.query('ROLLBACK');
        expect(error).toBeDefined();

        // Verify that the property was not inserted
        const result = await pool.query('SELECT COUNT(*) FROM properties');
        expect(parseInt(result.rows[0].count)).toBe(0);
      } finally {
        client.release();
      }
    });

    it('should handle nested transaction errors', async () => {
      const pool = getTestPool();
      const client = await pool.connect();

      try {
        await client.query('BEGIN');
        await client.query('SAVEPOINT sp1');

        // This should fail
        await client.query('SELECT * FROM non_existent_table');

        await client.query('RELEASE SAVEPOINT sp1');
        await client.query('COMMIT');
        fail('Should have thrown an error');
      } catch (error) {
        await client.query('ROLLBACK');
        expect(error).toBeDefined();
      } finally {
        client.release();
      }
    });
  });

  describe('Connection Pool Management', () => {
    it('should handle multiple concurrent queries', async () => {
      const pool = getTestPool();

      const queries = Array.from({ length: 10 }, (_, i) =>
        pool.query('SELECT $1::int as number', [i])
      );

      const results = await Promise.all(queries);

      expect(results).toHaveLength(10);
      results.forEach((result, index) => {
        expect(parseInt(result.rows[0].number)).toBe(index);
      });
    });

    it('should handle query after pool exhaustion attempt', async () => {
      const pool = getTestPool();

      // Create many concurrent queries
      const queries = Array.from({ length: 30 }, (_, i) =>
        pool.query('SELECT $1 as number', [i])
      );

      await Promise.all(queries);

      // Should still be able to query after high load
      const result = await pool.query('SELECT NOW()');
      expect(result.rows).toHaveLength(1);
    });

    it('should release client after error', async () => {
      const pool = getTestPool();
      const client = await pool.connect();

      try {
        await client.query('INVALID SQL');
      } catch (error) {
        // Expected error
      } finally {
        client.release();
      }

      // Should still be able to get a client after error
      const newClient = await pool.connect();
      const result = await newClient.query('SELECT 1');
      expect(result.rows).toHaveLength(1);
      newClient.release();
    });
  });

  describe('Data Integrity', () => {
    it('should handle JSONB data correctly', async () => {
      const pool = getTestPool();

      const amenities = ['WiFi', 'Pool', 'Kitchen'];
      const images = ['https://example.com/1.jpg', 'https://example.com/2.jpg'];

      await pool.query(
        'INSERT INTO properties (name, description, amenities, images, airbnb_url) VALUES ($1, $2, $3, $4, $5)',
        ['Test', 'Description', JSON.stringify(amenities), JSON.stringify(images), 'https://airbnb.com/test']
      );

      const result = await pool.query('SELECT amenities, images FROM properties WHERE name = $1', ['Test']);

      expect(result.rows).toHaveLength(1);
      const row = result.rows[0];
      expect(Array.isArray(row.amenities)).toBe(true);
      expect(Array.isArray(row.images)).toBe(true);
      expect(row.amenities).toEqual(amenities);
      expect(row.images).toEqual(images);
    });

    it('should handle date/time fields correctly', async () => {
      const pool = getTestPool();

      const checkIn = new Date('2026-06-01');
      const checkOut = new Date('2026-06-07');

      await pool.query(
        'INSERT INTO inquiries (name, email, phone, check_in_date, check_out_date, guest_count, message) VALUES ($1, $2, $3, $4, $5, $6, $7)',
        ['John Doe', 'john@example.com', '+1-555-1234', checkIn, checkOut, 4, 'Test message']
      );

      const result = await pool.query('SELECT check_in_date, check_out_date FROM inquiries WHERE email = $1', ['john@example.com']);

      expect(result.rows).toHaveLength(1);
      expect(new Date(result.rows[0].check_in_date).toISOString().split('T')[0]).toBe('2026-06-01');
      expect(new Date(result.rows[0].check_out_date).toISOString().split('T')[0]).toBe('2026-06-07');
    });

    it('should handle decimal precision correctly', async () => {
      const pool = getTestPool();

      await pool.query(
        'INSERT INTO properties (name, description, amenities, images, airbnb_url, size_sqm, price_per_night) VALUES ($1, $2, $3, $4, $5, $6, $7)',
        ['Test', 'Description', '["WiFi"]', '["https://example.com/img.jpg"]', 'https://airbnb.com/test', 123.45, 678.99]
      );

      const result = await pool.query('SELECT size_sqm, price_per_night FROM properties WHERE name = $1', ['Test']);

      expect(result.rows).toHaveLength(1);
      expect(parseFloat(result.rows[0].size_sqm)).toBe(123.45);
      expect(parseFloat(result.rows[0].price_per_night)).toBe(678.99);
    });
  });
});