SharePoint to Heroku Postgres

Last updated 26 October 2026

One way: SharePoint stays the system of record, and SPSync keeps a read-only copy of your lists current in Heroku Postgres.

Status: Shipped · product: SPSync for Relational Databases

A Heroku app that needs data from a SharePoint list usually ends up calling Graph from a worker dyno and caching the result somewhere. SPSync replaces that worker: it keeps the list in your Heroku Postgres database as tables, and the app queries them with the ORM it already uses.

How do I connect SPSync to Heroku Postgres?

Heroku attaches DATABASE_URL to any app with a Postgres add-on. SPSync reads its connection string from an environment variable, so you can copy that value into the variable on the machine that runs the sync. Two points to know:

• The URL begins postgres://. SPSync accepts that scheme, so leave it unchanged.
• Heroku Postgres requires TLS. Add sslmode=require to the URL.

The sync runs outside Heroku, on the desktop app, SPSync in your Azure or a server with the command-line tool, and reaches the database over the internet like any external client. Heroku updates DATABASE_URL on every attached app when it rotates the credential, after a pg:credentials:rotate or some maintenance, but it cannot reach the copy on the sync host. Update that variable in the same change, or the next run fails to connect and the one after it catches up once you have.

Databases on Private and Shield plans sit inside a Private Space and are not open to the internet by default. Check Heroku’s options for reaching them from outside the space before you plan where the sync runs.

SPSync writes to Heroku through its standard PostgreSQL destination. The automated tests run against stock PostgreSQL; the Heroku specifics here come from Heroku’s Dev Center, so run a first load against a fork or a staging database if the production database is small.

Plan limits that matter for a sync

Heroku’s plans differ in more than storage, and two of the differences catch SharePoint replications.

• Tables. Essential plans cap a database at 4,000 tables. A list becomes one table, each multi-value column on it becomes another, and every library is a list too. A single site rarely gets close, but a replication of many sites into one database can.
• Connections. Essential-0 and Essential-1 allow 20 connections, and your dynos’ pools share them with the sync. Standard and Premium plans allow far more.
• Credentials. Only production plans (Standard, Premium, Private and Shield) can create extra credentials. On Essential there is one owner credential, so the sync, the app and anyone else who connects share it.
• Server-side pooling. Heroku’s PgBouncer pool, reached through DATABASE_CONNECTION_POOL_URL on port 5433, exists only on Standard and higher. The sync does not need it.

Postgres caps identifiers at 63 bytes on every plan, and SPSync shortens long SharePoint names to fit.

A credential for the sync

On Standard and higher plans, give the sync its own credential, so rotating it never touches the app’s, and the database logs show which writes came from SharePoint:

heroku pg:credentials:create DATABASE --name spsync -a your-app
heroku pg:credentials:url DATABASE --name spsync -a your-app

The second command prints a URL for that credential; add sslmode=require and store it on the sync host. A new credential starts with no rights on existing objects, so as the default credential grant it what the sync needs: the right to create schemas in the database, or ownership of two schemas you create for it. The sync writes tables to the schema named in its dataset_name and stages each run’s changes in a second schema with _staging on the end, so it needs both.

create schema club authorization spsync;
create schema club_staging authorization spsync;

On an Essential plan none of this applies. The owner credential is the only one, and the sync uses it with the app.

What the app sees

A member directory list on a site replicated with the prefix club, with a Branch lookup, a Membership type choice, a Joined date and a multi-choice Interests column, becomes four tables:

Every person column in every list points at that one sp_users table, so “created by” and “account manager” join the same way. New SharePoint columns appear on the next run, removed ones stay, and a retyped column gets a _v2 copy so existing queries still work. Items deleted in SharePoint keep their row with sp_deleted_at set, which suits a members’ app that must not lose history when someone tidies the list.

The members at each branch who listed cycling as an interest:

select b.title as branch, m.title as member, m.membership_type, m.joined
from club_members m
join club_branches b on b.sp_item_id = m.branch_id
join club_members__interests i on i.sp_item_id = m.sp_item_id
where i.value = 'Cycling'
  and m.sp_deleted_at is null
order by b.title, m.title;

Run it from heroku pg:psql, or save it as a Dataclip to share the result with someone who has no database access. In a Rails app, a model needs two lines to read the table: self.table_name = "club_members" and self.primary_key = "sp_item_id". Treat the model as read-only, since the next sync overwrites any change the app makes to a synced row.

Personal data and deleted members

A member directory is personal data, and the default of keeping a deleted item’s row with sp_deleted_at set means a member removed from the SharePoint list is still in the Heroku database. Two ways to handle that. Run the sync from the command-line tool with deletes: hard in the replication’s YAML, and a deleted item’s row is removed on the next run; the desktop app and SPSync in your Azure always keep the row. Or keep soft deletes for the audit trail and schedule your own purge of rows whose sp_deleted_at is older than your retention period. Either way, the app should filter on sp_deleted_at is null so a departed member never appears on a page.

