Improving Data Retrieval with Custom NodeJS API and PostgreSQL DB for WordPress MemberPress
I have been working on developing a reporting system with responsive dashboards for a client of mine. The system consists of dashboards where reports are generated to provide his clients with a quick overview of the company's current stats and growth. For example, the dashboards include graphs that show the total members per state and worldwide, as well as tables that display different types of members, among other data. The goal of the system was to have a consolidated view of the company in a single report that is easily accessible and quick to view. This saves the client a lot of time from having to compile reports from various systems, which would take hours each month and only be available for one day. Now, the client and his client can view an up-to-date, live view of the company's growth at any time.
I will not focus on the report system here, as I will create a separate case study when the system is delivered. Instead, I want to focus on one of the systems that I use to acquire the data from, which is the memberPress plugin for WordPress. Although memberPress has an API to gather information, it cannot gather information added with custom fields structure that was added to the system. In other words, the memberPress functionality was extended with custom fields to meet business requirements, but the API was unable to fulfill those requirements.
Another issue is the way WordPress stores its data in the database, especially in the wp_metauser table. It does not store each property of a user (member) in a separate column, but instead uses only three columns (ID, KEY, VALUE) for all the meta properties in the database. For example, consider a user named Peter who is an engineer and a member of WordPress. The database would store the information as:
| id | key | value |
|---|---|---|
| 56 | role | member |
| 56 | occupation | engineer |
| 56 | customfields | a:6:{s:7:"general";s:2:"on" |
| ;s:10:"engineering" ... |
Just a side note, I do get why the WordPress engineers designed it like this, and this to accommodate consistence in the WordPress ecosystem for custom plugins developed by other developers.
MemberPress and many other WordPress plugin developers sometimes accommodate custom fields by adding all custom fields as one entry that is serialized via PHP. This is not ideal if you need to search for a specific field in the database, as it would make the query slow and expensive. In PHP, you can deserialize the details, but it is still expensive, and you cannot index values inside serialized arrays.
The solution I came up with was to create a SQL query that constructs a new temporary table in memory. I combined all the user's details from both the wp_user and wp_usermeta tables, plus the membership tables and the post table, into this one table. I also read the serialized arrays and their key-value pairs, using the key name to construct the column name and the value to populate the row for that column. In other words, I spread out the serialized data into individual columns while constructing the temporary table in memory. I also added two magic columns to each row to keep track of unique id’s so that no duplication is enforced with a schema rule. I used a custom-built NodeJS API server that I developed for the client to connect to the database and perform this action. After constructing the table with all the required data, I instructed the separate PostgreSQL database that I developed for the reporting system to create a table with the sorted data. Now the data is imported into the PostgreSQL database with columns for each piece of data, making it super-fast to read and allowing for indexing for the reporting dashboards. I chose to store the refined data in PostgreSQL because it is excellent for data partitioning and to have a separate database to read from so that the main production MySQL database does not get hammered with expensive queries when I need to compile reports for the dashboards I developed.
Data partitioning is important for reporting systems, as the cost of creating reports increases as the data grows beyond a certain point. With PostgreSQL, I can break down the report data into years or months. This allows for fast and efficient querying of data for a specific period in the dashboard widgets, as data is divided into buckets like months.
In conclusion, I developed a custom reports system for my client, which consisted of responsive dashboards, graphs, and tables to give his client’s administration team a consolidated live view of their company's growth to be able to quickly report back to their investors and members. I faced challenges in acquiring data from the MemberPress plugin for WordPress, as the API was unable to gather information added with custom fields. To solve this, I created a NodeJS API server that constructed a temporary table in memory, combined all the required data from different tables, and deserialized the data into separate columns. I then imported this refined data into a PostgreSQL database, which was ideal for data partitioning and fast, responsive reporting. My system is completely separate from the WordPress, WooCommerce, and WordPress MySQL database, providing redundancy and not affecting the existing functionality.
Redundant, Reliable, and Revolutionary: Enhance Your WordPress/WooCommerce System with My NodeJS APIs and Microservices
Just one of the many systems I develop these days with Next.js(React) & NodeJS, with my Cloud Based Systems that also allows for redundancy. If WordPress, WooCommerce, or the WordPress database go offline, it would not affect my report system. The other way around, as well. Furthermore, my system cannot corrupt or interfere with the data of WordPress, WooCommerce, or the WordPress database, as I only read from it.
This is the beauty of the NodeJS APIs and microservices that I develop. You can create completely new functionality on top of existing system or plugin functionality, without interfering with the current functionality of the system. You can also branch off functionality to any of my systems, adding a lot more new functionality than what is currently available. Another reason, especially with WordPress plugins, is that you are not dependent on the original plugin developer and don't have to make constant changes if they decide to update their code or replace functionalities. This is because I don't plug into their programs, I communicate directly with WordPress and its MySQL database through the WordPress REST API.
I hope you find this useful. If you have a current WordPress or WooCommerce system and want to expand on existing functionality, feel free to get in touch and let me know what you'd like to get done. Although I was a WordPress/WooCommerce verified affiliate worker and have specialized in WordPress and WooCommerce systems for over a decade, I am also able to develop microservices and functionality for ANY other system, including Shopify, Magento, or any other system you need help with. Lastly, I also specialize in headless WordPress and WooCommerce setups. This involves using WordPress and WooCommerce as the backend, and Next.js (React) and sometimes a separate NodeJS as the frontend management platform, to deliver super-fast websites and web applications that score 99% to 100% for performance, 100% for construction, and 100% for SEO.