Getting Started: Connecting Golang Console App with Azure PostgreSQL Database

Setting up Go in VS Code

Firstly, I need to equip Visual Studio Code with the Go language support by installing Go extension in the IDE. Installing the extension helps us to do day-to-day job, such as, code navigating, code editing, code testing and debugging in an easy and efficient manner.

Installed and enabled the Go extension from Microsoft.

There are many cool features I like in the extension.

Firstly, it’s the convenience of F12 Code Navigation. With Go extension, in Go code I can easily view the source code of the type definition with just F12 or Alt+F12 on it. This is similar to my C# coding experience on Visual Studio too. To show all references of the type, I can simply use Shift+F12.

Secondly, for every file save, the extension will build, vet, and lint. Build (go build) builds the command or the package. Vet (go vet) examines Go source code and reports suspicious constructs, such as Printf calls whose arguments do not align with the format string. A linter is a tool giving coding style feedback and suggestions. By default, this extension uses the official golint as a linter.

We can update go.lintTool to other linter, for example the more advanced Go Meta Linter. Take note of the warnings (and errors) shown under the Problems tab below.

After I have installed the Go extension, I proceed to start coding. The app that I am going to build in this learning journey is a YouTube video player app. So, let’s get started!

Storing Connection String in Environment

I will start with building a console application connecting to Azure PostgreSQL. To do that, I will connect to the database as follows.

package main;

const (
// Initialize connection constants.
HOST = "...postgres.database.azure.com"
DATABASE = "..."
USER = "..."
PASSWORD = "..."
)

...

function main() {
var connectionString = fmt.Sprintf("host=%s port=5432 user=%s password=%s dbname=%s sslmode=require", HOST, USER, PASSWORD, DATABASE)
...
}

The code above is quite straight-forward. However, it does have a problem. The configuration to connect to database is hard-coded in the code. This will thus reduce the code maintainability. Also, it is extremely bad to have password appearing in the code in plain text. To store such sensitive data, we need to store them as Environment variables, as recommended by the article Best Practice for Configuration File in Your Code.

In Powershell, there are two ways of setting Environment variable. The first one is a temporarily variable that lasts only as long as the Powershell session. The command is as follows.

> $env:CONNECTION_STRING = '...'

If what you are looking for is a permanent one, you can do as follows.

> [environment]::SetEnvironmentVariable("CONNECTION_STRING", "...", "User")

After setting the connection string in environment variable, we then can edit the earlier code to be something shorter as follows.

package main;



function main() {
var connectionString = fmt.Sprintf(os.Getenv("CONNECTION_STRING"))


}

Connecting to Database

After that, we can initialize the connection object.

package main

...

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

func main() {
var connectionString = ...

// Initialize connection string.
db, err := sql.Open("postgres", connectionString)
checkError(err)

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

...
}

The Ping() function verifies a connection to the database is alive and it will establish a connection if necessary. After that, we can use the database handler db to do CRUD operations, as demonstrated below.

Remember to allow the PostgreSQL access to Azure service and also your local IP if you need to access the database from your local machine.

Insert Data into Table

sqlStatement := "INSERT INTO table (column1) VALUES ($1);"
_, err = db.Exec(sqlStatement, "New Value")
checkError(err)

Read Data from Table

var id int
var column1 string

sqlStatement := "SELECT * from table;"
rows, err := db.Query(sqlStatement)
checkError(err)

defer rows.Close()

for rows.Next() {
switch err := rows.Scan(&id, &column1); err {
case sql.ErrNoRows:
fmt.Println("No rows were returned")
case nil:
fmt.Printf("Data row = (%d, %s)\n", id, column1)
default:
checkError(err)
}
}

Update Data in Database

sqlStatement := "UPDATE table SET column1 = $1 WHERE id = $2;"
_, err = db.Exec(sqlStatement, "New Value 2", 1)
checkError(err)

Delete Data from Database

sqlStatement := "DELETE FROM table WHERE id = $1;"
_, err = db.Exec(sqlStatement, 1)
checkError(err)

A YouTube Playlist Console App

With the codes above, we then can create a simple table to store our YouTube video URLs as a playlist, as shown in the following screenshot.

The console app allows user to choose video from the list and plays it on default browser.

To make the program to play the video on a browser, we then can make use of the code written by hyg (黄勇刚) which is to open a web page on the default browser of the machine, as shown below.

