{"_id":"576c42596c24681700c90321","version":{"_id":"57531435be31940e0035ad96","project":"56e5982f9191742000ef204a","__v":3,"createdAt":"2016-06-04T17:47:33.623Z","releaseDate":"2016-06-04T17:47:33.623Z","categories":["57531435be31940e0035ad97","57531435be31940e0035ad98","57531652d8fe070e00258a51","576c609fba92910e0099ad3f"],"is_deprecated":false,"is_hidden":false,"is_beta":true,"is_stable":true,"codename":"","version_clean":"0.2.0","version":"0.2"},"githubsync":"","parentDoc":null,"category":{"_id":"57531652d8fe070e00258a51","__v":0,"project":"56e5982f9191742000ef204a","version":"57531435be31940e0035ad96","sync":{"url":"","isSync":false},"reference":false,"createdAt":"2016-06-04T17:56:34.972Z","from_sync":false,"order":2,"slug":"expression-api","title":"Expression API"},"project":"56e5982f9191742000ef204a","__v":26,"user":"543466ea0e8e2b0e00341818","updates":[],"next":{"pages":[],"description":""},"createdAt":"2016-06-23T20:11:05.544Z","link_external":false,"link_url":"","sync_unique":"","hidden":false,"api":{"results":{"codes":[]},"settings":"","auth":"required","params":[],"url":""},"isReference":false,"order":3,"body":"In the expression API documentation above, there is only documentation for generating sql & bindings from helper functions. Now, we'll be dealing with executing statements using qb engine.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Executing Inserts, Upserts, Updates & Deletes\"\n}\n[/block]\nThe following table & qb initializations are assumed;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"package main\\n\\nimport (\\n\\t\\\"fmt\\\"\\n\\t\\\"github.com/aacanakin/qb\\\"\\n)\\n\\nfunc main() {\\n\\tdb, _ := qb.New(\\\"mysql\\\", \\\"root::::at:::tcp(localhost:3306)/qb_test?charset=utf8\\\")\\n\\tdefer db.Close()\\n\\n\\tusers := qb.Table(\\n\\t\\t\\\"user\\\",\\n\\t\\tqb.Column(\\\"id\\\", qb.Varchar().Size(36)),\\n\\t\\tqb.Column(\\\"email\\\", qb.Varchar().Unique().NotNull()),\\n\\t\\tqb.Column(\\\"name\\\", qb.Varchar().NotNull()),\\n\\t\\tqb.PrimaryKey(\\\"id\\\"),\\n\\t)\\n\\n\\tmetadata := qb.MetaData(db.Dialect())\\n\\tmetadata.AddTable(users)\\n\\terr := metadata.CreateAll(db.Engine())\\n\\tcheckErr(err)\\n  defer metadata.DropAll(db.Engine())\\n  \\n  // insert examples here\\n}\\n\\nfunc checkErr(err error) {\\n\\tif err != nil {\\n\\t\\tpanic(err)\\n\\t}\\n}\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\nA simple insert statement can be executed by the following;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"ins := users.\\n\\t\\tInsert().\\n\\t\\tValues(map[string]interface{}{\\n\\t\\t\\t\\\"id\\\":    \\\"f82cdc-4d21-473b-a175-cbc3f9119eda\\\",\\n\\t\\t\\t\\\"email\\\": \\\"al@pacino.com\\\",\\n\\t\\t\\t\\\"name\\\":  \\\"Al Pacino\\\",\\n\\t\\t}).\\n\\t\\tBuild(db.Dialect())\\n\\nres, err := db.Engine().Exec(ins)\\ncheckErr(err)\\n\\ninsertId, err := res.LastInsertId()\\ncheckErr(err)\\n\\nrowsAffected, err := res.RowsAffected()\\ncheckErr(err)\\n\\nfmt.Printf(\\\"insertId: %d\\\\n\\\", insertId)\\nfmt.Printf(\\\"rowsAffected: %d\\\\n\\\", rowsAffected)\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\nThe output would be the following;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"insertId: 0\\nrowsAffected: 1\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nThe Update, Upsert & Delete statements are executed using Exec functions just the same with Insert.\n\nYou can use `Exec()` in any sql statements that inserts or updates data. Sqlite & mysql works fine in LastInsertId() & RowsAffected() functions. However, due to the driver implementation of postgres, the driver satisfies the exec interface but does give meaningless results.\n[block:callout]\n{\n  \"type\": \"danger\",\n  \"title\": \"Postgres driver does not support LastInsertId() & RowsAffected()\"\n}\n[/block]\nTherefore, there is an optional Returning clause just for postgres to keep retrieving results. Here's the following example shows how to retrieve by returning clause;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"package main\\n\\nimport (\\n\\t\\\"fmt\\\"\\n\\t\\\"github.com/aacanakin/qb\\\"\\n)\\n\\nfunc main() {\\n\\tdb, _ := qb.New(\\\"postgres\\\", \\\"user=postgres dbname=qb_test sslmode=disable\\\")\\n\\tdefer db.Close()\\n\\n\\tdb.Dialect().SetEscaping(true)\\n\\n\\tusers := qb.Table(\\n\\t\\t\\\"user\\\",\\n\\t\\tqb.Column(\\\"id\\\", qb.Varchar().Size(36)),\\n\\t\\tqb.Column(\\\"email\\\", qb.Varchar().Unique().NotNull()),\\n\\t\\tqb.Column(\\\"name\\\", qb.Varchar().NotNull()),\\n\\t\\tqb.PrimaryKey(\\\"id\\\"),\\n\\t)\\n\\n\\tmetadata := qb.MetaData(db.Dialect())\\n\\tmetadata.AddTable(users)\\n\\terr := metadata.CreateAll(db.Engine())\\n\\tdefer metadata.DropAll(db.Engine())\\n\\tcheckErr(err)\\n\\n\\tins := users.\\n\\t\\tInsert().\\n\\t\\tValues(map[string]interface{}{\\n\\t\\t\\t\\\"id\\\":    \\\"f82cdc-4d21-473b-a175-cbc3f9119eda\\\",\\n\\t\\t\\t\\\"email\\\": \\\"al@pacino.com\\\",\\n\\t\\t\\t\\\"name\\\":  \\\"Al Pacino\\\",\\n\\t\\t}).\\n\\t\\tReturning(users.C(\\\"id\\\")).\\n\\t\\tBuild(db.Dialect())\\n\\n\\tvar id string\\n\\tdb.Engine().QueryRow(ins).Scan(&id)\\n\\tcheckErr(err)\\n\\tfmt.Printf(\\\"id: %s\\\\n\\\", id)\\n}\\n\\nfunc checkErr(err error) {\\n\\tif err != nil {\\n\\t\\tpanic(err)\\n\\t}\\n}\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\nThis outputs the following;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"id: f82cdc-4d21-473b-a175-cbc3f9119eda\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nThe Returning clause is available for Insert(), Upsert(), Update() & Delete() functions.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Executing selects\"\n}\n[/block]\nThe select executor functions are Query(), QueryRow(), Get() & Select().\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Select using Query() & QueryRow()\"\n}\n[/block]\nSelects can be executed using Query() & QueryRow() functions as the following;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"sel := users.\\n\\tSelect(users.C(\\\"id\\\")).\\n\\tWhere(users.C(\\\"email\\\").Eq(\\\"al@pacino.com\\\")).\\n\\tBuild(db.Dialect())\\n\\nvar id string\\ndb.Engine().QueryRow(sel).Scan(&id)\\nfmt.Println(\\\"id:\\\", id)\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\nOutput is;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"id: f82cdc-4d21-473b-a175-cbc3f9119eda\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nThe Query function can also be used in the following example;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"sel := users.\\n\\t\\tSelect(users.C(\\\"id\\\")).\\n\\t\\tWhere(users.C(\\\"email\\\").Eq(\\\"al@pacino.com\\\")).\\n\\t\\tBuild(db.Dialect())\\n\\nids := []string{}\\nrows, err := db.Engine().Query(sel)\\ncheckErr(err)\\nfor rows.Next() {\\n\\tvar id string\\n\\terr = rows.Scan(&id)\\n\\tcheckErr(err)\\n\\tids = append(ids, id)\\n}\\nfmt.Println(\\\"ids:\\\", ids)\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\nOutput is;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"ids: [f82cdc-4d21-473b-a175-cbc3f9119eda]\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nTherefore, Query() function can be used to extract multiple rows from db.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Select using Get() & Select()\"\n}\n[/block]\nGet() & Select() functions of Engine is for mapping results to queries. Check out the following example with Get();\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"sel := users.\\n\\tSelect(users.C(\\\"id\\\")).\\n\\tWhere(users.C(\\\"email\\\").Eq(\\\"al@pacino.com\\\")).\\n\\tBuild(db.Dialect())\\n\\ntype User struct {\\n\\tID string\\n}\\n\\nvar user User\\nerr = db.Engine().Get(sel, &user)\\ncheckErr(err)\\n\\nfmt.Printf(\\\"User%+v\\\\n\\\", user)\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nOutput is;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"User{ID:f82cdc-4d21-473b-a175-cbc3f9119eda}\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nThe same example can be done by using Select() & user slice;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"sel := users.\\n\\t\\tSelect(users.C(\\\"id\\\")).\\n\\t\\tWhere(users.C(\\\"email\\\").Eq(\\\"al@pacino.com\\\")).\\n\\t\\tBuild(db.Dialect())\\n\\ntype User struct {\\n\\tID string\\n}\\n\\nvar selUsers []User\\nerr = db.Engine().Select(sel, &selUsers)\\ncheckErr(err)\\n\\nfmt.Printf(\\\"Users%+v\\\\n\\\", selUsers)\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\nAs you might notice, Select() can be used for mapping result sets to multiple structs.\n[block:callout]\n{\n  \"type\": \"warning\",\n  \"title\": \"Joins are not \\\"Select() & Get()\\\"able\",\n  \"body\": \"Currently, Select() & Get() functions can be used only while retrieving result sets from a single table. This issue will be addressed in 0.3 milestone of qb.\"\n}\n[/block]","excerpt":"","slug":"executing-statements","type":"basic","title":"Executing statements"}

Executing statements


In the expression API documentation above, there is only documentation for generating sql & bindings from helper functions. Now, we'll be dealing with executing statements using qb engine. [block:api-header] { "type": "basic", "title": "Executing Inserts, Upserts, Updates & Deletes" } [/block] The following table & qb initializations are assumed; [block:code] { "codes": [ { "code": "package main\n\nimport (\n\t\"fmt\"\n\t\"github.com/aacanakin/qb\"\n)\n\nfunc main() {\n\tdb, _ := qb.New(\"mysql\", \"root:@tcp(localhost:3306)/qb_test?charset=utf8\")\n\tdefer db.Close()\n\n\tusers := qb.Table(\n\t\t\"user\",\n\t\tqb.Column(\"id\", qb.Varchar().Size(36)),\n\t\tqb.Column(\"email\", qb.Varchar().Unique().NotNull()),\n\t\tqb.Column(\"name\", qb.Varchar().NotNull()),\n\t\tqb.PrimaryKey(\"id\"),\n\t)\n\n\tmetadata := qb.MetaData(db.Dialect())\n\tmetadata.AddTable(users)\n\terr := metadata.CreateAll(db.Engine())\n\tcheckErr(err)\n defer metadata.DropAll(db.Engine())\n \n // insert examples here\n}\n\nfunc checkErr(err error) {\n\tif err != nil {\n\t\tpanic(err)\n\t}\n}", "language": "go" } ] } [/block] A simple insert statement can be executed by the following; [block:code] { "codes": [ { "code": "ins := users.\n\t\tInsert().\n\t\tValues(map[string]interface{}{\n\t\t\t\"id\": \"f82cdc-4d21-473b-a175-cbc3f9119eda\",\n\t\t\t\"email\": \"al@pacino.com\",\n\t\t\t\"name\": \"Al Pacino\",\n\t\t}).\n\t\tBuild(db.Dialect())\n\nres, err := db.Engine().Exec(ins)\ncheckErr(err)\n\ninsertId, err := res.LastInsertId()\ncheckErr(err)\n\nrowsAffected, err := res.RowsAffected()\ncheckErr(err)\n\nfmt.Printf(\"insertId: %d\\n\", insertId)\nfmt.Printf(\"rowsAffected: %d\\n\", rowsAffected)", "language": "go" } ] } [/block] The output would be the following; [block:code] { "codes": [ { "code": "insertId: 0\nrowsAffected: 1", "language": "text" } ] } [/block] The Update, Upsert & Delete statements are executed using Exec functions just the same with Insert. You can use `Exec()` in any sql statements that inserts or updates data. Sqlite & mysql works fine in LastInsertId() & RowsAffected() functions. However, due to the driver implementation of postgres, the driver satisfies the exec interface but does give meaningless results. [block:callout] { "type": "danger", "title": "Postgres driver does not support LastInsertId() & RowsAffected()" } [/block] Therefore, there is an optional Returning clause just for postgres to keep retrieving results. Here's the following example shows how to retrieve by returning clause; [block:code] { "codes": [ { "code": "package main\n\nimport (\n\t\"fmt\"\n\t\"github.com/aacanakin/qb\"\n)\n\nfunc main() {\n\tdb, _ := qb.New(\"postgres\", \"user=postgres dbname=qb_test sslmode=disable\")\n\tdefer db.Close()\n\n\tdb.Dialect().SetEscaping(true)\n\n\tusers := qb.Table(\n\t\t\"user\",\n\t\tqb.Column(\"id\", qb.Varchar().Size(36)),\n\t\tqb.Column(\"email\", qb.Varchar().Unique().NotNull()),\n\t\tqb.Column(\"name\", qb.Varchar().NotNull()),\n\t\tqb.PrimaryKey(\"id\"),\n\t)\n\n\tmetadata := qb.MetaData(db.Dialect())\n\tmetadata.AddTable(users)\n\terr := metadata.CreateAll(db.Engine())\n\tdefer metadata.DropAll(db.Engine())\n\tcheckErr(err)\n\n\tins := users.\n\t\tInsert().\n\t\tValues(map[string]interface{}{\n\t\t\t\"id\": \"f82cdc-4d21-473b-a175-cbc3f9119eda\",\n\t\t\t\"email\": \"al@pacino.com\",\n\t\t\t\"name\": \"Al Pacino\",\n\t\t}).\n\t\tReturning(users.C(\"id\")).\n\t\tBuild(db.Dialect())\n\n\tvar id string\n\tdb.Engine().QueryRow(ins).Scan(&id)\n\tcheckErr(err)\n\tfmt.Printf(\"id: %s\\n\", id)\n}\n\nfunc checkErr(err error) {\n\tif err != nil {\n\t\tpanic(err)\n\t}\n}", "language": "go" } ] } [/block] This outputs the following; [block:code] { "codes": [ { "code": "id: f82cdc-4d21-473b-a175-cbc3f9119eda", "language": "text" } ] } [/block] The Returning clause is available for Insert(), Upsert(), Update() & Delete() functions. [block:api-header] { "type": "basic", "title": "Executing selects" } [/block] The select executor functions are Query(), QueryRow(), Get() & Select(). [block:api-header] { "type": "basic", "title": "Select using Query() & QueryRow()" } [/block] Selects can be executed using Query() & QueryRow() functions as the following; [block:code] { "codes": [ { "code": "sel := users.\n\tSelect(users.C(\"id\")).\n\tWhere(users.C(\"email\").Eq(\"al@pacino.com\")).\n\tBuild(db.Dialect())\n\nvar id string\ndb.Engine().QueryRow(sel).Scan(&id)\nfmt.Println(\"id:\", id)", "language": "go" } ] } [/block] Output is; [block:code] { "codes": [ { "code": "id: f82cdc-4d21-473b-a175-cbc3f9119eda", "language": "text" } ] } [/block] The Query function can also be used in the following example; [block:code] { "codes": [ { "code": "sel := users.\n\t\tSelect(users.C(\"id\")).\n\t\tWhere(users.C(\"email\").Eq(\"al@pacino.com\")).\n\t\tBuild(db.Dialect())\n\nids := []string{}\nrows, err := db.Engine().Query(sel)\ncheckErr(err)\nfor rows.Next() {\n\tvar id string\n\terr = rows.Scan(&id)\n\tcheckErr(err)\n\tids = append(ids, id)\n}\nfmt.Println(\"ids:\", ids)", "language": "go" } ] } [/block] Output is; [block:code] { "codes": [ { "code": "ids: [f82cdc-4d21-473b-a175-cbc3f9119eda]", "language": "text" } ] } [/block] Therefore, Query() function can be used to extract multiple rows from db. [block:api-header] { "type": "basic", "title": "Select using Get() & Select()" } [/block] Get() & Select() functions of Engine is for mapping results to queries. Check out the following example with Get(); [block:code] { "codes": [ { "code": "sel := users.\n\tSelect(users.C(\"id\")).\n\tWhere(users.C(\"email\").Eq(\"al@pacino.com\")).\n\tBuild(db.Dialect())\n\ntype User struct {\n\tID string\n}\n\nvar user User\nerr = db.Engine().Get(sel, &user)\ncheckErr(err)\n\nfmt.Printf(\"User%+v\\n\", user)", "language": "text" } ] } [/block] Output is; [block:code] { "codes": [ { "code": "User{ID:f82cdc-4d21-473b-a175-cbc3f9119eda}", "language": "text" } ] } [/block] The same example can be done by using Select() & user slice; [block:code] { "codes": [ { "code": "sel := users.\n\t\tSelect(users.C(\"id\")).\n\t\tWhere(users.C(\"email\").Eq(\"al@pacino.com\")).\n\t\tBuild(db.Dialect())\n\ntype User struct {\n\tID string\n}\n\nvar selUsers []User\nerr = db.Engine().Select(sel, &selUsers)\ncheckErr(err)\n\nfmt.Printf(\"Users%+v\\n\", selUsers)", "language": "go" } ] } [/block] As you might notice, Select() can be used for mapping result sets to multiple structs. [block:callout] { "type": "warning", "title": "Joins are not \"Select() & Get()\"able", "body": "Currently, Select() & Get() functions can be used only while retrieving result sets from a single table. This issue will be addressed in 0.3 milestone of qb." } [/block]