Parameterize DB Connections

[KateW]

Hi, I was wondering if there is a way to parameterize the DB connections used in my canvas app.

Background is I have one Power App connecting to 2 SQL Azure DBs.  The DB tables are a mirror of each other, one for dev and one for prod.  I was hoping I could set the DB table as a parameter on each screen so when I make changes on either dev or prod screens I don't have to rename all my connections between the screens.  Hope that makes sense, thanks!

[mdevaney]

@KateW 
Are you displaying the tables in gallery?  If yes, you could use this code in the Items property of the gallery.

If(varApp="dev", your_dev_datasource, varApp="prod", your_prod_datasource)

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[KateW]

Thanks @mdevaney!

Unfortunately it is basically throughout the entire app so will be for galleries, forms, drop downs, labels, the lot.

[mdevaney]

@KateW 

In that case the answer is no, you cannot dynamically change the datasource using parameters.

What you could do is save a copy of the prod app when changes are needed, delete the prod datasource and then re-add the dev datasource.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[KateW]

No worries, thanks for your help, @mdevaney , really appreciate it.

[mike528]

@KateW 

How are you connecting to the Azure SQL DB? Are you adding the tables directly as data sources? You might consider reading/writing your data using flows. You can control the server as part of the SQL Connection when using Azure AD Integrated security. Within any SQL action, using this connector, the first two parameters are the server (i.e. *****-dev-sql.database.windows.net or *****-prd-sql.database.windows.net)  and the database. It might be a bit of work to update your app, but where the data comes from and is written to would be completely transparent to any part of your app except passing the environment (dev or prod) to the flow as a Ask in PowerApps parameter.

It's an interesting challenge, I'll poke around with another idea I have an post back if it works. I know the above will work, but may require a fair amount of retooling.

Thanks

[mike528]

@KateW 

Another approach would simply be to create a new environment, promote your app to the new environment and change the SQL connection within the second environment, Then one is completely for dev and the other is for prd. This has a lot of other benefits that just being able to switch DB connections. 

Hope this helps.

[KateW]

Thanks @mike528 , yes I am adding the tables as data connections into the app.  The flow approach is certainly an interesting idea.

[KateW]

@mike528 yeah, we originally wanted to go down the different environment path however this app uses button triggered flows which doesn't work so well with solutions.  Our Dev/Prod split for this app uses a completely different Dev/Prod DB and calls different Dev/Prod flows so making it a solution and deploying/promoting to new environments didn't seem to be much of an option.

[mike528]

@KateW 

You mentioned that the DBs are mirrors (same tables, views, stored procs, etc) just dev vs prod data, correct? Are the flows different because they connect to different dev/prod endpoints or there is different logic? If they are basically the same, you should be able to parameterize almost anything in a flow to suite your dev/prod switch. You don't have to use solutions to promote data from one environment to another, especially if you are not using CDS.

Hope this helps.