Skip to main content

Advanced: programmatic data I/O

The normal operation of DepotTest is to rely on static files (CSV, JSON, etc.) to provide input data and to compare the results of a test with a set of static files containing the expected results

It is possible to instruct DepotTest to use data available in client memory (TypeScript) and let the client perform the comparisons instead of DepotTest. This is useful when the expected results are not static, for example when the expected results are computed by the client.

Programmatic Data Input​

Any place that accepts a dataUri field (setContent(), transaction(), check()) can be provided with a data field instead.

The data field is an object whose keys are the names of the schemas to be loaded, and whose values are arrays of objects (that should conform to the schema as Depot knows it):

describe("Programmatic Input demo", () => {
    it("should retrieve contents for programmatic testing", async () => {
      const scenarioDirectory = path.join(testObjectDirectory, "assert05");

      const scenario = DepotTest.in(petSchema)
        .setContent({
            data: {
                "source.pets": [
                    { id: 100, species: "Felis catus", breed: "Siamese", name: "Maki" },
                    { id: 101, species: "F. catus", breed: "Orange gutter", name: "Tysha" },
                    { id: 1, species: "Canis familiaris", breed: "Poodle", age: 3, name: "Toodles" },
                    { id: 2, species: "C. familiaris", breed: "Labrador", age: 1, name: "Geoff" },
                ]
            },
        })
       .check({
           expectedUri: path.join(scenarioDirectory, "expected"),
           actual: [
               {
                   schema: "views.dogs"
               },
               {
                   schema: "views.cats"
               }
           ]
       })

       await scenario.run();
    })
});

Checking data against inline expectations​

Instead of supplying the expected data as a set of files, it is possible to supply it inline as an array of objects per schema to be compared:

import { TestCheckSortDirection } from "@stage-tech/depot-test";

describe("Programmatic Expectation Data demo", () => {
  it("should retrieve contents for programmatic testing", async () => {
    const scenarioDirectory = path.join(testObjectDirectory, "assert05");

    const scenario = DepotTest.in(petSchema)
      .setContent(path.join(scenarioDirectory, "data"))
      .check({
        data: [
          {
            scope: {
              schema: "views.cats"
            },
            values: [
              {name: "Maki", id: "100", breed: "Siamese", species: "Felis catus"},
              {name: "Tysha", id: "101", breed: "Orange gutter", species: "F. catus"}
            ]
          },
          {
            scope: CheckContent.bySchema("views.dogs").orderBy([{ field: "name", direction: TestCheckSortDirection.DESCENDING }]),
            values: [
              {name: "Toodles", id: "1", breed: "Poodle", species: "Canis familiaris"},
              {name: "Geoff", id: "2", breed: "Labrador", species: "C. familiaris"}
            ]
          }
        ],
      });

    await scenario.run();
  })
  });
tip

The orderBy clause is used to sort, before applying the comparison algorithm which tries to not be too dependent on the ordering of rows. It should not make a difference in successful cases, but it may help reduce the amount of noise in case of error

Checking counts and existence​

Sometimes a test can be effective by only checking whether a certain number of rows exists, or whether rows exists or not.

This may be cheaper to execute, as there is a need for less data on the side of the database, and next to no communication between the database and the backend, and between the backend and the frontend of depot-test.

tip

if the precise count is not necessary, consider checking for existence. It is simpler for the database, as the existence is proven as soon as any row passes filtering.

The syntax is as follows:

describe("Counts and Existence demo", () => {
    it("should retrieve counts or simple existence for programmatic testing", async () => {
        const scenarioDirectory = path.join(testObjectDirectory, "assert05");

        const scenario = DepotTest.in(petSchema)
            .setContent(path.join(scenarioDirectory, "data"))
            .check({
                count: [
                    {
                        scope: {
                            schema: "views.cats"
                        },
                        expected: 2
                    },
                    {
                        scope: {
                            schema: "views.dogs"
                        },
                        expected: 7
                    }
                ],
            })
            .check({
                exists: [
                    {
                        scope: {
                            schema: "views.dogs"
                        },
                        expected: true // redundant, we've tested count=7 above
                    },
                    {
                        scope: {
                            schema: "views.chicken"
                        },
                        expected: false // would be disproved as soon as you have one in your input data
                    }
                ],
            })

        await scenario.run();
    })
});

Programmatic Data Output​

It is possible to request that a schema (table, view, query or S3 stage) is extracted from the database and brought back to the client for custom analysis.

This works in two steps:

  1. in your DepotTest scenario, use the DepotTest.getContent() method to request the content of one or more schemas to be brought back.
  2. When you call DepotTest.run(), the requested schema contents will be provided under the returnedContent field of the result
caution

