Cross Check/ Advise on a simple implementation that uses an Enum type

[BigBoulard]

Hi guys,

I was wondering what is the best way to implement INSERT and SELECT using an enum type in Postgres and a custom type in Go like this:

CREATE TYPE order_status AS ENUM
    ('UNKNOWN', 'CREATED', 'PROCESSING', 'COMPLETED');

CREATE TABLE IF NOT EXISTS orders
(
    order_id integer NOT NULL,
    order_status order_status NOT NULL DEFAULT 'UNKNOWN'::order_status,
    CONSTRAINT test_pkey PRIMARY KEY (id)
)

I've created this but I just wanted if I'm using correctly the Scanner and Valuer interfaces before I replicate this everywhere in my codebase and figure out that I missed something important :D

package main

import (
	"context"
	"database/sql/driver"
	"fmt"
	"log"

	"pgclient"
)

type Order struct {
	Id     int64  `sql:"order_id"`
	Status Status `sql:"order_status"`
}

type Status int64

const (
	StatusUnknown Status = iota
	StatusCreated
	StatusProcessing
	StatusCompleted
)

// deserialize from DB
func (s *Status) Scan(value interface{}) error {
	*s = toStatus(value.(string))
	return nil
}

// serialize to DB
func (s Status) Value() (driver.Value, error) {
	return s.String(), nil
}

func toStatus(s string) Status {
	switch s {
	case "UNKNOWN":
		return StatusUnknown
	case "CREATED":
		return StatusCreated
	case "PROCESSING":
		return StatusProcessing
	case "COMPLETED":
		return StatusCompleted
	}
	return StatusUnknown
}

func (s Status) String() string {
	switch s {
	case StatusCreated:
		return "CREATED"
	case StatusProcessing:
		return "PROCESSING"
	case StatusCompleted:
		return "COMPLETED"
	}
	return "unknown"
}

func main() {
	//  insert test
	orderToDB := &Order{
		Id:     1,
		Status: StatusCreated,
	}
	_, err := pgclient.GetSession().Exec(context.Background(), "INSERT INTO test (order_id, order_status) VALUES($1,$2)", orderToDB.Id, orderToDB.Status)
	if err != nil {
		log.Fatalf("insert error: %v\n", err)
	}

	// select test
	var orderFromDB Order
	err = pgclient.GetSession().QueryRow(context.Background(), "SELECT order_id, order_status FROM test WHERE order_id = 1").Scan(&orderFromDB.Id, &orderFromDB.Status)
	if err != nil {
		log.Fatalf("select error: %v\n", err)
	}
	fmt.Printf("%+v", orderFromDB) // {Id:1 Status:CREATED}
}

package pgclient

import (
	"context"
	"fmt"
	"github.com/BigBoulard/.../conf"
	"github.com/BigBoulard/.../log"
	"github.com/jackc/pgx/v5/pgxpool"
)

var dbpool *pgxpool.Pool

func GetSession() *pgxpool.Pool {
	return dbpool
}

func init() {
	conf.LoadEnv()
	connStr := fmt.Sprintf("postgres://%s:%s@%s/%s?sslmode=%s",
		conf.DB.User,
		conf.DB.Pass,
		conf.DB.Host,
		conf.DB.DBName,
		conf.DB.SSLMode,
	)
	var err error

	dbpool, err = pgxpool.New(context.Background(), connStr)
	if err != nil {
		log.Fatal(err, "pgclient", "init",
			fmt.Sprintf("Can't connect to host %s db %s", conf.DB.Host, conf.DB.DBName),
		)
		panic(err)
	}
	fmt.Println("Connected to database.")
}

Thank you so much

[jackc]

Unless you really need to map the enum string to an integer I would do something like this:

config.AfterConnect = func(ctx context.Context, conn *pgx.Conn) error {
	dt, err := conn.LoadType(ctx, "order_status")
	if err != nil {
		return err
	}

	conn.TypeMap().RegisterType(dt)

	return nil
}

This will maintain an internal registry of enum values. There will only be one copy of any string (per connection).

You can use normal strings or you can still make your own type with a string backing.

[BigBoulard]

Thank you jack! Once again you're here to give a hand and that's much appreciated.
Yes, I would prefer working with a custom type but maybe it's better to use string in fact, I'm not sure.

type Status string

const (
	StatusUnknown        Status = "UNKNOWN"
	StatusCreated          Status = "CREATED"
	StatusProcessing     Status = "PROCESSING"
	StatusCompleted     Status = "COMPLETED"
)

