· Charlotte Will · Amazon API  · 8 min read

How to Use Amazon RDS for Relational Database Management with API Data

Discover how to efficiently manage relational databases using Amazon RDS and API data. This comprehensive guide offers practical advice, keyword integration, and internal linking to related articles for a well-rounded SEO strategy.

Discover how to efficiently manage relational databases using Amazon RDS and API data. This comprehensive guide offers practical advice, keyword integration, and internal linking to related articles for a well-rounded SEO strategy.

Introduction

Most teams that pull data from an Amazon API end up in the same place: a JSON file on disk and nowhere to put it. The Selling Partner API returns order records and inventory levels a page at a time, on a schedule you set yourself. Storing that properly means settling on an engine and a schema, plus finding somewhere to run it.

Amazon RDS takes care of the running part: you pick the engine and the instance size, and Amazon handles the storage and the engine patching. What you keep is the schema and the code that fills it.

Provisioning an RDS instance

The RDS API is scriptable, which matters the moment you have more than one environment. boto3 exposes it through the rds client, and the create call takes the engine and the instance class as plain arguments.

import os

import boto3

rds = boto3.client('rds', region_name='us-east-1')

rds.create_db_instance(
    DBInstanceIdentifier='seller-metrics',
    Engine='postgres',
    EngineVersion='15.4',
    DBInstanceClass='db.t4g.medium',
    AllocatedStorage=50,
    StorageType='gp3',
    StorageEncrypted=True,
    MasterUsername='dbadmin',
    MasterUserPassword=os.environ['RDS_MASTER_PASSWORD'],
    BackupRetentionPeriod=14,
    PubliclyAccessible=False,
)

rds.get_waiter('db_instance_available').wait(
    DBInstanceIdentifier='seller-metrics'
)

db.t4g.medium is burstable, and it throttles if a backfill pushes it past its CPU credit balance. db.m6g.large costs more per hour but holds a steady rate. Storage is billed separately from compute, so you can grow the volume later with modify_db_instance without changing the endpoint.

Leave PubliclyAccessible off. A loader outside the VPC then needs a bastion host or a VPC endpoint to reach it. Getting the API responses is a separate problem, covered in What is Amazon SP-API for Automated Data Collection?.

Designing the schema for API data

Amazon API responses are nested and mostly optional, so a schema mirroring the raw JSON fights you later. A flat set of typed columns, plus one JSONB column for everything else, behaves better.

import os

import psycopg2

conn = psycopg2.connect(
    host=os.environ['RDS_HOST'],
    port=5432,
    dbname='seller_metrics',
    user='dbadmin',
    password=os.environ['RDS_MASTER_PASSWORD'],
    connect_timeout=10,
)

with conn:
    with conn.cursor() as cur:
        cur.execute("""
            CREATE TABLE IF NOT EXISTS order_records (
                order_id        TEXT PRIMARY KEY,
                marketplace_id  TEXT NOT NULL,
                purchase_date   TIMESTAMPTZ,
                total_amount    NUMERIC(12, 2),
                item_count      INTEGER,
                raw_payload     JSONB NOT NULL,
                ingested_at     TIMESTAMPTZ NOT NULL DEFAULT now()
            )
        """)
        cur.execute("""
            CREATE INDEX IF NOT EXISTS idx_order_records_purchase
            ON order_records (purchase_date)
        """)

The order_id is the natural key, and every SP-API order record carries one. That choice decides a lot here. Without a stable identifier the upsert has no target, so duplicate rows accumulate and reporting queries get slower.

raw_payload exists so a field you did not think of on day one is still recoverable. It has a price: storage grows faster than a typed schema, and queries that ignore the typed columns get slower. Treat it as a holding area.

Upserting records without duplicating rows

Backfills get re-run. A script that half failed on Tuesday needs to run again on Wednesday and leave the same table behind, not a doubled one. ON CONFLICT gives you that.

import json

UPSERT_SQL = """
    INSERT INTO order_records (
        order_id, marketplace_id, purchase_date,
        total_amount, item_count, raw_payload
    )
    VALUES (%s, %s, %s, %s, %s, %s)
    ON CONFLICT (order_id) DO UPDATE SET
        purchase_date = EXCLUDED.purchase_date,
        total_amount  = EXCLUDED.total_amount,
        item_count    = EXCLUDED.item_count,
        raw_payload   = EXCLUDED.raw_payload,
        ingested_at   = now()
    RETURNING order_id
"""


def upsert_order(cur, record):
    cur.execute(
        UPSERT_SQL,
        (
            record['order_id'],
            record['marketplaceId'],
            record.get('purchaseDate'),
            record.get('totalAmount'),
            len(record.get('orderItems', [])),
            json.dumps(record),
        ),
    )
    return cur.fetchone()

Parameterised queries matter for a practical reason. Values from a JSON payload are untrusted input, and the %s placeholders let psycopg2 handle escaping instead of you. Building the statement with an f-string puts anyone who can influence a field value in a position to change the query.

ON CONFLICT ... DO UPDATE is idempotent by construction. Send the same order twice and you get one row holding the newer values. For the live side, where you chase changes rather than history, Maximizing Efficiency with Real-Time Data Sync via Amazon PA-API covers the pricing feed and where it fits in the pipeline.

Pooling connections with SQLAlchemy

Opening a fresh TCP connection and a fresh Postgres backend for every page of API results is slow, and Postgres caps concurrent connections per instance. A pool fixes both problems at once.

from sqlalchemy import create_engine, text

engine = create_engine(
    'postgresql+psycopg2://[email protected]:5432/seller_metrics',
    pool_size=5,
    max_overflow=10,
    pool_timeout=30,
    pool_recycle=1800,
    pool_pre_ping=True,
)

