Skip to content

CLIENT.md

Latest commit

 

History

History
234 lines (166 loc) · 6.75 KB

CLIENT.md

File metadata and controls

234 lines (166 loc) · 6.75 KB

SQLite Sync

SQLite Sync is a multi-platform extension that brings a true local-first experience to your applications with minimal effort. It extends standard SQLite tables with built-in support for offline work and automatic synchronization, allowing multiple devices to operate independently — even without a network connection — while seamlessly staying in sync.

With SQLite Sync, developers can build distributed, collaborative applications while continuing to rely on the simplicity, reliability, and performance of SQLite.

Under the hood, SQLite Sync uses advanced CRDT (Conflict-free Replicated Data Type) algorithms and data structures designed specifically for collaborative, distributed systems:

  • Devices can update data independently, even without a network connection.
  • When they reconnect, all changes are merged automatically and without conflicts.
  • No data loss. No overwrites. No manual conflict resolution.

IMPORTANT

Conversion Between SQLite and PostgreSQL Tables

In this version, make sure to manually create the same tables in the PostgreSQL database as used in the SQLite client.

This guide shows how to manually convert a SQLite table definition to PostgreSQL so CloudSync can sync between a PostgreSQL server and SQLite clients.

1) Primary Keys

  • Use TEXT primary keys in SQLite.
  • PostgreSQL primary keys can be TEXT or UUID. If the PK type isn't explicitly mapped to a DBTYPE (like UUID), it will be converted to TEXT in the payload so it remains compatible with the SQLite extension.
  • Generate IDs with cloudsync_uuid() on both sides.
  • Avoid INTEGER auto-increment PKs.

SQLite:

id TEXT PRIMARY KEY

PostgreSQL:

id TEXT PRIMARY KEY

PostgreSQL (UUID):

id UUID PRIMARY KEY

2) NOT NULL Columns Must Have DEFAULTs

CloudSync merges column-by-column. Any NOT NULL (non-PK) column needs a DEFAULT to avoid constraint failures during merges.

Example:

title TEXT NOT NULL DEFAULT ''
count INTEGER NOT NULL DEFAULT 0

3) Safe Type Mapping

Use types that map cleanly to CloudSync's DBTYPEs:

  • INTEGER → INTEGER (SQLite) / INTEGER (Postgres)
  • FLOAT → REAL / DOUBLE (SQLite) / DOUBLE PRECISION (Postgres)
  • TEXT → TEXT (both)
  • BLOB → BLOB (SQLite) / BYTEA (Postgres)

Avoid: JSON/JSONB, UUID, INET, CIDR, RANGE, ARRAY unless you accept text-cast behavior.

4) Defaults That Match Semantics

Use defaults that serialize the same on both sides:

  • TEXT: DEFAULT ''
  • INTEGER: DEFAULT 0
  • FLOAT: DEFAULT 0.0
  • BLOB: DEFAULT X'00' (SQLite) vs DEFAULT E'\\x00' (Postgres)

5) Foreign Keys and Triggers

  • Foreign keys can cause merge conflicts; test carefully.
  • Application triggers will fire during merge; keep them idempotent or disable in synced tables.

6) Example Conversion

SQLite:

CREATE TABLE notes (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL DEFAULT '',
  body TEXT DEFAULT '',
  views INTEGER NOT NULL DEFAULT 0,
  rating REAL DEFAULT 0.0,
  data BLOB
);

PostgreSQL:

CREATE TABLE notes (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL DEFAULT '',
  body TEXT DEFAULT '',
  views INTEGER NOT NULL DEFAULT 0,
  rating DOUBLE PRECISION DEFAULT 0.0,
  data BYTEA
);

7) Enable CloudSync

SQLite:

.load dist/cloudsync.dylib
SELECT cloudsync_init('notes');

PostgreSQL:

CREATE EXTENSION cloudsync;
SELECT cloudsync_init('notes');

Checklist

  • PKs are TEXT (or UUID in PostgreSQL)
  • All NOT NULL columns have DEFAULT
  • Only INTEGER/FLOAT/TEXT/BLOB-compatible types
  • Same column names and order
  • Same defaults (semantic match)

Please follow these Database Schema Recommendations

Pre-built Binaries

Download the appropriate pre-built binary for your platform from the official Releases page:

  • Linux: x86 and ARM
  • macOS: x86 and ARM
  • Windows: x86
  • Android
  • iOS

Loading the Extension

-- In SQLite CLI
.load ./cloudsync

-- In SQL
SELECT load_extension('./cloudsync');

WASM Version -> React client-side

Make sure to install the extension tagged as dev and not latest

npm i @sqliteai/sqlite-wasm@dev

Then follow the instructions from the README

Swift Package

You can add this repository as a package dependency to your Swift project. After adding the package, you'll need to set up SQLite with extension loading by following steps 4 and 5 of this guide.

Android Package

Add the following to your Gradle dependencies:

implementation 'ai.sqlite:sync.dev:0.9.96'

Expo

Install the Expo package:

npm install @sqliteai/sqlite-sync-expo-dev

Then follow the instructions from the README

React Native

Install the React Native library:

npm install @sqliteai/sqlite-sync-react-native

Then follow the instructions from the README

Node -> React server-side

npm i better-sqlite3
npm i @sqliteai/sqlite-sync-dev

echo "import { getExtensionPath } from '@sqliteai/sqlite-sync-dev';
import Database from 'better-sqlite3';

const db = new Database(':memory:');
db.loadExtension(getExtensionPath());

// Ready to use
const version = db.prepare('SELECT cloudsync_version()').pluck().get();
console.log('Sync extension version:', version);" >> index.js

node index.js

Naming Clarification

  • sqlite-sync → Client-side SQLite extension
  • cloudsync → Synchronization server microservice
  • postgres-sync → PostgreSQL extension

The sqlite-sync extension is loaded in SQLite under the extension name:
cloudsync