Go xorm Quick Tutorial

INTRODUCTION

The database interface database/sql provided by the Go standard library is relatively low-level, and using it to operate the database is very cumbersome and error-prone. Therefore, the community has open sourced many third-party libraries, such as the sqlc tool in the previous article, and various ORMs (Object Relational Mapping, Object Relational Mapping Library), such as gorm and xorm. This article introduces xorm. Xorm is a simple but powerful ORM library for Go language, using it can greatly simplify our database operations.

QUICK START

Install xorm in command line

				
					go get xorm.io/xorm
				
			

Install driver for your database

				
					go get github.com/lib/pq
				
			

Initialization

model.DB.Walk here is a local varieble which you can define by your self.

				
					// import xorm
import (
    _ "github.com/lib/pq"
    "github.com/xormplus/xorm"
)

// init string
const OnlineConn = "postgres://bytepower_rw:xxxxxx@zzzxxx.com.cn:5432/xxx_db_namexxx?sslmode=disable;"

// init db  model.DB.Walk is an engine init by xorm 
model.DB.Walk, err = xorm.NewEngine("postgres", OnlineConn)
if err != nil {
	panic("Initialization Failed:" + err.Error())
}

// Connection pooling settings
model.DB.Walk.SetMaxOpenConns(cfg.MaxOpenConns)
//Set Idle connections
model.DB.Walk.SetMaxIdleConns(cfg.MaxIdleConns)
// Set connections' lifetime
model.DB.Walk.SetConnMaxLifetime(10 * time.Minute)

defer model.DB.Walk.Close()
				
			

Define Struct

Define struct to describe the table in your database

				
					type StudentTest struct {
	// Primary key sid
	Id string `json:"id" xorm:"varchar(255) pk 'sid' comment('ID')"`
	Name string `json:"name" xorm:"varchar(25) notnull 'name' comment('Studen's Name')"`
	Age  int    `json:"age" xorm:"notnull 'age' comment('Student's Age')"`
	Score   float64 `json:"score" xorm:"notnull 'score' comment('Student's Score')"`
	ClassId string  `json:"class_id" xorm:"notnull 'class_id' comment('Student's Class')"`
	CreatedTime time.Time `json:"created_time" xorm:"created notnull"`
	UpdatedTime time.Time `json:"updated_time" xorm:"updated notnull"`
}

func (s *StudentTest) TableName() string {
	return "student_test"
}

// Create a class table for cross table querry
type SClassTestModel struct {
	Id   string `json:"id" xorm:"varchar(255) pk 'cid' comment('Class id')"`
	Name string `json:"name" xorm:"varchar(255) notnull 'name' comment('Class Name')"`
}

func (sc *SClassTestModel) TableName() string {
	return "sclass_test"
}
				
			

Create table

				
					// Create Table Class
func TestCreateClassTable(t *testing.T) {
	err := test.Session.Sync2(aaa_module.SClassTestModel{})
	require.Equal(t, nil, err)
}

// Create Table Student
func TestCreateTable(t *testing.T) {
	err := test.Session.Sync2(aaa_module.StudentTest{})
	require.Equal(t, nil, err)
}
				
			

Insert multiple rows

				
					// Create Table Class
func TestCreateClassTable(t *testing.T) {
	err := test.Session.Sync2(aaa_module.SClassTestModel{})
	require.Equal(t, nil, err)
}

// Create Table Student
func TestCreateTable(t *testing.T) {
	err := test.Session.Sync2(aaa_module.StudentTest{})
	require.Equal(t, nil, err)
}
				
			

Insert Single Row

				
					// Insert Single Row
func InsertStudentObj(stuObj *StudentTest) (int64, error) {
	if ok, err := model.DB.Walk.Table("student_test").Insert(stuObj); err != nil {
		return ok, err
	} else {
		return ok, nil
	}
}

// Implementation
func TestInsertStuObj(t *testing.T) {
	stuObj := aaa_module.StudentTest{Id: "6", Name: "naruto", Age: 22, Score: 99, ClassId: "231"}
	if ok, err := aaa_module.InsertStudentObj(&stuObj); err != nil {
		fmt.Println("insert stuObj raise error! ", err.Error())
	} else {
		fmt.Println("insert stuObj successfully! ok: ", ok)
	}
}
				
			

Query the number of records that match the condition

				
					func GetCount(session *xorm.Session, query interface{}, args ...interface{}) (count int64, err error) {
	return session.Where(query, args...).Count(&WithdrawRecordModel{})
}

// Usage
cashId := "123"
userId := "2"
query := "cash_id = ? and user_id = ?"
cashCount, err := GetCount(session, query, cashId, userId)
				
			

