Connect to OceanBase Database by using Go-SQL-Driver/MySQL

Download the go-oceanbase sample project

Prerequisites

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

• Install OceanBase Database.
• Install Go.
• Install Go-SQL-Driver/MySQL.

Procedure

1. (Optional) Install Go and Go-SQL-Driver/MySQL.
2. Obtain the connection information of OceanBase Database.
3. Modify the database connection information in the go-oceanbase project.
4. Run the go-oceanbase project.

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

If you have installed Go and Go-SQL-Driver/MySQL, skip this step. If you have not installed them, perform the following steps:

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

     ```shell
     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. Enter the following command in the command shell to check the version of Go:

      C:\Users\admin\> go version
      go version go1.20.6 windows/amd64
      

2. Install Go-SQL-Driver/MySQL.

   You can install Go-SQL-Driver/MySQL by using different methods based on the version of Go. To install Go-SQL-Driver/MySQL, you must open a command shell in the project directory. For more information about Go-SQL-Driver/MySQL, visit GitHub.

   The 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 the version or network, you can run the go install command instead.

   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 
      

      Notice

      You must replace /usr/local/go/src with the actual installation directory of Go.

   2. Run the go install command to install Go-SQL-Driver/MySQL.

      go install mysql
      

      Notice

      For some Go versions, the default execution directory for the go install command may not be /src. You can determine the actual directory based on the error returned after you run the go install command. For example, if the error message cannot find package "mysql" in: /usr/local/go/src/vendor/mysql is returned, you must first place the mysql folder under the /src/vendor directory and then run the command.

   3. Check whether Go-SQL-Driver/MySQL 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 the deployment personnel or administrator of OceanBase Database to obtain the connection string.

obclient  -h{host} -u{username} -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.

Parameters in the connection string are described as follows:

• host: the IP address for connecting to OceanBase Database. For connection through OceanBase Database Proxy (ODP), use the IP address of an ODP. For direct connection, use the IP address of an OBServer node.
• user_name: the tenant account. For connection through ODP, two account formats are supported: username@tenant name#cluster name and cluster name:tenant name:username. For direct connection, the username@tenant name format is supported.
• password: the password of the account.
• port: the port for connecting to OceanBase Database. For connection through ODP, the default value is 2883, which can be customized when ODP is deployed. For direct connection, the default value is 2881, which can be customized when OceanBase Database is deployed.
• schema_name: the name of the schema to be accessed.

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

Modify the database connection information in the test.go file under the sample project directory based on the connection string 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 10.10.10.1.
• The access port is 2881.
• The name of the schema to be accessed is test.
• The tenant account is root@mysql. root@mysql is a MySQL user tenant created in OceanBase Database, and test is the username of a user in the root@mysql tenant.
• The password is ******.

Here is the sample code:

conn := "root@mysql:******@tcp(10.10.10.1:2881)/test"

Step 4: Run the go-oceanbase project

After the code is compiled, open a command shell in the project directory and enter the go run command to run the Go file.

C:\Users\admin\Desktop\go-oceanbase>go run test.go

(Optional) In a Linux or macOS environment, you must configure a temporary environment variable before you run go run.

export PATH=$PATH:/usr/local/go/bin
go run test.go

If the following result is returned, you have connected to OceanBase Database, and the sample project runs properly.

C:\Users\admin\Desktop\go-oceanbase>go run test.go
success to connect OceanBase with go_mysql driver
Hello OceanBase

Project code introduction

Click here to download the project code, which is a package named go-oceanbase.

Decompress the package to obtain a folder named go-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, which defines the module dependencies and module versions in the project.
• go.sum: the module management file for Go 1.11 and later. It records the module dependencies, module versions, and corresponding checksums in the project.
• test.go: the Go source code file that contains the 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 go-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 github.com/go-sql-driver/mysql v1.7.1 // indirect: the dependency declaration of the project. It declares that the project depends on the github.com/go-sql-driver/mysql module of version 1.7.1, and that the dependency is an indirect one associated with the go.sum dependency.

Code in go.sum

The go.sum file defines the version of the github.com/go-sql-driver/mysql dependency to ensure that the project uses the correct version.

Code in the go.sum file contains the following parts:

• github.com/go-sql-driver/mysql v1.7.1 h1:lUIinVbN1DY0xBg0eMOzmmtGoHwWBbvnWubQUrtU8EI= provides the hash value of the source code file of the module, which ensures that the correct version is used when the project is built. The hash value here is lUIinVbN1DY0xBg0eMOzmmtGoHwWBbvnWubQUrtU8EI=.
• github.com/go-sql-driver/mysql v1.7.1/go.mod h1:OXbVy3sEdcQ2Doequ6Z5BW6fXNQTmx+9S1MCJN5yJMI= provides the hash value of the dependency file of the module, which ensures that the correct dependency version is used when the project is built. The hash value here is OXbVy3sEdcQ2Doequ6Z5BW6fXNQTmx+9S1MCJN5yJMI=.

Code in test.go

The test.go file defines how to use Go to interact with OceanBase Database in MySQL mode, including operations such as connecting to the database, creating tables, inserting data, querying data, and dropping tables. To configure the test.go file, perform the following steps:

1. Define the main package. package main indicates that this package is an executable application package that contains a main() function. The function is executed when the application runs.

2. Define import packages.

   The import statement imports the following four packages:

   • database/sql: provides common SQL database access interfaces. It defines a set of common interfaces and functions for connecting to and operating various types of SQL databases.
   • fmt: provides functions to format input and output. It defines a set of functions for formatting data into strings and outputting them to the console or other devices.
   • log: provides logging functions. It defines a set of functions for outputting logs to the console or other devices.
   • github.com/go-sql-driver/mysql: provides a driver in MySQL mode. It implements the interfaces defined in the database/sql package for connecting to and operating OceanBase Database in MySQL mode by using Go. You need to specify the correct installation path of Go-SQL-Driver/MySQL here.

   Here is the sample code:

   import (
   "database/sql"
   "fmt"
   "log"
   
   _ "github.com/go-sql-driver/mysql"
   // Specify the installation path of Go-SQL-Driver/MySQL.
   )
   

3. Define the Str struct.

   The struct contains the Name field for storing query results. In the struct, the main() function is defined, which contains the selectAll() function for creating tables, inserting data, querying data, and dropping tables.

   Here is the sample code:

   type Str struct {
   Name string
   }
   
   func main() {
   selectAll()
   }
   

4. Define the selectAll() function.

   The selectAll() function includes operations of connecting to the database, creating tables, inserting data, querying data, and dropping tables.

   1. Connect to the database.

      1. Define the connection string conn, which contains the connection parameters for the MySQL mode of OceanBase Database, including the username, password, IP address, port number, and database name.
      2. Call the sql.Open() function to open the database connection. If an error occurs, log the error and exit the program.
      3. Use the defer keyword to defer the closure of the database connection, ensuring that the connection is closed after the function is executed.

      Here is the sample code:

      conn := "user_name:******@tcp(host:port)/schema_name"
      // Database connection parameters
      
      db, err := sql.Open("mysql", conn)
      if err != nil {
          log.Fatal(err)
      }
      

   2. Print messages to the console.

      1. Use the defer keyword to defer the execution of the db.Close() function, which ensures that the database connection is closed after the function is executed.
      2. Use the fmt.Printf() function to output a successful connection message to the console.

   The sample code is as follows:

    ```go
    defer db.Close()
   
    if err != nil {
        log.Fatal(err)
    }
   
    fmt.Printf("success to connect OceanBase with go_mysql driver\n")
    ```
   

   3. Create a table.

      Create a table named t1 that contains the str field of the varchar(256) type.

      Here is the sample code:

          _, err = db.Query("create table t1(str varchar(256))")
      if err != nil {
          log.Fatal(err)
      }
      

   4. Insert data.

   Insert a row where the str value is Hello OceanBase into the t1 table.

    Here is the sample code:
   
    ```go
    _, err = db.Query("insert into t1 values ('Hello OceanBase')")
    if err != nil {
        log.Fatal(err)
    }
    ```
   

   5. Query data.

      Query all data in the t1 table and assign the query result to the res variable. An error message is returned if the query fails.

      Here is the sample code:

      res, err := db.Query("SELECT * FROM t1")
      if err != nil {
          log.Fatal(err)
      }
      defer res.Close()
      
      for res.Next() {
          var str Str
          res.Scan(&str.Name)
          fmt.Printf("%s\n", str.Name)
      }
      

   6. Drop the table.

      Drop the t1 table. An error message is returned if the operation fails.

      Here is the sample code:

      _, err = db.Query("drop table t1")
      if err != nil {
          log.Fatal(err)
      }
      

Complete code

module go-oceanbase
go 1.20
require github.com/go-sql-driver/mysql v1.7.1 // indirect

github.com/go-sql-driver/mysql v1.7.1 h1:lUIinVbN1DY0xBg0eMOzmmtGoHwWBbvnWubQUrtU8EI=
github.com/go-sql-driver/mysql v1.7.1/go.mod h1:OXbVy3sEdcQ2Doequ6Z5BW6fXNQTmx+9S1MCJN5yJMI=

    package main

    import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/go-sql-driver/mysql"
    // Specify the installation path of Go-SQL-Driver/MySQL.
    )

    type Str struct {
    Name string
    }

    func main() {
    selectAll()
    }

    func selectAll() {
    conn := "user_name:******@tcp(host:port)/schema_name"
    // Database connection parameters

    db, err := sql.Open("mysql", conn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    if err != nil {
        log.Fatal(err)
    }

    fmt.Printf("success to connect OceanBase with go_mysql driver\n")
    // Create a table named t1.
    _, err = db.Query("create table t1(str varchar(256))")
    if err != nil {
        log.Fatal(err)
    }

    // Insert data.
    _, err = db.Query("insert into t1 values ('Hello OceanBase')")
    if err != nil {
        log.Fatal(err)
    }

    // Query data.
    res, err := db.Query("SELECT * FROM t1")
    if err != nil {
        log.Fatal(err)
    }
    defer res.Close()

    for res.Next() {
        var str Str
        res.Scan(&str.Name)
        fmt.Printf("%s\n", str.Name)
    }

    // Drop the t1 table.
    _, err = db.Query("drop table t1")
    if err != nil {
        log.Fatal(err)
    }
    }

References

For more information about Go-SQL-Driver/MySQL, see Go-SQL-Driver/MySQL.