The introduction and use of GORM framework under Golang

Gorm is an ORM library with excellent performance in Golang language, which is relatively friendly to developers. Of course, there is another XORM library is also more famous, interested can also take a look at this library, next I will introduce some basic use of gorM library.

GORM introduction and quick start

Functions overview

Fully functional ORM(Infinite Proximity)
(Has One, Has Many, Belongs To, Many To Many, polymorphism)
Hook function Hook(before or after creating/saving/updating/deleting/finding)
preload
The transaction
Composite primary key
SQL generator
Automatic database migration
Custom Logs
Extensible, plug-ins can be written based on GORM callbacks
All features are covered by tests
Developer friendly

The installation

As we all know, golang uses driver packages to operate on specific databases, such as the github.com/go-sql-driver/mysql library for MySQL, Sqlite needs to use github.com/mattn/go-sqlite3 library to support, but fortunately, gorM framework for each driver package is simple packaging, so we can write programs when we can easily manage the driver library.

Supported databases and import paths are as follows:

mysql: github.com/jinzhu/gorm/dialects/mysql
postgres: github.com/jinzhu/gorm/dialects/postgres
sqlite: github.com/jinzhu/gorm/dialects/sqlite
sqlserver: github.com/jinzhu/gorm/dialects/mssql

Note: The GORM framework simply encapsulates the database driver package, and you still need to download the original driver package at installation time

Mysql is used for data storage in this project

$ go get -u github.com/jinzhu/gorm

$ go get -u github.com/go-sql-driver/mysql



Copy the code

Quick start

Use docker to quickly create a locally accessible mysql instance

$ docker run -itd -e MYSQL_ROOT_PASSWORD='bgbiao.top' --name go-orm-mysql  -p 13306:3306 mysql:5.6



Log in to mysql and create a test library

$ docker exec -it go-orm-mysql mysql -uroot -pbgbiao.top

.

mysql> create database test_api;

Query OK, 1 row affected (0.00 sec)



mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test_api           |

+--------------------+

4 rows in set (0.00 sec)



Run a simple example

$ cat gorm-mysql-example.go

package main

import (

    "fmt"

    "time"



    "github.com/jinzhu/gorm"

    _ "github.com/jinzhu/gorm/dialects/mysql"

)





// Define a data model (user table)

// The column name is the serpentine lowercase of the field name (PassWd->pass_word)

type User struct {

    Id          uint        `gorm:"AUTO_INCREMENT"`

    Name        string      `gorm:"size:50"`

    Age         int         `gorm:"size:3"`

    Birthday    *time.Time

Email string `gorm:"type:varchar(50); unique_index"`

    PassWord    string      `gorm:"type:varchar(25)"`

}



var db *gorm.DB



func main() {

The db, err: = gorm. Open (" mysql ", "root: bgbiao. Top @ (127.0.0.1:13306)/test_api? charset=utf8&parseTime=True&loc=Local")

if err ! = nil {

Errorf(" failed to create database connection :%v",err)



    }



    defer db.Close()



// Automatically migrate data structure (table schema)

// Note: In GORM, the default table name is the plural form of the structure name. For example, the User structure creates the table users by default

// db.singularTable (true) Removes the plural form of the table name to make it consistent with the structure name

    db.AutoMigrate(&User{})





// Add a unique index

    db.Model(&User{}).AddUniqueIndex("name_email", "id", "name","email")



// Insert a record

    db.Create(&User{Name:"bgbiao",Age:18,Email:"[email protected]"})

    db.Create(&User{Name:"xxb",Age:18,Email:"[email protected]"})



    var user User

    var users []User

// View all the inserted elements

FMT.Printf(" Insert element :\n")

    db.Find(&users)

    fmt.Println(users)



// Query a record

db.First(&user,"name = ?" ,"bgbiao")

FMT.Println(" query record :",user)



// Update records (based on found data)

    db.Model(&user).Update("name","biaoge")

FMT.Println(" Updated record :",user)



// Delete the record

    db.Delete(&user)



// View all records

FMT.Println(" View all records :")



    db.Find(&users)

    fmt.Println(users)

}



