Creating a DELETE Endpoint in a Data Virtualization API

Introduction

Data Virtualization endpoints in FME Flow support removing records from a dataset. A DELETE endpoint removes a specific resource by referencing its unique identifier in the URL path.

Unlike operations that create or update data, a DELETE request does not include a request body. Instead, the resource to be removed is identified entirely by a path parameter in the URL. This makes deletion one of the simplest write operations. However, the workspace must still verify that the target record exists before attempting to remove it, and return the appropriate response for both successful and unsuccessful requests.

After completing this lesson, you will be able to add a DELETE endpoint with a path parameter, verify that a target record exists before deleting it, remove a record from a database using a FeatureWriter, and return the appropriate HTTP response codes (200, 404).

Requirements

• Completed Create a Data Virtualization API prior to proceeding

Step-by-Step Instructions

In this exercise, the EnvironData Coordination Office wants to allow authorized users to remove outdated or erroneous wildfire records from their system. They will create a DELETE /wildfires/{id} endpoint in their EnvironData API. When a client submits a delete request, the workspace verifies that the targeted wildfire record exists in the database, removes the record if found, and returns a 200 OK response on success or a 404 Not Found response if the record does not exist.

1. Create an Endpoint

Navigate to the Data Virtualization Environmental Impact and Response API in FME Flow. Open the Endpoints tab. Click Create to begin the DELETE endpoint.

In the Endpoint Details, set the following:

• Path: wildfires/{id}
• Operation: DELETE
• Summary: Delete a wildfire record
• Description: Removes a wildfire event by ID
• Tags: events
• Security: Inherit

Unlike POST and PUT, DELETE does not require a request body schema. The resource is identified entirely by the {id} path parameter.

2. Configure the Path Parameter

Because the path contains {id}, a Path Parameters tab is now available. Open this tab and configure the path parameter with the following values:

• Name: id
• Type: String
• Description: The ID of the wildfire record to delete

3. Configure the Response

Open the Response tab. For Response Type, click Workspace. Leave Inherit API Job Queue and Inherit API Job Expiry Time enabled.

Click Add Status Code to define response codes. Add two status codes.

First, define a 200 – OK status code, used when the record is deleted successfully:

• HTTP Status Code: 200 - OK
• Description: Record deleted successfully
• Content-Type: application/json
• Response Structure: Create Properties
• JSON Type: Object
• Property:
  • Name: Message
  • Type: String

Add a second status code, 404 – Not Found, for requests targeting a record that does not exist:

• HTTP Status Code: 404 - Not Found
• Description: Wildfire record not found
• Content-Type: application/json
• Response Structure: Create Properties
• JSON Type: Object
• Property:
  • Name: ErrorMessage
  • Type: String

Because DELETE operates on a specific resource by ID, a 404 is the appropriate response when the client references an ID that does not exist, rather than silently succeeding or returning a generic error.

Click Create to save the endpoint.

4. Assign a Workspace

Open the Workspaces tab. The new DELETE endpoint is listed with Workspace Status as "Unassigned".

Click the DELETE /wildfires/{id} endpoint and expand the Endpoint Actions menu. Click Generate Workspace. Name the workspace DeleteWildfire.fmw.

Once assigned, the Workspace Status updates to "Needs Authoring".

5. Download the Workspace

In FME Workbench, open the Data Virtualization menu and click the DELETE /wildfires/{id} workspace to open it.

The workspace opens with a Data Virtualization reader of type DELETE /wildfires/{id}.

6. Use a DELETE Template

Run the workspace.

In the Translation Parameter Values dialog, click Use Template and update the template with sample data:

{
   "method": "DELETE",
   "path": "/wildfires/{id}",
   "pathParameters": {
      "id": "FIR-101"
   }
}

DELETE templates do not include a body section, unlike PUT and POST templates. DELETE carries no request payload.

Click Save As Sample and give the template a name, for example, delete_wildfires_{id}-sample.json.

7. Check Whether the Record Exists

Before deleting, the workspace must verify that the target wildfire record exists. Attempting to delete a non-existent record should return a 404 rather than silently succeed.

On the top menu, click Add Transformer. In the Add Transformer dialog, search for and select SQLExecutor. Connect it to the DELETE /wildfires/{id} feature type.

Double-click the SQLExecutor to configure its parameters. Set the following:

• Connection: events SQLite database
• SQL Statement: SELECT fire_id FROM fires WHERE fire_id = '@Value(path.id)'

This query returns a _matched_records attribute indicating how many matching rows were found.

8. Test Whether the Record Was Found

Connect the Initiator port of the SQLExecutor to a Tester.

On the top menu, click Add Transformer. In the Add Transformer dialog, search for and select Tester.

Double-click the Tester and configure the following clause:

• Clause 1:
  • Left Value: _matched_records
  • Operator: =
  • Right Value: 0

Features that pass this test (no matching record found) return a 404 response. Features that fail it (the record exists) proceed to the deletion step.

Click OK to close.

9. Delete the Database Record

Add a FeatureWriter to the canvas and connect it to the Failed port of the Tester from the previous step (where _matched_records is not 0, meaning the record exists).

On the top menu, click Add Transformer. In the Add Transformer dialog, search for and select FeatureWriter.

Double-click the FeatureWriter to configure its parameters. Set the connection to the events SQLite database, and set the following:

• Table Name: fires
• Table Handling: Use Existing
• Feature Operation: Delete
• WHERE Clause: fire_id = '@Value(path.id)'

The fire_id column is the WHERE clause key. FME deletes only the row where fire_id matches the value from path.id.

10. Format Successful Response Attributes

Add an AttributeCreator and connect it to the FeatureWriter's Summary port.

On the top menu, click Add Transformer. In the Add Transformer dialog, search for and select AttributeCreator.

Double-click the AttributeCreator and create the response attributes for the 200 status code:

• Output Attribute: response.status_code
  • Value: 200
• Output Attribute: response.body.content
  • Value: {"Message": "Record deleted successfully"}
• Output Attribute: response.body.content_type
  • Value: application/json

Connect this AttributeCreator to the Data Virtualization writer, http_response.

11. Format Not Found Response Attributes

Add another AttributeCreator and connect it to the Passed port of the Tester from the earlier step (where _matched_records equals 0).

Double-click the AttributeCreator and create the response attributes for the 404 status code:

• Output Attribute: response.status_code
  • Value: 404
• Output Attribute: response.body.content
  • Value: {"ErrorMessage": "Wildfire record not found"}
• Output Attribute: response.body.content_type
  • Value: application/json

Connect this AttributeCreator to the Data Virtualization writer, http_response.

Both response paths are now complete.

12. Test the Workspace

Run the workspace with the sample template from the earlier step.

To confirm the deletion, open the events.sqlite database in the FME Data Inspector and verify that the targeted row no longer appears.

Save the workspace.

13. Publish to FME Flow

Publish the workspace to FME Flow. Confirm the DELETE /wildfires/{id} Workspace Status is now Assigned.

14. Test the Request from a Client

Find the DELETE /wildfires/{id} endpoint in the EnvironData API Swagger documentation. Click Try It Out.

Set the id path parameter to an existing wildfire ID (for example, FIR-103). Click Execute.

A 200 – OK response confirms the DELETE /wildfires/{id} endpoint works correctly.

To test the 404 path, repeat with an ID that does not exist.