Connect to OceanBase Database by using Go-MySQL-Driver

Download the go-oceanbase sample project

Prerequisites

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

• Install OceanBase Database

• Install Go

• Install Go-MySQL-Driver

Procedure

1. (Optional) Install Go and Go-MySQL-Driver.

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-MySQL-Driver

If you have installed Go and Go-MySQL-Driver, skip this step. If not, perform the following operations:

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:

        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. Run the following command in a command-line terminal to check the version of Go, thus verifying the installation:

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

2. Install Go-MySQL-Driver.

   You can install Go-MySQL-Driver by using different methods based on the version of Go. To install Go-MySQL-Driver, you must open a command-line terminal in the project directory. For more information about Go-MySQL-Driver, see go-sql-driver/mysql on GitHub.

   The installation 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 version or network issues, you can run the go install command to install go-sql-driver/mysql.

   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 

      Important

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

   2. Run the go install command.

      go install mysql

      Important

      For some Go versions, the default running directory for the go install command may not be /src. You can determine the correct directory based on the error reported after you run the go install command. For example, if the cannot find package "mysql" in: /usr/local/go/src/vendor/mysql error is reported, you must put the mysql folder in the /src/vendor directory and then run the command again.

   3. Check whether Go-MySQL-Driver 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 a deployment engineer or administrator of OceanBase Database to obtain the database 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.

The parameters are described as follows:

• host: the IP address for connecting to OceanBase Database.

• user_name: the username of the tenant account.

• password: the password of the tenant account.

• port: the port for connecting to OceanBase Database. The default port is 3306.

• schema_name: the name of the schema to access.

Step 3: Modify the database connection information in the go-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 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 xxx.xxx.xxx.xxx.

• The port is 3306.

• The name of the schema to access is test.

• The username of the tenant account is root.

• The password of the tenant account is ******.

The sample connection string is as follows:

conn := "root:******@tcp(xxx.xxx.xxx.xxx:3306)/test"

Step 4: Run the go-oceanbase project

After you modify the code as needed, open a command-line terminal 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 can run go run.

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

If the following information 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 go-oceanbase to download the project code, which is a compressed file named go-oceanbase.

After decompressing it, you will find 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 that defines the dependencies of the project on other modules and versions of the modules.

• go.sum: the module management file for Go 1.11 and later. It records the dependencies of the project on other modules and versions of the modules, as well as the corresponding checksums.

• test.go: the Go source code file that contains 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 dependency 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 the MySQL mode of OceanBase Database, 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 program package that contains a main() function. The function is executed when the program 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 the 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 the driver in MySQL mode. It implements the interfaces defined in the database/sql package for connecting to and operating the MySQL mode of OceanBase Database by using Go. You need to specify the correct installation path of go-sql-driver/mysql here.

   The sample code is as follows:

   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, define the main() function, which contains the selectAll() function for creating tables, inserting data, querying data, and dropping tables.

   The sample code is as follows:

   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. 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. Call the sql.Open() function to open the database connection. If an error occurs, log the error and exit the program. Use the defer keyword to defer the closure of the database connection, which ensures that the connection is closed after the function is executed.

      The sample code is as follows:

      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. 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. Use the fmt.Printf() function to output a successful connection message to the console.

      The sample code is as follows:

      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.

      The sample code is as follows:

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

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

      The sample code is as follows:

      _, 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 variable res. Return an error message if the query fails.

      The sample code is as follows:

      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 a table. Drop the t1 table. Return an error message if the operation fails.

      The sample code is as follows:

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

Complete code examples

go.mod

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

go.sum

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=

test.go

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"
    // Set the 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-MySQL-Driver, see go-sql-driver/mysql on GitHub.