Run the gorM instance

$ go run gorm-mysql-example.go

Insert element:

[{1 bgbiao 18 <nil> [email protected] } {2 xxb 18 <nil> [email protected] }]

{1 bgbiao 18 <nil> [email protected]}

Updated record: {1 biaoge 18 <nil> [email protected]}

View all records:

[{2 xxb 18 <nil> [email protected] }]





Copy the code

GORM common function functions

Automatic migration

Note: Using automatic migration mode keeps the table up to date, but does not update the index and the types of existing columns or delete unused columns

// Migrate multiple models simultaneously

db.AutoMigrate(&User{}, &Product{}, &Order{})



// Add parameters when creating a table

// Change the character type of the table CHARSET=utf8

db.Set("gorm:table_options", "ENGINE=InnoDB").AutoMigrate(&User{})

Copy the code

Check the table

// Check whether the model exists

db.HasTable(&User{})



// Check whether the table exists

db.HasTable("users")



Copy the code

Add, delete, change table structure

Note: in a real enterprise production environment, database level change operations usually need to be converted into SQL for the DBA brothers to help with online database table changes, so both automatic migration and manual table creation are in the development environment stage

// Use model creation

db.CreateTable(&User{})



// Add parameters to create

db.Set("gorm:table_options", "ENGINE=InnoDB").CreateTable(&User{})



/ / delete table

db.DropTable(&User{})

db.DropTable("users")



// mix model and table names

db.DropTableIfExists(&User{}, "products")





// Modify column (modify field type)

db.Model(&User{}).ModifyColumn("description", "text")



/ / delete columns

db.Model(&User{}).DropColumn("description")



// Create a table with a table name

db.Table("deleted_users").CreateTable(&User{})



// specify table name query

var deleted_users []User

db.Table("deleted_users").Find(&deleted_users)



Copy the code

Indexes and constraints

// Add a foreign key

// 1st param: foreign key field

// 2nd Param: foreign key table

// 3rd param : ONDELETE

// 4th param : ONUPDATE

db.Model(&User{}).AddForeignKey("city_id", "cities(id)", "RESTRICT", "RESTRICT")



// Single index

db.Model(&User{}).AddIndex("idx_user_name", "name")



// Multi-field index

db.Model(&User{}).AddIndex("idx_user_name_age", "name", "age")



// Add a unique index (usually multiple fields are used to uniquely identify a record)

db.Model(&User{}).AddUniqueIndex("idx_user_name", "name")

db.Model(&User{}).AddUniqueIndex("idx_user_name_age", "name", "id","email")





// Drop index

db.Model(&User{}).RemoveIndex("idx_user_name")





Copy the code

GORM Model considerations

Gorm. Model structure

In the example of the gorM official documentation, a gorm.model property is added to the properties of the Model by default. The original structure of the property is as follows:

// Official sample model

type User struct {

  gorm.Model

  Name         string

.

}



// Gorm.model structure

type Model struct {

	ID        uint `gorm:"primary_key"`

	CreatedAt time.Time

	UpdatedAt time.Time

	DeletedAt *time.Time `sql:"index"`

}

Copy the code

Obviously, when we add gorm.model to our user-defined Model, we automatically add id, CREATED_AT, UPDATed_AT, and deleted_AT fields to our table.

At the same time, it is important to note that when our model have CreatedAt, UpdatedAt, DeletedAt properties and types for time. Time or * time. The time type, operation, when there is data will automatically update the corresponding time.

Therefore, when defining the Model, you can decide whether to introduce the gorm.Model structure according to the actual requirements

