REST API to SQL Database with Boomi: A step-by-step guide Boomi tutorial

How do you get data from a REST API into your SQL database without writing a single line of backend code? That’s a common challenge for  IT teams and data analysts working with APIs and databases but lacking deep development resources. The need to bring external data into internal systems, quickly and reliably, is more common than ever.

While importing structured API data into a SQL table sounds straightforward, many teams find themselves stuck between writing custom scripts or dealing with clunky manual workarounds. What should be a quick integration becomes a time sink.

Fortunately, with Boomi’s low-code integration platform, this task becomes far more approachable. In this article, you’ll learn how to configure an HTTP Client connection, create data mappings and connect to a SQL database using Boomi’s Database (Legacy) connector. We’ll walk through each step, from setup to validation, so even if you’re new to Boomi, you can follow along and get results.

Setup prerequisites: What you need to connect a REST API to a SQL Database with Boomi

Before diving into the step-by-step process, make sure you have the following components ready. These are essential for building and testing your Boomi integration:

Before diving into the step-by-step process, make sure you have the following components ready. These are essential for building and testing your Boomi integration:

1. Boomi Account

If you don’t already have access to Boomi, you can sign up for a free trial: Boomi Free Trial

2. NASA API Key

We’ll be using NASA’s Astronomy Picture of the Day (APOD) API. Get your free API key here: https://api.nasa.gov/

3. Sample JSON File

You’ll need a sample response to generate your response profile in Boomi.
Here is a sample file:

1{
2    "copyright": "\nOgetay Kayali\n(Michigan Tech U.)\nText: \nOgetay Kayali\n(Michigan Tech U.)\n",
3    "date": "2025-07-08",
4    "explanation": "If you have looked at the sky and seen a group of stars about the size of the full Moon, that's the Pleiades (M45). Perhaps the most famous star cluster in the sky, its brightest stars can be seen even from the light-polluted cities. But your unaided eye can also see its nebulosity -- the gas and dust surrounding it -- under dark skies. However, telescopes can catch even more. The bright blue stars of the Pleiades, also known as the Seven Sisters, light up their surrounding dust, causing it to appear a diffuse blue that can only be seen under long exposures. But that's not all. The cosmic dust appears to stretch upward like ethereal arms. And the entire structure is surrounded by a reddish glow from the most abundant element in the universe: hydrogen. The featured image is composed of nearly 25 hours of exposure and was captured last year from Starfront Observatory, in Texas, USA  Explore Your Universe: Random APOD Generator",
5    "hdurl": "https://apod.nasa.gov/apod/image/2507/Pleiades_Kayali_2560.jpg",
6    "media_type": "image",
7    "service_version": "v1",
8    "title": "The Pleiades in Red and Blue",
9    "url": "https://apod.nasa.gov/apod/image/2507/Pleiades_Kayali_1080.jpg"
10}

4. SQL Database

You’ll need access to a SQL database, either locally or in the cloud (e.g. Azure SQL, AWS RDS, or SQL Server Express).
Create a database named Customers in SQL and add the following table structure:

1CREATE TABLE [dbo].[APOD](
2	[id] [int] IDENTITY(1,1) NOT NULL,
3	[Request_DateTime] [datetime] NULL,
4	[IMG_Date] [date] NULL,
5	[Title] [varchar](max) NULL,
6	[Explaination] [text] NULL,
7	[IMG_URL] [text] NULL,
8PRIMARY KEY CLUSTERED 
9(
10	[id] ASC
11)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
12) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
13

Once you’ve completed these steps, you’re ready to start building your Boomi integration process.

Step-by-Step: Build a REST API to SQL Database Integration in Boomi (HTTP Client + Database Connector)

Before we start building the process, it’s worth taking a moment to understand what we’re about to create. In the next steps, we’ll configure Boomi to call a REST API, capture the JSON response, map the fields into a SQL-ready structure and insert the data into a database table using Boomi’s Database (Legacy) connector.

By the end of this walkthrough, you’ll have a working integration flow you can reuse as a template for many real scenarios, from syncing SaaS data into reporting tables to feeding operational dashboards with external API data.

Here’s the exact step-by-step process to build it in Boomi from start to finish:

• Step 1: Create a new process by clicking the three dots beside a folder in the left hand file directory and clicking New Component.

• Step 2: On the start step, select HTTP Client in the connector box, then click the green + in the connection box to create a new connection.

• Step 3: Add the APOD API URL to the URL box and add your API key in replacement of “DEMO_KEY” https://api.nasa.gov/planetary/apod?api_key=DEMO_KEY
  

Save and Close the new connection.

• Step 4: Create a new Operation by clicking the green + at the end of the Operation field.
  Set the Response profile type to JSON, the content type to application/json and check the return HTTP Errors.

• Step 5: We need to generate the response profile by clicking the green + next at the end of the Response Profile Field then click Import profile and import the apod.json file supplied at the beginning of this tutorial.

Click next, then click Finish.
Then click save and close the profile and save and close the operation.
Click “ok” on the start step.

• Step 6: Now we have our source setup lets go ahead and setup our Target.
  Drag the Database (Legacy) Connector to the process canvas.
  The connector step should automatically open, click the + on the connection field.
  Add your Host address, credentials and Database name then click save & close.

Test the connection using the test button at the top right using your preferred runtime. I will be using Boomi cloud runtime.
Set the action to Send on the connector step.

• Step 7: Next create a new operation using the green + in the operation field.

Then click the green + in the profile field.
Click on Statement, then change the type to Dynamic Insert, then click the import button at the top right.

Click next on the Database Import Wizard, then select the APOD table.

Click next, select all of the fields then click next.

Click finish, click Save & Close, click save & close again on the operation, then click OK on the connector step.
We now have both our source and targets.

• Step 8: Drag a stop shape on to the canvas and connect the Database connector to it.

Drag a map shape to the canvas, the map step will open automatically. We need to create a new map by clicking the green + on the map field.

Add the Source profile to the source column by clicking Choose at the top of that column selecting the JSON profile we created earlier. Do the same for the target profile selecting the database profile we generated earlier.

Drag the fields from the left (source) side to the right (target) side fields as per the following mapping document.

• Step 9: In the functions column, click the blue plus at the top, then select date from the type column and select Get Current Date.

Drag the result from the function box to the Request_DateTime field on the target.

Click Save and Close then OK on the Map Step.

• Step 10: Join the HTTP Source Connector to the map and the Map object to the Database Target Connector.

Click save and then test and select the runtime you wish to use, I will be using the Boomi Cloud Runtime, if all works as it should each object in the process will have a green hue, if it is not it will be a red hue and you can click on the object and inspect the logs below to find the cause of the issue.

In the database, we should see a new row.

Conclusion

So lets recap, we have created a connection from the NASA API using HTTP connector, mapped the JSON output to the Database profile and inserted it in to the database using the database legacy connector. We used the legacy connector as it is preconfigured with the correct JDBC drivers for MS SQL and would save us having to upload and configure our own JDBC drivers.

Getting data from a REST API into a SQL database can often seem more technical than it should be. But with Boomi, the process becomes clear and manageable, even for teams with limited coding experience.

This approach is not only efficient, it’s also scalable. Once you’ve mastered the flow, you can apply the same logic to many other APIs and data sources, reducing manual work and improving data consistency across your systems.

If your organisation needs to integrate systems or you're looking for expert support to get started, we can help. Our team specialises in designing Boomi-based solutions that align with your architecture and business goals. Get in touch to explore how we can support your next integration project.

Chris Fry

Share