This topic describes how to build an application using the Go-SQL-Driver/MySQL driver and OceanBase AI Database to perform basic operations such as creating tables, inserting data, and querying data.
Download the go-oceanbase sample project Prerequisites
Install OceanBase AI Database, the Go language, and related drivers, and ensure that environment variables are correctly configured.
- Install OceanBase AI Database.
- Install the Go language.
- Install the Go-SQL-Driver/MySQL driver.
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) Install the Go language and its drivers.
- Obtain the connection information for OceanBase AI Database.
- Modify the database connection information in the
go-oceanbaseproject. - Run the
go-oceanbaseproject.
Step 1: (Optional) Install the Go language and its drivers
If you have already installed the Go language and the Go-SQL-Driver/MySQL driver, skip this step. If not, follow these steps to install them.
Install the Go language
Download the Go installation package: You can download the appropriate package 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 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 during Go installation, replace it with the corresponding directory.Verify the installation: Enter the following command in the shell command line to check the Go version and verify the installation:
C:\Users\admin\> go version go version go1.20.6 windows/amd64
Install the Go-SQL-Driver/MySQL driver
You can choose different installation methods depending on the version of the Go language. When installing the Go-SQL-Driver/MySQL driver, navigate to the corresponding project directory and open a command-line terminal. For more information about
Go-SQL-Driver/MySQL, visit 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.1If you cannot install it using the
go getcommand due to version or network issues, you can use thego installcommand to installgo-sql-driver/mysql.Clone the
go-sql-driver/mysqlrepository from GitHub into thego/srcdirectory.cd /usr/local/go/src git clone https://github.com/go-sql-driver/mysql.gitNote
/usr/local/go/srcmust be replaced with the actual installation directory of Go.Install it using
go install.go install mysqlNote
For some versions, the default execution directory of
go installmay not be/src. You can determine the actual directory from the error message after executinggo install. For example, if the errorcannot find package "mysql" in: /usr/local/go/src/vendor/mysqloccurs, place the mysql folder in the/src/vendordirectory before running the installation command.Check whether the Go-SQL-Driver/MySQL driver has been installed. If the installation fails, modify the configuration according to the error message.
go list -m github.com/go-sql-driver/mysql
Step 2: Obtain OceanBase AI Database connection information
Contact the OceanBase AI Database deployment personnel or administrator to obtain the corresponding database connection string.
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 required in the test.go file.
Parameter description:
host: The IP address for connecting to OceanBase AI Database. For ODP connections, this is an ODP address; for direct connections, this is the IP address of an OBServer node.user_name: The connection account for the tenant. For ODP connections, the common formats areusername@tenant_name#cluster_nameorcluster_name:tenant_name:username. For direct connections, the format isusername@tenant_name.password: The account password.port: The port for connecting to OceanBase AI Database. For ODP connections, the default port is2883, which can be customized during ODP deployment. For direct connections, the default port is2881, which can be customized during OceanBase AI Database deployment.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 OceanBase AI Database connection information. Select the test.go file, right-click, and choose Open With to open it with Notepad or another editing tool.

Example:
- The IP address of the OBServer node is
10.10.10.1. - The access port is 2881.
- The name of the schema to access is
test. - The tenant connection account is
root@mysql.root@mysqlis a user tenant created in OceanBase AI Database, andtestis the username for theroot@mysqltenant. - 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 editing the code, open a command-line terminal in the project directory and run the Go file directly using 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 was 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
Project code
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
The files and directories are described as follows:
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.
Code in 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.
Code in 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=.
Code in test.go
The test.go file defines how to interact with OceanBase AI Database using the 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:
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
importstatement imports the following four packages:database/sql: Provides a general SQL database access interface. It defines a set of general 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 information to the console or other devices.github.com/go-sql-driver/mysql: Provides the driver. It implements the interfaces defined in thedatabase/sqlpackage to connect to and operate on databases in Go. You must provide the correct installation path forgo-sql-driver/mysql.
The sample code is as follows:
import ( "database/sql" "fmt" "log" _ "github.com/go-sql-driver/mysql" // Enter the installation path of go-sql-driver/mysql. )Define the
Strstruct. It 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 dropping a table.The sample code is as follows:
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 dropping a table.Connect to the database. Define the connection string
conn, which includes connection parameters 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 delay closing the database connection, ensuring it is closed at the end of the function.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) }Print a message to the console. Use the
deferkeyword to delay executing thedb.Close()function, ensuring the database connection is closed at the end of the function. Use thefmt.Printf()function to output a success 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")Create data. Create a table named
t1with a string-type fieldstrof length256.The sample code is as follows:
_, 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.The sample code is as follows:
_, 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.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) }Delete data. Delete table
t1. If the deletion fails, return an error message.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"
// 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, see Go-SQL-Driver/MySQL.