Note also that all fields with zero values, such as 0, “”, false, or other zero values are not stored in the database, but their DEFAULT values are used. Therefore, for non-mandatory fields, you can use the DEFAULT tag field to declare the column DEFAULT values.

Tags supported by the GORM model

Note: GORM supports some common tags for defining extended information from model fields

Structural marking	describe
column	List (default is serpentine lowercase for field)
type	The data type
size	The length of the column
PRIMARY_KEY	Statement of the primary key
UNIQUE	The statement only
DEFAULT	Specify default values
PRECISION	Declare column precision
NOT NULL	Specifies the column as non-NULL
AUTO_INCREMENT	Declaration increment column
INDEX	Create indexes with or without names, or create composite indexes if multiple indexes have the same name
UNIQUE_INDEX	Create a unique index
EMBEDDED	Set the structure to embed
EMBEDDED_PREFIX	Sets the prefix of the embedded structure
–	Ignore this field

Note: Gorm also supports some associated structure tags, such as foreign keys, associated foreign keys, and other operations, usually in the complex enterprise environment, it is recommended to design the related tables in the library table are designed as isolated tables, the specific association logic by the business layer to achieve (may increase the cost of development, but when the business development is more complex, This is undoubtedly convenient for later expansion and optimization.)

Detailed CRUD interface

create

Insert records

Insert into users(name,age,brithday) values("BGBiao",18, time.now ())

user := User{Name: "BGBiao", Age: 18, Birthday: time.Now()}

// Return 'true' if the primary key is empty

db.NewRecord(user)

db.Create(&user)

// Return false after creating 'user'

db.NewRecord(user)



Copy the code

Set field values in Hooks

Note: Usually when we design a model we have some original fields that we want to have records when we initialize the model, so we can use hooks to insert the original records

If you want to change the value of the column in BeforeCreatehook, you can use scope.SetColumn:

func (user *User) BeforeCreate(scope *gorm.Scope) error {

  scope.SetColumn("ID", uuid.New())

  return nil

}



Copy the code

Create extension options

// Add extended SQL options for Instert statements

// insert into produce(name,code) values("name","code") on conflict;

db.Set("gorm:insert_option", "ON CONFLICT").Create(&product)



Copy the code

The query

The basic query

// Query the first record by primary key

// SELECT * FROM users ORDER BY id LIMIT 1;

db.First(&user)





// Get a random record

// SELECT * FROM users LIMIT 1;

db.Take(&user)





// Query the last record by primary key

// SELECT * FROM users ORDER BY id DESC LIMIT 1;

db.Last(&user)





// Query all records

// SELECT * FROM users;

db.Find(&users)





// Query a specified record (only available if the primary key is an integer)

// SELECT * FROM users WHERE id = 10;

db.First(&user, 10)





Copy the code

Query in structure mode

// Struct mode

// select * from users where name = 'bgbiao.top'

db.Where(&User{Name: "bgbiao.top", Age: 20}).First(&user)



/ / Map

// select * from users where name = 'bgbiao.top' and age = 20;

db.Where(map[string]interface{}{"name": "bgbiao.top", "age": 20}).Find(&users)



// Slice the primary key

// select * from users where id in (20,21,22);

db.Where([]int64{20, 21, 22}).Find(&users)



Copy the code

Where conditional query

Note: The Where() method is used, which is basically basic SQL syntax

// Get a record using the condition First() method

db.Where("name = ?" , "bgbiao.top").First(&user)



// Get all records Find() method

db.Where("name = ?" , "jinzhu").Find(&users)



/ / is not equal to

db.Where("name <> ?" , "jinzhu").Find(&users)



// IN

db.Where("name IN (?) ", []string{"jinzhu", "bgbiao.top"}).Find(&users)



// LIKE

db.Where("name LIKE ?" , "%jin%").Find(&users)



// AND

db.Where("name = ? AND age >= ?" , "jinzhu", "22").Find(&users)



// Time

