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.