서버 Postgres를 브라우저 안에 실시간으로 미러링하기 — ElectricSQL Shapes와 PGlite로 구현하는 로컬 퍼스트 앱

import { PGlite } from '@electric-sql/pglite'
import { live } from '@electric-sql/pglite/live'
 
const db = new PGlite('idb://my-app-db', {
  extensions: { live },
})
 
const result = await db.live.query(
  'SELECT * FROM issues ORDER BY created_at DESC',
  [],
  (updatedResult) => {
    console.log('데이터 변경됨:', updatedResult.rows)
  }
)

import { useShape } from '@electric-sql/react'
 
function IssueList({ userId }: { userId: string }) {
  const { isLoading, data } = useShape<{ id: string; title: string; status: string }>({
    url: `${ELECTRIC_URL}/v1/shape`,
    params: {
      table: 'issues',
      where: `user_id = '${userId}'`,
    },
  })
 
  if (isLoading) return <div>로딩 중...</div>
  return <ul>{data.map(issue => <li key={issue.id}>{issue.title}</li>)}</ul>
}

params: {
  table: 'issues',
  where: `user_id = '${userId}'`,
}

클라이언트 → 인증 미들웨어 / 게이트키퍼 → Electric 서버

npm install @electric-sql/pglite @electric-sql/pglite-sync @electric-sql/pglite-react

// pglite-worker.js
import { PGlite } from '@electric-sql/pglite'
import { live } from '@electric-sql/pglite/live'
import { electricSync } from '@electric-sql/pglite-sync'
import { registerWorkerMessageHandler } from '@electric-sql/pglite/worker'
 
const db = new PGlite('idb://issues-app', {
  extensions: {
    live,
    electric: electricSync(),
  },
})
 
registerWorkerMessageHandler(db)

// lib/db.ts
import { PGliteWorker } from '@electric-sql/pglite/worker'
 
export const db = new PGliteWorker(
  new Worker(new URL('./pglite-worker.js', import.meta.url), { type: 'module' })
)

// lib/db.ts (계속)
export async function initDb(userId: string) {
  await db.exec(`
    -- Electric이 소유하는 서버 동기화 테이블
    CREATE TABLE IF NOT EXISTS issues (
      id TEXT PRIMARY KEY,
      title TEXT NOT NULL,
      description TEXT,
      status TEXT NOT NULL DEFAULT 'open',
      user_id TEXT NOT NULL,
      created_at TIMESTAMPTZ DEFAULT now()
    );
 
    -- 완전히 로컬 전용: 낙관적 상태 보관
    CREATE TABLE IF NOT EXISTS issues_local (
      id TEXT PRIMARY KEY,
      title TEXT NOT NULL,
      description TEXT,
      status TEXT NOT NULL DEFAULT 'open',
      user_id TEXT NOT NULL,
      created_at TIMESTAMPTZ DEFAULT now()
    );
 
    -- 두 테이블을 병합한 뷰: UI는 이 뷰에서만 읽음
    CREATE OR REPLACE VIEW issues_merged AS
    SELECT id, title, description, status, user_id, created_at, false AS is_local
    FROM issues
    UNION ALL
    SELECT id, title, description, status, user_id, created_at, true AS is_local
    FROM issues_local
    WHERE id NOT IN (SELECT id FROM issues);
  `)
 
  -- Electric은 issues 테이블만 관리. issues_local은 건드리지 않음.
  await db.electric.syncShapeToTable({
    shape: {
      url: `${process.env.ELECTRIC_URL}/v1/shape`,
      params: {
        table: 'issues',
        where: `user_id = '${userId}'`,
      },
    },
    table: 'issues',
    primaryKey: ['id'],
  })
}

// hooks/useIssues.ts
import { useLiveQuery } from '@electric-sql/pglite-react'
import { db } from '../lib/db'
 
export function useIssues(statusFilter?: string) {
  return useLiveQuery(
    statusFilter
      ? `SELECT * FROM issues_merged WHERE status = $1 ORDER BY created_at DESC`
      : `SELECT * FROM issues_merged ORDER BY created_at DESC`,
    statusFilter ? [statusFilter] : [],
    db
  )
}

// lib/offline-queue.ts
type QueueEntry = {
  id: string
  type: string
  payload: unknown
  timestamp: number
}
 
const QUEUE_KEY = 'offline-queue'
 
