Quickstart: Use Go language to connect and query data in Azure Database for MySQL

APPLIES TO: Azure Database for MySQL - Single Server

Important

Azure Database for MySQL single server is on the retirement path. We strongly recommend that you upgrade to Azure Database for MySQL flexible server. For more information about migrating to Azure Database for MySQL flexible server, see What's happening to Azure Database for MySQL Single Server?

This quickstart demonstrates how to connect to an Azure Database for MySQL from Windows, Ubuntu Linux, and Apple macOS platforms by using code written in the Go language. It shows how to use SQL statements to query, insert, update, and delete data in the database. This topic assumes that you are familiar with development using Go and that you are new to working with Azure Database for MySQL.

Prerequisites

This quickstart uses the resources created in either of these guides as a starting point:

• Create an Azure Database for MySQL server using Azure portal
• Create an Azure Database for MySQL server using Azure CLI

Important

Ensure the IP address you're connecting from has been added the server's firewall rules using the Azure portal or Azure CLI

Install Go and MySQL connector

Install Go and the go-sql-driver for MySQL on your own computer. Depending on your platform, follow the steps in the appropriate section:

Windows

1. Download and install Go for Microsoft Windows according to the installation instructions.

2. Launch the command prompt from the start menu.

3. Make a folder for your project such. mkdir %USERPROFILE%\go\src\mysqlgo.

4. Change directory into the project folder, such as cd %USERPROFILE%\go\src\mysqlgo.

5. Set the environment variable for GOPATH to point to the source code directory. set GOPATH=%USERPROFILE%\go.

6. Install the go-sql-driver for mysql by running the go get github.com/go-sql-driver/mysql command.

   In summary, install Go, then run these commands in the command prompt:

   mkdir  %USERPROFILE%\go\src\mysqlgo
   cd %USERPROFILE%\go\src\mysqlgo
   set GOPATH=%USERPROFILE%\go
   go get github.com/go-sql-driver/mysql
   

Linux (Ubuntu)

1. Launch the Bash shell.

2. Install Go by running sudo apt-get install golang-go.

3. Make a folder for your project in your home directory, such as mkdir -p ~/go/src/mysqlgo/.

4. Change directory into the folder, such as cd ~/go/src/mysqlgo/.

5. Set the GOPATH environment variable to point to a valid source directory, such as your current home directory's go folder. At the Bash shell, run export GOPATH=~/go to add the go directory as the GOPATH for the current shell session.

6. Install the go-sql-driver for mysql by running the go get github.com/go-sql-driver/mysql command.

   In summary, run these bash commands:

   sudo apt-get install golang-go git -y
   mkdir -p ~/go/src/mysqlgo/
   cd ~/go/src/mysqlgo/
   export GOPATH=~/go/
   go get github.com/go-sql-driver/mysql
   

Apple macOS

1. Download and install Go according to the installation instructions matching your platform.

2. Launch the Bash shell.

3. Make a folder for your project in your home directory, such as mkdir -p ~/go/src/mysqlgo/.

4. Change directory into the folder, such as cd ~/go/src/mysqlgo/.

5. Set the GOPATH environment variable to point to a valid source directory, such as your current home directory's go folder. At the Bash shell, run export GOPATH=~/go to add the go directory as the GOPATH for the current shell session.

6. Install the go-sql-driver for mysql by running the go get github.com/go-sql-driver/mysql command.

   In summary, install Go, then run these bash commands:

   mkdir -p ~/go/src/mysqlgo/
   cd ~/go/src/mysqlgo/
   export GOPATH=~/go/
   go get github.com/go-sql-driver/mysql
   

Get connection information

Get the connection information needed to connect to the Azure Database for MySQL. You need the fully qualified server name and login credentials.

1. Log in to the Azure portal.
2. From the left-hand menu in Azure portal, click All resources, and then search for the server you have created (such as mydemoserver).
3. Click the server name.
4. From the server's Overview panel, make a note of the Server name and Server admin login name. If you forget your password, you can also reset the password from this panel.

Build and run Go code

