Running Serverless Apollo GraphQL on AzureFunctions with cheap Azure Blob Table database(s)

leonardo-ramos-CJ4mbwSK3EY-unsplash.jpg

Merry Christmas holidays and happy new years.

This is a bit of a holiday reading, I wanted to start by give you the sense of what I’ve been tinkering as an experiment, and it looks increasingly like this is going into production before 2019 ends.



Plan

  • Have

    • I have a bunch of Azure storage Tables (about 70k rows of cheap database - at $1 / month storage) - they are spread out across several containers but for the basic purposes - I think of them as independent tables that lack proper relationships defined between them.
      I think that’s appropriate to describe most no-sql databases.

    • Azure Functions based CRUD REST APIs wraps around the Azure Blob Table - problem - no good caching or relationship mechanism. I wasn’t a big fan of keep rolling out CRUD REST endpoints, feeling that I should try find a different way.

  • Idea

    • Run a GraphQL server on Azure Functions

  • Learn:

    • If you want to run GraphQL on Azure Functions - there’s a dotnet way and there’s a NodeJS way

    • The dotnet version

      • https://www.tpeczek.com/2019/05/serverless-graphql-with-azure-functions.html

      • https://medium.com/@seekdavidlee/eklee-azure-functions-graphql-a-serverless-graphql-implementation-on-azure-484611c3680b

      • I’ve personally made a stack decision to stick to NodeJS/TypeScript/JavaScript, so I won’t be exploring the dotnet server.

    • The NodeJS version

      • A quick look around will send you to Apollo Server which has done 3 videos with Microsoft Azure/Channel9 on getting started, running on AppService, and running on Azure Functions (on consumption).

      • Part 1 https://www.youtube.com/watch?v=7R33hGFV4f0

      • Part 2 https://www.youtube.com/watch?v=Mt4bBpOdwyE

      • Part 3 https://www.youtube.com/watch?v=unUeFApHeT0

  • Write

    • Steps after Apollo + AzureFunctions 3-part videos.

    • Resolving GraphQL entities to Azure Blob Table

    • Resolving Relationships

    • Securing with Azure AD

    • Deploying to AzureFunctions

    • Reroute via Azure Function proxy

  • Future

    • Switch to Apollo RESTDataSource

    • Apollo Client with Angular and Observable bindings

    • Apollo Server with Redis cache



Learn

After the three videos, particularly the first and the third - to understand GraphQL server, and hosting it on AzureFunctions, you’ll end up with a working graphql server running on localhost showing you books array from memory. Getting here after watching three videos was extremely fast ~30minutes. Very surprising how much shortcut we took to get here this fast.

const typeDefs = gql`
    type Book {
        title: String
        author: String
    }
    type Query {
        books: [Book]
    }
`;

const resolvers = {
    Query: {
        books: () => books,
    },
};

let books = [
    {
        title: "A",
        author: "Mark B"
    },
    {
        title: "C",
        author: "John D"
    }
];

const playgroundSettings: any = {
    "schema.polling.enable": false
};
const server = new ApolloServer({ 
    typeDefs, 
    resolvers, 
    playground: {
        settings: playgroundSettings
    },
    debug: false 
});
export default server.createHandler();


A really fancy thing Apollo did was to put the graphql endpoint on POST, and run the playground test environment on GET. Pointing a browser to the same endpoint will show the playground.

Here is the playground running the sample books query. Debug works wonderfully running in localhost.

By default, the playground will do continuous polling on the endpoint. That’s going to keep the Function awake and incur a lot of costs. It might also keep bumping into any local debug breakpoints. So let’s turn that off. I also set debug to false.


Write - Resolving Azure Table

Next, we need to extend from here. My original AzureFunctions has REST endpoints that calls Azure storage Tables via the Azure.Storage SDK. So bring those in.

import { ApolloServer, gql } from "apollo-server-azure-functions"; 
import * as azure from "azure-storage";

And start to switch out the code

const typeDefs = gql`
    type Flow {
        RowKey: String
        name: String
        displayName: String
        critical: Boolean
        environmentName: String
        state: String
    }
    type Query {
        flows: [Flow]
    }
`;

