No means of omitting zero values, generating useless INSERT queries

[AlbinoGeek]

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.)

[doug-martin]

@AlbinoGeek is this a case where you can use goqu:"skipinsert"?

Internally we use that tag as we know what fields we want to skip when inserting.

[AlbinoGeek]

@doug-martin

This seems inconsistent, why when db struct tags are used for naming, is a goqu struct tag used here?
Previously, I had tried to specify db:"profile_id,skipinsert" and that didn't work.

Not to mention, this needs to be in some of the three places documentation is available.
(Also, why are there three different places for documentation with different information?)
-- I would make a different issue for this (consolidating documentation.) --

---

I am trying this now...

Alright, that works! With the exception of the documentation issue, this is solved.

---

Also worth noting, the link doesn't show the tag you mention. It says skipudate only, in that example.
Nvm, simple typo, I think you meant the inserting documentation

[AlbinoGeek]

Various Documentation Sources

• GoDoc (official): https://godoc.org/github.com/doug-martin/goqu
• master Tree: https://github.com/doug-martin/goqu/tree/master/docs
• GitHub Pages: https://doug-martin.github.io/goqu/ (is this the same content?)

Broken, Outdated or Unofficial?

• https://www.worldlink.com.cn/en/osdir/goqu.html
• https://recordnotfound.com/goqu-doug-martin-38702
• Lots of outdated StackOverflow issues with no links to docs

( Why did I even mention the SO? They have higher Google SER than the official docs when looking for issues. )

If you prefer we'd discuss documentation of the issue in another issue, feel free to close this one as solved.