Customize field column in generated code

[hpower2]

type User struct {
	ID              string     
	Username        string     
	Password        string     
	Email           types.EncryptedString     
	CreateTimestamp int64      
	UpdateTimestamp int64      
	DeleteTimestamp null.Int64 
}

I want to create a generated Go from the database schema that looks like this :

create domain encrypted_field varchar(255);
create table public.user (
	id varchar(255) primary key not null,
	username varchar(255) not null,
	password varchar(255) not null,
	email encrypted_field not null,
	create_timestamp bigint not null,
	update_timestamp bigint not null,
	delete_timestamp bigint null
);

is there a way to configure SQLBoiler.toml?

because I want to generalize beforeHooks and afterHooks behaviors based on this custom type,

for example :
doing encryption

func BeforeInsert(ctx context.Context, exec boil.ContextExecutor, u *models.User) error {
	v := reflect.ValueOf(u).Elem()
	for i := 0; i < v.NumField(); i++ {
		field := v.Field(i)
		if field.Type() == reflect.TypeOf((*types.EncryptedString)(nil)).Elem() {
			encryptedValue, err := encryptEngine.Encrypt(field.String())
			if err != nil {
				log.Println(err.Error())
				return err
			}
			field.SetString(encryptedValue)
		}
	}

	now := time.Now().UnixMilli()
	if u.CreateTimestamp == 0 {
		u.CreateTimestamp = now
	}
	u.UpdateTimestamp = now
	u.ID = ID_GENERATOR.GenerateId()
	return nil
}

thanks for reading my questions

[stephenafamo]

I'm not 100% sure what you're trying to achieve. It seems you want to encrypt the email field. There are several possibilities:

1. Use a custom type in the generated model

You can do this in the types configuration. https://github.com/volatiletech/sqlboiler?tab=readme-ov-file#types.

NOTE: Make sure to do the encryption and decryption in the Value and Scan methods of your custom type.

2. Do it with hooks

Here is the documentation on how to register hooks. https://github.com/volatiletech/sqlboiler?tab=readme-ov-file#hooks

[hpower2]

This link didnt specify my case

https://github.com/volatiletech/sqlboiler?tab=readme-ov-file#types

Can you give example how i might achieve this?

[hpower2]

create domain encrypted_field varchar(255);
create table public.user (
	id varchar(255) primary key not null,
	username varchar(255) not null,
	password varchar(255) not null,
	email encrypted_field not null,
	create_timestamp bigint not null,
	update_timestamp bigint not null,
	delete_timestamp bigint null
);

run : sqlboiler psql -c sqlboiler.toml

Become user.go

With base struct (my target, not yet achieved, i dont know how to configure it) :

type User struct {
	ID              string     
	Username        string     
	Password        string     
	Email           types.EncryptedString     
	CreateTimestamp int64      
	UpdateTimestamp int64      
	DeleteTimestamp null.Int64 
}

My current golang file generation, can only be creating Email as a string

[stephenafamo]

Try adding this to your config.

[[types]]
  tables = ['users']

  [types.match]
    name = "email"
    type = "string"

  [types.replace]
    type = "types.EncryptedString"

  [types.imports]
    third_party = ['"github.com/hpower/project/types"']

Make sure to define the Scan and Value methods on the type

package types

import "database/sql/driver"

type EncryptedString string

func (e *EncryptedString) Value() (driver.Value, error) {
	// Encrypt and return the encrypted value
	var encrypted string
	return encrypted, nil
}

func (e *EncryptedString) Scan(src any) error {
	var encrypted string

	switch v := src.(type) {
	case []byte:
		encrypted = string(v)
	case string:
		encrypted = v

	default:
		return fmt.Errorf("unsupported type: %T, expected string or []byte", src)
	}

	// decrypt the encrypted value
	*e = EncryptedString(encrypted)
	return nil
}

[hpower2]

is there a way to make types.match as an arrays? or multiple column at the same time?

[stephenafamo]

You can use any property defined in the drivers.Column struct

// Column holds information about a database column.
// Types are Go types, converted by TranslateColumnType.
type Column struct {
	Name          string `json:"name" toml:"name"`
	Type          string `json:"type" toml:"type"`
	DBType        string `json:"db_type" toml:"db_type"`
	Default       string `json:"default" toml:"default"`
	Nullable      bool   `json:"nullable" toml:"nullable"`
	Unique        bool   `json:"unique" toml:"unique"`
	AutoGenerated bool   `json:"auto_generated" toml:"auto_generated"`

	// Postgres only extension bits
	// ArrType is the underlying data type of the Postgres
	// ARRAY type. See here:
	// https://www.postgresql.org/docs/9.1/static/infoschema-element-types.html
	ArrType *string `json:"arr_type" toml:"arr_type"`
	UDTName string  `json:"udt_name" toml:"udt_name"`
	// DomainName is the domain type name associated to the column. See here:
	// https://www.postgresql.org/docs/10/extend-type-system.html#EXTEND-TYPE-SYSTEM-DOMAINS
	DomainName *string `json:"domain_name" toml:"domain_name"`

	// MySQL only bits
	// Used to get full type, ex:
	// tinyint(1) instead of tinyint
	// Used for "tinyint-as-bool" flag
	FullDBType string `json:"full_db_type" toml:"full_db_type"`
}

For example, you could match on the DBType field to match encrypted_field across your whole database

[[types]]
  [types.match]
    db_type = "encrypted_field"

  [types.replace]
    type = "types.EncryptedString"

  [types.imports]
    third_party = ['"github.com/hpower/project/types"']

[hpower2]

its resolved, i can use domain_name for better invoker, and just once set and done in postgres. thanks for your dedicating answer for me !!!

[stephenafamo]

You're welcome