Solved: How to use Javascript function in SQLX query

lawlesspattern

Good day, im having trouble in understanding how to pass arguments to js functions at my sqlx queries, for example I have the following sqlx file where I have a simple function called addOne(number):

config {

type: "view"

}

js {

const tableName = "my-project.my-dataset.cars";

function addOne(number) {

return number + 1;

}

SELECT c.id, c.color, ${addOne(c.id)} FROM ${tableName} AS c

I can use the constant for the table name with no errors, but when I try to use the addOne function and pass the id as argument it shows error: c is not defined

I was following this documentation https://cloud.google.com/dataform/docs/javascript-in-dataform but could not find any example that uses table columns as function parameters, appreciate any help

ms4446

Your JavaScript block executes before any SQL. This phase is dedicated to preparing and manipulating parts of the SQL query that will be executed.

Limitations: SQL column values (e.g., c.id) are inaccessible during this phase since the SQL query hasn't run yet.

Dataform combines the output of your JavaScript (e.g., constants, dynamic SQL snippets) with your SQL template to construct the final query.

This final, complete SQL query is then executed against your database.

The "c is not defined" Error

This error arises when you attempt to pass a SQL column alias (like c.id) directly into a JavaScript function. Remember, JavaScript runs before the SQL query, so it doesn't recognize these aliases yet.

Solution: Dynamic SQL Generation with JavaScript

The workaround is to use JavaScript for dynamically generating SQL expressions based on the available columns:

 

config { type: "view" }

js {
  const tableName = "my-project.my-dataset.cars";
  
  // Function to generate a SQL expression
  function addOne(colName) {
    return `(${colName} + 1)`; 
  }
}

SELECT c.id, c.color, ${addOne('c.id')} AS id_plus_one FROM ${tableName} AS c

Explanation:

Dynamic SQL: The addOne function now returns a SQL expression string (like (c.id + 1)) instead of attempting to compute it with JavaScript.
Interpolation: Dataform's SQLX engine seamlessly incorporates this generated SQL expression into the final query.
Alias: AS id_plus_one provides a meaningful name for the resulting calculated column.

Example Output:

id	color	id_plus_one
1	red	2
2	blue	3
3	green	4

Advanced JavaScript Functions

You can create more sophisticated functions following this pattern:

 

function calculateValue(colName, operation) {
  return `CASE WHEN ${colName} IS NULL THEN NULL ELSE ${colName} ${operation} 1 END`;
}

Use it like this:

${calculateValue('c.id', '-')} (for subtraction)
${calculateValue('c.price', '*')} (for multiplication)

Best Practices & Tips

Think SQL First: Design your JavaScript primarily to generate and manipulate SQL expressions, not to process row-level data.
Leverage Dataform Functions: Utilize Dataform's built-in JavaScript functions (ref(), self()) for efficient referencing of other tables and views.
Modularity: For complex JavaScript logic, split it into separate .js files and include them in your SQLX files for better organization.
Comments and Testing: Use comments to document your code and thoroughly test your queries before deployment.

View solution in original post

ms4446