Using PostgreSQL Document Databases with Azure Functions and Marten

With the appearance of managed PostgreSQL databases on Azure, we can now harness the simplicity of Marten to create document databases that Azure Functions can utilize.

Marten is on open source library headed by Jeremy Miller and offers simple document database style persistence for .NET apps which means it can also be used from Azure Functions.

Creating a PostgreSQL Azure Server

Log in to the Azure Portal and create a new “Azure Database for PostgreSQL”:

Creating a PostgreSQL Azure Server

You can follow these detailed steps to create and setup the PostgreSQL instance. Be sure to follow the firewall instructions to be able to connect to the database from an external source.

Creating a PostgreSQL Azure Server

Connecting and Creating a Database Using pgAdmin

pgAdmin is a tool for working with PostgreSQL database. Once installed, a new connection can be added to the Azure database server (you’ll need to provide the server, username, and password).

Connecting and Creating a Database Using pgAdmin

Once connected, right-click the newly added Azure server instance and choose Create –> Database. In this example a “quotes” database was added:

Connecting and Creating a Database Using pgAdmin

Notice in the preceding screenshot there are currently no tables in the database.

Reading and Writing to an Azure PostgreSQL Database from an Azure Function

Now we have a database, we can access it from an Azure Function using Marten.

First create a new Azure Functions project in Visual Studio 2017, reference Marten, and add a new POCO class called Quote:

public class Quote
{
    public int Id { get; set; }
    public string Text { get; set; }
}

Next add a new HTTP-triggered function called QuotesPost that will allow new quotes to be added to the database:

using System.Net;
using System.Net.Http;
using System.Threading.Tasks;
using Marten;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.Azure.WebJobs.Host;

namespace MartenAzureDocDbDemo
{
    public static class QuotesPost
    {
        [FunctionName("QuotesPost")]
        public static async Task<HttpResponseMessage> Run(
            [HttpTrigger(AuthorizationLevel.Anonymous, "post", Route = "quotes")]HttpRequestMessage req, 
            TraceWriter log)
        {
            log.Info("C# HTTP trigger function processed a request.");

            Quote quote = await req.Content.ReadAsAsync<Quote>();

            using (var store = DocumentStore
                .For("host=dctquotesdemo.postgres.database.azure.com;database=quotes;password=3ncei*3!@)nco39zn;username=dctdemoadmin@dctquotesdemo"))
            {
                using (var session = store.LightweightSession())
                {
                    session.Store(quote);

                    session.SaveChanges();
                }
            }

            return req.CreateResponse(HttpStatusCode.OK, $"Added new quote with ID={quote.Id}");
        }
    }
}

Next add another new function called QuotesGet that will read quote data:

using System.Net;
using System.Net.Http;
using Marten;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.Azure.WebJobs.Host;

namespace MartenAzureDocDbDemo
{
    public static class QuotesGet
    {
        [FunctionName("QuotesGet")]
        public static HttpResponseMessage Run(
            [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "quotes/{id}")]HttpRequestMessage req, 
            int id, 
            TraceWriter log)
        {
            log.Info("C# HTTP trigger function processed a request.");

            using (var store = DocumentStore
                .For("host=dctquotesdemo.postgres.database.azure.com;database=quotes;password=3ncei*3!@)nco39zn;username=dctdemoadmin@dctquotesdemo"))
            {
                using (var session = store.QuerySession())
                {
                    Quote quote = session.Load<Quote>(id);
                    return req.CreateResponse(HttpStatusCode.OK, quote);
                }
            }                        
        }
    }
}

Testing the Azure Functions Locally

Hit F5 in Visual Studio to start the local functions runtime, and notice the info messages, e.g.

Http Function QuotesGet: http://localhost:7071/api/quotes/{id}
Http Function QuotesPost: http://localhost:7071/api/quotes

We can now use a tool like Postman to hit these endpoints.

We can POST to “http://localhost:7071/api/quotes” the JSON: { "Text" : "Be yourself; everyone else is already taken." } and get back the response “"Added new quote with ID=3001"”.

If we use pgAdmin, we can see the mt_doc_quote table has been created by Marten and the new quote added with the id of 3001.

Querying Azure PostgreSQL with pgAdmin

 

Doing a GET to “http://localhost:7071/api/quotes/3001” returns the quote data:

{
    "Id": 3001,
    "Text": "Be yourself; everyone else is already taken."
}

Pricing details are available here.

To learn more about Marten, check out the docs or my Pluralsight courses Getting Started with .NET Document Databases Using Marten and Working with Data and Schemas in Marten.

To learn more about Azure Functions check out the docs, my other posts or my Pluralsight course Azure Function Triggers Quick Start .

Comments (2) -

  • Eniep Yrekcaz

    8/29/2017 2:52:25 PM | Reply

    FYI: You posted your password for this setup in your connection string above.

    • Jason Roberts

      8/30/2017 3:03:48 AM | Reply

      Hi Eniep - thanks for taking the time to notice this and write a comment. I'll normally reset passwords or delete Azure resources once I've written a post. Thanks again for taking the time to leave a comment Smile

Add comment

Loading