database.test.ts

import { Client } from "pg";

it("has the database", async () => {
  const client = new Client({
    user: process.env.POSTGRES_USER,
    host: process.env.POSTGRES_HOST,
    password: process.env.POSTGRES_PASSWORD,
  });
  await client.connect();
  const query = await client.query(
    `SELECT * FROM pg_database WHERE datname='${process.env.POSTGRES_DB}'`
  );

  expect(query.rows.length).toBeGreaterThan(0);

  await client.end();
});

describe("Database Tests", () => {
  let client: Client;
  const databaseTables = [
    {
      table_name: "checkin",
      columns: [
        { column_name: "id", data_type: "uuid" },
        { column_name: "startTime", data_type: "timestamp with time zone" },
        {
          column_name: "estimatedEndTime",
          data_type: "timestamp with time zone",
        },
        { column_name: "boatId", data_type: "uuid" },
        { column_name: "createdAt", data_type: "timestamp with time zone" },
        { column_name: "updatedAt", data_type: "timestamp with time zone" },
        { column_name: "email", data_type: "character varying" },
        {
          column_name: "fullNameOfResponsableClient",
          data_type: "character varying",
        },
        { column_name: "additionalClients", data_type: "ARRAY" },
      ],
    },
    {
      table_name: "boat",
      columns: [
        { column_name: "status", data_type: "boolean" },
        { column_name: "createdAt", data_type: "timestamp with time zone" },
        { column_name: "updatedAt", data_type: "timestamp with time zone" },
        { column_name: "boattype", data_type: "uuid" },
        { column_name: "id", data_type: "uuid" },
        { column_name: "name", data_type: "character varying" },
        { column_name: "tags", data_type: "ARRAY" },
      ],
    },
    {
      table_name: "boattype",
      columns: [
        { column_name: "id", data_type: "uuid" },
        { column_name: "seats", data_type: "integer" },
        { column_name: "createdAt", data_type: "timestamp with time zone" },
        { column_name: "updatedAt", data_type: "timestamp with time zone" },
        { column_name: "name", data_type: "character varying" },
      ],
    },
    {
      table_name: "employee",
      columns: [
        { column_name: "id", data_type: "uuid" },
        { column_name: "createdAt", data_type: "timestamp with time zone" },
        { column_name: "updatedAt", data_type: "timestamp with time zone" },
        { column_name: "last_name", data_type: "character varying" },
        { column_name: "password", data_type: "character varying" },
        { column_name: "role", data_type: "character varying" },
        { column_name: "email", data_type: "character varying" },
        { column_name: "first_name", data_type: "character varying" },
      ],
    },
  ];
  beforeAll(async () => {
    client = new Client({
      user: process.env.POSTGRES_USER,
      host: process.env.POSTGRES_HOST,
      database: process.env.POSTGRES_DB,
      password: process.env.POSTGRES_PASSWORD,
    });
    await client.connect();
  });

  afterAll(async () => await client.end());

  it("has the correct tables", async () => {
    const query = await client.query(
      `SELECT table_name FROM information_schema.tables WHERE table_schema='public'`
    );
    const tableNames = query.rows.map((row) => row.table_name).sort();
    const shouldHaveTableNames = databaseTables
      .map((table) => table.table_name)
      .sort();

    expect(tableNames).toEqual(shouldHaveTableNames);
  });

  it.each(databaseTables)(
    "has correct columns for each table",
    async ({ table_name, columns }) => {
      const query = await client.query(
        `SELECT column_name, data_type FROM information_schema.columns WHERE table_name='${table_name}';`
      );
      const queryColumns = query.rows;

      for (let queryColumn of queryColumns) {
        expect(columns).toContainEqual(queryColumn);
      }
    }
  );

  it("has the initial employee", async () => {
    const query = await client.query(
      `SELECT * FROM employee WHERE email='${process.env.INITIAL_COORDINATOR_EMAIL}';`
    );

    expect(query.rows.length).toEqual(1);
  });
});