const resolvers = {
    Query: {
        //books: () => books,
        flows: (parent,args,context) => azureTableQuery('flows'),
    }
};

const tableService = azure.createTableService();
const partition = "xxxx-partition-key";

const azureTableQuery = (table) => {
    let pRows = new Promise((resolve, reject)=>{

        let query = new azure.TableQuery().where('PartitionKey eq ?', partition);
        tableService.queryEntities(table, query, null, (error, result, response)=>{
            if(error){
                reject(error);
            }
            else {
                resolve(response.body['value']);
            }
        });
    });
    return pRows;
}

Notes: Azure.Storage does old style callbacks, so I’m wrapping them in a Promise that I can resolve. When I resolve - I want the raw JSON from the service, not the ‘tweaked’ JSON in result (it does weird things to your JSON entities, that’s a blog for another time).

A really nice thing about Apollo Server resolve function is that it knows how to handle promises naturally. So this tweak basically is me saying - hey, go fetch from Azure Storage Table from the ‘flows’ container.

To run this locally, we’ll need to store an Azure storage key in local.settings.json

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "",
    "FUNCTIONS_WORKER_RUNTIME": "node",
    "MyStore": "DefaultEndpointsProtocol=https;AccountName=mystore;AccountKey=FOOxxx"
  }
}

We can reference this like so:

const tableService = azure.createTableService(process.env["MyStore"]);

Write - Resolving relationships

const azureTableRetrieve = (table, key) => {
    return new Promise((resolve, reject)=>{
        tableService.retrieveEntity(table, partition, key, null,
        (error, result, response)=>{
            if(error){
                reject(error);
            }
            else {
                let entity: any = response.body;
                resolve(entity);
            }
        });
    });
}

const typeDefs = gql`
    type Environment {
        RowKey: String
        name: String
        displayName: String
        isDefault: Boolean
    }
    type Flow {
        RowKey: String
        id: String
        name: String
        displayName: String
        critical: Boolean

        environmentName: String
        ref_environment: Environment
        state: String
    }
}`;

const resolvers = {
    Query: {
        environment: (parent,{name},context) => azureTableRetrieve('environments', name),
        flows: (parent,args,context) => azureTableQuery('flows'),
        flow: (parent,{name},context) => azureBlobTableRetrieve('flows', name)
    },
    Flow: {
        ref_environment: (parent,args,context) =>
          azureTableRetrieve('environments', parent.environmentName),
    }
};

Notes:

I call “ref_” because I’m terrible at making property names. Lots of fields like “environmentName” or “environment” are already taken. It got too hard picking new names, I decided to call all entity references ref_

Deploy to Azure Functions

Need to deploy two parts:

Friends don’t let friends right click to deploy unless another friend put it in the right click menu. In that case, please don’t make me choose my friends.

Also, right click and upload local settings file to send our MyStore setting into Azure functions.

That’s two right clicks and deployment done!

Connect Azure Function proxy

My existing API routes are already covered with Azure Functions proxy. So I added a new route that takes graphql over to this separate Azure Functions. I also bury the functionkey in the route so users can’t see it.

Future

There’s a bunch more ideas for the future.

  • Explore federated authentication and user level security.

  • Figure out why Azure Storage SDK next generation doesn’t do Tables

  • Switch from using SDK to Azure Storage REST and use Apollo’s excellent RESTDataSource implementation instead (it already implemented HTTP caching, pagination, top, etc)

  • implement mutations, especially e-tag handling.

  • implement server side Redis cache

  • Implement Apollo Client to fetch data for my existing Angular front-end

  • Implement client side cache and observable pattern

Let me know if you find this interesting and want to follow along. I wrote this mostly as documentation for what I’m doing and the incremental changes I’m making as I go.

Redirecting SharePoint list's NewFormUrl and EditFormUrl to Power Apps

In this scenario, we have a Power Apps app that takes a query param() “AssetID” and determines whether the app should create a new item or update an existing item.

