What is the best database for PowerApps?

In this article we will give you all the information you need to choose the best database for PowerApps.

Your final PowerApp solution might be integrating to all kinds of third-party systems such as Facebook, Twitter, Salesforce etc to pull and push data. This article will be useful for people looking to start building a canvas PowerApp solution and a place to store the data from scratch. Scroll down for more information. 

Contact Us

    5 Minute Vlog – Database for PowerApps

    Unsure which database is right for your PowerApp? Watch the vlog on this topic of “What’s the best Database for PowerApps” by clicking the image below.
    Article by: Dougie Wood

    PowerApps Choosing a Database

    Design your database for PowerApps First

    A common mistake that is made when embarking on a PowerApps project is to focus on the user interface design before considering where the data will be stored.

    First agree where the data is going to be stored and what columns of data will be required before you start designing your Power App interface.

    Questions to ask when choosing a database for PowerApps

    There are multiple factors to consider to help you choose the best database for PowerApps. As part of your initial project meeting to discuss PowerApps ask your Team the following questions:

    • Do we have a tight budget?

      One of the key reasons businesses choose Power Apps is that it can be a very cost-effective way of developing software. However, if you rush into the project without considering where your data is going to be stored you could quickly find costs increasing.

      If cost is your primary reason for choosing Power Apps, then selecting to store your data in Microsoft Excel spreadsheet or SharePoint Lists could be the best option for you as these options are often included in the majority of Microsoft 365 licenses you likely already have.

    • How much time do we have?

      Another unique selling point of Power Apps is how quickly you can spin up a mobile friendly app. However, often not factored into this time is how long it takes to setup the backend database for storing the relevant data for the Power App.

      If you are looking to create proof of concept app to pilot quickly then setting up a new SQL server might take too much time. So, you might choose to set this up in Dataverse as it will be a much quicker setup process.

      Choosing Excel Spreadsheets or SharePoint Lists would also provide a very quick option for storing your data. This would be dependent on the amount of data you need to store.

    • How much data will be stored?

      There are a number of issues that can be caused by not correctly estimating how much data your Power App will be generating. This could lead to delegation issues in your Power Apps, issues with SharePoint views, load times opening Excel documents and so much more.

      You need to consider how many columns will be in your database and how much data will be submitted over the lifetime of the Power App.

      If you are expecting thousands of rows of data, then an Excel Spreadsheet would be sufficient. If you are expecting up to a hundred thousand rows of data, then a SharePoint List would be the chosen option. If you need more than that you would look to use Microsoft Dataverse.

    • How many users of the Power App?

      Knowing how many people will be using the Power App will help scope which database you choose for your solution.

      If it will just be a handful of users in a small Team that will be using the Power App, then a Microsoft Excel Spreadsheet can be used. However, this option would not scale well if the Power App was then shared with a wider audience of users.

      For small to medium sized organisations that want to build solutions that can scale to the wider group of users then selecting Microsoft Lists (SharePoint Lists) would be the best option.

      If you are building an enterprise solution, then choosing Dataverse should be considered.

    • How will the Power App be Supported?

      It is worth considering not only how and who will be supporting the Power App, but also the database that is supporting it in the background. If you do not have the IT resource or PowerApps Developers to support a new SQL server instance, then this might not be the best option.

      Using Microsoft Lists (SharePoint Lists) as your database for the Power App will make it much easier for non-technical employees to be able to maintain the data.

    Examples of Database for PowerApps

    You can watch the 5 minute webinar on this topic by clicking the picture below. You can also see some live examples of powerapps in our other blog resource.
    Content by: Dougie Wood

    PowerApps Choosing a Database

    Top 4 Microsoft Databases for PowerApps

    There are many different types of databases available on the market. We will not be discussing any of the third-party databases such as MySQL, Oracle, IBM Db2, SAP or AWS (Amazon Relational Database Service) as these are typically not used in combination with PowerApps.

    Below we have listed the most commonly used Microsoft products to store data with Canvas PowerApps.

    Microsoft Excel Logo

    Microsoft Excel Spreadsheet: Database for Power Apps

    A Microsoft Excel Spreadsheet hosted in either SharePoint Online or a OneDrive for Business is a viable option for small teams to store data. Using Excel would be the smallest scaled database.

    This would be sufficient for personal use Power Apps or if you only plan to store up to a few thousand rows of data. Main benefits of this option include being quick to setup and not requiring an Office 365 Admin to setup an area to store your data.

    Microsoft Lists Logo

    Microsoft Lists (SharePoint Lists): Database for Power Apps

    The most commonly chosen place to store data that we recommend is within Microsoft Lists. These Lists are essentially stored on SharePoint sites and are great for small (SMB) organisations to store up to one hundred thousand rows of data.

    Using Microsoft Lists within Office 365 as a DataSource is also a chosen option as SharePoint Online is included within the majority of Microsoft 365 licenses, meaning there is no additional cost.

    Microsoft SQL Server Logo

    Microsoft SQL: Database for PowerApps

    If you need to store hundreds of thousands or more rows of data to use with your PowerApp, then Microsoft SQL could be considered.

    However, it will have a few down sides including additional premium licenses that are required for end users to access SQL data. You can view license costs.

    Your PowerApp might be being built to interface with an existing SQL database. However, it can be a very costly process to setup and support a new Microsoft SQL environment for your new PowerApp.

    So, if you require a database to store large volumes of data then consider Dataverse (Formerly Common Data Service) instead.

    Microsoft Dataverse Logo

    Dataverse (Formerly Common Data Service): Database for PowerApps

    Dataverse is the best option if you need to store hundreds of thousands of rows of data.

    Choose Dataverse if you are looking for a database that will scale to enterprise levels over time.

    The key difference of Dataverse is that it is a relational database just like Microsoft SQL compared with the other options. This will allow you to form relationships and lookups to other tables of data.

    The downside of this option is that it will require premium licenses which can be quite costly. Find out more about premium PowerApp license costs.

    Contact our Experts

      Next Steps

      I hope you found this article useful, if you are interested in getting some assistance with Power Apps, please feel free to contact me with any questions using our contact us form.

      If you are interested in PowerApps then please download our free PowerApps white paper.

      Author

      Free Icon Download | Linkedin Dougie Wood

      Solutions Architect