Connect to OceanBase Cloud using the Go-SQL-Driver/MySQL driver

This topic describes how to build an application by using the Go-SQL-Driver/MySQL driver and OceanBase Cloud to perform basic operations such as creating a table, inserting data, and querying data.

Download the go-oceanbase sample project Connect to OceanBase Cloud using the Go-SQL-Driver/MySQL driver

Prerequisites

You have created an instance of OceanBase Cloud, installed Go, and configured the environment variables.

• You have registered an OceanBase Cloud account and created an instance and a MySQL-compatible tenant. For more information, see Create an instance and Create a tenant.
• You have installed Go.
• You have installed the Go-SQL-Driver/MySQL driver.

Procedure

1. (Optional) Install Go and the driver.
2. Obtain the connection information of OceanBase Cloud.
3. Modify the database connection information in the go-oceanbase project.
4. Run the go-oceanbase project.

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

If you have already installed Go and the Go-SQL-Driver/MySQL driver, you can skip this step. If not, follow the steps below.

1. Install Go

   1. Download the Go installation package: You can download the installation package for your operating system from Go's official website.

      Note

      The Go installation package used in this topic is named go1.20.6.windows-amd64.msi.

   2. Install Go: Double-click the downloaded installation package and follow the prompts to install Go.

   3. Configure the environment variables: Add the Go installation path to the system's PATH environment variable.

      • In a Windows environment, you can add the Path value to C:\usr\local\go\bin in Control Panel > System and Security > System > Advanced System Settings > Environment Variables > System Variables.

      • In a Linux or macOS environment, you can edit the ~/.bashrc or ~/.bash_profile file and add the following content:

        export PATH=$PATH:/usr/local/go/bin
        

      Note

      \usr\local\go\bin is the default installation directory. If you changed the installation directory during Go installation, replace it with the corresponding directory.

   4. Verify the installation: In the Shell command line, enter the following command to check the Go version and verify the installation:

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

2. Install the Go-SQL-Driver/MySQL driver

   Depending on the Go version, you can choose different installation methods. When installing the Go-SQL-Driver/MySQL driver, you need to navigate to the corresponding project directory and open the command-line terminal. For more information about Go-SQL-Driver/MySQL, see Github.

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

   1. Clone the go-sql-driver/mysql repository from GitHub into the go/src directory.

      cd /usr/local/go/src   
      git clone https://github.com/go-sql-driver/mysql.git 
      

      Notice

      Replace /usr/local/go/src with the actual Go installation directory.

   2. Use the go install command to install the driver.

      go install mysql
      

      Notice

      In some versions, the default execution directory for go install may not be /src. You can determine the actual directory based on the error message after executing go install. For example, if the error message is cannot find package "mysql" in: /usr/local/go/src/vendor/mysql, you should place the mysql folder in the /src/vendor directory before executing the installation command.

   3. Check whether the Go-SQL-Driver/MySQL driver is installed. If the installation fails, modify the configuration based on the error message.

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

Step 2: Obtain the connection information of the OceanBase Cloud database

1. Log in to the OceanBase Cloud console. In the instance list, expand the information of the target instance, and in the target tenant, choose Connect > Get Connection String.

   For more information, see Get connection string.

2. Fill in the corresponding information in the URL based on the created OceanBase Cloud database.

   obclient  -h{host} -u{username} -p****** -P{port} -D{schema_name}
   

   The database connection string contains the parameters required to access the database. You can use the database connection string to verify the login and ensure that the parameters are correct.

   Note

   The URL information is required in the test.go file.

   Parameter description:

   • host: the connection address of the OceanBase Cloud database, for example, t********.********.oceanbase.cloud.
   • user_name: the account for accessing the database.
   • password: the password of the account.
   • port: the connection port of the OceanBase Cloud database, which is 3306 by default.
   • 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 based on the information obtained in Step 2: Obtain the connection information of the OceanBase Cloud database. Right-click the test.go file and select Open With. You can open the file in Notepad or other editing software.

