Learn how you can use JavaScript to re-use code across your scripts and define several actions.

Power your code with JavaScript

Learn how to re-use code across your project with includes.

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.
<a target="_blank" rel="noopener" href="https://developer.mozilla.org/en-US/docs/Web/JavaScript/A_re-introduction_to_JavaScript">MDN</a> 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:

```js
// includes/constants.js
const PROJECT_ID = "my_project_name";
module.exports = { PROJECT_ID };
```
<br />
<callout intent="warning">
  Note that in order to use functions or constants elsewhere in the project, they must be exported
  using the <code>module.exports = {"{}"}</code> syntax.
</callout>

## 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`:

```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:

```js
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.

```js
// 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 SQLX file:

```js
// 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:

```js
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:

```js
// 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`:

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

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

```js
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.

```js
// 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`:

```js
${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:

```js
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
```


includes

Learn how to define several actions in a single Javascript file.

Use JavaScript files

## Introduction

Dataform provides a JavaScript API for defining all actions via code instead of creating individual SQL files for each action. This can be useful in advanced use cases.

To use the JavaScript API, create a `.js` file anywhere in the `definitions/` folder of your project. This code will be run during project compilation.

The JavaScript API exposes functions that create any of the actions that you would otherwise define in a SQL file:

- Datasets
- Declarations
- Assertions
- Operations
- Tests

These are regular JavaScript (ES5) files that can contain arbitrary code: loops, functions, constants, etc.

## Defining actions in JavaScript

The following file is a simple example of creating actions in JavaScript:

`definitions/example.js`:

```js
publish("table1").query("SELECT 1 AS test");

declare({
  schema: "rawdata",
  name: "input1"
});

assert("assertion1").query("SELECT * FROM source_table WHERE value IS NULL");

operate("operation1").queries("INSERT INTO some_table (test) VALUES (2)");

test("test1")
  .dataset("some_table")
  .input("input_data", "SELECT 1 AS test")
  .expect("SELECT 1 AS test");
```

This example creates a dataset, a declaration, an assertion, an operation, and a test in a single file, which if written in SQL would have required 5 seperate files.

## Setting properties on actions

Each of the global methods - `publish()`, `declare()`, `operate()`, `assert()`, and `test()` - return an object that can be used to configure that action. The API follows a builder syntax which can be seen in the following example:

```js
publish("table1")
  .query("SELECT 1 AS test") // Defines the query
  .type("table") // Sets the query's type
  .dependencies(["other_table"]) // Specifies dataset dependencies
  .descriptor({
    test: "Value is 1" // Describes fields in the dataset
  });
```

Multiple configuration properties can also be set using the `config()` method, or alternatively simply passed as a second argument to the method:

```js
publish("table1", {
  type: "table",
  dependencies: ["other_table"],
  descriptor: {
    test: "Value is 1"
  }
});
```

Learn more about configuration options for each type of action:

- [`publish()` (Datasets)](../datasets/publish)
- [`declare()` (Declarations)](../declarations)
- [`assert()` (Assertions)](../assertions)
- [`operate()` (Operations)](../operations)
- [`test()` (Tests)](../tests)

## Using built-in functions

When writing `.sqlx` files, Dataform makes a number of built-in functions such as `ref()` and `self()` available to use within the main query. For example:

```js
// definitions/example.sqlx
config { type: "view" }
SELECT * FROM ${ref("other_table")}
```

The `ref()` function is made available for the script to use automatically. Note that this is not the case when using the JavaScript API.

To use these functions in JavaScript, API methods - such as `query()` - take a `Contextable` argument. Instead of providing a string as the argument to the API method,
you can pass a function whose only parameter is a `context` object. This object exposes the built-in functions for JavaScript code to use.

For example, the above example written in JavaScript (making handy use of JavaScript template strings), in `definitions/example.js`:

```js
publish("example").query(ctx => `SELECT * FROM ${ctx.ref("other_table")}`);
```

<div className="bp3-callout bp3-icon-info-sign bp3-intent-warning" markdown="1">
  Note that when using JavaScript template strings - which allow use of the <code>${"{}"}</code>{" "}
  syntax - the string in question must be wrapped in backticks <code>`</code> instead of single{" "}
  <code>'</code> or double <code>"</code> quotes.
</div>

<br />

The following methods and configuration options accept a function taking a `Contextable` argument as in the above example:

- `query()`
- `where()`
- `preOps()`
- `postOps()`

## Referencing includes files

Just like in `.sqlx` files, you can reference any `includes` function, macro or constant inside a `.js` file. However, the syntax differs slightly and varies depending on the `includes` file location.

### Top-Level includes

When importing from a top-level `includes` file, you can simply reference the file name when declaring your variables.

For example, if you would like to reference `SERVICE_NAME` and `SERVICE_ID` from the `includes/service.js` file, you can reference the file name directly. This is because Dataform automatically imports
all top-level `includes` files.

```js
const {SERVICE_NAME, SERVICE_ID} = service;
...
```

### Nested includes

When importing from a nested `includes` file, you must declare these constants using the JavaScript builtin `require` function

For example, if you would like to reference `SERVICE_NAME` and `SERVICE_ID` from the `includes/all_services/service.js` file, you must use `require`.

```js
const {SERVICE_NAME, SERVICE_ID} = require("includes/all_services/service.js");
...
```

