Cross Check/ Advise on a simple implementation that uses an Enum type #1559
-
|
Hi guys, I was wondering what is the best way to implement INSERT and SELECT using an 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 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 |
Beta Was this translation helpful? Give feedback.
Replies: 1 comment 5 replies
-
|
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. |
Beta Was this translation helpful? Give feedback.
-
|
Thank you jack! Once again you're here to give a hand and that's much appreciated. type Status string
const (
StatusUnknown Status = "UNKNOWN"
StatusCreated Status = "CREATED"
StatusProcessing Status = "PROCESSING"
StatusCompleted Status = "COMPLETED"
)Just to validate your suggestion, working with 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 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}
} |
Beta Was this translation helpful? Give feedback.
-
|
I think that would work, but the rather than implementing |
Beta Was this translation helpful? Give feedback.
-
|
I understand that var should then be 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}
} |
Beta Was this translation helpful? Give feedback.
-
|
Your type But the real issue is importing |
Beta Was this translation helpful? Give feedback.
-
|
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 |
Beta Was this translation helpful? Give feedback.
Unless you really need to map the enum string to an integer I would do something like this:
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.