func openbrowser(url string) {
var err error

switch runtime.GOOS {
case "linux":
err = exec.Command("xdg-open", url).Start()
case "windows":
err = exec.Command("rundll32", "url.dll,FileProtocolHandler", url).Start()
case "darwin":
err = exec.Command("open", url).Start()
default:
err = fmt.Errorf("unsupported platform")
}

if err != nil {
checkError(err)
}

}

Playing YouTube video via console is cool, isn’t it? Next, we will see how we can make it to be web application so that we can have a better UI/UX.

To be continued in next article

References

1. Quickstart: Create an Azure Database for PostgreSQL server in the Azure portal;
2. Azure Database for PostgreSQL: Use Go language to connect and query data;
3. [StackOverflow] List all environment variables from command line;
4. Azure Database for PostgreSQL Server firewall rules;
5. PostgreSQL – CREATE Database;
6. [StackOverflow] How to switch databases in psql?;
7. [StackOverflow] How to read input from console line?;
8. [StackOverflow] Convert string to integer type in Go?;
9. [StackOverflow] Reading an integer from standard input;
10. [StackOverflow] How to exit from PostgreSQL command line utility: psql.

Spring 2019 Golang Self-Learning

As a web developer in C# and .NET ecosystem for seven years, I decided to learn Golang after I was introduced the Chang Sausheong’s book “Go Web Programming”.

Objectives

Learning a new language other than C#, especially a language which is not in .NET, gives me a new perspective on understanding web development. With powerful framework like ASP .NET Core, it’s easy for developers who build their web applications do not understand why the frameworks do things in certain ways and trade-offs in the frameworks.

ASP .NET Core makes developers’ life easier with Convention over Configuration concept. As long as we know the conventions of where everything is located and you place each component into its correct location, we can easily build a web application. However, this also hide too much details from us and eventually makes us hard to master web development. Hence, learning Golang helps me to gain a new perspective in understanding web development.

When I am working with ASP .NET Core, I normally deal with MS SQL Server or Azure SQL (which is cloud-based MS SQL Server). In the Golang web development, I switch to use PostgreSQL, which is also available on Microsoft Azure.

Contents

During the long holiday in Chinese New Year, I spent my time in reading the book and online resources regarding Golang web programming. With the new opening of library beside my workplace, I also get to spend time there after work to do some Golang coding.

After one to two months of self-learning, I managed to compile some notes about what I’ve learnt in this Spring. The following is a list of nine topics that I learned in the early-stage of my Golang journey.

  1. Getting Started: Connecting Golang Console App with Azure PostgresSQL Database;
  2. Getting Started: Making It a Golang Web Application;
  3. Deploy Golang App to Azure Web Apps with CI/CD on DevOps;
  4. Monitoring Golang Web App with Application Insights;
  5. RESTful Web Service in Golang and Front-end Served with VueJS;
  6. Authenticating Users in Golang Web App;
  7. Unit Testing with Golang;
  8. Containerize Golang Code and Deploy to Azure Web App;
  9. Leveraging Golang Concurrency in Web App Development.

Learning After Work

I’m working in Haulio, a startup incubated by PSA. Since it’s a startup and I am the CTO, I have no choice but to stay late in office most of the time, especially the period when we rushed for the launch of our mobile application in end of February.

Hence, whenever I have time, I will spend it on doing some Golang research and coding. Sometimes, however, I am just too tired and I would not write anything even though it’s weekend. Hence, I end up finishing all the nine topics only in mid of March.

I’m also very fortunate to have to share what I have learned with engineers in Azure Community Singapore. It turns out that many of them are using Golang too. Hence, that also gives me a great opportunity to learn from those experienced Golang developers. =)

Sharing how to deploy Golang web application with Azure DevOps Pipelines.

Again, I am not that hardworking to work on personal projects every day. Sometimes, I will accompany my Mom at home. Sometimes I will have dinner with friends. Sometimes, I will travel to overseas (I am writing this in Japan). Sometimes, I will also play computer games or simply just sleep at home. So ya, this self-learning project takes a longer time to complete.

Working on personal projects after work is stressful also. Yup, so the project involved in this self-learning is about creating a YouTube Re-Player to loop my favourite YouTube music to calm myself down. =P

[KOSD Series] Code Review and VSTS

