Quickstart: Use Go language to connect and query data in Azure Database for PostgreSQL - Flexible Server
APPLIES TO: Azure Database for PostgreSQL - Flexible Server
This quickstart demonstrates how to connect to an Azure Database for PostgreSQL using code written in the Go language (golang). It shows how to use SQL statements to query, insert, update, and delete data in the database. This article assumes you are familiar with development using Go, but that you are new to working with Azure Database for PostgreSQL.
Prerequisites
This quickstart uses the resources created in either of these guides as a starting point:
Important
We recommend you use a server with Public access (allowed IP addresses) enabled for this quickstart. Using a server with Private access (VNet Integration) enabled to complete this quickstart might involve extra steps that aren't covered.
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 pq connector
Install Go and the Pure Go Postgres driver (pq) on your own machine. Depending on your platform, follow the appropriate steps:
Download and install Go for Microsoft Windows according to the installation instructions.
Launch the command prompt from the start menu.
Make a folder for your project, such as
mkdir %USERPROFILE%\go\src\postgresqlgo
.Change directory into the project folder, such as
cd %USERPROFILE%\go\src\postgresqlgo
.Set the environment variable for GOPATH to point to the source code directory.
set GOPATH=%USERPROFILE%\go
.Run go mod init to create a module in the current directory. For example:
go mod init postgresqlgo
.- The
<module_path>
parameter is generally a location in a GitHub repo - such asgithub.com/<your_github_account_name>/<directory>
. - When you're creating a command-line app as a test and won't publish the app, the
<module_path>
doesn't need to refer to an actual location. For example,postgresqlgo
.
- The
Install the Pure Go Postgres driver (pq) by running the
go get github.com/lib/pq
command.In summary, install Go, then run these commands in the command prompt:
mkdir %USERPROFILE%\go\src\postgresqlgo cd %USERPROFILE%\go\src\postgresqlgo set GOPATH=%USERPROFILE%\go go mod init postgresqlgo go get github.com/lib/pq
Get connection information
Get the connection information needed to connect to the Azure Database for PostgreSQL. You need the fully qualified server name and login credentials.
- Log in to the Azure portal.
- From the left-hand menu in Azure portal, select All resources, and then search for the server you have created (such as mydemoserver).
- Select the server name.
- 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
- To write Golang code, you can use a plain 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 GoLand by Jetbrains, Visual Studio Code by Microsoft, or Atom.
- Paste the Golang code from the following sections into text files, and save into your project folder with file extension *.go, such as Windows path
%USERPROFILE%\go\src\postgresqlgo\createtable.go
or Linux path~/go/src/postgresqlgo/createtable.go
. - Locate the
HOST
,DATABASE
,USER
, andPASSWORD
constants in the code, and replace the example values with your own values. A database named postgres is created when you create your Azure Database for PostgreSQL server instance. You can use that database or another one that you've created. - Launch the command prompt or bash shell. Change directory into your project folder. For example, on Windows
cd %USERPROFILE%\go\src\postgresqlgo\
. On Linuxcd ~/go/src/postgresqlgo/
. Some of the IDE environments mentioned offer debug and runtime capabilities without requiring shell commands. - Run the code by typing the command
go run createtable.go
to compile the application and run it. - Alternatively, to build the code into a native application,
go build createtable.go
, then launchcreatetable.exe
to run the application.
Connect and create a table
Use the following code to connect and create a table using CREATE TABLE SQL statement, followed by INSERT INTO SQL statements to add rows into the table.
The code imports three packages: the sql package, the pq package as a driver to communicate with the PostgreSQL server, 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 PostgreSQL database, 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 several times to run several SQL commands. Each time a custom checkError() method checks if an error occurred and panic to exit if an error does occur.
Replace the HOST
, DATABASE
, USER
, and PASSWORD
parameters with your own values.
package main
import (
"database/sql"
"fmt"
_ "github.com/lib/pq"
)
const (
// Initialize connection constants.
HOST = "mydemoserver.postgres.database.azure.com"
DATABASE = "postgres"
USER = "mylogin"
PASSWORD = "<server_admin_password>"
)
func checkError(err error) {
if err != nil {
panic(err)
}
}
func main() {
// Initialize connection string.
var connectionString string = fmt.Sprintf("host=%s user=%s password=%s dbname=%s sslmode=require", HOST, USER, PASSWORD, DATABASE)
// Initialize connection object.
db, err := sql.Open("postgres", connectionString)
checkError(err)
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.
sql_statement := "INSERT INTO inventory (name, quantity) VALUES ($1, $2);"
_, err = db.Exec(sql_statement, "banana", 150)
checkError(err)
_, err = db.Exec(sql_statement, "orange", 154)
checkError(err)
_, err = db.Exec(sql_statement, "apple", 100)
checkError(err)
fmt.Println("Inserted 3 rows of data")
}
Read data
Use the following code to connect and read the data using a SELECT SQL statement.
The code imports three packages: the sql package, the pq package as a driver to communicate with the PostgreSQL server, 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 PostgreSQL database, and checks the connection using method db.Ping(). A database handle is used throughout, holding the connection pool for the database server. The select query is run by calling method db.Query(), and the resulting rows are kept in a variable of type rows. The code reads the column data values in the current row using method rows.Scan() and loops over the rows using the iterator rows.Next() until no more rows exist. Each row's column values are printed to the console out. Each time a custom checkError() method is used to check if an error occurred and panic to exit if an error does occur.
Replace the HOST
, DATABASE
, USER
, and PASSWORD
parameters with your own values.
package main
import (
"database/sql"
"fmt"
_ "github.com/lib/pq"
)
const (
// Initialize connection constants.
HOST = "mydemoserver.postgres.database.azure.com"
DATABASE = "postgres"
USER = "mylogin"
PASSWORD = "<server_admin_password>"
)
func checkError(err error) {
if err != nil {
panic(err)
}
}
func main() {
// Initialize connection string.
var connectionString string = fmt.Sprintf("host=%s user=%s password=%s dbname=%s sslmode=require", HOST, USER, PASSWORD, DATABASE)
// Initialize connection object.
db, err := sql.Open("postgres", connectionString)
checkError(err)
err = db.Ping()
checkError(err)
fmt.Println("Successfully created connection to database")
// Read rows from table.
var id int
var name string
var quantity int
sql_statement := "SELECT * from inventory;"
rows, err := db.Query(sql_statement)
checkError(err)
defer rows.Close()
for rows.Next() {
switch err := rows.Scan(&id, &name, &quantity); err {
case sql.ErrNoRows:
fmt.Println("No rows were returned")
case nil:
fmt.Printf("Data row = (%d, %s, %d)\n", id, name, quantity)
default:
checkError(err)
}
}
}
Update data
Use the following code to connect and update the data using an UPDATE SQL statement.
The code imports three packages: the sql package, the pq package as a driver to communicate with the Postgres server, 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 PostgreSQL database, 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 SQL statement that updates the table. A custom checkError() method is used to check if an error occurred and panic to exit if an error does occur.
Replace the HOST
, DATABASE
, USER
, and PASSWORD
parameters with your own values.
package main
import (
"database/sql"
_ "github.com/lib/pq"
"fmt"
)
const (
// Initialize connection constants.
HOST = "mydemoserver.postgres.database.azure.com"
DATABASE = "postgres"
USER = "mylogin"
PASSWORD = "<server_admin_password>"
)
func checkError(err error) {
if err != nil {
panic(err)
}
}
func main() {
// Initialize connection string.
var connectionString string =
fmt.Sprintf("host=%s user=%s password=%s dbname=%s sslmode=require", HOST, USER, PASSWORD, DATABASE)
// Initialize connection object.
db, err := sql.Open("postgres", connectionString)
checkError(err)
err = db.Ping()
checkError(err)
fmt.Println("Successfully created connection to database")
// Modify some data in table.
sql_statement := "UPDATE inventory SET quantity = $2 WHERE name = $1;"
_, err = db.Exec(sql_statement, "banana", 200)
checkError(err)
fmt.Println("Updated 1 row of data")
}
Delete data
Use the following code to connect and delete the data using a DELETE SQL statement.
The code imports three packages: the sql package, the pq package as a driver to communicate with the Postgres server, 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 PostgreSQL database, 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 SQL statement that deletes a row from the table. A custom checkError() method is used to check if an error occurred and panic to exit if an error does occur.
Replace the HOST
, DATABASE
, USER
, and PASSWORD
parameters with your own values.
package main
import (
"database/sql"
_ "github.com/lib/pq"
"fmt"
)
const (
// Initialize connection constants.
HOST = "mydemoserver.postgres.database.azure.com"
DATABASE = "postgres"
USER = "mylogin"
PASSWORD = "<server_admin_password>"
)
func checkError(err error) {
if err != nil {
panic(err)
}
}
func main() {
// Initialize connection string.
var connectionString string =
fmt.Sprintf("host=%s user=%s password=%s dbname=%s sslmode=require", HOST, USER, PASSWORD, DATABASE)
// Initialize connection object.
db, err := sql.Open("postgres", connectionString)
checkError(err)
err = db.Ping()
checkError(err)
fmt.Println("Successfully created connection to database")
// Delete some data from table.
sql_statement := "DELETE FROM inventory WHERE name = $1;"
_, err = db.Exec(sql_statement, "orange")
checkError(err)
fmt.Println("Deleted 1 row of data")
}
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
Share your suggestions and bugs with the Azure Database for PostgreSQL product team.
Related content
- Manage Azure Database for PostgreSQL - Flexible Server.
- Quickstart: Use Python to connect and query data from an instance of Azure Database for PostgreSQL flexible server.
- Quickstart: Use Java to connect and query data from an instance of Azure Database for PostgreSQL flexible server.
- Quickstart: Use .NET (C#) to connect and query data from an instance of Azure Database for PostgreSQL flexible server.
- Quickstart: Use PHP to connect and query data from an instance of Azure Database for PostgreSQL flexible server.
- Quickstart: Use Azure CLI to connect and query data from an instance of Azure Database for PostgreSQL flexible server.
- Quickstart: Import data from Azure Database for PostgreSQL - Flexible Server in Power BI.