Connect to OceanBase Database by using GORM

This topic describes how to use GORM and OceanBase Database to build an application for basic database operations such as creating tables, inserting data, and querying data.

Download the gorm-oceanbase sample project

Prerequisites

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

• 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 steps:

1. Install Go.

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

      Note

      This topic uses the go1.20.6.windows-amd64.msi package.

   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 Windows, 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 Linux or macOS, add the following content to the ~/.bashrc or ~/.bash_profile file:

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

      Note

      \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 shell to check the version of Go:

      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 shell in the project directory. For more information about Go-SQL-Driver/MySQL, visit GitHub.

   Run the following commands to install Go-SQL-Driver/MySQL:

   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 the version or network, you can run the go install command instead.

   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 to install Go-SQL-Driver/MySQL.

      go install mysql
      

      Notice

      For some Go versions, the default execution directory for the go install command may not be /src. You can determine the actual directory based on the error returned after you run the go install command. For example, if the error message cannot find package "mysql" in: /usr/local/go/src/vendor/mysql is returned, you must first place the mysql folder under the /src/vendor directory and then run the 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 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 in to OceanBase Database by using the database connection string, to verify that the parameters are correct.

The parameters are described as follows:

• host: the IP address for connecting to OceanBase Database. It is the IP address of OceanBase Database Proxy (ODP) for connection through ODP, or the IP address of an OBServer node for direct connection.
• user_name: the tenant account. For connection through ODP, two account formats are supported: username@tenant name#cluster name and cluster name:tenant name:username. For direct connection, the username@tenant name format is supported.
• password: the password of the account.
• 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 be accessed.

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 connection string 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 access port is 2881.
• The name of the schema to be accessed is test.
• The tenant account is root@mysql. root@mysql is a MySQL user tenant created in OceanBase Database, and test is the username of a user in the root@mysql tenant.
• The password is ******.

Here is the sample code:

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

Step 4: Run the go-oceanbase project

After the code is compiled, open a command shell 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 Linux or macOS, configure a temporary environment variable before you run go run.

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

If the following result 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

Click here to download the project code, which is a package named gorm-oceanbase. Decompress the package to obtain a folder named gorm-oceanbase. The directory structure is as follows:

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

The files and directories are described as follows:

• go.mod: the Go module file, which defines the module dependencies and module versions in the project.
• go.sum: the module management file for Go 1.11 and later. It records the module dependencies, module versions, and corresponding checksums in the project.
• test.go: the Go source code file that contains the 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 operate OceanBase Database in MySQL mode.
  • github.com/jinzhu/inflection: the string conversion library used to singularize or pluralize a normal or camel-case string.
  • github.com/jinzhu/now: the time processing library used to perform operations such as obtaining the current time, calculating the time difference, and formatting the time.
  • github.com/sirupsen/logrus: the log library used to record logs when the application 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 process text such as Unicode strings and format numbers.
  • gorm.io/driver/mysql: the GORM MySQL driver used to connect to and operate OceanBase Database in MySQL mode in GORM.
  • gorm.io/gorm: the GORM framework for simplifying database operations.

Here is the sample code:

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 log library used to record logs when the application is running.
• golang.org/x/text: the text processing library used to process text such as Unicode strings and format numbers.
• gorm.io/driver/mysql: the GORM MySQL driver used to connect to and operate OceanBase Database in MySQL mode in GORM.
• gorm.io/gorm: the GORM framework for simplifying database operations.

Here is the sample code:

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 OceanBase Database in MySQL mode and use the APIs provided by GORM to perform database operations. Code in the test.go file contains the following parts:

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

2. Define import packages.

   The import statement imports the following packages:

   • fmt: provides functions to format 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 OceanBase Database in MySQL mode.
   • gorm.io/gorm: maps Go structs 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.

   Here is the sample code:

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

3. Define the User struct.

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

   Here is the sample code:

   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.

   Here is the sample code:

   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.

      Here is the sample code:

      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 OceanBase Database in MySQL mode, including the username, password, host address, port number, database name, and character set. Call the gorm.Open function to connect to OceanBase Database in MySQL mode, 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 returned and the application exits.

      Here is the sample code:

      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 struct. 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 that the table is dropped when the program execution ends.
      2. Create a User struct instance 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.

      Here is the sample code:

      db.AutoMigrate(&User{})
      defer db.Migrator().DropTable("users")
      // Record the start time.
      start := time.Now()
      // Create a User struct 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 application execution time, call the logrus.WithFields function to create a logger with fields, and call the Debug function to output the logs.

   Here is the sample code:

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

Complete code

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.