Discussions

Defining Models

Suggest Edits

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

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;

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;

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; 

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;

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;

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;

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

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

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;

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;

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;

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 typemysqlpostgressqlite
stringVARCHAR(255)VARCHAR(255)VARCHAR(255)
intINTINTINT
int8SMALLINTSMALLINTSMALLINT
int16SMALLINTSMALLINTSMALLINT
int32INTINTINT
int64BIGINTBIGINTBIGINT
uintINT UNSIGNEDBIGINTBIGINT
uint8TINYINT UNSIGNEDSMALLINTSMALLINT
uint16SMALLINT UNSIGNEDINTINT
uint32INT UNSIGNEDBIGINTBIGINT
uint64BIGINT UNSIGNEDBIGINTBIGINT
float32FLOATFLOATFLOAT
float64FLOATFLOATFLOAT
boolBOOLEANBOOLEANBOOLEAN
time.Time or *time.TimeTIMESTAMPTIMESTAMPTIMESTAMP
other typesVARCHARVARCHARVARCHAR

❗️

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;

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. 

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;

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;

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.

Updated less than a minute ago