Re-use 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:

includes/constants.js
1const PROJECT_ID = "my_project_name";
2module.exports = { PROJECT_ID };

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
1SELECT * FROM ${constants.PROJECT_ID}.my_schema_name.my_table_name

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

1SELECT * 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
1function countryGroup(countryCodeField) {
2  return `CASE
3          WHEN ${countryCodeField} IN ("US", "CA") THEN "NA"
4          WHEN ${countryCodeField} IN ("GB", "FR", "DE", "IT", "PL") THEN "EU"
5          WHEN ${countryCodeField} IN ("AU") THEN ${countryCodeField}
6          ELSE "Other countries"
7          END`;
8}
9
10module.exports = { countryGroup };

This function can be used in a SQLX file:

definitions/revenue_by_country_group.sqlx
1SELECT
2  ${country_mapping.countryGroup("country_code")} AS country_group,
3  SUM(revenue) AS revenue
4FROM my_schema.revenue_by_country
5GROUP BY 1

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

1SELECT
2  CASE
3    WHEN country_code IN ("US", "CA") THEN "NA"
4    WHEN country_code IN ("GB", "FR", "DE", "IT", "PL") THEN "EU"
5    WHEN country_code IN ("AU") THEN country_code
6    ELSE "Other countries"
7  END AS country_group,
8  SUM(revenue) AS revenue
9FROM my_schema.revenue_by_country
10GROUP 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
1function groupBy(n) {
2  var indices = [];
3  for (var i = 1; i <= n; i++) {
4    indices.push(i);
5  }
6  return `GROUP BY ${indices.join(", ")}`;
7}
8
9module.exports = { groupBy };

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

1SELECT field1,
2       field2,
3       field3,
4       field4,
5       field5,
6       SUM(revenue) AS revenue
7FROM my_schema.my_table
8${utils.groupBy(5)}

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

1SELECT field1,
2       field2,
3       field3,
4       field4,
5       field5,
6       SUM(revenue) AS revenue
7FROM my_schema.my_table
8GROUP 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
1function renderScript(table, dimensions, metrics) {
2  return `
3      SELECT
4      ${dimensions.map((field) => `${field} AS ${field}`).join(",\\n")},
5      ${metrics.map((field) => `SUM(${field}) AS ${field}`).join(",\\n")}
6      FROM ${table}
7      GROUP BY ${dimensions.map((field, i) => `${i + 1}`).join(", ")}
8    `;
9}
10module.exports = { renderScript };

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

1${script_builder.renderScript(
2  ref("source_table"),
3  ["country", "device_type"],
4  ["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:

1SELECT country AS country,
2       device_type AS device_type,
3       SUM(revenue) AS revenue,
4       SUM(pageviews) AS pageviews,
5       SUM(sessions) AS sessions
6FROM my_schema.source_table
7GROUP BY 1, 2

Sitemap