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);
    });
});

Returning the row counts a write reported

getCount queries a schema after the fact. To get the number of rows a write itself touched, set returnRowCountsAs on the write. The counts come back under that name in result.returnedRowCounts.

returnRowCountsAs is accepted by merge, upsert, insert, replace, patch, delete, deleteRecord, and insertRecord.

describe("Row counts demo", () => {
    it("should report what each write touched", async () => {
      const result = await DepotTest.in(petSchema)
        .upsert({
          data: {
            "source.pets": [
              { id: 2, species: "C. familiaris", name: "Geoff", age: 2 },
              { id: 3, species: "Canis familiaris", name: "Minnie", age: 5 },
            ],
          },
          returnRowCountsAs: "upserted",
        })
        .run();

      const [upserted] = result.returnedRowCounts["upserted"];
      expect(upserted.rowsAffected).toBe(2);
    });
});

Each name maps to a list, one entry per transaction request the step made. An entry carries:

Field	Meaning
rowsAffected	Rows touched by the write, or null if the count could not be attributed. null never means 0.
counts	Per-verb breakdown (inserted, updated, deleted) where the engine supplies one.
operation	The operation that ran, in lower case (e.g. 'upsert', 'insert-ignore', 'delete-record').
target	The schema written, when the request named one; null for a write given only data or dataUri.
actionIndex	Index of the transaction action.

rowsAffected counts rows touched, not the net change: a merge that updates 3 rows and deletes 2 reports 5. A write that matches nothing, such as a delete of rows that are not there, reports 0.

caution

counts depends on the engine. Snowflake reports inserted, updated and deleted; Postgres reports {} and carries the total in rowsAffected only. A test that asserts on counts only holds on Snowflake.