Go – Part 2 – Connecting to PostgreSQL on Azure (Identity API)

This post is a continuation of Go – Part 1 – Simple Web Application (Identity API) and requires a PostgreSQL server available, see PostgreSQL – Local on Docker and Cloud on Azure

There are many ways of connecting to a database server in Go.

One option is GORM, an Object-Relational Mapping library. It makes mapping to structs a simple and helps with data migration and abstraction between different database options.

Another project designed and maintained by Meta is ent, an entity framework for Go.

Those are 2 very good options but same as in the previous post, I will handle things a bit more in the raw version using pgx instead of an ORM. This will give us the opportunity to look a bit more into SQL and to be able to test both ways and compare performance if needed.

Goign with pgx brings a different approach as it is a driver for PostgreSQL instead of an ORM library. It is less flexible in terms of migrating from one database to another, but on the other hand it has really good performance.

To start, I had reverted the controller instantiation back to the regular Go naming convention.

# main.go

(...)
discoveryController := controllers.NewDiscoveryController(logger)
(...)

# discovery_controller.go

(...)
func NewDiscoveryController(logger *log.Logger) *DiscoveryController {
	return &DiscoveryController{logger: logger}
}
(...)

It is never a good idea to include credentials as part of your code and have those going to source code management systems, exposing secrets to external parties. To mitigate this, we are going to use environment variables to hold the variables needed for the database configuration.

We need to install the godotenv package. In a terminal inside your project directory run:

go get github.com/joho/godotenv

Now, we install pgx:

go get github.com/jackc/pgx/v5
go get github.com/jackc/puddle/v2

In order to configure the variables, we can use a .env file in the root directory of the project (which is how we are going to do it for this post) or configure environment variables like demonstrated in Windows 11 – Environment variables and the Path.

Create a .env file with following content (I’m using example for PostgreSQL on Azure, which also includes SSL enabling):

APP_ENVIRONMENT=development
DB_HOST=<YOUR_HOST>.postgres.database.azure.com
DB_PORT=5432
DB_NAME=identity
DB_USER=identity
DB_PASS=<YOUR_PASSWORD>
DB_SSLM=verify-ca
DB_CERT=<YOUR_CREDENTIALS_PATH>\\DigiCertGlobalRootCA.crt

As you can see, we are using database and username different than postgres, then we need to create those.

Using pgAdmin4 that we installed in the post , we will create an user called identity_development. We do this to be able to have different users and databases for development, staging, production or any other profile. Locally this is just fine. In the final deployment to the cloud we should have actual separate environments and resources for each environment.

Once user is created, it is time to create the database and associate to the user.

Now identity_development user has all permissions in identity_development database and we can start looking into Go code.

But there’s one more thing to do, include .env* in the .gitignore file to make sure we don’t accidentally commit the credentials to the repository, again, exposing the secrets.

Included code is highlighted.

logger.Println("| Starting Identity API")

(...)

logger.Println("| ")
logger.Println("| Initializing environment variables...")

// Try to load environment variables
variablesError:= godotenv.Load()
if variablesError!= nil {
    logger.Println("| Not possible to load variables. Required settings not present. Please, verify and try again.")
    logger.Fatal("| Error: ", variablesError)
    os.Exit(1)
}

app_environment := os.Getenv("APP_ENVIRONMENT")
logger.Printf("| Application environment is set to: %s", app_environment)

(...)

// Setting application context
applicationContext := context.Background()

We can do a quick test to make sure the variables can be read before we continue.

Now, we add the other environment variables:

db_host := os.Getenv("DB_HOST")
db_port := os.Getenv("DB_PORT")
db_name := os.Getenv("DB_NAME")
db_user := os.Getenv("DB_USER")
db_pass := os.Getenv("DB_PASS")
db_sslm := os.Getenv("DB_SSLM")
db_cert := os.Getenv("DB_CERT")

Then we parse the variables, creating the connection string. First, the append of the app_environment:

// Parse connecting string
switch app_environment {
case "development":
    db_name = db_name + "_development"
    db_user = db_user + "_development"
case "staging":
    db_name = db_name + "_staging"
    db_user = db_user + "_staging"
case "production":
    db_name = db_name + "_production"
    db_user = db_user + "_production"
default:
    logger.Fatal("| Error: Only legal environment names are development, staging and production. Please, verify and try again.")
    os.Exit(1)
}

connectionString := fmt.Sprintf("postgres://%s:%s@%s:%s/%s", db_user, db_pass, db_host, db_port, db_name)

if db_sslm != "" {
    connectionString = connectionString + "?sslmode=" + db_sslm
}

if db_cert != "" {
    connectionString = connectionString + "&sslrootcert=" + db_cert
}

In pgx we have single connection and pool. We will use pgxpool for this application.

// Instantiate pgxpool as dataContext
dataContext, dbError := pgxpool.New(applicationContext, connectionString)
if dbError != nil {
    logger.Println("| It was not possible to create connection pool. Please, verify and try again.")
    logger.Fatal("| Error: ", dbError)
    os.Exit(1)
}

dbConnectError := dataContext.Ping(applicationContext)
if dbConnectError != nil {
    logger.Println("| It was not possible to connect to the database. Please, verify and try again.")
    logger.Fatal("| Error: ", dbConnectError)
    os.Exit(1)
}
defer dataContext.Close()

Now we can try to query the database version using SQL, then print it.

// Try to get database version via SQL query
var databaseServerVersion string
checkVersionError := dataContext.QueryRow(applicationContext, "SELECT version()").Scan(&databaseServerVersion)
if checkVersionError != nil {
    logger.Println("| It was not possible to check server version. Please, verify and try again.")
    logger.Printf("| Error: %v", checkVersionError)
}
logger.Printf("| Database server is using version: %s", databaseServerVersion)

We should see the following output: