Introduction to JavaScript in Dataform

This document helps you understand how you can use JavaScript to develop SQL workflows in Dataform.

Dataform core lets you create SQL workflow objects with SQLX and JavaScript. While optional, using JavaScript along with SQLX to create similar elements repeatedly in your workflow. For example, with JavaScript you can create a view of each table in your workflow with certain user IDs removed. You can also develop SQL workflow objects exclusively with JavaScript.

To get started with developing Dataform SQL workflows using JavaScript, you must be familiar with JavaScript syntax and the following JavaScript concepts:

  • Variables
  • Arrays
  • Conditional statements
  • For loops
  • Maps
  • Functions
  • Objects
  • Exporting and importing modules

Add JavaScript code to a SQLX file

You can add JavaScript code to a SQLX file in two ways: inline or inside a JavaScript block.

You can use a JavaScript block to define functions or constants in a SQLX file. You can use inline JavaScript to dynamically modify a SQLX or SQL query.

The following code sample shows the self Dataform core built-in JavaScript function added inline to the post_operations block in a SQLX file:

config {type: "table"}

SELECT * FROM ...

post_operations {
  GRANT `roles/bigquery.dataViewer`
  ON
  TABLE ${self()}
  TO "group:allusers@example.com", "user:otheruser@example.com"
}

The following code sample shows a constant defined in a JavaScript block and used inline inside a query in a SQLX file:

js {
  const columnName = "foo";
}

SELECT 1 AS ${columnName} FROM "..."

Reuse code with JavaScript

You can reuse JavaScript code to streamline development in Dataform. To reuse JavaScript constants and functions across a single SQLX file, you can encapsulate them in a JavaScript block. To reuse JavaScript code across a single Dataform repository, you can create includes. To reuse JavaScript code across multiple Dataform repositories, you can create or import a package.

Reuse code across a single SQLX file with JavaScript encapsulation

To create repetitive parts of SQL code that you can reuse within a single SQLX file, you can encapsulate functions and constants in a JavaScript block. You can reuse code defined in a JavaScript block only inside the SQLX file where the block is defined. For more information, see Overview of Dataform core.

The following code sample shows a constant and function defined in a JavaScript block and used inline inside a query in a SQLX file:

js {
 const foo = 1;
 function bar(number){
     return number+1;
 }
}

select
 ${foo} as one,
 ${bar(foo)} as two

Reuse code across a single repository with includes

Includes are JavaScript constants or functions global to your repository. You define includes in the includes directory of your repository. You can then reuse them across your repository in JavaScript and SQLX files.

The following code sample shows the definition of the launch_date constant in the includes/constants.js file:

// filename is includes/constants.js
const launch_date = "11.11.2011";
module.exports = { launch_date };

The following code sample shows the launch_date constant referenced in a table definition query in a SQLX file:

config {type: "table"}

SELECT * FROM source_table WHERE date > ${constants.launch_date}

Reuse code across multiple repositories with packages

Packages are collections of JavaScript code that you can import and use across multiple Dataform repositories to streamline SQL workflow development.

You can create your own custom package in Dataform or use one of the open-source Dataform packages, available on the GitHub open-source Dataform page.

For an example of using an open-source package in Dataform, see Use Slowly changing dimensions in Dataform.

To be able to use the contents of a package in Dataform, you need to install the package in your Dataform repository, and then import it to the individual JavaScript or SQLX file in which you want to use the package. For more information, see Install a package in Dataform.

To be able to install a private NPM package in a Dataform repository, you need to authenticate the package.

Create SQL workflows in JavaScript

As an alternative to developing SQL workflows in SQLX, or SQLX combined with JavaScript, you can create SQL workflow objects in .js files by using only JavaScript. You can create multiple SQL workflow objects within one JavaScript file with Dataform global methods and arbitrary JavaScript ES5 code, such as loops and constants. Each of the Dataform global JavaScript methods contain properties you can use to configure the created objects.

You can create the following SQL workflow objects by using only JavaScript in Dataform:

  • Data source declarations
  • Tables
  • Manual assertions
  • Custom SQL operations

For more information on creating SQL workflow objects using only JavaScript, see Create Dataform SQL workflows with JavaScript.

What's next