// select * from users where updated_at > '2020-03-06 00:00:00'

db.Where("updated_at > ?" , lastWeek).Find(&users)



// BETWEEN

// select * from users where created_at between '2020-03-06 00:00:00' and '2020-03-14 00:00:00'

db.Where("created_at BETWEEN ? AND ?" , lastWeek, today).Find(&users)



Copy the code

The Not condition

// select * from users where name ! = 'bgbiao.top';

db.Not("name", "jinzhu").First(&user)



// Not In

// select * from users where name not in ("jinzhu","bgbiao.top");

db.Not("name", []string{"jinzhu", "bgbiao.top"}).Find(&users)



// The primary key is not in slice

// select * from users where id not in (1,2,3)

Db. The Not (int64 [] {1, 2, 3}). The First (and user)



// select * from users;

db.Not([]int64{}).First(&user)



/ / the native SQL

// select * from users where not(name = 'bgbiao.top');

db.Not("name = ?" , "bgbiao.top").First(&user)



// struct

// select * from users where name ! = 'bgbiao.top'

db.Not(User{Name: "bgbiao.top"}).First(&user)

Copy the code

Or conditions

// SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin';

db.Where("role = ?" , "admin").Or("role = ?" , "super_admin").Find(&users)



/ / Struct

// SELECT * FROM users WHERE name = 'jinzhu' OR name = 'bgbiao.top';

db.Where("name = 'jinzhu'").Or(User{Name: "bgbiao.top"}).Find(&users)



/ / Map

// SELECT * FROM users WHERE name = 'jinzhu' OR name = 'bgbiao.top';

db.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "bgbiao.top"}).Find(&users)



Copy the code

FirstOrCreate

Gets the first record that matches, otherwise a new record is created based on the given condition (struct and map conditions only supported)

// Insert record if not found

if select * from users where name = 'non_existing') is null; insert into users(name) values("non_existing")

db.FirstOrCreate(&user, User{Name: "non_existing"})



/ / find

// select * from users where name = 'bgbiao.top'

db.Where(User{Name: "bgbiao.top"}).FirstOrCreate(&user)



## attrs argument: If the record is not found, the struct and record are created using the argument.

// Not found (preconditions and populates the unattrs data into the Attrs method)

db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrCreate(&user)



/ / find

db.Where(User{Name: "bgbiao.top"}).Attrs(User{Age: 30}).FirstOrCreate(&user)



## Assgin parameter: Assigns the parameter to the struct and saves it to the database whether the record is found or not

db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrCreate(&user)





Copy the code

The subquery

* gorm expr subqueries

// SELECT * FROM "orders"  WHERE "orders"."deleted_at" IS NULL AND (amount > (SELECT AVG(amount) FROM "orders"  WHERE (state = 'paid')));

db.Where("amount > ?" , DB.Table("orders").Select("AVG(amount)").Where("state = ?" , "paid").QueryExpr()).Find(&orders)



Copy the code

A field

Typically, you only want to select a few fields for the query, specify which fields you want to retrieve from the database, and select all fields by default.

// SELECT name, age FROM users;

db.Select("name, age").Find(&users)



// SELECT name, age FROM users;

db.Select([]string{"name", "age"}).Find(&users)



// SELECT COALESCE(age,'42') FROM users;

db.Table("users").Select("COALESCE(age,?) ", 42).Rows()

Copy the code

Sorting (Order)

// SELECT * FROM users ORDER BY age desc, name;

db.Order("age desc, name").Find(&users)



// Multi-field sort

// SELECT * FROM users ORDER BY age desc, name;

db.Order("age desc").Order("name").Find(&users)



// override sort

//

db.Order("age desc").Find(&users1).Order("age", true).Find(&users2)

Copy the code

Limit the output

// SELECT * FROM users LIMIT 3;

db.Limit(3).Find(&users)



// -1 cancels the Limit condition

// SELECT * FROM users LIMIT 10;

