Converting REST API JSON Responses to SQL INSERT Statements

REST API Response to SQL

Real-world API responses are rarely flat arrays. They typically include metadata envelopes, pagination, and nested data that must be unwrapped before SQL conversion.

Typical API Response

{
  "status": "success",
  "data": {
    "users": [
      { "id": 1, "name": "Alice", "created_at": "2024-06-15T09:30:00Z" },
      { "id": 2, "name": "Bob", "created_at": "2024-06-14T14:22:00Z" }
    ]
  },
  "pagination": {
    "page": 1,
    "per_page": 20,
    "total": 152
  }
}

Step 1: Extract the Data Array

The converter needs to identify and extract the actual data from the envelope. Common patterns:

• response.data -- standard envelope
• response.data.users -- nested under a named key
• response.results -- alternative naming
• response.items -- another common pattern

Step 2: Generate SQL

INSERT INTO users (id, name, created_at) VALUES
  (1, 'Alice', '2024-06-15T09:30:00Z'),
  (2, 'Bob', '2024-06-14T14:22:00Z');

Handling Pagination

For paginated APIs, the converter can generate SQL for each page and combine them:

-- Page 1
INSERT INTO users (id, name, created_at) VALUES
  (1, 'Alice', '2024-06-15T09:30:00Z'),
  (2, 'Bob', '2024-06-14T14:22:00Z');

-- Page 2 (separate request)
INSERT INTO users (id, name, created_at) VALUES
  (3, 'Charlie', '2024-06-13T11:00:00Z'),
  (4, 'Diana', '2024-06-12T08:15:00Z');

Nested Objects in API Responses

API responses often include nested related objects:

{
  "id": 1,
  "name": "Alice",
  "department": { "id": 10, "name": "Engineering" }
}

The converter can either flatten (department_id, department_name) or normalize into separate tables.

Incremental Import Pattern

For periodic syncs, use timestamps to fetch only new data:

-- Use UPSERT to handle re-fetched records
INSERT INTO users (id, name, created_at) VALUES (...)
ON CONFLICT (id) DO UPDATE SET
  name = EXCLUDED.name,
  created_at = EXCLUDED.created_at;

Common API Providers

The converter handles response formats from popular APIs including GitHub, Stripe, Shopify, Slack, and Twilio, each with their own envelope structure and pagination style.