{"_id":"575316148712340e0061bf56","user":"543466ea0e8e2b0e00341818","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"},"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":21,"updates":[],"next":{"pages":[],"description":""},"createdAt":"2016-06-04T17:55:32.137Z","link_external":false,"link_url":"","githubsync":"","sync_unique":"","hidden":false,"api":{"results":{"codes":[]},"settings":"","auth":"required","params":[],"url":""},"isReference":false,"order":0,"body":"In the [Defining Models](doc:defining-models) models] section, it is explained how to build tables from structs. However, there is a secondary option to create tables without using any structs. This method is a lightweight solution to build tables that can be used in manipulating data. This method also doesn't use any reflection.\n\nThe reason to have this in the expression api is simply because you may need extra flexibility to build table objects that can work with databases. Moreover, defining tables using this method would be more idiomatic to go. Here's an example user struct using orm api;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"type User struct {\\n  ID string `db:\\\"_id\\\" qb:\\\"type:varchar(36); constraints:primary_key\\\"`\\n  Email string `db:\\\"_email\\\" qb:\\\"type:varchar(64); constraints: unique, not_null, \\\"`\\n}\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\nAs you might notice, the tags are long enough to keep track of it. Here's the equivalent table using the expression api;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"usersTable := qb.Table(\\n  \\\"user\\\",\\n  qb.Column(\\\"_id\\\", qb.Varchar().Size(40)),\\n  qb.Column(\\\"_email\\\", qb.Varchar().Size(64).Unique().NotNull()),\\n  qb.PrimaryKey(\\\"_id\\\"),\\n)\\n\\nfmt.Println(usersTable.Create(db.Builder().Adapter()))\\n\\n// prints\\n/*\\nCREATE TABLE user (\\n\\t_id VARCHAR(40),\\n\\t_email VARCHAR(64) UNIQUE NOT NULL,\\n\\tPRIMARY KEY(_id)\\n);\\n*/\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\nThis method is more performant than the auto mapping method. Performance critical applications should use table api instead of orm.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Defining Column Types & Constraints\"\n}\n[/block]\nThe `Column(name string, t TypeElem)` function generates a column and appends a column to the table. The following builtin types are available for columns;\n[block:parameters]\n{\n  \"data\": {\n    \"0-0\": \"Char()\",\n    \"h-0\": \"func\",\n    \"0-1\": \"generates a char type\",\n    \"1-0\": \"Varchar()\",\n    \"1-1\": \"generates a varchar type with 255 as default size\",\n    \"2-0\": \"Text()\",\n    \"2-1\": \"generates a text type\",\n    \"3-0\": \"SmallInt()\",\n    \"3-1\": \"generates a small int type\",\n    \"4-0\": \"Int()\",\n    \"4-1\": \"generates an int type\",\n    \"6-0\": \"Numeric()\",\n    \"6-1\": \"generates a numeric type\",\n    \"7-0\": \"Float()\",\n    \"7-1\": \"generates a float type\",\n    \"5-0\": \"BigInt()\",\n    \"5-1\": \"generates an int64 type\",\n    \"8-0\": \"Boolean()\",\n    \"8-1\": \"generates a boolean type\",\n    \"9-0\": \"Timestamp()\",\n    \"9-1\": \"generates a timestamp type\",\n    \"h-1\": \"desc\"\n  },\n  \"cols\": 2,\n  \"rows\": 10\n}\n[/block]\nYou can optionally define your own type using `Type(name string)` function as in the following;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"col := qb.Column(\\\"id\\\", qb.Type(\\\"UUID\\\"))\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\nAfter defining types, the constraints of that column can be defined using chaining as in the following exampe;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"col := qb.Column(\\\"email\\\", qb.Varchar().Size(40).NotNull().Unique())\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\nThis means that we have an `email` column with `VARCHAR(40) NOT NULL UNIQUE` types & constraints.\n\nHere is the builtin constraints you can use;\n[block:parameters]\n{\n  \"data\": {\n    \"h-0\": \"func\",\n    \"h-1\": \"Desc\",\n    \"0-0\": \"Size(size int)\",\n    \"0-1\": \"Adds a size constraint given size\",\n    \"1-0\": \"Default(val interface{})\",\n    \"1-1\": \"Adds a default constraint given value\",\n    \"2-0\": \"Null()\",\n    \"2-1\": \"Adds a nullable constraint\",\n    \"3-0\": \"NotNull()\",\n    \"3-1\": \"Adds a non nullable constraint\",\n    \"4-0\": \"Unique()\",\n    \"4-1\": \"Adds a unique constraint\"\n  },\n  \"cols\": 2,\n  \"rows\": 5\n}\n[/block]\nYou can optionally define your custom constraint using `Constraint(name string)` function as in the following;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"col := qb.Column(\\\"id\\\", qb.Int().Constraint(\\\"CHECK (id > 0)\\\"))\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\nThis example adds email column a check constraint\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Defining Table constraints\"\n}\n[/block]\nThe table constraints (primary, foreign keys, unique keys) can be defined using builtin table constraints.\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"usersTable := qb.Table(\\n  \\\"user\\\",\\n  qb.Column(\\\"id\\\", qb.Varchar().Size(36)),\\n  qb.Column(\\\"address_id\\\", qb.Text()),\\n  qb.PrimaryKey(\\\"id\\\"),\\n  qb.ForeignKey().Ref(\\\"address_id\\\", \\\"address\\\", \\\"id\\\"),\\n)\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\nThis will tell qb to add a primary key table constraint on `id` column and a foreign key constraint on `address_id` referenced from `address.id`.\n\nYou can also add composite unique keys using `UniqueKey(cols ...string)` function. Note that it should be used `Unique()` in defining column type if a single unique constraint is required.\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"usersTable := qb.Table(\\n  \\\"user\\\",\\n  qb.Column(\\\"id\\\", qb.Varchar().Size(36)),\\n  qb.Column(\\\"address_id\\\", qb.Text()),\\n  qb.UniqueKey(\\\"id\\\", \\\"address_id\\\"),\\n)\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\n\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Defining table indices\"\n}\n[/block]\nTable indices can be defined by chaining `Table()` function of qb.\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"usersTable := qb.Table(\\n  \\\"user\\\",\\n  qb.Column(\\\"id\\\", qb.Varchar().Size(36)),\\n  qb.Column(\\\"address_id\\\", qb.Text()),\\n).Index(\\\"id\\\").Index(\\\"id\\\", \\\"address_id\\\")\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\nAs you might notice, `Index()` function can be used single indices as well as composite indices.","excerpt":"","slug":"the-expression-api","type":"basic","title":"Defining Tables"}
In the [Defining Models](doc:defining-models) models] section, it is explained how to build tables from structs. However, there is a secondary option to create tables without using any structs. This method is a lightweight solution to build tables that can be used in manipulating data. This method also doesn't use any reflection. The reason to have this in the expression api is simply because you may need extra flexibility to build table objects that can work with databases. Moreover, defining tables using this method would be more idiomatic to go. Here's an example user struct using orm api; [block:code] { "codes": [ { "code": "type User struct {\n ID string `db:\"_id\" qb:\"type:varchar(36); constraints:primary_key\"`\n Email string `db:\"_email\" qb:\"type:varchar(64); constraints: unique, not_null, \"`\n}", "language": "go" } ] } [/block] As you might notice, the tags are long enough to keep track of it. Here's the equivalent table using the expression api; [block:code] { "codes": [ { "code": "usersTable := qb.Table(\n \"user\",\n qb.Column(\"_id\", qb.Varchar().Size(40)),\n qb.Column(\"_email\", qb.Varchar().Size(64).Unique().NotNull()),\n qb.PrimaryKey(\"_id\"),\n)\n\nfmt.Println(usersTable.Create(db.Builder().Adapter()))\n\n// prints\n/*\nCREATE TABLE user (\n\t_id VARCHAR(40),\n\t_email VARCHAR(64) UNIQUE NOT NULL,\n\tPRIMARY KEY(_id)\n);\n*/", "language": "go" } ] } [/block] This method is more performant than the auto mapping method. Performance critical applications should use table api instead of orm. [block:api-header] { "type": "basic", "title": "Defining Column Types & Constraints" } [/block] The `Column(name string, t TypeElem)` function generates a column and appends a column to the table. The following builtin types are available for columns; [block:parameters] { "data": { "0-0": "Char()", "h-0": "func", "0-1": "generates a char type", "1-0": "Varchar()", "1-1": "generates a varchar type with 255 as default size", "2-0": "Text()", "2-1": "generates a text type", "3-0": "SmallInt()", "3-1": "generates a small int type", "4-0": "Int()", "4-1": "generates an int type", "6-0": "Numeric()", "6-1": "generates a numeric type", "7-0": "Float()", "7-1": "generates a float type", "5-0": "BigInt()", "5-1": "generates an int64 type", "8-0": "Boolean()", "8-1": "generates a boolean type", "9-0": "Timestamp()", "9-1": "generates a timestamp type", "h-1": "desc" }, "cols": 2, "rows": 10 } [/block] You can optionally define your own type using `Type(name string)` function as in the following; [block:code] { "codes": [ { "code": "col := qb.Column(\"id\", qb.Type(\"UUID\"))", "language": "go" } ] } [/block] After defining types, the constraints of that column can be defined using chaining as in the following exampe; [block:code] { "codes": [ { "code": "col := qb.Column(\"email\", qb.Varchar().Size(40).NotNull().Unique())", "language": "go" } ] } [/block] This means that we have an `email` column with `VARCHAR(40) NOT NULL UNIQUE` types & constraints. Here is the builtin constraints you can use; [block:parameters] { "data": { "h-0": "func", "h-1": "Desc", "0-0": "Size(size int)", "0-1": "Adds a size constraint given size", "1-0": "Default(val interface{})", "1-1": "Adds a default constraint given value", "2-0": "Null()", "2-1": "Adds a nullable constraint", "3-0": "NotNull()", "3-1": "Adds a non nullable constraint", "4-0": "Unique()", "4-1": "Adds a unique constraint" }, "cols": 2, "rows": 5 } [/block] You can optionally define your custom constraint using `Constraint(name string)` function as in the following; [block:code] { "codes": [ { "code": "col := qb.Column(\"id\", qb.Int().Constraint(\"CHECK (id > 0)\"))", "language": "go" } ] } [/block] This example adds email column a check constraint [block:api-header] { "type": "basic", "title": "Defining Table constraints" } [/block] The table constraints (primary, foreign keys, unique keys) can be defined using builtin table constraints. [block:code] { "codes": [ { "code": "usersTable := qb.Table(\n \"user\",\n qb.Column(\"id\", qb.Varchar().Size(36)),\n qb.Column(\"address_id\", qb.Text()),\n qb.PrimaryKey(\"id\"),\n qb.ForeignKey().Ref(\"address_id\", \"address\", \"id\"),\n)", "language": "go" } ] } [/block] This will tell qb to add a primary key table constraint on `id` column and a foreign key constraint on `address_id` referenced from `address.id`. You can also add composite unique keys using `UniqueKey(cols ...string)` function. Note that it should be used `Unique()` in defining column type if a single unique constraint is required. [block:code] { "codes": [ { "code": "usersTable := qb.Table(\n \"user\",\n qb.Column(\"id\", qb.Varchar().Size(36)),\n qb.Column(\"address_id\", qb.Text()),\n qb.UniqueKey(\"id\", \"address_id\"),\n)", "language": "go" } ] } [/block] [block:api-header] { "type": "basic", "title": "Defining table indices" } [/block] Table indices can be defined by chaining `Table()` function of qb. [block:code] { "codes": [ { "code": "usersTable := qb.Table(\n \"user\",\n qb.Column(\"id\", qb.Varchar().Size(36)),\n qb.Column(\"address_id\", qb.Text()),\n).Index(\"id\").Index(\"id\", \"address_id\")", "language": "go" } ] } [/block] As you might notice, `Index()` function can be used single indices as well as composite indices.