Connecting Azure Functions with PostgreSQL

Introduction

In cloud computing, Azure Functions have become a popular choice for developing serverless applications. These lightweight, event-driven functions offer scalability and cost-efficiency, making them an ideal solution for various use cases. Recently we got one such requirement where we need to read the PostgreSQL database view and need to update data in Dynamics 365.

We used Azure function to connect with PostgreSQL, a powerful open-source relational database management system. In this blog post, we will see a sample code to connect with PostgreSQL from Azure Function.

Details

Our requirement was to Fetch data from PostgreSQL daily and update Dynamics 365 CE data, so we used Azure Timer Trigger Function to connect data. Below are the high-level steps to set up a connection

Step 1. Set Up Azure Timer Trigger Function App.

Step 2. We need to Add a reference to Npgsql (https://www.nuget.org/packages/Npgsql/).

string connectionstring = string.Empty;
string viewquery = string.Empty;

string host = Environment.GetEnvironmentVariable("Host");
string port = Environment.GetEnvironmentVariable("Port");
string database = Environment.GetEnvironmentVariable("Database");
string username = Environment.GetEnvironmentVariable("User");
string password = Environment.GetEnvironmentVariable("Password");
string timeout = Environment.GetEnvironmentVariable("Timeout");

//prepare connection string
connectionstring = "Host=" + host + ";Port=" + port + ";Database=" + database + ";Username=" + username + ";Password=" + password + ";Timeout=" + timeout;

viewquery = "select * from <ViewName>";
using(NpgsqlConnection connection = new NpgsqlConnection(connectionstring)) {
  try {
    logger.LogInformation("Opening connection to Postgres...");
    connection.Open();
    logger.LogInformation("Connected to Host:" + host);
    logger.LogInformation("Feteching data from <ViewName>");

    using(var command = new NpgsqlCommand(viewquery, connection)) {

      using(var reader = command.ExecuteReader()) {
        //PostgreSQLView is class where we have properties that we want to fetch from view
        List < PostgreSQLView > list = new List < PostgreSQLView > ();

        while (reader.Read()) {
          PostgreSQLView tbl = new PostgreSQLView();
          tbl.id = reader.GetInt32(0).ToString();
          tbl.name = reader.GetString(1).ToString();
          list.Add(tbl);

        }
      }
    }

    connection.Close();
  } catch (Exception ex) {
    logger.LogInformation(ex.Message);

  }

}

In the above code, I have declared variables for storing the connection string, view query, and environment variables (host, port, database, username, password, and timeout). I will be getting values for these variables from Azure Functions settings.

Once the connection string is constructed by concatenating the individual components retrieved from the environment variables, I am using viewquery variable to store the SQL query string.

First need to set up a connection so that inside the using statement, a new instance of NpgsqlConnection is created with the connection string. The using statement ensures that the connection is properly closed and disposed of after it is no longer needed.

Finally, I have used command.ExecuteReader() to execute the query and obtain a NpgsqlDataReader object; from there, we can get individual fields based on the structure. 

Summary

Once we have data from view, we can connect to Dynamics 365 and update data based on the identifiers. You can check our earlier blogs for Azure Function.