Blog
Data - 3 things you should know before using the MySQL or PostgreSQL connectors
May 11. 2021
If you're thinking of using Power Apps to connect to MySQL or PostgreSQL databases, here are 3 things you should be aware of before you start.
Power Apps provides basic connectivity to MySQL and PostgreSQL databases - two of the most popular open source databases.
The documentation for these connectors are beneath:
1 - MySQL and PostgreSQL require a premium license plan
This is a frequently requested feature, and it is something that you can vote for here:
The documentation for these connectors are beneath:
https://docs.microsoft.com/en-us/connectors/mysql/
https://docs.microsoft.com/en-us/connectors/postgresql/
https://docs.microsoft.com/en-us/connectors/postgresql/
If you're thinking of using these connectors, here are the top 3 things you should be aware of.
1 - MySQL and PostgreSQL require a premium license plan
Both the MySQL and PostgreSQL connectors are premium connectors. This means that each user requires a per user or per app license at $10/$40 per user per month respectively.
For some app builders, this can conflict with the reason why they choose these data sources - which is to access a relational database in a low-cost way.
Therefore, it's important to factor the cost of premium licensing when we consider using MySQL or PostgreSQL with Power Apps.
For some app builders, this can conflict with the reason why they choose these data sources - which is to access a relational database in a low-cost way.
Therefore, it's important to factor the cost of premium licensing when we consider using MySQL or PostgreSQL with Power Apps.
2 - MySQL and PostgreSQL work only through an premise gateway
Both the MySQL and PostgreSQL connectors work only through an on-premise gateway. This means that we must install the gateway on a computer that is always switched on, and this is an additional component that we must manage.
It's not possible to connect directly to an instance of that is accessible through the cloud or the web. An example of this is an instance of a MySQL and PostgreSQL database that is hosted in Amazon AWS or Azure.
It's not possible to connect directly to an instance of that is accessible through the cloud or the web. An example of this is an instance of a MySQL and PostgreSQL database that is hosted in Amazon AWS or Azure.
This is a frequently requested feature, and it is something that you can vote for here:
https://powerusers.microsoft.com/t5/Power-Apps-Ideas/MySQL-connector-connectivity/idi-p/133968
3 - Tables with 'auto increment' primary key columns are not fully supported
Finally, one of the biggest limitations of the MySQL and PostgreSQL connectors is that it is not possible to add records to tables with 'auto increment' primary key fields.
3 - Tables with 'auto increment' primary key columns are not fully supported
Finally, one of the biggest limitations of the MySQL and PostgreSQL connectors is that it is not possible to add records to tables with 'auto increment' primary key fields.
When we attempt to patch or to add a record to a table with an 'auto increment' primary key field, we receive an error from Power Apps that prompts us to pass a primary key field value. This is incorrect because the database is set to automatically generate the primary key field value.
There are several posts about this issue.
https://powerusers.microsoft.com/t5/Building-Power-Apps/MySQL-Auto-Increment-Primary-Key-Incompatibility/m-p/408532
https://powerusers.microsoft.com/t5/Building-Power-Apps/Using-Collect-to-upload-multiple-rows-to-PostgresSQL-database/m-p/582508
https://powerusers.microsoft.com/t5/Building-Power-Apps/Writing-data-into-PostgreSQL-database-results-in-AutoIncrement/m-p/743699
https://powerusers.microsoft.com/t5/Building-Power-Apps/Using-Collect-to-upload-multiple-rows-to-PostgresSQL-database/m-p/582508
https://powerusers.microsoft.com/t5/Building-Power-Apps/Writing-data-into-PostgreSQL-database-results-in-AutoIncrement/m-p/743699
It would be great if Microsoft could fix this problem. However, I suspect this has always been a low priority issue because Microsoft's focus will be on a Dataverse, and other Power App features that are focused around the Microsoft ecosystem, and features that can generate more sales or income for Microsoft.
Therefore, I have created a post in the ideas forum which I hope will raise awareness. If MySQL and PostgreSQL support is important to you, I would recommend that you vote for the idea beneath.
Conclusion
If you're thinking of connecting to MySQL or PostgreSQL databases, it's important to be aware that the corresponding connectors are premium connectors which require a premium license plan. Second, these connectors work only through the on-premise gateway. Most importantly, there is a bug that prevents us from adding records to tables with primary key columns that are set to 'auto increment'.
- Categories:
- data
- mysql
- postgresql
Related posts
- Data - How to show the distinct rows from 2 data sources or collections
- Data - How to implement circular rotational date sorting
- Bug - What to do when the data section of the Power Apps Maker portal doesn't work
- Data - Combine columns from separate tables into a single table
- Formula - Transposing/converting rows to columns- an almost impossible task?
- Data - How to rename field names in a record
- Data - How to hide duplicate rows in a gallery / show distinct multiple columns in a gallery
- Data - Retrieving news/forum/blog articles with RSS
- Data - How to sort by partial numbers in a text field
- Data - How to return the last record from a table
- Data - How to create bulk test/dummy records with random values
- Data - A walkthrough of how to migrate the data source of an app from Excel to Sharepoint
- Data - How to enforce unique values (or prevent duplicate values) in one or more columns
- Data - How much mobile data does Power Apps consume? What ways can we minimise this?
- Data - How to save and retrieve Google calendar entries
- Data - How to save and retrieve Google contacts
- SQL - Caution! This is how users can hack shared SQL connections
- SharePoint – 2 Mistakes to avoid when importing Excel data
- SQL - Don't let this DateTime bug catch you out!
- Settings - What's the purpose of the "Explicit Column Selection" Setting?
- SQL Server for Beginners Part 3 - Installing On-Premises Gateway
- SQL Server for Beginners Part 2 - Installing Management Studio
- SQL Server for Beginners Part 1 - Installing SQL Server
- Searching data–What you need to know about case sensitivity
- Images - How to create images that can change depending on data
- Excel - Reasons NOT to use Excel as a data source
- SharePoint - What you need to know about Filtering Data
- Formulas - Generating Row Numbers
