How to marshal SQL-Types?

I am taking my first steps in Go :slight_smile: To do so, I’m creating a small REST-API based on Postgres (Procedures/Functions) which itself works fine.

However I’ve noticed that the “standard marshaller” creates JSON output slightly different from what I’ve expected.

Below you may see the Model (struct) I’m using for all services:

type Aufgabe struct {

    ID             int             `json:"id"`

    Beschreibung   string          `json:"beschreibung"`

    StatusCode     int             `json:"statusCode"`

    StatusText     string          `json:"statusText"` // nullable, may be not present

    KategorieCode  int             `json:"kategorieCode"`

    KategorieText  string          `json:"kategorieText"` // nullable, may be not present

    ErstelltAm     time.Time       `json:"erstelltAmTS"`

    ZuErledigenBis sql.NullTime    `json:"zuErledigenBisTS"` // nullable, may be not present, Date

    Kosten         sql.NullFloat64 `json:"kosten"`           // nullable, may be not present

    DauerMin       sql.NullInt32   `json:"dauerMin"`         // nullable, may be not present

    Wichtig        bool            `json:"wichtig"`

    ErledigtAm     sql.NullTime    `json:"erledigtAmTS"` // nullable, may be not present

}

and the Output is like:

{

    "id": 5,

    "beschreibung": "sdsd",

    "statusCode": 2,

    "statusText": "gelöscht",

    "kategorieCode": 2,

    "kategorieText": "einkauf",

    "erstelltAmTS": "2020-09-15T18:45:39.974309Z",

    "zuErledigenBisTS": {

        "Time": "2020-09-22T00:00:00Z",

        "Valid": true

    },

    "kosten": {

        "Float64": 0,

        "Valid": false

    },

    "dauerMin": {

        "Int32": 0,

        "Valid": false

    },

    "wichtig": false,

    "erledigtAmTS": {

        "Time": "0001-01-01T00:00:00Z",

        "Valid": false

    }

}

Obviously those “SQL-Types” are Objects themself. I’d like to return only the actual value and I might have to format the TimeStamps as well.

Here’s an example of the same structure produced with node.js:

{

    "id": 5,

    "beschreibung": "sdsd",

    "statusCode": 2,

    "statusText": "gelöscht",

    "kategorieCode": 2,

    "erstelltTS": "2020-09-15T16:45:39.974Z",

    "kategorieText": "einkauf",

    "kosten": null,

    "dauerMin": null,

    "wichtig": false,

    "erledigtAmTS": null

}

You may see it only contains the actual values and prints null for empty values.

Another issue I’m having with the Go-Version is that the “add/create” Service also expects those Object-Type JSON structures and fails to map the actual sql-type if there’s only the value provided by the client.

So in other words: What are best practices to deal with JSON and Databases in Golang? I’ve read about custom marshallers, but that’s yet too complicated for me to figur out. Perhaps there’s another library i should use?

Switch to the library https://github.com/guregu/null and use null.Time instead of sql.NullTime and null.Float instead of sql.NullFloat64. It encodes NULL values as null in JSON.

I don’t understand what you’re trying to say here, can you provide examples of what you mean.