REST API to Postgres: Sync Any API with a YAML Manifest

Loading a REST API into Postgres means walking every page of an authenticated endpoint, typing the JSON, and upserting rows on a schedule without rereading everything. As of October 2026 you have five options, a YAML manifest, a Python library like dlt, a hosted builder like Airbyte or Hevo, hosted code on Fivetran, or your own script. What decides is which one owns pagination, cursors, rate limits, and typed upserts.

One clarification first. This guide is about pulling API data into Postgres, not about exposing Postgres as an API, which is the PostgREST job most results for this phrase describe.

What a REST API sync actually has to do

An API sync is four small problems stacked together, and every tool is a bundle of answers to them. The API you are syncing picks one convention for each.

Pagination comes first because no API returns everything at once. RFC 8288 standardizes the Link header, and GitHub's docs say "the URL for the next page is followed by rel="next"" with per_page capped at 100, per GitHub. Stripe returns a has_more flag and takes the last object id as starting_after, per Stripe. Page numbers and offsets are the other common styles.

Auth is a bearer token, basic auth, an API key, or OAuth 2 client credentials, per the OAuth spec. Rate limits arrive as HTTP 429, which "MAY include a Retry-After header indicating how long to wait," per RFC 6585. GitHub allows 5,000 requests per hour and tells you to wait for the reset time, per GitHub.

Incremental reads make the sync cheap after the first run. A cursor such as updated_at "means that records whose updated_at value is less than or equal than that cursor value have been synced already," per Airbyte. A lookback window rewinds the bound to catch late edits. A cursor cannot see a hard delete, so deletes need a full run or an events feed.

The destination shape is the last call. Postgres jsonb "is stored in a decomposed binary format" that is faster to query and indexable, per the Postgres docs. The working pattern is typed columns for the fields you query plus a jsonb remainder. Writes should use INSERT ON CONFLICT DO UPDATE, which the Postgres docs call UPSERT and guarantee as "an atomic INSERT or UPDATE outcome," so retries converge.

How to choose a tool for syncing an API to Postgres

Six criteria separate the approaches, and the first two matter most.

• Declaration surface. YAML, a Python dict, UI forms, or full code decides who on the team can add the next endpoint.

• Pagination and auth coverage. Link header, cursor, offset, page number, and body cursors on one side, bearer, basic, API key, and OAuth 2 on the other.

• Incremental cursor with lookback, and whether an interrupted full read resumes mid-listing or restarts.

• Rate limit handling. Honoring Retry-After, a static limiter, and backoff rules declared per API.

• Destination shape and write mode. Typed columns or JSON blobs, and whether upsert on a primary key is the default.

• Deployment and cost. Library, self-hosted server, embedded engine, or vendor cloud, and whether the bill scales with rows.

Five ways to sync a REST API to Postgres

Every option below lands API data in Postgres, and each trades a different thing away.

dlt's rest_api source extracts "data from any REST API" through "declarative configuration" in a Python dict, per dlt, and its Postgres destination supports merge and a COPY fast path, per dlt. The LICENSE is Apache 2.0. The documented limit is that pagination "is supported only for GET requests" except one paginator, and the library ships no server or scheduler. The managed tier, dltHub, lists about $12,000 a month, per dltHub.

Airbyte's Connector Builder is "a no-code tool" that creates "a YAML manifest behind the scenes," per Airbyte, and it has the richest documented pagination and auth matrix of the hosted options. It builds sources only. Airbyte's own Postgres destination page says to "only consider using postgres as a destination for small data volumes (e.g. less than 10GB)," per Airbyte.

The platform LICENSE is ELv2, and Cloud bills API sources at 6 credits per million rows, per Airbyte.

Fivetran's Connector SDK lets you "develop a custom data connector using Python" that Fivetran hosts and schedules, per Fivetran. You write pagination, auth, and cursor logic yourself, and usage is billed in Monthly Active Rows. Keeping compute in your own network needs an Enterprise or Business Critical plan.

Hevo's REST API source is pure UI with session, page number, or offset pagination, per Hevo. The limit is structural. Hevo "cannot perform only incremental ingestion," so each run "begins ingesting data from the start," and every event is billable including the initial load. Plans start at $299 a month, per Hevo pricing.

Sling's YAML API specs are marked "CLI Pro Required," per Sling, at $79 a month per Sling, under a GPL 3.0 LICENSE. Meltano's tap-rest-api-msdk offers nine pagination styles, per Meltano Hub, while target-postgres defaults to append-only, per Meltano Hub. A script on psycopg's COPY, per psycopg, starts fastest and leaves you owning retries, cursors, and schema drift.

Where the Filament manifest fits

Filament answers all six criteria with one YAML file read by a generic HTTP engine that writes typed, verified, upserted tables into Postgres. "One generic HTTP connector interprets declarative YAML that declares endpoints, auth, pagination, field projection, incremental cursors, and parent and child relationships," per Filament. The GitHub and Stripe sources are built this way.

