Gorm CRUD demo

This tutorial shows you how to build a simple GO+Gorm CRUD(Create, Read, Update, Delete) application with MatrixOne. Gorm is one of the most popular ORM tools in GO language.

Before you start

A brief introduction about these softwares concerned:

  • Gorm: The fantastic ORM library for Golang aims to be developer friendly. gorm.io/gorm and gorm.io/driver/mysql will vbe used to make Go connect to MYSQL.

Setup your environment

Before you start, make sure you have downloaded and installed the following software.

  1. Make sure you have already installed and launched MatrixOne. Connect to MatrixOne and create a database by MySQL client.

    1. mysql> create database test;

  2. Make sure you have already installed Golang 1.18 (or plus) version.

    1. #To check with Golang installation and its version
    2. go version

  3. Make sure you have already installed MySQL.

  4. Use command go get to install gorm.io/gorm and gorm.io/driver/mysql.

    1. go get -u gorm.io/gorm
    2. go get -u gorm.io/driver/mysql

As we have explained how to connect to MatrixOne by Gorm in the other tutorial, we will focus on the CRUD(Create, Read, Update, Delete) implementations in this tutorial.

Create

As an Object Relational Mapper(ORM) tool, Gorm allows developers to create go class to map the table in relational database. In the example below, we will create a User class. The class name and the attribute name must starts with a capital letter to make public access.

Create a go file gorm_create.go and put the code below.

  1. package main
  2. import (
  3.     "fmt"
  5.     "gorm.io/driver/mysql"
  6.     "gorm.io/gorm"
  7.     "gorm.io/gorm/logger"
  8. )
  9. // user model
  10. type USER struct {
  11.     ID       uint `gorm:"primaryKey"`
  12.     CNAME    string
  13.     CADDRESS string
  14. }
  16. func getDBConn() *gorm.DB {
  17.     dsn := "root:111@tcp(127.0.0.1:6001)/test?charset=utf8mb4&parseTime=True&loc=Local" //MO
  18.     db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
  19.     // Logger: logger.Default.LogMode(logger.Info), //print SQL
  20.   })
  21.     // get connection
  22.     if err != nil {
  23.         fmt.Println("Database Connection Failed") //Connection failed
  24.     } else {
  25.         fmt.Println("Database Connection Succeed") //Connection succeed
  26.     }
  27.     return db
  28. }
  30. func main() {
  31.     //get *gorm.DB
  32.     db := getDBConn()
  34.     // auto create table
  35.     db.AutoMigrate(&USER{})
  36. }

To enable the logging of converted SQL output, you can uncomment the following line:Logger: logger.Default.LogMode(logger.Info)

To run the Go file, open a terminal and use the following command:

  1. go run gorm_create.go

You can use a MySQL client to verify if the table has been successfully created.

  1. mysql> show tables;
  2. +----------------+
  3. | Tables_in_test |
  4. +----------------+
  5. | users          |
  6. +----------------+
  7. 1 row in set (0.01 sec)

Inserting Data

In the following example, you will be guided on how to insert two records into the users table that was created earlier. The ID column is assumed to be auto-incremented, but you can also specify a fixed value.

Create a new text file named gorm_insert.go and copy the following code into the file:

  1. package main
  2. import (
  3.     "fmt"
  5.     "gorm.io/driver/mysql"
  6.     "gorm.io/gorm"
  7.     "gorm.io/gorm/logger"
  8. )
  9. // user model
  10. type USER struct {
  11.     ID       uint `gorm:"primaryKey"`
  12.     CNAME    string
  13.     CADDRESS string
  14. }
  16. func getDBConn() *gorm.DB {
  17.     dsn := "root:111@tcp(127.0.0.1:6001)/test?charset=utf8mb4&parseTime=True&loc=Local" //MO
  18.     db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
  19.     // get connection
  20.     if err != nil {
  21.         fmt.Println("Database Connection Failed") //Connection failed
  22.     } else {
  23.         fmt.Println("Database Connection Succeed") //Connection succeed
  24.     }
  25.     return db
  26. }
  28. func main() {
  29.     //get *gorm.DB
  30.     db := getDBConn()
  32.     // auto create table
  33.     db.AutoMigrate(&USER{})
  35.   // **Insert users**
  36.     users := []USER{
  37.         {
  38.             // ID: 1, //autoincrement
  39.             CNAME:    "lili",
  40.             CADDRESS: "Shanghai"},
  41.         {
  42.             ID:       111,
  43.             CNAME:    "zhang",
  44.             CADDRESS: "Biejing",
  45.         },
  46.     }
  48.     db.Create(users)
  50. }

Open your terminal and execute the following command to run the go file:

  1. go run gorm_insert.go

Similarly, you can use a MySQL client to verify if the data has been successfully inserted into the table.

  1. mysql> select * from users;
  2. +------+-------+----------+
  3. | id   | cname | caddress |
  4. +------+-------+----------+
  5. |    1 | lili  | Shanghai |
  6. |  111 | zhang | Biejing  |
  7. +------+-------+----------+
  8. 2 rows in set (0.01 sec)

Querying Data

In the following example, you will be guided on how to query a subset of data based on a condition, specifically querying data where CNAME=zhang .

