Shopify to Postgres: how to replicate store data your app can actually query

How do you connect Shopify to Postgres?

You connect Shopify to Postgres by reading store objects through the Admin API and writing them into Postgres tables, because Shopify has no native database export. Something sits in the middle: a managed connector, or a scheduled job that authenticates with a custom app access token, pages through orders and their related objects, and upserts each batch into your schema.

The connection has three parts. First, a custom app in the Shopify admin with the read scopes you actually need (read_orders, read_products, read_customers, read_fulfillments), which issues an access token you send on every request. Note that reading orders older than sixty days requires the additional protected scope Shopify grants on request, and a backfill will silently come back short without it. Second, a Postgres landing spot: a database, a schema, and a role that can create tables and run upserts. Third, the transfer loop itself. The managed path is the Shopify to Postgres integration, which owns the cursor paging, the watermark, the staging tables, the upserts, and the backoff.

Extraction method	What it is	When it fits and what it costs you
REST Admin API polling	Paged JSON reads filtered on updated_at_min, walked with the page_info cursor in the Link header	Simple steady-state incremental sync. Slow for large backfills, and every request draws down the rate limit bucket
GraphQL Admin API (bulk operations)	An asynchronous bulk query that Shopify runs server side and hands back as a JSONL file you download once	The right tool for the first full backfill and for wide nested reads (orders with line items in one pass). More moving parts: submit, poll for completion, then download
Webhooks	Shopify POSTs a payload to your endpoint on events like orders/updated, orders/cancelled, refunds/create	Near real time latency. Deliveries can be missed, retried, or arrive out of order, so treat them as a hint to refresh, not as the source of truth
Managed sync tool	A hosted connector that handles auth, cursors, throttling, schema changes, and idempotent loads	When you would rather not own the retry and backfill logic. Less control over exact field selection

Most production setups combine two of these: a bulk operation for the initial load, then REST or GraphQL polling on the watermark, with webhooks layered on top when a feature needs sub-minute freshness. If your reporting lives in a warehouse rather than an application database, the sibling path is Shopify to BigQuery, and the tradeoffs are covered in the Shopify to BigQuery guide.

Does Shopify have a database you can query?

No. Shopify does not expose a SQL database, a read replica, or direct database credentials. The only programmatic access is the Admin API (REST or GraphQL), plus CSV exports from the admin UI and ShopifyQL inside the analytics product. If you want to run arbitrary SQL against your store data, you have to replicate it into a database you control.

That gap is the whole reason this pipeline exists. Calling the Admin API live from a dashboard is slow and fragile: results are paged, requests are rate limited, and a report that joins orders to customers to line items turns into hundreds of round trips. Worse, you cannot join API responses to your own tables. Once orders sit in Postgres, attributing revenue to your internal subscription plans, or reconciling Shopify fulfillments against your own inventory ledger, is one join instead of an afternoon of pagination code. ShopifyQL answers merchandising questions well, but it stops at the store boundary, and anything mixing store data with your product or billing systems needs one place to live, which is what a data integration platform is for.

How Shopify objects map to Postgres tables and types

Model orders and line items as separate tables. Flattening line items into the order row (as a JSON blob or as item_1_sku, item_2_sku columns) destroys SKU-level analysis: you cannot group revenue by product, join to your inventory table, or answer which variant drives refunds without unnesting on every query. A child table keyed to the order id keeps all of that as ordinary SQL. Plan on tables for orders, order line items, customers, products, product variants, refunds, transactions, and fulfillments.

