Connect to OceanBase Cloud using GORM

This topic describes how to build an application by using GORM and OceanBase Cloud. The application can perform basic operations such as creating tables, inserting data, and querying data.

Download the gorm-oceanbase sample project Connect to OceanBase Cloud using GORM

Prerequisites

You have created an instance of OceanBase Cloud, installed Go, and configured the environment variables.

• You have registered an OceanBase Cloud account and created an instance and a MySQL compatible tenant. For more information, see Create an instance and Create a tenant.
• Go is installed.
• Go-SQL-Driver/MySQL is installed.

Procedure

1. (Optional) Install Go and the driver.
2. Obtain the connection information of OceanBase Cloud.
3. Modify the database connection information in the gorm-oceanbase project.
4. Run the gorm-oceanbase project.

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

If you have already installed Go and the Go-SQL-Driver/MySQL driver, you can skip this step. Otherwise, follow the steps below to install them.

1. Install Go

   1. Download the Go installation package: You can download the installation package suitable for your operating system from the Go official website.

      Note

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

   2. Install Go: Double-click the downloaded installation package and follow the prompts to install Go.

   3. Configure the environment variables: Add the Go installation path to the system's PATH environment variable.

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

      • In a Linux or macOS environment, you can edit the ~/.bashrc or ~/.bash_profile file and add the following content:

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

      Note

      \usr\local\go\bin is the default installation directory. If you changed the installation directory when installing Go, replace it with the corresponding directory.

   4. Verify the installation: Enter the following command in the Shell command line to view the Go version information and verify whether the installation was successful:

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

2. Install the Go-SQL-Driver/MySQL driver

   Based on the version of Go, you can choose different installation methods. When installing the Go-SQL-Driver/MySQL driver, you need to enter the corresponding project directory and open the command-line terminal. For more information about Go-SQL-Driver/MySQL, see Github.

   The installation command is 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 install the driver by using the go get command due to version or network issues, you can use the go install command to install go-sql-driver/mysql.

   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

      /usr/local/go/src needs to be replaced with the actual Go installation directory.

   2. Install the driver by using the go install command.

      go install mysql
      

      Notice

      In some versions, the default execution directory of go install may not be /src. You can determine the actual directory based on the error message generated after executing go install. For example, if the error message is cannot find package "mysql" in: /usr/local/go/src/vendor/mysql, you need to place the mysql folder in the /src/vendor directory before executing the installation command.

   3. Check whether the Go-SQL-Driver/MySQL driver is installed. If the installation fails, modify the settings based on the error message.

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

Step 2: Obtain the connection information of the OceanBase Cloud database

1. Log in to the OceanBase Cloud console. In the instance list, expand the information of the target instance, and in the target tenant, choose Connect > Get Connection String.

   For more information, see Obtain a connection string.

2. Fill in the corresponding information in the URL based on the created OceanBase Cloud database.

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

   The database connection string contains the parameters required to access the database. You can use the database connection string to verify the login and ensure that the parameters in the connection string are correct.

   Note

   The URL information is required in the test.go file.

   Parameter description:

   • host: the endpoint of the OceanBase Cloud database, for example, t********.********.oceanbase.cloud.
   • user_name: the account for accessing the database.
   • password: the password of the account.
   • port: the port of the OceanBase Cloud database. The default value is 3306.
   • 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 information obtained in Step 2: Obtain the connection information of the OceanBase Cloud database. Right-click the test.go file and select Open With to open it in Notepad or another editing tool.

Here is an example:

• The database access address is t********.********.oceanbase.cloud.
• The access port is 3306.
• The name of the schema to be accessed is test.
• The tenant connection account is mysql001.
• The password is ******.

Here is the sample code:

dsn := "mysql001:******@tcp(t********.********.oceanbase.cloud:3306)/test?charset=utf8mb4&parseTime=True&loc=Local"

Step 4: Run the go-oceanbase project

After editing the code, open a command-line terminal in the project directory and run the following command:

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

(Recommended for Linux and macOS) You need to configure the temporary environment variable before running go run.

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

If the following content is returned, the database connection is successful and the sample statement is correctly executed:

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 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

File description:

• go.mod: a Go module file that defines the module dependencies and version information of the project.
• go.sum: a new module management file added in Go V1.11 and later, which records the module and version information of the project dependencies, as well as the corresponding checksum (Checksum).
• test.go: a Go source code file that contains the sample code of the project.

