Tutorial: Use Database Adapter

This tutorial builds on the "Real-Time Collaborative Workbook" tutorial to configure and use a database adapter. A SQLite3 adapter is implemented as a practical example to show configuration steps.

Prerequisites

Complete the Real-Time Collaborative Workbook
Foundational knowledge of Database Adapter

Specific Steps

Step 1: Install Dependency

npm install sqlite3 @mescius/js-collaboration-ot-sqlite

Step 2: Modify server.js

Replace the code from the basic tutorial with the following content:

import express from 'express';
import http from 'http';
import { Server } from '@mescius/js-collaboration';
import * as OT from '@mescius/js-collaboration-ot';
import { type } from '@mescius/spread-sheets-collaboration';
import sqlite3 from 'sqlite3';
import { SqliteDb } from '@mescius/js-collaboration-ot-sqlite';

// Register the type
OT.TypesManager.register(type);

const app = express();
const httpServer = http.createServer(app);
const server = new Server({ httpServer });
const port = 8080;

// Initialize the db
const db = new sqlite3.Database("./docs.db");
const sqliteDbAdapter = new SqliteDb(db);
// Initialize OT document services
const documentServices = new OT.DocumentServices({ db: sqliteDbAdapter });
server.useFeature(OT.documentFeature(documentServices));

// Initialize OT document services
app.use(express.static('public'));

// Start the server
httpServer.listen(port, () => {
    console.log(`Server listening on port ${port}`);
    console.log(`<http://127.0.0.1:${port}/index.html`);
});

Step 3: Initialize the Database

Create an init-database.js file in the root directory.

import sqlite3 from "sqlite3";
const db = new sqlite3.Database("./docs.db");
async function initSqliteDataTables(db) {
    const run = (sql) => {
        return new Promise((resolve, reject) => {
            db.run(sql, (e) => (e ? reject(e) : resolve()));
        });
    };
    await run(
        `CREATE TABLE IF NOT EXISTS documents (
            id TEXT PRIMARY KEY,
            type TEXT NOT NULL,
            version INTEGER NOT NULL,
            snapshot_version INTEGER NOT NULL
        )`
    );
    await run(
        `CREATE TABLE IF NOT EXISTS operations (
            doc_id TEXT NOT NULL,
            version INTEGER NOT NULL,
            operation TEXT NOT NULL,
            PRIMARY KEY (doc_id, version),
            FOREIGN KEY (doc_id) REFERENCES documents (id) ON DELETE CASCADE
        )`
    );
    await run(
        `CREATE TABLE IF NOT EXISTS snapshot_fragments (
            doc_id TEXT NOT NULL,
            fragment_id TEXT NOT NULL,
            data TEXT NOT NULL,
            PRIMARY KEY (doc_id, fragment_id),
            FOREIGN KEY (doc_id) REFERENCES documents (id) ON DELETE CASCADE
        )`
    );
}
// Call the initialization function
initSqliteDataTables(db);

Execute this file to initialize the database tables:

type=warning
You only need to execute the database initialization the first time or after resetting the database.

node init-database.js

Step 4: Build and Run

Build the Client Code
```
npm run build
```
Start the Server
```
npm run start
```

Next Steps

To learn how to implement a custom database, see: Custom Database Adapter.