sql.Scan, sql.Value and Modifiers

Scanner and Valuer support

Since all arguments passed to the Query* and Exec functions are forwarded directly to the underlying database driver (e.g. database/sql or pgx) if the underlying implementation supports the sql.Scanner and sql.Valuer interfaces KSQL will also support it.

What is a KSQL Modifier?

A KSQL modifier is a special tag you can add to any of the attributes of your struct to alter the behavior of KSQL when reading or writing that attribute into the database. To use it, it is necessary to add the name of the modifier on the ksql tag after a comma, e.g.:

`ksql:"column_name,json"`

KSQL Modifiers play a similar role to the sql.Scanner and sql.Valuer interfaces giving you an easier way of having a custom serialization behavior for a specific attribute whenever you read or write it to/from the database.

The important difference is: KSQL Modifiers are reusable.

A single KSQL Modifier can be used in many different attributes of different types, which means we can actually provide a few built-in Modifiers that are very useful.

Using KSQL Modifiers

KSQL comes with a few built-in Modifiers and an API for creating your own modifiers if necessary. These will be discussed in more detail in the next sections.

Here is an example of how a User struct might look like when using some of the built-in modifiers:

type User struct {
	ID   uint   `ksql:"id"`
	Name string `ksql:"name"`
	Age  int    `ksql:"age"`

	Address Address `ksql:"address,json"`

	UpdatedAt time.Time `ksql:"updated_at,timeNowUTC"`
	CreatedAt time.Time `ksql:"created_at,timeNowUTC/skipUpdates"`
}

Please note that each attribute can only accept one and only one modifier at most. So where you see timeNowUTC/skipUpdates in the example above it's not a special syntax, that's the full name of the modifier.

The name is long, but every built-in Modifier will have an extremely descriptive name so it's hard to misinterpret it.

Applying a Modifier to a struct attribute will cause KSQL to use this modifier when Inserting, Updating, and Querying that field.

A Modifier can therefore be used to:

1. Make any type of conversion on the attribute value before sending it to the database.
2. Make any type of conversion on the attribute value when reading it from the database.
3. Informing KSQL that this attribute should be ignored during Insertions and/or Updates.

Modifiers improve on the Valuer/Scanner feature provided by the standard library in two ways:

1. They are not coupled to the type of the attribute.
2. They are reusable: You create it once and you can use it in many attributes of many different types.

List of Built-in Modifiers

• json: It will convert any type to JSON before writing to the database and unmarshal it back from JSON when reading, this makes it easy to save structs as JSON in the database if this fits your use-case.
• timeNowUTC: This one works on attributes of type time.Time by setting its value to time.Now().UTC() every time before insertions and updates. This modifier was created to be used on UpdatedAt timestamp fields.
• timeNowUTC/skipUpdates: Does the same as the above but only on insertions, during Updates attributes marked with this modifier will be completely ignored. This is useful for CreatedAt fields where you only want them to be set once when first creating the record.
• skipUpdates: Will ignore fields on updates.
• skipInserts: Will ignore fields on inserts.
• nullable: Modifies the default behavior of ignoring nil pointers during Updates and Insertions, i.e. it makes it possible to set database columns to NULL, which was not possible before. On KSQL V2 this modifier might be removed in favor of an Update() function whose default behavior is to not ignore NULL fields.

Registering new Modifiers

Registering a new Modifier is simple: Describe it using an instance of ksqlmodifiers.AttrModifier and register it on KSQL by calling the global function: ksqlmodifiers.RegisterAttrModifier as illustrated below:

func init() {
	ksqlmodifiers.RegisterAttrModifier("my_modifier_name", ksqlmodifiers.AttrModifier{
		// Set SkipInserts to true if you want this modifier to
		// cause the field to be ignored on inserts.
		SkipInserts: false,

		// Set SkipUpdates to true if you want this modifier to
		// cause the field to be ignored on updates.
		SkipUpdates: false,
	
		Scan: func(ctx context.Context, opInfo ksqlmodifiers.OpInfo, attrPtr interface{}, dbValue interface{}) error {
			// Read the dbValue, modify it and then save it
			// into the attrPtr argument using reflection if necessary.
		},

		Value: func(ctx context.Context, opInfo ksqlmodifiers.OpInfo, inputValue interface{}) (outputValue interface{}, _ error) {
			// Read the inputValue, modify it and then
			// return it so the database can save it.
		},
	})
}

This registration should be performed inside your code before making any calls to the KSQL library. I recommend doing it inside a init() function since then it will run before main() starts.

Modifiers vs sql.Scanner and sql.Valuer interfaces

When using a Modifier, if that Modifier specifies a Scan() function and the type of the affected attribute implements the sql.Scanner interface the method implementation will be ignored in favor of the Modifier implementation. The same is valid for the Modifier Value() function when the type implements the sql.Valuer interface.