Using APIs Instead of Direct Database Access
In today’s intricate business landscape, IT staff are in a constant state of intertwining multiple technologies on different platforms. Services and applications, internally and externally, need to be united to bring business value and innovation. Linking these various and ever-changing systems together to deliver modern solutions is of the utmost importance for businesses to stay ahead of their competition.
And there is one thing all these technologies need, your data.
In the past, when an application needed data, most teams took the easy road by setting up direct database access outside the application. Applications utilize Microsoft’s’ ODBC, Java’s JDBC, or some language-specific interface to access the business’s database and complete other vital tasks. This scenario rang especially true for organizations operating on IBM i. Businesses with native IBM i applications always had direct database access as IBM’s Db2 database integrates into the operating system. Direct database access in an application is expected, so many do not think twice about using the same paradigm for external applications.
Scenario
The best way to explore this topic is to start with a real-world example.
Consider this – a business has decided that to achieve its revenue goals, they must develop a new source of revenue. The clear choice is to create an online store for direct-to-customer sales. Regardless of how they build a new e-commerce site, the new site will need real-time data from their line of business applications and interact with product and order data.
Direct Database Access
As we know, on IBM i, the database is integrated into the operating system. Meaning the credentials provided are for an actual system user profile. Now the external application is using a set of credentials that could potentially be used to log into the system using 5250, SSH, FTP, or other methods.
The possibility of abuse is very real. If the system is not well secured, this could be disastrous. Unfortunately, many IBM i enterprises, including some we have interacted with over the years, do not have their system secured properly. This makes providing direct database access to outside applications and services risky.
No matter what database the applications use, it is common for busy IT staff to create a profile for access, but many fail to take the time to ensure to only grant access to database objects required for the intended use case. It is easy to fall into the trap of just defaulting on a profile to have access to the entire database. Even within something as simple as a data table, there may be rows that should not be accessible by the external application, but many forget to take the time to build appropriate views to protect this data.
Securing data should not be taken lightly. Your data is the lifeblood of your business.
Business Rules or Processes
In the business world, rarely does an event only need to update a table. There are business rules and workflows to run. When an order is created, a copious number of processes begin to complete the order – inventory must be allocated and adjusted, tickets sent to the warehouse for item retrieval, and invoices generated. If these processes have been implemented within the database layer using triggers, then a simple database may suffice, but this is rarely the case.
So, now it becomes the responsibility of the external application developer to understand both the database layout and business processes. Developers must ensure that the business processes are callable and scalable. And, if only using database access, a stored procedure would need to be created.
Can the external application be trusted to properly enforce these rules and workflows? If the business processes change, will the IT staff remember to have the external application updated?
API Implementation
A better way to address these needs is to implement a REST API layer for the external application to consume. Using the above example scenario, the application would need an API for products and one for orders.
The e-commerce system will only need read access to the product data. So, the product APIs needed would be a method to retrieve a list of products and one for a specific product. The path should be simple; /product would make the most sense. A GET request to that path would return the product list. Additional parameters should be added to the body or the query string to filter that list. To retrieve data for a specific product, a GET request that includes the product number as part of the path would be needed, /product/12345.
The e-commerce system will need to manage orders as well as view them. So, the /order API will need to GET methods like the product API. It will also need the POST method to create a new order. This method would require all needed order information in JSON format to be included in the request’s body. If an order needs to be updated, the PUT method would be used, the order number included in the path (/order/54321), and the data to update in the body of the request.
Security
API authentication is usually handled independently of database or system profiles. Basic authentication using a profile and password is still an option but is considered insecure for anything more than trivial internal interfaces. For an external application like the example, a more secure method of authentication such as API keys, tokens, or OAuth should be used. Since these methods are not tied to system or database profiles, they do not have the associated risks.
In addition to authentication, a system of authorization, or access control, is also desirable. Authentication identifies an API user, and authorization controls what APIs and methods a user can access. The product API described above likely has routes defined for more than just viewing product data, but the example system should be restricted to only the GET methods. A complete API solution should have this capability. When properly implemented, the example application can only perform functions exposed as an API and authorized to use.
Business Rules/Processes
Unlike a database update, an API has logic. It can invoke any needed business processes, enforce any business rules, as well as update the database. The API developer has complete control of the process and shields these intricacies from the external application. Developers of the example e-commerce solution do not need to understand business processes or even the database layout. The API documentation, most commonly an OpenAPI or Swagger document, describes the interface for the API, and the data to be sent and returned. The interface is controlled entirely by the API developer, in this case, internal system developers.
Reusability
Another crucial factor to consider is that the APIs created for this project are reusable. The next project that needs to integrate with product or order data can simply use the same APIs. This modularity and reusability allow future projects to be developed more quickly. When processes need to be changed, there is a single API interface to be updated, not duplicated logic spread out over multiple applications written in different languages, making maintenance more efficient.
In the end, the example e-commerce system may require more work than simply accessing the database directly. Even with a tool designed to simplify API creation and management, like Profound API, there will be a time investment to build an API layer. However, the investment will pay off with a more secure, easy-to-implement integration layer that is ready for today’s needs as well as tomorrow’s.
This is what we call futurization.
Profound API Can Help
Profound API is designed from the ground up to make creating, managing, deploying, and consuming APIs simple. Using Profound API makes it easy to:
- Define API interfaces, including methods, paths, and parameters.
- Secure APIs with built-in authentication and authorization.
- Build API logic using an intuitive low code development environment.
- Access databases (Db2, MySQL, MariaDB, Microsoft SQL Server, Oracle).
- Automatically document API interfaces using OpenAPI/Swagger.
- Make API documentation easily discoverable with a built in API Explorer portal.
- Monitor the performance of APIs with an easy-to-understand dashboard.
Profound API is a comprehensive set of easy-to-use tools that reduce the learning curve and have you working with APIs in minutes, not weeks.
Find out more at https://profoundlogic.com/api
