A Pega DAO Pattern for External Databases

The Data Access Object (DAO) pattern is well known in object-oriented languages as a means of abstracting the ultimate data source from the model (aka entity, business object, etc.) layer. This is a best practice in object-oriented software development in general, and so it is equally relevant to Pega development in particular. This article will show a simple method for implementing this pattern using Pega 7.x Data Pages, Data Transforms, and Report Definitions in order to access an external database

The Scenario

The application is common enough: we have an external database that our Pega application needs to access for relevant information. We will consider it read-only for our purposes here, but one can easily understand the principles and apply them to other operations (create, update, delete).

We could of course map our Data- classes directly to these tables, and we’d be off and running, but this creates a tightly coupled relationship between our external database structure and our application. You are implicitly enforcing the structure and semantics of the external data, which may not align with the structure and semantics most applicable to the application. To avoid this, we can and should use the Int- classes to map to these tables. This keeps the Int- data structure in line with the underlying data source for access, but does not imply any structural impact on the Data- layer. Of course, we want to avoid using the Int- classes directly in our Work- objects, as Int- classes should be transient and only exist for the duration of the interaction with that data source. The purpose of the Int- classes is to map into the data structure of the application (Data- classes), so let’s look at how we can quickly accomplish that.

Create the External Int- Class

Once the Database connectivity is defined and tested, setting up the ability to read from a table is accomplished easily through the Connector and Metadata Accelerator, selecting the SQL option and giving it the database and table you wish to connect to. Once complete, you will be able to use the Obj-* methods just as if it were an internal table. (For the most part, this obviates the need for RDB-SQL connectors, though not entirely.)

Pro Tip: Before running the accelerator, it is a good idea to create a sub-domain under the Int- class that identifies the information source. For example, connecting to the MyCo “Super Amazing Information” Database might lead us to create a subclass namespace of *-Int-SAI to house all of that database’s classes. This will help separate these classes from any other application interfaces you might require. Also consider the “owner” of that information and place the classes at the right spot in the class hierarchy for reuse.

Of course, these classes should be created in the appropriate integration Rule Set, as all rules on an Int- class should be.

Create a Data Page

Data Pages are how we want to access most information in Pega 7.x, so we’ll start there. Create a Data Page of either Page (D_<Type>) or List (D_<Type>List) structure, where that Data Page will reside either under the class it is defined on or under Code-Pega-List if it is a List structure. You will have to determine whether the Data Page is a Thread, Requestor, or Node level Page based on the type of data involved. Domain/Lookup data is typically static and set to the Node level, session-specific data is set to Requestor level, and work-specific data is set at the Thread level. This should be created in your application Rule Set, not the integration Rule Set, since it is a Data- class.

Pro Tip: When using parameters to a data source, always have your Data Page check that appropriate parameters are set before actually retrieving data. If no parameters are set, have it exit immediately to avoid reading the entire contents of the data source – e.g: a database table with a large number of rows. Pega will ignore parameters that have no value and not use them as a filter!

Create a Report Definition

In order to load the Data Page, you can select from a number of options. Our scenario is for an external database whose classes have been generated under the Int- layer, so in order to get information from them, we can specify a Report Definition under that specific Int- class. (The same pattern will apply to a SOAP Connector, which is another option.) Under the Int-* class, create the report definition that will read the data table represented by that class type. Its content will of course be dictated by the underlying class and your intent, and it should be defined in the integration Rule Set.

Once created, the report can be specified on the Data Page we created earlier (if it was not already created from the new rule form presented when selecting the spyglass icon for the Report Definition in the Data Page). Parameters can be passed from the Data Page to the Report Definition as well. However, since the Page type of the Data- Data Page will not match the Page type of the Int- class, the rule form prompts you for a Data Transform to map between the two.

Create a Data Transform

As part of the creation of the Data Transform from the Data Page, you get a DataSource Parameter implicitly passed, as well as the beginnings of mapping the Primary Page (Data-) from the DataSource Page (Int-). Simply set the values as necessary between the source and target. Note that you can use other Data Pages in the Data Transform to map complex data (other Page type Property rules) sourced using the same pattern! Since the Data- layer can access the Int- layer by virtue of the Rule Set stack, this rule should be created in the application Rule Set of the appropriate layer.

Pro Tip: Instead of going with the mapping for List structured Data Transforms, create a single type that takes a DataSource page parameter as well, and use it in an Apply-DataTransform step for your list iteration. You will only have to update any changes in one place in order to address both Page and List types!


Once all of the above is in place, you can save your Data Page and test it using the Run function in the Developer Studio. The Data Page will look to its source to retrieve its information, which in our case was a Report Definition. This report will get the data from the external table based on its configuration and read it into a DataSource page that will be passed to the Data Transform. The Data transform then converts the external data structure to the internal data structure represented by the class type of the Data Page. Just like that, your Data Page now has its desired information, and the rest of the application can remain completely unaware of its source!

By leveraging this pattern, the Work- classes can reference the Data- information with no knowledge that it is an external resource and without being bound to the data structure of that external resource. This promotes loose coupling, which leads to more extensible, maintainable, and less brittle applications whose layered application design will provide dividends for years to come.

Colin Campbell and Ryan Mongold are the owners of Stratosphere Technical Consulting, specializing in the strategic development of large-scale Pega Applications, Programs, and Centers of Excellence using a proprietary model for the ongoing evolution of business and technical BPM maturity across the enterprise.

Want to learn more about what we can do for your organization? Contact us below

Interested in working with a company rather than for one? We’re always looking for the best talent in the industry. Contact us at Careers

Name *