Inserting

• Creating An InsertDataset
• Examples
  • Insert Cols and Vals
  • Insert goqu.Record
  • Insert Structs
  • Insert Map
  • Insert From Query
  • Returning
  • SetError
  • Executing

To create a InsertDataset you can use

goqu.Insert

When you just want to create some quick SQL, this mostly follows the Postgres with the exception of placeholders for prepared statements.

ds := goqu.Insert("user").Rows(
    goqu.Record{"first_name": "Greg", "last_name": "Farley"},
)
insertSQL, _, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley')

SelectDataset.Insert

If you already have a SelectDataset you can invoke Insert() to get a InsertDataset

NOTE This method will also copy over the WITH clause as well as the FROM

ds := goqu.From("user")

ds := ds.Insert().Rows(
    goqu.Record{"first_name": "Greg", "last_name": "Farley"},
)
insertSQL, _, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley')

DialectWrapper.Insert

Use this when you want to create SQL for a specific dialect

// import _ "github.com/doug-martin/goqu/v9/dialect/mysql"

dialect := goqu.Dialect("mysql")

ds := dialect.Insert().Rows(
    goqu.Record{"first_name": "Greg", "last_name": "Farley"},
)
insertSQL, _, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO `user` (`first_name`, `last_name`) VALUES ('Greg', 'Farley')

Database.Insert

Use this when you want to execute the SQL or create SQL for the drivers dialect.

// import _ "github.com/doug-martin/goqu/v9/dialect/mysql"

mysqlDB := //initialize your db
db := goqu.New("mysql", mysqlDB)

ds := db.Insert().Rows(
    goqu.Record{"first_name": "Greg", "last_name": "Farley"},
)
insertSQL, _, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO `user` (`first_name`, `last_name`) VALUES ('Greg', 'Farley')

Examples

For more examples visit the Docs

Insert with Cols and Vals

ds := goqu.Insert("user").
	Cols("first_name", "last_name").
	Vals(
		goqu.Vals{"Greg", "Farley"},
		goqu.Vals{"Jimmy", "Stewart"},
		goqu.Vals{"Jeff", "Jeffers"},
	)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []

Insert goqu.Record

ds := goqu.Insert("user").Rows(
	goqu.Record{"first_name": "Greg", "last_name": "Farley"},
	goqu.Record{"first_name": "Jimmy", "last_name": "Stewart"},
	goqu.Record{"first_name": "Jeff", "last_name": "Jeffers"},
)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []

Insert Structs

type User struct {
	FirstName string `db:"first_name"`
	LastName  string `db:"last_name"`
}
ds := goqu.Insert("user").Rows(
	User{FirstName: "Greg", LastName: "Farley"},
	User{FirstName: "Jimmy", LastName: "Stewart"},
	User{FirstName: "Jeff", LastName: "Jeffers"},
)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []

You can skip fields in a struct by using the skipinsert tag

type User struct {
	FirstName string `db:"first_name" goqu:"skipinsert"`
	LastName  string `db:"last_name"`
}
ds := goqu.Insert("user").Rows(
	User{FirstName: "Greg", LastName: "Farley"},
	User{FirstName: "Jimmy", LastName: "Stewart"},
	User{FirstName: "Jeff", LastName: "Jeffers"},
)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("last_name") VALUES ('Farley'), ('Stewart'), ('Jeffers') []

If you do not want to set the database field when the struct field is a nil pointer you can use the omitnil tag.

type item struct {
    FirstName string  `db:"first_name" goqu:"omitnil"`
    LastName  string  `db:"last_name" goqu:"omitnil"`
    Address1  *string `db:"address1" goqu:"omitnil"`
    Address2  *string `db:"address2" goqu:"omitnil"`
    Address3  *string `db:"address3" goqu:"omitnil"`
}
address1 := "111 Test Addr"
var emptyString string
i := item{
    FirstName: "Test First Name",
    LastName:  "",
    Address1:  &address1,
    Address2:  &emptyString,
    Address3:  nil, // will omit nil pointer
}

