PostgreSQL Composite Types support

[Acnologla]

How to make model for Composite Types?
Ref: https://www.postgresql.org/docs/9.6/rowtypes.html

[vmihailenco]

Bun does not support composite types. If you are interested, you could try to port that feature from go-pg.

[github-actions]

This issue has been automatically marked as stale because it has not had activity in the last 30 days. If there is no update within the next 7 days, this issue will be closed.

[smyrman]

We have some good cases where we would like to use composite types.

Which option would be preferred?

Declare type in composite model

Require new struct tag usage.

type inventoryItem struct {
	bun.BaseModel `bun"type:myschema.inventory_item"`

	Name       string  `bun:"name"`
	SupplierID int     `bun:"supplier_id"`
	Price      float64 `bun:"price"`
}

type onHand struct {
	bun.BaseModel `bun:"table:myschema.on_hand"`

	InventoryItem inventoryItem `bun:"inventory_item"`
	Count         int           `bun:"count"`
}

Declare type when used

Doesn't require new struct tag usage.

type inventoryItem struct {
	Name       string  `bun:"name"`
	SupplierID int     `bun:"supplier_id"`
	Price      float64 `bun:"price"`
}

type onHand struct {
	bun.BaseModel `bun:"table:myschema.on_hand"`

	InventoryItem inventoryItem `bun:"inventory_item,type:myschema.intentory_item"`
	Count         int           `bun:"count"`
}

EDIT: Forgot to add bun prefix to the BaseModel struct rags; fixed now.

[vmihailenco]

In your first example, InventoryItem inventoryItem could mean JSONB.

In your 2nd example, it is not obvious that type:myschema.intentory_item means a composite type, i.e. it can be just a less-known PostgreSQL type/extension.

It probably should be more explicit:

type onHand struct {
	bun.BaseModel `table:"myschema.on_hand"`

	InventoryItem inventoryItem `bun:"inventory_item,type:myschema.intentory_item,composite"`
	Count         int           `bun:"count"`
}

[smyrman]

I forgot the bun prefix on some of the stuct tags in my previous comment; fixed now.

In your first example, InventoryItem inventoryItem could mean JSONB.

Don't I need to do bun",type:bson" for that? Anyways, I would assume that 2nd example is probably slightly easier to implement. And perhaps more explict.

In your 2nd example, it is not obvious that type:myschema.intentory_item means a composite type, i.e. it can be just a less-known PostgreSQL type/extension.

Does it have to be obvious? I see Gorm (according to Atlas at least), just use a type prefix:

• https://atlasgo.io/guides/orms/gorm/composite-types

I think that as a user, I wouldn't really need to care if the referenced type is a simple type or a composite type, would I?

[smyrman]

Don't I need to do bun",type:bson" for that?

Right, I see bson is the default in the docs. Have always used explicit tags, so haven't really noticed before.

[smyrman]

Trying to understand the code for where this would have to be added.

I found there is already matching done on the key "composite" here when determining the type name:

• bun/dialect/pgdialect/sqltype.go

  Line 66 in 014b142

  if v, ok := field.Tag.Option("composite"); ok {

So to understand what's lacking here, we probably need a new append implementation? And/or a parser similar to arrayParser in the pgdialect package. We maybe also need to update the array parser implementation?

To get started quicker, is there an easy way for me to do this with an sql.Scanner and sql.Driver interface that uses bun for the heavy lifting?

type inventoryItem struct {
	bun.BaseModel `bun"type:myschema.inventory_item"`

	Name       string  `bun:"name"`
	SupplierID int     `bun:"supplier_id"`
	Price      float64 `bun:"price"`
}

func (inventoryItem) Value() (driver.Value, error) {
	var b []byte
	b = append(b, '(')
	
	// What goes here?
	
	b = append(b, ')')
	return b, nil
}

func (inventoryItem) Scan(src any) error {
	b, ok := src.([]byte)
	if !ok {
		return fmt.Errorf("unexpected type; got %T, want []byte", src)
	}
	b = bytes.TrimSpace(b)
	if b[0] != '(' || b[len(b)-1] != ')' {
		return fmt.Errorf("expecting surrounding parenthesis")
	}
	b = b[1 : len(b)-1]

	// What goes here?
}

And is this likely to work for array fields?

type onHand struct {
	bun.BaseModel `table:"myschema.on_hand"`

	InventoryItems []inventoryItem `bun:"inventory_items,composite:myschema.intentory_item,array"`
}