With a direct connection from PowerApps to SQL Server based off this link https://powerapps.microsoft.com/en-us/tutorials/limits-and-config/ I am guessing that powerApps is hitting a SQL Server API. This works fairly well as I can add fields to the database and somehow the powerApp knows about it and can transfer the data.
I wish to somehow put my own API in-between and be able to do some centralised business logic however I don't want to lose the flexibility that is currently there with the SQL API.
Any ideas how I would achieve this?
I am not sure I fully understand your question, could you please expand on what you mean by 'centralised business logic', and why that cannot be achieved with the OTTB SQL Server connection?
PS The link is broken in your post
If I understand your question, I think you were looking for something like Azure functions. See if this blog post is along the right track of what you are looking for... its not SQL but it addresses the "business-logic" aspect of your question I suspect...
I don't know what your limits are in your shop, but you've been suggested Azure Functions. I think that you might need more things than what Azure Functions brings to the table.
You might want to create your own custom restful webAPI and leverage PowerApps ontop of that WebApi. Host it within Azure and you're good to go. I really recommend you downloading and installing the latest SDK from Azure to incorporate it with Visual Studio if you go this route.
Yes a customAPI is basically the idea. Currently without using a customAPI you can hook a PowerApp up to a SQL Azure DB. Out of the box the app must be talking to a Azure API that handles basic CRUD operations. Whats really good about this is if I add a new field to the database the PowerApp designer immediately sees this.
What I find concerning is its all smoke and mirrors. I cannot find any documentation on this API and when an error occurs between the App and the database I will have no way to debug/log the error.
If I develop my own API I will lose the already existing CRUD functionality. What I'm really after is a way to wrap the hidden API to extend the existing functionality to add monitoring, logging and potentially to centralise some of the row level security.
Very interesting post you have written there. Definately does open doors to centralised logic.
Had a look at your latest post and realised you live in the same city as me.
What I struggle with is the lack of transparacy between the PowerApp and the SQL Azure Database.
When you have 100+ staff using the app things will go wrong (blame Murphy) and without logging/monitoring you would be relying on users to report the errors.
Small world @jmartin. Also notwithstanding that PowerApps cannot talk to SQL views and stored procs, all the more reason to use Azure functions for certain operations to get the level of granularity you are looking for.