ShareDB (OT) Collaboration

Real-time collaboration using ShareDB

ShareDB is an OT-based real-time database that enables collaborative editing with automatic conflict resolution. The @rowsncolumns/sharedb package provides a React hook for integrating ShareDB with your spreadsheet.

Installation

yarn

yarn add "@rowsncolumns/sharedb sharedb reconnecting-websocket"

npm

Quick Start

import { useShareDBSpreadsheet } from "@rowsncolumns/sharedb";
import ShareDBClient from "sharedb/lib/client";
import ReconnectingWebSocket from "reconnecting-websocket";

// Create ShareDB connection
const socket = new ReconnectingWebSocket("ws://localhost:8080");
const connection = new ShareDBClient.Connection(socket);

function SpreadsheetEditor() {
  const [sheetData, setSheetData] = useState({});
  const [sheets, setSheets] = useState([]);
  const [tables, setTables] = useState([]);
  const [sheetId, setSheetId] = useState(1);
  const [activeCell, setActiveCell] = useState({ rowIndex: 1, columnIndex: 1 });

  const { onBroadcastPatch, users, synced, isLeader } = useShareDBSpreadsheet({
    connection,
    collection: "spreadsheets",
    documentId: "my-spreadsheet",
    userId: "user-123",
    title: "John Doe",
    sheetId,
    activeCell,
    initialSheets: [],
    onChangeSheetData: setSheetData,
    onChangeSheets: setSheets,
    onChangeTables: setTables,
    onChangeActiveSheet: setSheetId,
    calculateNow,
    enqueueGraphOperation: (op) => {
      // Handle dependency graph updates for formula recalculation
    },
  });

  // Pass onBroadcastPatch to useSpreadsheetState
  return <Spreadsheet sheetData={sheetData} sheets={sheets} users={users} />;
}

Integration with useSpreadsheetState

Connect the ShareDB adapter to your spreadsheet state:

import { useSpreadsheetState } from "@rowsncolumns/spreadsheet-state";
import { useShareDBSpreadsheet } from "@rowsncolumns/sharedb";

function CollaborativeSpreadsheet() {
  const {
    sheetData,
    sheets,
    tables,
    charts,
    embeds,
    namedRanges,
    protectedRanges,
    conditionalFormats,
    dataValidations,
    pivotTables,
    cellXfs,
    sharedStrings,
    setSheetData,
    setSheets,
    setTables,
    setCharts,
    setEmbeds,
    setNamedRanges,
    setProtectedRanges,
    setConditionalFormats,
    setDataValidations,
    setPivotTables,
    setCellXfs,
    setSharedStrings,
    enqueueGraphOperation,
    sheetId,
    activeCell,
    calculateNow,
  } = useSpreadsheetState({
    // Your spreadsheet state config
  });

  const { onBroadcastPatch, users, synced } = useShareDBSpreadsheet({
    connection,
    collection: "spreadsheets",
    documentId: "doc-id",
    userId: currentUser.id,
    title: currentUser.name,
    sheetId,
    activeCell,
    initialSheets: [],
    calculateNow,
    onChangeSheetData: setSheetData,
    onChangeSheets: setSheets,
    onChangeTables: setTables,
    onChangeCharts: setCharts,
    onChangeEmbeds: setEmbeds,
    onChangeNamedRanges: setNamedRanges,
    onChangeProtectedRanges: setProtectedRanges,
    onChangeConditionalFormats: setConditionalFormats,
    onChangeDataValidations: setDataValidations,
    onChangePivotTables: setPivotTables,
    onChangeCellXfs: setCellXfs,
    onChangeSharedStrings: setSharedStrings,
    enqueueGraphOperation,
  });

  // Pass onBroadcastPatch to your spreadsheet
  // ...
}

Setting Up a ShareDB Server

Basic Server

Create a file server.js:

const http = require("http");
const express = require("express");
const ShareDB = require("sharedb");
const WebSocket = require("ws");
const WebSocketJSONStream = require("@teamwork/websocket-json-stream");

// Initialize ShareDB
const backend = new ShareDB();

// Create Express app and HTTP server
const app = express();
const server = http.createServer(app);

// Create WebSocket server
const wss = new WebSocket.Server({ server });

// Handle WebSocket connections
wss.on("connection", (ws) => {
  const stream = new WebSocketJSONStream(ws);
  backend.listen(stream);
});

// Start server
const PORT = process.env.PORT || 8080;
server.listen(PORT, () => {
  console.log(`ShareDB server listening on port ${PORT}`);
});

Install dependencies:

npm install express sharedb ws @teamwork/websocket-json-stream

Run the server:

node server.js

Server with MongoDB Persistence

For production environments, persist data to MongoDB:

npm install sharedb-mongo mongodb

const ShareDBMongo = require("sharedb-mongo");

