1# Inserting 2 3* [Creating An InsertDataset](#create) 4* Examples 5 * [Insert Cols and Vals](#insert-cols-vals) 6 * [Insert `goqu.Record`](#insert-record) 7 * [Insert Structs](#insert-structs) 8 * [Insert Map](#insert-map) 9 * [Insert From Query](#insert-from-query) 10 * [Returning](#returning) 11 * [SetError](#seterror) 12 * [Executing](#executing) 13 14<a name="create"></a> 15To create a [`InsertDataset`](https://godoc.org/github.com/doug-martin/goqu/#InsertDataset) you can use 16 17**[`goqu.Insert`](https://godoc.org/github.com/doug-martin/goqu/#Insert)** 18 19When you just want to create some quick SQL, this mostly follows the `Postgres` with the exception of placeholders for prepared statements. 20 21```go 22ds := goqu.Insert("user").Rows( 23 goqu.Record{"first_name": "Greg", "last_name": "Farley"}, 24) 25insertSQL, _, _ := ds.ToSQL() 26fmt.Println(insertSQL, args) 27``` 28Output: 29``` 30INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley') 31``` 32 33**[`SelectDataset.Insert`](https://godoc.org/github.com/doug-martin/goqu/#SelectDataset.Insert)** 34 35If you already have a `SelectDataset` you can invoke `Insert()` to get a `InsertDataset` 36 37**NOTE** This method will also copy over the `WITH` clause as well as the `FROM` 38 39```go 40ds := goqu.From("user") 41 42ds := ds.Insert().Rows( 43 goqu.Record{"first_name": "Greg", "last_name": "Farley"}, 44) 45insertSQL, _, _ := ds.ToSQL() 46fmt.Println(insertSQL, args) 47``` 48Output: 49``` 50INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley') 51``` 52 53**[`DialectWrapper.Insert`](https://godoc.org/github.com/doug-martin/goqu/#DialectWrapper.Insert)** 54 55Use this when you want to create SQL for a specific `dialect` 56 57```go 58// import _ "github.com/doug-martin/goqu/v9/dialect/mysql" 59 60dialect := goqu.Dialect("mysql") 61 62ds := dialect.Insert().Rows( 63 goqu.Record{"first_name": "Greg", "last_name": "Farley"}, 64) 65insertSQL, _, _ := ds.ToSQL() 66fmt.Println(insertSQL, args) 67``` 68Output: 69``` 70INSERT INTO `user` (`first_name`, `last_name`) VALUES ('Greg', 'Farley') 71``` 72 73**[`Database.Insert`](https://godoc.org/github.com/doug-martin/goqu/#DialectWrapper.Insert)** 74 75Use this when you want to execute the SQL or create SQL for the drivers dialect. 76 77```go 78// import _ "github.com/doug-martin/goqu/v9/dialect/mysql" 79 80mysqlDB := //initialize your db 81db := goqu.New("mysql", mysqlDB) 82 83ds := db.Insert().Rows( 84 goqu.Record{"first_name": "Greg", "last_name": "Farley"}, 85) 86insertSQL, _, _ := ds.ToSQL() 87fmt.Println(insertSQL, args) 88``` 89Output: 90``` 91INSERT INTO `user` (`first_name`, `last_name`) VALUES ('Greg', 'Farley') 92``` 93 94### Examples 95 96For more examples visit the **[Docs](https://godoc.org/github.com/doug-martin/goqu/#InsertDataset)** 97 98<a name="insert-cols-vals"></a> 99**Insert with Cols and Vals** 100 101```go 102ds := goqu.Insert("user"). 103 Cols("first_name", "last_name"). 104 Vals( 105 goqu.Vals{"Greg", "Farley"}, 106 goqu.Vals{"Jimmy", "Stewart"}, 107 goqu.Vals{"Jeff", "Jeffers"}, 108 ) 109insertSQL, args, _ := ds.ToSQL() 110fmt.Println(insertSQL, args) 111``` 112 113Output: 114```sql 115INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') [] 116``` 117 118<a name="insert-record"></a> 119**Insert `goqu.Record`** 120 121```go 122ds := goqu.Insert("user").Rows( 123 goqu.Record{"first_name": "Greg", "last_name": "Farley"}, 124 goqu.Record{"first_name": "Jimmy", "last_name": "Stewart"}, 125 goqu.Record{"first_name": "Jeff", "last_name": "Jeffers"}, 126) 127insertSQL, args, _ := ds.ToSQL() 128fmt.Println(insertSQL, args) 129``` 130 131Output: 132``` 133INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') [] 134``` 135 136<a name="insert-structs"></a> 137**Insert Structs** 138 139```go 140type User struct { 141 FirstName string `db:"first_name"` 142 LastName string `db:"last_name"` 143} 144ds := goqu.Insert("user").Rows( 145 User{FirstName: "Greg", LastName: "Farley"}, 146 User{FirstName: "Jimmy", LastName: "Stewart"}, 147 User{FirstName: "Jeff", LastName: "Jeffers"}, 148) 149insertSQL, args, _ := ds.ToSQL() 150fmt.Println(insertSQL, args) 151``` 152 153Output: 154``` 155INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') [] 156``` 157 158You can skip fields in a struct by using the `skipinsert` tag 159 160```go 161type User struct { 162 FirstName string `db:"first_name" goqu:"skipinsert"` 163 LastName string `db:"last_name"` 164} 165ds := goqu.Insert("user").Rows( 166 User{FirstName: "Greg", LastName: "Farley"}, 167 User{FirstName: "Jimmy", LastName: "Stewart"}, 168 User{FirstName: "Jeff", LastName: "Jeffers"}, 169) 170insertSQL, args, _ := ds.ToSQL() 171fmt.Println(insertSQL, args) 172``` 173 174Output: 175``` 176INSERT INTO "user" ("last_name") VALUES ('Farley'), ('Stewart'), ('Jeffers') [] 177``` 178 179If you want to use the database `DEFAULT` when the struct field is a zero value you can use the `defaultifempty` tag. 180 181```go 182type User struct { 183 FirstName string `db:"first_name" goqu:"defaultifempty"` 184 LastName string `db:"last_name"` 185} 186ds := goqu.Insert("user").Rows( 187 User{LastName: "Farley"}, 188 User{FirstName: "Jimmy", LastName: "Stewart"}, 189 User{LastName: "Jeffers"}, 190) 191insertSQL, args, _ := ds.ToSQL() 192fmt.Println(insertSQL, args) 193``` 194 195Output: 196``` 197INSERT INTO "user" ("first_name", "last_name") VALUES (DEFAULT, 'Farley'), ('Jimmy', 'Stewart'), (DEFAULT, 'Jeffers') [] 198``` 199 200`goqu` will also use fields in embedded structs when creating an insert. 201 202**NOTE** unexported fields will be ignored! 203 204```go 205type Address struct { 206 Street string `db:"address_street"` 207 State string `db:"address_state"` 208} 209type User struct { 210 Address 211 FirstName string 212 LastName string 213} 214ds := goqu.Insert("user").Rows( 215 User{Address: Address{Street: "111 Street", State: "NY"}, FirstName: "Greg", LastName: "Farley"}, 216 User{Address: Address{Street: "211 Street", State: "NY"}, FirstName: "Jimmy", LastName: "Stewart"}, 217 User{Address: Address{Street: "311 Street", State: "NY"}, FirstName: "Jeff", LastName: "Jeffers"}, 218) 219insertSQL, args, _ := ds.ToSQL() 220fmt.Println(insertSQL, args) 221``` 222 223Output: 224``` 225INSERT INTO "user" ("address_state", "address_street", "firstname", "lastname") VALUES ('NY', '111 Street', 'Greg', 'Farley'), ('NY', '211 Street', 'Jimmy', 'Stewart'), ('NY', '311 Street', 'Jeff', 'Jeffers') [] 226``` 227 228**NOTE** When working with embedded pointers if the embedded struct is nil then the fields will be ignored. 229 230```go 231type Address struct { 232 Street string 233 State string 234} 235type User struct { 236 *Address 237 FirstName string 238 LastName string 239} 240ds := goqu.Insert("user").Rows( 241 User{FirstName: "Greg", LastName: "Farley"}, 242 User{FirstName: "Jimmy", LastName: "Stewart"}, 243 User{FirstName: "Jeff", LastName: "Jeffers"}, 244) 245insertSQL, args, _ := ds.ToSQL() 246fmt.Println(insertSQL, args) 247``` 248 249Output: 250``` 251INSERT INTO "user" ("firstname", "lastname") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') [] 252``` 253 254You can ignore an embedded struct or struct pointer by using `db:"-"` 255 256```go 257type Address struct { 258 Street string 259 State string 260} 261type User struct { 262 Address `db:"-"` 263 FirstName string 264 LastName string 265} 266 267ds := goqu.Insert("user").Rows( 268 User{Address: Address{Street: "111 Street", State: "NY"}, FirstName: "Greg", LastName: "Farley"}, 269 User{Address: Address{Street: "211 Street", State: "NY"}, FirstName: "Jimmy", LastName: "Stewart"}, 270 User{Address: Address{Street: "311 Street", State: "NY"}, FirstName: "Jeff", LastName: "Jeffers"}, 271) 272insertSQL, args, _ := ds.ToSQL() 273fmt.Println(insertSQL, args) 274``` 275 276Output: 277``` 278INSERT INTO "user" ("firstname", "lastname") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') [] 279``` 280 281<a name="insert-map"></a> 282**Insert `map[string]interface{}`** 283 284```go 285ds := goqu.Insert("user").Rows( 286 map[string]interface{}{"first_name": "Greg", "last_name": "Farley"}, 287 map[string]interface{}{"first_name": "Jimmy", "last_name": "Stewart"}, 288 map[string]interface{}{"first_name": "Jeff", "last_name": "Jeffers"}, 289) 290insertSQL, args, _ := ds.ToSQL() 291fmt.Println(insertSQL, args) 292``` 293 294Output: 295``` 296INSERT INTO "user" ("first_name", "last_name") VALUES ('Greg', 'Farley'), ('Jimmy', 'Stewart'), ('Jeff', 'Jeffers') [] 297``` 298 299<a name="insert-from-query"></a> 300**Insert from query** 301 302```go 303ds := goqu.Insert("user").Prepared(true). 304 FromQuery(goqu.From("other_table")) 305insertSQL, args, _ := ds.ToSQL() 306fmt.Println(insertSQL, args) 307``` 308 309Output: 310``` 311INSERT INTO "user" SELECT * FROM "other_table" [] 312``` 313 314You can also specify the columns 315 316```go 317ds := goqu.Insert("user").Prepared(true). 318 Cols("first_name", "last_name"). 319 FromQuery(goqu.From("other_table").Select("fn", "ln")) 320insertSQL, args, _ := ds.ToSQL() 321fmt.Println(insertSQL, args) 322``` 323 324Output: 325``` 326INSERT INTO "user" ("first_name", "last_name") SELECT "fn", "ln" FROM "other_table" [] 327``` 328 329<a name="returning"></a> 330**Returning Clause** 331 332Returning a single column example. 333 334```go 335sql, _, _ := goqu.Insert("test"). 336 Rows(goqu.Record{"a": "a", "b": "b"}). 337 Returning("id"). 338 ToSQL() 339fmt.Println(sql) 340``` 341 342Output: 343``` 344INSERT INTO "test" ("a", "b") VALUES ('a', 'b') RETURNING "id" 345``` 346 347Returning multiple columns 348 349```go 350sql, _, _ = goqu.Insert("test"). 351 Rows(goqu.Record{"a": "a", "b": "b"}). 352 Returning("a", "b"). 353 ToSQL() 354fmt.Println(sql) 355``` 356 357Output: 358``` 359INSERT INTO "test" ("a", "b") VALUES ('a', 'b') RETURNING "a", "b" 360``` 361 362Returning all columns 363 364```go 365sql, _, _ = goqu.Insert("test"). 366 Rows(goqu.Record{"a": "a", "b": "b"}). 367 Returning(goqu.T("test").All()). 368 ToSQL() 369fmt.Println(sql) 370``` 371 372Output: 373``` 374INSERT INTO "test" ("a", "b") VALUES ('a', 'b') RETURNING "test".* 375``` 376 377<a name="seterror"></a> 378**[`SetError`](https://godoc.org/github.com/doug-martin/goqu/#InsertDataset.SetError)** 379 380Sometimes while building up a query with goqu you will encounter situations where certain 381preconditions are not met or some end-user contraint has been violated. While you could 382track this error case separately, goqu provides a convenient built-in mechanism to set an 383error on a dataset if one has not already been set to simplify query building. 384 385Set an Error on a dataset: 386 387```go 388func GetInsert(name string, value string) *goqu.InsertDataset { 389 390 var ds = goqu.Insert("test") 391 392 if len(field) == 0 { 393 return ds.SetError(fmt.Errorf("name is empty")) 394 } 395 396 if len(value) == 0 { 397 return ds.SetError(fmt.Errorf("value is empty")) 398 } 399 400 return ds.Rows(goqu.Record{name: value}) 401} 402 403``` 404 405This error is returned on any subsequent call to `Error` or `ToSQL`: 406 407```go 408var field, value string 409ds = GetInsert(field, value) 410fmt.Println(ds.Error()) 411 412sql, args, err = ds.ToSQL() 413fmt.Println(err) 414``` 415 416Output: 417``` 418name is empty 419name is empty 420``` 421 422<a name="executing"></a> 423## Executing Inserts 424 425To execute INSERTS use [`Database.Insert`](https://godoc.org/github.com/doug-martin/goqu/#Database.Insert) to create your dataset 426 427### Examples 428 429**Executing an single Insert** 430```go 431db := getDb() 432 433insert := db.Insert("goqu_user").Rows( 434 goqu.Record{"first_name": "Jed", "last_name": "Riley", "created": time.Now()}, 435).Executor() 436 437if _, err := insert.Exec(); err != nil { 438 fmt.Println(err.Error()) 439} else { 440 fmt.Println("Inserted 1 user") 441} 442``` 443 444Output: 445 446``` 447Inserted 1 user 448``` 449 450**Executing multiple inserts** 451 452```go 453db := getDb() 454 455users := []goqu.Record{ 456 {"first_name": "Greg", "last_name": "Farley", "created": time.Now()}, 457 {"first_name": "Jimmy", "last_name": "Stewart", "created": time.Now()}, 458 {"first_name": "Jeff", "last_name": "Jeffers", "created": time.Now()}, 459} 460 461insert := db.Insert("goqu_user").Rows(users).Executor() 462if _, err := insert.Exec(); err != nil { 463 fmt.Println(err.Error()) 464} else { 465 fmt.Printf("Inserted %d users", len(users)) 466} 467 468``` 469 470Output: 471``` 472Inserted 3 users 473``` 474 475If you use the RETURNING clause you can scan into structs or values. 476 477```go 478db := getDb() 479 480insert := db.Insert("goqu_user").Returning(goqu.C("id")).Rows( 481 goqu.Record{"first_name": "Jed", "last_name": "Riley", "created": time.Now()}, 482).Executor() 483 484var id int64 485if _, err := insert.ScanVal(&id); err != nil { 486 fmt.Println(err.Error()) 487} else { 488 fmt.Printf("Inserted 1 user id:=%d\n", id) 489} 490``` 491 492Output: 493 494``` 495Inserted 1 user id:=5 496``` 497