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

This topic introduces how to build an application by using Go-SQL-Driver/MySQL and OceanBase Database. It also covers the use of the application for fundamental database operations, including table creation, data insertion, and data query.

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-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 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. Enter the following command in the 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-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-line terminal in the project directory. For more information about Go-SQL-Driver/MySQL, see 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 version or network issues, you can run the go install command for installation.

   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.

      go install mysql
      

      Notice

      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 following error is reported: cannot find package "mysql" in: /usr/local/go/src/vendor/mysql, you must put the mysql folder in the /src/vendor directory and then run the go install 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 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.

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), this parameter is the IP address of an ODP. For direct connection, this parameter is the IP address of an OBServer node.
• user_name: the tenant account. For connection through ODP, the tenant account can be in the username@tenant name#cluster name or cluster name:tenant name:username format. For direct connection, the tenant account is in the username@tenant name format.
• password: the account password.
• 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 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 10.10.10.1.
• The port is 2881.
• The name of the schema to access is test.
• The tenant account is root@mysql. root@mysql is a user tenant created in MySQL-compatible mode of OceanBase Database.
• The password is ******.

The sample code is as follows:

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-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 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 project's dependencies on other modules and versions of the modules.
• go.sum: the module management file for Go 1.11 and later. It records the project's dependencies 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-compatible 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 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-compatible mode. It implements the interfaces defined in the database/sql package for connecting to and operating the MySQL-compatible 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 structure.

   The structure contains the Name field for storing query results. In the structure, 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.

      1. Define the connection string conn, which contains the connection parameters for the MySQL-compatible 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.

      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.

      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:

      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

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 table 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 table t1.
    _, 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.