The manifest grammar declares Link header, cursor, offset, page number, and next URL pagination plus bearer, header, basic, and OAuth client credentials auth, per the HTTP manifests page. An incremental block names the cursor field, where the lower bound goes, a comparator, and an overlap window. A per-connection rate limit can adjust from reset headers, and throttling responses "are retried up to 10 attempts, honoring Retry-After."

On resilience, "top-level resources checkpoint their pagination cursor per page, so an interrupted full run resumes mid-listing," and a missing cursor fails the run rather than skipping records. Every batch is checksummed with CRC32-C on both sides of the write, per Filament.

The Postgres sink is beta, per its docs, and it creates typed tables, maps json to jsonb, evolves schemas add-only, and upserts with ON CONFLICT DO UPDATE. Filament ships as a binary, a Go library, or a Helm chart under Apache 2.0 and is pre-1.0, per the announcement. Filament is built by Galaxy, which also publishes this guide.

How to sync a REST API to Postgres with a Filament manifest

The walkthrough builds a private GitHub connector from scratch, registers it without recompiling anything, and lands repositories and issues as typed Postgres tables. Swap the base URL, auth, and resources for any API.

Step 1. Install the CLI

curl -fsSL https://getgalaxy.io/filament/install | sh

curl -fsSL https://getgalaxy.io/filament/install | sh

curl -fsSL https://getgalaxy.io/filament/install | sh

The local context runs in process with file-backed SQLite, per the CLI docs.

Step 2. Write the manifest

Create the connector directory under its own name and API version.

mkdir -p

mkdir -p

mkdir -p

Save this as ~/filament-manifests/internal_github/2022-11-28/manifest.yaml. It declares repositories and a child list of issues that reads incrementally by updated_at.

version: 1
name: internal_github
display_name: Internal GitHub
api_version: "2022-11-28"
config:
  token:
    type: secret
    required: true
    help: Personal access token
  organization:
    type: string
    required: true
defaults:
  method: GET
  query: { per_page: "100" }
  response:
    records: $
    pagination: { link: next }
connection:
  base_url: https://api.github.com
  auth:
    bearer: config.token
  headers:
    X-GitHub-Api-Version: "2022-11-28"
  rate_limit:
    requests_per_second: 1
    dynamic:
      remaining_header: X-RateLimit-Remaining
      reset_header: X-RateLimit-Reset
      reset_format: unix_seconds
resources:
  - name: repositories
    path: /orgs/{organization}/repos
    params: { organization: config.organization }
    primary_key: [id]
    fields:
      id: int64
      name: string
      description: string?
      raw: { path: $, type: json, mode: remainder }
    capture: { repository: name }
  - name: issues
    path: /repos/{organization}/{repository}/issues
    params:
      organization: config.organization
      repository: parent.repository
    for_each: repositories
    primary_key: [id]
    fields:
      id: int64
      title: string
      updated_at: timestamptz
      raw: { path: $, type: json, mode: remainder }
    incremental:
      cursor_field: updated_at
      start_param: since
      inject_into: query
      checkpoint_key: issues_updated_at
      comparator: time
      overlap_seconds: 300

version: 1
name: internal_github
display_name: Internal GitHub
api_version: "2022-11-28"
config:
  token:
    type: secret
    required: true
    help: Personal access token
  organization:
    type: string
    required: true
defaults:
  method: GET
  query: { per_page: "100" }
  response:
    records: $
    pagination: { link: next }
connection:
  base_url: https://api.github.com
  auth:
    bearer: config.token
  headers:
    X-GitHub-Api-Version: "2022-11-28"
  rate_limit:
    requests_per_second: 1
    dynamic:
      remaining_header: X-RateLimit-Remaining
      reset_header: X-RateLimit-Reset
      reset_format: unix_seconds
resources:
  - name: repositories
    path: /orgs/{organization}/repos
    params: { organization: config.organization }
    primary_key: [id]
    fields:
      id: int64
      name: string
      description: string?
      raw: { path: $, type: json, mode: remainder }
    capture: { repository: name }
  - name: issues
    path: /repos/{organization}/{repository}/issues
    params:
      organization: config.organization
      repository: parent.repository
    for_each: repositories
    primary_key: [id]
    fields:
      id: int64
      title: string
      updated_at: timestamptz
      raw: { path: $, type: json, mode: remainder }
    incremental:
      cursor_field: updated_at
      start_param: since
      inject_into: query
      checkpoint_key: issues_updated_at
      comparator: time
      overlap_seconds: 300

version: 1
name: internal_github
display_name: Internal GitHub
api_version: "2022-11-28"
config:
  token:
    type: secret
    required: true
    help: Personal access token
  organization:
    type: string
    required: true