Shopify object and field	Postgres table.column	Type and notes
Order id	orders.id	BIGINT primary key. The upsert conflict target
Order name / order_number	orders.name / orders.order_number	TEXT / INTEGER. name is the merchant-facing string such as #1042
Order total_price	orders.total_price	NUMERIC(18,4). Arrives as a decimal string. Never cast to float
Order subtotal_price, total_tax	orders.subtotal_price, orders.total_tax	NUMERIC(18,4). Same string-to-numeric cast
Order financial_status	orders.financial_status	TEXT. paid, pending, partially_refunded, refunded, voided
Order fulfillment_status	orders.fulfillment_status	TEXT, nullable. NULL means unfulfilled, which is not the same as an empty string
Order currency / presentment_currency	orders.shop_currency / orders.presentment_currency	TEXT (3-char code). Keep both. Do not blend amounts across them
Order created_at, updated_at	orders.created_at, orders.updated_at	TIMESTAMPTZ in UTC. updated_at is the incremental watermark. Index it
Order cancelled_at	orders.cancelled_at	TIMESTAMPTZ, nullable. Cancelled orders still return from the API
Customer id, email	customers.id, customers.email; orders.customer_id	BIGINT PK and TEXT. Order carries the FK. Guest checkouts have no customer id
Line item id, order_id	order_line_items.id, order_line_items.order_id	BIGINT PK and BIGINT FK to orders.id. Index the FK
Line item sku, quantity, price	order_line_items.sku, .quantity, .price	TEXT, INTEGER, NUMERIC(18,4). SKU can be null on custom items
Line item variant_id, product_id	order_line_items.variant_id, .product_id	BIGINT, nullable. Products deleted after the sale leave these dangling
Refund id, order_id, refund amount	refunds.id, refunds.order_id, refunds.amount	BIGINT, BIGINT FK, NUMERIC(18,4). Sum transactions on the refund for the true amount
Fulfillment id, status, tracking_number	fulfillments.id, .status, .tracking_number	BIGINT PK, TEXT, TEXT. One order can have many fulfillments

The money trap deserves its own paragraph, because it is the most common source of numbers that are almost right. Shopify returns monetary values as decimal strings such as "129.95", not as numbers. If your loader parses them as floats (which many JSON libraries do by default), you inherit binary floating point rounding, and summing tens of thousands of order totals drifts by cents that finance will eventually notice. Cast the string straight to NUMERIC and let Postgres hold exact decimals.

