PostgreSQL in the Browser: A Practical Guide to PGlite

import { useLiveQuery } from '@electric-sql/pglite-react'
 
function TaskList() {
  const tasks = useLiveQuery<{ id: number; title: string; done: boolean }>(
    'SELECT * FROM tasks WHERE done = false ORDER BY created_at DESC'
  )
 
  return (
    <ul>
      {tasks?.rows.map(task => (
        <li key={task.id}>{task.title}</li>
      ))}
    </ul>
  )
}

pnpm add @electric-sql/pglite

import { PGlite } from '@electric-sql/pglite'
 
// In-memory (for testing — cleared on refresh)
const db = new PGlite()
 
// IndexedDB persistence (browser apps)
const db = new PGlite('idb://my-app-db', {
  relaxedDurability: true, // Relaxed fsync — recent writes may be lost on forced crash
})
 
// OPFS (must be inside a Web Worker context only)
const db = new PGlite('opfs://my-app-db')
 
// DDL uses exec() — can execute multiple statements separated by semicolons
await db.exec(`
  CREATE TABLE IF NOT EXISTS tasks (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    done BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ DEFAULT NOW()
  )
`)
 
try {
  // Parameter binding uses query() — $1 syntax prevents SQL injection and enables plan reuse
  const result = await db.query<{ id: number; title: string }>(
    'SELECT id, title FROM tasks WHERE done = $1',
    [false]
  )
  console.log(result.rows)
} catch (err) {
  // IndexedDB can fail due to storage quota exceeded, etc.
  console.error('Query failed:', err)
}

pnpm add drizzle-orm @electric-sql/pglite
pnpm add -D drizzle-kit

// schema.ts
import { pgTable, serial, text, boolean, timestamp } from 'drizzle-orm/pg-core'
 
export const tasks = pgTable('tasks', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  done: boolean('done').default(false),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow(),
})

// db.ts
import { PGlite } from '@electric-sql/pglite'
import { drizzle } from 'drizzle-orm/pglite'
import { migrate } from 'drizzle-orm/pglite/migrator'
import * as schema from './schema'
 
const client = new PGlite('idb://my-app-db', {
  relaxedDurability: true,
})
 
export const db = drizzle(client, { schema })
 
// Browser migrations: unlike the server, instead of running drizzle-kit push,
// we call migrate() directly at runtime. Include the migrations folder in the bundle
// and call this once at app initialization.
export async function initDb() {
  try {
    await migrate(db, { migrationsFolder: './drizzle' })
  } catch (err) {
    console.error('Migration failed:', err)
    throw err
  }
}

// Usage example
import { db, initDb } from './db'
import { tasks } from './schema'
import { eq } from 'drizzle-orm'
 
await initDb() // Call once at the app entry point
 
await db.insert(tasks).values({ title: 'Browser DB test' })
 
const pendingTasks = await db
  .select()
  .from(tasks)
  .where(eq(tasks.done, false))
  .orderBy(tasks.createdAt)
 
await db.update(tasks).set({ done: true }).where(eq(tasks.id, 1))

pnpm add @electric-sql/pglite-worker

// worker.ts (separate file; bundler processes as a separate chunk)
import { PGlite } from '@electric-sql/pglite'
import { worker } from '@electric-sql/pglite/worker'
 
// Initialize with the worker() function — PGliteWorker.init() is the old API.
worker({
  async init() {
    return new PGlite('idb://my-app-db', { relaxedDurability: true })
  },
})

// main.ts
import { PGliteWorker } from '@electric-sql/pglite/worker'
 
let db: PGliteWorker
 
export async function getDb() {
  if (!db) {
    try {
      db = await PGliteWorker.create(
        new Worker(new URL('./worker.ts', import.meta.url), { type: 'module' })
      )
    } catch (err) {
      console.error('Worker DB initialization failed:', err)
      throw err
    }
  }
  return db
}
 
// Usage after this is identical to regular PGlite
const db = await getDb()
await db.query('SELECT * FROM tasks')

pnpm add @electric-sql/pglite-sync

import { PGlite } from '@electric-sql/pglite'
import { electricSync } from '@electric-sql/pglite-sync'
 
