Skip to main content

Connecting a SQL DB

Introduction#

The user will be continuing from the same integration started in the previous lesson. In this lesson, we will learn how to add an RDS Database as a resource, and then, using the SQL component, query some data from that DB. Afterward, in the next lesson, we will finish this integration by mutating the appearance of that data so we can see the output in Jetic.

VisionsCorp’s request#

VisionsCorp needs to access their data from their RDS Database on Jetic, which is possible with Jetic’s built-in SQL component. VisionsCorp has provided us with the SQL Query so that we can query their Database and output the appropriate information, i.e. countries in which some of their prospects live.

Integration Ingredients:#

  • GET method (created in the last lesson)
  • SQL
  • Log

Goals:#

  • Add a Database Resource
  • Connect that resource to the SQL component
  • Make a SQL query

Step 1: Update the previous integration#

Docusaurus

  • In the last lesson, we created an API, we will continue from where we left off
  • Delete the Log from under the API
  • Drag a SQL component
  • Make sure that the USE MESSAGE BODY is unchecked
  • Leave the DATA SOURCE BEAN empty

Step 2: Provision a Database Resource#

Docusaurus

  • In the left sidebar Click on Explorer
  • Click on Resources → Database Resources → and the (+) Create New Resource
  • For the Name put DemoDB, you can add anything for the Description
  • After that, a modal will appear fill it out as follows:
  • Type → Simple (Leave standard)
  • Driver Class → MySQL (Leave standard)
  • Driver Class Name → com.mysql.cj.jdbc.Driver (Leave standard)
  • URL → YOUR DATABASE URL
  • Username → YOUR DATABASE USERNAME
  • Password→ YOUR DATABASE PASSWORD
  • Maven → mysql:mysql-connector-java:8.0.32
  • Bean Name → DemoDB (notice it matches the Name)
  • Make sure DemoDB is added as a dependency

Step 3: Adding a Query#

Docusaurus

  • Back in the route’s area, click on the SQL component
  • In DATA SOURCE BEAN add #DemoDB
  • This is the SQL query that was provided to you by VisionsCorp:

SELECT [c.id](http://c.id/) as country_id, [c.name](http://c.name/) as country_name, [ci.id](http://ci.id/) as city_id, [ci.name](http://ci.name/) as city_name, c.created_date as country_created, ci.created_date as city_created FROM countries as c INNER JOIN cities ci ON [c.id](http://c.id/) = ci.country_id

  • Copy this code to the clipboard and add it to the SQL component
  • Add a Log under the SQL component and for the MESSAGE add THIS IS THE BODY: ${body}
  • Run the integration and wait for it to boot up
  • You should then be able to see INFO [log] (executor-thread-0) THIS IS THE BODY: [{country_id=1, country_name=United States, city_id=1, city_na....

🎉 Congratulations you have successfully connected your first DB in Jetic! In the next lesson we will finish this integration by splitting this message up🎉