with engine.connect() as conn:
    rows = conn.execute(
        text("SELECT marketplace_id, count(*) FROM order_records GROUP BY 1"),
    ).all()

pool_recycle=1800 is there because RDS closes idle connections after a while, and the error you get back is a dropped connection partway through a query, which is confusing when it lands on the first request of a scheduled job. pool_pre_ping costs one round trip and clears out dead ones first.

Set pool_size from the connection limit of the instance, not from the size of your application. db.t4g.medium sits far lower than a large instance, and going over the limit produces too many clients already errors rather than a slow query.

Backfilling historical data with pagination

SP-API endpoints hand back a NextToken whenever there is more to fetch. A backfill is a loop that follows that token, and a good one can be resumed after a failure.

from datetime import datetime, timezone

import requests

API_ROOT = 'https://sellingpartnerapi-na.amazon.com'


def fetch_orders(access_token, marketplace_id, next_token=None, page_size=50):
    url = f'{API_ROOT}/orders/v0/orders'
    params = {
        'marketplaceIds': [marketplace_id],
        'limit': page_size,
    }
    if next_token:
        params['NextToken'] = next_token

    response = requests.get(
        url,
        params=params,
        headers={
            'x-amz-access-token': access_token,
            'x-amz-date': datetime.now(timezone.utc).strftime('%Y-%m-%dT%H:%M:%SZ'),
        },
        timeout=30,
    )
    response.raise_for_status()
    return response.json()


def backfill(conn, access_token, marketplace_id):
    next_token = None
    seen = 0

    while True:
        page = fetch_orders(access_token, marketplace_id, next_token)
        records = page.get('payload', {}).get('Orders', [])

        with conn:
            with conn.cursor() as cur:
                for record in records:
                    upsert_order(cur, record)

        seen += len(records)
        next_token = page.get('payload', {}).get('NextToken')
        if not next_token:
            break

    return seen

Real SP-API requests also need SigV4 signing, which adds an Authorization header the call above does not build. The SP-API article linked earlier shows the full shape.

SP-API does not expose every field a seller wants to track, and a scraping API is a common second source feeding the same tables. How to Use Amazon Scraping API for E-Commerce Data Extraction covers that route.

Two details decide whether a multi-million row backfill survives. Rate limits apply across the whole SP-API operation family, so back off on a 429 instead of retrying at once. And a run that dies halfway should be resumable, which the upsert makes possible: re-running from the start of a date range is safe because the writes are idempotent.

Monitoring connections and storage usage

A database that fills its disk or runs out of connection slots fails in ways that are awkward to diagnose from an application log. Two queries cover most of it.

def monitor(conn):
    with conn.cursor() as cur:
        cur.execute("""
            SELECT count(*) AS connections,
                   count(*) FILTER (WHERE state = 'active') AS active
            FROM pg_stat_activity
            WHERE datname = current_database()
        """)
        connections, active = cur.fetchone()

        cur.execute("""
            SELECT pg_size_pretty(pg_database_size(current_database())) AS size
        """)
        size = cur.fetchone()[0]

    return {
        'connections': connections,
        'active': active,
        'database_size': size,
    }

pg_stat_activity counts every session pointed at the instance, including the one asking the question. If that number sits near the limit for the class, the fix is a smaller pool or a bigger instance, not a faster query.

For storage, watch the FreeStorageSpace metric in the RDS console and alarm before it reaches zero. gp3 volumes grow on their own up to a ceiling, though autoscaling never shrinks them again. A database that has run a year of daily backfills also holds many rows you may not need.

Conclusion

RDS gives you a managed Postgres at a predictable per-hour cost, and the parts you still own decide data quality: the schema and the primary key, plus the logic that writes the rows. Provision the instance with boto3 and model the schema around a natural key, keeping unmodelled fields in a JSONB column. Pool the connections, then page through the API in a loop that can be re-run safely. Two bills stay with you either way: the capacity you provision, and every row you keep. Decide what you need to retain rather than storing everything you can fetch again.

FAQs

  1. Does Amazon RDS include backups and database maintenance? Yes. RDS takes automated snapshots on a schedule you set and keeps them for a retention window you choose. It also patches the engine and the underlying operating system. You do not run the database server or the backup tooling yourself.

  2. What is the difference between RDS and a database running on EC2? With RDS, Amazon owns the host and the disk and handles failover. With EC2 you manage the operating system and the Postgres install yourself. RDS costs more at the same instance size, and you give up some low-level control in exchange.

  3. How do I avoid duplicate rows when a backfill runs twice? Give the table a natural primary key, such as order_id, and write inserts with ON CONFLICT (order_id) DO UPDATE. The second run updates the existing rows instead of inserting new ones.

  4. Can I store the raw JSON response alongside the parsed columns? Yes. PostgreSQL has a JSONB type, and you can read a field out of it with the -> and ->> operators. Keep it as a fallback for fields you have not modelled, since it grows the table faster than typed columns do.

  5. Is the Amazon MWS API still usable for order management? No. Amazon ended support for the Marketplace Web Service on April 1, 2024, so new integrations need the Selling Partner API. Older workflows written against MWS are described in How to Integrate Amazon MWS API for Order Management, which is worth reading as a migration reference rather than a starting point.

    Back to Blog

    Related Posts

    View All Posts »
    What is Amazon Vendor Central API?

    What is Amazon Vendor Central API?

    Discover how Amazon Vendor Central API can revolutionize your vendor operations on Amazon. Learn about its features, benefits, and practical uses in this comprehensive guide.