Connect to OceanBase Database by using GORM

This topic introduces how to build an application by using GORM and OceanBase Database. It also covers the use of the application for fundamental database operations, including table creation, data insertion, data updating, and data query.

Download the gorm-oceanbase sample project

Prerequisites

You have completed the following preparations and correctly configured the corresponding environment variable:

• Install OceanBase Database
• Install Go
• Install Go-SQL-Driver/MySQL

Procedure

1. (Optional) Install Go and Go-SQL-Driver/MySQL.
2. Obtain the connection information of OceanBase Database.
3. Modify the database connection information in the gorm-oceanbase project.
4. Run the gorm-oceanbase project.

Step 1: (Optional) Install Go and Go-SQL-Driver/MySQL

If you have installed Go and Go-SQL-Driver/MySQL, skip this step. If you have not installed them, perform the following operations:

1. Install Go.

   1. Download the Go installation package that suits your operating system from the Go official website.

      Note

      The installation package used in this topic is go1.20.6.windows-amd64.msi.

   2. Double-click the installation package and follow the wizard to install Go.

   3. Add the installation path of Go to the Path environment variable of the system.

      • In a Windows environment, choose Control Panel > System and Security > System > Advanced system settings > Environment Variables > System variables and add C:\usr\local\go\bin to the value of Path.

      • In a Linux or macOS environment, add the following content in the ~/.bashrc or ~/.bash_profile file:

        export PATH=$PATH:/usr/local/go/bin
        

        Note

        Here, \usr\local\go\bin is the default installation directory of Go. If you select another directory when you install Go, replace the default directory with the actual one.

   4. Enter the following command in the command-line terminal to check the version of Go, thus verifying the installation:

      C:\Users\admin\> go version
      go version go1.20.6 windows/amd64
      

2. Install Go-SQL-Driver/MySQL.

   You can install Go-SQL-Driver/MySQL by using different methods based on the version of Go. To install Go-SQL-Driver/MySQL, you must open a command-line terminal in the project directory. For more information about Go-SQL-Driver/MySQL, see GitHub.

   The commands are as follows:

   C:\Users\admin\Desktop\go-oceanbase>go get -u github.com/go-sql-driver/mysql
   go: downloading github.com/go-sql-driver/mysql v1.7.1
   go: added github.com/go-sql-driver/mysql v1.7.1
   

   If you cannot use the go get command due to version or network issues, you can run the go install command for installation.

   1. Clone the go-sql-driver/mysql repository from GitHub to the go/src directory.

      cd /usr/local/go/src   
      git clone https://github.com/go-sql-driver/mysql.git
      

      Notice

      You must replace /usr/local/go/src with the actual installation directory of Go.

   2. Run the go install command.

      go install mysql
      

      Notice

      For some Go versions, the default running directory for the go install command may not be /src. You can determine the correct directory based on the error reported after you run the go install command. For example, if the following error is reported: cannot find package "mysql" in: /usr/local/go/src/vendor/mysql, you must put the mysql folder in the /src/vendor directory and then run the go install command.

   3. Check whether Go-SQL-Driver/MySQL has been installed. If the installation fails, make corrections based on the error message.

      go list -m github.com/go-sql-driver/mysql
      

Step 2: Obtain the connection information of OceanBase Database

Contact the deployment personnel or administrator of OceanBase Database to obtain the database connection information.

obclient  -h{host} -u{user_name} -p****** -P{port} -D{schema_name}

The database connection string contains parameters required for accessing OceanBase Database. You can log on to OceanBase Database by using the database connection string, to verify that the parameters are correct.

Parameters in the connection string are described as follows:

• host: the IP address for connecting to OceanBase Database. For connection through OceanBase Database Proxy (ODP), this parameter is the IP address of an ODP. For direct connection, this parameter is the IP address of an OBServer node.
• user_name: the tenant account. For connection through ODP, the tenant account can be in the username@tenant name#cluster name or cluster name:tenant name:username format. For direct connection, the tenant account is in the username@tenant name format.
• password: the account password.
• port: the port for connecting to OceanBase Database. For connection through ODP, the default value is 2883, which can be customized when ODP is deployed. For direct connection, the default value is 2881, which can be customized when OceanBase Database is deployed.
• schema_name: the name of the schema to access.

