[Snowflake Official] SDK V2 Design Document

[sfc-gh-swinkler]

Problem Statement

As part of our ongoing effort to improve stability of the provider, the Snowflake Terraform Provider team would like to introduce design standards for a new v2 SDK. The purpose of this SDK is to replace the snowflake package (herein referred to as v1 SDK), and also make it easier to write scripts for other sorts of applications which could benefit from a consistent SDK.

Over the years the library of functions in the snowflake package has grown immensely, and multiple competing design patterns have emerged for how to implement certain API endpoints. The most common pattern seems to be the builder pattern with helper functions.

Builder Pattern

builder := snowflake.NewObjectBuilder(name, database, schema, params…)
builder.WithParam1(p1)
builder.WithParam2(p2)
builder.WithParam3(p3)

stmt := builder.Create()

if err := snowflake.Exec(db, stmt); err != nil {
  return err
}

row := snowflake.QueryRow(db,stmt)
object, err := snowflake.ScanObject(row)

Helper Functions

// Builders are hold context of a single object, 
// while helper functions perform specific operations.

b := snowflake.NewObjectBuilder(name,database,schema)
stmt := b.SetParam("k","v")
if err := snowflake.Exec(db, stmt); err != nil {
  return err
}
b.SetComment("comment")
if err := snowflake.Exec(db, stmt); err != nil {
  return err
}

// Lists are always helper functions and do not use a builder // at all.
objectList, err := snowflake.ListObjects(db)

In both the builder pattern with helper functions design, the v1 SDK is implemented by appending strings together until the final statement is constructed using many if/else blocks. This works, but is a bit tedious, as it results in a lot of duplicated code.

The second most commonly used pattern is what I call the “generic sql builder” pattern. This used to be the standard for all resources, but the trend has been to move away from it in recent times, since it doesn’t always put properties in the right order, and is difficult to debug. Also if you “fix” a bug for one issue, you often introduce a bug somewhere else. There is also not much room for customization, other than a raw SQL escape hatch, so helper functions are still employed to shore up the gaps. There’s not much difference between using the generic sql builder vs non generic from a user perspective, since there are wrapper builders that hide this detail.

Generic Builder

// warehouse uses a generic builder, but its not easy to tell 
// from the outside
warehouseBuilder := snowflake.NewWarehouseBuilder(d.Id())
stmt := warehouseBuilder.Show()
row := snowflake.QueryRow(db, stmt)
w, err := snowflake.ScanWarehouse(row)

// here is the wrapper builder
type WarehouseBuilder struct {
	*Builder
}

The main problem of the v1 provider is consistency and testability. I have discussed the most popular designs, but there are also one-off designs for specific resources. Examples are Materialized View, which uses a token parser, and ExternalOAuth integration which uses another kind of generic SQL generator in the same vein as Generic Builder.

V2 SDK Proposal

We have developed a new design pattern which we intend to have completely replace the existing v1 SDK. We call this the “v2 SDK”. The basic idea is that there is an overall Client which manages connections, and holds services. Each service implements an interface that matches as closely as possible to the Snowflake SQL API. For example:

Password Policy Interface

type PasswordPolicies interface {
    Create(ctx context.Context, id SchemaObjectIdentifier, opts *PasswordPolicyCreateOptions) error
    Alter(ctx context.Context, id SchemaObjectIdentifier, opts *PasswordPolicyAlterOptions) error
    Drop(ctx context.Context, id SchemaObjectIdentifier, opts *PasswordPolicyDropOptions) error
    Show(ctx context.Context, opts *PasswordPolicyShowOptions) ([]*PasswordPolicy, error)
    Describe(ctx context.Context, id SchemaObjectIdentifier) (*PasswordPolicyDetails, error)
}

Example Usage of the v2 SDK

client, err := NewDefaultClient()	
err := client.PasswordPolicies.Create(ctx, id, &PasswordPolicyCreateOptions{
			OrReplace:                 Bool(true),
			PasswordMinLength:         Int(10),
			PasswordMaxLength:         Int(20),
			PasswordMinUpperCaseChars: Int(1),
			PasswordMinLowerCaseChars: Int(1),
			PasswordMinNumericChars:   Int(1),
			PasswordMinSpecialChars:   Int(1),
			PasswordMaxAgeDays:        Int(30),
			PasswordMaxRetries:        Int(5),
			PasswordLockoutTimeMins:   Int(30),
			Comment:                   String("test comment"),
		})

passwordPolicyDetails, err := client.PasswordPolicies.Describe(ctx, id)

alterOptions := &PasswordPolicyAlterOptions{
  Set: &PasswordPolicyAlterSet{
    PasswordMinLength: Int(10),
    PasswordMaxLength: Int(20),
  },
}
err := client.PasswordPolicies.Alter(ctx, passwordPolicy.Identifier(), alterOptions)