Create a new text file named gorm_query.go and copy the following code into the file:

  1. package main
  2. import (
  3.     "fmt"
  5.     "gorm.io/driver/mysql"
  6.     "gorm.io/gorm"
  7.     "gorm.io/gorm/logger"
  8. )
  9. // user model
  10. type USER struct {
  11.     ID       uint `gorm:"primaryKey"`
  12.     CNAME    string
  13.     CADDRESS string
  14. }
  16. func getDBConn() *gorm.DB {
  17.     dsn := "root:111@tcp(127.0.0.1:6001)/test?charset=utf8mb4&parseTime=True&loc=Local" //MO
  18.     db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
  19.     // get connection
  20.     if err != nil {
  21.         fmt.Println("Database Connection Failed") //Connection failed
  22.     } else {
  23.         fmt.Println("Database Connection Succeed") //Connection succeed
  24.     }
  25.     return db
  26. }
  28. func main() {
  29.     //get *gorm.DB
  30.     db := getDBConn()
  32.     // auto create table
  33.     db.AutoMigrate(&USER{})
  35.   // **Query—— String condition** 
  36.     res := USER{}
  37.     tx := db.Where("CNAME = ? ", "zhang").Find(&USER{}).Scan(&res)
  38.     if tx.Error != nil {
  39.         fmt.Println(tx.Error)
  40.         return
  41.     }
  42.     fmt.Println(res)
  44. }

Open your terminal and execute the following command to run the go file:

  1. go run gorm_query.go

The output of the terminal will include the following data.

  1. {111 zhang Biejing}

Updating Data

In the following demonstration, you will be guided on how to update data. Create a text file named gorm_update.go and copy-paste the following code into the file:

  1. package main
  2. import (
  3.     "fmt"
  5.     "gorm.io/driver/mysql"
  6.     "gorm.io/gorm"
  7.     "gorm.io/gorm/logger"
  8. )
  9. // user model
  10. type USER struct {
  11.     ID       uint `gorm:"primaryKey"`
  12.     CNAME    string
  13.     CADDRESS string
  14. }
  16. func getDBConn() *gorm.DB {
  17.     dsn := "root:111@tcp(127.0.0.1:6001)/test?charset=utf8mb4&parseTime=True&loc=Local" //MO
  18.     db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
  19.     // get connection
  20.     if err != nil {
  21.         fmt.Println("Database Connection Failed") //Connection failed
  22.     } else {
  23.         fmt.Println("Database Connection Succeed") //Connection succeed
  24.     }
  25.     return db
  26. }
  28. func main() {
  29.     //get *gorm.DB
  30.     db := getDBConn()
  32.     // auto create table
  33.     db.AutoMigrate(&USER{})
  35.   // **Update** 
  36.     aUser := USER{}
  37.     tx := db.Where("CNAME = ? ", "zhang").Find(&USER{}).Scan(&aUser)
  38.     if tx.Error != nil {
  39.         fmt.Println(tx.Error)
  40.         return
  41.     }
  42.     res:=db.Model(&aUser).Update("CADDRESS", "HongKong")
  43.   if res.Error != nil {
  44.         fmt.Println(tx.Error)
  45.         return
  46.     }
  48. }

Open your terminal and execute the following command to run the go file:

  1. go run gorm_update.go

You can use the MySQL client to verify if the table has been updated successfully:

  1. mysql> select * from users;
  2. +------+-------+----------+
  3. | id   | cname | caddress |
  4. +------+-------+----------+
  5. |  111 | zhang | HongKong |
  6. |    1 | lili  | Shanghai |
  7. +------+-------+----------+
  8. 2 rows in set (0.00 sec)

Deleting Data

In the following demonstration, you will be guided on how to delete a single data record. It is important to note that when deleting a single record, you need to specify the primary key, otherwise it may trigger a batch delete. Create a text file named gorm_delete.go and copy-paste the following code into the file:

  1. package main
  2. import (
  3.     "fmt"
  5.     "gorm.io/driver/mysql"
  6.     "gorm.io/gorm"
  7.     "gorm.io/gorm/logger"
  8. )
  9. // user model
  10. type USER struct {
  11.     ID       uint `gorm:"primaryKey"`
  12.     CNAME    string
  13.     CADDRESS string
  14. }
  16. func getDBConn() *gorm.DB {
  17.     dsn := "root:111@tcp(127.0.0.1:6001)/test?charset=utf8mb4&parseTime=True&loc=Local" //MO
  18.     db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
  19.     // get connection
  20.     if err != nil {
  21.         fmt.Println("Database Connection Failed") //Connection failed
  22.     } else {
  23.         fmt.Println("Database Connection Succeed") //Connection succeed
  24.     }
  25.     return db
  26. }
  28. func main() {
  29.     //get *gorm.DB
  30.     db := getDBConn()
  32.     // auto create table
  33.     db.AutoMigrate(&USER{})
  35.   // **Delete** 
  36.     aUser := USER{}
  37.     tx := db.Where("CNAME = ? ", "zhang").Find(&USER{}).Scan(&aUser)
  38.     if tx.Error != nil {
  39.         fmt.Println(tx.Error)
  40.         return
  41.     }
  42.     res := db.Delete(&aUser)
  43.     if res.Error != nil {
  44.         fmt.Println(tx.Error)
  45.         return
  46.     }
  48. }

Open your terminal and execute the following command to run the go file:

  1. go run gorm_delete.go

You can use the MySQL client to verify if the table has been deleted successfully:

  1. mysql> select * from users;
  2. +------+-------+----------+
  3. | id   | cname | caddress |
  4. +------+-------+----------+
  5. |    1 | lili  | Shanghai |
  6. +------+-------+----------+
  7. 1 row in set (0.00 sec)

The above is just a partial demonstration of CRUD operations in GORM. For more usage and examples, please refer to the GORM Official Guides.