CODEWITHSUNDEEP
sqlpostgresqlgosqlx
Neighbourhood
Neighbourhood

Reputation: 336

Golang: insert nested structure model into Postgres

i have this model data structure:

type Order struct {
    OrderEntry OrderEntry `db:"order"`
}

type OrderEntry struct {
    Order_uid          string
    Track_number       string
    Entry              string
    Delivery           Delivery
    Payment            Payment
    Items              []Item
    Locale             string
    Internal_signature string
    Customer_id        string
    Delivery_service   string
    Shardkey           string
    Sm_id              int64
    Date_created       string
    Oof_shard          string
}

type Delivery struct {
    ...
}

type Payment struct {
    ...
}

type Item struct {
    ...
}

And i have table

CREATE TABLE "order"
(
    "order" jsonb NOT NULL
);

How do i insert Order object to this psq table? I use sqlx and for this code im getting error

func (r *RepositoryPostgres) CreateDocument(order L0.Order) error {
    tx := r.db.MustBegin()
    tx.MustExec("INSERT INTO order (order) VALUES ($1)", order.OrderEntry)
    err := tx.Commit()

    if err != nil {
        return err
    }

    return nil
}

panic: sql: converting argument $1 type: unsupported type L0.OrderEntry, a struct

How to properly fix this? Thanks

Upvotes: 1

Views: 834

Answers (1)

samsmi7h
samsmi7h

Reputation: 41

There are a couple of changes you need to make here.

  1. You need to marshal to JSON the struct you want to store. You can do that with:
orderB, err := json.Marshal(order.OrderEntry)
if err != nil {
    // handle err
}

tx.MustExec("INSERT INTO order (order) VALUES ($1)", orderB)

This will now store your struct as a JSON byte-array in the table.

  1. In order to generate JSON from your struct correctly, you need to add JSON struct tags to each field. e.g.:
type OrderEntry struct {
    Order_uid          string `json:"order_uid"`
    Track_number       string `json:"track_number"`
    Entry              string `json:"entry"`
    Delivery           Delivery `json:"delivery"`
    Payment            Payment `json:"payment"`
    Items              []Item `json:"items"`
    Locale             string `json:"locale"`
    Internal_signature string `json:"internal_signature"`
    Customer_id        string `json:"customer_id"`
    Delivery_service   string `json:"delivery_service"`
    Shardkey           string `json:"shard_key"`
    Sm_id              int64 `json:"sm_id"`
    Date_created       string `json:"date_created"`
    Oof_shard          string `json:"oof_shard"`
}

You can name these however you like, snake-case being the common format. These tags provide the names for the JSON fields to the parser.

Without these fields, you'll end up with empty JSON, and you won't be able to retrieve the data.

Upvotes: 4

Related Questions