Just to validate your suggestion, working with type Status int64 instead of string, your solution would modify the pgclient package as below, right? (tested):

package pgclient

var pgxPool *pgxpool.Pool

func GetSession() *pgxpool.Pool {
	return pgxPool
}

func init() {
	conf.LoadEnv()
	connStr := fmt.Sprintf("postgres://%s:%s@%s/%s?sslmode=%s&pool_max_conns=%s",
		conf.DB.User,
		conf.DB.Pass,
		conf.DB.Host,
		conf.DB.DBName,
		conf.DB.SSLMode,
		conf.DB.PoolMaxConns,
	)
	var err error

	// builds a config from connStr
	config, err := pgxpool.ParseConfig(connStr)
	if err != nil {
		log.Fatal(err, "pgclient", "init",
			fmt.Sprintf("Can't parse config %s", connStr),
		)
		panic(err)
	}

	// called after a connection is established, but before it is added to the pool
	config.AfterConnect = func(ctx context.Context, conn *pgx.Conn) error {
		// inspects the database for "order_status" and produces a suitable pgtype.Type
		pgType, err := conn.LoadType(ctx, "order_status")
		if err != nil {
			return err
		}
		// register the "order_status" type
		conn.TypeMap().RegisterType(pgType)
		return nil
	}

	// creates a pool
	pgxPool, err = pgxpool.NewWithConfig(context.Background(), config)
	if err != nil {
		log.Fatal(err, "pgclient", "init",
			fmt.Sprintf("Can't connect to host %s db %s", conf.DB.Host, conf.DB.DBName),
		)
		panic(err)
	}

	fmt.Println("You are connected to your database.")
}

and then, my understanding is that there's no need to implement the Valuer interface anymore (and we get rid of the database/sql/driver at the same time), but we still need to implement the Scanner to convert the String retrieved in the Select test to the Status Go type. Is that correct?

type Order struct {
	Id     int64  `sql:"order_id"`
	Status Status `sql:"order_status"`
}

type Status int64

const (
	StatusUnknown Status = iota
	StatusCreated
	StatusProcessing
	StatusCompleted
)

// deserialize from DB
func (s *Status) Scan(value interface{}) error {
	*s = toStatus(value.(string))
	return nil
}

func toStatus(s string) Status {
	switch s {
	case "UNKNOWN":
		return StatusUnknown
	case "CREATED":
		return StatusCreated
	case "PROCESSING":
		return StatusProcessing
	case "COMPLETED":
		return StatusCompleted
	}
	return StatusUnknown
}

// used by pgx to use a string instead of an int64
func (s Status) String() string {
	switch s {
	case StatusCreated:
		return "CREATED"
	case StatusProcessing:
		return "PROCESSING"
	case StatusCompleted:
		return "COMPLETED"
	}
	return "unknown"
}

func main() {
	///////  insert test ///////
	orderToDB := &Order{
		Id:     1,
		Status: StatusCreated,
	}

	// because the "order_status" type is registered in pgx, we can pass a Status (int64) as $2
	// pgx automatically calls the String() method to match the registered enum type in postgres
	_, err := pgclient.GetSession().Exec(context.Background(), "INSERT INTO test (order_id, order_status) VALUES($1, $2)", orderToDB.Id, orderToDB.Status)
	if err != nil {
		log.Fatalf("insert error: %v\n", err)
	}

	/////// select test ///////
	var orderFromDB Order
	// here we still need the scan method to convert the String received from the postgres Enum to the Status (int64) Go type
	err = pgclient.GetSession().QueryRow(context.Background(), "SELECT order_id, order_status FROM test WHERE order_id = 1").Scan(&orderFromDB.Id, &orderFromDB.Status)
	if err != nil {
		log.Fatalf("select error: %v\n", err)
	}
	fmt.Printf("%+v\n", orderFromDB) // result: {Id:1 Status:CREATED}
}

[jackc]

I think that would work, but the rather than implementing sql.Scanner I would implement pgtype.TextScanner.

[BigBoulard]

I understand that var should then be pgtype.Text and that I should implement pgtype.TextScanner (the ScanText func).
I got rid of the String() receiver cause it collapse with the String Field of pgtype.Text.
ScanText seems never executed so I'm missing something ...

package main

import (
	"context"
	"fmt"
	"log"

	"github.com/../pgclient"
	"github.com/jackc/pgtype"
)

type Order struct {
	Id     int64  `sql:"order_id"`
	Status Status `sql:"order_status"`
}

type Status pgtype.Text