We want to change the SharePoint default New or Edit items on a modern SharePoint list to go to our App. The app is a landscape canvas application and not an integrated portrait application (so we can’t use SharePoint form setting to switch this).

Because SharePoint’s New and Edit forms must be server relative pages, we can create a redirect page.

Steps

  1. Use Param in Power Apps

  2. Create a simple redirect page

  3. Change SharePoint’s NewFormUrl and EditFormUrl to this redirect page

  4. Test: Redirect to PowerApps

  5. References

Use Param in Power Apps

https://powerapps.microsoft.com/en-us/blog/powerapps-deep-linking/

Create a simple redirect page

Create a redirect ASPX (HTML) page and upload it into the SitePage library. I saved this as "AssetEditForm.aspx"

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<%@ Page Language="C#" %>
<%@ Register tagprefix="SharePoint" namespace="Microsoft.SharePoint.WebControls" assembly="Microsoft.SharePoint, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<html dir="ltr" xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta name="WebPartPageExpansion" content="full" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Asset Form</title>
<meta http-equiv="X-UA-Compatible" content="IE=10" />
<SharePoint:CssRegistration Name="default" runat="server"/>
<script type="text/javascript">

function getQueryVariable(variable) {
    var query = window.location.search.substring(1);
    var vars = query.split('&');
    for (var i = 0; i < vars.length; i++) {
        var pair = vars[i].split('=');
        if (decodeURIComponent(pair[0]) == variable) {
            return decodeURIComponent(pair[1]);
        }
    }
//    console.log('Query variable %s not found', variable);
}
var play = "https://apps.powerapps.com/play/e3fdef18-2be5-4d25-ba04-3edf6411c0aa?tenantId=afa78dff-85bb-46f3-b036-b43bcf79c497";
var id = getQueryVariable("ID");
if (id) {
    window.location.replace(play+"&AssetID="+id);
}
else {
    window.location.replace(play);
}
</script>
</head>
<body>
<form id="form1" runat="server">
</form>
</body>
</html>


Change SharePoint’s NewFormUrl and EditFormUrl with PnP-PowerShell

Connect-PnPOnline -url https://johnliu365.sharepoint.com/
$list = Get-PnPList "Asset"
$list.ContentTypes[0]

# assuming list content type 0 == Item
# otherwise, you need to set this on the correct list content type

$list.ContentTypes[0].EditFormUrl = "SitePages/AssetEditForm.aspx"
$list.ContentTypes[0].NewFormUrl = "SitePages/AssetEditForm.aspx"
$list.ContentTypes[0].Update($false)

# no update child content types
# you may have to set $true if you have content types in your list

$context = Get-PnPContext
$context.ExecuteQuery()


Redirecting Magic

This will change the “New”, “Edit” behaviour on the SharePoint modern or classic list. Also, this will work in the edit menu. So if you want to redirect your users to the new Power Apps experience, this is one way to change the link everywhere.

Reference Reading

Please also read April Dunnam’s two part series on how to customize SharePoint via modern ListView JSON

True Governance of the Power Platform

track-2.jpg

It’s very early Sunday morning and I’ve just returned from an red-eye flight home from a week spent in Perth with Paul, Ashlee and Terrie Culmsee, meeting their many clients, participating in Perth App in a Day, and talking about PowerApps, Flow and Power Platform Governance.

It is testament to Paul’s nurturing of his clients that every company that I talk to on this trip have the same forward thinking mentality.

Many of the companies I had talked to online, or here in Sydney also shares these same traits. They want to implement proper governance of the power platform.

You might be on the same journey - I wish you the best, and I want to support you in this quest.

True Governance is about creating a partnership

The scenario is the same, over and over and over. Business wants agility, they want applications that traditional IT struggles to deliver.

IT already struggle with supporting existing applications created by Business - they worry this is yet another thing they need to support - these “citizen developed apps” will fall into their laps, not following best practices, not documented, and yet highly business critical.

Power Platform is an opportunity, to ride this wave and fix the partnership problem in your business.

True leadership, are the managers seizing this unique opportunity to (re)engage Business and IT