Step 3: Modify the database connection information in the gorm-oceanbase project

Modify the database connection information in the test.go file based on the information obtained in Step 2: Obtain the connection information of OceanBase Database. Select and right-click the test.go file, choose Open With, and select Notepad or another editor to open the file.

Here is an example:

• The IP address of the OBServer node is 10.10.10.1.
• The port is 2881.
• The name of the schema to access is test.
• The tenant account is root@mysql. root@mysql is a user tenant created in MySQL mode of OceanBase Database.
• The password is ******.

The sample code is as follows:

dsn := "root@mysql:******@tcp(10.10.10.1:2881)/test?charset=utf8mb4&parseTime=True&loc=Local"

Step 4: Run the gorm-oceanbase project

After the code is compiled, open a command-line terminal in the project directory and enter the go run command to run the Go file.

PS D:\demo\go-demo\gorm-oceanbase> go run test.go

(Optional) In a Linux or macOS environment, you must configure a temporary environment variable before you run go run.

export PATH=$PATH:/usr/local/go/bin
go run test.go

If the following information is returned, you have connected to OceanBase Database, and the sample project runs properly.

PS D:\demo\go-demo\gorm-oceanbase> go run test.go
1
<nil>
1
{1 OceanBase 12 2022-06-01 08:00:00 +0800 CST}
<nil>
1
{1 ob 13 2023-06-01 00:00:00 +0000 UTC}
<nil>
1
1
<nil>
1
time="2023-08-09T15:55:46+08:00" level=debug msg=DropTable duration=589.2031ms

2023/08/09 15:55:47 D:/demo/go-demo/gorm-oceanbase/test.go:85 SLOW SQL >= 200ms
[336.194ms] [rows:0] DROP TABLE IF EXISTS `users` CASCADE

Project code introduction

Click gorm-oceanbase to download the project code, which is a compressed file named gorm-oceanbase.

After decompressing it, you will find a folder named gorm-oceanbase. The directory structure is as follows:

|-- go.mod
|-- go.sum
|-- test.go

Here is a breakdown of the files and directories:

• go.mod: the Go module file that defines the project’s dependencies on other modules and versions of the modules.
• go.sum: the module management file for Go 1.11 and later. It records the project’s dependencies on other modules and versions of the modules, as well as the corresponding checksums.
• test.go: the Go source code file that contains sample code for the project.

Code in go.mod

The go.mod file defines the module name, Go version, and dependencies of the project.

Code in the go.mod file contains the following parts:

• module gorm-oceanbase: the name of the module, which defines the project namespace. In Go 1.16 and later, the module name must match the name of the root directory of the project.

• go 1.20: the Go version required for the project.

• require: the dependency declaration for the project. It specifies the third-party libraries that the project depends on and their versions. The following dependencies are indirect ones and are associated with the go.sum dependency.

  • github.com/go-sql-driver/mysql: the Go-SQL-Driver/MySQL library used to connect to and operating the MySQL mode of OceanBase Database.
  • github.com/jinzhu/inflection: the string conversion library used to convert strings to singular form, plural form, or camel case.
  • github.com/jinzhu/now: the time processing library used to obtain the current time, calculate the time difference, format the time, and so on.
  • github.com/sirupsen/logrus: the logger library used to record logs when the program is running.
  • golang.org/x/sys: the system library that provides some system-level operation functions and constants.
  • golang.org/x/text: the text processing library used to handle Unicode strings, format numbers, and so on.
  • gorm.io/driver/mysql: the GORM MySQL driver used to connect to and operate the MySQL mode of OceanBase Database in GORM.
  • gorm.io/gorm: the GORM framework for simplifying database operations.

The sample code is as follows:

