Sqlx: Generate insert/update queries from structs

[aeneasr]

I have a struct like this:

type sqlData struct {
    ID                string   `json:"id" db:"id"`
    Name              string   `json:"client_name" db:"client_name"`
}

Is it possible to generate an INSERT or UPDATE statement that would look like INSERT INTO footable (id, client_name) VALUES (:id, :client_name) from this struct? If the struct holds a lot of data, writing those SQL commands is error prone and annoying.

[robert-zaremba]

By design this is supposed to be handled by other package. For example https://github.com/jmoiron/modl does this.

[aeneasr]

awesome, thanks for the hint

[marioizquierdo]

Sorry for opening this again, but while using other packages like modl or gorp seems good for other reasons, it still feels like there should be an equivalent to .Get or .Select to get the column names from the struct. While it is nice to have structs with db tags to query data, it is sad that insert and update queries always need all the fields.

I ended up writing my own reflection method to list columns from the struct, following the same rules. Then I can make a generic insert method like this:

func Insert(db sqlx.Ext, table string, arg interface{}) (sql.Result, error) {
    fields := DBFields(arg) // e.g. []string{"id", "name", "description"}
    csv := fieldsCSV(fields) // e.g. "id, name, description"
    csvc := fieldsCSVColons(fields) // e.g. ":id, :name, :description"
    sql := "INSERT INTO " + table + " (" + csv + ") VALUES (" + csvc + ")"
    return sqlx.NamedExec(db, sql, arg)
}

NamedExec is very close to allow insert/update from structs, just needs a little push. Maybe it would make sense to implement something like DBFields or ColumnNames in sqlx?

[aeneasr]

Can you show your implementation of ColumnNames? I think adding this little helper to sqlx would be perfect - it adheres to the very clean/tidy structure of sqlx whilst also providing the ability to compute such an INSERT/UPSERT/UPDATE statement.

[marioizquierdo]

My implementation works only with db tags or map[string]interface{}. Would need to edit to also work with NameMapper. I always tag my structs so I didn't need that.

// DBFields reflects on a struct and returns the values of fields with `db` tags,
// or a map[string]interface{} and returns the keys.
func DBFields(values interface{}) []string {
    v := reflect.ValueOf(values)
    if v.Kind() == reflect.Ptr {
        v = v.Elem()
    }
    fields := []string{}
    if v.Kind() == reflect.Struct {
        for i := 0; i < v.NumField(); i++ {
            field = v.Type().Field(i).Tag.Get("db")
            if field != "" {
                fields = append(fields, field)
            }
        }
        return fields
    }
    if v.Kind() == reflect.Map {
        for _, keyv := range v.MapKeys() {
            fields = append(fields, keyv.String())
        }
        return fields
    }
    panic(fmt.Errorf("DBFields requires a struct or a map, found: %s", v.Kind().String()))
}

[aeneasr]

Nice, thanks! :)

[tcurdt]

By design this is supposed to be handled by other package.

I have hard time understanding why this applies to Insert but not Get/Select.

[pinpox]

By design this is supposed to be handled by other package. For example https://github.com/jmoiron/modl does this.

Does anyone here have an example of how to do exactly what you described with the mentionend modl package?

[mjs]

FWIW, goqu can cleanly generate SELECT, INSERT and UPDATE statements using structs. The query building API is quite nice. It can generate a SQL string for use with database/sql or it can run queries for you.