Your first SharePoint replication, start to finish

22 October 2026

Tags: Relational databasesDesktopCommand line

You have SPSync installed, either the desktop app or the command line, and there is one list you care about. Maybe it is an Equipment Loans list that the facilities team keeps on their site, with a lookup to a Locations list, a Borrower person column and a multi-choice Accessories column. Somebody wants it in a report, or in a script, or in front of an agent, and the list itself is starting to feel slow in the browser. This post walks through the first replication of that list in the order you will do it, with what you see at each step.

The steps are the same in the desktop app and on the command line, because both run the same engine. Where the two differ, both are shown. The full reference is the setup guide, and how it works has the same flow with screenshots. If a colleague called this job mirroring or syncing the list, what each of those words means for a SharePoint list explains why we call it replication.

Connecting SPSync to SharePoint with Sites.Selected

SPSync reads SharePoint through Microsoft Graph, using one multi-tenant app registration that you consent to. There are two ways to give it access, and the choice matters more than anything else you set on day one.

Delegated sign-in runs as you. You consent to Sites.Read.All, which in delegated mode means the sites and lists you can already open, plus your own profile and a refresh token. Any user can grant that to themselves unless the tenant requires admin consent for every app. It is the quickest way to a first run, and its limit is that it stops when you do: a delegated replication keeps going only while your refresh token is alive, which is about 90 days of being idle.

Sites.Selected is the application permission. A tenant admin grants the SPSync app one named site, and the replication then runs unattended, on a shorter interval, whether or not anyone is signed in. Security teams tend to push back on tenant-wide read access, and this grant is the narrow alternative they can check. If your IT team has told you “Sites.Selected only”, this is the grant they mean, and the admin permissions page explains it in terms they will recognise. For the tightest grant of all there is Lists.SelectedOperations.Selected, which names single lists, at the cost of breaking that list’s permission inheritance from the site.

In the desktop app, the permissions wizard on the Connect screen handles this. You sign in, it checks what is already in place, and then offers three ways to finish: grant it yourself, send a short pack to your tenant admin, or continue delegated for now. The admin pack is the useful one if you are not an admin. It is a Markdown or PDF document with your tenant name, the app’s client id, the sites and lists you picked, and the grant commands filled in with your real ids, so the admin has nothing to look up.

On the command line, sign in with a device code:

spsync auth login --client-id <client-id>

The CLI prints a code and a URL. Open the URL in any browser, on any device, and enter the code. If you use your own app registration because policy requires it, add --tenant <your-tenant-id> and the same scopes apply.

Picking the site and the list to replicate

With access in place, find the site. The desktop app has a Search sites box. On the command line, spsync discover sites --client-id <client-id> lists every site the signed-in identity can see, and --search narrows it.

An identity that only holds Sites.Selected cannot enumerate sites at all, which is the point of the grant. In that case discover sites says so and asks for the site’s address, so give it the full URL from your browser:

spsync discover sites --site-url https://contoso.sharepoint.com/sites/facilities --client-id <client-id>
spsync discover lists <site-id> --client-id <client-id>

discover lists shows every list and library on the site before you commit to anything. The desktop app shows the same thing as soon as you pick the site, with each list’s columns and item counts.

Tick the list you came for, and tick the lists its lookup columns point to as well. Our Equipment Loans list has a Location lookup, so the Locations list goes in too. Leave it out and the lookup still arrives as an id and a display value, but there is no Locations table to join that id to.

Choosing SQLite or Postgres as the destination

For a first replication, these two cover most people. SQLite is a single file on your own disk, with no server to run and no credentials of its own. You open it with DB Browser for SQLite, DuckDB, pandas or any SQLite driver. It is the fastest way to see the schema SPSync builds, and it is the right home for data that one person reads on one machine.

Postgres is the choice once anyone else needs the data. A Power BI report, an internal app or an agent needs a server to connect to, and Postgres gives you database roles, so you decide who reads which table. Self-hosted Postgres works, and so do the hosted ones, including Azure Database for PostgreSQL, Supabase and Neon, which all want sslmode=require on the connection string.

The tables come out with the same names and columns in both. Two SQLite habits are worth knowing before you write queries against the file. Dates are stored as text with a space between date and time and no time zone suffix, so compare them with SQLite’s datetime() function. Currency columns are stored as floating point, so round in money reports.

On the command line, the connection string always lives in an environment variable and the config file only names the variable. For Postgres that looks like this:

export SPSYNC_DEST_DSN=postgres://user:pass@host:5432/spsync

spsync export-config \
  --output replication.yaml \
  --site-url https://contoso.sharepoint.com/sites/facilities \
  --list "Equipment Loans" --list Locations \
  --pipeline-name facilities \
  --prefix ops \
  --destination-kind postgres \
  --connection-string-env SPSYNC_DEST_DSN \
  --dataset-name spsync \
  --client-id <client-id>

For SQLite, the variable holds a file path, --destination-kind sqlite selects it and --dataset-name main uses SQLite’s default schema. In the desktop app, pick the database on the Destination step and press Test connection. The same step has a Create reporting views toggle, which adds a v_ops_equipment_loans view with person columns already resolved to names. Turn it on if a BI tool will read the data.

If the list has a date-only column, set the site’s time zone now with --site-time-zone Europe/London (any IANA name). SPSync needs it to put a date-only value on the right day, and the run reports a warning until it is set.

Before the first run, spsync doctor replication.yaml checks your sign-in, the site grant, the sync interval in effect and that the database answers. It is a quick way to know the run will start cleanly.

