Use the GORM library to develop applications

Prerequisites

Before you begin, ensure that you have:

• Go 1.17 or later installed. See Download and install

• LindormTable 2.6.1 or later. See Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance

• The MySQL compatibility feature enabled on your instance. See Enable the MySQL compatibility feature

• Your client IP address added to the instance whitelist. See Configure whitelists

Add dependencies

In your project's go.mod file, add the GORM MySQL driver and core library:

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

Connect to LindormTable

LindormTable supports two network paths. Choose based on where your application runs:

To enable the Internet endpoint, go to the Lindorm console, select Database Connections > Wide Table Engine, and click Enable Public Endpoint on the Wide Table Engine tab.

To get your endpoint, see View endpoints.

Build the connection string and open a GORM session:

package main

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

func connectDB() *gorm.DB {
    // Format: <user>:<password>@tcp(<lindorm_mysql_url>:33060)/<database>
    // Replace the placeholders with your actual credentials and endpoint.
    // The default database name is "default".
    dsn := "<user>:<password>@tcp(<lindorm_mysql_url>:33060)/<database>"

    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
    if err != nil {
        panic(err)
    }

    // LindormTable does not support transactions.
    // Always create sessions with SkipDefaultTransaction set to true.
    session := db.Session(&gorm.Session{SkipDefaultTransaction: true})
    return session
}

Define a model

GORM maps Go structs to database tables. LindormTable has two constraints that affect how you define your model:

• No auto increment: Set autoIncrement:false on primary key fields.

• No longtext: Map string fields to varchar explicitly; otherwise GORM defaults to longtext, which LindormTable does not support.

type Product struct {
    ID    int64   `gorm:"primaryKey;autoIncrement:false"`
    Code  string  `gorm:"type:varchar"`
    Price float64
}

Run CRUD operations

The examples below use the session returned by connectDB(). All examples use session.Debug() to log the generated SQL.

err := session.Migrator().CreateTable(&Product{})
if err != nil {
    panic(err)
}

// Insert a row with ID 1
tx := session.Debug().Create(&Product{ID: 1, Code: "D42", Price: 100.1})
if tx.Error != nil {
    panic(tx.Error)
}

// Insert a row with ID 2
tx = session.Debug().Create(&Product{ID: 2, Code: "B41", Price: 105.5})
if tx.Error != nil {
    panic(tx.Error)
}

// Query by primary key
var product1 Product
session.Debug().First(&product1, 1)

// Query by a non-primary-key column
var product2 Product
session.Debug().First(&product2, "code = ?", "B41")

// Update the Price column for the row where ID = 1
session.Debug().Model(&Product{}).Where("id = ?", 1).Update("price", 101.8)

// Delete the row where the primary key is 1
session.Delete(&Product{}, 1)

Complete example

The following code puts all the steps together:

package main

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

// Product maps to the "products" table in LindormTable.
// autoIncrement:false — LindormTable does not support auto increment.
// type:varchar — LindormTable does not support longtext, which is GORM's default for string.
type Product struct {
    ID    int64   `gorm:"primaryKey;autoIncrement:false"`
    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)
    }

    // LindormTable does not support transactions.
    session := db.Session(&gorm.Session{SkipDefaultTransaction: true})

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

    // Insert two rows.
    tx := session.Debug().Create(&Product{ID: 1, Code: "D42", Price: 100.1})
    if tx.Error != nil {
        panic(tx.Error)
    }
    tx = session.Debug().Create(&Product{ID: 2, Code: "B41", Price: 105.5})
    if tx.Error != nil {
        panic(tx.Error)
    }

    // Query by primary key.
    var product1 Product
    session.Debug().First(&product1, 1)
    fmt.Println(product1)

    // Query by column value.
    var product2 Product
    session.Debug().First(&product2, "code = ?", "B41")
    fmt.Println(product2)

    // Update the Price column for ID = 1.
    session.Debug().Model(&Product{}).Where("id = ?", 1).Update("price", 101.8)
    product1 = Product{}
    session.Debug().First(&product1, 1)
    fmt.Println(product1)

    // Delete the row where the primary key is 1.
    session.Delete(&Product{}, 1)
    product1 = Product{}
    session.Debug().First(&product1, 1)
    fmt.Println(product1)
}

Expected output:

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