Awesome

go-queryset

100% type-safe ORM for Go (Golang) with code generation and MySQL, PostgreSQL, Sqlite3, SQL Server support. GORM under the hood.

Contents

• Installation
• Usage
  • Define models
  • Relation with GORM
  • Create models
  • Select models
  • Update models
  • Delete models
  • Full list of generated methods
• Golang version
• Why
  • Why not just use GORM?
  • Why not another ORM?
  • Why not any ORM?
  • Why not raw SQL queries?
  • Why not go-kallax?
• How it relates to another languages ORMs
• Features
• Limitations
• Performance

Installation

go get -u github.com/jirfag/go-queryset/cmd/goqueryset

Usage

Define models

Imagine you have model User in your models.go file:

type User struct {
	gorm.Model
	Rating      int
	RatingMarks int
}

Now transform it by adding comments for query set generation:

//go:generate goqueryset -in models.go

// User struct represent user model. Next line (gen:qs) is needed to autogenerate UserQuerySet.
// gen:qs
type User struct {
	gorm.Model
	Rating      int
	RatingMarks int
}

Take a look at line // gen:qs. It's a necessary line to enable querysets for this struct. You can put it at any line in struct's doc-comment.

Then execute next shell command:

go generate ./...

And you will get file autogenerated_models.go in the same directory (and package) as models.go.

In this autogenerated file you will find a lot of autogenerated typesafe methods like these:

func (qs UserQuerySet) CreatedAtGte(createdAt time.Time) UserQuerySet {
	return qs.w(qs.db.Where("created_at >= ?", createdAt))
}

func (qs UserQuerySet) RatingGt(rating int) UserQuerySet {
	return qs.w(qs.db.Where("rating > ?", rating))
}

func (qs UserQuerySet) IDEq(ID uint) UserQuerySet {
	return qs.w(qs.db.Where("id = ?", ID))
}

func (qs UserQuerySet) DeletedAtIsNull() UserQuerySet {
	return qs.w(qs.db.Where("deleted_at IS NULL"))
}

func (o *User) Delete(db *gorm.DB) error {
	return db.Delete(o).Error
}

func (qs UserQuerySet) OrderAscByCreatedAt() UserQuerySet {
	return qs.w(qs.db.Order("created_at ASC"))
}

See full autogenerated file here.

Now you can use this queryset for creating/reading/updating/deleting. Let's take a look at these operations.

Relation with GORM