Check if records exists

				
					// Check if records exists
func ExistStu(stu *StudentTest) (bool, error) {
	has, err := model.DB.Walk.Table("student_test").Exist(stu)
	return has, err
}

// Implementation
func TestExistStuObj(t *testing.T) {
	stuObj := aaa_module.StudentTest{Name: "naruto"}
	has, err := aaa_module.ExistStu(&stuObj)
	if err != nil {
		fmt.Println("err: ", err.Error())
	} else if !has {
		fmt.Println("Record don't exist!")
	} else {
		fmt.Println("Record exist!")
	}
}
				
			

Query efficiently with primary key

				
					// More efficient way to qury students via id
func GetStudentById(sid string) (*StudentTest, bool, error) {
	stuObj := StudentTest{}
	has, err := model.DB.Walk.Table("student_test").Id(sid).Get(&stuObj)

	// Can also point out columns
	// has, err := model.DB.Walk.Table("student_test").Id(sid).Cols("sid", "name", "score")

	return &stuObj, has, err
}

//Implementation
func TestGetStudentById(t *testing.T) {
	sid := "2"
	stuObj, has, err := aaa_module.GetStudentById(sid)
	if err != nil {
		fmt.Println("get student by i·d raise error! ", err.Error())
	} else {
		if !has {
			fmt.Println("can't get stuObj by that sid!")
		} else {
			fmt.Println(fmt.Sprintf("get the stuObj: sid: %s, Name: %s, Age: %d, Score: %.3f, ClassId %s ", stuObj.Id, stuObj.Name, stuObj.Age, stuObj.Score, stuObj.ClassId))
		}
	}
}
				
			

Query with conditions

				
					// Pass a struct as query condition
func GetStuObj(stu *StudentTest) (*StudentTest, bool, error) {
	has, err := model.DB.Walk.Table("student_test").Get(stu)
	return stu, has, err
}

// Implementation
//Save your conditions into the target struct
func TestGetStudent(t *testing.T) {
	stu := &aaa_module.StudentTest{Name: "naruto"}

	stu, has, err := aaa_module.GetStuObj(stu)
	if err != nil {
		fmt.Println("get student raises error! ", err.Error())
	} else if !has {
		fmt.Println("can't get stuObj!")
	} else {
		fmt.Println("stuObj: ", stu.Id, stu.Name, stu.Score)
	}
}
				
			

Query with native SQL code

				
					func JoinQuery() error {
	// Native SQL code
	sqlStr := "select t1.sid as stu_id, t1.name as stu_name,t1.age as stu_age, t1.score as stu_score, t2.name as class_name, t2.cid as class_id " +
		"from student_test as t1 " +
		"left join s_class_test_model as t2 " +
		"on t1.class_id = t2.cid"

	// 1. QueryInterface: recommend
	rets, err := model.DB.Walk.Table("student_test").QueryInterface(sqlStr)
	fmt.Println("QueryInterface: ret: ", rets)
	for _, mp := range rets {
		fmt.Println("mp: ", mp["class_id"], mp["class_name"], mp["stu_name"])
	}

	// 2. QueryString
	results, err := model.DB.Walk.Table("student_test").QueryString(sqlStr)
	fmt.Println("QueryString ret: ", results)
	for _, mp := range results {
		fmt.Println("mp: ", mp["class_id"], mp["class_name"], mp["stu_name"])
	}

	return err
}

// Usage
func TestJoinQuery(t *testing.T){
	err := aaa_module.JoinQuery()
	if err != nil{
		fmt.Println("err: ", err)
	}else{
		fmt.Println("join Query successfully")
	}
}
				
			

Update rows

				
					// Update records
/*
	Calling engine.Update() to implement updates,can use map[string]interface{} as condition.
*/
func UpdateData() (int64, error){

	affected, err := model.DB.Walk.Table("student_test").Where("name=?", "naruto").Update(&StudentTest{Score: 123})

	// Alternatively
	// affected, err := model.DB.Walk.Table("student_test").ID("2").Update(&StudentTest{Score: 666})

	return affected, err
}

// Usage
func TestUpdate(t *testing.T){

	affected, err := aaa_module.UpdateData()
	if err != nil{
		fmt.Println("err: ", err)
	}else{
		fmt.Println("Update Successful !affected: ", affected)
	}
}
				
			

Delete rows

				
					func DeleteData() (int64, error){
	affected, err := model.DB.Walk.Table("student_test").Where("name=?", "sasuke").Delete(&StudentTest{})
	return affected, err
}
				
			

Leave a Comment

Your email address will not be published.