PGlite: تشغيل PostgreSQL في المتصفح باستخدام WebAssembly

npm create vite@latest pglite-todo -- --template react-ts
cd pglite-todo

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

// src/db.ts
import { PGlite } from "@electric-sql/pglite";
 
// استخدام IndexedDB للتخزين الدائم عبر إعادة تحميل الصفحة
const db = new PGlite("idb://todo-app");
 
export default db;

// src/migrate.ts
import db from "./db";
 
export async function runMigrations() {
  await db.exec(`
    CREATE TABLE IF NOT EXISTS todos (
      id SERIAL PRIMARY KEY,
      title TEXT NOT NULL,
      completed BOOLEAN DEFAULT false,
      created_at TIMESTAMP DEFAULT NOW()
    );
  `);
}

// src/PGliteProvider.tsx
import { PGliteProvider } from "@electric-sql/pglite-react";
import { useEffect, useState, type ReactNode } from "react";
import db from "./db";
import { runMigrations } from "./migrate";
 
export function DatabaseProvider({ children }: { children: ReactNode }) {
  const [ready, setReady] = useState(false);
 
  useEffect(() => {
    runMigrations().then(() => setReady(true));
  }, []);
 
  if (!ready) {
    return <div className="loading">جارٍ تهيئة قاعدة البيانات...</div>;
  }
 
  return <PGliteProvider db={db}>{children}</PGliteProvider>;
}

// src/TodoApp.tsx
import { useLiveQuery, usePGlite } from "@electric-sql/pglite-react";
import { useState } from "react";
 
interface Todo {
  id: number;
  title: string;
  completed: boolean;
  created_at: string;
}
 
export function TodoApp() {
  const db = usePGlite();
  const [newTitle, setNewTitle] = useState("");
 
  // استعلام حي — يُحدّث تلقائياً عند تغيّر البيانات
  const todos = useLiveQuery<Todo>(
    "SELECT * FROM todos ORDER BY created_at DESC"
  );
 
  const addTodo = async () => {
    if (!newTitle.trim()) return;
    await db.exec("INSERT INTO todos (title) VALUES ($1)", [newTitle.trim()]);
    setNewTitle("");
  };
 
  const toggleTodo = async (id: number, completed: boolean) => {
    await db.exec("UPDATE todos SET completed = $1 WHERE id = $2", [
      !completed,
      id,
    ]);
  };
 
  const deleteTodo = async (id: number) => {
    await db.exec("DELETE FROM todos WHERE id = $1", [id]);
  };
 
  const handleKeyDown = (e: React.KeyboardEvent) => {
    if (e.key === "Enter") addTodo();
  };
 
  return (
    <div className="todo-app">
      <h1>تطبيق مهام PGlite</h1>
      <p className="subtitle">
        مدعوم بـ PostgreSQL يعمل في متصفحك عبر WebAssembly
      </p>
 
      <div className="input-row">
        <input
          type="text"
          value={newTitle}
          onChange={(e) => setNewTitle(e.target.value)}
          onKeyDown={handleKeyDown}
          placeholder="ما الذي يجب إنجازه؟"
        />
        <button onClick={addTodo}>إضافة</button>
      </div>
 
      <ul className="todo-list">
        {todos?.rows.map((todo) => (
          <li key={todo.id} className={todo.completed ? "completed" : ""}>
            <label>
              <input
                type="checkbox"
                checked={todo.completed}
                onChange={() => toggleTodo(todo.id, todo.completed)}
              />
              <span>{todo.title}</span>
            </label>
            <button className="delete" onClick={() => deleteTodo(todo.id)}>
              حذف
            </button>
          </li>
        ))}
      </ul>
 
      {todos?.rows.length === 0 && (
        <p className="empty">لا توجد مهام بعد. أضف واحدة أعلاه!</p>
      )}
    </div>
  );
}

// src/App.tsx
import { DatabaseProvider } from "./PGliteProvider";
import { TodoApp } from "./TodoApp";
import "./App.css";
 
function App() {
  return (
    <DatabaseProvider>
      <TodoApp />
    </DatabaseProvider>
  );
}
 
export default App;

/* src/App.css */
:root {
  font-family: system-ui, -apple-system, sans-serif;
  line-height: 1.6;
  color: #1a1a2e;
  background: #f0f0f5;
}
 
.loading {
  display: flex;
  justify-content: center;
  align-items: center;
  height: 100vh;
  font-size: 1.2rem;
  color: #666;
}
 
.todo-app {
  max-width: 600px;
  margin: 2rem auto;
  padding: 2rem;
  background: white;
  border-radius: 12px;
  box-shadow: 0 4px 24px rgba(0, 0, 0, 0.08);
}
 
h1 {
  margin: 0 0 0.25rem;
  font-size: 1.8rem;
}
 
.subtitle {
  margin: 0 0 1.5rem;
  color: #888;
  font-size: 0.9rem;
}
 
.input-row {
  display: flex;
  gap: 0.5rem;
  margin-bottom: 1.5rem;
}
 
