Defining Models

To define models, qb uses structs with tagging options. This tutorial will continue without defining qb again and again. Therefore it is assumed here that qb is initialized by the following code;

📘

Examples

Although the library works in mysql and sqlite, the following examples would be in postgres

Go

import (
	"github.com/aacanakin/qb" 
)

db, err := qb.New("postgres", "user=postgres dbname=qb_test sslmode=disable")
if err != nil {
  panic(err)
}
defer db.Close()

The simplest model definition would be the following;

Go

type User struct {
  ID int64
  Name string
}

// register struct to metadata to be mapped
db.Metadata().Add(User{})

// create all tables registered to metadata within a transaction
err := db.Metadata().CreateAll()

// OR db has wrapper functions for metadata

db.AddTable(User{})

err := db.CreateAll()

The code will generate a transaction and creates the following sql statement;

SQL

CREATE TABLE user(
	id BIGINT,
	name VARCHAR(255)
);

It is noticeable here that the most simple table would not solve any of our real world problems. Therefore, let's add some constraints.

Ignoring struct fields

It may be required to have ignoring for a pre or post processed field in a struct that won't be in a database field. Use - character to ignore a struct field as in the following;

Go

type User struct {
		ID        int64      `qb:"constraints:primary_key"`
		Sessions  []Session  `qb:"-"`
}

In this struct definition, the Sessions field would not be added in the database.

Overriding colum names

You can also override column names using db tag. The following example shows how to override column names in the db tables;

Go

type User struct {
  ID int64 `db:"_id" qb:"constraints:primary_key"` 
}

This will tell qb to map ID field into _id in the db table.

Constraints

In qb there are several constraint definitions. The most common is to use struct tags. Here's the following;

Go

type User struct {
	ID   int64 `qb:"constraints:primary_key"`
  Name string
}

// register struct to metadata to be mapped
db.Metadata().Add(User{})

// create all tables registered to metadata within a transaction
db.Metadata().CreateAll()

This would generate the following sql statement in a transaction;

SQL

CREATE TABLE user(
	id BIGINT PRIMARY KEY,
	name VARCHAR(255)
);

Let's improve our model and use not null & unique constraints

Go

type User struct {
		ID        int64      `qb:"constraints:primary_key"`
		Name      string     `qb:"constraints:not_null"`
		Email     string     `qb:"constraints:unique, not_null"`
		CreatedAt time.Time  `qb:"constraints:not_null"`
		DeletedAt *time.Time `qb:"constraints:null"`
}

db.Metadata().Add(User{})

Notice here that DeletedAt field is a time.Time pointer instead of time.Time. It is because to make DeletedAt field nullable. You can also have pointer types with not null constraints.

This would generate the following sql statement in a transaction;

SQL

CREATE TABLE user(
	id BIGINT PRIMARY KEY,
	name VARCHAR(255) NOT NULL,
	email VARCHAR(255) UNIQUE NOT NULL,
	created_at TIMESTAMP NOT NULL,
	deleted_at TIMESTAMP NULL
);

Foreign Keys

Foreign keys lets you define relationships between tables. In qb, foreign keys are done in the following example;

Go

type User struct {
		ID        int64      `qb:"constraints:primary_key"`
		Name      string     `qb:"constraints:not_null"`
		Email     string     `qb:"constraints:unique, not_null"`
		CreatedAt time.Time  `qb:"constraints:not_null"`
		DeletedAt *time.Time `qb:"constraints:null"`
	}

type Session struct {
		ID        string `qb:"type:uuid; constraints:primary_key"`
		UserID    int64  `qb:"constraints:ref(user.id)"`
		AuthToken string `qb:"type:uuid", constraints:not_null`
}

db.Metadata().Add(User{})
db.Metadata().Add(Session{})
db.Metadata().CreateAll()

As you might notice, qb supports definitions of multiple constraints like in the User.Email field.

This would generate the following sql statements within a transaction;

SQL

CREATE TABLE user(
	id BIGINT PRIMARY KEY,
	name VARCHAR(255) NOT NULL,
	email VARCHAR(255) UNIQUE NOT NULL,
	created_at TIMESTAMP NOT NULL,
	deleted_at TIMESTAMP NULL
);

CREATE TABLE session(
	id UUID PRIMARY KEY,
	user_id BIGINT,
	auth_token UUID,
	FOREIGN KEY (user_id) REFERENCES user(id)
);

As it might be noticed, it is really really easy to build simple relationships between tables.

Types

The following type mappings are applied in qb types;

go type	mysql	postgres	sqlite
string	VARCHAR(255)	VARCHAR(255)	VARCHAR(255)
int	INT	INT	INT
int8	SMALLINT	SMALLINT	SMALLINT
int16	SMALLINT	SMALLINT	SMALLINT
int32	INT	INT	INT
int64	BIGINT	BIGINT	BIGINT
uint	INT UNSIGNED	BIGINT	BIGINT
uint8	TINYINT UNSIGNED	SMALLINT	SMALLINT
uint16	SMALLINT UNSIGNED	INT	INT
uint32	INT UNSIGNED	BIGINT	BIGINT
uint64	BIGINT UNSIGNED	BIGINT	BIGINT
float32	FLOAT	FLOAT	FLOAT
float64	FLOAT	FLOAT	FLOAT
bool	BOOLEAN	BOOLEAN	BOOLEAN
time.Time or *time.Time	TIMESTAMP	TIMESTAMP	TIMESTAMP
other types	VARCHAR	VARCHAR	VARCHAR

❗️

Need feedbacks in float mappings

It is currently not clear to map floating point types. Feedbacks and contributions are welcome!

Enforcing types

Type enforcements can be achieved using the type tag as in the following example;

Go

type Session struct {
		ID string `qb:"type:uuid; constraints:primary_key"`
}

As you can see although the type defined is string, type tag enforces the type to be uuid.

Enforcing types is a great feature when you need to define database specific types such as uuid, datetime, decimal, etc.

SQL

CREATE TABLE session(
	id UUID PRIMARY KEY
);

Indexing

Indexing is also supported in qb. The following example shows how to do indexing with using struct tag index;

Go

type Session struct {
		ID                string `qb:"type:uuid; constraints:primary_key"`
		UserID            int64  `qb:"constraints:ref(user.id)"`
		AuthToken         string `qb:"type:uuid", constraints:not_null; index`
		qb.CompositeIndex `qb:"index:id, user_id"`
}

This definition would create the following sql statements within a transaction;

SQL

CREATE TABLE session(
	id UUID PRIMARY KEY,
	user_id BIGINT,
	auth_token UUID,
	FOREIGN KEY (user_id) REFERENCES user(id)
);

CREATE INDEX index_user_id ON session (user_id);
CREATE INDEX index_id_user_id ON session (id, user_id);

As it might be noticed qb supports single indices as well as composite indices. This could be useful when there are selective queries with both querying a single column and multiple columns.