KOSD, or Kopi-O Siew Dai, is a type of Singapore coffee that I enjoy. It is basically a cup of coffee with a little bit of sugar. This series is meant to blog about technical knowledge that I gained while having a small cup of Kopi-O Siew Dai.

kosd-vsts-azure.png

Code reviews are a best practice for software development projects but it’s normally ignored in startups and SMEs because

  • the top management doesn’t understand the value of doing so;
  • the developers have no time to do code reviews and even unit testing.

So, in order to improve our code quality and management standards, we decided to introduce the idea of code reviewing by enforcing pull requests creating in our deployment procedure, even though our team is very small and we are working in a startup environment.

Firstly, we set up two websites on Azure App Service, one for UAT and another for the Production. We enabled Continuous Deployment feature for two of them by configuring Azure App Service integration with our Git repository on Visual Studio Team Services (VSTS).

Secondly, we have two branches in the Git repository of the project, i.e. master and development-deployment. Changes pushed to the branches will automatically be deployed to the Production and the UAT websites, respectively.

In order to prevent that our codes are being deployed to even the UAT site without code reviews, we created a new branch known as the development branch. The development branch allows all the relevant developers (in the example below, we call them Alvin and Bryan) to pull/push their local changes freely from/to it.

git-flow-on-vsts.png

Once any of the developers is confident with his/her changes, he/she can create a new pull request on VSTS.

creating-pull-request.png
Creating a new pull request on VSTS.

We then proceed to make use of the new capability on VSTS, which is to set policies for the branches. In the policy setting, we checked the option “Require a minimum number of reviewers” to prevent direct pushes to both master and development-deployment branches.

branch-policies.png
Enabled the code review requirement in each pull request to protect the branch.

So for every deployment to our UAT and Production websites, the checking step is in place to make sure that the deployments are all properly reviewed and approved. This is not just to protect the system but also to protect the developers by having a standardized quality checking across the development team.

This is the end of this episode of KOSD series. If you have any comment or suggestion about this article, please shout out. Hope you enjoy this cup of electronic Kopi-O Siew Dai. =)

Exploring Azure Functions for Scheduler

azure-function-documentdb.png

During my first job after finishing my undergraduate degree in NUS, I worked in a local startup which was then the largest bus ticketing portal in Southeast Asia. In 2014, I worked with a senior to successfully migrate the whole system from on-premise to Microsoft Azure Virtual Machines, which is the IaaS option. Maintaining the virtual machines is a painful experience because we need to setup the load balancing with Traffic Manager, database mirroring, database failover, availability set, etc.

In 2015, when I first worked in Singapore Changi Airport, with the support of the team, we made use of PaaS technologies such as Azure Cloud Services, Azure Web Apps, and Azure SQL, we successfully expanded our online businesses to 7 countries in a short time. With the help of PaaS option in Microsoft Azure, we can finally have a more enjoyable working life.

Azure Functions

Now, in 2017, I decided to explore Azure Functions.

Azure Functions allows developers to focus on the code for only the problem they want to solve without worrying about the infrastructure like we do in Azure Virtual Machines or even the entire applications as we do in Azure Cloud Services.

There are two important benefits that I like in this new option. Firstly, our development can be more productive. Secondly, Azure Functions has two pricing models: Consumption Plan and App Service Plan, as shown in the screenshot below. The Consumption Plan lets us pay per execution and the first 1,000,000 executions are free!

Screen Shot 2017-02-01 at 2.22.01 PM.png
Two hosting plans in Azure Functions: Consumption Plan vs. App Service Plan

After setting up the Function App, we can choose “Quick Start” to have a simpler user interface to get started with Azure Function.

Under “Quick Start” section, there are three triggers available for us to choose, i.e. Timer, Data Processing, and Webhook + API. Today, I’ll only talk about Timer. We will see how we can achieve the scheduler functionality on Microsoft Azure.

Screen Shot 2017-02-05 at 11.16.40 PM.png
Quick Start page in Azure Function.

Timer Trigger

Timer Trigger will execute the function according to a schedule. The schedule is defined using CRON expression. Let’s say if we want our function to be executed every four hours, we can write the schedule as follows.

0 0 */4 * * *

This is similar to how we did in the cron job. The CRON expression consists of six fields. The first one is second (0-59), followed by minute (0 – 59), followed by hour (0 – 23), followed by day of month (1 – 31), followed by month (1 – 12) and day of week (0-6).