You can embed and not embed gorm.Model into your model (e.g. if you don't need DeletedAt field), but you must use *gorm.DB to properly work. Don't worry if you don't use GORM yet, it's easy to create *gorm.DB:

import (
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/mysql"
)

func getGormDB() *gorm.DB {
	db, err := gorm.Open("mysql", "user:password@/dbname?charset=utf8&parseTime=True&loc=Local")
	// ...
}

If you already use another ORM or raw sql.DB, you can reuse your sql.DB object (to reuse connections pool):

	var sqlDB *sql.DB = getSQLDBFromAnotherORM()
	var gormDB *gorm.DB
	gormDB, err = gorm.Open("mysql", sqlDB)

Create

u := User{
	Rating: 5,
	RatingMarks: 0,
}
err := u.Create(getGormDB())

Under the hood Create method just calls db.Create(&u).

Select

It's the most powerful feature of query set. Let's execute some queries:

Select all users

var users []User
err := NewUserQuerySet(getGormDB()).All(&users)
if err == gorm.ErrRecordNotFound {
	// no records were found
}

It generates this SQL request for MySQL:

SELECT * FROM `users` WHERE `users`.deleted_at IS NULL

deleted_at filtering is added by GORM (soft-delete), to disable it use Unscoped.

Select one user

var user User
err := NewUserQuerySet(getGormDB()).One(&user)

Select N users with highest rating

var users []User
err := NewUserQuerySet(getGormDB()).
	RatingMarksGte(minMarks).
	OrderDescByRating().
	Limit(N).
	All(&users)

Select users registered today

In this example we will define custom method on generated UserQuerySet for later reuse in multiple functions:

func (qs UserQuerySet) RegisteredToday() UserQuerySet {
	// autogenerated typesafe method CreatedAtGte(time.Time)
	return qs.CreatedAtGte(getTodayBegin())
}

...
var users []User
err := NewUserQuerySet(getGormDB()).
	RegisteredToday().
	OrderDescByCreatedAt().
	Limit(N).
	All(&users)

Select specific fields

By default all fields are fetched using the * field selector. using the select methd it is possible to limit the SQL statement to fetch specific fields:

var users []User
err := NewUserQuerySet(getGormDB()).Select(UserDBSchema.ID, UserDBSchema.Rating).All(&users)
if err == gorm.ErrRecordNotFound {
	// no records were found
}

It generates this SQL request for MySQL:

SELECT id, rating FROM `users` WHERE `users`.deleted_at IS NULL

Update

Update one record by primary key

u := User{
	Model: gorm.Model{
		ID: uint(7),
	},
	Rating: 1,
}
err := u.Update(getGormDB(), UserDBSchema.Rating)

Goqueryset generates DB names for struct fields into UserDBSchema variable. In this example we used UserDBSchema.Rating.

And this code generates next SQL:

UPDATE `users` SET `rating` = ? WHERE `users`.deleted_at IS NULL AND `users`.`id` = ?

Update multiple record or without model object

Sometimes we don't have model object or we are updating multiple rows in DB. For these cases there is another typesafe interface:

err := NewUserQuerySet(getGormDB()).
	RatingLt(1).
	GetUpdater().
	SetRatingMarks(0).
	Update()

UPDATE `users` SET `rating_marks` = ? WHERE `users`.deleted_at IS NULL AND ((rating < ?))

UpdateNum

This method makes the same sql queries as Update() method, except return values: it returns number of affected rows and error

num, err := NewUserQuerySet(getGormDB()).
	RatingLt(1).
	GetUpdater().
	SetRatingMarks(0).
	UpdateNum()

UPDATE `users` SET `rating_marks` = ? WHERE `users`.deleted_at IS NULL AND ((rating < ?))

Delete

Delete one record by primary key

u := User{
	Model: gorm.Model{
		ID: uint(7),
	},
}
err := u.Delete(getGormDB())

Delete multiple records

err := NewUserQuerySet(getGormDB()).
	RatingMarksEq(0).
	Delete()

Full list of generated methods

QuerySet methods - func (qs {StructName}QuerySet)

• create new queryset: New{StructName}QuerySet(db *gorm.DB)

func NewUserQuerySet(db *gorm.DB) UserQuerySet

• filter by field (where)

  • all field types
    • Equals: {FieldName}(Eq|Ne)(arg {FieldType})

    func (qs UserQuerySet) RatingEq(rating int) UserQuerySet
    

    • In: {FieldName}(Not)In(arg {FieldType}, argsRest ...{FieldType})

    func (qs UserQuerySet) NameIn(name string, nameRest ...string) UserQuerySet {}
    func (qs UserQuerySet) NameNotIn(name string, nameRest ...string) UserQuerySet {}
    

    • Order(Asc|Desc)By{FieldName}()

    func (qs UserQuerySet) OrderDescByRating() UserQuerySet
    

  • numeric types (int, int64, uint etc + time.Time):
    • {FieldName}(Lt|Lte|Gt|Gte)(arg {FieldType)

    func (qs UserQuerySet) RatingGt(rating int) UserQuerySet
    

  • string types (string):
    • {FieldName}(Like/Notlike)(arg {FieldType)

    func (qs UserQuerySet) NameLike(name string) UserQuerySet
    

  • pointer fields: {FieldName}IsNull(), {FieldName}IsNotNull()

  func (qs UserQuerySet) ProfileIsNull() UserQuerySet {}
  func (qs UserQuerySet) ProfileIsNotNull() UserQuerySet {}
  

• preload related object (for structs fields or pointers to structs fields): Preload{FieldName}() For struct

  	type User struct {
  		profile *Profile
  	}
  

  will be generated:

  func (qs UserQuerySet) PreloadProfile() UserQuerySet
  

  Preload functions call gorm.Preload to preload related object.

• selectors

  • Select all objects, return gorm.ErrRecordNotFound if no records

  func (qs UserQuerySet) All(users *[]User) error
  

  • Select one object, return gorm.ErrRecordNotFound if no records

  func (qs UserQuerySet) One(user *User) error
  

• Limit

func (qs UserQuerySet) Limit(limit int) UserQuerySet

• get updater (for update + where, based on current queryset):

func (qs UserQuerySet) GetUpdater() UserUpdater

• delete with conditions from current queryset: Delete()

func (qs UserQuerySet) Delete() error

• Aggregations
  • Count

  func (qs UserQuerySet) Count() (int, error)
  

Object methods - func (u *User)

• create object

func (o *User) Create(db *gorm.DB) error

• delete object by PK

func (o *User) Delete(db *gorm.DB) error

• update object by PK

func (o *User) Update(db *gorm.DB, fields ...userDBSchemaField) error

Pay attention that field names are automatically generated into variable

type userDBSchemaField string

// UserDBSchema stores db field names of User
var UserDBSchema = struct {
	ID          userDBSchemaField
	CreatedAt   userDBSchemaField
	UpdatedAt   userDBSchemaField
	DeletedAt   userDBSchemaField
	Rating      userDBSchemaField
	RatingMarks userDBSchemaField
}{

	ID:          userDBSchemaField("id"),
	CreatedAt:   userDBSchemaField("created_at"),
	UpdatedAt:   userDBSchemaField("updated_at"),
	DeletedAt:   userDBSchemaField("deleted_at"),
	Rating:      userDBSchemaField("rating"),
	RatingMarks: userDBSchemaField("rating_marks"),
}

And they are typed, so you won't have string-misprint error.

Updater methods - func (u UserUpdater)

• set field: Set{FieldName}

func (u UserUpdater) SetCreatedAt(createdAt time.Time) UserUpdater

• execute update: Update()

func (u UserUpdater) Update() error

Golang version

Golang >= 1.8 is required. Tested on go 1.8, 1.9 versions by Travis CI

Why?

Why not just use GORM?

I like GORM: it's the best ORM for golang, it has fantastic documentation, but as a Golang developers team lead I can point out some troubles with it:

1. GORM isn't typesafe: it's so easy to spend 1 hour trying to execute simple Update. GORM gets all arguments as interface{} and in the case of invalid GORM usage you won't get error: you will get invalid SQL, no SQL (!) and error == nil etc. It's easy to get SELECT * FROM t WHERE string_field == 1 SQL in production without type safety.
2. GORM is difficult for beginners because of unclear interface{} interfaces: one can't easily find which arguments to pass to GORM methods.

Why not another ORM?

Type-safety, like with GORM.

Why not any ORM?

I didn't see any ORM that properly handles code duplication. GORM is the best with Scopes support, but even it's far from ideal. E.g. we have GORM and next typical code:

type User struct {
	gorm.Model
	Rating      int
	RatingMarks int
}

func GetUsersWithMaxRating(limit int) ([]User, error) {
	var users []User
	if err := getGormDB().Order("rating DESC").Limit(limit).Find(&users).Error; err != nil {
		return nil, err
	}
	return users, nil
}

func GetUsersRegisteredToday(limit int) ([]User, error) {
	var users []User
	today := getTodayBegin()
	err := getGormDB().Where("created_at >= ?", today).Limit(limit).Find(&users).Error
	if err != nil {
		return nil, err
	}
	return users, nil
}

At one moment PM asks us to implement new function, returning list of users registered today AND sorted by rating. Copy-paste way is to add Order("rating DESC") to GetUsersRegisteredToday. But it leads to typical copy-paste troubles: when we change rating calculation logics (e.g. to .Where("rating_marks >= ?", 10).Order("rating DESC")) we must change it in two places.

How to solve it? First idea is to make reusable functions:

func queryUsersWithMaxRating(db *gorm.DB, limit int) *gorm.DB {
	return db.Order("rating DESC").Limit(limit)
}

func queryUsersRegisteredToday(db *gorm.DB, limit int) *gorm.DB {
	today := getTodayBegin()
	return db.Where("created_at >= ?", today).Limit(limit)
}

func GetUsersWithMaxRating(limit int) ([]User, error) {
	var users []User
	if err := queryUsersWithMaxRating(getGormDB(), limit).Find(&users).Error; err != nil {
		return nil, err
	}
	return users, nil
}

func GetUsersRegisteredToday(limit int) ([]User, error) {
	var users []User
	if err := queryUsersRegisteredToday(getGormDB(), limit).Find(&users).Error; err != nil {
		return nil, err
	}
	return users, nil
}

func GetUsersRegisteredTodayWithMaxRating(limit int) ([]User, error) {
	var users []User
	err := queryUsersWithMaxRating(queryUsersRegisteredToday(getGormDB(), limit), limit).
		Find(&users).Error
	if err != nil {
		return nil, err
	}
	return users, nil
}

We can use GORM Scopes to improve how it looks:

func queryUsersWithMaxRating(db *gorm.DB) *gorm.DB {
	return db.Order("rating DESC")
}

func queryUsersRegisteredToday(db *gorm.DB) *gorm.DB {
	return db.Where("created_at >= ?", getTodayBegin())
}

func GetUsersRegisteredTodayWithMaxRating(limit int) ([]User, error) {
	var users []User
	err := getGormDB().
		Scopes(queryUsersWithMaxRating, queryUsersRegisteredToday).
		Limit(limit).
		Find(&users).Error
	if err != nil {
		return nil, err
	}
	return users, nil
}

Looks nice, but we loosed ability to parametrize our reusable GORM queries (scopes): they must have only one argument of type *gorm.DB. It means that we must move out Limit from them (let's say we get it from user). If we need to implement query QueryUsersRegisteredAfter(db *gorm.DB, t time.Time) - we can't do it.

Now compare it with go-queryset solution:

// UserQuerySet is an autogenerated struct with a lot of typesafe methods.
// We can define any methods on it because it's in the same package
func (qs UserQuerySet) WithMaxRating(minMarks int) UserQuerySet {
	return qs.RatingMarksGte(minMarks).OrderDescByRating()
}

func (qs UserQuerySet) RegisteredToday() UserQuerySet {
	// autogenerated typesafe method CreatedAtGte(time.Time)
	return qs.CreatedAtGte(getTodayBegin())
}

// now we can parametrize it
const minRatingMarks = 10

func GetUsersWithMaxRating(limit int) ([]User, error) {
	var users []User
	err := NewUserQuerySet(getGormDB()).
		WithMaxRating(minRatingMarks). // reuse our method
		Limit(limit).                  // autogenerated typesafe method Limit(int)
		All(&users)                    // autogenerated typesafe method All(*[]User)
	if err != nil {
		return nil, err
	}
	return users, nil
}

func GetUsersRegisteredToday(limit int) ([]User, error) {
	var users []User
	err := NewUserQuerySet(getGormDB()).
		RegisteredToday(). // reuse our method
		Limit(limit).      // autogenerated typesafe method Limit(int)
		All(&users)        // autogenerated typesafe method All(*[]User)
	if err != nil {
		return nil, err
	}
	return users, nil
}

func GetUsersRegisteredTodayWithMaxRating(limit int) ([]User, error) {
	var users []User
	err := NewUserQuerySet(getGormDB()).
		RegisteredToday().             // reuse our method
		WithMaxRating(minRatingMarks). // reuse our method
		Limit(limit).
		All(&users) // autogenerated typesafe method All(*[]User)
	if err != nil {
		return nil, err
	}
	return users, nil
}

Why not raw SQL queries?

No type-safety, a lot of boilerplate code.

Why not go-kallax?

1. It works only with PostgreSQL. Go-queryset supports mysql, postgresql, sqlite, mssql etc (all that gorm supports).
2. Lacks simplier model updating interface

How it relates to another languages ORMs

QuerySet pattern is similar to:

• Django QuerySet, but better than it because of type-safety (Python)
• Rails Active Record and it's scopes, but better than it because of type-safety (Ruby)

Features

• 100% typesafe: there is no one method with interface{} arguments.
• QuerySet pattern allows to reuse queries by defining custom methods on it.
• Supports all DBMS that GORM supports: MySQL, PostgreSQL, Sqlite3, SQL Server.
• Supports creating, selecting, updating, deleting of objects.

Limitations

• Joins aren't supported
• Struct tags aren't supported

Performance

Runtime

Performance is similar to GORM performance. GORM uses reflection and it may be slow, so why don't we generate raw SQL code?

1. Despite the fact GORM uses reflection, it's the most popular ORM for golang. There are really few tasks where you are CPU-bound while working with DB, usually you are CPU-bound in machine with DB and network/disk bound on machine with golang server.
2. Premature optimization is the root of all evil.
3. Go-queryset is fully compatible with GORM.
4. Code generation is used here not to speedup things, but to create nice interfaces.
5. The main purpose of go-queryset isn't speed, but usage convenience.

Code generation

Code generation is fast:

1. We parse AST of needed file and find needed structs.
2. We load package and parse it by go/types
3. We don't use reflect module for parsing, because it's slow