1. To write Golang code, you can use a simple text editor, such as Notepad in Microsoft Windows, vi or Nano in Ubuntu, or TextEdit in macOS. If you prefer a richer Interactive Development Environment (IDE), try Gogland by Jetbrains, Visual Studio Code by Microsoft, or Atom.
2. Paste the Go code from the sections below into text files, and then save them into your project folder with file extension *.go (such as Windows path %USERPROFILE%\go\src\mysqlgo\createtable.go or Linux path ~/go/src/mysqlgo/createtable.go).
3. Locate the HOST, DATABASE, USER, and PASSWORD constants in the code, and then replace the example values with your own values.
4. Launch the command prompt or Bash shell. Change directory into your project folder. For example, on Windows cd %USERPROFILE%\go\src\mysqlgo\. On Linux cd ~/go/src/mysqlgo/. Some of the IDE editors mentioned offer debug and runtime capabilities without requiring shell commands.
5. Run the code by typing the command go run createtable.go to compile the application and run it.
6. Alternatively, to build the code into a native application, go build createtable.go, then launch createtable.exe to run the application.

Connect, create table, and insert data

Use the following code to connect to the server, create a table, and load the data by using an INSERT SQL statement.

The code imports three packages: the sql package, the go sql driver for mysql as a driver to communicate with the Azure Database for MySQL, and the fmt package for printed input and output on the command line.

The code calls method sql.Open() to connect to Azure Database for MySQL, and it checks the connection by using method db.Ping(). A database handle is used throughout, holding the connection pool for the database server. The code calls the Exec() method several times to run several DDL commands. The code also uses Prepare() and Exec() to run prepared statements with different parameters to insert three rows. Each time, a custom checkError() method is used to check if an error occurred and panic to exit.

Replace the host, database, user, and password constants with your own values.

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
)

const (
	host     = "mydemoserver.mysql.database.azure.com"
	database = "quickstartdb"
	user     = "myadmin@mydemoserver"
	password = "yourpassword"
)

func checkError(err error) {
	if err != nil {
		panic(err)
	}
}

func main() {

	// Initialize connection string.
	var connectionString = fmt.Sprintf("%s:%s@tcp(%s:3306)/%s?allowNativePasswords=true", user, password, host, database)

	// Initialize connection object.
	db, err := sql.Open("mysql", connectionString)
	checkError(err)
	defer db.Close()

	err = db.Ping()
	checkError(err)
	fmt.Println("Successfully created connection to database.")

	// Drop previous table of same name if one exists.
	_, err = db.Exec("DROP TABLE IF EXISTS inventory;")
	checkError(err)
	fmt.Println("Finished dropping table (if existed).")

	// Create table.
	_, err = db.Exec("CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);")
	checkError(err)
	fmt.Println("Finished creating table.")

	// Insert some data into table.
	sqlStatement, err := db.Prepare("INSERT INTO inventory (name, quantity) VALUES (?, ?);")
	res, err := sqlStatement.Exec("banana", 150)
	checkError(err)
	rowCount, err := res.RowsAffected()
	fmt.Printf("Inserted %d row(s) of data.\n", rowCount)

	res, err = sqlStatement.Exec("orange", 154)
	checkError(err)
	rowCount, err = res.RowsAffected()
	fmt.Printf("Inserted %d row(s) of data.\n", rowCount)

	res, err = sqlStatement.Exec("apple", 100)
	checkError(err)
	rowCount, err = res.RowsAffected()
	fmt.Printf("Inserted %d row(s) of data.\n", rowCount)
	fmt.Println("Done.")
}

Read data

Use the following code to connect and read the data by using a SELECT SQL statement.

The code imports three packages: the sql package, the go sql driver for mysql as a driver to communicate with the Azure Database for MySQL, and the fmt package for printed input and output on the command line.

The code calls method sql.Open() to connect to Azure Database for MySQL, and checks the connection using method db.Ping(). A database handle is used throughout, holding the connection pool for the database server. The code calls the Query() method to run the select command. Then it runs Next() to iterate through the result set and Scan() to parse the column values, saving the value into variables. Each time a custom checkError() method is used to check if an error occurred and panic to exit.

Replace the host, database, user, and password constants with your own values.

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
)

const (
	host     = "mydemoserver.mysql.database.azure.com"
	database = "quickstartdb"
	user     = "myadmin@mydemoserver"
	password = "yourpassword"
)

func checkError(err error) {
	if err != nil {
		panic(err)
	}
}

