Oracle APEX: the low-code and low-cost application middle tier

Oracle APEX is a low code application development framework. It can be used free of charge – either as part of an existing Oracle Database license or running in the free Oracle Database 18c XE product. An Oracle APEX application should be considered a three-tier application – consisting of a client tier (the browser), the middle tier (the APEX application engine) and the data tier (back end databases and REST APIs on top of various systems and data stores).

A way to visualize the multi-tier architecture for the most common approach to APEX applications is the following:

The application is used by the end user in a browser. From that browser, HTTP requests are made to the ORDS (Oracle REST Data Services) listener – running on a web server in the DMZ. Requests for the APEX application engine are passed to the PL/SQL packages that make up this engine. The APEX application engine runs in an Oracle Database – which could well be the light weight and free Oracle Database Express Edition.

Whenever data must be retrieved in order to handle a request – or data should be manipulated as result of a user action – the APEX middle tier will reach out to the backend system. This will frequently be an Oracle Database – but it does not have to be.

The next figure makes the role of the Oracle Database a little bit clearer. It is very important to realize the (logical) decoupling between the APEX application engine and the Oracle Database that contains the business data presented and manipulated in the APEX application. In most APEX applications, the Oracle Database will appear in three locations. It could be the same Oracle Database instance in all three cases – but it does not have to be.

ORDS is a REST server with its own meta data defining “modules” and “templates”.  The meta data for ORDS can be stored in any Oracle database (in an ORDS schema), so it could be the local database serving APEX or another database.  ORDS may be capable to work without a database repo and just use XML files for a highly optimized runtime, with no metadata to look up (this is not currently a supported feature – although the capability exists for internal use at Oracle for example in SQL Developer Web – but it is on the roadmap).

The APEX application engine has its local database – an Oracle Database instance – that contains the meta-data that describes the application itself (pages, fields, navigation, validation logic, and more). It also holds the relevant session data: APEX is a stateless engine; the user session state is held partly in the client and partly in the APEX database. Note: the size of this session state is very small – typically just a few KB. The APEX database can also be used as data cache – to retain local, quickly accessible, read only copies of data retrieved from remote sources.

The APEX application can reach out to business data in the local database in which it is running itself as well to a PDB co-located within the same container database (in a shared multi-tenant instance, you would use database link syntax – with schema.objectname@PDB_LINK- and when the PDBs for APEX and the business data are in the same root, the Oracle Database can transparently re-write queries expressed in Database Link syntax to use SQL that is effectively local). It can also access business data in a different Oracle Database across a database link or through an ORDS instance that exposes HTTP access to packages and tables in this other database instance.

Mike Hichwa, VP Software Development at Oracle and responsible among other products for APEX, has many insights with me, some of which I have paraphrased here:

“Accessing data in a PDB from APEX in a different PDB (with both PDBs in the same CDB) can be done today via database link syntax.  The read access is optimized but updates do perform distributed transactions.  The intra-PDB access is being improved and optimized. You may or will be able to use simplified syntax (e.g. not database links) and that updates without two phase commit / distributed transactions. Database links and gateways (for non-Oracle databases) can be used, but would not be recommended for applications with large numbers of end users (so personally I like database links / gateways for ETL but not for general web apps)”

The idea that APEX is only suitable for low code application development on top of an Oracle Database is no longer correct. Through database links and gateways, APEX has long been able to [make to] run against business data in different databases. With the Oracle Database capabilities to call out to Web Services (SOAP and REST, for example using the UTL_HTTP package) it was also quite possible – albeit a bit cumbersome – to create an APEX application on external data sources. With the recent APEX feature called Web Source Module it has become largely a declarative – low code! – action to retrieve data from or manipulate data through a remote REST API.

With Web Source Modules, any REST API becomes a viable data source for APEX applications. Low code application development against virtually any backend becomes a reality. This is depicted in the next figure. Here you see how the APEX application shows and potentially creates and updates data from various types of databases (through REST APIs or possibly through heterogenuous gateways) , microservices and serverless functions as well as from Oracle SaaS applications.

Note: ORDS is expected to have support for MySQL sometime in 2019. That would enable quick, declarative exposure of tables in MySQL through a generated REST API. ORDS also has support for TimesTen in-memory database on its 2019 roadap. For other databases – SQL or NoSQL – a REST API has to be developed. For this, several tools and frameworks are available and of course implementing REST APIs is quite straightforward these days.

APEX is not only a Low Code Application Middle Tier – it is also a Low Cost Application Middle Tier

Low code development is attractive because of the high productivity and high quality (robustness) that can be achieved with a relatively low investment in technological skills and knowledge. A quick time to market can be realized. All of this applies primarily if functional requirements can be met by the out of the box capabilities of the low code framework.

The cost of low code development is of course also determined by the cost of the tooling and run time infrastructure that is required. With APEX, this cost is extremely low. The required components for developing and running an APEX application are ORDS and APEX on an Oracle Database. ORDS can be used for free and can run on a free web server like Apache Tomcat, Jetty or GlassFish (note: The documentation does state that “Glassfish Server support will be desupported in a future release”) . The database used for running APEX can be Oracle Database 18c XE – free as well!

And, as discussed before, the business data can be held in various data stores – from Oracle Database (any edition including the free XE) to MySQL or other open source databases, either SQL/ACID or NoSQL/BASE.

APEX – more than just low code

The term “low code” (associated with the Citizen Developer) is a catchphrase that perhaps does not deal well with close scrutiny. The essence of software development is not coding – as in writing lines of program code. It is much more about capturing the logic associated with functional requirements – in a structured way such that a machine can execute the logic. How you instruct the machine – with low level code or high level no code is not so relevant in my book. Low code frameworks can help speed up the process of laying down the machine instruction – and improving the quality of the instruction by providing a framework within it is created with reusable constructs and visual representation.

One of the challenges with low code platforms can be that the abstract high level language for describing the application behavior may not have enough expressiveness to capture all nuances stated in the business requirements. A lower level programming model may be needed then to capture the nuances and subtleties. As Joel Kallman – Director of Software Development at Oracle, responsible for APEX – states:

“APEX has the important ability to gracefully transition from No Code to Low Code to High Control.  Many customers can live within the “black box” of APEX and do no coding.  But everyone needs to customize, and the way you customize is with code.  With APEX, you can use a very small amount of code (snippets, as we call it) to customize your application.  It could be a snippet of PL/SQL or snippet of CSS, HTML or JavaScript.  Most low code frameworks abruptly go from no code to a high control (or “high code”) environment, with no middle ground.  Once you go into high code, you’ve lost all but the most professional of developers.  With APEX, it’s a very elegant and seamless transition. And for those who demand high control (high code), you can still exploit pre-compiled PL/SQL packages or JavaScript libraries or completely customized HTML templates & themes.  “