Instant REST APIs for Native SQLs or Stored Procedures

Mock API, Script-to-API, and DB-to-API can be produced with a low-code method through the API Creator module of Apinizer Platform. In this article, I tried to explain what DB-to-API is, how it can be applied, the problems it solves and what are the conveniences.

Many APIs whose data we make available for other services, web applications, and mobile developments communicate to databases. With the DB-to-API feature of Apinizer, a REST API is produced instantly by designing endpoints that match the SQL statements of the database type.

Advantages of Creating a DB-to-API with a Low-Code Approach

Generally, low-code is the approach that allows you to turn an idea into an application, without requiring deep technical knowledge, by writing very little code. DB-to-API provides API generation from a database with the low-code method. If we talk about the advantages of this situation;

  • It reduces the need for time and coding knowledge in making the API available to API consumers.
  • The API development process is managed through a powerful visual interface. Endpoints can be easily customized, maintained, and updated. Therefore, it increases speed and enables better service to customers.
  • Anyone writing a SQL statement can develop on the API. That’s why it reduces human cost. Non-technical API stakeholders can also contribute. (no need for expensive consultants!?!)
  • It supports the agility capability of enterprise applications.

Code-First vs API Design-First

Let’s look at the API, whose job is to run the query in the relevant database, in comparison with the development scenario, code-first, and generation with the API design-first tool;

First, let’s take a step-by-step look at what we will do with the code-first approach, that is, creating an API by creating an application;

  • Planning the process (stack usage, API endpoints, etc.),
  • Creation of application and coding the classes such as the API’s models, repository, service, resource, etc.,
  • error-prone codes and therefore analysis of the errors occurred,
  • Creating a docker configuration and installing k8s (or running on a server) to deploy the API,
  • testing the application and API.

In addition to these steps, if there is feedback from the customer or the team using the API or a request to add a new feature during the maintenance or development process of the API, the above steps will be performed again.

If the security, monitoring, etc. of this API are desired, third-party dependencies will be added and development work will continue.

And most importantly, there should be someone(s) who have the technical knowledge to do these operations.

In such a scenario, an API, which runs SQL queries and returns the result, can take days to weeks to develop and go live. Consider this timespan for a scenario where the number of APIs increases and multiple teams develop APIs! 😱 Even a minor action such as a change of date format may take half a day for the developer to do and test it. The generation of the API description document (API Spec) can be skipped due to a focus on rapid code development or lack of experience. (Therefore, if there is no API documentation, how will these people make a call? 🙃). Frequently encountering such problems can put the system in spaghetti form or force it to find a workaround solution.

Additionally, this approach appears to be a more traditional solution. In today’s world where being agile is an important criterion, it should be taken into consideration that the API is quickly made available to stakeholders (time-to-market) or consumed and that the workflow is manageable.

Let’s look at the implementation of this scenario on Apinizer with the API design-first approach (These operations will take place through user-friendly interfaces and will be explained in detail later in the article.);

  • Database connection integration is created.
  • Endpoints are designed with database queries. HTTP requests can be tested on the fly. API description documents are automatically created in different types and formats.
  • API Proxy is created and deployed from the API.

In this scenario, it takes much less time to develop and go live on the API. Making transactions through the interface rather than the application will reduce the risk of error. Operations such as updating the endpoints of the API can be implemented, tested, and deployed in seconds.

Then let’s start practicing the subject! During the application process, an API will be created based on the ’employees’ table on the MySQL-type ’employees’ database.

Creating database connection description from MySQL

Apinizer interacts with the database via JDBC. Supported connections are for; Oracle Database, Microsoft SQL Server, PostgreSQL, MySQL, IBM DB2, SAP SYBASE, Apache Hive, Apache Impala (for now).

Apinizer allows multiple connections to be created for the databases it supports and uses connection pooling for them.

In below video, testing and building the connection to the Employees’ database is shown. This operation is done to determine which database the query will run on while creating the endpoint.

You can expand the database connection description according to your needs.

Creating API from database

At this stage, we write the endpoint information and decide from which area of the request we will get the parameters in the query and which query to be run on the database. We also test the value returned from the query. In this section, attention should be paid to the API design (which HTTP method will be used? How should the URL design be?) according to the type of SQL statement.

Below, creating and testing the API named ‘Employees DB-to-API’ and the endpoint named ‘/employee/findByFirstNameAndLastName’ is shown:

List of all endpoints of Employees DB-to-API

Viewing DB-to-API’s API Specification

Once the API is created, the Swagger and Open API types of API description documents can be accessed on the fly in JSON and YAML formats.

Creating API Proxy from DB-to-API

Another convenience provided by Apinizer is that API Proxy can be created within seconds and made available to other stakeholders.

Below, creating API Proxy and testing the endpoint from DB-to-API is shown:

API Proxy provides the following conveniences with less code;

  • Security (Simple, Base64, Digest, JWT, JWT 3.Party, OAuth2, Backend API, Encryption, Password Open authorization, etc. authentication policies), access policies (allowed/forbidden API list, API based quota/throttling), securing the API with threat protection (content filters, max/min message length, XML/JSON schema validation) policies,
  • Building the API by creating a business rule, script policies, and manipulation or business logic in the areas of the message
  • Monitoring, analyzing, and reporting API Traffic through log records, debugging logs by enabling detailed log records,
  • Testing the API without the need for another tool,
  • Managing API versions,
  • Distributed and isolated work in different environments (production, sandbox, test),
  • Detecting anomaly conditions, monitoring API health, etc.

Click here to get more information about Apinizer API Gateway.

Click here to try DB-to-API in Apinizer demo environment.