Kysely: منشئ استعلامات SQL آمن الأنواع مع Next.js و PostgreSQL 2026

npx create-next-app@latest kysely-tasks --typescript --tailwind --app --src-dir --eslint
cd kysely-tasks

npm run dev

npm install kysely pg
npm install -D @types/pg kysely-codegen tsx

DATABASE_URL=postgres://user:password@localhost:5432/kysely_tasks

docker run --name kysely-pg -e POSTGRES_PASSWORD=password -e POSTGRES_DB=kysely_tasks -p 5432:5432 -d postgres:16

import { Kysely, sql } from "kysely";
 
export async function up(db: Kysely<any>): Promise<void> {
  await db.schema
    .createTable("users")
    .addColumn("id", "uuid", (col) =>
      col.primaryKey().defaultTo(sql`gen_random_uuid()`)
    )
    .addColumn("email", "varchar(255)", (col) => col.notNull().unique())
    .addColumn("name", "varchar(255)", (col) => col.notNull())
    .addColumn("created_at", "timestamptz", (col) =>
      col.notNull().defaultTo(sql`now()`)
    )
    .execute();
 
  await db.schema
    .createTable("tasks")
    .addColumn("id", "uuid", (col) =>
      col.primaryKey().defaultTo(sql`gen_random_uuid()`)
    )
    .addColumn("title", "varchar(500)", (col) => col.notNull())
    .addColumn("description", "text")
    .addColumn("status", "varchar(20)", (col) =>
      col.notNull().defaultTo("todo")
    )
    .addColumn("assignee_id", "uuid", (col) =>
      col.references("users.id").onDelete("set null")
    )
    .addColumn("due_date", "date")
    .addColumn("created_at", "timestamptz", (col) =>
      col.notNull().defaultTo(sql`now()`)
    )
    .execute();
 
  await db.schema
    .createIndex("tasks_assignee_idx")
    .on("tasks")
    .column("assignee_id")
    .execute();
}
 
export async function down(db: Kysely<any>): Promise<void> {
  await db.schema.dropTable("tasks").execute();
  await db.schema.dropTable("users").execute();
}

import { promises as fs } from "node:fs";
import path from "node:path";
import { Kysely, Migrator, FileMigrationProvider, PostgresDialect } from "kysely";
import { Pool } from "pg";
 
async function migrate() {
  const db = new Kysely<any>({
    dialect: new PostgresDialect({
      pool: new Pool({ connectionString: process.env.DATABASE_URL }),
    }),
  });
 
  const migrator = new Migrator({
    db,
    provider: new FileMigrationProvider({
      fs,
      path,
      migrationFolder: path.join(process.cwd(), "src/db/migrations"),
    }),
  });
 
  const { error, results } = await migrator.migrateToLatest();
 
  results?.forEach((r) => {
    if (r.status === "Success") {
      console.log(`تم تطبيق الهجرة "${r.migrationName}"`);
    } else if (r.status === "Error") {
      console.error(`فشل تطبيق "${r.migrationName}"`);
    }
  });
 
  if (error) {
    console.error("فشلت عملية الهجرة:", error);
    process.exit(1);
  }
 
  await db.destroy();
}
 
migrate();

{
  "scripts": {
    "db:migrate": "tsx --env-file=.env.local src/db/migrate.ts",
    "db:codegen": "kysely-codegen --out-file src/db/types.ts --camel-case"
  }
}

npm run db:migrate

DATABASE_URL=$DATABASE_URL npm run db:codegen

export interface DB {
  users: Users;
  tasks: Tasks;
}
 
export interface Users {
  id: Generated<string>;
  email: string;
  name: string;
  createdAt: Generated<Date>;
}
 
export interface Tasks {
  id: Generated<string>;
  title: string;
  description: string | null;
  status: Generated<string>;
  assigneeId: string | null;
  dueDate: Date | null;
  createdAt: Generated<Date>;
}

import { Kysely, PostgresDialect, CamelCasePlugin } from "kysely";
import { Pool } from "pg";
import type { DB } from "./types";
 
const globalForDb = globalThis as unknown as {
  db: Kysely<DB> | undefined;
};
 
export const db =
  globalForDb.db ??
  new Kysely<DB>({
    dialect: new PostgresDialect({
      pool: new Pool({
        connectionString: process.env.DATABASE_URL,
        max: 10,
      }),
    }),
    plugins: [new CamelCasePlugin()],
  });
 
if (process.env.NODE_ENV !== "production") {
  globalForDb.db = db;
}