// SELECT * FROM users;

db.Limit(10).Find(&users1).Limit(-1).Find(&users2)

Copy the code

Statistical count

// SELECT count(*) from USERS WHERE name = 'jinzhu' OR name = 'bgbiao.top';

db.Where("name = ?" , "jinzhu").Or("name = ?" , "bgbiao.top").Find(&users).Count(&count)



// select count(*) from users where name = 'bgbiao.top'

db.Model(&User{}).Where("name = ?" , "bgbiao.top").Count(&count)



// SELECT count(*) FROM deleted_users;

db.Table("deleted_users").Count(&count)



// SELECT count( distinct(name) ) FROM deleted_users;

db.Table("deleted_users").Select("count(distinct(name))").Count(&count)

Copy the code

Group (grouo&having)

rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Rows()

for rows.Next() {

.

}



rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?" , 100).Rows()

for rows.Next() {

.

}



type Result struct {

  Date  time.Time

  Total int64

}

db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?" , 100).Scan(&results)

Copy the code

Join queries

rows, err := db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Rows()

for rows.Next() {

.

}



db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&results)



// Multiple connections and parameters

db.Joins("JOIN emails ON emails.user_id = users.id AND emails.email = ?" , "[email protected]").Joins("JOIN credit_cards ON credit_cards.user_id = users.id").Where("credit_cards.number = ?", "411111111111").Find(&user)

Copy the code

Pluck the query

Pluck, query a column in the model as a slice, if you want to query multiple columns, you should use Scan.

var ages []int64

db.Find(&users).Pluck("age", &ages)



var names []string

db.Model(&User{}).Pluck("name", &names)



db.Table("deleted_users").Pluck("name", &names)



Copy the code

Scan to scan

type Result struct {

  Name string

  Age  int

}



var result Result

db.Table("users").Select("name, age").Where("name = ?" , "Antonio").Scan(&result)



/ / the native SQL

db.Raw("SELECT name, age FROM users WHERE name = ?" , "Antonio").Scan(&result)



Copy the code

update

Update all fields

Note: Save updates all fields, even if no values are assigned

db.First(&user)



user.Name = "bgbiao.top"

user.Age = 100

// update users set name = 'bgbiao.top',age=100 where id = user.id

db.Save(&user)





Copy the code

Update modify field

Use the Update and Updates methods

// Update a single attribute if it changes

// update users set name = 'hello' where id = user.id

db.Model(&user).Update("name", "hello")



// Update a single attribute based on a given condition

// update users set name = 'hello' where active = true

db.Model(&user).Where("active = ?" , true).Update("name", "hello")



// Using map to update multiple attributes will update only those attributes that have changed

// update users set name = 'hello',age=18,actived=false where id = user.id

db.Model(&user).Updates(map[string]interface{}{"name": "hello", "age": 18, "actived": false})



// Struct to update multiple attributes, only the fields that have changed and are non-zero are updated

db.Model(&user).Updates(User{Name: "hello", Age: 18})



// Warning: When using struct updates, GORM will only update fields that are not zero values

// For the following operations, no updates will occur, "", 0, false are all zero values of their type

db.Model(&user).Updates(User{Name: "", Age: 0, Actived: false})

Copy the code

Update selected fields

If you want to update or Omit fields, use Select or Omit first

// update users set name = 'hello' where id = user.id;

