Database testing strategies and fixtures

                  -- Create test database
CREATE DATABASE myapp_test;

-- Test isolation with transactions
/*
beforeEach(async () => {
  await db.query('BEGIN');
});

afterEach(async () => {
  await db.query('ROLLBACK');
});

test('creates user', async () => {
  await db.query('INSERT INTO users (username) VALUES ($1)', ['test']);
  const result = await db.query('SELECT * FROM users WHERE username = $1', ['test']);
  expect(result.rows).toHaveLength(1);
}); // Rolled back automatically
*/

-- Test fixtures (seed data)
CREATE TABLE test_users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) UNIQUE,
  email VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Load fixtures
TRUNCATE test_users CASCADE;

INSERT INTO test_users (id, username, email) VALUES
(1, 'alice', 'alice@test.com'),
(2, 'bob', 'bob@test.com'),
(3, 'carol', 'carol@test.com');

-- Reset sequence
SELECT setval('test_users_id_seq', (SELECT MAX(id) FROM test_users));

-- Factory function
CREATE OR REPLACE FUNCTION create_test_user(
  username_prefix TEXT DEFAULT 'user'
)
RETURNS test_users AS $$
DECLARE
  new_user test_users;
BEGIN
  INSERT INTO test_users (username, email)
  VALUES (
    username_prefix || '_' || floor(random() * 1000000),
    username_prefix || '@test.com'
  )
  RETURNING * INTO new_user;

  RETURN new_user;
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT * FROM create_test_user('testuser');

-- Clean database helper
CREATE OR REPLACE FUNCTION truncate_all_tables()
RETURNS VOID AS $$
DECLARE
  table_name TEXT;
BEGIN
  FOR table_name IN
    SELECT tablename
    FROM pg_tables
    WHERE schemaname = 'public'
  LOOP
    EXECUTE format('TRUNCATE TABLE %I CASCADE', table_name);
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Reset database for tests
SELECT truncate_all_tables();

-- Test data builder pattern
CREATE OR REPLACE FUNCTION build_test_order(
  p_user_id INT DEFAULT NULL,
  p_total DECIMAL DEFAULT 99.99,
  p_status VARCHAR DEFAULT 'pending'
)
RETURNS orders AS $$
DECLARE
  v_user_id INT;
  new_order orders;
BEGIN
  IF p_user_id IS NULL THEN
    v_user_id := (SELECT id FROM create_test_user('order_user'));
  ELSE
    v_user_id := p_user_id;
  END IF;

  INSERT INTO orders (user_id, total, status)
  VALUES (v_user_id, p_total, p_status)
  RETURNING * INTO new_order;

  RETURN new_order;
END;
$$ LANGUAGE plpgsql;

-- Create order with defaults
SELECT * FROM build_test_order();

-- Create order with specific values
SELECT * FROM build_test_order(1, 199.99, 'completed');

-- Assert functions for tests
CREATE OR REPLACE FUNCTION assert_equals(
  expected ANYELEMENT,
  actual ANYELEMENT,
  message TEXT DEFAULT 'Assertion failed'
)
RETURNS VOID AS $$
BEGIN
  IF expected IS DISTINCT FROM actual THEN
    RAISE EXCEPTION '% - Expected: %, Actual: %',
      message, expected, actual;
  END IF;
END;
$$ LANGUAGE plpgsql;

-- Test example
DO $$
DECLARE
  user_count INT;
BEGIN
  SELECT COUNT(*) INTO user_count FROM test_users;
  PERFORM assert_equals(3, user_count, 'User count should be 3');
END $$;

-- Snapshot testing (save and compare)
CREATE TABLE test_snapshots (
  test_name VARCHAR(100) PRIMARY KEY,
  snapshot JSONB,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Save snapshot
INSERT INTO test_snapshots (test_name, snapshot)
VALUES (
  'user_list',
  (SELECT jsonb_agg(row_to_json(u.*)) FROM test_users u)
);

-- Compare snapshot
DO $$
DECLARE
  current_data JSONB;
  saved_snapshot JSONB;
BEGIN
  SELECT jsonb_agg(row_to_json(u.*)) INTO current_data
  FROM test_users u;

  SELECT snapshot INTO saved_snapshot
  FROM test_snapshots
  WHERE test_name = 'user_list';

  IF current_data IS DISTINCT FROM saved_snapshot THEN
    RAISE EXCEPTION 'Snapshot mismatch for user_list';
  END IF;
END $$;