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?

In this sample I’m going to walk through the steps necessary to design a model first database using EntityFramework Core, connect to an Azure PostgreSQL database and provide simple CRUD (create, read, update, delete) functionality as a REST-style http endpoint.

Prerequisites

Create a new PostgreSQL database server in the Azure Portal

Image for post
Image for post

For our demo we can choose the cheapest option with the Basic SKU, 1 core and 5 GB storage:

Image for post
Image for post

Once the database server has been successfully deployed go to Connection strings and copy the value of ADO.NET:

Image for post
Image for post

Next add your client IP address to the firewall rules so you are able to later connect to the database server from your local machine:

Image for post
Image for post

Create a new database with Azure Data Studio

Image for post
Image for post

Go back to connections, click on Add Connection, enter the details from the connections string and click on Connect:

Image for post
Image for post

When connected click on New Query, enter

CREATE DATABASE ServerlessDemo

and click on Run. On first access the server takes some seconds to finally create the new database:

Image for post
Image for post

Let’s write some code!

To use EntityFramework Core in a Function app we need some packages from Nuget. So let’s add these:

  • Microsoft.Azure.Functions.Extensions, to we can use Dependency Injection
  • 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

Important: At the time of writing this only works with version 3.1.4 and NOT with 5.x of the EntityFramework and Npgsql packages!

Step 1: Code first model

Step 2: Database Context

Step 3: Service

To make our code testable let’s introduce an interface for our service with the CRUD methods:

The implementation of the service gets the DbContext injected:

Implement the CreateAsync method:

Implement the GetAsync method:

Implement the UpdateAsync method:

Implement the DeleteAsync method:

Step 4: Configure dependency injection

For dependency injection we need a new class Startup.cs in the root namespace. Here we can retrieve the connection string and register both the context and the service:

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

Implement a IDesignTimeDbContextFactory to help entity framework discover our context:

Next we need to make sure our project’s dll is in the right spot for entity framework. So add this copy task to the project’s csproj:

Now we can finally run our first migration and update the database in the Package Manager Console. Also note that I added the connection string as an environment variable as EntityFramework doesn’t now it from our local.settings.json:

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

Step 6: Http Functions

Function to create a new customer

Function to get a list of customers

Function to update a customer

Function to delete a customer

Step 7: Test with Postman

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

Find the full source code on GitHub: GrillPhil/ServerlessSQLDemo (github.com)

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