.input-row input {
  flex: 1;
  padding: 0.75rem 1rem;
  border: 2px solid #e0e0e0;
  border-radius: 8px;
  font-size: 1rem;
  transition: border-color 0.2s;
}
 
.input-row input:focus {
  outline: none;
  border-color: #6c63ff;
}
 
.input-row button {
  padding: 0.75rem 1.5rem;
  background: #6c63ff;
  color: white;
  border: none;
  border-radius: 8px;
  font-size: 1rem;
  cursor: pointer;
  transition: background 0.2s;
}
 
.input-row button:hover {
  background: #5a52d5;
}
 
.todo-list {
  list-style: none;
  padding: 0;
  margin: 0;
}
 
.todo-list li {
  display: flex;
  align-items: center;
  justify-content: space-between;
  padding: 0.75rem 0;
  border-bottom: 1px solid #f0f0f0;
}
 
.todo-list li label {
  display: flex;
  align-items: center;
  gap: 0.75rem;
  cursor: pointer;
  flex: 1;
}
 
.todo-list li.completed span {
  text-decoration: line-through;
  color: #aaa;
}
 
.delete {
  background: none;
  border: none;
  color: #e74c3c;
  cursor: pointer;
  font-size: 0.85rem;
  padding: 0.25rem 0.5rem;
  border-radius: 4px;
}
 
.delete:hover {
  background: #ffeaea;
}
 
.empty {
  text-align: center;
  color: #999;
  padding: 2rem 0;
}

npm run dev

// أضف وظيفة بحث إلى TodoApp
const searchTodos = async (query: string) => {
  if (!query.trim()) return;
 
  const result = await db.query<Todo>(
    `SELECT * FROM todos
     WHERE to_tsvector('english', title) @@ plainto_tsquery('english', $1)
     ORDER BY created_at DESC`,
    [query]
  );
 
  return result.rows;
};

const stats = useLiveQuery<{
  total: number;
  completed: number;
  pending: number;
}>(`
  SELECT
    COUNT(*) as total,
    COUNT(*) FILTER (WHERE completed = true) as completed,
    COUNT(*) FILTER (WHERE completed = false) as pending
  FROM todos
`);

// تخزين البيانات الوصفية كـ JSONB
await db.exec(`
  ALTER TABLE todos ADD COLUMN IF NOT EXISTS
    metadata JSONB DEFAULT '{}'::jsonb
`);
 
// الاستعلام بمعاملات JSON
await db.exec(
  `UPDATE todos SET metadata = metadata || $1::jsonb WHERE id = $2`,
  [JSON.stringify({ priority: "high", tags: ["work"] }), todoId]
);

// node-example.ts
import { PGlite } from "@electric-sql/pglite";
 
async function main() {
  // في Node.js، استخدم مسار ملف للتخزين الدائم
  const db = new PGlite("./my-local-db");
 
  await db.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id SERIAL PRIMARY KEY,
      name TEXT NOT NULL,
      email TEXT UNIQUE
    )
  `);
 
  await db.exec(
    "INSERT INTO users (name, email) VALUES ($1, $2) ON CONFLICT DO NOTHING",
    ["Alice", "alice@example.com"]
  );
 
  const result = await db.query("SELECT * FROM users");
  console.log(result.rows);
 
  await db.close();
}
 
main();

npx tsx node-example.ts

// __tests__/todo.test.ts
import { PGlite } from "@electric-sql/pglite";
import { describe, it, expect, beforeEach } from "vitest";
 
describe("عمليات المهام", () => {
  let db: PGlite;
 
  beforeEach(async () => {
    // قاعدة بيانات جديدة في الذاكرة لكل اختبار
    db = new PGlite();
    await db.exec(`
      CREATE TABLE todos (
        id SERIAL PRIMARY KEY,
        title TEXT NOT NULL,
        completed BOOLEAN DEFAULT false
      )
    `);
  });
 
  it("يجب إدراج مهمة", async () => {
    await db.exec("INSERT INTO todos (title) VALUES ($1)", ["شراء حليب"]);
    const result = await db.query("SELECT * FROM todos");
    expect(result.rows).toHaveLength(1);
    expect(result.rows[0].title).toBe("شراء حليب");
  });
 
  it("يجب تبديل حالة الإكمال", async () => {
    await db.exec("INSERT INTO todos (title) VALUES ($1)", ["تمارين"]);
    await db.exec("UPDATE todos SET completed = true WHERE id = 1");
    const result = await db.query("SELECT completed FROM todos WHERE id = 1");
    expect(result.rows[0].completed).toBe(true);
  });
});

// webpack.config.js
module.exports = {
  experiments: {
    asyncWebAssembly: true,
  },
};

// دائم
const db = new PGlite("idb://my-app");
 
// في الذاكرة فقط (البيانات تُفقد عند التحديث)
const db = new PGlite();

// vite.config.ts
export default defineConfig({
  plugins: [react()],
  server: {
    headers: {
      "Cross-Origin-Opener-Policy": "same-origin",
      "Cross-Origin-Embedder-Policy": "require-corp",
    },
  },
});