defaults:
  method: GET
  query: { per_page: "100" }
  response:
    records: $
    pagination: { link: next }
connection:
  base_url: https://api.github.com
  auth:
    bearer: config.token
  headers:
    X-GitHub-Api-Version: "2022-11-28"
  rate_limit:
    requests_per_second: 1
    dynamic:
      remaining_header: X-RateLimit-Remaining
      reset_header: X-RateLimit-Reset
      reset_format: unix_seconds
resources:
  - name: repositories
    path: /orgs/{organization}/repos
    params: { organization: config.organization }
    primary_key: [id]
    fields:
      id: int64
      name: string
      description: string?
      raw: { path: $, type: json, mode: remainder }
    capture: { repository: name }
  - name: issues
    path: /repos/{organization}/{repository}/issues
    params:
      organization: config.organization
      repository: parent.repository
    for_each: repositories
    primary_key: [id]
    fields:
      id: int64
      title: string
      updated_at: timestamptz
      raw: { path: $, type: json, mode: remainder }
    incremental:
      cursor_field: updated_at
      start_param: since
      inject_into: query
      checkpoint_key: issues_updated_at
      comparator: time
      overlap_seconds: 300

Every projected field becomes a typed column, raw keeps the rest of the record as jsonb, and ? marks a nullable column.

Step 3. Add the registry file

Save this as ~/filament-manifests/internal_github/registry.json. Maturity is set per API version, and new connectors start at alpha.

{
  "schema_version": 1,
  "name": "internal_github",
  "default_version": "2022-11-28",
  "versions": {
    "2022-11-28": { "maturity": "alpha" }
  }
}

{
  "schema_version": 1,
  "name": "internal_github",
  "default_version": "2022-11-28",
  "versions": {
    "2022-11-28": { "maturity": "alpha" }
  }
}

{
  "schema_version": 1,
  "name": "internal_github",
  "default_version": "2022-11-28",
  "versions": {
    "2022-11-28": { "maturity": "alpha" }
  }
}

Step 4. Load the private catalog

export HTTP_MANIFESTS_DIR

export HTTP_MANIFESTS_DIR

export HTTP_MANIFESTS_DIR

Filament validates the whole directory at startup and reports every problem together. The connector appears as internal_github@2022-11-28 with internal_github as its default alias.

Step 5. Create the source and discover its resources

export GITHUB_TOKEN=ghp_your_token

filament source create gh \
  --source-connector internal_github \
  --source-token-env GITHUB_TOKEN \
  --source-organization your-org

filament source

export GITHUB_TOKEN=ghp_your_token

filament source create gh \
  --source-connector internal_github \
  --source-token-env GITHUB_TOKEN \
  --source-organization your-org

filament source

export GITHUB_TOKEN=ghp_your_token

filament source create gh \
  --source-connector internal_github \
  --source-token-env GITHUB_TOKEN \
  --source-organization your-org

filament source

Manifest config fields become CLI flags with a source- prefix, and the token is stored as an environment reference. Discovery lists repositories and issues with their primary keys.

Step 6. Create the Postgres sink

export POSTGRES_SINK_DSN=postgres://user:pass@localhost:5432/warehouse

filament sink create warehouse \
  --sink-connector postgres \
  --sink-connection-method url \
  --sink-dsn-env

export POSTGRES_SINK_DSN=postgres://user:pass@localhost:5432/warehouse

filament sink create warehouse \
  --sink-connector postgres \
  --sink-connection-method url \
  --sink-dsn-env

export POSTGRES_SINK_DSN=postgres://user:pass@localhost:5432/warehouse

filament sink create warehouse \
  --sink-connector postgres \
  --sink-connection-method url \
  --sink-dsn-env

Step 7. Create the pipeline and run it

filament pipeline create github-to-pg \
  --source gh \
  --sink warehouse \
  --resources repositories,issues \
  --sync-mode full \
  --write-mode

filament pipeline create github-to-pg \
  --source gh \
  --sink warehouse \
  --resources repositories,issues \
  --sync-mode full \
  --write-mode

filament pipeline create github-to-pg \
  --source gh \
  --sink warehouse \
  --resources repositories,issues \
  --sync-mode full \
  --write-mode

The first run creates one typed table per resource in a schema named after the source connection, loads rows through binary COPY, and upserts on id.

Step 8. Switch to incremental runs

filament pipeline edit github-to-pg --sync-mode

filament pipeline edit github-to-pg --sync-mode

filament pipeline edit github-to-pg --sync-mode

Issues now read from the saved updated_at watermark minus the 300 second overlap, and the upsert absorbs re-read rows. Put the run on a cron and the table stays current.

How to verify it worked

Count the rows and check the run history before you trust the pipeline. The schema is named after the source connection, gh here, and every row carries the run id and start time under the _filament_ prefix.

