snowsql-helpers: Extending Snowflake stored procedures with helpers

Motivation:

Snowflake is a great platform. And using JS for stored procedures is nice I have no complains with that. However sometimes the current API forces you to do a lot of repetitive work. For example when you are executing a SQL statement or trying to retrieve results.

The Snowflake stored procedures do not allow you to do any kind of import. So this very simple script implements something like that.

In your stored procedure body you can do:

CREATE ....
$$
"@USING_<snippetname>";
$$

The tool will map the snipped name to .snippet and replace it.

So you can write your store procedures, then do

snowsql-helpers file.sql outdir

and the deploy the modified file.

I hope that helps :)

Installation

npm install -g snowsql-helpers

Available snippets

Currently there are only two snippets available:

EXEC Snippet

it allows you to execute queries like:

EXEC(`SELECT CURRENT_DATE`);

if you want to pass arguments you can use:

EXEC(`SELECT Employee where EmpID = ?`,[PARAM1]);

To do something like a select into then you can do:

EXEC(`SELECT EmployeeName, Salary where EmpID = ?`,[PARAM1]);
[vEmpName, vSalary] = INTO();

or as a one liner:

[vEmpName, vSalary] = EXEC(`SELECT Employee where EmpID = ?`,[PARAM1]);

This helper has some other nice things like: it sets a global variable for:

• ROW_COUNT
• ACTIVITY_COUNT
• MESSAGE_TEXT
• SQLCODE
• SQLSTATE

So you can easily do things like:

EXEC(`Delete from Employee where EmpID = ?`,[PARAM1]);
if (ACTIVITY_COUNT) {
    return "employee was deleted";
}
else {
    return "no employees were deleted";
}

or

EXEC(`Select EmployeeName from Employee where EmpID = ?`,[PARAM1]);
if (ROW_COUNT) {
    [vEmpName] = INTO();
    return `employee ${vEmpName} was found`;
}
else {
    return "no employees were deleted";
}

Cursor SNIPPET

A lot of database provide some kind of cursor functionality.

You can for example do something like this:

DECLARE CURSOR C1 AS SELECT * FROM EMPLOYEE;

You can also set parameter for the cursor:

DECLARE CURSOR C1 AS SELECT * FROM EMPLOYEE where EmpId = :var1;

So this snippet allows that too:

var C1=new Cursor("SELECT * FROM EMPLOYEE");
var C2=new Cursor("SELECT EmpName From Employee where EmpId = ?",()=>[EmpID]);
//...
EmpID = 100;
//...
C2.OPEN();
[EmpName] = C2.FETCH();