This topic describes how to build an application using the Go-SQL-Driver/MySQL driver and OceanBase Cloud to perform basic operations such as creating tables, inserting data, and querying data.
Prerequisites
Create an OceanBase Cloud instance, set up Go, and ensure that environment variables are correctly configured.
- You have registered an OceanBase Cloud account and created a cluster instance and a tenant in MySQL mode. For details, see Create a cluster instance and Create a tenant.
- Install the Go language.
- Download the sample project dependencies.
Procedure
Note
The steps provided in this document are generated for a Windows environment. If you are using a different operating system or compiler, the steps may vary slightly.
- (Optional) Set up Go and the project dependencies.
- Obtain the OceanBase Cloud connection information.
- Modify the database connection information in the
go-oceanbaseproject. - Run the
go-oceanbaseproject.
Step 1: (Optional) Set up Go and the project dependencies
If Go is already installed, skip ahead to downloading the sample project dependencies.
Install the Go language
Download the Go language installation package: You can download the appropriate version for your operating system from the Go official website.
Note
The Go installation package used in this document is named go1.20.6.windows-amd64.msi.
Install the Go language: Double-click the downloaded installation package and follow the prompts to install it.
Configure environment variables: Add the Go installation path to the system's PATH environment variable.
In a Windows environment, you can add
C:\usr\local\go\binto the Path value in Control Panel > System and Security > System > Advanced system settings > Environment Variables > System Variables.In a Linux or macOS environment, edit the
~/.bashrcor~/.bash_profilefile and add the following content:export PATH=$PATH:/usr/local/go/bin
Note
\usr\local\go\binis the default installation directory. If you modified the installation directory when installing Go, replace it with the corresponding directory.Verify the installation: Enter the following command in the shell command line to check the Go language version and verify the installation:
C:\Users\admin\> go version go version go1.20.6 windows/amd64
Download the sample project dependencies.
Extract the sample project and run the following command in the directory that contains
go.mod. Go downloads the dependencies declared by the project, including the MySQL driver.go mod download
Step 2: Obtain the OceanBase Cloud database connection information
Log in to the OceanBase Cloud console. On the instance list page, expand the target instance's information, and under the target tenant, select Connect > Get connection string.
For more detailed operations, see Get a connection string.
Fill in the corresponding information in the URL below based on your 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 it to log in to the database and verify that the parameter information is correct.
Note
The URL information here is needed in the
test.gofile.Parameter description:
host: The connection address of OceanBase Cloud, for example,t********.********.oceanbase.cloud.user_name: The account used to access the database.password: The password of the account.port: The port for connecting to OceanBase Cloud. The default value is 3306.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 OceanBase Cloud. Select the test.go file, right-click it, and choose Open with to open it with Notepad or other editing software.
Example:
- The database access endpoint is
t********.********.oceanbase.cloud. - The access port is 3306.
- The name of the schema to be accessed is
test. - The tenant connection account is
mysql001. - The password is
******.
Sample code:
conn := "mysql001:******@tcp(t********.********.oceanbase.cloud:3306)/test"
Step 4: Run the go-oceanbase project
After editing the code, open a command-line terminal in the project directory and run the following command directly with go run:
C:\Users\admin\Desktop\go-oceanbase>go run test.go
(Optional) In Linux or macOS environments, you must configure temporary environment variables before running go run.
export PATH=$PATH:/usr/local/go/bin
go run test.go
If the following content is returned after running, the database connection is successful and the sample statement is executed correctly:
Note
This content shows the result after running the statement that "drops table t1".
C:\Users\admin\Desktop\go-oceanbase>go run test.go
success to connect OceanBase with go_mysql driver
Hello OceanBase
Sample project overview
Click go-oceanbase to download the project code. It is a compressed file named go-oceanbase.
After decompressing it, you will get a folder named go-oceanbase. The directory structure is as follows:
|-- go.mod
|-- go.sum
|-- test.go
File description:
go.mod: A Go module file that defines the project's module dependencies and version information.go.sum: A module management file introduced in Go V1.11 and later. It records the modules and their versions that the project depends on, along with their corresponding checksums.test.go: A Go source code file containing sample code for the project.
Introduction to go.mod
The go.mod file is used to define the project's module name, Go version number, and dependency declarations.
The go.mod file contains the following content:
module go-oceanbase: This is the project's module name, which defines its namespace. In Go 1.16 and later, the module name must match the name of the project's root directory.go 1.20: This is the Go version required by the project.require github.com/go-sql-driver/mysql v1.7.1 // indirect: This is the project's dependency declaration. It specifies that the project requires thegithub.com/go-sql-driver/mysqlmodule version V1.7.1, and that this dependency is an indirect dependency, associated with another dependencygo.sum.
Introduction to go.sum
The go.sum file is used to define the version information for the github.com/go-sql-driver/mysql dependency, ensuring 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 files, used to ensure the correct version is used when building the project. The hash value here islUIinVbN1DY0xBg0eMOzmmtGoHwWBbvnWubQUrtU8EI=.github.com/go-sql-driver/mysql v1.7.1/go.mod h1:OXbVy3sEdcQ2Doequ6Z5BW6fXNQTmx+9S1MCJN5yJMI=The hash value of the module's dependency files, used to ensure the correct dependency version is used when building the project. The hash value here isOXbVy3sEdcQ2Doequ6Z5BW6fXNQTmx+9S1MCJN5yJMI=.
Introduction to test.go
The test.go file defines how to interact with OceanBase Cloud in MySQL-compatible mode using the Go language, including operations such as connecting to the database, creating tables, inserting data, querying data, and dropping tables. The test.go file contains the following content:
Define the
mainpackage.package mainindicates that this is a package for a runnable program. This package contains amain()function, which is executed when the program runs.Define the
importstatements.The
importstatements import the following four packages:database/sql: Provides a generic SQL database access interface. It defines a set of common interfaces and functions for connecting to 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 information. It defines a set of functions for outputting log messages to the console or other devices.github.com/go-sql-driver/mysql: Provides a driver for MySQL-compatible mode. It implements the interfaces defined in thedatabase/sqlpackage, enabling connections to and operations on MySQL-compatible mode in Go. You must provide the correct installation path forgo-sql-driver/mysql.
Sample code:
import ( "database/sql" "fmt" "log" _ "github.com/go-sql-driver/mysql" // Enter the installation path of go-sql-driver/mysql. )Define the
Strstruct. The struct contains a fieldNameto store query results. Amain()function is defined, which includes aselectAll()function to perform operations such as creating a table, inserting data, querying data, and deleting a table.Sample code:
type Str struct { Name string } func main() { selectAll() }Define the
selectAll()function.The
selectAll()function encompasses operations such as connecting to a database, creating a table, inserting data, querying data, and deleting a table.Connect to the database. Define the connection string
conn, which includes connection parameters for MySQL-compatible mode, such as the username, password, IP address, port number, and database name. Call thesql.Open()function to open a database connection. If an error occurs, log it and exit the program. Use thedeferkeyword to defer closing the database connection, ensuring it is closed at the end of the function.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) }Print a message to the console. Use the
deferkeyword to defer executing thedb.Close()function, ensuring the database connection is closed at the end of the function. Use thefmt.Printf()function to print a 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")Create data. Create a table named
t1with a string-type fieldstrof length256.Sample code:
_, err = db.Query("create table t1(str varchar(256))") if err != nil { log.Fatal(err) }Insert data. Insert a row of data into table
t1, setting the value of thestrfield toHello OceanBase.Sample code:
_, err = db.Query("insert into t1 values ('Hello OceanBase')") if err != nil { log.Fatal(err) }Query data. Query all data from table
t1and assign the query result to the variableres. If the query fails, return an 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) }Delete data. Delete table
t1. If the deletion fails, return an error message.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"
// Enter 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)
}
// Delete table t1
_, err = db.Query("drop table t1")
if err != nil {
log.Fatal(err)
}
}
References
For more information about Go-SQL-Driver/MySQL, visit the OceanBase Database open-source community. For details, see Go-SQL-Driver/MySQL.