implementation of a websocket server storing yjs documents in a postgresql database

yjs-pg-server.js

const WebSocket = require('ws');
const http = require('http');
const Y = require('yjs');
const wsUtils = require('./utils');
const cookie = require('cookie');
const QuillDelta = require('quill-delta');

//const QuillConverter = require('node-quill-converter');
//const MdastFromQuillDelta = require('mdast-util-from-quill-delta');

const PGPool = require('pg').Pool
const db = new PGPool({
  connectionString: process.env.DATABASE_URL
})

persistence = {
  bindState: (identifier, doc) => {
    return db.query('SELECT notes_y_doc_state, notes_quill_delta from documents WHERE identifier = $1 LIMIT 1', [identifier])
      .then(res => {
        console.log('loaded state for ', identifier);
        const delta = res.rows[0].notes_quill_delta;
        const initialState = res.rows[0].notes_y_doc_state;
        if (initialState) {
          console.log('applied initial update', identifier);
          Y.applyUpdate(doc, initialState);
        }
      })
      .catch(e => { console.log('failed to load', identifier, e) });

  },
  writeState: (identifier, doc) => {
    console.log('writeState for', identifier);

    const state = Y.encodeStateAsUpdate(doc);
    const rawText = doc.getText('quill').toString();
    const delta = doc.getText('quill').toDelta();
    const deltaJSON = JSON.stringify(delta, null, 2);
    // TODO: add support for custom blots
    // const html = rawText; //QuillConverter.convertDeltaToHtml(delta);
    // const mdast = null;// MdastFromQuillDelta(new QuillDelta(delta));
    // const mdastJSON = '{}';//JSON.stringify(mdast, null, 2);

    return db.query('UPDATE documents SET notes_raw_text = $1, notes_quill_delta = $2, notes_y_doc_state = $3 WHERE identifier = $4', [rawText, deltaJSON, state, identifier])
      .then(res => { console.log('updated', identifier); })
      .catch(e => { console.log('failed to update', identifier, e); })
  }
}

wsUtils.setPersistence(persistence);

const wss = new WebSocket.Server({ noServer: true });

const port = process.env.PORT || 1234;

const server = http.createServer((request, response) => {
  response.writeHead(200, { 'Content-Type': 'text/plain' });
  response.end('');
})

wss.on('connection', (conn, req) => wsUtils.setupWSConnection(conn, req));

server.on('upgrade', (request, socket, head) => {
  // TODO: proper authentication
  let user = null;
  if (request.headers.cookie) {
    const cookies = cookie.parse(request.headers.cookie);
    const user = cookies.user_identifier;
  }
  if (!user) {
    console.log('Failed to authenticate', user)
    socket.destroy();
    return;
  }
  wss.handleUpgrade(request, socket, head, (ws) => {
    wss.emit('connection', ws, request);
  })
})

server.listen(port);

console.log('editor server running on port', port);

Hey Michael. Thanks for posting this. What column type did you use for notes_y_doc_state?

@SamDuvall it seems I used a postgresql binary field t.binary "notes_y_doc_state"

  create_table "meetings", force: :cascade do |t|
    t.string "name"
    t.string "identifier"
    t.text "notes_raw_text"
    t.text "notes_html"
    t.json "notes_mdast"
    t.json "notes_quill_delta"
    t.binary "notes_y_doc_state"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["identifier"], name: "index_meetings_on_identifier"
  end

Great job!!! Star!
Little update

thank you. In case someone stuck on updating database, such as https://discuss.yjs.dev/t/persist-data-in-postgres/1176

then that's true - it's because multiple yjs instances. This solution: yjs/y-websocket#72 (comment) works.