"use server";
 
import { revalidatePath } from "next/cache";
import { db } from "@/db/client";
 
export async function createTask(formData: FormData) {
  const title = formData.get("title") as string;
  const assigneeId = (formData.get("assigneeId") as string) || null;
 
  const task = await db
    .insertInto("tasks")
    .values({
      title,
      assigneeId,
    })
    .returningAll()
    .executeTakeFirstOrThrow();
 
  revalidatePath("/");
  return task;
}
 
export async function listTasks() {
  return db
    .selectFrom("tasks")
    .leftJoin("users", "users.id", "tasks.assigneeId")
    .select([
      "tasks.id",
      "tasks.title",
      "tasks.description",
      "tasks.status",
      "tasks.dueDate",
      "users.name as assigneeName",
    ])
    .orderBy("tasks.createdAt", "desc")
    .execute();
}
 
export async function updateTaskStatus(id: string, status: string) {
  await db
    .updateTable("tasks")
    .set({ status })
    .where("id", "=", id)
    .execute();
 
  revalidatePath("/");
}
 
export async function deleteTask(id: string) {
  await db.deleteFrom("tasks").where("id", "=", id).execute();
  revalidatePath("/");
}

import { listTasks, createTask, updateTaskStatus } from "./actions/tasks";
 
export default async function HomePage() {
  const tasks = await listTasks();
 
  return (
    <main className="max-w-3xl mx-auto p-8">
      <h1 className="text-3xl font-bold mb-6">مهام الفريق</h1>
 
      <form action={createTask} className="flex gap-2 mb-8">
        <input
          type="text"
          name="title"
          placeholder="ما الذي يجب فعله؟"
          className="flex-1 border rounded px-3 py-2"
          required
        />
        <button type="submit" className="bg-black text-white px-4 py-2 rounded">
          إضافة
        </button>
      </form>
 
      <ul className="space-y-2">
        {tasks.map((task) => (
          <li
            key={task.id}
            className="border rounded p-4 flex items-center justify-between"
          >
            <div>
              <div className="font-medium">{task.title}</div>
              {task.assigneeName && (
                <div className="text-sm text-gray-600">
                  مُسنَدة إلى {task.assigneeName}
                </div>
              )}
            </div>
            <form
              action={async () => {
                "use server";
                await updateTaskStatus(
                  task.id,
                  task.status === "done" ? "todo" : "done"
                );
              }}
            >
              <button className="text-sm text-blue-600">
                {task.status === "done" ? "إعادة فتح" : "إنجاز"}
              </button>
            </form>
          </li>
        ))}
      </ul>
    </main>
  );
}

import { sql } from "kysely";
 
const stats = await db
  .selectFrom("tasks")
  .select([
    "status",
    db.fn.count<number>("id").as("total"),
    sql<number>`COUNT(*) FILTER (WHERE due_date < now())`.as("overdue"),
  ])
  .groupBy("status")
  .execute();

const result = await db
  .with("recent_tasks", (cte) =>
    cte
      .selectFrom("tasks")
      .selectAll()
      .where("createdAt", ">", new Date(Date.now() - 7 * 86400000))
  )
  .selectFrom("recent_tasks")
  .selectAll()
  .where("status", "=", "todo")
  .execute();

await db.transaction().execute(async (trx) => {
  const user = await trx
    .insertInto("users")
    .values({ email: "alice@example.com", name: "Alice" })
    .returning("id")
    .executeTakeFirstOrThrow();
 
  await trx
    .insertInto("tasks")
    .values({
      title: "أهلًا بك معنا",
      assigneeId: user.id,
    })
    .execute();
});

import { sql } from "kysely";
 
const fuzzyMatches = await db
  .selectFrom("tasks")
  .select(["id", "title"])
  .where(sql<boolean>`title % ${"deploy"}`)
  .orderBy(sql`similarity(title, ${"deploy"})`, "desc")
  .limit(10)
  .execute();

import { describe, it, expect, beforeEach } from "vitest";
import { db } from "@/db/client";
 
describe("tasks", () => {
  beforeEach(async () => {
    await db.deleteFrom("tasks").execute();
  });
 
  it("ينشئ ويقرأ مهمة", async () => {
    await db
      .insertInto("tasks")
      .values({ title: "إطلاق" })
      .execute();
 
    const found = await db
      .selectFrom("tasks")
      .selectAll()
      .where("title", "=", "إطلاق")
      .executeTakeFirst();
 
    expect(found?.title).toBe("إطلاق");
  });
});