Here is an example:

• The database access address is t********.********.oceanbase.cloud.
• The access port is 3306.
• The schema name to be accessed is test.
• The tenant connection account is mysql001.
• The password is ******.

Here is the sample code:

conn := "mysql001:******@tcp(t********.********.oceanbase.cloud:3306)/test"

Step 4: Run the go-oceanbase project

After you complete the code editing, open the command-line terminal in the project directory and run the Go file by using the go run command:

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

(Recommended) In Linux or macOS, you must configure the temporary environment variable before you can run go run.

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

If the following content is returned, the database connection is successful and the sample statement is executed correctly:

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

Project code

Click go-oceanbase to download the project code. It is a compressed file named go-oceanbase.

After decompressing the file, you will find a folder named go-oceanbase. The directory structure is as follows:

|-- go.mod
|-- go.sum
|-- test.go

File description:

• go.mod: the Go module file. It defines the module dependencies and version information of the project.
• go.sum: the module management file added in Go V1.11 and later. It records the module dependencies and version information of the project, as well as the corresponding checksums.
• test.go: the Go source code file. It contains the sample code of the project.

Introduction to the go.mod file

The go.mod file is used to define the module name, Go version, and dependency declarations for the project.

The go.mod file contains the following content:

• module go-oceanbase: This is the module name, which defines the project's namespace. In Go 1.16 and later, the module name must match the name of the project's root directory.
• go 1.20: This specifies the required Go version for the project.
• require github.com/go-sql-driver/mysql v1.7.1 // indirect: This is a dependency declaration for the project. It specifies that the github.com/go-sql-driver/mysql module version required by the project is V1.7.1, and that this dependency is an indirect dependency associated with another dependency, go.sum.

Introduction to the go.sum file

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

The go.sum file contains the following content:

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

Introduction to the test.go file

The test.go file defines how to interact with the MySQL compatible mode of OceanBase Cloud using Go language, including operations such as connecting to the database, creating tables, inserting data, querying data, and deleting tables. The test.go file contains the following content:

1. Define the main package. package main indicates that this is a package for an executable program. This package contains a main() function, which will be executed when the program runs.

2. Define the import package.

   The import statement imports the following four packages:

   • database/sql: Provides a generic SQL database access interface. It defines a set of generic interfaces and functions for connecting and operating various types of SQL databases.
   • fmt: Provides functions for formatted 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 functions for logging. It defines a set of functions for outputting log information to the console or other devices.
   • github.com/go-sql-driver/mysql: Provides a driver for the MySQL compatible mode. It implements the interfaces defined in the database/sql package to enable connection and operation in Go language. You need to specify the correct installation path of go-sql-driver/mysql.

   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 structure. The structure contains a field Name for storing query results and a main() function. The main() function includes the selectAll() function for performing operations such as creating tables, inserting data, querying data, and deleting tables.

   Sample code:

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

4. Define the selectAll() function.

   The selectAll() function includes operations such as connecting to the database, creating tables, inserting data, querying data, and deleting tables.

   1. Connect to the database. Define the connection string conn, which contains the connection parameters for the MySQL compatible mode, 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, record the log and exit the program. Use the defer keyword to delay the closure of the database connection to ensure it is closed when the function ends.

      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 a message to the console. Use the defer keyword to delay the execution of the db.Close() function to ensure the database connection is closed when the function ends. Use the fmt.Printf() function to output a connection success message to the console.

      Sample code:

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

   3. Create data. Create a table named t1 with a string-type field str of length 256.

      Sample code:

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

   4. Insert data. Insert a row of data into the t1 table, setting the value of the str field to Hello OceanBase.

      Sample code:

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

   5. Query data. Query all data from the t1 table and assign the query results to the variable res. If the query fails, return the error message.

      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. Delete data. Delete the t1 table. If the deletion fails, return the error message.

      Sample code:

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

Full 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 in the OceanBase Database open-source community.