Gorm: Support for Protobuf Timestamp for CreatedAt, UpdatedAt...

[bamzi]

When migrating a protobuf message to DB, most fields map correctly, however, Timestamp on proto complied package doesn't map over to SQL query for column creation.

Expected a column "created_at" type "timestamp with time zone", but got nothing!

What version of Go are you using (go version)?

Irrelevant but I'm using 1.10.3

What version of Protobuf?

proto3

Which database and its version are you using?

Postgres 10.4

Please provide a complete runnable program to reproduce your issue. IMPORTANT

Given the following sample proto file:

// $GOPATH/src/sample/sample.proto

syntax = "proto3";
package data;
import "google/protobuf/timestamp.proto";
message SomeData {
     string id = 1;
     google.protobuf.Timestamp created_at = 2;
}

Complied to Go using
protoc -I=$(GOPATH)/src/sample sample.proto --go_out=plugins=grpc:$(GOPATH)/src

Produces:

// $GOPATH/src/sample/sample.pb.go
package sample
...
type SomeData struct {
    Id  string  `protobuf:"bytes,1,opt,name=id" json:"id,omitempty"`
    CreatedAt  *google_protobuf.Timestamp `protobuf:"bytes,1001,opt,name=created_at" json:"created_at,omitempty"`
}
...

when using

var db *gorm.DB
err := db.AutoMigrate(&sample.SomeData{}).Error
// there are no errors
// but not all fields, specially created_at are not created into db

REF

google.protobuf.Timestamp

[ermik]

Does the DB support protobuf timestamps? ORM is just an extension of the database and can't support more than a handful of extra types. For any type you can implement Scan and Value methods to make sure your data get's read and written correctly. For typing the database use gorm:"type:" annotation.

[pedromorgan]

Please re-open if any more problems.

[bamzi]

After playing a lot with it, there's no clear winner solution. The best compromise is to have a string formatted time in the Protobuf for easy migration.

[PassKit]

For any type you can implement Scan and Value methods to make sure your data get's read and written correctly. For typing the database use gorm:"type:" annotation.

This would need to be implemented in the google/protobuf/timestamp.proto since go cannot define new methods on non-local types.

[zapkub]

I had been struck to this problem and this method works fine to me but you may lose some benefit feature from original protobuf.timestamp

Declare your GRPC message for Date and inject custom tag for Gorm

message Date {
    required int64 Seconds = 1;
}
message User {
    // @inject_tag: gorm:"not_null;type:TIMESTAMP"
    optional Date PassportIssueDate = 11;
}

then create new go file inside proto package output folder and implement your own Date scanner and valuer

func (d *Date) Scan(val interface{}) error {
    switch t := val.(type) {
    case time.Time:
        d.Seconds = foundation.Int64(t.Unix())
    default:
        return fmt.Errorf("Result data of Date is not a time")
    }
    return nil
}

func (d Date) Value() (driver.Value, error) {
    dd := time.Unix(*d.Seconds, 0).UTC()
    return dd, nil
}

[aconchillo]

I did something slightly different than @zapkub taking advantage of google.protobuf.Timestamp:

message Timestamp {
  google.protobuf.Timestamp timestamp = 1;
}

message User {
  // @inject_tag: gorm:"primary_key"
  uint32 ID = 1;
  // @inject_tag: gorm:"type:timestamp"
  Timestamp createdAt = 2;
  // @inject_tag: gorm:"type:timestamp"
  Timestamp updatedAt = 3;
  // @inject_tag: gorm:"type:timestamp"
  Timestamp deletedAt = 4;
  ...
  ...
}

then convert the new Timestamp type so SQL can handle it:

import (
        "database/sql/driver"
        "fmt"
        "time"

        "github.com/golang/protobuf/ptypes"
)

func (ts *Timestamp) Scan(value interface{}) error {
        switch t := value.(type) {
        case time.Time:
                var err error
                ts.Timestamp, err = ptypes.TimestampProto(t)
                if err != nil {
                        return err
                }
        default:
                return fmt.Errorf("Not a protobuf Timestamp")
        }
        return nil
}

func (ts Timestamp) Value() (driver.Value, error) {
        return ptypes.Timestamp(ts.Timestamp)
}