module gorm-oceanbase

go 1.20

require (
 github.com/go-sql-driver/mysql v1.7.1 // indirect
 github.com/jinzhu/inflection v1.0.0 // indirect
 github.com/jinzhu/now v1.1.5 // indirect
 github.com/sirupsen/logrus v1.9.3 // indirect
 golang.org/x/sys v0.5.0 // indirect
 golang.org/x/text v0.12.0 // indirect
 gorm.io/driver/mysql v1.5.1 // indirect
 gorm.io/gorm v1.25.2 // indirect
)

Code in go.sum

The go.sum file defines the dependencies for the project. Each dependency consists of three parts: the name of the library, the version number, and the hash value.

Code in the go.sum file contains the following parts:

• github.com/sirupsen/logrus: the logger library used to record logs when the program is running.
• golang.org/x/text: the text processing library used to handle Unicode strings, format numbers, and so on.
• gorm.io/driver/mysql: the GORM MySQL driver used to connect to and operate the MySQL mode of OceanBase Database in GORM.
• gorm.io/gorm: the GORM framework for simplifying database operations.

The sample code is as follows:

github.com/sirupsen/logrus v1.9.3 h1:dueUQJ1C2q9oE3F7wvmSGAaVtTmUizReu6fjN8uqzbQ=
github.com/sirupsen/logrus v1.9.3/go.mod h1:naHLuLoDiP4jHNo9R0sCBMtWGeIprob74mVsIT4qYEQ=
golang.org/x/text v0.12.0 h1:k+n5B8goJNdU7hSvEtMUz3d1Q6D/XW4COJSJR6fN0mc=
golang.org/x/text v0.12.0/go.mod h1:TvPlkZtksWOMsz7fbANvkp4WM8x/WCo/om8BMLbz+aE=
gorm.io/driver/mysql v1.5.1 h1:WUEH5VF9obL/lTtzjmML/5e6VfFR/788coz2uaVCAZw=
gorm.io/driver/mysql v1.5.1/go.mod h1:Jo3Xu7mMhCyj8dlrb3WoCaRd1FhsVh+yMXb1jUInf5o=
gorm.io/gorm v1.25.2 h1:gs1o6Vsa+oVKG/a9ElL3XgyGfghFfkKA2SInQaCyMho=
gorm.io/gorm v1.25.2/go.mod h1:L4uxeKpfBml98NYqVqwAdmV1a2nBtAec/cf3fpucW/k=

Code in test.go

The test.go file defines how to use Go-SQL-Driver/MySQL to connect to the MySQL mode of OceanBase Database and use the APIs provided by GORM to perform database operations.

To configure the test.go file, perform the following steps:

1. Define the main package.

   package main indicates that this package is an executable program package that contains a main() function. The function is executed when the program runs.

2. Define import packages.

   The import statement imports the following packages:

   • fmt: provides functions for formatted input and output. It defines a set of functions for formatting data into strings and outputting them to the console or other devices.
   • time: provides some time-related functions and types.
   • os: provides some functions and types related to the operating system.
   • gorm.io/driver/mysql: the MySQL driver used to connect to and operate the MySQL mode of OceanBase Database.
   • gorm.io/gorm: maps Go structures to database tables and provides some methods for querying and operating the database.
   • golang.org/x/text/transform: provides some basic features of text processing, such as character set conversion and Unicode processing.
   • github.com/sirupsen/logrus: provides some log output and formatting features.

   The sample code is as follows:

   import (
     "fmt"
     "time"
     "os"
     "gorm.io/driver/mysql"
     "gorm.io/gorm"
     "golang.org/x/text/transform"
     "github.com/sirupsen/logrus"
   )
   

3. Define the User structure.

   The User structure specifies the basic information of a user. It contains four fields: ID, Name, Age, and Birthday.

   The sample code is as follows:

   type User struct {
   Id         int
   Name     string
   Age      int
   Birthday time.Time
   
   }
   

