sprocketship 0.0.13

Better stored procedure management

⚙️ 🚀

Sprocketship

Better stored procedure management

Table of Contents

1. About The Project
   • Built With
2. Getting Started
   • Installation
3. Usage
4. License

About The Project

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.).

Built With

(back to top)

Getting Started

Installation

pip install sprocketship

Usage

Currently, sprocketship expects a .sprocketship.yml file and a procedures/ directory at the same level in a directory structure.

├── dbt_models
│   ├── customers.sql
│   ├── products.sql
├── procedures
│   ├── admin
│   │   ├── create_database_writer_role.js
│   │   ├── create_database_reader_role.js
│   ├── development
│   │   ├── 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:
    - name: create_temp_database
      replace_if_exists: true
      database: !env_var SNOWFLAKE_DATABASE
      schema: !env_var SNOWFLAKE_SCHEMA
      ...

  admin:
    - name: create_database_reader
      replace_if_exists: true
      database: !env_var SNOWFLAKE_DATABASE
      schema: !env_var SNOWFLAKE_SCHEMA
      ...

    - name: create_database_writer
      replace_if_exists: true
      database: !env_var SNOWFLAKE_DATABASE
      schema: !env_var SNOWFLAKE_SCHEMA
      ...

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.

Exhaustive Options for Stored Procedure Configuration

name: The name of the procedure
replace_if_exists: Boolean, inserts `OR REPLACE` into the create command
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
args:
    arg_name: arg_type
returns: The return type
comment: Explanation of the procedure

Support

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:

• <arg-name> <arg-type> DEFAULT <default-value>
• COPY GRANTS
• RETURNS <result-data-type> NOT NULL
• CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT }
• VOLATILE | IMMUTABLE (deprecated)

License

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

(back to top)