REST API to PostgreSQL: How to Load Your Data

There is no connector for your API. There are more than 600 in the catalogue and yours is not one of them, because it is an internal service, a regional vendor, or something niche enough that nobody has contributed one yet.

The usual next step is a Python script, a cron entry, and a slow accumulation of retry logic, pagination handling, and state management that nobody wants to own. The alternative is to build the connector declaratively and let the platform own the hard parts.

What do you need from the API first?

Before opening anything, get four things from the API documentation.

The base URL and the endpoint you want. How authentication works, whether that is a bearer token, an API key in a header, basic auth, or OAuth2. How pagination works, whether by page number, offset, cursor token, or a link header. And whether there is any way to request only recently changed records, usually a date filter parameter.

That fourth one determines whether you get incremental sync or reload everything on every run, so it is worth looking for properly rather than assuming it does not exist.

How do you build the connector?

The Connector Builder is a UI over a declarative specification. You define streams, and for each stream you set the URL path, the record selector, the authenticator, and the paginator.

The record selector is where most first attempts go wrong. APIs rarely return a bare array. They return an object with the records nested under a key such as data, results, or items, or something entirely idiosyncratic. Point the selector at that path rather than at the response root.

Pagination is then configured to match the API: page increment for numbered pages, offset increment for offset and limit, and cursor pagination for tokens or link headers. Get this right in the builder's test panel, where you can watch the actual requests going out and the records coming back, before you save anything.

For incremental sync you specify a cursor field, its datetime formats in order of preference, a start datetime, and how the time filter is injected into the request. A typical configuration injects the cursor as a request parameter and sets the end datetime to now. Airbyte saves the highest cursor value it saw and resumes from there on the next run.

If the API is well documented, the AI Assistant can generate a first draft of the configuration from the docs, which you then correct in the test panel rather than writing from scratch.

Where does the data land in Postgres?

The Postgres destination writes each stream to a table in your target schema, with your fields as typed columns plus Airbyte's metadata columns. This half of the pipeline is well-trodden, so most of your attention belongs on the source.

One thing does deserve thought at design time. The shape of what arrives depends on how nested your API responses are. Deeply nested objects land as JSON columns, which Postgres queries perfectly well with its JSON functions and operators, but which will not behave like flat columns in a BI tool. If your consumers expect flat tables, plan a dbt model or a view on top rather than expecting the connector to flatten arbitrary nesting for you.

Which sync mode should you choose?

If the API supports a date filter and records carry a reliable updated timestamp, use incremental append with deduplication on the record's primary key. You get a table mirroring the API's current state, with history handled underneath.

If the API has no filter, you are running full refresh on every sync, and sync frequency becomes a rate-limit question. Check the published limits and set a schedule that stays comfortably inside them.

If the records are immutable events, incremental append is simpler and cheaper than deduplicating something that never changes.

The test most people skip

Test the connector across a real page boundary before you trust it. A paginator that works on a single page of results and breaks on the second is the most common failure in a hand-built connector, and it looks exactly like success until data volume grows.

In the test panel, force a small page size, confirm that page two arrives, and check that no records are duplicated or dropped across the boundary. Five minutes here saves a silent data gap later.

Frequently asked questions

Do I need to write code?

No. The Connector Builder is declarative and configured through a UI. Python is available through the CDK if you need request logic the builder cannot express, but most REST APIs do not require it.

What if the API has no incremental filter?

You run full refresh on every sync. Set the frequency to respect the API's rate limits, and consider whether incremental append against a monotonically increasing ID is possible instead of a timestamp.

How do nested JSON objects land in Postgres?

As JSON columns, which you query with Postgres JSONB operators. If you need flat columns, model them downstream rather than expecting the connector to flatten arbitrary nesting.

Can I share the connector I build?

Yes. Connectors built in the builder can stay private to your workspace or be contributed to the public catalogue for others to use and maintain.

Get your API data into PostgreSQL

Build the stream, verify pagination across a page boundary, pick a cursor if the API gives you one, and let the platform handle state and retries. For more on the general pattern, see our guide to moving data from an API to a database. Replicating between databases instead? See MySQL to PostgreSQL.

Start syncing now →