PowerApps / Microsoft Flow – Using data in SQL Azure

Last week Mark Sipsma mentioned to me:

I’m working on a PowerApps project that uses Microsoft SQL as the back end. Recently we have noticed that amendments to the SQL DB such as new fields and field amendments can take around 5 hours to show in a PowerApp as a selectable option in the formula bar and also in Flow as a selectable SQL field.

It feels like there is some sort of PowerApps to SQL cache that is taking time to update. I’m struggling to find any information on the architecture between PowerApps and SQL Azure DBs.

Today I had a look into this problem.

SQL Azure in Microsoft Flow

I started by creating a SQL DB in Azure.

Then I tested the connection in Flow

And when I ran a flow a default table ‘sys.database_firewall_rules’ is found.

Create a Table in SQL Azure

Time to create a table for myself. The query editor makes this easy:

And my table is created within no time.

SQL Azure in PowerApps

Within PowerApps I can connect to my SQL Server Database like I would do with any datasource. Still all is easy to do.

Even my newly created table is there immediately.

Again to connect my gallery to my persons table is just a matter of selecting the table.

But when I look in Flow my Persons table is still not found.

Within PowerApps however I can display my data form the table using a few label elements

I can run my app and all is working as expected.

Time to update my data. Again I use the preview query editor to add a record.

Even though I saved the data, my app will only show one record. I decided to add a refresh button. Of course you don’t need to have a button in your apps,  you can simply run the code on screen load.

Using the refresh function on the persons datasource, loads the data as I would expect.

Time for the ultimate test. I’m creating an additional table in my database. I’m calling this table Persons2. PowerApps however doesn’t see this table.

Ok, that isn’t nice. I decided refresh my connection, but still no luck.

When I created a new connection however the new table did appear.

After some investigation with Mark, we found that there is a delay due to cache lifetime which is 30 min any database scheme changes should be visible by design after 30 min.