This topic describes how to build an application using GORM and OceanBase AI Database to perform basic operations such as creating tables, inserting data, and querying data.
Download the gorm-oceanbase sample project Prerequisites
Install OceanBase AI Database, the Go language, and related drivers, 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.
- Install OceanBase AI Database.
- Install the Go language.
- Install the Go-SQL-Driver/MySQL driver.
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) Install the Go language and its driver.
- Obtain the connection information for OceanBase AI Database.
- Modify the database connection information in the
gorm-oceanbaseproject. - Run the
gorm-oceanbaseproject.
Step 1: (Optional) Install the Go language and its driver
If you have already installed the Go language and the Go-SQL-Driver/MySQL driver, skip this step. If not, follow these steps to install them.
Install the Go language
Download the Go installation package: You can download the appropriate version 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 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, 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 during Go installation, 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 and verify whether the installation is successful:
C:\Users\admin\> go version go version go1.20.6 windows/amd64
Install the Go-SQL-Driver/MySQL driver
You can choose different installation methods depending on the version of the Go language. When installing the Go-SQL-Driver/MySQL driver, navigate to the corresponding project directory and open a command-line terminal. For more information about
Go-SQL-Driver/MySQL, visit 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.1If you cannot install it using the
go getcommand due to version or network issues, you can use thego installcommand to installgo-sql-driver/mysql.Clone the
go-sql-driver/mysqlrepository from GitHub into thego/srcdirectory.cd /usr/local/go/src git clone https://github.com/go-sql-driver/mysql.gitNote
/usr/local/go/srcmust be replaced with the actual installation directory of Go.Install it using
go install.go install mysqlNote
For some versions, the default execution directory of
go installmay not be/src. You can determine the actual directory from the error message after executinggo install. For example, if the errorcannot find package "mysql" in: /usr/local/go/src/vendor/mysqloccurs, place the mysql folder in the/src/vendordirectory before running the installation command.Check whether the Go-SQL-Driver/MySQL driver has been installed. If the installation fails, modify the configuration according to the error message.
go list -m github.com/go-sql-driver/mysql
Step 2: Obtain OceanBase AI Database connection information
Contact the OceanBase AI Database deployment personnel or administrator to obtain the corresponding database connection information.
obclient -h{host} -u{user_name} -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.go file.
Parameter description:
host: The IP address for connecting to OceanBase AI Database. For connections through ODP, this is an ODP address; for direct connections, it is the IP address of an OBServer node.user_name: The connection account for the tenant. For connections through ODP, the common formats areusername@tenant_name#cluster_nameorcluster_name:tenant_name:username. For direct connections, the format isusername@tenant_name.password: The account password.port: The port for connecting to OceanBase AI Database. For connections through ODP, the default port is2883, which can be customized during ODP deployment. For direct connections, the default port is2881, which can be customized during OceanBase AI Database deployment.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 OceanBase AI Database connection information. Select the test.go file, right-click, and choose Open With to open it with Notepad or another editing software.

Example:
- The IP address of the OBServer node is
10.10.10.1. - The access port is 2881.
- The name of the schema to access is
test. - The connection account for the tenant is
root@mysql.root@mysqlis a user tenant created in OceanBase AI Database, andtestis the username for theroot@mysqltenant. - 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 editing the code, open a command-line terminal in the project directory and run the Go file directly with 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 output is returned after running, the database connection is successful and the sample statement was 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
Project code
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
The files and directories are described as follows:
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.
Code in 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 handling 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.
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 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.
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 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
importstatement 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 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 structs 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.
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
Userstruct.Defines a struct named
Userto represent a user's basic information. It contains four fields: the user's unique identifierID, the user's nameName, the user's ageAge, and the user's birthdayBirthday.The sample code is as follows:
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 the conversion, it returns the original string. Finally, the function returns 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
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.The sample code is as follows:
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 the error message and exit the program.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 }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 the table 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 for the user with ID 1 and save it to the database.
- Delete the user with ID 1 and output the deletion result.
The sample code is as follows:
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.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 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.