USE CASE: Application Integration

How to integrate a RESTful API with a SQL database

David Brown  |  August 9, 2021

rest-to-sql

Writing the response from a RESTful api to a SQL database

Pretty simple stuff. Take the response from an API and through it into a database. Simple but very, very useful.

In this demo we'll sync billing data from the Demo Billing API to a SQL database.

watch this use case in action

Line by Line: How this "Restful API to SQL" demo works

  • Startup Services:
    • The demo-api-billing and demo-rest-to-sql packages each have their own setup service to configure the database connection to be used in the demo: \ code\demo_api_billing\config\Setup - The setup service for creating the database connection pool for demo-api-billing. \ code\demo-rest-to-sql\config\CreateDatabase - The setup service for creating the database connection pool for demo-rest-to-sql. \ Each of these services configures a database connection pool in Martini as well as the database tables needed by this demo.
  • Syncing the Invoice data from demo-api-billing to the database.
    • In the reporting dashboard, the SYNC DATA button is configured to call a service exposed as an adhoc REST API called RefreshService.
    • The code\demo_rest_to_sql\services\RefreshService uses several Functions to pull the data from the demo-api-billing:
      • Line 3: demo_rest_to_sql.services_imported.billing_api.invoices.DemoApiBillingServiceInvoicesSelectAllInvoices \ This is a service that was generated by consuming the demo-api-billing OpenAPI schema. This service calls to an API endpoint from demo-api-billing to get all the invoice data
      • Line 4: demo_rest_to_sql.services_imported.billing_api.invoiceLines.DemoApiBillingServiceInvoicelinesSelectAllInvoiceLines \ This is a service that was also generated by demo-api-billing OpenAPI schema. This service calls an API endpoint from demo-api-billing to get all the invoice line items.
      • Line 5: demo_rest_to_sql.services_imported.billing_api.payments.DemoApiBillingServicePaymentsSelectAllPayments \ Also a service generated from demo-api-billing’s OpenAPI schema, this service calls to an API endpoint that retrieves all the payment data from demo-api-billing
      • The rest of the steps are iterating through these data from demo-api-billing and saving them to the database.
    • When the SYNC DATA button is clicked, it will trigger the service mentioned in the previous step to pull all invoice data from the Billing API, and saves it to the database.
  • Resetting the synced Billing API data.
    • In the reporting dashboard, a reset button is also added to remove all the synced data from Billing API. Clicking this will truncate all the rows saved in the database tables for this demo.