Image for post
Image for post
Original photo by Jan Antonin Kolar on Unsplash

Serverless relational code first database apps in Azure

How to built a cloud native app with Azure Functions to create, design and connect to a Azure Database for PostgreSQL using .Net Core and EntityFramework Core

Philipp Bauknecht
Nov 29, 2020 · 5 min read

Why?

Azure Functions are a lightweight, inexpensive and scalable alternative to write backend functionality. If you’ve written your backends so far in ASP.NET (Core) and connected to your relational database using EntityFramework (Core) you might wonder if this also works in a C# Function app? Turns out yes with some quirks!

Prerequisites

Create a new PostgreSQL database server in the Azure Portal

Search for Azure PostgreSQL in the Azure Marketplace to create a new database server. Make sure to pick the offer by Microsoft with the single server option:

Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post

Create a new database with Azure Data Studio

Start Azure Data Studio, search and install the PostgreSQL extension by Microsoft:

Image for post
Image for post
Image for post
Image for post
CREATE DATABASE ServerlessDemo
Image for post
Image for post

Let’s write some code!

With the database ready in the cloud we can finally start with the app by creating a new empty C# Azure Functions app in Visual Studio.

  • Microsoft.EntityFrameworkCore (3.1.4)
  • Microsoft.EntityFrameworkCore.Design (3.1.4)
  • Microsoft.EntityFrameworkCore.Tools (3.1.4)
  • Npgsql.EntityFrameworkCore.PostgreSQL (3.1.4), PostgreSQL provider for EntityFramework Core

Step 1: Code first model

Let’s create a simple model of a customer in a new customer namespace:

Step 2: Database Context

Now we need to add this model to a context deriving from DbContext to add this model as a table to our database:

Step 3: Service

To serve the data to the view (in our case a http function) we want to have a service that loads the model data from the DbContext and returns the data as a view model. So first the view model:

Step 4: Configure dependency injection

Dependency Injection helps us to wire everything together and also to provide settings like the connection string. The connection is best placed in local.settings.json (Azure Key Vault for production):

Step 5: Enable design-time context creation and adding a migration

So this is some wisdom from a great blog post by Jeff Hollan on this matter: https://dev.to/azure/using-entity-framework-with-azure-functions-50aa

$env:SqlConnectionString="Server=tcp:mySqlServerStuffxxx"
Add-Migration InitialCreate
Update-Database

Step 6: Http Functions

Now that the database is up to date, let’s also add 4 functions with http triggers to expose the customer CRUD operations as REST-style endpoints. Each function class and Run method needs to be modified from static to non static and we need to inject the ICustomerService into each function class.

Step 7: Test with Postman

With all functions in place our code is ready for some testing. So let’s spin up Postman to test our CRUD API:

Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post

Summary

So it’s absolutely possible to connect an Azure Function app written with C#/.NET Core to a relational database like Azure PostgreSQL or Azure SQL using EntityFramework Core with a code first approach. There are still some things that feel a bit rough like having to stick with Version 3.1.4 of EF Core (at least at the moment), having to implement IDesignTimeDbContextFactory and copying the dll after every build. But once this is set up it works really well!

medialesson

We help our customers design, architect, develop and…

Philipp Bauknecht

Written by

CEO @ medialesson. Microsoft Regional Director & MVP Windows Development. Father of identical twins. Passionate about great User Interfaces, NYC & Steaks

medialesson

We help our customers design, architect, develop and operate modern, intelligent, beautiful and usable apps on any platform powered by the Cloud, IoT and AI.

Philipp Bauknecht

Written by

CEO @ medialesson. Microsoft Regional Director & MVP Windows Development. Father of identical twins. Passionate about great User Interfaces, NYC & Steaks

medialesson

We help our customers design, architect, develop and operate modern, intelligent, beautiful and usable apps on any platform powered by the Cloud, IoT and AI.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store