Skip to content

gorm basic example

This document will guide you how to build a simple application usinggolang andgorm and implement CRUD (create, read, update, delete) functions.

Gorm is one of the most popular ORM tools in the Python language.

Prepare before starting

A brief introduction to related software:

  • Gorm: A magical full-featured ORM library based on golang. This tutorial mainly uses the two libraries gorm.io/gorm and ``gorm.io/driver/mysql``` to connect Go to the MYSQL database and complete CRUD operations.

Environment Configuration

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

  • Confirm that you have completed the installation of the MySQL client.

  • Completed Create instance and connect to MatrixOne Intelligence through the MySQL client and create a database named test:

    mysql> create database test;

  • Confirm that you have completed the installation [Golang version 1.18 and above] (https://go.dev/dl/), and you can use the following command line to confirm your Golang version:

    #To check with Golang installation and its version
go version

  • Confirm that you have installed gorm.io/gorm and gorm.io/driver/mysql, and use the go get command to install it. The code is as follows:

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

You can refer to Golang Connect MatrixOne Intelligence Service to learn how to connect to MatrixOne Intelligence through Gorm. This document will guide you how to implement CRUD (create, read, update, delete).

Create a new table

As an object relational mapper (ORM) tool, Gorm allows developers to create GO classes to map tables in relational databases. In the following code example, a USER class will be created, where the class name and property name must start with capital English to ensure access to public, otherwise it cannot be created. Under the action of GORM, the USER class will be converted into a SQL statement to create a table with the table name users. Create a new text file of gorm_create.go and copy and paste the following code into the file:

package main

import (
    "fmt"
    "net/url"
    "strconv"

    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)

// user model
type USER struct {
    ID uint `gorm:"primaryKey"`
    CNAME string
    CADDRESS string
}

func getDBConn() *gorm.DB {
    username := "585b49fc_852b_4bd1_b6d1_d64bc1d8xxxx:admin:accountadmin" // modify this
    host := "freetier-01.cn-hangzhou.cluster.matrixonecloud.cn" // modify this
    password := "your_password" // modify this
    port := 6001
    database := "test"
    encodedUsername := url.QueryEscape(username)
    dsn := encodedUsername + ":" + password + "@tcp(" + host + ":" + strconv.Itoa(port) + ")/" + database
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
        // Logger: logger.Default.LogMode(logger.Info), //print SQL
    })
    // get connection
    if err != nil {
        fmt.Println("Database Connection Failed") //Connection failed
    } else {
        fmt.Println("Database Connection Succeed") //Connection succeeded
    }
    return db
}

func main() {
    //get *gorm.DB
    db := getDBConn()

    // auto create table
    db.AutoMigrate(&USER{})
}

You may have noticed that the above example code uses url.QueryEscape() to encode username because there is an English colon : in username, which ensures that the parameter values ​​in the connection string do not interfere with the structure of the connection string.

You can uncomment Logger: logger.Default.LogMode(logger.Info)`` to output the converted SQL. Open the terminal and run this go file with the following code:

go run gorm_create.go

You can use the MySQL client to verify that the table was created successfully:

mysql> show tables;
+---------------------+
| Tables_in_test |
+---------------------+
| users |
+---------------------+
1 row in set (0.01 sec)

Insert data

In the following demonstration, you will guide you to insert two data records into the users table you just created. Here, the ID defaults to self-increment, and can also be specified as a fixed value. Create a new text file of gorm_insert.go and copy and paste the following code into the file:

package main

import (
    "fmt"
    "net/url"
    "strconv"

    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)

// user model
type USER struct {
    ID uint `gorm:"primaryKey"`
    CNAME string
    CADDRESS string
}

func getDBConn() *gorm.DB {
    username := "585b49fc_852b_4bd1_b6d1_d64bc1d8xxxx:admin:accountadmin" // modify this
    host := "freetier-01.cn-hangzhou.cluster.matrixonecloud.cn" // modify this
    password := "your_password" // modify this
    port := 6001
    database := "test"
    encodedUsername := url.QueryEscape(username)
    dsn := encodedUsername + ":" + password + "@tcp(" + host + ":" + strconv.Itoa(port) + ")/" + database
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
    // get connection
    if err != nil {
        fmt.Println("Database Connection Failed") //Connection failed
    } else {
        fmt.Println("Database Connection Succeed") //Connection succeeded
    }
    return db
}

func main() {
    //get *gorm.DB
    db := getDBConn()

    // auto create table
    db.AutoMigrate(&USER{})

    //**Insert users**
    users := []USER{
        {
            // ID: 1, //autoincrement
            CNAME: "lili",
            CADDRESS: "Shanghai"},
        {
            ID: 111,
            CNAME: "zhang",
            CADDRESS: "Biejing",
        },
    }

    db.Create(users)

}

Open the terminal and run this go file with the following code:

go run gorm_insert.go

Similarly, the terminal will output SQL statements, and you can use the MySQL client to verify that the table is successfully inserted:

mysql> select * from users;
+------+----------------------+
| id | cname | caddress |
+------+----------------------+
| 1 | lili | Shanghai |
| 111 | zhang | Biejing |
+------+----------------------+
2 rows in set (0.01 sec)

Query data

In the following demonstration, you will be guided to query some data using conditions and query the data of CNAME=zhang. Create a new text file of gorm_query.go and copy and paste the following code into the file:

package main

import (
    "fmt"
    "net/url"
    "strconv"

    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)

// user model
type USER struct {
    ID uint `gorm:"primaryKey"`
    CNAME string
    CADDRESS string
}

func getDBConn() *gorm.DB {
    username := "585b49fc_852b_4bd1_b6d1_d64bc1d8xxxx:admin:accountadmin" // modify thishost := "freetier-01.cn-hangzhou.cluster.matrixonecloud.cn" // modify this
    password := "your_password" // modify this
    port := 6001
    database := "test"
    encodedUsername := url.QueryEscape(username)
    dsn := encodedUsername + ":" + password + "@tcp(" + host + ":" + strconv.Itoa(port) + ")/" + database
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
    // get connection
    if err != nil {
        fmt.Println("Database Connection Failed") //Connection failed
    } else {
        fmt.Println("Database Connection Succeed") //Connection succeeded
    }
    return db
}

func main() {
    //get *gorm.DB
    db := getDBConn()

    // auto create table
    db.AutoMigrate(&USER{})

    //**Query—— String condition**
    res := USER{}
    tx := db.Where("CNAME = ? ", "zhang").Find(&USER{}).Scan(&res)
    if tx.Error != nil {
        fmt.Println(tx.Error)
        Return
    }
    fmt.Println(res)

}

Open the terminal and run this go file with the following code:

go run gorm_query.go

The output result of the terminal will contain the following data:

{111 zhang Biejing}

Update data

In the following demonstration, you will guide you on how to update the data. Create a new text file of gorm_update.go and copy and paste the following code into the file:

package main

import (
    "fmt"
    "net/url"
    "strconv"

    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)

// user model
type USER struct {
    ID uint `gorm:"primaryKey"`
    CNAME string
    CADDRESS string
}

func getDBConn() *gorm.DB {
    username := "585b49fc_852b_4bd1_b6d1_d64bc1d8xxxx:admin:accountadmin" // modify this
    host := "freetier-01.cn-hangzhou.cluster.matrixonecloud.cn" // modify this
    password := "your_password" // modify this
    port := 6001
    database := "test"
    encodedUsername := url.QueryEscape(username)
    dsn := encodedUsername + ":" + password + "@tcp(" + host + ":" + strconv.Itoa(port) + ")/" + database
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
    // get connection
    if err != nil {
        fmt.Println("Database Connection Failed") //Connection failed
    } else {
        fmt.Println("Database Connection Succeed") //Connection succeeded
    }
    return db
}

func main() {
    //get *gorm.DB
    db := getDBConn()

    // auto create table
    db.AutoMigrate(&USER{})

    //**Update**
    aUser := USER{}
    tx := db.Where("CNAME = ? ", "zhang").Find(&USER{}).Scan(&aUser)
    if tx.Error != nil {
        fmt.Println(tx.Error)
        Return
    }
    res := db.Model(&aUser).Update("CADDRESS", "HongKong")
    if res.Error != nil {
        fmt.Println(tx.Error)
        Return
    }

}

Open the terminal and run this go file with the following code:

go run gorm_update.go

You can use the MySQL client to verify that the table is updated successfully:

mysql> select * from users;
+------+----------------------+
| id | cname | caddress |
+------+----------------------+
| 111 | zhang | HongKong |
| 1 | lili | Shanghai |
+------+----------------------+
2 rows in set (0.00 sec)

Delete data

In the following demonstration, you will guide you how to delete a single piece of data. It should be noted that when deleting a single record, you need to specify a primary key, otherwise batch deletion may be triggered. Create a new text file of gorm_delete.go and copy and paste the following code into the file:

package main

import (
    "fmt"
    "net/url"
    "strconv"

    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)

// user model
type USER struct {
    ID uint `gorm:"primaryKey"`
    CNAME string
    CADDRESS string
}

func getDBConn() *gorm.DB {
    username := "585b49fc_852b_4bd1_b6d1_d64bc1d8xxxx:admin:accountadmin" // modify this
    host := "freetier-01.cn-hangzhou.cluster.matrixonecloud.cn" // modify this
    password := "your_password" // modify this
    port := 6001
    database := "test"
    encodedUsername := url.QueryEscape(username)
    dsn := encodedUsername + ":" + password + "@tcp(" + host + ":" + strconv.Itoa(port) + ")/" + database
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
    // get connection
    if err != nil {
        fmt.Println("Database Connection Failed") //Connection failed
    } else {
        fmt.Println("Database Connection Succeed") //Connection succeeded
    }
    return db
}

func main() {
    //get *gorm.DB
    db := getDBConn()

    // auto create table
    db.AutoMigrate(&USER{})

    //**Delete**
    aUser := USER{}
    tx := db.Where("CNAME = ? ", "zhang").Find(&USER{}).Scan(&aUser)
    if tx.Error != nil {
        fmt.Println(tx.Error)
        Return
    }
    res := db.Delete(&aUser)
    if res.Error != nil {
        fmt.Println(tx.Error)
        Return
    }

}

Open the terminal and run this go file with the following code:

go run gorm_delete.go

You can use the MySQL client to verify that the table is deleted successfully:

mysql> select * from users;
+------+----------------------+
| id | cname | caddress |
+------+----------------------+
| 1 | lili | Shanghai |
+------+----------------------+
1 row in set (0.00 sec)

The above is only a partial demonstration of CRUD operations in GORM. For more usage and cases, please refer to GORM Official Guide