db.Model(&user).Select("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "actived": false})



// Omit() is used to Omit fields

// update users set age=18,actived=false where id = user.id

db.Model(&user).Omit("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "actived": false})

Copy the code

No hooks to update

The update operation will automatically run the BeforeUpdate,AfterUpdate methods of model to update some updatedat-like fields and save their Associations when updated. If you don’t want to call these methods, You can use UpdateColumn UpdateColumns

// Update a single attribute, similar to 'Update'

// update users set name = 'hello' where id = user.id;

db.Model(&user).UpdateColumn("name", "hello")



// Update multiple properties, similar to 'Updates'

// update users set name = 'hello',age=18 where id = user.id;

db.Model(&user).UpdateColumns(User{Name: "hello", Age: 18})



Copy the code

Batch update

Note: When using struct instances, only non-zero fields will be updated, and all fields will be updated for weak items. Map is recommended

// update users set name = 'hello',age=18 where id in (10,11)

db.Table("users").Where("id IN (?) ", []int{10, 11}).Updates(map[string]interface{}{"name": "hello", "age": 18})



Map [string]interface{}

db.Model(User{}).Updates(User{Name: "hello", Age: 18})



// Use 'RowsAffected' to get the total number of updates

db.Model(User{}).Updates(User{Name: "hello", Age: 18}).RowsAffected

Copy the code

Use SQL to evaluate expressions

// update products set price = price*2+100 where id = product.id

DB.Model(&product).Update("price", gorm.Expr("price * ? + ?", 2, 100))



// update products set price = price*2+100 where id = product.id;

DB.Model(&product).Updates(map[string]interface{}{"price": gorm.Expr("price * ? + ?", 2, 100)})



// update products set quantity = quantity-1 where id = product.id;

DB.Model(&product).UpdateColumn("quantity", gorm.Expr("quantity - ?" , 1))



// update products set quantity = quantity -1 where id = product.id and quantity > 1

DB.Model(&product).Where("quantity > 1").UpdateColumn("quantity", gorm.Expr("quantity - ?" , 1))

Copy the code

delete

Delete records

Note: When deleting a record, make sure that the primary key field has a value. GORM will delete the record through the primary key. If the primary key is empty, GORM will delete all records of the model.

// Delete existing records

// delete from emails where id = email.id;

db.Delete(&email)



// Add additional SQL operations for deleting SQL

// delete from emails where id = email.id OPTION (OPTIMIZE FOR UNKNOWN)

db.Set("gorm:delete_option", "OPTION (OPTIMIZE FOR UNKNOWN)").Delete(&email)



Copy the code

Batch delete

// delete from emails where email like '%jinzhu%'

db.Where("email LIKE ?" , "%jinzhu%").Delete(Email{})



/ / same as above

db.Delete(Email{}, "email LIKE ?" , "%jinzhu%")



Copy the code

Soft delete

If a model has a DeletedAt field, it automatically gets soft delete!

When the Delete method is called, the record is not actually deleted from the database, only the value of the DeletedAt field is set to the current time.

Previously, we might have used a field like isDelete to mark record deletions, but gorM has a built-in DeletedAt field and hooks to ensure soft deletions.

// UPDATE users SET deleted_at="2020-03-13 10:23" WHERE id = user.id;

db.Delete(&user)



// Batch delete

// Delete from deleted_at (deleted_at, deleted_at, deleted_at)

db.Where("age = ?" , 20).Delete(&User{})



// Records that are soft deleted are ignored when querying records

// SELECT * FROM users WHERE age = 20 AND deleted_at IS NULL;

db.Where("age = 20").Find(&user)



// The Unscoped method can be used to query soft deleted records

// SELECT * FROM users WHERE age = 20;

db.Unscoped().Where("age = 20").Find(&users)



Copy the code

Physically deleted

Note: The Unscoped().delete () method is used to execute the Delete statement in SQL.

// The Unscoped method can physically delete records

// DELETE FROM orders WHERE id=10;

db.Unscoped().Delete(&order)



Copy the code

This article is formatted using MDNICE

The introduction and use of GORM framework under Golang

GORM introduction and quick start

GORM common function functions

GORM Model considerations

Detailed CRUD interface

create

The query

update

delete

Related Posts

JDBC is not the only connection to the database, but the reactive driver specifications ADBA and R2DBC

GO language foundation (2) – source files and code packages

Learn to GO with Open Source Projects (3)