SELECT count(*) FROM gh.issues;
SELECT id, title, updated_at, raw->>'state' AS state
FROM gh.issues ORDER BY updated_at DESC LIMIT 5

SELECT count(*) FROM gh.issues;
SELECT id, title, updated_at, raw->>'state' AS state
FROM gh.issues ORDER BY updated_at DESC LIMIT 5

SELECT count(*) FROM gh.issues;
SELECT id, title, updated_at, raw->>'state' AS state
FROM gh.issues ORDER BY updated_at DESC LIMIT 5

filament run ls

filament run ls

filament run ls

The second query proves the typed columns and the jsonb remainder both landed. An incremental run should move far fewer rows than the first full run.

What to do when it breaks

Most failures trace to the manifest, the token, or the API's own limits, and each has a specific fix.

• Startup fails naming a manifest path. The catalog rejects unknown YAML fields and missing registry entries, so fix the reported file and restart.

• A 4xx stops the run. Those are fatal unless they match an empty rule, so check the token's scopes first and add a response.empty rule only after confirming the API returns that status for an empty collection.

• The run fails on a missing cursor. That is deliberate, so pick a cursor the API always returns rather than making the field nullable.

• Duplicates appear after a resume. Child resources share one watermark across all parents, so raise overlap_seconds and keep the upsert write mode.

• The run crawls. Keep requests_per_second under the API's budget, let the dynamic block read the reset header, and narrow --resources to what you query.

Which option fits which job

Pick by the constraint that breaks first.

For the main reader, an engineer who wants a repeatable sync into a Postgres they run, the manifest path wins. The API description lives in one reviewable file and the runtime owns cursors, retries, typing, and verification. Fivetran is the alternative when nobody will operate software, and dlt when the team already thinks in Python.

Related guides

• Best Way to Sync GitHub to Postgres (2026) applies the pattern to the full GitHub connector.

• Best Way to Sync Stripe to Postgres (2026) covers object id cursors and lookbacks.

• Best Way to Sync Notion to Postgres (2026) shows a manifest against a body cursor API.

• Full Load vs Incremental vs CDC, which to use explains when a cursor is enough.

• What Is Change Data Capture (CDC)? The Complete Guide covers the delete problem a cursor cannot solve.

• Introducing Filament is the announcement with deployment options and the connector list.

Frequently asked questions

How do I get data from a REST API into Postgres?

Find the pagination style, auth method, and incremental cursor the API exposes, declare them in a manifest or write them in code, and land each record as a typed row upserted on its primary key. A declarative tool handles retries, checkpoints, and typing so a scheduled run keeps the table current.

What is the difference between REST API to Postgres and Postgres to REST API?

They are opposite jobs. Loading API data into Postgres pulls JSON from a third-party API and writes rows into your database. Exposing Postgres as a REST API, which is what PostgREST does, puts an HTTP layer in front of tables you already own. This guide covers the first job only.

What is a YAML manifest for an API connector?

A manifest is a declarative file that describes an API's base URL, auth, pagination, rate limits, endpoints, field types, and incremental cursors. A generic HTTP engine reads the file and runs the sync, so adding an API means writing YAML instead of a new codebase. Airbyte, Sling, and Filament all use the pattern.

How do I handle API pagination when syncing to a database?

Match the style the API uses. GitHub returns a Link header with rel next, Stripe returns a has_more flag and takes the last object id as starting_after, and many APIs use page numbers or offsets. The connector must follow pages to the terminator and should checkpoint the cursor so an interrupted run resumes mid-listing.

Can I sync a REST API incrementally instead of reloading everything?

Yes, when the API filters by a watermark such as updated_at or since. Store the largest cursor value you saw, send it as the lower bound next run, and rewind it by a lookback window to catch late edits. Hard deletes stay invisible to a cursor, so plan a periodic full run or use an events feed.

Should I store API JSON as jsonb or as typed columns in Postgres?

Both. Project the fields you filter and join on into typed columns and keep the rest of the payload in a jsonb remainder column. Postgres stores jsonb in a decomposed binary form that is faster to query and indexable, so you keep the full record while gaining real types for the hot columns.

How does Filament sync a REST API to Postgres?

You write a YAML manifest declaring the API's auth, pagination, resources, field types, and incremental cursor, load it with HTTP_MANIFESTS_DIR, and create a pipeline from that source to a Postgres sink. Each run writes typed tables with upserts, checkpoints progress per page, and verifies every batch with a checksum.

Which tool is best for syncing any REST API to Postgres?

For a self-hosted sync with incremental cursors, typed upserts, and no per-row bill, Filament's manifest path covers the most ground, with dlt the strongest choice when the team lives in Python. Airbyte fits teams that want a visual builder, Fivetran fits teams that will not run software, and Hevo fits small full reloads.