Connect REST API to PostgreSQL
Point at any JSON REST API, handle pagination and auth, pull the records out and land them in your destination. The escape hatch for any tool without a dedicated connector.
What you can pull from REST API
A REST API job pulls records into a flat table on your schedule. Nested fields are flattened into ordinary columns, so what lands in PostgreSQL is ready to sort, filter and total rather than needing another cleanup step.
- Any JSON endpoint
- Page / offset / cursor paging
- Bearer, header or query auth
Options you can set
- url
- The full request URL
- method
- GET or POST — defaults to
GET - records_path
- Dot path to the array holding your rows, e.g. data.items
- pagination
- none, page, offset or cursor — defaults to
none - page_size
- Records per page, up to 1,000 — defaults to
100 - max_pages
- Safety cap on the paging loop, up to 1,000 — defaults to
50 - timeout
- Per-request timeout in seconds, up to 300 — defaults to
30
How it lands in PostgreSQL
PostgreSQL accepts append, replace, upsert writes. Upsert is the one that matters on a schedule: it merges on a key you choose, so matching rows are updated in place and only genuinely new records are created. Append instead, and a daily job multiplies your data.
Upsert uses key_columns as the conflict target.
- Rows are written in chunks of 1,000 by default, configurable up to 50,000.
- Replace truncates the target table before writing.
Authentication
REST API authenticates with an API key held as a stored connection, so it never appears in a spreadsheet cell, a formula, or a shared copy of a sheet.
Keeping runs incremental
Yes. Most APIs accept an updated-since query parameter; set it in query_params and the run fetches only what changed.
Pair a narrowed read with an upsert write and a re-run costs almost nothing: the rows it already knows are updated, and nothing is duplicated. That combination is what makes a frequent schedule affordable.
Limits and pacing
These are the provider-side ceilings that shape a schedule, not ours. Knowing them up front is the difference between a job that runs quietly every morning and one that starts failing the week your data grows.
| Limit | Value | Applies to |
|---|---|---|
| Page size | up to 1,000 records | REST API |
| Pages per run Defaults to 50. | up to 1,000 | REST API |
Common gotchas
Most of what goes wrong with a scheduled sync is not a bug — it is a detail of how one side behaves that nobody wrote down. These are the ones that come up for this pair.
Cursor pagination reads the cursor from the response body
The next cursor is taken from a dot path inside the JSON body, and may be either an opaque token or a full next-page URL. APIs that return their cursor in a Link response header instead - Shopify’s REST Admin API and the GitHub API among them - cannot be followed past page one. Page and offset pagination are unaffected.
The URL is re-validated on every page
With cursor pagination the next URL comes from the response, which makes it attacker-influenced. It is checked again before each request rather than trusted because the first one passed.
Set either table or query, never both
The config rejects both being set, and rejects neither - caught when the job is saved rather than on its first run.
Schema drift is silent until it is not
APIs add fields. In append mode a new field simply is not written; in replace mode the table is rebuilt around whatever shape arrived. Pin the columns you care about rather than accepting whatever the API returns this week.
Set it up in four steps
- 1
Connect REST API
Optional. A stored credential is sent as a bearer token, a named header, or a query parameter - never pasted into a spreadsheet cell.
- 2
Connect PostgreSQL
Host, port, database, user and password, stored encrypted.
- 3
Shape the data
Select the columns you want, filter rows, cast types and add computed fields. Everything else is dropped before it reaches the destination.
- 4
Schedule it
Run once, or on a cron. Every run refreshes PostgreSQL with the latest REST API data.
Do I need an add-on or extension for this?
No. The job runs server-side and writes into PostgreSQL through its API, so there is nothing installed in the destination itself. It keeps running when nobody has the file open, and a copy of the file does not need anything installed to work.
How often does the data refresh?
On whatever schedule you set with a cron expression — hourly, daily, or a specific time on specific days. Each run pulls the latest from REST API.
Do I need to write any code?
No. You connect both sides, map the fields in a wizard and set a schedule. Computed fields accept small expressions — abs, round, min, max, len — but there is nothing to host or maintain.
What if my API is not in the connector list?
That is what this connector is for. If it speaks JSON over HTTP, point the REST source at it, set the path to the array holding your rows, and pick a pagination style.
Where does my API key live?
In a stored connection, sent as a bearer token, a named header or a query parameter at request time. It is never written into a spreadsheet cell or a formula, which is the usual failure mode of script-based approaches.
Can I run my own SQL rather than reading a table?
Yes. Set a query instead of a table and aggregate in the database, which is usually far cheaper than moving raw rows and aggregating downstream. One statement only - multi-statement queries are rejected.
Does it need a superuser?
No. A user with SELECT on what you are reading is enough for a source; a destination needs INSERT and, for upsert, UPDATE on the target table.
Is the REST API connector free to use?
You can connect REST API and start syncing on the free plan.
Other sources into PostgreSQL
Related reading
Airtable upsert: stop creating duplicate records
Appending on every run turns a tidy base into six copies of every row. Here is how upsert works in Airtable, and how to choose a merge key that holds up.
Get data into Google Sheets without an add-on
Every major Sheets data tool is an add-on you install, authorise and keep updated inside the spreadsheet. Here is how to schedule the same imports without one.
IMPORTJSON in Google Sheets: what to use instead
IMPORTJSON is not a Google Sheets function. It is a community script people paste from unmaintained gists. Here is why it breaks, and the routes that do not.