Connect to OceanBase Database by using beego ORM|OceanBase Database|docs|OceanBase Documentations

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

Obtain the connection string of OceanBase Database.
Install beego ORM and the MySQL driver.
Write the main.go file to implement database operations.
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.
$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.

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:

Install Go.

Update the system packages and install the necessary dependencies:
```
sudo apt update
sudo apt install -y wget
```

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

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

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

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

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

Run the program:

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

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.

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

Prerequisites

Procedure

Step 1: Obtain the connection string of OceanBase Database

Notice

Step 2: Install the Go programming language and beego ORM

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

References