Connect SPSync to SQLite

SQLite is a single file on the machine that runs SPSync. There is nothing to install on a server, and you manage no users and no firewall. It suits an evaluation or a small, local dataset that a person queries from the same machine.

Before you start

• A folder on the machine that runs SPSync, on a local disk. A network share is a poor home for a SQLite file because file locking over a network is unreliable.
• A SQLite browser or the sqlite3 command if you want to look inside.

Create the database and a least-privilege user

There is nothing to create. SPSync makes the file the first time it runs. SQLite has no users, so the permission that matters is the operating system’s: the account that runs SPSync must be able to create and write files in the folder. Give the folder to that account and nobody else. A second reader can open the file at the same time, because SPSync turns on write-ahead logging (WAL) on every connection, which stops a reader blocking a writer.

Allow SPSync to reach it

Not applicable. The file is local, so no network port is involved. If another machine needs the data, copy the file, or choose a server destination such as PostgreSQL. The Azure managed application cannot write to a file on your computer.

Encrypt the connection

There is no connection to encrypt. The file itself is not encrypted by SPSync. Use full-disk encryption on the machine, such as FileVault or BitLocker, if the data needs protecting at rest.

Authentication SPSync supports today

None, and none is needed. Access is whatever the operating system allows for the file.

Connect from SPSync

The “connection string” is a file path:

/Users/you/spsync/replica.db

A sqlite:/// prefix in front of the path is accepted as well.

• Desktop app: open the Destination screen, choose SQLite (local file), type the path into File path and choose Test connection.

• CLI: the config names an environment variable that holds the path:

  export SPSYNC_SQLITE_PATH=/Users/you/spsync/replica.db
  spsync export-config --output replication.yaml \
    --site-url https://contoso.sharepoint.com/sites/x --list Projects \
    --pipeline-name spsync_dev --prefix dev \
    --destination-kind sqlite --connection-string-env SPSYNC_SQLITE_PATH --dataset-name main \
    --client-id <entra-app-client-id>

  main is SQLite’s own default schema. Any other --dataset-name is attached as a second file next to the first.

• AI assistant: with the admin MCP server connected, the path is not a secret, so the assistant can pass it as the connection_string field when it creates the destination.

What Test connection and doctor check

Both open the file and run SELECT 1. A pass means the path is writable or can be created and the file is a valid SQLite database. spsync doctor reports it as destination-connectivity.

Things to know about the data

SQLite records a column’s declared type but does not enforce it.

• Dates are text with a space, not a T. A datetime is stored like 2026-01-01 00:00:00.000000, with no Z or offset. A comparison against '2026-01-01T00:00:00Z' will not match. Use SQLite’s datetime() and strftime() functions, or write the literal in the stored shape. Every value is still UTC.
• Currency can lose exactness. A currency column is declared NUMERIC(38, 9), but SQLite stores it as a floating-point number when it can. Round, or compare with a tolerance.

Common errors

What you see	What it means and the fix
unable to open database file	The folder does not exist, or the account running SPSync cannot write to it.
database is locked	Another program has the file open with a write lock. Close it and run again.
file is not a database	The path points at a file that is not SQLite, for example a DuckDB file.