insertSQL, args, _ := goqu.Insert("items").Rows(i).ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "items" ("address1", "address2", "first_name", "last_name") VALUES ('111 Test Addr', '', 'Test First Name', '') []

If you do not want to set the database field when the struct field is a zero value (including nil pointers) you can use the omitempty tag.

Empty embedded structs implementing the Valuer interface (eg. sql.NullString) will also be omitted.

type item struct {
    FirstName string  `db:"first_name" goqu:"omitempty"`
    LastName  string  `db:"last_name" goqu:"omitempty"`
    Address1  *string `db:"address1" goqu:"omitempty"`
    Address2  *string `db:"address2" goqu:"omitempty"`
    Address3  *string `db:"address3" goqu:"omitempty"`
}
address1 := "112 Test Addr"
var emptyString string
i := item{
    FirstName: "Test First Name",
    LastName:  "", // will omit zero field
    Address1:  &address1,
    Address2:  &emptyString,
    Address3:  nil, // will omit nil pointer
}
insertSQL, args, _ := goqu.Insert("items").Rows(i).ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "items" ("address1", "address2", "first_name") VALUES ('112 Test Addr', '', 'Test First Name') []

If you want to use the database DEFAULT when the struct field is a zero value you can use the defaultifempty tag.

type User struct {
	FirstName string `db:"first_name" goqu:"defaultifempty"`
	LastName  string `db:"last_name"`
}
ds := goqu.Insert("user").Rows(
	User{LastName: "Farley"},
	User{FirstName: "Jimmy", LastName: "Stewart"},
	User{LastName: "Jeffers"},
)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("first_name", "last_name") VALUES (DEFAULT, 'Farley'), ('Jimmy', 'Stewart'), (DEFAULT, 'Jeffers') []

goqu will also use fields in embedded structs when creating an insert.

NOTE unexported fields will be ignored!

type Address struct {
	Street string `db:"address_street"`
	State  string `db:"address_state"`
}
type User struct {
	Address
	FirstName string
	LastName  string
}
ds := goqu.Insert("user").Rows(
	User{Address: Address{Street: "111 Street", State: "NY"}, FirstName: "Greg", LastName: "Farley"},
	User{Address: Address{Street: "211 Street", State: "NY"}, FirstName: "Jimmy", LastName: "Stewart"},
	User{Address: Address{Street: "311 Street", State: "NY"}, FirstName: "Jeff", LastName: "Jeffers"},
)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("address_state", "address_street", "firstname", "lastname") VALUES ('NY', '111 Street', 'Greg', 'Farley'), ('NY', '211 Street', 'Jimmy', 'Stewart'), ('NY', '311 Street', 'Jeff', 'Jeffers') []

NOTE When working with embedded pointers if the embedded struct is nil then the fields will be ignored.

type Address struct {
	Street string
	State  string
}
type User struct {
	*Address
	FirstName string
	LastName  string
}
ds := goqu.Insert("user").Rows(
	User{FirstName: "Greg", LastName: "Farley"},
	User{FirstName: "Jimmy", LastName: "Stewart"},
	User{FirstName: "Jeff", LastName: "Jeffers"},
)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("firstname", "lastname") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []

You can ignore an embedded struct or struct pointer by using db:"-"

type Address struct {
	Street string
	State  string
}
type User struct {
	Address   `db:"-"`
	FirstName string
	LastName  string
}

ds := goqu.Insert("user").Rows(
	User{Address: Address{Street: "111 Street", State: "NY"}, FirstName: "Greg", LastName: "Farley"},
	User{Address: Address{Street: "211 Street", State: "NY"}, FirstName: "Jimmy", LastName: "Stewart"},
	User{Address: Address{Street: "311 Street", State: "NY"}, FirstName: "Jeff", LastName: "Jeffers"},
)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("firstname", "lastname") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []

Insert map[string]interface{}

ds := goqu.Insert("user").Rows(
	map[string]interface{}{"first_name": "Greg", "last_name": "Farley"},
	map[string]interface{}{"first_name": "Jimmy", "last_name": "Stewart"},
	map[string]interface{}{"first_name": "Jeff", "last_name": "Jeffers"},
)
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') []

