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
- Golang version
- Why
- 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
- Equals:
- numeric types (
int,int64,uintetc +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 {}
- all field types
-
preload related object (for structs fields or pointers to structs fields):
Preload{FieldName}()For structtype User struct { profile *Profile }
will be generated:
func (qs UserQuerySet) PreloadProfile() UserQuerySet
Preloadfunctions callgorm.Preloadto preload related object. -
selectors
- Select all objects, return
gorm.ErrRecordNotFoundif no records
func (qs UserQuerySet) All(users *[]User) error
- Select one object, return
gorm.ErrRecordNotFoundif no records
func (qs UserQuerySet) One(user *User) error
- Select all objects, return
-
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:
- 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 (!) anderror == niletc. It's easy to getSELECT * FROM t WHERE string_field == 1SQL in production without type safety. - 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?
- It works only with PostgreSQL. Go-queryset supports mysql, postgresql, sqlite, mssql etc (all that gorm supports).
- 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?
- 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.
- Premature optimization is the root of all evil.
- Go-queryset is fully compatible with GORM.
- Code generation is used here not to speedup things, but to create nice interfaces.
- The main purpose of go-queryset isn't speed, but usage convenience.
Code generation
Code generation is fast:
- We parse AST of needed file and find needed structs.
- We load package and parse it by
go/types - We don't use
reflectmodule for parsing, because it's slow
