Connect to OceanBase Database by using beego ORM

beego ORM is a powerful ORM framework built for Go applications. Its main features include:

• Comprehensive ORM capabilities, including model definitions, CRUD operations, and relationships
• Support for popular databases like MySQL, PostgreSQL, and SQLite
• Advanced features such as automatic table creation and transaction management
• Seamless integration with the beego framework, and it also works as an independent library

This topic will show you how to use beego ORM to connect to OceanBase Database and perform basic operations like creating tables, inserting data, updating data, and querying data.

Prerequisites

• You have installed Go 1.13 or later.
• You have installed OceanBase Database and created a MySQL-compatible tenant.
• You have obtained the connection string of OceanBase Database.

Procedure

1. Obtain the connection string of OceanBase Database.
2. Install beego ORM and the MySQL driver.
3. Write the main.go file to implement database operations.
4. Run the program and verify the results.

Step 1: Obtain the connection string of OceanBase Database

Contact the deployment personnel or administrator of OceanBase Database to obtain the corresponding database connection string.

obclient -h$host -P$port -u$user_name -p$password -D$database_name

Parameters:

• $host: the IP address for connecting to OceanBase Database. For connection through OceanBase Database Proxy (ODP), use the ODP IP address. For direct connection, use the IP address of an OBServer node.

• $port: the port for connecting to OceanBase Database. For connection through ODP, the default value is 2883, which can be customized during ODP deployment. For direct connection, the default value is 2881, which can be customized during OceanBase Database deployment.

• $database_name: the name of the database to be accessed.

  Notice

  The user used to connect to the tenant must have the CREATE, INSERT, UPDATE, and SELECT privileges on the database. For more information about user privileges, see Privilege types in MySQL-compatible mode.

• $user_name: the username for connecting to the tenant. For connection through ODP, the format is username@tenant name#cluster name or cluster name:tenant name:username. For direct connection, the format is username@tenant name.

• $password: the password for the account.

For more information about the connection string, see Connect to an OceanBase tenant by using OBClient.

Here is an example:

obclient -hxxx.xxx.xxx.xxx -P2881 -utest_user001@mysql001 -p****** -Dtest

Step 2: Install the Go programming language and beego ORM

If you already have Go and beego ORM installed, you can skip this step. Otherwise, follow these instructions:

1. Install Go.

   1. Update the system packages and install the necessary dependencies:

      sudo apt update
      sudo apt install -y wget
      

   2. Download and install Go:

      # Download the latest version of Go (visit https://golang.org/dl/ for the latest version)
      wget https://dl.google.com/go/go1.20.6.linux-amd64.tar.gz
      
      # Extract it to the /usr/local directory
      sudo tar -C /usr/local -xzf go1.20.6.linux-amd64.tar.gz
      
      # Delete the installation package
      rm go1.20.6.linux-amd64.tar.gz
      

   3. Configure the environment variables:

      # Edit the ~/.bashrc file
      echo 'export PATH=$PATH:/usr/local/go/bin' >> ~/.bashrc
      echo 'export GOPATH=$HOME/go' >> ~/.bashrc
      echo 'export PATH=$PATH:$GOPATH/bin' >> ~/.bashrc
      
      # Apply the configuration
      source ~/.bashrc
      

   4. Verify the installation:

      go version
      

      If the installation is successful, you will see output similar to the following:

      go version go1.20.6 linux/amd64
      

2. Install beego ORM and the MySQL driver.

   1. Create a working directory and initialize the Go module:

      mkdir -p ~/go/src/beego-oceanbase
      cd ~/go/src/beego-oceanbase
      go mod init beego-oceanbase
      

   2. Install beego ORM and the MySQL driver:

      go get -u github.com/beego/beego/v2
      go get -u github.com/go-sql-driver/mysql
      

   3. (Optional) If direct installation fails due to network issues, set up a Go proxy:

      go env -w GOPROXY=https://goproxy.cn,direct
      

      Then, repeat the installation commands.

3. Verify the installation:

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

   If the installation is successful, you will see the corresponding version information.

Step 3: Create the main.go file to implement database operations

