No means of omitting zero values, generating useless INSERT queries #234
Description
Issue Summary
Dialect: sqlite3: However, this issue is not related to the dialect.
Using the struct format of goqu for INSERTs lead to... unusable queries, should you be (DRY) using the same struct as you would SELECTing. This results in the following error on the second insert:
UNIQUE constraint failed: profile.profile_id
This is because of the very visibly erroneous SQL statement as generated:
INSERT INTO "profile" ("first_name", "last_name", "profile_id", "updated_at") VALUES ('Foo', 'Bar', 0, '2020-09-17T07:27:30.495796305Z')Notice the profile_id field set to 0?
Sample Reproduction
type Profile struct {
ID uint64 `db:"profile_id,omitempty"` // <--- does not respect omitempty
Updated time.Time `db:"updated_at"`
FirstName string `db:"first_name"`
LastName string `db:"last_name"`
// ...
}
func foo() error {
db, err := sqlx.Connect("sqlite3", "file.db")
if err != nil {
return err
}
// scaffold test database
if _, err = db.Exec(`CREATE TABLE IF NOT EXISTS profile (
profile_id INTEGER PRIMARY KEY AUTOINCREMENT,
updated_at DATETIME,
first_name TEXT,
last_name TEXT,
);`); err != nil {
return err
}
// prepare query
query, args, err := goqu.Insert("profile").Rows(profile).ToSQL()
if err != nil {
return err
}
// insert first row, should work
if _, err = db.Exec(query, args...); err != nil {
return err
}
// insert second row, will not work
if _, err = db.Exec(query, args...); err != nil {
return err
}
return nil
}
func main() {
if err := foo(); err != nil {
panic(err)
}
}Expectation
I would expect goqu to support either omitempty or some other tag when it comes to avoiding the inclusion of a parameter in an INSERT statement. Such, that I wonder how anyone is using this package for INSERTs given this issue existing. (Perhaps I have missed something? I checked the documentation available in 3+ places however, and did not see it mentioned.)
Maybe related to #174 -- but not strictly a duplicate, unless you plan to make nullable IDs a standard... (eww.)