var (
	StatusUnknown    = Status{String: "UNKNOWN", Status: pgtype.Present}
	StatusCreated    = Status{String: "CREATED", Status: pgtype.Present}
	StatusProcessing = Status{String: "PROCESSING", Status: pgtype.Present}
	StatusCompleted  = Status{String: "COMPLETED", Status: pgtype.Present}
)

func (s *Status) ScanText(t pgtype.Text) error {
	*s = toStatus(t.String)
	return nil
}

func toStatus(s string) Status {
	switch s {
	case "UNKNOWN":
		return StatusUnknown
	case "CREATED":
		return StatusCreated
	case "PROCESSING":
		return StatusProcessing
	case "COMPLETED":
		return StatusCompleted
	}
	return StatusUnknown
}

func main() {
	///////  insert test: OK ///////
	orderToDB := &Order{
		Id:     1,
		Status: StatusCreated,
	}

	_, err := pgclient.GetSession().Exec(
                context.Background(), 
                "INSERT INTO test (order_id, order_status) VALUES($1, $2)", 
                orderToDB.Id, orderToDB.Status.String,
         )
	if err != nil {
		log.Fatalf("insert error: %v\n", err)
	}

	/////// select test: KO  ///////
	var orderFromDB Order

	err = pgclient.
		GetSession().
		QueryRow(
			context.Background(),
			"SELECT order_id, order_status::order_status FROM test WHERE order_id = 1",
		).
		Scan(&orderFromDB.Id, &orderFromDB.Status) // can't scan into dest[1]: cannot scan unknown type (OID 122045) in text format into *main.Status

	if err != nil {
		log.Fatalf("select error: %v\n", err)
	}
	fmt.Printf("%+v\n", orderFromDB) // result: {Id:1 Status:CREATED}
}

[jackc]

Your type Status can still be a string (and probably should be).

But the real issue is importing github.com/jackc/pgtype instead of github.com/jackc/pgx/v5/pgtype. The former is the old version for pgx v4.

[BigBoulard]

Got it ! Using string we ended up with this much more simpler solution

type Order struct {
	Id     int64  `sql:"order_id"`
	Status Status `sql:"order_status"`
}

type Status string

const (
	StatusUnknown    = "UNKNOWN"
	StatusCreated    = "CREATED"
	StatusProcessing = "PROCESSING"
	StatusCompleted  = "COMPLETED"
)

func main() {
	///////  insert test ///////
	orderToDB := &Order{
		Id:     1,
		Status: StatusCreated,
	}

	_, err := pgclient.GetSession().Exec(context.Background(), "INSERT INTO test (order_id, order_status) VALUES($1, $2)", orderToDB.Id, orderToDB.Status)
	if err != nil {
		log.Fatalf("insert error: %v\n", err)
	}

	/////// select test ///////
	var orderFromDB Order

	err = pgclient.
		GetSession().
		QueryRow(
			context.Background(),
			"SELECT order_id, order_status::order_status FROM test WHERE order_id = 1",
		).
		Scan(&orderFromDB.Id, &orderFromDB.Status)

	if err != nil {
		log.Fatalf("select error: %v\n", err)
	}
	fmt.Printf("%+v\n", orderFromDB) // result: {Id:1 Status:CREATED}
}

I took note about the new v5/pgtype many thanks!

[Morpheus0x]

@BigBoulard Using a custom string type instead of an enum doesn't make sense in my opinion. I want to use real enums to enable flexibility to modify, in this case, the order status workflow. Using real enums provides a logical ordering for each stage. Otherwise, we are just using strings again, like we have been doing since php with very confusing status names in old codebases.

I have been using enums with underlying uint type by assigning the default starting stage to 2 and the current known end stage to 1024. All the stages we need now get distributed at appropriate power of 2s. In this example I would do the following:

type Status uint

const (
  StatusCreated Status = 2
  StatusProcessing = 512
  StatusComplete = 1024
  StatusUnknown = 4096
)

Now, if in the future a stage such as a status of unpaid orders is necessary, we can just add StatusUnpaid = 256 and it is immediately apparent what the correct status workflow is.
Doing it this way provides enough flexibility to add as many stages one could considerable need.

The issue with mapping this to postgres still remains. I solved this by just using INTEGER as type in PG, since limiting the available options in postgres is not necessary, in my opinion. I just make sure to correctly write my insert/update queries. And in case an invalid status is saved to the db, reading it into a struct (i am using github.com/georgysavva/scany/v2/pgxscan for that) will produce an error. And this should only occur in testing anyway and if not, it would be easy to fix with a db edit and a bugfix.