Connect to OceanBase Database by using GORM - ApsaraDB for OceanBase

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

Download the gorm-oceanbase project

Prerequisites

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

Note

The tool used to run the sample code in this topic is IntelliJ IDEA 2021.3.2 (Community Edition). You can also choose a tool that suits your personal preference to run the code.

Install OceanBase Database
Install Go
Install Go-MySQL-Driver

Procedure

Note

The steps provided in this topic are for Windows environments. If you are using a different operating system or compiler, the steps may vary slightly.

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

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

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

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. Run the following command in a 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
```
Install Go-MySQL-Driver.
You can install Go-MySQL-Driver by using different methods based on the version of Go. To install Go-MySQL-Driver, you must open a command-line terminal in the project directory. For more information about Go-MySQL-Driver, see go-sql-driver/mysql on GitHub.
The installation 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 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 
```
  Important
  You must replace /usr/local/go/src with the actual installation directory of Go.
2. Run the go install command.
```
go install mysql
```
  Important
  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 cannot find package "mysql" in: /usr/local/go/src/vendor/mysql error is reported, you must put the mysql folder in the /src/vendor directory and then run the command again.
3. Check whether Go-MySQL-Driver 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 a deployment engineer or administrator of OceanBase Database to obtain the database connection string.

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.

Note

The URL obtained here is required in the test.go file.

The parameters are described as follows:

host: the IP address for connecting to OceanBase Database.
user_name: the username of the tenant account.
password: the password of the tenant account.
port: the port for connecting to OceanBase Database. The default port is 3306 in MySQL mode.
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 xxx.xxx.xxx.xxx.
The port is 3306.
The name of the schema to access is test.
The username of the tenant account is root.
The password of the tenant account is ******.

The sample connection string is as follows:

dsn := "root:******@tcp(xxx.xxx.xxx.xxx:3306)/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 can 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 dependencies of the project on other modules and versions of the modules.
go.sum: the module management file for Go 1.11 and later. It records the dependencies of the project 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 of 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-MySQL-Driver library used to connect to and operate 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 perform operations such as obtaining the current time, calculating the time difference, and formatting the time.
- 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 perform operations such as handling Unicode strings and formatting numbers.
- 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 perform operations such as handling Unicode strings and formatting numbers.
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.

Note

The dependencies listed in the go.sum file vary with the running environment. Download the required dependencies as prompted.

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-MySQL-Driver 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:

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.
Define import packages.
The import statement imports the following packages:
- fmt: provides functions to format the 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 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.
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"
)
```
Define the User struct.
The User struct 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

}
```
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
}
```
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, and character set. 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 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 the table is dropped when the program execution ends.
  2. Create an instance of the User struct 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 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)
```
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

go.mod

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
)

go.sum

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=

test.go

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-MySQL-Driver, see go-sql-driver/mysql on GitHub.

Prerequisites

Procedure

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

Step 2: Obtain the connection information of OceanBase Database

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

Step 4: Run the gorm-oceanbase project

Project code introduction

Code in go.mod

Code in go.sum

Code in test.go

Complete code examples

References

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

Step 4: Run the `gorm-oceanbase` project