4. Define the transformString function.

   The transformString function converts a string to a specified encoding format. It accepts two parameters: str and encoder, and calls the transform.String function to convert the string to the specified encoding format. If an error occurs during the conversion, the function returns the original string. That is, the return result of the function may be the converted string or the original string.

   The sample code is as follows:

   func transformString(str string, encoder transform.Transformer) string {
       result, _, err := transform.String(encoder, str)
       if err != nil {
           return str
       }
       return result
   }
   

5. Define the main function.

   The main function is called to add, delete, modify, and query the created user information and output the corresponding debugging log to the console by using logrus.

   1. Use logrus for initialization.

      Use the logrus library to initialize the log output. To be specific, set the log output format to text and the log level to Debug, and output the logs to the standard output stream.

      The sample code is as follows:

      logrus.SetFormatter(&logrus.TextFormatter{})
      logrus.SetLevel(logrus.DebugLevel)
      logrus.SetOutput(os.Stdout)
      

   2. Connect to the database.

      Define a string variable named dsn, which contains the information required to connect to the MySQL mode of OceanBase Database, including the username, password, host address, port number, database name, character set, and so on. Call the gorm.Open function to connect to the MySQL mode of OceanBase Database, pass the dsn variable and a parameter of the gorm.Config type, and return a connection object. If a connection error occurs, an error message is output and the program exits.

      The sample code is as follows:

      dsn := "user_name:******@tcp(host:port)/schema_name?charset=utf8mb4&parseTime=True&loc=Local"
      db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
      if err != nil {
          fmt.Println(err.Error())
          return
      }
      

   3. Perform database operations.

      Use the gorm.DB object to perform database operations such as automatic migration, data insertion, data query, data update, and data deletion. The specific process is as follows:

      1. Call the db.AutoMigrate function to automatically migrate the table corresponding to the User structure. If the table does not exist, create the table. Use the defer keyword and the db.Migrator().DropTable function to defer dropping the users table, so the table is dropped when the program execution ends.
      2. Create an instance of the User structure named user and insert it into the database.
      3. Query the user whose ID is 1 and output the query result.
      4. Update the information for the user whose ID is 1 and save it to the database.
      5. Delete the user whose ID is 1 and output the deletion result.

      The sample code is as follows:

      db.AutoMigrate(&User{})
      defer db.Migrator().DropTable("users")
      // Record the start time.
      start := time.Now()
      // Create a User structure instance named user and insert it into the database.  
      user := User{Name: "OceanBase", Age: 12, Birthday: time.Date(2022, 06, 01, 00, 00, 00, 00, time.UTC)}
      result := db.Create(&user)
      fmt.Println(user.ID)
      fmt.Println(result.Error)
      fmt.Println(result.RowsAffected)
      // Query the user whose ID is 1 and output the query result.
      user = User{ID: 1}
      result = db.First(&user)
      fmt.Println(user)
      fmt.Println(result.Error)
      fmt.Println(result.RowsAffected)
      // Update the information for the user whose ID is 1 and save it to the database.
      user = User{ID: 1, Name: "ob", Age: 13, Birthday: time.Date(2023, 06, 01, 00, 00, 00, 00, time.UTC)}
      result = db.Save(&user)
      fmt.Println(user)
      fmt.Println(result.Error)
      fmt.Println(result.RowsAffected)
      // Delete the user whose ID is 1 and output the deletion result.
      user = User{ID: 1}
      result = db.Delete(&user)
      fmt.Println(user.ID)
      fmt.Println(result.Error)
      fmt.Println(result.RowsAffected)
      

6. Output logs.

   Call the time.Since function to calculate the program execution time, call the logrus.WithFields function to create a logger with fields, and call the Debug function to output the logs.

   The sample code is as follows:

   logrus.WithFields(logrus.Fields{
       "duration": time.Since(start),
       }).Debug("DropTable")
   

Complete code examples

module gorm-oceanbase

go 1.20