const db = new PGlite('idb://my-app-db', {
  relaxedDurability: true,
  extensions: { electric: electricSync() },
})
 
try {
  await db.electric.syncShapeToTable({
    shape: {
      url: 'https://your-electric-server.com/v1/shape',
      params: { table: 'tasks' },
    },
    table: 'tasks',
    primaryKey: ['id'],
  })
} catch (err) {
  // Electric retries network errors internally, but failures
  // in the initial connection itself must be handled at the app level.
  console.error('Sync setup failed:', err)
}

// Pattern for encrypting with AES-GCM and storing in IndexedDB (pseudocode level)
async function encryptAndStore(db: PGlite, plaintext: string, key: CryptoKey) {
  const iv = crypto.getRandomValues(new Uint8Array(12))
  const encoded = new TextEncoder().encode(plaintext)
 
  const ciphertext = await crypto.subtle.encrypt(
    { name: 'AES-GCM', iv },
    key,
    encoded
  )
 
  // Serialize the encrypted binary as base64 for storage
  const stored = btoa(String.fromCharCode(...new Uint8Array(ciphertext)))
  await db.query(
    'INSERT INTO secrets (iv, ciphertext) VALUES ($1, $2)',
    [btoa(String.fromCharCode(...iv)), stored]
  )
}
 
// Store the key in Web Crypto Key Storage or session memory, NOT in IndexedDB
// Storing the plaintext key on the device defeats the purpose of encryption.

import { PGlite } from '@electric-sql/pglite'
import { vector } from '@electric-sql/pglite/vector'
 
const db = new PGlite('idb://ai-app-db', {
  extensions: { vector },
})
 
await db.exec(`
  CREATE EXTENSION IF NOT EXISTS vector;
 
  CREATE TABLE IF NOT EXISTS documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding vector(384)
  );
 
  CREATE INDEX ON documents
  USING hnsw (embedding vector_cosine_ops);
`)
 
// Generate embeddings client-side with @xenova/transformers
// import { pipeline } from '@xenova/transformers'
// const embedder = await pipeline('feature-extraction', 'Xenova/all-MiniLM-L6-v2')
// const output = await embedder('text to search', { pooling: 'mean', normalize: true })
// const embedding = Array.from(output.data) // 384-dimensional float array
 
// Dummy vector for testing when no embedding is available: Array.from({ length: 384 }, (_, i) => i * 0.001)
const dummyEmbedding = Array.from({ length: 384 }, (_, i) => i * 0.001)
 
try {
  await db.query(
    'INSERT INTO documents (content, embedding) VALUES ($1, $2::vector)',
    ['Offline-first app development', JSON.stringify(dummyEmbedding)]
  )
 
  // Cosine similarity-based search
  const results = await db.query<{ content: string; similarity: number }>(
    `SELECT content, 1 - (embedding <=> $1::vector) AS similarity
     FROM documents
     ORDER BY embedding <=> $1::vector
     LIMIT 5`,
    [JSON.stringify(dummyEmbedding)]
  )
  console.log(results.rows)
} catch (err) {
  console.error('Vector search failed:', err)
}

// ❌ Runtime error when executed on the main thread
const db = new PGlite('opfs://my-db')
 
// ✅ Only works inside a Web Worker file
// Inside worker.ts:
const db = new PGlite('opfs://my-db')

// ❌ Slow performance waiting for fsync on every write
const db = new PGlite('idb://my-db')
 
// ✅ Better performance by relaxing WAL flushes — but recent writes may be lost on forced crash
const db = new PGlite('idb://my-db', {
  relaxedDurability: true,
})
// For data where loss is absolutely unacceptable, such as financial transactions, keep this false.

// ❌ Running queries before migration
const db = drizzle(client, { schema })
await db.select().from(tasks) // Table may not exist yet
 
// ✅ Run migrations first at the app entry point
await initDb() // Includes the migrate() call
const results = await db.select().from(tasks)

// ❌ Opening the same IndexedDB in a second tab causes a lock conflict
const db = new PGlite('idb://my-db') // Independent instance per tab — conflict
 
// ✅ All tabs share a single instance through a Worker
const db = await PGliteWorker.create(new Worker(...))