Similar to the usual Azure Web App, the default time zone used in Azure Functions is also UTC. Hence, if we would like to change it to use another timezone, what we need to do is just add the WEBSITE_TIME_ZONE application setting in the Function App.

Companion File: function.json

So, where do we set the schedule? The answer is in a special file called function.json.

In the Function App directory, there always needs a function.json file. The function.json file will contain the configuration metadata for the function. Normally, a function can only have a single trigger binding and can have none or more than one I/O bindings.

The trigger binding will be the place we set the schedule.

{
    "bindings": [
        {
            "name": "myTimer",
            "type": "timerTrigger",
            "direction": "in",
            "schedule": "0 0 */4 * * *"
        },
        ...
    ],
    ...
}

The name attribute is to specify the name of the parameter used in the C# function later. It is used for the bound data in the function.

The type attribute specifies the binding time. Our case here will be timerTrigger.

The direction attribute indicates whether the binding is for receiving data into the function (in) or sending data from the function (out). For scheduler, the direction will be “in” because later in our C# function, we can actually retrieve info from the myTimer parameter.

Finally, the schedule attribute will be where we put our schedule CRON expression at.

To know more about binding in Azure Function, please refer to the Azure Function Developer Guide.

Function File: run.csx

2nd file that we must have in the Function App directory is the function itself. For C# function, it will be a file called run.csx.

The .csx format allows developers to focus on just writing the C# function to solve the problem. Instead of wrapping everything in a namespace and class, we just need to define a Run method.

#r "Newtonsoft.Json"

using System;
using Newtonsoft.Json;
...

public static async Task Run(TimerInfo myTimer, TraceWriter log)
{
    ...
}

Assemblies in .csx File

Same as how we always did in C# project, when we need to import the namespaces, we just need to use the using clause. For example, in our case, we need to process the Json file, so we need to make use of the library Newtonsoft.Json.

using Newtonsoft.Json;

To reference external assemblies, for example in our case, Newtonsoft.Json, we just need to use the #r directive as follows.

#r "Newtonsoft.Json"

The reason why we are allowed to do so is because Newtonsoft.Json and a few more other assemblies are “special case”. They can be referenced by simplename. As of Jan 2017, the assemblies that are allowed to do so are as follows.

  • Newtonsoft.Json
  • Microsoft.WindowsAzure.Storage
  • Microsoft.ServiceBus
  • Microsoft.AspNet.WebHooks.Receivers
  • Microsoft.AspNet.WebHooks.Common
  • Microsoft.Azure.NotificationHubs

For other assemblies, we need to upload the assembly file, for example MyAssembly.dll, into a bin folder relative to the function first. Only then we can reference is as follows.

#r "MyAssembly.dll"

Async Method in .csx File

Asynchronous programming is recommended best practice. To make the Run method above asynchronous, we need to use the async keyword and return a Task object. However, developers are advised to always avoid referencing the Task.Result property because it will essentially do a busy-wait on a lock of another thread. Holding a lock creates the potential for deadlocks.

Inputs in .csx File and DocumentDB

latest-topics-on-dotnet-sg-facebook-group
This section will display the top four latest Facebook posts pulled by Azure Function.

For our case, the purpose of Azure Function is to process the Facebook Group feeds and then store the feeds somewhere for later use. The “somewhere” here is DocumentDB.

To gets the inputs from DocumentDB, we first need to have 2nd binding specified in the functions.json as follows.

{
    "bindings": [
        ...
        {
            "type": "documentDB",
            "name": "inputDocument",
            "databaseName": "feeds-database",
            "collectionName": "facebook-group-feeds",
            "id": "41f7adb1-cadf-491e-9973-28cc3fca57df",
            "connection": "dotnetsg_DOCUMENTDB",
            "direction": "in"
        }
    ],
    ...
}

In the DocumentDB input binding above, the name attribute is, same as previous example, used to specify the name of the parameter in the C# function.

The databaseName and collectionName attributes correspond to the names of the database and collection in our DocumentDB, respectively. The id attribute is the Document Id of the document that we want to retrieve. In our case, we store all the Facebook feeds in one document, so we specify the Document Id in the binding directly.

