While Power Apps can be incredibly effective at providing a means to creating and deploying a front-end web application quickly, the back-end support has traditionally been kept behind a paywall. Those who first start learning Power Apps will likely utilize one of two native connectors to create a pseudo-database for their Power App: SharePoint Lists and Excel. However, there are two main drawbacks to each of these.
Using Excel as a database is highly unstable and your Power App will quickly run into issues as multiple users try to utilize your app for business purposes. Excel files can also be locked for editing when being utilized by the app for up to 6 minutes, even if they are saved on OneDrive / OneDrive for Business. SharePoint and Excel also both have limitations when delegating functions to the back-end, leading to partial data being read into the app when the data exceeds 500 rows. While this can be manually increased to 2,000 rows, anything over 2,000 requires additional finessing.
As Power App Power Users get more experienced with web development, they may learn about SQL databases, which offer faster performance compared to Excel / Lists, allow for near limitless data storage, and allow for relationships to be built between tables for those who want to pull data on the back end for additional analysis. Unfortunately, Power App requires a premium subscription to utilize most SQL database connectors. On top of this, all users of the application will also need premium subscriptions to utilize the app if it uses premium connectors, exponentially raising the cost of maintaining the app, and requiring the developer to work with licensing teams to ensure all users have premium licenses to utilize the app.
This is where the combination of two different technologies can prove useful: API’s and HTTP requests.
Power Automate allows a user to generate an HTTP request if the developer has a premium subscription. Power Automate Flows are run based on the owner of the flow, regardless of who initiated the flow. As such, the Power App trigger can be used to start a flow, have it run on the developer’s account, then return the data back to the Power App being used by a non-premium user.
On the API side, a Django Ninja API, or other API technology, can be developed to accept POST and GET requests from the Power Automate / App initiated HTTP requests that save data to a database or retrieve data for the app. This API can be hosted on a free cloud storage provider, such as render.com or PythonAnywhere.com. Each of these providers includes a SQL database as part of their free tiers, allowing seamless integration between the API and the database. Additional security measures can be added as well on the API side, such as including an authentication token in the header. A static token can be utilized for simple security, or a more advanced authentication set up could be created utilizing multiple API endpoints for session based authentication for additional security. Other database solutions can also be utilized, such as Neon.tech and cockroachDB if additional security is needed.
The API can be structured in such a way that it receives and returns data in JSON format, which can then be read in by the Power App and rendered either as a table, a gallery, or as a collection.