Defining Tables
In the 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;
type User struct {
ID string `db:"_id" qb:"type:varchar(36); constraints:primary_key"`
Email string `db:"_email" qb:"type:varchar(64); constraints: unique, not_null, "`
}
As you might notice, the tags are long enough to keep track of it. Here's the equivalent table using the expression api;
usersTable := qb.Table(
"user",
qb.Column("_id", qb.Varchar().Size(40)),
qb.Column("_email", qb.Varchar().Size(64).Unique().NotNull()),
qb.PrimaryKey("_id"),
)
fmt.Println(usersTable.Create(db.Builder().Adapter()))
// prints
/*
CREATE TABLE user (
_id VARCHAR(40),
_email VARCHAR(64) UNIQUE NOT NULL,
PRIMARY KEY(_id)
);
*/
This method is more performant than the auto mapping method. Performance critical applications should use table api instead of orm.
Defining Column Types & Constraints
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;
func | desc |
---|---|
Char() | generates a char type |
Varchar() | generates a varchar type with 255 as default size |
Text() | generates a text type |
SmallInt() | generates a small int type |
Int() | generates an int type |
BigInt() | generates an int64 type |
Numeric() | generates a numeric type |
Float() | generates a float type |
Boolean() | generates a boolean type |
Timestamp() | generates a timestamp type |
You can optionally define your own type using Type(name string)
function as in the following;
col := qb.Column("id", qb.Type("UUID"))
After defining types, the constraints of that column can be defined using chaining as in the following exampe;
col := qb.Column("email", qb.Varchar().Size(40).NotNull().Unique())
This means that we have an email
column with VARCHAR(40) NOT NULL UNIQUE
types & constraints.
Here is the builtin constraints you can use;
func | Desc |
---|---|
Size(size int) | Adds a size constraint given size |
Default(val interface{}) | Adds a default constraint given value |
Null() | Adds a nullable constraint |
NotNull() | Adds a non nullable constraint |
Unique() | Adds a unique constraint |
You can optionally define your custom constraint using Constraint(name string)
function as in the following;
col := qb.Column("id", qb.Int().Constraint("CHECK (id > 0)"))
This example adds email column a check constraint
Defining Table constraints
The table constraints (primary, foreign keys, unique keys) can be defined using builtin table constraints.
usersTable := qb.Table(
"user",
qb.Column("id", qb.Varchar().Size(36)),
qb.Column("address_id", qb.Text()),
qb.PrimaryKey("id"),
qb.ForeignKey().Ref("address_id", "address", "id"),
)
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.
usersTable := qb.Table(
"user",
qb.Column("id", qb.Varchar().Size(36)),
qb.Column("address_id", qb.Text()),
qb.UniqueKey("id", "address_id"),
)
Defining table indices
Table indices can be defined by chaining Table()
function of qb.
usersTable := qb.Table(
"user",
qb.Column("id", qb.Varchar().Size(36)),
qb.Column("address_id", qb.Text()),
).Index("id").Index("id", "address_id")
As you might notice, Index()
function can be used single indices as well as composite indices.
Updated less than a minute ago