// Connect to MongoDB
const db = new ShareDBMongo("mongodb://localhost:27017/spreadsheets");

// Initialize ShareDB with MongoDB
const backend = new ShareDB({ db });

Server with PostgreSQL Persistence

npm install sharedb-postgres pg

const ShareDBPostgres = require("sharedb-postgres");

const db = new ShareDBPostgres({
  connectionString: "postgresql://user:password@localhost:5432/spreadsheets",
});

const backend = new ShareDB({ db });

Document Structure

The ShareDB document stores all spreadsheet data:

type ShareDBSpreadsheetDoc = {
  // Cell data: flat map with keys like "1!A1" -> { value, sId, r, c }
  sheetData: Record<string, CellDataV3>;

  // Sheet definitions
  sheets: Sheet[];

  // Tables, charts, embeds
  tables: TableView[];
  charts: EmbeddedChart[];
  embeds: EmbeddedObject[];

  // Named ranges, protected ranges
  namedRanges: NamedRange[];
  protectedRanges: ProtectedRange[];

  // Conditional formats, data validations
  conditionalFormats: ConditionalFormatRule[];
  dataValidations: DataValidationRuleRecord[];

  // Pivot tables
  pivotTables: PivotTable[];

  // Cell formats and shared strings
  cellXfs: Record<string, CellFormat>;
  sharedStrings: Record<string, string>;
};

Cell Key Format

Cell keys follow the pattern ${sheetId}!${A1Address}:

Key	Description
"1!A1"	Cell A1 on sheet 1
"2!B5"	Cell B5 on sheet 2
"1!AA100"	Cell AA100 on sheet 1

CellDataV3 Structure

Each cell value is stored with its position metadata:

type CellDataV3<T> = {
  value: T; // The cell data (text, formula, number, etc.)
  sId: number; // Sheet ID
  r: number; // Row index (0-based)
  c: number; // Column index (0-based)
};

Presence Awareness

The hook automatically manages presence, allowing you to display other users' cursor positions:

const { users } = useShareDBSpreadsheet({ ... });

// Render collaborators in your grid
<CanvasGrid
  users={users}
  userId={currentUserId}
/>

Each user in the users array includes:

type Collaborator = {
  userId: string;
  title: string;
  sheetId: number;
  activeCell: { rowIndex: number; columnIndex: number };
};

Leader Election

The adapter automatically elects a leader among connected clients. The leader is responsible for coordinating recalculation operations.

const { isLeader } = useShareDBSpreadsheet({ ... });

// Leader-specific logic
if (isLeader) {
  // This client is responsible for coordinating recalcs
}

Error Handling

Handle connection errors with the onError callback:

useShareDBSpreadsheet({
  // ...
  onError: (err) => {
    console.error("ShareDB error:", err);
    toast.error("Connection lost. Reconnecting...");
  },
});

Why ShareDB for Spreadsheets?

ShareDB's Operational Transformation (OT) approach is particularly well-suited for spreadsheet collaboration compared to CRDT-based solutions like Yjs.

Comparison with Y.js

Aspect	ShareDB (OT)	Yjs (CRDT)
Conflict Resolution	OT - Server determines canonical order	CRDT - Automatic merge, eventual consistency
Server Required	Yes	Optional (P2P possible)
Recalculation	Leader election makes coordination easy	Requires additional coordination layer
Undo/Redo	Manual (via Immer patches)	Built-in
Offline Support	Requires server connection	Full offline-first support
Audit Trails	Sequential operation log	Distributed history
Data Persistence	MongoDB, PostgreSQL, etc.	LevelDB, IndexedDB, etc.

Why OT Works Well for Spreadsheets

1. Cell-level granularity - The V3 flat map structure ("sheetId!A1" keys) maps perfectly to ShareDB's json0 OT type. Each cell is an independent key-value pair, so conflicts are rare.

2. Server-centric calculation - Spreadsheets typically need a server anyway for formula calculation, persistence, and permissions. OT's server authority aligns naturally with this architecture.

3. Simpler undo/redo - OT's sequential operation log makes history management straightforward. The server maintains a clear order of operations.

4. Predictable conflict resolution - When two users edit the same cell simultaneously (rare in practice), the server determines the winner. No surprising merged states.

5. Batched operations - Large operations (paste 1000 cells, delete rows) can be batched into a single atomic submitOp call, maintaining consistency.

When to Choose ShareDB

• Need a traditional client-server architecture

• Want to leverage existing MongoDB/PostgreSQL infrastructure

• Prefer predictable OT semantics for conflict resolution

• Server-side formula calculation is required

• Need clean audit trails and operation history

When to Choose Y.js

• Need offline-first capabilities

• Want peer-to-peer collaboration without a server

• Need built-in undo/redo support

• Decentralized architecture is preferred

For most spreadsheet use cases with a server backend, ShareDB provides a cleaner, more predictable collaboration model.