Introduction to the go.mod file

The go.mod file is used to define the module name, Go version, and dependency declarations for the project.

The go.mod file contains the following content:

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

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

• require: This is the dependency declaration for the project. It lists the third-party libraries and their version information that the project depends on. This dependency is an indirect dependency and is associated with another dependency go.sum.

  • github.com/go-sql-driver/mysql: The Go-SQL-Driver/MySQL driver, used to connect to and operate on a MySQL database.
  • github.com/jinzhu/inflection: A string conversion library used to convert strings to singular, plural, camel case, etc.
  • github.com/jinzhu/now: A time processing library used to obtain the current time, calculate time differences, and format time.
  • github.com/sirupsen/logrus: A logging library used to record log information during program execution.
  • golang.org/x/sys: A system library that provides some system-level operation functions and constants.
  • golang.org/x/text: A text processing library used to process Unicode strings and format numbers.
  • gorm.io/driver/mysql: The MySQL driver for GORM, used to connect to and operate on a MySQL database in GORM.
  • gorm.io/gorm: The GORM ORM framework, used to simplify database operations.

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
)

Introduction to the go.sum file

The go.sum file is used to define the dependency information for the project. Each dependency consists of three parts: the library name, version number, and hash value.

The go.sum file contains the following content:

• github.com/sirupsen/logrus: A logging library used to record log information during program execution.
• golang.org/x/text: A text processing library used to process Unicode strings and format numbers.
• gorm.io/driver/mysql: The MySQL driver for GORM, used to connect to and operate on a MySQL database in GORM.
• gorm.io/gorm: The GORM ORM framework, used to simplify database operations.

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=

Introduction to the test.go file

The test.go file defines how to use the Go-SQL-Driver/MySQL driver to connect to a MySQL database and perform database operations using the APIs provided by GORM. The test.go file contains the following content:

1. Define the main package. package main indicates that this is a package for an executable program. This package contains a main() function, which will be executed when the program runs.

2. Define the import package.

   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 operating system-related functions and types.
   • gorm.io/driver/mysql: the MySQL database driver, used to connect to and operate on a MySQL database.
   • gorm.io/gorm: maps Go language structs to database tables and provides some query and database operation methods.
   • golang.org/x/text/transform: provides some basic text processing features, such as character set conversion and Unicode processing.
   • github.com/sirupsen/logrus: provides some log output and formatting features.

   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.

   Defines a User struct to represent user information, including four fields: ID (the user's unique identifier), Name (the user's name), Age (the user's age), and Birthday (the user's birthday).

   Code:

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

4. Define the transformString function. Defines a transformString function to convert a string to a specified encoding format. It accepts two parameters: str and encoder. The function uses transform.String to convert the string to the specified encoding format. If an error occurs during the conversion, it returns the original string. Finally, it returns the converted string or the original string.

   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 performs create, read, update, and delete operations on the user information and uses logrus to output the corresponding debug logs to the console.

   1. Initialize logrus.

      Initializes the logrus package, sets the log output format to text, the log level to Debug, and outputs to the standard output stream.

      Code:

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

   2. Connect to the database.

      Defines a string variable named dsn that contains the information required to connect to the MySQL database, including the username, password, host address, port number, database name, and character set. Calls the gorm.Open function to connect to the MySQL database, passing in the dsn variable and a gorm.Config parameter, and returns a connection object. If an error occurs during the connection, it outputs the error message and exits the program.

      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.

      Uses the gorm.DB object to perform database operations, including automatic migration, inserting data, querying data, updating data, and deleting data. The specific process is as follows:

      1. Calls the db.AutoMigrate function to automatically migrate the table corresponding to the User struct. If the table does not exist, it creates the table. Uses the defer keyword and the db.Migrator().DropTable function to delay the deletion of the users table, which will be deleted when the program ends.
      2. Creates a User struct instance named user and inserts it into the database.
      3. Queries the user with ID 1 and outputs the query result.
      4. Updates the information of the user with ID 1 and saves it to the database.
      5. Deletes the user with ID 1 and outputs the deletion result.

      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 with ID 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 of the user with ID 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 with ID 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.

   Calls the time.Since function to calculate the program runtime, calls the logrus.WithFields function to create a logger with fields, and calls the Debug function to output the log information.

   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 a 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() {

      // Initialize logrus.
      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 in the OceanBase Database open-source community.