Re-usable code with includes

Introduction

JavaScript files can be added to the includes/ folder to define simple scripts, constants or macros that can be reused across your project. Each file in the includes folder will be made available for use within your other SQL or JavaScript files.

It's also possible to add JavaScript to a .sqlx file by wrapping it in a js {...} block. Note: Functions, constants or macros defined in this way will only be available within the .sqlx file they are defined in, not across the whole project.

If you are new to JavaScript, the examples below should cover some common use cases. MDN is a useful learning resource if you'd like to learn more.

Example: Defining and using constants

Create a new file in your project under the includes/ folder, such as:

definitions/constants.js:

const PROJECT_ID = "my_project_name";
module.exports = { PROJECT_ID };
Note that in order to use functions or constants elsewhere in the project, they must be exported using the module.exports = {} syntax.

Example: Using an include

You can reference any include function, constant, or macro by using its file name without the .js extension, followed by the name of the exported function or constant.

For example, to reference the constant PROJECT_ID in the file includes/constants.js:

definitions/query.sqlx:

SELECT * FROM ${constants.PROJECT_ID}.my_schema_name.my_table_name

The query will be compiled into the following SQL before it is run:

SELECT * FROM my_project_name.my_schema_name.my_table_name

Example: Writing functions

Functions enable you to reuse the same block of SQL logic across many different scripts. Functions take 0 or more named parameters and must return a string.

In the example below, the function countryGroup() takes as input the name of the country code field and returns a CASE statement that maps country codes to country groups.

includes/country_mapping.js:

function countryGroup(countryCodeField) {
  return `CASE
          WHEN ${countryCodeField} IN ("US", "CA") THEN "NA"
          WHEN ${countryCodeField} IN ("GB", "FR", "DE", "IT", "PL") THEN "EU"
          WHEN ${countryCodeField} IN ("AU") THEN ${countryCodeField}
          ELSE "Other countries"
          END`;
}

module.exports = { countryGroup };

This function can be used in a SQL query definitions/revenue_by_country_group.sqlx:

SELECT
  ${country_mapping.countryGroup("country_code")} AS country_group,
  SUM(revenue) AS revenue
FROM my_schema.revenue_by_country
GROUP BY 1

The query will be compiled into the following SQL before it is run:

SELECT
  CASE
    WHEN country_code IN ("US", "CA") THEN "NA"
    WHEN country_code IN ("GB", "FR", "DE", "IT", "PL") THEN "EU"
    WHEN country_code IN ("AU") THEN country_code
    ELSE "Other countries"
  END AS country_group,
  SUM(revenue) AS revenue
FROM my_schema.revenue_by_country
GROUP BY 1

Example: An include with parameters: groupBy

The example below defines a groupBy() function that takes as input a number of fields to group by and generates a corresponding GROUP BY statement:

includes/utils.js:

function groupBy(n) {
  var indices = [];
  for (var i = 1; i <= n; i++) {
    indices.push(i);
  }
  return `GROUP BY ${indices.join(", ")}`;
}

module.exports = { groupBy };

This function can be used in a SQL query definitions/example.sqlx:

SELECT field1,
       field2,
       field3,
       field4,
       field5,
       SUM(revenue) AS revenue
FROM my_schema.my_table
${groupBy(5)}

The query will be compiled into the following SQL before it is run:

SELECT field1,
       field2,
       field3,
       field4,
       field5,
       SUM(revenue) AS revenue
FROM my_schema.my_table
GROUP BY 1, 2, 3, 4, 5

Example: Generating queries

Functions can be used to generate entire queries. This is a powerful feature that can be useful if you need to create several datasets which share a similar structure.

The example below includes a function that aggregates all metrics (using SUM) and groups by every dimension.

includes/script_builder.js:

function renderScript(table, dimensions, metrics) {
  return `
      SELECT
      ${dimensions.map(field => `${field} AS ${field}`).join(",\n")},
      ${metrics.map(field => `SUM(${field}) AS ${field}`).join(",\n")}
      FROM ${table}
      GROUP BY ${dimensions.map((field, i) => `${i + 1}`).join(", ")}
    `;
}
module.exports = { renderScript };

This function can be used in a SQL query definitions/stats_per_country_and_device.sqlx:

${script_builder.renderScript(
  ref("source_table"),
  ["country", "device_type"],
  ["revenue", "pageviews", "sessions"])}

Note that calls to functions such as ref() should be made in the SQL file itself and passed to the include function so that dependencies are configured correctly.

The query will be compiled into the following SQL before it is run:

SELECT country AS country,
       device_type AS device_type,
       SUM(revenue) AS revenue,
       SUM(pageviews) AS pageviews,
       SUM(sessions) AS sessions
FROM my_schema.source_table
GROUP BY 1, 2