require (
 github.com/go-sql-driver/mysql v1.7.1 // indirect
 github.com/jinzhu/inflection v1.0.0 // indirect
 github.com/jinzhu/now v1.1.5 // indirect
 github.com/sirupsen/logrus v1.9.3 // indirect
 golang.org/x/sys v0.5.0 // indirect
 golang.org/x/text v0.12.0 // indirect
 gorm.io/driver/mysql v1.5.1 // indirect
 gorm.io/gorm v1.25.2 // indirect
)

github.com/sirupsen/logrus v1.9.3 h1:dueUQJ1C2q9oE3F7wvmSGAaVtTmUizReu6fjN8uqzbQ=
github.com/sirupsen/logrus v1.9.3/go.mod h1:naHLuLoDiP4jHNo9R0sCBMtWGeIprob74mVsIT4qYEQ=
golang.org/x/text v0.12.0 h1:k+n5B8goJNdU7hSvEtMUz3d1Q6D/XW4COJSJR6fN0mc=
golang.org/x/text v0.12.0/go.mod h1:TvPlkZtksWOMsz7fbANvkp4WM8x/WCo/om8BMLbz+aE=
gorm.io/driver/mysql v1.5.1 h1:WUEH5VF9obL/lTtzjmML/5e6VfFR/788coz2uaVCAZw=
gorm.io/driver/mysql v1.5.1/go.mod h1:Jo3Xu7mMhCyj8dlrb3WoCaRd1FhsVh+yMXb1jUInf5o=
gorm.io/gorm v1.25.2 h1:gs1o6Vsa+oVKG/a9ElL3XgyGfghFfkKA2SInQaCyMho=
gorm.io/gorm v1.25.2/go.mod h1:L4uxeKpfBml98NYqVqwAdmV1a2nBtAec/cf3fpucW/k=

package main

import (
  "fmt"
  "time"
  "os"
  "gorm.io/driver/mysql"
  "gorm.io/gorm"
    "golang.org/x/text/transform"
    "github.com/sirupsen/logrus"
)

type User struct {
  Id         int
  Name     string
  Age      int
  Birthday time.Time

}

// Convert the string to the specified encoding format.
func transformString(str string, encoder transform.Transformer) string {
    result, _, err := transform.String(encoder, str)
    if err != nil {
        return str
    }
    return result
}

func main() {

      // Use logrus for initialization.
      logrus.SetFormatter(&logrus.TextFormatter{})
      logrus.SetLevel(logrus.DebugLevel)
      logrus.SetOutput(os.Stdout)


  dsn := "user_name:******@tcp(host:port)/schema_name?charset=utf8mb4&parseTime=True&loc=Local"

  db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
  if err != nil {
    fmt.Println(err.Error())
    return

  }

  db.AutoMigrate(&User{})
  defer db.Migrator().DropTable("users")

  // Record the start time.
  start := time.Now()

  user := User{Name: "OceanBase", Age: 12, Birthday: time.Date(2022, 06, 01, 00, 00, 00, 00, time.UTC)}
  result := db.Create(&user)
  fmt.Println(user.ID)
  fmt.Println(result.Error)
  fmt.Println(result.RowsAffected)

  user = User{ID: 1}
  result = db.First(&user)
  fmt.Println(user)
  fmt.Println(result.Error)
  fmt.Println(result.RowsAffected)

  user = User{ID: 1, Name: "ob", Age: 13, Birthday: time.Date(2023, 06, 01, 00, 00, 00, 00, time.UTC)}
  result = db.Save(&user)
  fmt.Println(user)
  fmt.Println(result.Error)
  fmt.Println(result.RowsAffected)

  user = User{ID: 1}
  result = db.Delete(&user)
  fmt.Println(user.ID)
  fmt.Println(result.Error)
  fmt.Println(result.RowsAffected)



  // Output logs.
  logrus.WithFields(logrus.Fields{
      "duration": time.Since(start),
    }).Debug("DropTable")

}

References

For more information about Go-SQL-Driver/MySQL, see Go-SQL-Driver/MySQL.