{"_id":"576c025774a8640e004ced68","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"},"user":"543466ea0e8e2b0e00341818","githubsync":"","project":"56e5982f9191742000ef204a","__v":14,"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"},"parentDoc":null,"updates":[],"next":{"pages":[],"description":""},"createdAt":"2016-06-23T15:37:59.270Z","link_external":false,"link_url":"","sync_unique":"","hidden":false,"api":{"settings":"","results":{"codes":[]},"auth":"required","params":[],"url":""},"isReference":false,"order":1,"body":"After 0.1 release, the builder is decentralized into separate query builders such as Insert, Update, Upsert, Delete and Select. This change allows more strict restrictions to build queries. It also prevents errors while building queries.\n\nSuppose you have the following table defined;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"\\tusers := qb.Table(\\\"user\\\",\\n\\t\\tqb.Column(\\\"id\\\", qb.Varchar().Size(36)),\\n\\t\\tqb.Column(\\\"email\\\", qb.Varchar().Unique().NotNull()),\\n\\t\\tqb.PrimaryKey(\\\"id\\\"),\\n\\t)\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\n\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Insert\"\n}\n[/block]\nThere are 2 ways to build insert queries in expression api;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"ins := qb.Insert(users).Values(map[string]interface{}{\\n\\t\\t\\\"id\\\":    \\\"3af82cdc-4d21-473b-a175-cbc3f9119eda\\\",\\n\\t\\t\\\"email\\\": \\\"al:::at:::pacino.com\\\",\\n\\t}).Build(db.Dialect())\\n\\nfmt.Println(ins.SQL())\\nfmt.Println(ins.Bindings())\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\nIn this instance, the following output would be printed;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"INSERT INTO user(id, email)\\nVALUES($1, $2);\\n[3af82cdc-4d21-473b-a175-cbc3f9119eda al@pacino.com]\",\n      \"language\": \"sql\"\n    }\n  ]\n}\n[/block]\nAnother option would be to generate the insert statement using table object;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"ins := users.Insert().Values(map[string]interface{}{\\n\\t\\t\\\"id\\\":    \\\"3af82cdc-4d21-473b-a175-cbc3f9119eda\\\",\\n\\t\\t\\\"email\\\": \\\"al@pacino.com\\\",\\n\\t}).Build(db.Dialect())\\n\\nfmt.Println(ins.SQL())\\nfmt.Println(ins.Bindings())\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\n\n[block:callout]\n{\n  \"type\": \"warning\",\n  \"title\": \"Not recommended\",\n  \"body\": \"Although it is possible to build insert statements using qb.Insert() function, it is not recommended to use because the table based users.Insert().Values() clause is much simpler.\"\n}\n[/block]\nThe output would be the same as the previous example above.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Update\"\n}\n[/block]\nThe update syntax is very similar to insert.\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"upd := users.\\n\\t\\tUpdate().\\n\\t\\tValues(map[string]interface{}{\\n\\t\\t\\t\\\"email\\\": \\\"al@pacino.com\\\",\\n\\t\\t}).\\n\\t\\tWhere(users.C(\\\"id\\\").Eq(\\\"3af82cdc-4d21-473b-a175-cbc3f9119eda\\\")).\\n\\t\\tBuild(db.Dialect())\\n\\nfmt.Println(upd.SQL())\\nfmt.Println(upd.Bindings())\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\n\n[block:callout]\n{\n  \"type\": \"info\",\n  \"title\": \"Recommended\",\n  \"body\": \"Building queries using table functions is highly recommended.\"\n}\n[/block]\nThe output would be the following;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"UPDATE user\\nSET email = $1\\nWHERE user.id = $2;\\n[al@pacino.com 3af82cdc-4d21-473b-a175-cbc3f9119eda]\",\n      \"language\": \"sql\"\n    }\n  ]\n}\n[/block]\nAs you might notice in this example, there exists a where clause using table's C() function. This functions is the column selector function of table struct. \n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Upsert\"\n}\n[/block]\nThe upsert statement generates dialect specific sql statement for each driver. The following example shows a simple Upsert statement in postgres.\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"ups := users.\\n\\t\\tUpsert().\\n\\t\\tValues(map[string]interface{}{\\n\\t\\t\\t\\\"id\\\":    \\\"3af82cdc-4d21-473b-a175-cbc3f9119eda\\\",\\n\\t\\t\\t\\\"email\\\": \\\"al@pacino.com\\\",\\n\\t\\t}).\\n\\t\\tBuild(db.Dialect())\\n\\nfmt.Println(ups.SQL())\\nfmt.Println(ups.Bindings())\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\nThe output would be following in postgres driver;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"INSERT INTO user(id, email)\\nVALUES($1, $2)\\nON CONFLICT (id) DO UPDATE SET id = $3, email = $4;\\n[3af82cdc-4d21-473b-a175-cbc3f9119eda al@pacino.com 3af82cdc-4d21-473b-a175-cbc3f9119eda al@pacino.com]\",\n      \"language\": \"sql\"\n    }\n  ]\n}\n[/block]\n\n[block:callout]\n{\n  \"type\": \"danger\",\n  \"title\": \"The upsert statement requires to have primary key field for postgres.\",\n  \"body\": \"If no primary key is defined for the table, it'll produce meaningless results.\"\n}\n[/block]\nIn sqlite, the example above would output the following;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"REPLACE INTO user(id, email)\\nVALUES(?, ?);\\n[3af82cdc-4d21-473b-a175-cbc3f9119eda al@pacino.com]\",\n      \"language\": \"sql\"\n    }\n  ]\n}\n[/block]\nLastly in mysql, the output would be the following;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"INSERT INTO user(id, email)\\nVALUES(?, ?)\\nON DUPLICATE KEY UPDATE id = ?, email = ?;\\n[3af82cdc-4d21-473b-a175-cbc3f9119eda al@pacino.com 3af82cdc-4d21-473b-a175-cbc3f9119eda al@pacino.com]\",\n      \"language\": \"sql\"\n    }\n  ]\n}\n[/block]\n\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Delete\"\n}\n[/block]\nLastly delete statements can be built by the following;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"del := users.\\n\\t\\tDelete().\\n\\t\\tWhere(users.C(\\\"id\\\").Eq(\\\"3af82cdc-4d21-473b-a175-cbc3f9119eda\\\")).\\n\\t\\tBuild(db.Dialect())\\n\\nfmt.Println(del.SQL())\\nfmt.Println(del.Bindings())\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\nThe output would be the following;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"DELETE FROM user\\nWHERE (user.id = ?);\\n[3af82cdc-4d21-473b-a175-cbc3f9119eda]\",\n      \"language\": \"sql\"\n    }\n  ]\n}\n[/block]","excerpt":"","slug":"defining-tables","type":"basic","title":"Inserts, Updates, Upserts & Deletes"}

Inserts, Updates, Upserts & Deletes


After 0.1 release, the builder is decentralized into separate query builders such as Insert, Update, Upsert, Delete and Select. This change allows more strict restrictions to build queries. It also prevents errors while building queries. Suppose you have the following table defined; [block:code] { "codes": [ { "code": "\tusers := qb.Table(\"user\",\n\t\tqb.Column(\"id\", qb.Varchar().Size(36)),\n\t\tqb.Column(\"email\", qb.Varchar().Unique().NotNull()),\n\t\tqb.PrimaryKey(\"id\"),\n\t)", "language": "go" } ] } [/block] [block:api-header] { "type": "basic", "title": "Insert" } [/block] There are 2 ways to build insert queries in expression api; [block:code] { "codes": [ { "code": "ins := qb.Insert(users).Values(map[string]interface{}{\n\t\t\"id\": \"3af82cdc-4d21-473b-a175-cbc3f9119eda\",\n\t\t\"email\": \"al@pacino.com\",\n\t}).Build(db.Dialect())\n\nfmt.Println(ins.SQL())\nfmt.Println(ins.Bindings())", "language": "go" } ] } [/block] In this instance, the following output would be printed; [block:code] { "codes": [ { "code": "INSERT INTO user(id, email)\nVALUES($1, $2);\n[3af82cdc-4d21-473b-a175-cbc3f9119eda al@pacino.com]", "language": "sql" } ] } [/block] Another option would be to generate the insert statement using table object; [block:code] { "codes": [ { "code": "ins := users.Insert().Values(map[string]interface{}{\n\t\t\"id\": \"3af82cdc-4d21-473b-a175-cbc3f9119eda\",\n\t\t\"email\": \"al@pacino.com\",\n\t}).Build(db.Dialect())\n\nfmt.Println(ins.SQL())\nfmt.Println(ins.Bindings())", "language": "go" } ] } [/block] [block:callout] { "type": "warning", "title": "Not recommended", "body": "Although it is possible to build insert statements using qb.Insert() function, it is not recommended to use because the table based users.Insert().Values() clause is much simpler." } [/block] The output would be the same as the previous example above. [block:api-header] { "type": "basic", "title": "Update" } [/block] The update syntax is very similar to insert. [block:code] { "codes": [ { "code": "upd := users.\n\t\tUpdate().\n\t\tValues(map[string]interface{}{\n\t\t\t\"email\": \"al@pacino.com\",\n\t\t}).\n\t\tWhere(users.C(\"id\").Eq(\"3af82cdc-4d21-473b-a175-cbc3f9119eda\")).\n\t\tBuild(db.Dialect())\n\nfmt.Println(upd.SQL())\nfmt.Println(upd.Bindings())", "language": "go" } ] } [/block] [block:callout] { "type": "info", "title": "Recommended", "body": "Building queries using table functions is highly recommended." } [/block] The output would be the following; [block:code] { "codes": [ { "code": "UPDATE user\nSET email = $1\nWHERE user.id = $2;\n[al@pacino.com 3af82cdc-4d21-473b-a175-cbc3f9119eda]", "language": "sql" } ] } [/block] As you might notice in this example, there exists a where clause using table's C() function. This functions is the column selector function of table struct. [block:api-header] { "type": "basic", "title": "Upsert" } [/block] The upsert statement generates dialect specific sql statement for each driver. The following example shows a simple Upsert statement in postgres. [block:code] { "codes": [ { "code": "ups := users.\n\t\tUpsert().\n\t\tValues(map[string]interface{}{\n\t\t\t\"id\": \"3af82cdc-4d21-473b-a175-cbc3f9119eda\",\n\t\t\t\"email\": \"al@pacino.com\",\n\t\t}).\n\t\tBuild(db.Dialect())\n\nfmt.Println(ups.SQL())\nfmt.Println(ups.Bindings())", "language": "go" } ] } [/block] The output would be following in postgres driver; [block:code] { "codes": [ { "code": "INSERT INTO user(id, email)\nVALUES($1, $2)\nON CONFLICT (id) DO UPDATE SET id = $3, email = $4;\n[3af82cdc-4d21-473b-a175-cbc3f9119eda al@pacino.com 3af82cdc-4d21-473b-a175-cbc3f9119eda al@pacino.com]", "language": "sql" } ] } [/block] [block:callout] { "type": "danger", "title": "The upsert statement requires to have primary key field for postgres.", "body": "If no primary key is defined for the table, it'll produce meaningless results." } [/block] In sqlite, the example above would output the following; [block:code] { "codes": [ { "code": "REPLACE INTO user(id, email)\nVALUES(?, ?);\n[3af82cdc-4d21-473b-a175-cbc3f9119eda al@pacino.com]", "language": "sql" } ] } [/block] Lastly in mysql, the output would be the following; [block:code] { "codes": [ { "code": "INSERT INTO user(id, email)\nVALUES(?, ?)\nON DUPLICATE KEY UPDATE id = ?, email = ?;\n[3af82cdc-4d21-473b-a175-cbc3f9119eda al@pacino.com 3af82cdc-4d21-473b-a175-cbc3f9119eda al@pacino.com]", "language": "sql" } ] } [/block] [block:api-header] { "type": "basic", "title": "Delete" } [/block] Lastly delete statements can be built by the following; [block:code] { "codes": [ { "code": "del := users.\n\t\tDelete().\n\t\tWhere(users.C(\"id\").Eq(\"3af82cdc-4d21-473b-a175-cbc3f9119eda\")).\n\t\tBuild(db.Dialect())\n\nfmt.Println(del.SQL())\nfmt.Println(del.Bindings())", "language": "go" } ] } [/block] The output would be the following; [block:code] { "codes": [ { "code": "DELETE FROM user\nWHERE (user.id = ?);\n[3af82cdc-4d21-473b-a175-cbc3f9119eda]", "language": "sql" } ] } [/block]