如何在Go中为MySQL构建RESTful API？

Question

I have the task of inserting a JSON payload into a table.

The (fixed) SQL table structure is defined like so:

$ echo "describe ut_invitation_api_data\G;" | mysql -h auroradb.dev.unee-t.com -P 3306 -u bugzilla --password=$(aws --profile uneet-dev ssm get-parameters --names MYSQL_PASSWORD --with-decryption --query Parameters[0].Value --output text) bugzilla  | grep Field
mysql: [Warning] Using a password on the command line interface can be insecure.
  Field: id
  Field: mefe_invitation_id
  Field: bzfe_invitor_user_id
  Field: bz_user_id
  Field: user_role_type_id
  Field: is_occupant
  Field: bz_case_id
  Field: bz_unit_id
  Field: invitation_type
  Field: is_mefe_only_user
  Field: user_more
  Field: mefe_invitor_user_id
  Field: processed_datetime
  Field: script
  Field: api_post_datetime

See fields.txt for the details showing different types and whether they can can be null or not.

So my first step is to create a struct and map varchar to string, any type of int to int and dates to time.Time.

Already the column names with underscores is making my Golang editor complain. Next comes the JSON tags and we have something like:

type invitation struct {
    id                   int       `json:"id"`
    mefe_invitation_id   string    `json:"mefe___invitation___id"`
    bzfe_invitor_user_id int       `json:"bzfe___invitor___user___id"`
    bz_user_id           int       `json:"bz___user___id"`
    user_role_type_id    int       `json:"user___role___type___id"`
    is_occupant          bool      `json:"is___occupant"`
    bz_case_id           int       `json:"bz___case___id"`
    bz_unit_id           int       `json:"bz___unit___id"`
    invitation_type      string    `json:"invitation___type"`
    is_mefe_only_user    bool      `json:"is___mefe___only___user"`
    user_more            string    `json:"user___more"`
    mefe_invitor_user_id int       `json:"mefe___invitor___user___id"`
    processed_datetime   time.Time `json:"processed___datetime"`
    script               string    `json:"script"`
    api_post_datetime    time.Time `json:"api___post___datetime"`
}

Which is looking very wrong. Is there an better approach to making RESTful APIs to mysql database using Golang? i.e. ensure a clean mapping and the right RESTful verbs are used?

Answer 1

The mysql field names must go in the json tag. The field names in your struct can be whatever you like. For example:

type Invitation struct {
    ID                int    `json:"id"`
    InvitationID      string `json:"mefe_invitation_id"`
    InvitorUserID     int    `json:"bzfe_invitor_user_id"`
    BzUserID          int    `json:"bz_user_id"`
    //...
}

Also note that in order for json package to be able to see your struct fields they need to be exported (start with capital letters) and you probably want to export your entire struct as well, so you can return it in public functions.