func main() {

	// Initialize connection string.
	var connectionString = fmt.Sprintf("%s:%s@tcp(%s:3306)/%s?allowNativePasswords=true", user, password, host, database)

	// Initialize connection object.
	db, err := sql.Open("mysql", connectionString)
	checkError(err)
	defer db.Close()

	err = db.Ping()
	checkError(err)
	fmt.Println("Successfully created connection to database.")

	// Variables for printing column data when scanned.
	var (
		id       int
		name     string
		quantity int
	)

	// Read some data from the table.
	rows, err := db.Query("SELECT id, name, quantity from inventory;")
	checkError(err)
	defer rows.Close()
	fmt.Println("Reading data:")
	for rows.Next() {
		err := rows.Scan(&id, &name, &quantity)
		checkError(err)
		fmt.Printf("Data row = (%d, %s, %d)\n", id, name, quantity)
	}
	err = rows.Err()
	checkError(err)
	fmt.Println("Done.")
}

Update data

Use the following code to connect and update the data using a UPDATE SQL statement.

The code imports three packages: the sql package, the go sql driver for mysql as a driver to communicate with the Azure Database for MySQL, and the fmt package for printed input and output on the command line.

The code calls method sql.Open() to connect to Azure Database for MySQL, and checks the connection using method db.Ping(). A database handle is used throughout, holding the connection pool for the database server. The code calls the Exec() method to run the update command. Each time a custom checkError() method is used to check if an error occurred and panic to exit.

Replace the host, database, user, and password constants with your own values.

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
)

const (
	host     = "mydemoserver.mysql.database.azure.com"
	database = "quickstartdb"
	user     = "myadmin@mydemoserver"
	password = "yourpassword"
)

func checkError(err error) {
	if err != nil {
		panic(err)
	}
}

func main() {

	// Initialize connection string.
	var connectionString = fmt.Sprintf("%s:%s@tcp(%s:3306)/%s?allowNativePasswords=true", user, password, host, database)

	// Initialize connection object.
	db, err := sql.Open("mysql", connectionString)
	checkError(err)
	defer db.Close()

	err = db.Ping()
	checkError(err)
	fmt.Println("Successfully created connection to database.")

	// Modify some data in table.
	rows, err := db.Exec("UPDATE inventory SET quantity = ? WHERE name = ?", 200, "banana")
	checkError(err)
	rowCount, err := rows.RowsAffected()
	fmt.Printf("Updated %d row(s) of data.\n", rowCount)
	fmt.Println("Done.")
}

Delete data

Use the following code to connect and remove data using a DELETE SQL statement.

The code imports three packages: the sql package, the go sql driver for mysql as a driver to communicate with the Azure Database for MySQL, and the fmt package for printed input and output on the command line.

The code calls method sql.Open() to connect to Azure Database for MySQL, and checks the connection using method db.Ping(). A database handle is used throughout, holding the connection pool for the database server. The code calls the Exec() method to run the delete command. Each time a custom checkError() method is used to check if an error occurred and panic to exit.

Replace the host, database, user, and password constants with your own values.

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)

const (
	host     = "mydemoserver.mysql.database.azure.com"
	database = "quickstartdb"
	user     = "myadmin@mydemoserver"
	password = "yourpassword"
)

func checkError(err error) {
	if err != nil {
		panic(err)
	}
}

func main() {

	// Initialize connection string.
	var connectionString = fmt.Sprintf("%s:%s@tcp(%s:3306)/%s?allowNativePasswords=true", user, password, host, database)

	// Initialize connection object.
	db, err := sql.Open("mysql", connectionString)
	checkError(err)
	defer db.Close()

	err = db.Ping()
	checkError(err)
	fmt.Println("Successfully created connection to database.")

	// Modify some data in table.
	rows, err := db.Exec("DELETE FROM inventory WHERE name = ?", "orange")
	checkError(err)
	rowCount, err := rows.RowsAffected()
	fmt.Printf("Deleted %d row(s) of data.\n", rowCount)
	fmt.Println("Done.")
}

Clean up resources

To clean up all resources used during this quickstart, delete the resource group using the following command:

az group delete \
    --name $AZ_RESOURCE_GROUP \
    --yes

Next steps

Migrate your database using Export and Import