Because traditional IT has become the department of “No” - modern business units and IT doesn’t really want to talk. And in the last few years, many businesses rode the Microsoft Power Platform submarine to get applications developed under the radar to reach critical mass first. Always easier to ask for forgiveness than blessing.

In this paragraph, I want to speak to you - the business. You must understand, there is an virtual ceiling you are about to hit. Let me explain.

See - your empowered citizen developers are learning as they go, their first apps may not be that fantastic, while they solve critical business problems - there may be bugs and we see there will always be more incremental improvements needed.

As the number of Power applications you create increases, your limited number of citizen developer will reach capacity. Without being able to properly support these apps, they can’t make more apps.

You still need more apps. But your citizen devs’ hands are now full. Worse, what happens if they take a role elsewhere? Who can support these apps?

The correct answer is, business should take a cost code to IT and ask IT to support these apps. IT should be paid to build internal capacity to understand, administer, and govern the Power Platform.

Ideally, someone that understands the Power Platform should lead this team and effort. They are the bridge that talks to Business and IT. The ultimate navigator that will unite the business and IT.

Look across to any successful enterprises now scaling to hundreds and thousands of Power Apps and Flows. This pattern is the same. Over and over.

IT must realize that part of governance is this great opportunity - go talk to your business. Find the super maker that is helping with adoption, and able to put together business plans that will include a maintenance support fee to IT. Stop being “free support” (see my previous blog post) and start being part of value generation in your business.

An emerging trend is that the internal super Power Platform maker joins the IT side of the business to be that navigator. Governance and adoption tools (I have a list near the end) support the quest of this navigator. Business pays IT to support and maintain more Power Apps and Flows.

Do you think this is not possible? This is a fairy tale? I saw these relationships, these bridges forming everywhere. Is this not your experience? Have a look around.

If you are an IT manager, rather than look at the Power Platform with fear, close your eyes, and reopen them and see it as the biggest opportunity you have ever had in the last decade.

A low code platform the Business wants, with tools and reports for IT to properly function and support this platform. Don’t squander this and drive your business away to some real Shadow IT platform and now you can’t even monitor that.

Governance tools are available - but true governance is People willing to talk

Business and IT not talking to each other is a problem. Here, is an opportunity to solve that. Take your off-shored IT back in-house. Be part of value generation.

If you want partnerships, I will help. There are many in the community that want to help you. Many Power platform champions are literally living this reality and working in this role. Borrow their job template and make it yours, initate the conversation - take it to your manager in business or in IT and say, hey, let’s do this.

I wanted to list a series of tools available. Some has (custom) tag and would need self assembly.

Build tools, build bridges

Disclaimer - I build Power Studio and Power Clarity. The point is, there are tools to provide governance on this platform. This is FAR better than choosing a different tool that IT has no means of providing any meaningful governance.

It would be somewhat easy for me to run purely on fear and say buy “my tools” - they will lock everything down and give you governance. Build a wall. You need a wall. Buy my wall.

If some snake oil salesman comes to you and say that, I want you to understand, real governance is not a wall. There is already enough of a chasm between business and IT.

Real governance is an opportunity to take this platform and transform your apps, yourself, your business and the relationships in your business. It’s hard, potentially very rewarding work.

What do you build your governance for?

Today, governance is a word thrown around without a care of what it actually means. We need to do governance - for what? No that’s all, we just need to do governance.

I wanted to share an article from Paul Culmsee. Those that sees governance as the goal, and not the means to an end, there’s something we all have to let go. Why do we implement governance? What outcome do we desire?

https://medium.com/@paulculmsee/how-to-doom-office365-governance-over-and-over-again-9feede8ef14a

If you have never thought about why you are implementing governance, and you don’t know what your end goal is, then take this quest:

We build governance, because our IT and Business must form a partnership. Our entire governance strategy is to make that partnership work.

The choices are yours, but I think you shouldn’t take forever to think about them

Do the right thing. Chances like this don’t come that often. Don’t look back and think, ah, I didn’t take that opportunity and initiate that conversation, and a decade later, I’m still only a cost center, still fighting shadow IT, and the board still wants to offshore my team.

That’d be unfortunate.

