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;

Go

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;

Go

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;

Go

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;

Go

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;

Go

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.

Go

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.

Go

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.

Go

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.