Prequel

Install

npm install @triptease/prequel

SQL Builder

Paramaters

import { BigQuery } from "@google-cloud/bigquery";
import { sql, param } from "./Prequel";

const searchTerm = param("term", "STRING[]");
const query = sql`
    SELECT COUNT(1)
    FROM ${"`bigquery-public-data.google_trends.top_terms`"}
    WHERE term IN UNNEST(${searchTerm})
  `;

const args = query.compile({ term: ["cat"] }); // correct

const [rows] = await new BigQuery().query(args);

// type safety
query.compile({ term: "cat" }); // throws a type error as string is not string[]
query.compile({ spellingMistake: "cat" }); // throws a type error as term is not provided

Composition

import { BigQuery } from "@google-cloud/bigquery";
import { sql, param } from "./Prequel";

const searchTerm = param("term", "STRING[]");
const requiresAterm = sql`
    SELECT *
    FROM ${"`bigquery-public-data.google_trends.top_terms`"}
    WHERE term IN UNNEST(${searchTerm})
  `;

const countRows = sql`
    WITH filteredRows as (${requiresAterm})

    SELECT *
    FROM filteredRows
  `;

const args = countRows.compile({ term: ["cat"] }); // correct

const [rows] = await new BigQuery().query(args);

const args = countRows.compile({}); // typeerror as the composed view requires term

Testing

Patching data

import { BigQuery } from "@google-cloud/bigquery";
import { Storage } from "@google-cloud/storage";
import { sql, param, BigQueryTestRunner } from "./Prequel";

jest.setTimeout(30_000);

it("should count terms", async () => {
  const searchTerm = param("term", "STRING[]");
  const query = sql`
    SELECT COUNT(1) as cnt
    FROM ${"`bigquery-public-data.google_trends.top_terms`"}
    WHERE term IN UNNEST(${searchTerm})
  `;

  const storage = new Storage().bucket("my-bucket");
  const bigQuery = new BigQuery();
  const runner = new BigQueryTestRunner(storage, bigQuery);
  runner.withPatch({
    uri: "`bigquery-public-data.google_trends.top_terms`",
    rows: [
      { term: "cat", score: 5 },
      { term: "dog", score: 2 },
    ],
  });

  const [rows] = await runner.query(query.compile({ term: ["cat"] }));
  expect(rows[0].cnt).toEqual(1);
});