This topic describes how to build an application using GORM and OceanBase Cloud to perform basic operations such as creating tables, inserting data, and querying data.
Prerequisites
Create an OceanBase Cloud instance, set up Go, and ensure that environment variables are correctly configured.
Note
The code in this document was written using IntelliJ IDEA 2021.3.2 (Community Edition). You can also choose a tool that suits your personal preference to view the sample code.
- You have registered an OceanBase Cloud account and created a cluster instance and a tenant in MySQL mode. For details, see Create a cluster instance and Create a tenant.
- Install the Go language.
- Download the sample project dependencies.
Procedure
Note
The steps provided in this document are generated for a Windows environment. If you are using a different operating system or compiler, the steps may vary slightly.
- (Optional) Set up Go and the project dependencies.
- Obtain the OceanBase Cloud connection information.
- Modify the database connection information in the
gorm-oceanbaseproject. - Run the
gorm-oceanbaseproject.
Step 1: (Optional) Set up Go and the project dependencies
If Go is already installed, skip ahead to downloading the sample project dependencies.
Install the Go language
Download the Go language installation package: You can download the appropriate package for your operating system from the Go official website.
Note
The Go installation package used in this document is named go1.20.6.windows-amd64.msi.
Install the Go language: Double-click the downloaded installation package and follow the prompts to install it.
Configure environment variables: Add the Go installation path to the system's PATH environment variable.
In a Windows environment, you can add
C:\usr\local\go\binto the Path value in Control Panel > System and Security > System > Advanced system settings > Environment Variables > System Variables.In a Linux or macOS environment, you can edit the
~/.bashrcor~/.bash_profilefile and add the following content:export PATH=$PATH:/usr/local/go/bin
Note
\usr\local\go\binis the default installation directory. If you modified the installation directory when installing Go, replace it with the corresponding directory.Verify the installation: Enter the following command in the Shell command line to view the version information of the Go language, so as to verify whether the installation is successful:
C:\Users\admin\> go version go version go1.20.6 windows/amd64
Download the sample project dependencies.
Extract the sample project and run the following command in the directory that contains
go.mod. Go downloads the dependencies declared by the project, including the MySQL driver.go mod download
Step 2: Obtain the OceanBase Cloud database connection information
Log in to the OceanBase Cloud console. On the instance list page, expand the target instance's information. Under the target tenant, select Connect > Get connection string.
For more detailed operations, see Get a connection string.
Fill in the corresponding information in the URL below based on your 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 connection string to log in to the database and verify that the parameter information is correct.
Note
The URL information here is required in the
test.gofile.Parameter description:
host: The connection address of OceanBase Cloud, for example,t********.********.oceanbase.cloud.user_name: The account used to access the database.password: The password of the account.port: The port for connecting to OceanBase Cloud. 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 OceanBase Cloud. Select the test.go file, right-click it, and choose Open with to open it with Notepad or other editing software.
Example:
- The database access endpoint 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
******.
Sample code:
dsn := "mysql001:******@tcp(t********.********.oceanbase.cloud:3306)/test?charset=utf8mb4&parseTime=True&loc=Local"
Step 4: Run the gorm-oceanbase project
After editing the code, open a command-line terminal in the project directory and run the Go file directly using go run:
PS D:\demo\go-demo\gorm-oceanbase> go run test.go
(Optional) In Linux or macOS environments, you must configure temporary environment variables before running go run.
export PATH=$PATH:/usr/local/go/bin
go run test.go
If the following content is returned after running, the database connection is successful and the sample statement is executed correctly:
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
Sample project overview
Click gorm-oceanbase to download the project code, which is a compressed file named gorm-oceanbase. After decompressing it, you will get 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 project's module dependencies and version information.go.sum: A module management file introduced in Go V1.11 and later. It records the modules and their versions that the project depends on, along with their corresponding checksums.test.go: A Go source code file containing sample code for the project.
Introduction to go.mod
The go.mod file is used to define the project's module name, Go version number, and dependency declarations.
The go.mod file contains the following content:
module gorm-oceanbase: This is the project's module name, which defines its namespace. In Go 1.16 and later, the module name must match the name of the project's root directory.go 1.20: This is the Go version required by the project.require: This is the project's dependency declaration. It lists the third-party libraries the project depends on and their version information. This dependency is an indirect dependency, associated with another dependency calledgo.sum.github.com/go-sql-driver/mysql: The Go-SQL-Driver/MySQL driver, used to connect to and operate MySQL databases.github.com/jinzhu/inflection: A string conversion library, used to convert strings into singular form, plural form, camelCase, etc.github.com/jinzhu/now: A time processing library, used to get the current time, calculate time differences, format time, etc.github.com/sirupsen/logrus: A logging library, used to record runtime log information for programs.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 handle Unicode strings, format numbers, etc.gorm.io/driver/mysql: The MySQL driver for GORM, used to connect to and operate MySQL databases in GORM.gorm.io/gorm: The GORM ORM framework, used to simplify database operations.
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 go.sum
The go.sum file is used to define the project's dependency information. 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 runtime log information for programs.golang.org/x/text: A text processing library, used to handle Unicode strings, format numbers, etc.gorm.io/driver/mysql: The MySQL driver for GORM, used to connect to and operate MySQL databases in GORM.gorm.io/gorm: The GORM ORM framework, used to simplify database operations.
Note
The dependencies required by the go.sum file vary depending on the runtime environment. Please download the necessary dependencies as prompted.
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 test.go
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 APIs provided by GORM. The test.go file contains the following sections:
Define the
mainpackage.package mainindicates that this is a package for an executable program. This package contains amain()function, which is executed when the program runs.Define the
importstatements.The
importstatements import the following packages:fmt: Provides functions for formatted input and output. It defines a set of functions to format data into strings and output them to the console or other devices.time: Provides time-related functions and types.os: Provides operating system-related functions and types.gorm.io/driver/mysql: A MySQL database driver for connecting to and operating MySQL databases.gorm.io/gorm: Maps Go language structures to database tables and provides methods for querying and operating the database.golang.org/x/text/transform: Provides basic text processing functions, such as charset conversion and Unicode handling.github.com/sirupsen/logrus: Provides logging output and formatting features.
Sample code:
import ( "fmt" "time" "os" "gorm.io/driver/mysql" "gorm.io/gorm" "golang.org/x/text/transform" "github.com/sirupsen/logrus" )Define the
Userstructure.Defines a structure named
Userto represent basic user information. It contains four fields: the user's unique identifierID, the user's nameName, the user's ageAge, and the user's birthdayBirthday.Sample code:
type User struct { ID int Name string Age int Birthday time.Time }Define the
transformStringfunction. Defines a function namedtransformStringto convert a string to a specified encoding format. It accepts two parameters:strandencoder. The function callstransform.Stringto convert the string to the specified encoding format. If an error occurs during conversion, it returns the original string. Finally, the function returns the converted string or the original string.Sample code:
func transformString(str string, encoder transform.Transformer) string { result, _, err := transform.String(encoder, str) if err != nil { return str } return result }Define the
mainfunction.By calling the
mainfunction, operations such as creating, reading, updating, and deleting user information are performed on the created user information, andlogrusis used to output corresponding debug logs to the console.Initialize
logrus.Use the
logruspackage to initialize log output, set the log output format to text, the log level toDebug, and output to the standard output stream.Code:
logrus.SetFormatter(&logrus.TextFormatter{}) logrus.SetLevel(logrus.DebugLevel) logrus.SetOutput(os.Stdout)Connect to the database.
Define a string variable named
dsn, which contains the information required to connect to a MySQL database, including the username, password, host address, port number, database name, character set, etc. Call thegorm.Openfunction to connect to the MySQL database, passing in thedsnvariable and a parameter of typegorm.Config, and return a connection object. If an error occurs during the connection, output an error message and exit 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 }Database operations.
Use a
gorm.DBobject to perform database operations, including automatic migration, inserting data, querying data, updating data, and deleting data. The specific process is as follows:- Call the
db.AutoMigratefunction to automatically migrate the table corresponding to theUserstruct. If the table does not exist, create it. Use thedeferkeyword and thedb.Migrator().DropTablefunction to defer the deletion of theuserstable, meaning it will be deleted when the program exits. - Create a
Userstruct instance nameduserand insert it into the database. - Query the user with ID 1 and output the query result.
- Update the information of the user with ID 1 and save it to the database.
- Delete the user with ID 1 and output the deletion result.
Code:
db.AutoMigrate(&User{}) defer db.Migrator().DropTable("users") // Record Start Time start := time.Now() // Create a User 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)- Call the
Output logs.
Call the
time.Sincefunction to calculate the program runtime, call thelogrus.WithFieldsfunction to create a logger with fields, and call theDebugfunction to output log information.Code:
logrus.WithFields(logrus.Fields{ "duration": time.Since(start), }).Debug("DropTable")
Complete 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
)
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 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 Log
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.