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.

How to download & run this "RESTful API to SQL" demo

  • If you haven't already done so, get yourself a Martini.
  • Within either Martini Desktop or Martini Online select the "REST API to SQL database" demo from the Welcome Screen and click Install:
    screenshot-welcome-screen
  • The Package demo004-rest-to-sql will be automatically downloaded. Right-click the Package and click Start. A green icon will indicate it has started:
    screenshot-package
  • Documentation containing a Test Procedure to run the demo is included in the Package readme file \resources\readme\readme.md. The readme file is automatically opened when the Package is started.
    screenshot-documentation
  • The Test Procedure will guide you through the process of syncing billing data from the RESTful API of the Demo Billing System with a SQL database.

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.
cta-left cta-right
Demo

Want a ringside seat to the action?

Book a demo to see how our fully integrated platform could revolutionise your organisation and help you wrangle your data for good!

Book demo