Lindorm:Use the GORM library to develop applications

Prerequisites

• Go is installed. We recommend that you install Go 1.17 or later versions. For more information about how to install Go, see Download and install.

• The LindormTable version is 2.6.1 or later. For more information about how to view or upgrade the version of LindormTable, see Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance.

• The MySQL compatibility feature is enabled for the instance. For more information, see Enable the MySQL compatibility feature.

• The IP address of your client is added to the whitelist of your Lindorm instance. For more information, see Configure whitelists.

Procedure

1. In the go.mode file of your Go project, add the dependency of GORM.

   require (
       gorm.io/driver/mysql v1.5.1
       gorm.io/gorm v1.25.4
   )

2. Configure connection parameters.

   dsn := "<user>:<password>@tcp(<lidnorm_mysql_url>:33060)/<database>"

   Parameters

   Parameter	Description
   user	If you forget your password, you can change the password in the cluster management system of LindormTable. For more information, see Manage users.
   password
   lindorm_mysql_url	The LindormTable endpoint for MySQL. For more information about how to obtain the endpoint, see View endpoints. Important If your application is deployed on an ECS instance, we recommend that you use a VPC to connect to the Lindorm instance to ensure higher security and lower network latency. If your application is deployed on a local server and needs to connect to the Lindorm instance over the Internet, you can perform the following steps to enable the Internet endpoint for the instance in the Lindorm console: In the left-side navigation pane, select Database Connections > Wide Table Engine. On the Wide Table Engine tab, click Enable Public Endpoint. If you use a VPC to access the Lindorm instance, specify the LindormTable VPC endpoint for MySQL in the value of lindorm_mysql_url. If you use the Internet to access the Lindorm instance, specify the LindormTable Internet endpoint for MySQL in the value of lindorm_mysql_url.
   database	The name of the database to which you want to connect. By default, your client is connected to a database named default.

3. Establish a connection and use LindormTable SQL to perform operations in LindormTable. The following code block provides an example on how to use LindormTable SQL to create a table:

   db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
   if err != nil {
       panic(err)
   }
   // Notice: Disable transactions because LindormTable does not support transactions.
   session := db.Session(&gorm.Session{SkipDefaultTransaction: true})
   
   // Create a table named Product.
   err = session.Migrator().CreateTable(&Product{})
   if err != nil {
       panic(err)
   }

Sample code

The following code provides a complete example on how to connect to and use LindormTable by using the GORM library:

package main

import (
    "fmt"
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)

// Define the mapping relationship between the fields in the table with corresponding elements in GORM.
type Product struct {
    // Specify the ID column as the primary key. Set auto increment to false because auto increment is not supported by LindormTable.
    ID    int64  `gorm:"primaryKey;autoIncrement:false"`
    // Map the string data types to varchar in GORM. Otherwise, the longtext data type is not supported in the column.
    Code  string `gorm:"type:varchar"`
    Price float64
}

func main() {
    // Set user to the username used to access LindormTable.
    // Set password to the password used to access LindormTable.
    // Set lindorm_mysql_url to the LindormTable endpoint for MySQL.
    // Set database to the database to which you want to connect.
    dsn := "user:test@tcp(ld-uf6k8yqb741t3****-proxy-sql-lindorm.lindorm.rds.aliyuncs.com:33060)/default"
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
    if err != nil {
        panic(err)
    }
    // Notice: Disable transactions because LindormTable does not support transactions.
    session := db.Session(&gorm.Session{SkipDefaultTransaction: true})

    // Creates a table.
    err = session.Migrator().CreateTable(&Product{})
    if err != nil {
        panic(err)
    }

    // Write a row of data whose value in the ID column is 1 to the table.
    tx := session.Debug().Create(&Product{ID: 1, Code: "D42", Price: 100.1})
    err = tx.Error
    if err != nil {
        panic(err)
    }

    // Write a row of data whose value in the ID column is 2 to the table.
    tx = session.Debug().Create(&Product{ID: 2, Code: "B41", Price: 105.5})
    err = tx.Error
    if err != nil {
        panic(err)
    }

    var product1 Product
    // Query data whose value in the ID column is 1.
    session.Debug().First(&product1, 1) // Query data whose value of the primary key is 1.
    fmt.Println(product1)

    var product2 Product
    // Query data whose value in the ID column is 2.
    session.Debug().First(&product2, "code = ?", "B41") // Query data whose value in the Code column is B41.
    fmt.Println(product2)


    // In data whose value in the ID column is 1, update the value in the Price column to 101.8.
    session.Debug().Model(&Product{}).Where("id = ?", 1).Update("price", 101.8)
    product1 = Product{}
    
    // Query data whose primary key value is 1.
    session.Debug().First(&product1, 1)
    fmt.Println(product1)

    // Delete the values in the data whose primary key value is 1.
    session.Delete(&Product{}, 1)
    product1 = Product{}
    session.Debug().First(&product1, 1)
    fmt.Println(product1)
}

After the preceding code is run, logs that contain the following query results are returned:

{1 D42 100.1}
{2 B41 105.5}
{1 D42 101.8}
{0  0}