Multi-currency stores add a second trap. When a store sells in multiple currencies, Shopify returns money in two forms: the shop currency (the store's own settlement currency) and the presentment currency (what the buyer actually saw and paid). The GraphQL money bag fields expose both explicitly. If you store one column and populate it from whichever field happened to be present, you end up summing euros and dollars into a single total that looks plausible and is wrong. Store both amounts in separate columns alongside both currency codes, and make every revenue query state which basis it uses. When the same store data also needs to reach accounting, the Shopify to QuickBooks path has the same currency discipline baked in, for the same reason.

How do you handle the Shopify API rate limit?

Handle it by reading the throttle status Shopify returns with every response and pacing requests to stay under the refill rate, rather than sending as fast as you can and reacting to failures. When a 429 does come back, honor the Retry-After header instead of retrying immediately, and resume from the same cursor so no page is skipped or read twice.

The REST Admin API uses a leaky bucket. Each app and shop pair gets a bucket that refills at a steady rate; a burst of calls drains it, and once it is empty further requests return HTTP 429 until the bucket refills. Responses carry a header showing your current usage against the bucket capacity, which is the signal you should pace on. The GraphQL Admin API works differently: each query is assigned a calculated cost in points based on the number of fields and connections it requests, and those points draw down a points bucket that also refills at a steady rate. GraphQL responses include a throttle status object reporting the maximum bucket size, the currently available points, and the restore rate. Read it, and if available points are running low, sleep before the next call.

Two practical consequences. First, on GraphQL, asking for fewer fields and smaller page sizes genuinely costs less, so a narrow query paged at a moderate size often moves more rows per minute than a greedy one that keeps getting throttled. Second, pagination is cursor based on both APIs: REST returns a page_info cursor in the Link header and GraphQL returns endCursor with hasNextPage. Offset pagination is not available, so you cannot jump to page 40 or parallelize a scan by slicing offsets. If you want parallelism on a backfill, split by date range and walk each range's cursor independently, or skip the problem entirely by using a bulk operation, which runs server side and is not paged by you at all.

How do you keep a Shopify Postgres sync from creating duplicates?

Make the load idempotent. Give every table a primary key on the Shopify id, land each batch in a staging table, and upsert with INSERT ... ON CONFLICT (id) DO UPDATE SET .... Because the id is stable, replaying an overlapping window or retrying a failed batch updates the existing row in place. Then you can overlap watermark windows freely, which is what correctness requires.

The reason overlap is mandatory: Shopify orders mutate after creation. An order gets edited, partly refunded, cancelled, or fulfilled in two shipments, and each of those moves updated_at while created_at stays fixed. A one-time import keyed on creation date is wrong within a day. So the incremental query filters on updated_at_min, set to your stored watermark minus a small lookback (five to fifteen minutes is typical), and the loader upserts. Rows you have already seen simply get rewritten with identical values, which costs almost nothing.

Line items need the same discipline one level down. When an order is edited, its line item set can change, so a plain upsert on line item id leaves orphaned rows for items that were removed. The clean pattern is to delete all line items for the order ids in the current batch and reinsert them inside the same transaction as the order upsert, so an order and its children are never briefly inconsistent.

Deletes are the case a watermark can never catch. A hard-deleted order or customer stops appearing in updated_at queries entirely, so your replica quietly keeps rows the store no longer has. Two mitigations, and you want both: subscribe to the orders/delete, products/delete, and customers/delete webhooks, and run a periodic reconciliation that pulls the full id set for a trailing window and removes (or flags) local ids that Shopify no longer returns. Note that a cancelled order is not a deleted one: it still comes back from the API with cancelled_at set, so keep the row and use the timestamp.

Should you use Shopify webhooks or polling?

Use both. Polling on an updated_at watermark is your correctness guarantee, because it is replayable and self-healing. Webhooks are a latency optimization on top, useful when a feature needs an order in your database within seconds. Running webhooks alone is the common mistake, since a dropped or out-of-order delivery leaves a permanent gap nothing ever repairs.

Webhook delivery is best effort. Shopify retries failed deliveries for a period and then gives up, so an endpoint that is down during a deploy loses those events for good. Deliveries also arrive out of order, which means an older orders/updated payload can land after a newer one and overwrite fresh data with stale values. Guard against this by comparing the payload's updated_at to the row you already have and skipping the write if yours is newer, which in Postgres is a WHERE excluded.updated_at > orders.updated_at clause on the DO UPDATE. Always verify the HMAC signature on the request, and respond 200 quickly after queueing the payload, since slow endpoints get treated as failures.

The reconciliation poll is what makes the whole thing trustworthy. Run it hourly or nightly over a trailing window wider than any plausible webhook outage, and it repairs anything the event stream missed. You also want to know when the replica has stopped moving at all, which is where tracking freshness and row volume against expected baselines earns its keep: a sync that fails loudly gets fixed in minutes, while one that silently stops feeding a revenue dashboard gets discovered by a finance team asking why last week looks flat. The broader comparison is in the webhooks versus polling guide.

How often should you sync Shopify to your database?

For most stores, every fifteen to sixty minutes on the watermark is the right steady state, with a nightly wider reconciliation pass. Match the interval to the decision the data drives: operational dashboards and inventory checks want fifteen minutes or webhooks, while finance and cohort reporting are fine hourly or daily. Syncing more often than anyone reads costs rate limit budget for nothing.

Backfilling a large store is a different job from the steady state, and should be built as one. Submit a GraphQL bulk operation per object, or chunk a REST backfill by created-date range so each chunk is a bounded cursor walk you can retry independently. Record which chunks completed, so a failure at 80 percent resumes rather than restarts. Load into staging tables first, then upsert. Only after the backfill lands should you set the watermark and start incremental runs, and set it to the start time of the backfill rather than its end, so anything that changed while the backfill was running gets picked up on the first incremental pass.

On the steady-state side, keep a loaded_at column on every table so freshness checks are a one-line query, and index updated_at and the foreign keys your joins use. Metered per-row pipeline pricing interacts badly with sync frequency and with Black Friday, since both push row counts up in the month you least want a surprise invoice. Our flat pricing is by plan rather than per row, so peak season does not change the bill. If you want to see the pair running against your own store before you wire anything up, book a walkthrough or map it yourself with the Shopify to Postgres connector.