Performing the comparison in TypeScript using jest may appear more convenient but may turn out to be slower than letting the backend perform the comparison. Make sure to use the smallest dataset that is sufficient to test your scenario.

tip

it is possible to combine programmatic input and output. For instance, you may want to generate random input, supply it to the database, and check that the database applied a certain transform for which you have an equivalent reference implementation in your test code.

The following example demonstrates this:

describe("Programmatic Output demo", () => {
    it("should retrieve contents for programmatic testing", async () => {
      const scenarioDirectory = path.join(testObjectDirectory, "assert05");
    
      const scenario = DepotTest.in(petSchema)
        .setContent(path.join(scenarioDirectory, "data"))
        .getContent({
          schemas: ["views.dogs", "views.cats"],
          outputFieldNameConvention: FieldNameConvention.DATABASE,
        });
    
      const result = await scenario.run();
    
      const dogsByName = Object.fromEntries(
        result.returnedContent["views.dogs"].map((dog: any) => [dog.NAME as string, dog])
      );
      expect(dogsByName["Geoff"]).toMatchObject({
        AGE: 1,
        ID: "2",
        NAME: "Geoff",
        SPECIES: "C. familiaris",
        BREED: "Labrador"
      });
      expect(dogsByName).toHaveProperty("Jimmie");

      expect(result.returnedContent["views.cats"]).toMatchObject([
          { NAME: "Maki", ID: "100", BREED: "Siamese", SPECIES: "Felis catus" },
          { NAME: "Tysha", ID: "101", BREED: "Orange gutter", SPECIES: "F. catus" }
      ]);
    });
});

note

The outputFieldNameConvention parameter is described on the next section.

Query arguments​

Query arguments can be provided via GetContent.bySchema(sourceSchema, arguments) parameter:

describe("Programmatic Output of Query with arguments demo", () => {
    it("should retrieve contents for programmatic testing", async () => {
      const scenarioDirectory = path.join(testObjectDirectory, "assert06");
    
      const scenario = DepotTest.in(petSchema)
        .setContent(path.join(scenarioDirectory, "data"))
        .getContent({
            schemas: {
                "views.dogs": GetContent.all,
                "cats": GetContent.bySchema("views.cats"),
                "dogs": GetContent.bySchema("queries.dogQuery", { argStr: "foo", argBool: true, argInt: 5 })
            }
        });
      ...
    });
});

Ordering of returned rows​

It is possible to specify the ordering of returned rows using the .orderBy() method on the things returned by the builders in GetContent:

import { TestCheckSortDirection } from "@stage-tech/depot-test";

describe("Programmatic Output of Query with arguments demo", () => {
  it("should retrieve contents for programmatic testing", async () => {
    const scenarioDirectory = path.join(testObjectDirectory, "assert06");

    const scenario = DepotTest.in(petSchema)
      .setContent(path.join(scenarioDirectory, "data"))
      .getContent({
        schemas: {
          "views.dogs": GetContent.all,
          "cats": GetContent.bySchema("views.cats").orderBy("age", "name"),
          "dogs": GetContent.bySchema("queries.dogQuery", {argStr: "foo", argBool: true, argInt: 5})
            .orderBy([
              {field: "updated", direction: TestCheckSortDirection.DESCENDING },
              {field: "id", direction: TestCheckSortDirection.ASCENDING },
              ])
        }
      });
  ...
  });
});

In this example,

  • the cats result will contain the cats ordered by age, and for cats of the same age, by name
  • the dogs result will contain the dogs ordered by the most recently updated, then within a same date, by ascending id
info

if no ordering is specified, and the schema is an Object or has an id property, the rows will be ordered by id ascending by default. You can opt out of this by specifying an empty array of ordering fields.

Returning only counts or existence​

It is possible to instruct depot-test to return only the count of rows, or the facts that rows exist or not, for TypeScript-side validation.

The following example demonstrates this:

describe("Programmatic Output demo", () => {
    it("should retrieve contents for programmatic testing", async () => {
      const scenarioDirectory = path.join(testObjectDirectory, "assert05");
    
      const scenario = DepotTest.in(petSchema)
        .setContent(path.join(scenarioDirectory, "data"))
        .getCount({
          schemas: {
              "cat.count": GetContent.bySchema("views.cats"),
              "dog": GetContent.bySchema("views.dogs")
          }
        })
       .getExists({
           schemas: {
               "views.dogs": GetContent.all,
               "chicken": GetContent.bySchema("views.chicken"),
           }
       });
    
      const result = await scenario.run();

      const catCount: number = result.returnedCount["cat.count"];
      expect(catCount).toBe(2);
      expect(result.returnedCount.dog).toBe(7);

      const dogsExist: boolean = result.returnedExists["views.dogs"];
      expect(dogsExist).toBe(true);
      expect(result.returnedExists.chicken).toBe(false);
    });
});