golang sqlx "missing destination name ...."

Question

I have error when use sqlx with postgresql : "missing destination name rec_created_by"

type Client struct {
    ClientID                    string          `json:"client_id"   db:"id"`
    Name                        string          `json:"name"    db:"name"`
    Version                     int16           `json:"version" db:"version"`
    IsActivated                 bool            `json:"is_activated"    db:"is_activated"`
    RecCreatedBy                string          `json:"rec_created_by"  db:"rec_created_by"`
    RecCreatedByUser            *User           `json:"rec_created_by_user" db:"-"`
    RecCreated                  *time.Time      `json:"rec_created" db:"rec_created"`
    RecModifiedBy               string          `json:"rec_modified_by" db:"rec_modified_by"`
    RecModifiedByUser           *User           `json:"rec_modified_by_user"    db:"-"`
    RecModified                 *time.Time      `json:"rec_modified"    db:"rec_modified"`
    CultureID                   string          `json:"culture_id"  db:"culture_id"`
    ...
}
func (c *Client) Get(id string) error {
    db, err := sqlx.Connect(settings.Settings.Database.DriverName, settings.Settings.GetDbConn())
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
    _client := Client{}
    err = db.QueryRowx("SELECT id, name, version, is_activated, rec_created_by, rec_created, rec_modified_by, rec_modified, culture_id, amount_decimal_places, amount_rounding_precision, \"unit-amount_decimal_places\", \"unit-amount_rounding_precision\", currency_lcy_id  FROM client WHERE id=$1", id).StructScan(&_client)
    if err == sql.ErrNoRows {
        return ErrClientNotFound
    } else if err != nil {
        return err
    }
    return nil
 }

I have db:"rec_created_by" in my Client type but why error occurred.

Answer 1

For those coming with a similar issue, here are a few possible reasons.

TL;DR

1. The struct field is not exported.
2. The struct tag syntax is invalid.
3. The query result column names don't match the struct tag.

The struct field is not exported

Struct fields must be exported to be marshaled and unmarshaled by sqlx.

type foo struct {
    bar string `db:"bar"` // This wont work. 'bar' needs to be 'Bar'
}

The struct tag is invalid

Yes. I have done all of these.

type Foo struct {
    bar string `db="bar"` // Should be : not =
}

type Foo struct {
    bar string `json:"bar", db:"bar"` // There should not be a comma
}

The query result column names don't match

type foo struct {
    BarName string `db:"bar_name"`
}

myfoo := foo{}
db.Get(&myfoo, `SELECT barname FROM foo_table WHERE barname = 'bar string'`)
// This will fail because the returned column is missing the '_'

type foo struct {
    Bar string `db:"Bar"`
}

myfoo := foo{}
db.Get(&myfoo, `SELECT Bar FROM foo_table WHERE Bar = 'bar string'`)
// This will fail because the returned column is actually 'bar'.

Postgres is case sensitive. So Bar and bar are not the same.

You can use uppercase identifiers but they must be surrounded in double quotes.

myfoo := foo{}
db.Get(&myfoo, `SELECT "Bar" FROM foo_table WHERE "Bar" = 'bar string'`)

Of course if you created your table with...

CREATE TABLE foo_table
(
    Bar TEXT NOT NULL
)

well... the column will actually be named bar. This is because Postgres will transform all unquoted identifiers to lowercase.

You can fix this problem by either...

1. Changing the case in your struct tag.
2. Renaming the columns in your database. (probably only an option in the early stages of development)
3. Using aliases in your query.

We could solve the above problem by writing our query like this...

type foo struct {
    Bar string `db:"FooBar"`
}

myfoo := foo{}
db.Get(&myfoo, `SELECT bar as "FooBar" FROM foo_table WHERE bar = 'bar string'`)
// This will work because we have renamed the returned column to "FooBar".