This new design closely matches the style of Snowflake API, and each function is unambiguously named. You can see the new design in the “pkg/sdk” folder. It relies on structs to define not only the interface, but how the SQL will be parsed.

Here is the PasswordPolicyCreateOptions. As you can see, the structure closely matches that of the Snowflake API https://docs.snowflake.com/en/sql-reference/sql/create-password-policy. This is intentional, as the way the SQL statement is constructed is by recursively looping through the struct fields and using the “ddl” and “db” tags to build up a list of clauses which are then joined together.

Options struct defines inputs

type PasswordPolicyCreateOptions struct {
    create         bool                   `ddl:"static" db:"CREATE"` //lint:ignore U1000 This is used in the ddl tag
    OrReplace      *bool                  `ddl:"keyword" db:"OR REPLACE"`
    passwordPolicy bool                   `ddl:"static" db:"PASSWORD POLICY"` //lint:ignore U1000 This is used in the ddl tag
    name           SchemaObjectIdentifier `ddl:"identifier"`
    IfNotExists    *bool                  `ddl:"keyword" db:"IF NOT EXISTS"`

    PasswordMinLength         *int `ddl:"parameter" db:"PASSWORD_MIN_LENGTH"`
    PasswordMaxLength         *int `ddl:"parameter" db:"PASSWORD_MAX_LENGTH"`
    PasswordMinUpperCaseChars *int `ddl:"parameter" db:"PASSWORD_MIN_UPPER_CASE_CHARS"`
    PasswordMinLowerCaseChars *int `ddl:"parameter" db:"PASSWORD_MIN_LOWER_CASE_CHARS"`
    PasswordMinNumericChars   *int `ddl:"parameter" db:"PASSWORD_MIN_NUMERIC_CHARS"`
    PasswordMinSpecialChars   *int `ddl:"parameter" db:"PASSWORD_MIN_SPECIAL_CHARS"`
    PasswordMaxAgeDays        *int `ddl:"parameter" db:"PASSWORD_MAX_AGE_DAYS"`
    PasswordMaxRetries        *int `ddl:"parameter" db:"PASSWORD_MAX_RETRIES"`
    PasswordLockoutTimeMins   *int `ddl:"parameter" db:"PASSWORD_LOCKOUT_TIME_MINS"`

    Comment *string `ddl:"parameter,single_quotes" db:"COMMENT"`
}

The “ddl” and “db” tags have special meaning. The tag “ddl” is used to specify the type of clause and how it should be rendered, while the tag “db” is used to set any word as it would appear in the query.

DDL Type List

Keyword - this means that the field may or may not be set. Like a switch that is either on or off. Keywords can appear on any struct type. If the “db” tag is set, then it will use that, otherwise it will use the field value (expecting string type).
Static - this is a special field that always appears no matter what. It is always a private unexported field, since it is not something the user can change. It only uses the “db” tag.
Identifier - identifiers implement the object identifier interface. They are used to properly display fully qualified names. Identifiers do not use the “db” tag.
Parameter - most fields are parameters. This means that they follow the syntax “key = value”. Parameters can be quoted via the “ddl” tag. E.g. “ddl:
Command - special kinds of parameters that don’t use equals signs. E.g. “key value”

These basic types can be applied to any struct or nested struct, allowing for more complexity than would otherwise be apparent.

Nested Structs

type PasswordPolicyShowOptions struct {
	show             bool  `ddl:"static" db:"SHOW"`              //lint:ignore U1000 This is used in the ddl tag
	passwordPolicies bool  `ddl:"static" db:"PASSWORD POLICIES"` //lint:ignore U1000 This is used in the ddl tag
	Like             *Like `ddl:"keyword" db:"LIKE"`
	In               *In   `ddl:"keyword" db:"IN"`
	Limit            *int  `ddl:"command,no_quotes" db:"LIMIT"`
}

type In struct {
	Account  *bool                   `ddl:"keyword"`
	Database AccountObjectIdentifier `ddl:"identifier" db:"DATABASE"`
	Schema   SchemaIdentifier        `ddl:"identifier" db:"SCHEMA"`
}

type Like struct {
	Pattern *string `ddl:"keyword,single_quotes"`
}

// Generates something of the form:
SHOW PASSWORD POLICIES [LIKE '<pattern>'] [IN] [ACCOUNT] [DATABASE db_identifier] [SCHEMA schema_identifer] [LIMIT <rows>]

All ddl types can be modified with quotes. For example, the following specifies a keyword that uses single quotes. I.e. ‘’

DDL Keyword with Quotes Modifier

	Pattern *string `ddl:"keyword,single_quotes"`

Contributing

We understand that it will be a long process to migrate the existing resources to use the new SDK, so we will continue to accept PRs using the old design patterns to fix bugs and such. For new resources and data sources however, we kindly ask that you adopt the new SDK style. If you have any questions, please feel free to reach out to the team.

Thank you,
Snowflake Terraform Provider Team