Agents on Heroku Postgres

An agent can answer questions about the directory from the same tables. Run a Postgres MCP server, give it a credential that can only read, and point it at the database or a follower. SPSync’s own read-only spsync mcp serve works the same way. On Standard and higher, create that credential with heroku pg:credentials:create and grant it SELECT on the replicated tables; a follower keeps the agent’s queries off the leader your app writes to. On Essential the only credential is the owner, which can change anything, so an agent belongs on a production plan. Member permissions in SharePoint are not copied, so the credential’s grants are the whole boundary.

Why does the first run fail?

• An error that names pg_hba.conf and ends in SSL off. The URL lacks sslmode=require, and Heroku refuses unencrypted connections.
• too many connections for role. The plan’s connection limit is used up, usually by dyno pools on an Essential plan. Scale the pools down, or move to a plan with more connections, and the next run connects.
• password authentication failed after weeks of good runs. Heroku rotated the credential and updated the app’s config vars; copy the new URL to the sync host.
• permission denied for database on a new credential. It cannot create its schemas yet; grant them as above.

A fork of the database, available on Standard and higher, is a cheap place to try the first load of a large site. Point a test replication at the fork, check the tables and row counts, then point the real replication at the production database.

Search by meaning on Heroku

Heroku Postgres offers the pgvector extension, so the member directory can answer “who here is into long-distance walking” even when nobody typed those words. Run create extension vector as the owner credential, then use the pgvector destination with the same URL. The sync embeds the columns you pick, with a local model or an API key you supply, and stores one vector per member; edits to other columns do not trigger a new embedding.

Uses on Heroku

The common one is an app built on SharePoint data, where staff keep editing in SharePoint and customers see the app. Another is an application read replica for an existing Heroku service. The same tables can feed a Power BI report or Metabase, and analysts can join several lists in SQL.

Getting started

1. Sign in with delegated access, or have an admin grant Sites.Selected on the site so the sync can run unattended.
2. Copy DATABASE_URL, add sslmode=require, and store it in the environment variable the destination names.
3. Choose the lists or the whole site.
4. The first run creates one table per list, child tables for multi-value columns and sp_users, then loads every item.
5. Set a schedule. The default is every five minutes, with a reconcile, weekly by default, that compares each table with its list.

Heroku bills the add-on as usual. If the first run fails with a TLS error, the URL is missing sslmode=require; if it fails on authentication a week later, look for a credential rotation.

spsync auth login --client-id 00000000-0000-0000-0000-000000000000
spsync export-config --output replication.yaml --pipeline-name heroku_postgres --prefix heroku_postgres --destination-kind postgres --connection-string-env SPSYNC_DB_CONNECTION --dataset-name heroku_postgres --client-id 00000000-0000-0000-0000-000000000000 --site-url https://contoso.sharepoint.com/sites/example --all-lists
spsync doctor replication.yaml
spsync run replication.yaml

pipeline_name: heroku_postgres
prefix: heroku_postgres
auth:
  mode: delegated
  client_id: 00000000-0000-0000-0000-000000000000
  tenant_id: common
destination:
  kind: postgres
  connection_string_env: SPSYNC_DB_CONNECTION
  dataset_name: heroku_postgres
scopes:
  - site_url: https://contoso.sharepoint.com/sites/example
    mode: entire_site
reconcile_interval_days: 7
create_reporting_views: false

# every night at 02:00
0 2 * * * cd /opt/spsync && SPSYNC_DB_CONNECTION="$(cat /etc/spsync/db.env)" uv run spsync run replication.yaml >> /var/log/spsync/run.log 2>&1

# .github/workflows/spsync-run.yml
name: SPSync
on:
  schedule:
    - cron: "0 2 * * *"
  workflow_dispatch: {}
jobs:
  run:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: astral-sh/setup-uv@v3
      - run: uv sync
      - run: uv run spsync run replication.yaml
        env:
          SPSYNC_DB_CONNECTION: ${{ secrets.SPSYNC_DB_CONNECTION }}
          SPSYNC_LICENCE_KEY: ${{ secrets.SPSYNC_LICENCE_KEY }}

# azure-pipelines.yml
schedules:
  - cron: "0 2 * * *"
    branches: { include: [main] }
    always: true
steps:
  - script: uv sync
  - script: uv run spsync run replication.yaml
    env:
      SPSYNC_DB_CONNECTION: $(SPSYNC_DB_CONNECTION)
      SPSYNC_LICENCE_KEY: $(SPSYNC_LICENCE_KEY)

# Windows Task Scheduler action (see /docs/cli/automation/)
$env:SPSYNC_DB_CONNECTION = (Get-Content C:\spsync\db.env)
uv run spsync run C:\spsync\replication.yaml
schtasks /Create /SC DAILY /ST 02:00 /TN "SPSync" /TR "powershell -File C:\spsync\run.ps1"

# ~/Library/LaunchAgents/io.spsync.run.plist, then: launchctl load ~/Library/LaunchAgents/io.spsync.run.plist