1. Create a file named main.go and add the following code:

   package main
   
   import (
       "fmt"
       "time"
       "github.com/beego/beego/v2/client/orm"
       _ "github.com/go-sql-driver/mysql"
       "github.com/beego/beego/v2/core/logs"
   )
   
   // User defines the user model
   type User struct {
       Id       int       `orm:"auto;pk"`
       Username string    `orm:"size(100)"`
       Email    string    `orm:"size(100);unique"`
       Age      int       `orm:"null"`
       Created  time.Time `orm:"auto_now_add;type(datetime)"`
       Updated  time.Time `orm:"auto_now;type(datetime);null"`
   }
   
   // TableName specifies the table name
   func (u *User) TableName() string {
       return "users"
   }
   
   func init() {
       // Initialize logging
       logs.Reset()
       logs.EnableFuncCallDepth(true)
       logs.SetLogFuncCallDepth(3)
   
       // Register the MySQL driver
       err := orm.RegisterDriver("mysql", orm.DRMySQL)
       if err != nil {
           logs.Error("Failed to register MySQL driver:", err)
           return
       }
   
       // Register the database
       dataSource := "root@mysql001:**********@tcp(xxx.xxx.xxx.xxx:2881)/db_test?charset=utf8mb4&parseTime=true&loc=Local"
       err = orm.RegisterDataBase("default", "mysql", dataSource)
       if err != nil {
           logs.Error("Failed to register database:", err)
           return
       }
   
       // Configure connection pool
       db, err := orm.GetDB("default")
       if err != nil {
           logs.Error("Failed to get database connection:", err)
           return
       }
       db.SetMaxIdleConns(10)
       db.SetMaxOpenConns(30)
       db.SetConnMaxLifetime(5 * time.Minute)
   
       // Register the model
       orm.RegisterModel(new(User))
   
       // Automatically create tables if they don't exist
       err = orm.RunSyncdb("default", false, true)
       if err != nil {
           logs.Error("Failed to automatically create tables:", err)
           return
       }
   
       logs.Info("Database initialization successful")
   }
   
   func main() {
       // Get the ORM object
       o := orm.NewOrm()
   
   // Insert data
   user := &User{
       Username: "oceanbase_user",
       Email:    "******@oceanbase.com",
       Age:      28,
   }
   
   logs.Info("Starting to insert data...")
   id, err := o.Insert(user)
   if err != nil {
       logs.Error("Failed to insert data:", err)
       return
   }
   logs.Info(fmt.Sprintf("Data inserted successfully, ID: %d", id))
   
   // Query data
   readUser := &User{Id: int(id)}
   err = o.Read(readUser)
   if err != nil {
       logs.Error("Failed to query data:", err)
       return
   }
   logs.Info(fmt.Sprintf("Data queried successfully: %+v", readUser))
   
   // Update data
   readUser.Age = 29
   readUser.Updated = time.Now()
   
   _, err = o.Update(readUser)
   if err != nil {
       logs.Error("Failed to update data:", err)
       return
   }
   logs.Info("Data updated successfully")
   
   // Query all data
   var users []*User
   _, err = o.QueryTable("users").All(&users)
   if err != nil {
       logs.Error("Failed to query all data:", err)
       return
   }
   logs.Info(fmt.Sprintf("Current number of users: %d", len(users)))
   
   // Delete data
   _, err = o.Delete(readUser)
   if err != nil {
       logs.Error("Failed to delete data:", err)
       return
   }
   logs.Info("Data deleted successfully")
   }
   
   

### Step 4: Run the program and verify the results

1. Run the program:

   cd ~/go/src/beego-oceanbase
   go run main.go
   

2. If you encounter the go: command not found error, make sure that you have correctly set the Go environment variables as described in step 2.

3. After the program has executed, you will see output similar to the following:

   create table `users`
   -- --------------------------------------------------
   --  Table Structure for `main.User`
   -- --------------------------------------------------
   CREATE TABLE IF NOT EXISTS `users` (
       `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
       `username` varchar(100) NOT NULL DEFAULT '' ,
       `email` varchar(100) NOT NULL DEFAULT ''  UNIQUE,
       `age` integer,
       `created` datetime NOT NULL,
       `updated` datetime
   ) ENGINE=INNODB;
   
   2025/06/10 11:08:42.497 [I] [main.go:67]  Database initialization successful
   2025/06/10 11:08:42.497 [I] [main.go:81]  Starting data insertion...
   2025/06/10 11:08:42.544 [I] [main.go:87]  Data insertion successful, ID: 1
   2025/06/10 11:08:42.550 [I] [main.go:96]  Data query successful: &{Id:1 Username:oceanbase_user Email:******@oceanbase.com Age:28 Created:2025-06-10 11:08:42 +0800 CST Updated:2025-06-10 11:08:42 +0800 CST}
   2025/06/10 11:08:42.553 [I] [main.go:107]  Data update successful
   2025/06/10 11:08:42.554 [I] [main.go:116]  Current number of users: 1
   2025/06/10 11:08:42.556 [I] [main.go:124]  Data deletion successful
   

References

• beego ORM Documentation
• OceanBase Database Documentation
• Go language Documentation
• MySQL Driver Documentation