export const offlineQueue = {
  enqueue(entry: Omit<QueueEntry, 'id' | 'timestamp'>) {
    const queue = this.getAll()
    queue.push({ ...entry, id: crypto.randomUUID(), timestamp: Date.now() })
    localStorage.setItem(QUEUE_KEY, JSON.stringify(queue))
  },
  getAll(): QueueEntry[] {
    try {
      return JSON.parse(localStorage.getItem(QUEUE_KEY) ?? '[]')
    } catch {
      return []
    }
  },
  remove(id: string) {
    const filtered = this.getAll().filter(e => e.id !== id)
    localStorage.setItem(QUEUE_KEY, JSON.stringify(filtered))
  },
}

// hooks/useCreateIssue.ts
import { db } from '../lib/db'
import { offlineQueue } from '../lib/offline-queue'
 
export function useCreateIssue() {
  const createIssue = async (title: string, description: string, userId: string) => {
    const optimisticId = crypto.randomUUID()
 
    // 낙관적 상태: Electric이 관리하는 issues가 아닌 issues_local에 저장
    await db.query(
      `INSERT INTO issues_local (id, title, description, status, user_id)
       VALUES ($1, $2, $3, 'open', $4)`,
      [optimisticId, title, description, userId]
    )
 
    try {
      const response = await fetch('/api/issues', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify({ id: optimisticId, title, description }),
      })
 
      if (!response.ok) {
        await db.query(`DELETE FROM issues_local WHERE id = $1`, [optimisticId])
        throw new Error('이슈 생성에 실패했습니다.')
      }
 
      // 서버 쓰기 성공: 낙관적 상태 제거
      // Electric이 issues 테이블에 서버 데이터를 동기화하면 뷰가 자동으로 서버 버전을 표시함
      await db.query(`DELETE FROM issues_local WHERE id = $1`, [optimisticId])
    } catch (error) {
      if (!navigator.onLine) {
        // 오프라인: 낙관적 상태 유지, 재시도 큐에 등록
        offlineQueue.enqueue({
          type: 'create_issue',
          payload: { optimisticId, title, description, userId },
        })
      } else {
        // 온라인이지만 서버 에러: 롤백
        await db.query(`DELETE FROM issues_local WHERE id = $1`, [optimisticId])
        throw error
      }
    }
  }
 
  return { createIssue }
}

// components/IssueList.tsx
import { useIssues } from '../hooks/useIssues'
import { useCreateIssue } from '../hooks/useCreateIssue'
 
export function IssueList({ userId }: { userId: string }) {
  const { rows: issues } = useIssues()
  const { createIssue } = useCreateIssue()
 
  return (
    <div>
      <button onClick={() => createIssue('새 이슈', '설명...', userId)}>
        이슈 추가
      </button>
      <ul>
        {issues?.map(issue => (
          <li key={issue.id} style={{ opacity: issue.is_local ? 0.6 : 1 }}>
            {issue.title} — <span>{issue.status}</span>
            {issue.is_local && <span> (동기화 중...)</span>}
          </li>
        ))}
      </ul>
    </div>
  )
}

// shared-worker.js
import { PGlite } from '@electric-sql/pglite'
import { live } from '@electric-sql/pglite/live'
import { electricSync } from '@electric-sql/pglite-sync'
import { registerWorkerMessageHandler } from '@electric-sql/pglite/worker'
 
const db = new PGlite('idb://issues-app', {
  extensions: {
    live,
    electric: electricSync(),
  },
})
 
registerWorkerMessageHandler(db)

// lib/db.ts (멀티 탭 환경)
import { PGliteWorker } from '@electric-sql/pglite/worker'
 
export const db = new PGliteWorker(
  new SharedWorker(new URL('./shared-worker.js', import.meta.url), { type: 'module' })
)

// 문제가 되는 패턴: syncShapeToTable이 관리하는 테이블에 직접 INSERT
await db.query(`INSERT INTO issues (id, title, ...) VALUES (...)`)
// 다음 Electric 싱크 이벤트가 이 행을 삭제하거나 덮어쓸 수 있음
 
// 올바른 패턴: 별도 로컬 테이블에 낙관적 상태 보관
await db.query(`INSERT INTO issues_local (id, title, ...) VALUES (...)`)
// issues_merged 뷰로 병합해 UI에 표시

// 피하는 것이 좋은 패턴: 테이블 전체 구독
params: { table: 'issues' }
 
// 권장 패턴: 현재 사용자 데이터만, 필요한 컬럼만
params: {
  table: 'issues',
  where: `user_id = '${userId}'`,
  columns: ['id', 'title', 'status'],
}