Sprocketship

Better stored procedure management

sprocketship makes it easy to develop, manage, and deploy stored procedures in Snowflake. Using the language of your choosing, you can write the contents of your stored procedure separately from its configurations (e.g., EXECUTE AS, RETURN TYPE, etc.).

(back to top)

pip install sprocketship

Currently, sprocketship expects a .sprocketship.yml file in a procedures/ directory.

├── dbt_models
│   ├── customers.sql
│   ├── products.sql
├── procedures
│   ├── useradmin
│   │   ├── create_database_writer_role.js
│   │   ├── create_database_reader_role.js
│   ├── sysadmin
│   │   ├── create_temp_database.js
└── .sprocketship.yml

The yaml path to each procedure in the sprocketship.yml should follow that of the paths to their corresponding files in the procedures/ directory.

procedures:
  development:
    create_temp_database:
      database: !env_var SNOWFLAKE_DATABASE
      schema: !env_var SNOWFLAKE_SCHEMA
      ...

  admin:
    create_database_reader:
      database: !env_var SNOWFLAKE_DATABASE
      schema: !env_var SNOWFLAKE_SCHEMA
      ...

    create_database_writer:
      database: !env_var SNOWFLAKE_DATABASE
      schema: !env_var SNOWFLAKE_SCHEMA
      ...

sprocketship allows providing default parameters at any given level of your project. These defaults will be applied recursively to any procedures defined in any of the subdirectories, unless overridden by a default in one of the subdirectories.

procedures:
  # for all procedures, default to the below database and schema
  +database: !env_var SNOWFLAKE_DATABASE
  +schema: !env_var SNOWFLAKE_SCHEMA
  development:
    # for all procedures in the development directory,
    # default to using the sysadmin role
    +use_role: sysadmin
    create_temp_database:
      args:
        - name: Name of argument
          type: Type of argument
          default: (Optional) default value for the argument
      returns: varchar

Thanks to ABSQL, sprocketship also provides the ability to define parameters using file frontmatter. Suppose we have a file create_database_writer_role.js, we can define parameters for the stored procedure within the file using frontmatter:

/*
database: my_database
schema: my_schema
language: javascript
execute_as: owner
use_role: sysadmin
*/

sprocketship will automatically parse and apply the parameters defined in the frontmatter to the stored procedure.

When setting up your sprocketship project, we recommend setting more general parameters (e.g., database, schema, language, etc.) in the .sprocketship.yml file, and anything that's specific to a given procedure should be defined in the file frontmatter of that procedure, such as the args or return type. Example below:

# .sprocketship.yml
procedures:
  +database: my_database
  +schema: my_schema
  +language: javascript
  +execute_as: owner
  sysadmin:
    +use_role: sysadmin
  useradmin:
    +use_role: useradmin

In the above .sprocketship.yml, we've set the database, schema, language, and executor at the highest level. This means that all procedures in the sysadmin and useradmin directories will inherit these defaults unless overridden. Now we can define procedure-specific parameters in the file frontmatter:

// procedures/useradmin/create_role.js
/*
args:
  - name: role_name
    type: varchar
returns: varchar
comment: |
  Creates a role with the provided name
*/

var roleName = ROLE_NAME;
snowflake.execute(`CREATE ROLE IF NOT EXISTS ${roleName}`)

From here, simply run

$ sprocketship liftoff

from the project directory (or provide the directory, e.g. sprocketship liftoff my/directory/path) and sprocketship will launch your stored procedures into the given directory.

database: The name of the database where the procedure will be stored
schema: The name of the schema where the procedure will be stored
language: The language of the procedure definition
execute_as: caller or owner
use_role: The role you'd like to own the procedure
args:
    - name: Name of argument
      type: Type of argument
      default: (Optional) default value for the argument
returns: The return type, this can include the `NOT NULL` option
comment: Explanation of the procedure

sprocketship currently only supports Javascript-based stored procedures (Python support coming soon!). Additionally, there are a few options from the CREATE STORED PROCEDURE function that are not yet supported:

• CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT }
• VOLATILE | IMMUTABLE (deprecated)

Distributed under the MIT License. See LICENSE for more information.

(back to top)