The connection attribute is the name of the Azure Function Application Setting storing the connection string of the DocumentDB account endpoint. Yes, Azure Function also has Application Settings available. =)

Finally, the direction attribute must be “in”.

We can then now enhance our Run method to include inputs from DocumentDB as follows. What it does is basically just reading existing feeds from the document and then update it with new feeds found in the Singapore .NET Facebook Group

#r "Newtonsoft.Json"

using System;
using Newtonsoft.Json;
...

private const string SG_DOT_NET_COMMUNITY_FB_GROUP_ID = "1504549153159226";

public static async Task Run(TimerInfo myTimer, dynamic inputDocument, TraceWriter log)
{
    string sgDotNetCommunityFacebookGroupFeedsJson = 
        await GetFacebookGroupFeedsAsJsonAsync(SG_DOT_NET_COMMUNITY_FB_GROUP_ID);
    
    ...

    var existingFeeds = JsonConvert.DeserializeObject(inputDocument.ToString());

    // Processing the Facebook Group feeds here...
    // Updating existingFeeds here...

    inputDocument.data = existingFeeds.Feeds;
}

Besides getting input from DocumentDB, we can also have DocumentDB output binding as follows to, for example, write a new document to DocumentDB database.

{
    "bindings": [
        ...
        {
            "type": "documentDB",
            "name": "outputDocument",
            "databaseName": "feeds-database",
            "collectionName": "facebook-group-feeds",
            "id": "41f7adb1-cadf-491e-9973-28cc3fca57df",
            "connection": "dotnetsg_DOCUMENTDB",
            "createIfNotExists": true,
            "direction": "out"
        }
    ],
    ...
}

We don’t really use this in our dotnet.sg case. However, as we can see, there are only two major differences between DocumentDB input and output bindings.

Firstly, we have a new createIfNotExists attribute which specify whether to create the DocumentDB database and collection if they don’t exist or not.

Secondly, we will have to set the direction attribute to be “out”.

Then in our function code, we just need to have a new parameter with “out object outputDocument” instead of “in dynamic inputDocument”.

You can read more at the Azure Functions DocumentDB bindings documentation to understand more about how they work together.

Application Settings in Azure Functions

Yes, there are our familiar features such as Application Settings, Continuous Integration, Kudu, etc. in Azure Functions as well. All of them can be found under “Function App Settings” section.

Screen Shot 2017-02-18 at 4.40.24 PM.png
Azure Function App Settings

As what we have been doing in Azure Web Apps, we can also set the timezone, store the App Secrets in the Function App Settings.

Deployment of Azure Functions with Github

We are allowed to link the Azure Function with variety of Deployment Options, such as Github, to enable the continuous deployment option too.

There is one thing that I’d like to highlight here is that if you are also starting from setting up your new Azure Function via Azure Portal, then when in the future you setup the continuous deployment for the function, please make sure that you first create a folder having the same name as the name of your Azure Function. Then all the files related to the function needs to be put in the folder.

For example, in dotnet.sg case, we have the Azure Function called “TimerTriggerCSharp1”. we will have the following folder structure.

Screen Shot 2017-02-18 at 4.49.11 PM.png
Folder structure of the TimerTriggerCsharp1.

When I first started, I made a mistake when I linked Github with Azure Function. I didn’t create the folder with the name “TimerTriggerCSharp1”, which is the name of my Azure Function. So, when I deploy the code via Github, the code in the Azure Function on the Azure Portal is not updated at all.

In fact, once the Continuous Deployment is setup, we are no longer able to edit the code directly on the Azure Portal. Hence, setting up the correct folder structure is important.

Screen Shot 2017-02-18 at 4.52.17 PM.png
Read only once we setup the Continuous Deployment in Azure Function.

If you would like to add in more functions, simply create new folders at the same level.

Conclusion

Azure Function and the whole concept of Serverless Architecture are still very new to me. However, what I like about it is the fact that Azure Function allows us to care about the codes to solve a problem without worrying about the whole application and infrastructure.

In addition, we are also allowed to solve the different problems using the programming language that best suits the problem.

Finally, Azure Function is cost-saving because we can choose to pay only for the time our code is being executed.

If you would like to learn more about Azure Functions, here is the list of references I use in this learning journey.

You can check out my code for TimerTriggerCSharp1 above at our Github repository: https://github.com/sg-dotnet/FacebookGroupFeedsProcessor.