SharePoint to SQLite (local file, desktop analytics)

Last updated 19 October 2026

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

Status: Shipped · product: Included in every product

The simplest place to put a SharePoint list is a file. SQLite runs without a server and needs neither a connection string nor credentials of its own: SPSync writes one file at a path you choose, and you open it with DB Browser for SQLite, DuckDB, pandas or any language’s SQLite driver.

What the file holds

A course booking list with a Course lookup, an Attendee person column, a Booked on date and a multi-choice Dietary needs column becomes a train_bookings table keyed on the SharePoint item ID. course_id and course_value carry the lookup. attendee_user_id lines up with the shared sp_users table. train_bookings__dietary_needs has one row per booking and need, so a query joins it with plain SQL and never splits a delimited string.

Deleted items keep their row with sp_deleted_at set. New SharePoint columns appear on the next run, removed ones stay in the file, and a retyped column keeps its history while a _v2 column takes the new type.

Three SQLite habits to know

SQLite declares types without enforcing them, and two of SPSync’s choices follow from that.

• Dates are text with a space separator. A datetime is stored as 2026-01-01 00:00:00.000000 in UTC, with no T and no time zone suffix. Compare with SQLite’s own datetime() and strftime() functions, or with a literal in the same shape, since '2026-01-01T00:00:00Z' will not match.
• Currency is not exact. Currency columns are declared NUMERIC(38, 9), but SQLite stores most values as floating point. Round, or compare with a tolerance, in money-sensitive reports.
• WAL mode is on. SPSync switches the file to WAL journaling, so readers keep reading while a run writes. Keep SPSync as the only writer.

Ways people use the file

Behind a small tool or script. A notebook or local utility that called Graph for reference data reads the file instead, with no network round trip; see a read replica for internal apps.

Desktop analysis. An analyst gets a relational schema in DB Browser for SQLite, R or Python, and stops exporting CSVs by hand; see reporting and analytics on SharePoint list data.

A queryable backup. Because deleted items stay on record, a copy of the file is a point-in-time backup of the list; see continuous backup and history of lists.

Evaluation. SQLite needs no decisions up front, so it shows the schema SPSync generates before you choose Postgres, SQL Server or a warehouse.

Setting it up

1. Sign in with delegated access. A scheduled sync on a server needs Sites.Selected app permissions, which an admin approves per site.
2. Choose a path for the file. SPSync creates it on the first run.
3. Choose the lists or a whole site. The first run creates one table per list, child tables for multi-value columns and sp_users.
4. Set how often it runs, every few minutes up to hourly.
5. Open the file in the tool you prefer.

The file needs to sit where its readers can open it, so most SQLite syncs run from the desktop app on the machine that uses the file, or from the command line as a build step that regenerates it.

Watch it run

Watch the full video

Chapters are in the video's description.

Watch the walkthrough: the Northwind Analytics demo lists in SharePoint, connecting to Microsoft 365 in the desktop app, picking lists, the run into a local SQLite file, and the replicated tables and rows.

spsync auth login --client-id 00000000-0000-0000-0000-000000000000
spsync export-config --output replication.yaml --pipeline-name sqlite --prefix sqlite --destination-kind sqlite --connection-string-env SPSYNC_DB_CONNECTION --dataset-name sqlite --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: sqlite
prefix: sqlite
auth:
  mode: delegated
  client_id: 00000000-0000-0000-0000-000000000000
  tenant_id: common
destination:
  kind: sqlite
  connection_string_env: SPSYNC_DB_CONNECTION
  dataset_name: sqlite
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