Insert from query

ds := goqu.Insert("user").Prepared(true).
	FromQuery(goqu.From("other_table"))
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" SELECT * FROM "other_table" []

You can also specify the columns

ds := goqu.Insert("user").Prepared(true).
	Cols("first_name", "last_name").
	FromQuery(goqu.From("other_table").Select("fn", "ln"))
insertSQL, args, _ := ds.ToSQL()
fmt.Println(insertSQL, args)

Output:

INSERT INTO "user" ("first_name", "last_name") SELECT "fn", "ln" FROM "other_table" []

Returning Clause

Returning a single column example.

sql, _, _ := goqu.Insert("test").
	Rows(goqu.Record{"a": "a", "b": "b"}).
	Returning("id").
	ToSQL()
fmt.Println(sql)

Output:

INSERT INTO "test" ("a", "b") VALUES ('a', 'b') RETURNING "id"

Returning multiple columns

sql, _, _ = goqu.Insert("test").
	Rows(goqu.Record{"a": "a", "b": "b"}).
	Returning("a", "b").
	ToSQL()
fmt.Println(sql)

Output:

INSERT INTO "test" ("a", "b") VALUES ('a', 'b') RETURNING "a", "b"

Returning all columns

sql, _, _ = goqu.Insert("test").
	Rows(goqu.Record{"a": "a", "b": "b"}).
	Returning(goqu.T("test").All()).
	ToSQL()
fmt.Println(sql)

Output:

INSERT INTO "test" ("a", "b") VALUES ('a', 'b') RETURNING "test".*

SetError

Sometimes while building up a query with goqu you will encounter situations where certain preconditions are not met or some end-user contraint has been violated. While you could track this error case separately, goqu provides a convenient built-in mechanism to set an error on a dataset if one has not already been set to simplify query building.

Set an Error on a dataset:

func GetInsert(name string, value string) *goqu.InsertDataset {

    var ds = goqu.Insert("test")

    if len(field) == 0 {
        return ds.SetError(fmt.Errorf("name is empty"))
    }

    if len(value) == 0 {
        return ds.SetError(fmt.Errorf("value is empty"))
    }

    return ds.Rows(goqu.Record{name: value})
}

This error is returned on any subsequent call to Error or ToSQL:

var field, value string
ds = GetInsert(field, value)
fmt.Println(ds.Error())

sql, args, err = ds.ToSQL()
fmt.Println(err)

Output:

name is empty
name is empty

Executing Inserts

To execute INSERTS use Database.Insert to create your dataset

Examples

Executing an single Insert

db := getDb()

insert := db.Insert("goqu_user").Rows(
	goqu.Record{"first_name": "Jed", "last_name": "Riley", "created": time.Now()},
).Executor()

if _, err := insert.Exec(); err != nil {
	fmt.Println(err.Error())
} else {
	fmt.Println("Inserted 1 user")
}

Output:

Inserted 1 user

Executing multiple inserts

db := getDb()

users := []goqu.Record{
	{"first_name": "Greg", "last_name": "Farley", "created": time.Now()},
	{"first_name": "Jimmy", "last_name": "Stewart", "created": time.Now()},
	{"first_name": "Jeff", "last_name": "Jeffers", "created": time.Now()},
}

insert := db.Insert("goqu_user").Rows(users).Executor()
if _, err := insert.Exec(); err != nil {
	fmt.Println(err.Error())
} else {
	fmt.Printf("Inserted %d users", len(users))
}

Output:

Inserted 3 users

If you use the RETURNING clause you can scan into structs or values.

db := getDb()

insert := db.Insert("goqu_user").Returning(goqu.C("id")).Rows(
		goqu.Record{"first_name": "Jed", "last_name": "Riley", "created": time.Now()},
).Executor()

var id int64
if _, err := insert.ScanVal(&id); err != nil {
	fmt.Println(err.Error())
} else {
	fmt.Printf("Inserted 1 user id:=%d\n", id)
}

Output:

Inserted 1 user id:=5