Running the first full load

Press Run now in the desktop app, or run spsync run replication.yaml on the command line. This first run is the full load. SPSync creates the tables, reads every item and writes it. There is nothing to create in the database by hand, though the database login needs the right to create tables in that schema.

How long it takes depends on the list. On a laptop against a live SharePoint Online tenant, we loaded three lists totalling 280 rows into Postgres in 11 seconds. A bigger run, four lists with 10,303 items between them, one of them holding 10,000 items, went to SQL Server from the desktop app in 1m 52s and used 115 Graph calls. The 5,000-item view threshold does not come into it, because SPSync reads the list with delta query and paging, and it honours SharePoint’s Retry-After header if the tenant asks it to slow down.

On the command line, each list gets a line when the run finishes, in the form Equipment Loans: read <n>, inserted <n>, updated <n>, soft-deleted <n>. The desktop app’s Runs screen shows Items read and Rows written for the run, with the same per-list breakdown. If a column could not be replicated, the run names it. Managed metadata is the column type you are most likely to see named, because it is not replicated yet.

What does the second run read?

Only what changed. After the full load, SPSync keeps a delta token for each list, and the next run asks Graph for the items added, edited or deleted since then. On those same three lists, a second run with nothing changed in SharePoint took 6 seconds.

Edits overwrite the row. A deleted item keeps its row, with sp_deleted_at set to when the deletion was seen, so you still have the record and can filter it out with WHERE sp_deleted_at IS NULL. Hard deletes are available as a deletes: hard setting in the command-line config if you would rather the row went. New columns appear on the next run, with existing rows set to null. Removed columns stay in the database, and a column whose type changes gets a _v2 column beside the old one.

The desktop app runs every five minutes by default. On the command line, spsync run does one round and exits, so cron, Task Scheduler, launchd or a CI pipeline decides how often it runs. CLI automation has a recipe for each. With Sites.Selected the interval can come down to one minute, against five minutes for delegated sign-in.

Where the SharePoint list tables and child tables appear

Every table takes the prefix you set followed by the list’s internal name, lowercased, with _ between the words. With the prefix ops, Equipment Loans becomes ops_equipment_loans and Locations becomes ops_locations. In Postgres they sit in the schema named by --dataset-name, so spsync.ops_equipment_loans. Renaming a column in SharePoint does not rename it in the database, because SPSync uses the internal name.

Each row carries the SharePoint item id as sp_item_id, which is the primary key, plus sp_created, sp_modified, sp_author_id, sp_editor_id and sp_synced_at. The Location lookup becomes two columns, location_id and location_value. The Borrower person column becomes borrower_user_id, which points at one shared sp_users table that every list and site in the destination uses.

The multi-choice Accessories column gets a child table of its own, ops_equipment_loans__accessories, with one row per value and an ordinal, keyed on sp_item_id. A loan with a charger and a case has two rows there. That is what lets a report count loans by accessory without splitting a delimited string. The schema reference has every column type and its mapping.

Here is the list joined to its people and its locations in plain SQL:

SELECT l.title, loc.title AS location, u.display_name AS borrower
FROM ops_equipment_loans l
LEFT JOIN ops_locations loc ON loc.sp_item_id = l.location_id
LEFT JOIN sp_users u ON u.id = l.borrower_user_id
WHERE l.sp_deleted_at IS NULL;

How do you check row counts against SharePoint?

Start with the item count SharePoint shows for the list on the site’s Site contents page. Then count the live rows in the list table:

SELECT COUNT(*) FROM ops_equipment_loans WHERE sp_deleted_at IS NULL;

As of the last run the two should agree, because each SharePoint item is one row in the list table. On the first run, the read figure on the list’s line is the same count seen from SPSync’s side.

Do not compare the list’s item count with Rows written. Rows written counts every row written to every table, child tables and sp_users included, so it is higher. In the 10,303-item run above it was 20,606 rows. The figure to compare with SharePoint is always the list table’s own count of live rows.

What reconcile is for

Reconcile is a full re-check of the replica against SharePoint. It reads every item again, compares, and corrects the table where the two differ. Treat it as your QA check, the thing you run before you hand the table to the finance team and say it matches the list. It is also how you catch back up after an outage on your side, such as a database server that was down for a weekend, or a Microsoft 365 incident that kept SharePoint unreachable.

It runs on its own schedule, weekly by default, and the interval is configurable per replication with reconcile_interval_days. From the command line you can run one whenever you like:

spsync reconcile replication.yaml

In the desktop app, reconcile runs on that schedule by itself. Running one on demand is a command-line action today.

Where the replica goes from here

Once the table is there, the next step depends on who reads it. A report connects with the database’s own connector. An internal app reads Postgres directly. For an agent, the agent data layer use case shows how to point a Postgres MCP server at the replica with a read-only database role. SPSync does not copy SharePoint item permissions, so the role you grant is what limits what the agent can see.

SPSync reads from SharePoint and never writes back to it. If the request you have is really “edit in Postgres and have it appear in the list”, the two-way sync use case explains what works today and what is designed. For a shorter walkthrough of the same steps against Postgres, see sync a SharePoint list to Postgres, and for the details of each database, the SQLite and PostgreSQL pages.

SQLite and Postgres are two of many destinations. SQL Server, MySQL, warehouses, lakes and search indexes are among the others, and the same list can go to several of them, one replication each. Once your first table looks right, let us know which database your team uses day to day, especially if it is not one we cover yet.