The Flow Studio journey - first lesson - seed money and salary

stanislav-kondratiev-MdexOj4D-MU-unsplash.jpg

I wanted to write a blog post because I don’t know, I guess you are a technical person that reads my blog, and you might be interested in my journey from a two decade consultant into a sole business owner building a product company.

It’s pretty exciting. Everyone should try this.

This blog post

I was talking to Elaine as she’s visiting Sydney and I was explaining a very critical lesson I’m in the middle of learning, now that my company “Flow Studio Solutions” is 3 months old.

I was also recently talking to Brett and Sandy who gave me a lot of insights into an early day of a founder. Brett asked me several questions and boy he’s a wizard how does he know that’s exactly what I have been doing the whole time.

And there is FIRST LESSON I’m rapidly learning.

When you decided to take part time or taking some time off and start doing your business, you have this tendency to think Oh, the business isn’t taking in enough income, so I’ll just not take a salary for this month. I’ll live off my savings in my personal account.

This is Brent’s wise words to me. Paraphrased

I modified those words into this trick - with the bank details added in.
Please do over. It’s a most simple trick.

  1. Make your business bank account. You should have this anyway, otherwise people can’t pay you.

  2. Transfer money from your savings that you intend to live on, into your business

  3. Give yourself a salary and sent that back to your personal savings every month (to pay your bills)

Now suddenly, you can claim expenses, tax deductions and have a clear idea how much money the business needs to continue paying you that salary.

Also, when you want to seek funding or grants - it is very clear to proof how much does the business need to operate, and how much existing seed money had already been injected into this business.

You can’t proof “oh here’s my savings account I need about $5000 a month” that isn’t your business, that’s your lavish/hermit lifestyle, nobody cares about that but you. But if you had your business sending you a $5000 salary, you can easily provide that as clear proof that’s the cost of the business, every month.

So, that is the first lesson.

Move your savings into your business, pay yourself a living salary, and work on your business, not in your business.

I’m grateful for Brent and Elaine for these conversations.

History

For the details which might make sense why there are different dates in the Flow Studio journey. He’s a rough timeline.

2018 April - Flow Studio the product started as a side project
2018 November - Started accepting Stripe subscriptions
2019 June - Until June, Flow Studio operated under a sole trader business registration in Australia
2019 July - incorporated Flow Studio Solutions - a product company that does a side consulting to build the product.
2019 October - first quarter. This update.

M365 Developer Bootcamp and M365 Saturday Sydney

There are two events coming up soon in Sydney regarding the SharePoint, Office 365 and Microsoft 365 community and ecosystem.

M365 Developer Bootcamp Sydney

The M365 Developer Bootcamp is the return of the popular Global Office 365 Developer Bootcamp. This is when the Office 365 MVPs and community experts help everyone and anyone that wants to have a crack at solving any Office 365 (and M365) related coding challenges.

https___cdn.evbuc.com_images_71120587_230532921863_1_original.jpg

Whether it is to build a SPFx webpart, extend Microsoft Teams, explore Microsoft Graph. The format this year will be lighter on talks, and more focused on helping you pick a challenge you want to do, and help you complete that project.

Friday, October 18 at the Microsoft Reactor in Level 10, 11 York Street, Sydney (above Wynyard Station in the Sydney Startup Hub).

https://www.eventbrite.com.au/e/global-microsoft-365-developer-bootcamp-sydney-2019-tickets-71607805875

M365 Saturday

The SharePoint Saturday / O365 Saturday Sydney returns this year as M365 Saturday.

Administrators, end users, architects, developers, and other professionals that work with Microsoft 365 Technologies will meet for the 11th SPS Events Sydney event!

This event is aimed to cover best practices, product/feature usage, and getting the most from the platform and discovering what is possible rather than deep diving into "how to" development topics. Developer topics will be covered more in the M365 Developer Bootcamp above.

Saturday, October 26 at the Microsoft Reactor

http://www.spsevents.org/city/Sydney

Call for speakers is still open for M365 Saturday

https://sessionize.com/m365-saturday-sydney-2019


Help us spread the word, and let us know if you would like to present something!