Comparing Microsoft Power Apps and Oracle APEX – Part 2 Database Centric Applications

Alexis Galdamez Feb 22, 2021 3:39:36 PM

In the first part of our blog series about Low Code comparison between Oracle APEX and Microsoft Power apps we looked to what both companies have to offer in the Low Code market, their focus, and target audience. In this blog post we will talk about what each provide when creating database centric web applications.

To compare both tools we will evaluate how they perform when facing some basic challenges when creating a data centric application with the data and business logic stored in their respective databases, Oracle Database for Oracle APEX and SQL Server for Microsoft Power Apps. These three challenges are:

  • Retrieving data from the database
  • Database stored procedure execution
  • Client-side code extendibility

Retrieving Data from the Database

Oracle APEX runs in the database, accessing data and displaying it in the web browser is its specialty. In APEX you can leverage the whole power of SQL directly from APEX. The data will flow from the database to the web server running ORDS and from the ORDS webserver to the browser seamlessly. When developing an application, the developer does not need to worry about the data access layer, only about writing the SQL and what to do with the data. The developer can use their database skills and does not need to learn a new programming language or syntax to retrieve information from the database.

Microsoft Power Apps allows the developer to query the database using a set of functions and expressions, then later Power apps translates to SQL and executes the query in the database. You are not able to leverage the whole power of SQL directly from Power apps because you need to use another abstraction on top of SQL to query the data. Sometimes the function you need cannot be delegated. This means it will not run in SQL Server, and it will run in the client with a limit of how many records it can process. You will need to apply workarounds like creating views to pass the limitations.

The problems before-mentioned are related to the fact that Power Apps is not aim specifically to database developers but to citizen developers (I talked about this in the first part of my blog series). Power Apps offers great support for SQL Server but because it is not the main use-case for it, you will find that there are still details to polish when fetching data from the database.

Database Store Procedures Execution

Oracle APEX gives the developer the option to call and execute any code stored in the database. This could be Java, PLSQL or JavaScript (using Oracle Multilingual Engine). It is common for database centric applications to have their business logic written next to the data. In the case of Oracle, PLSQL is the programming language of choice. With APEX the developer can call PLSQL code directly from the framework, for example when the user submits a form you can call a PLSQL procedure to process the user input and insert it in a table or you could define a dynamic action which executes PLSQL code when the user clicks a button.

Microsoft Power Apps does not support calling T-SQL (Transaction language used in SQL Server) procedures directly. The developer must use another tool which is part of the Power Platform named ‘Power Automate’. You need to define a flow and after that, call the flow in any place of the application where we need to execute the stored procedure. A few disadvantages of this approach is the amount of clicks required to define a flow and the billing schema which is different to what a developer is familiar with. Power Automate gives you a range of flow executions - every time you called a flow, one execution is consumed. When you run out of executions you need to buy more. They are not expensive but thinking that every time a user clicks a button, they are running out of credits is a bit uncomfortable.

Client-side Code Extendibility

In Oracle APEX the developer can make use of any of the JavaScript APIs APEX exposes or they can add their own JavaScript code. You can even create plugins so other users only install the plugin, and use the component as if it was native without worrying about the implementation. This gives the developer the opportunity to complement or extend the power of APEX. If there is a requirement with a specific client-side behaviour that APEX does not include out of the box, you could develop it with JavaScript or include any JavaScript library in your application which solves the problem.

The same freedom applies to CSS. You can create CSS style sheets and include them with your application to change bits of the looks and feel, or if you want a whole different look, you can even create your own themes.

In Microsoft Power Apps there is not an official supported way to call JavaScript. The reason for this is that Power Apps should be a no-code tools (well, that is the reason given on this declined feature request post for Power Apps!). This is another important area where we can see that citizen developers are the main focus of Power Apps and not to give flexibility to professional developers. Another reason s that JavaScript is not allowed due to security concerns, because a non-professional could add unsafe JavaScript. That is a good reason and something to take in consideration when you allow non-professional developers to create applications. But still, the lack of native support for JavaScript makes the tool rigid and difficult to adapt to a complex requirement.

Summary

As we see in this post, both tools allow us to create applications and accessing the database resources, but they perform differently on this. A different approach could have been taken for this evaluation, but these covered are some of the main challenges a developer faces when creating a professional database centric web application.

APEX’s main goal is facilitating the creation of database centric web applications and it does a wonderful job, when creating an application. The logic and the data are there next to the framework in the database, this makes the developing experience easy and efficient. Power apps in the other hand does not performance so well here due the fact that creating web applications on top of the SQL Server database and provide flexibility to professional developers it is not its main goal. Its goal is to give power to non-professionals developers to create apps without technical knowledge, and it performs fantastically on that, but this feature is a trade-off, and the professional developer can feel its repercussions from day one when working with the tool.

 


 

Author: Alexis Galdamez

Job Title: Development Consultant

Bio: Alexis is a Development Consultant at DSP-Explorer. Alexis has developed plenty of mission critical applications for the financial sector.
He is passionate about Web technologies and Oracle APEX and utilises them to provide to the customers a high-quality software solution.