Regardless of whether the source file is top-level or nested, once declared, these variables are available without any additional notation (i.e.. you don't need to wrap the constants in `${}`).

## Example: Dynamic dataset generation

One of the most common use cases for using the JavaScript API is to perform a similar action repeatedly.

For example, imagine you have several datasets, all of which have a `user_id` field. Perhaps you would like to create a view of each dataset with certain blacklisted user IDs removed.
You could perform this action across all datasets using a JavaScript `forEach()` statement:

```js
// definitions/blacklist_views.js
const datasetNames = ["user_events", "user_settings", "user_logs"];

datasetNames.forEach(datasetNames => {
  publish(datasetNames + "_blacklist_removed").query(
    ctx => `
      SELECT * FROM ${ctx.ref(tableName)}
      WHERE user_id NOT IN (
        SELECT user_id
        FROM ${ctx.ref("blacklisted_user_ids")}
      )`
  );
});
```

This script would create 3 new datasets as views named `user_events_blacklist_removed`, `user_settings_blacklist_removed`, and `user_logs_blacklist_removed` that don't contain any of the blacklisted user IDs.


js-api

javascript

Home

Learn the basics of Dataform, how it works, and where it fits in your data stack.

Introduction

An introduction to ELT and where Dataform fits in.

ELT and the modern data stack

modern-data-stack

Learn how Dataform and SQLX can help your team manage data in your warehouse.

Dataform and SQLX in 5 minutes

dataform-in-5-minutes

Learn how Dataform compiles your project and runs it in your warehouse.

How Dataform works

how-dataform-works

introduction

Learn about which data warehouses Dataform can work with and how to configure them.

Supported warehouses

Authentification, configuration options, and content for Snowflake.

Snowflake

snowflake

Authentification, configuration options, and content for BigQuery.

Google BigQuery

bigquery

Authentification, configuration options, and content for Redshift.

Amazon Redshift

redshift

Authentification and configuration options for Azure SQL Data Warehouse.

Azure SQL Data Warehouse

sqldatawarehouse

Authentification and configuration options for Postgres.

Postgres

postgres

warehouses

This tutorial is for people who are new to Dataform and want to be taught how to set up a new project. We will show you how to create your own data model, how to test and document it and how to run schedules on it.

Getting started tutorial

Learn how to create a new BigQuery project and generate warehouse credentials.

Getting set up

set-up

Learn how to connect to a warehouse and create and publish your first dataset.

Building your data model

building-your-data-model

Learn how to use the ref function in Dataform and how to view your project in the Dependency tree.

Managing dependencies

managing-dependencies

Learn how to set up a schedule and alerts in Dataform

Setting up a schedule

scheduling

Learn how to set up data quality tests using assertions and how to document your datasets

Data quality tests and documenting datasets

testing-and-documentation

Learn how to committ changes you've made in your Dataform project

Committing your changes

committing-changes

getting-started-tutorial

Guides to build your Dataform project.

Build your Dataform project

Learn how to configure, publish and document data tables in your warehouse.

Publish data tables and views

Learn how to publish tables and views in your warehouse.

Publish datasets

publish

Learn how to configure tables that update incrementally.

Incremental datasets

incremental

Learn how to add data documentation for your tables and views.

Document datasets

documentation

datasets

Learn about the structure and features of SQLX files.

SQLX

sqlx

Learn how to test data quality with assertions.

Test data quality with assertions

assertions

Learn how to declare external datasets with declarations.

Declare external datasets with declarations

declarations

Learn how to define custom SQL operations in Dataform.

Write custom SQL operations

operations

Learn how to configure your Dataform project.

Configure your project

configuration

Learn how to organise your project with tags.

Organise your project with tags

Learn how to run unit tests on your queries.

Run unit tests on your queries

tests

Configure continuous integration/deployment workflows for your Dataform project.

Configure CI/CD

ci-cd

incremental-datasets

guides

Learn how to set up and run your projects in Dataform Web's cloud environment.

Dataform web guides

Tutorials for getting start with Dataform web.

Tutorials

Learn how to create a new dataform web project and connect to your warehouse.

Creating a new project

Start building out your dataform pipelines using SQLX files and managing dependencies.

tutorials

Learn how to use version control in Dataform Web.

Version control

version-control

Learn how to use Git access tokens for connecting to git providers.

Git access tokens

git-access-tokens

Learn how to schedule runs and view logs

Schedule runs

Learn about environments in Dataform web and how to configure them

Environments

environments

Learn about configuring slack notifications with Dataform web

Slack Notifications

slack-notifications

Learn how to use the REST API to control Dataform Web

Use the REST API

Web API Reference

reference

using-the-api

api-reference

set-up-warehouse

dataform-web

Best practices to scale your Dataform project and your analytics

Best practices using Dataform

Principles to start your project on the right foot

First steps with your Dataform project

start-your-dataform-project

Follow best practices to build a project that will scale and maximise productivity

Best practices for managing dataform projects

best-practices

Guide to learn how to use the Dataform command line interface tool.

Use the Dataform CLI

dataform-cli

Learn how Dataform works with examples projects and scripts.

Example projects and scripts

A list of example projects for Snowflake, BigQuery, and Redshift.

Example projects

A simple project turning Stackoverflow public raw data into reporting tables in a BigQuery data warehouse.

Power your code with JavaScript

What's next

Re-use code with includes

Use JavaScript files

Sitemap