Kysely va Knex: tipli va klassik query builder, kysely-codegen, migratsiya va seed

Qisqacha: Query builder — ORM emas (model, relation, "obyekt" yo'q), lekin SQL satrini ham qo'lda yozmaysiz: so'rovni metod zanjiri bilan quraysiz. Kysely — to'liq tipli: Database interfeysi (kysely-codegen uni bazadan yasaydi) bo'yicha jadval va ustun nomlari kompilyatsiyada tekshiriladi. Knex — eski, keng tarqalgan, tipsiz uslub, o'z migratsiya va seed CLI'si bor. Biz topgan tuzoqlar: Kysely 0.29 da migratsiya klasslari kysely/migration ga ko'chgan va FileMigrationProvider Windows'da ishlamadi; Knex'ning migrate:make shabloni CommonJS — "type": "module" loyihada exports is not defined.

Bu darsda

• Kysely'da Database interfeysini yozasiz va kysely-codegen bilan bazadan yasaysiz.
• selectFrom, insertInto, updateTable, deleteFrom va jsonArrayFrom bilan so'rov yozasiz.
• Kysely Migrator bilan migratsiyalarni qo'llaysiz.
• Knex query builder'ini o'qiysiz va yozasiz: where, whereIn, raw, tranzaksiya.
• knex migrate va knex seed bilan ishlaysiz va eski Knex kodini Kysely'ga o'girasiz.

Oldin bilishingiz kerak: Drizzle ORM 2: relations va drizzle-kit, ORM, query builder va raw SQL, Parametrli so'rovlar va SQL injection, Generics: turni parametr qilish.

1. Nega bu kerak?

Drizzle va Prisma — ORM: ular jadvallarni o'zlarining "model" tushunchasiga o'raydi. Ba'zi jamoalar bundan ham yengilroq narsani xohlaydi: "Menga model kerak emas, men SQL'ni bilaman. Faqat satrlarni yopishtirmasdan, xavfsiz va tipli yozay".

Bu — query builder (so'rov quruvchi): db.selectFrom("vazifalar").select(["id", "matn"]).where("egasi_id", "=", 2) — har metod SQL'ning bitta bo'lagi. ORM, query builder va raw SQL darsida bu uch qavatni nazariy ko'rgan edik; endi ikki asosiy vakili bilan amalda.

• Kysely (talaffuzi "Key-Seh-Lee") — zamonaviy, to'liq TypeScript: ustun nomini xato yozsangiz — kompilyatsiya xatosi.
• Knex — 2010-yillar boshidan beri bor, TypeScript'gacha bo'lgan davrning standarti. Ko'p eski loyihalar (va ba'zi ORM'lar ichida) Knex bor. Yangi loyihada kam tanlanadi, lekin ishga kirsangiz uni o'qishga to'g'ri keladi.

O'xshatish: Prisma — tayyor taomlar menyusi, Drizzle — retsept bo'yicha o'zingiz pishirasiz, query builder — sifatli pichoq va taxta: hamma ishni o'zingiz qilasiz, lekin barmog'ingizni kesib olmaysiz.

Chiqishlarni biz ishga tushirdik: PostgreSQL 17.6 (holat 20 namunasi nusxasi), kysely 0.29.6, kysely-codegen 0.20.0, knex 3.3.0, pg 8.23.1, TypeScript 6.0.3, Node 24.21.0, Windows 11.

2. Kysely: tiplar bazadan

2.1 Database interfeysi

Kysely'ga bazaning tuzilishini TypeScript interfeysi bilan aytasiz: jadval nomi → ustunlar turi. Qo'lda yozish mumkin, lekin kysely-codegen uni haqiqiy bazadan yasaydi:

npm i kysely@0.29.6 pg
npm i -D kysely-codegen@0.20.0
DATABASE_URL=postgres://postgres@127.0.0.1:5432/vazifalar \
  npx kysely-codegen --dialect postgres --out-file src/kysely-db.d.ts

• Using dialect 'postgres'.
• Introspecting database...
✓ Introspected 8 tables and generated .\src\kysely-db.d.ts in 134ms.

Yasalgan fayldan vazifalar qismi (kanon SQL'dagi COMMENT izohlari ham turga ko'chdi; bir nechtasini qoldirdik):

export type Generated<T> =
  T extends ColumnType<infer S, infer I, infer U>
  ? ColumnType<S, I | undefined, U>
  : ColumnType<T, T | undefined, T>;

export type Int8 = ColumnType<string, bigint | number | string,
  bigint | number | string>;

export interface Vazifalar {
  bajarildi: Generated<boolean>;
  egasi_id: Int8;
  id: Generated<Int8>;
  /** Counter cache — izohlar_soni_trg yangilaydi; qo'lda
   * yozilmaydi */
  izohlar_soni: Generated<number>;
  matn: string;
  ochirilgan_vaqt: Timestamp | null;
  /** Foydalanuvchi tartibi …; berilmasa trigger oxiriga qo'yadi */
  tartib: number;
  ustuvorlik: Generated<Ustuvorlik>;
  // … boshqa ustunlar
}

export interface DB {
  foydalanuvchilar: Foydalanuvchilar;
  vazifalar: Vazifalar;
  // … jami 8 ta (view va MV ham)
}

Uch narsa:

• ColumnType<O'qish, Yozish, Yangilash> — bitta ustunning uch xil turi. Int8: o'qishda string (chunki pg bigint ni satr qaytaradi!), yozishda son ham, satr ham bo'ladi.
• Generated<T> — sukut qiymatli ustun: yozishda ixtiyoriy.
• tartib: number — bazada NOT NULL va sukutsiz, shuning uchun majburiy. Haqiqatda uni trigger qo'yadi — codegen buni bilmaydi. Kanon ham shu sababli insert'da as never yozishga majbur bo'lgan; biz sql`DEFAULT` bilan hal qildik (pastda).

2.2 Ulanish va birinchi so'rov

import pg from "pg";
import { Kysely, PostgresDialect } from "kysely";
import type { DB } from "./kysely-db.d.ts";

const db = new Kysely<DB>({
  dialect: new PostgresDialect({
    pool: new pg.Pool({ connectionString: process.env.DATABASE_URL }),
  }),
});

await db.selectFrom("vazifalar")
  .select(["id", "matn"])
  .where("egasi_id", "=", 2)
  .where("bajarildi", "=", false)
  .orderBy("id")
  .limit(3)
  .execute();

[
  { id: '14', matn: 'IELTS writing task 2 mashqi #ingliz' },
  { id: '26', matn: "Dugonamning tug'ilgan kuniga sovg'a #oila" },
  { id: '36', matn: 'Rezyume yangilash #ish' }
]
SQL: select "id", "matn" from "vazifalar" where "egasi_id" = $1 and "bajarildi" = $2 order by "id" limit $3 -- [2,false,3]

id — '14' satr: Kysely pg qaytarganini o'zgartirmaydi (Drizzle mode: "number" bilan son bergan edi, Prisma — bigint). Ketma-ket .where() — AND. Shartda ustun, operator va qiymat alohida argumentlar — qiymat har doim parametr.

compile() — bajarmasdan SQL'ni ko'rish (Drizzle'dagi toSQL()):

const q = db.selectFrom("vazifalar").select("id")
  .where((eb) => eb.or([
  eb("ustuvorlik", "=", "yuqori"),
  eb("matn", "ilike", "%bozor%"),
]));
console.log(q.compile().sql);
console.log(q.compile().parameters);

select "id" from "vazifalar" where ("ustuvorlik" = $1 or "matn" ilike $2)
[ 'yuqori', '%bozor%' ]

eb — expression builder (ifoda quruvchi): murakkab shartlar (or, and, not, exists) uchun.

2.3 Tiplar ishlaydi

tsc (strict) bilan uch xatoni sinadik:

await db.selectFrom("vazifalar").select(["id", "matnn"]).execute();
await db.insertInto("vazifalar")
  .values({ egasi_id: 2, matn: "Non olish" }).execute();
const r = await db.selectFrom("vazifalar").select(["id", "matn"])
  .executeTakeFirstOrThrow();
const id: number = r.id;

TS2769: No overload matches this call. … Type '"matnn"' is not assignable to type 'SelectExpression<DB, "vazifalar">'. Did you mean '"matn"'?
TS2345: Argument of type '{ egasi_id: number; matn: string; }' is not assignable to parameter of type 'InsertExpression<DB, "vazifalar">'. … Property 'tartib' is missing …
TS2322: Type 'string' is not assignable to type 'number'.

(Uzun xabarlar … bilan qisqartirildi.) Birinchisi — xato ustun nomi, hatto "Did you mean" tavsiyasi bilan. Ikkinchisi — tartib majburiy (yuqoridagi codegen cheklovi). Uchinchisi — id satr, son emas. Kysely ham Drizzle kabi generatsiyasiz tip xulosasi bilan ishlaydi — faqat turlarni bazadan siz (codegen orqali) olasiz.

3. Kysely: murakkabroq so'rovlar

3.1 JOIN va ichma-ich JSON

import { jsonArrayFrom } from "kysely/helpers/postgres";

await db.selectFrom("vazifalar as v")
  .innerJoin("foydalanuvchilar as f", "f.id", "v.egasi_id")
  .select((eb) => [
    "v.matn",
    "f.ism",
    jsonArrayFrom(eb.selectFrom("izohlar as i")
      .select("i.matn")
      .whereRef("i.vazifa_id", "=", "v.id")
      .orderBy("i.id")).as("izohlar"),
  ])
  .where("v.id", "=", "2")
  .executeTakeFirstOrThrow();

{"matn":"Postgres'ni Docker'da ko'tarish #o'qish #docker","ism":"Aziz Karimov","izohlar":[{"matn":"Port 5432 band ekan, 5499 ga o'tdim"}]}
SQL: select "v"."matn", "f"."ism", (select coalesce(json_agg(agg), '[]') from (select "i"."matn" from "izohlar" as "i" where "i"."vazifa_id" = "v"."id" order by "i"."id") as agg) as "izohlar" from "vazifalar" as "v" inner join "foydalanuvchilar" as "f" on "f"."id" = "v"."egasi_id" where "v"."id" = $1 -- ["2"]

Kysely'da relation yo'q — bog'lanishni o'zingiz yozasiz. Postgres uchun yordamchi jsonArrayFrom ichki so'rovni json_agg ga o'raydi (Drizzle'ning with i qiladigan ishning qo'lda shakli). whereRef — ikki ustunni solishtirish (whereRef siz "v.id" matn qiymat bo'lib qolardi). "vazifalar as v" — taxallus (alias), turlar ham unga moslashadi.

3.2 Yozish

await db.insertInto("vazifalar")
  .values({
    egasi_id: 2,
    matn: "Kysely bilan #kysely",
    tartib: sql<number>`DEFAULT`,
  })
  .returning(["id", "tartib", "versiya"])
  .executeTakeFirst();
await db.updateTable("vazifalar")
  .set({
    bajarildi: true,
    bajarilgan_vaqt: new Date("2026-10-25T06:00:00Z"),
  })
  .where("matn", "=", "Kysely bilan #kysely")
  .returning(["id", "versiya"]).execute();
await db.deleteFrom("vazifalar")
  .where("matn", "=", "Kysely bilan #kysely").executeTakeFirst();

{ id: '49', tartib: 7, versiya: 1 }
SQL: insert into "vazifalar" ("egasi_id", "matn", "tartib") values ($1, $2, DEFAULT) returning "id", "tartib", "versiya" -- [2,"Kysely bilan #kysely"]
[ { id: '49', versiya: 2 } ]
DeleteResult { numDeletedRows: 1n }

sql<number>`DEFAULT` — tartib ga Postgres'ning DEFAULT kalit so'zini berdik: tur talabi bajarildi, bazada esa trigger 7 ni qo'ydi. returning siz delete — DeleteResult (o'chirilganlar soni, bigint).

3.3 Tranzaksiya

await db.transaction().execute(async (tx) => {
  await tx.insertInto("vazifalar").values({
    egasi_id: 2, matn: "Tx ichida", tartib: sql<number>`DEFAULT`,
  }).execute();
  throw new Error("bekor");
});

xato: bekor
bazada: 0
SQL: begin -- []
SQL: insert into "vazifalar" ("egasi_id", "matn", "tartib") values ($1, $2, DEFAULT) -- [2,"Tx ichida"]
SQL: rollback -- []

Istisno — rollback. Izolyatsiya: db.transaction().setIsolationLevel("serializable").execute(…) (Kysely hujjati).

4. Kysely migratsiyalari

4.1 Migratsiya fayli

Kysely'da migratsiya — up va down funksiyali TypeScript fayl. Sxema o'zgarishi uchun db.schema quruvchisi:

// kmig/2026_10_25_01_eslatmalar.ts
import { Kysely, sql } from "kysely";

export async function up(db: Kysely<unknown>): Promise<void> {
  await db.schema.createTable("eslatmalar")
    .addColumn("id", "bigint",
      (c) => c.primaryKey().generatedAlwaysAsIdentity())
    .addColumn("matn", "text", (c) => c.notNull())
    .addColumn("vaqt", "timestamptz",
      (c) => c.notNull().defaultTo(sql`now()`))
    .execute();
}

export async function down(db: Kysely<unknown>): Promise<void> {
  await db.schema.dropTable("eslatmalar").execute();
}

Kysely<unknown> — ataylab: migratsiya bajarilgan paytdagi sxema hozirgi DB turidan farq qiladi; eski migratsiya yangi turga bog'lanmasin. Fayllar nom bo'yicha alifbo tartibida qo'llanadi — shuning uchun sana bilan boshlanadi.

4.2 Tuzoq: 0.29 da import yo'li va Windows

Hujjatdagi (va ko'p maqolalardagi) eski import:

import { FileMigrationProvider, Kysely, Migrator } from "kysely";

Kysely 0.29.6 da:

SyntaxError: The requested module 'kysely' does not provide an export named 'FileMigrationProvider'

0.29 da migratsiya klasslari alohida kirish nuqtasiga ko'chgan — package.json dagi exports da ./migration bor: import { FileMigrationProvider, Migrator } from "kysely/migration". Import tuzatilgach, ikkinchi xato — Windows'da:

latest undefined Error [ERR_UNSUPPORTED_ESM_URL_SCHEME]: Only URLs with a scheme in: file, data, and node are supported by the default ESM loader. On Windows, absolute paths must be valid file:// URLs. Received protocol 'c:'

FileMigrationProvider fayllarni import("C:\\…") bilan yuklaydi, Node esa Windows'da mutlaq yo'lni file:// URL sifatida kutadi. Yechim — provider'ni o'zimiz yozamiz (u — oddiy interfeys):

import { readdir } from "node:fs/promises";
import path from "node:path";
import { pathToFileURL } from "node:url";
import { Migrator } from "kysely/migration";
import type { Migration, MigrationProvider } from "kysely/migration";

const papka = path.resolve("kmig");
const provider: MigrationProvider = {
  async getMigrations() {
    const natija: Record<string, Migration> = {};
    const fayllar = (await readdir(papka))
      .filter((x) => x.endsWith(".ts")).sort();
    for (const f of fayllar) {
      const url = pathToFileURL(path.join(papka, f)).href;
      natija[f.replace(/\.ts$/, "")] = await import(url);
    }
    return natija;
  },
};
const migrator = new Migrator({ db, provider });

latest, yana latest, keyin down:

latest [["2026_10_25_01_eslatmalar","Up","Success"]]
latest []
down [["2026_10_25_01_eslatmalar","Down","Success"]]

Ikkinchi latest — bo'sh: hammasi qo'llangan. Kysely holatni kysely_migration va kysely_migration_lock jadvallarida saqlaydi; lock — ikki server bir vaqtda migratsiya qilmasin. kysely paketining o'zida CLI yo'q — migratsiyani o'zingizning kichik skriptingiz ishga tushiradi (alohida kysely-ctl paketi bor — sinalmagan).

flowchart LR
  F["kmig/*.ts (up/down)"] --> P["MigrationProvider"]
  P --> M["Migrator"]
  M --> T[("kysely_migration")]
  M --> L[("kysely_migration_lock")]
  M --> S["db.schema … execute()"]

Diagrammaga qarang: provider faqat "qaysi fayllar bor"ni aytadi; nimani qo'llashni Migrator jadval bo'yicha hal qiladi.

5. Knex: klassik query builder

5.1 So'rovlar

import knexFactory from "knex";

const knex = knexFactory({
  client: "pg", connection: process.env.DATABASE_URL,
  pool: { min: 0, max: 5 },
});

await knex("vazifalar").select("id", "matn")
  .where({ egasi_id: 2, bajarildi: false })
  .orderBy("id").limit(3);

[
  { id: '14', matn: 'IELTS writing task 2 mashqi #ingliz' },
  { id: '26', matn: "Dugonamning tug'ilgan kuniga sovg'a #oila" },
  { id: '36', matn: 'Rezyume yangilash #ish' }
]
SQL: select "id", "matn" from "vazifalar" where "egasi_id" = $1 and "bajarildi" = $2 order by "id" asc limit $3 -- [2,false,3]

Natija Kysely'niki bilan bir xil, SQL ham deyarli. Farq — tiplarda: knex("vazifalar") qaysi ustunlar borligini bilmaydi, select("matnn") ham kompilyatsiyadan o'tadi va faqat bazada yiqiladi. .where({ … }) obyekt — har kalit AND. Knex so'rovi — "thenable": await qilganda bajariladi.

5.2 toSQL() va toString()

const kq = knex("vazifalar").select("id")
  .whereIn("egasi_id", [1, 2])
  .andWhere("matn", "ilike", "%bozor%");
console.log(kq.toSQL().toNative());
console.log(kq.toString());

{
  sql: 'select "id" from "vazifalar" where "egasi_id" in ($1, $2) and "matn" ilike $3',
  bindings: [ 1, 2, '%bozor%' ]
}
select "id" from "vazifalar" where "egasi_id" in (1, 2) and "matn" ilike '%bozor%'

toNative() — bazaga haqiqatan yuboriladigan shakl ($1 + bindings). toString() — qiymatlar yopishtirilgan satr: faqat log va debug uchun (Drizzle ORM 1, 2-mashqdagi toldir ning o'zi). Uni bazaga yuborish — injection eshigi.

5.3 Yozish, raw va injection

await knex("vazifalar")
  .insert({ egasi_id: 2, matn: "Knex bilan #knex" })
  .returning(["id", "tartib"]);
await knex("vazifalar").where({ matn: "Knex bilan #knex" })
  .update({ ustuvorlik: "yuqori" });
await knex("vazifalar").where({ matn: "Knex bilan #knex" }).del();

[ { id: '51', tartib: 7 } ]
1
1

update va del — ta'sirlangan qatorlar soni. Raw SQL — knex.raw bilan; ikki xil belgi bor:

await knex.raw(
  "SELECT ?? FROM vazifalar WHERE egasi_id = ? ORDER BY id LIMIT 1",
  ["matn", 3],
);

[ { matn: "Mashinani texnik ko'rikka olib borish #mashina" } ]
SQL: SELECT "matn" FROM vazifalar WHERE egasi_id = $1 ORDER BY id LIMIT 1 -- [3]

? — qiymat ($1 parametr bo'ladi), ?? — nom (qo'shtirnoqqa olib, identifikator sifatida qo'yiladi — pg-format ning %I i). ?? ga foydalanuvchi matnini berish baribir xavfli — nom oq ro'yxatdan bo'lsin (SQL injection).

Eski Knex kodida eng ko'p uchraydigan teshik — whereRaw ga satr yopishtirish:

const soz = "' OR 1=1 --";
// ❌
await knex("vazifalar").whereRaw(`matn = '${soz}'`).count("* as n");
// ✅
await knex("vazifalar").where("matn", soz).count("* as n");

Chiqishda ikki sonni va ikki SQL qatorini solishtiring — birida qiymat $1 bo'lib ketgan, birida matn ichiga yopishgan:

whereRaw ulangan: { n: '48' }
where qiymat: { n: '0' }
SQL: select count(*) as "n" from "vazifalar" where matn = '' OR 1=1 --' -- []
SQL: select count(*) as "n" from "vazifalar" where "matn" = $1 -- ["' OR 1=1 --"]

Bir matn — 48 qator. Prisma'dagi $queryRawUnsafe va Drizzle'dagi sql.raw bilan bir xil saboq.

5.4 Tranzaksiya

await knex.transaction(async (trx) => {
  await trx("vazifalar").insert({ egasi_id: 2, matn: "Knex tx" });
  await trx("izohlar")
    .insert({ vazifa_id: 999999, matn: "yo'q vazifa" });
});

xato: 23503 izohlar_vazifa_id_fkey
{ n: '0' }

FK xatosi — ikkala INSERT ham bekor. Knex xatosi — pg xatosining o'zi (code, constraint) — o'ramsiz.

Tekshirib ko'ring: knex.raw("SELECT * FROM ?? WHERE id = ?", [jadval, id]) da jadval URL'dan kelsa, xavfsizmi?

6. Knex migrate va seed

6.1 knexfile.js

// knexfile.js — Knex CLI sozlamasi
export default {
  client: "pg",
  connection: process.env.DATABASE_URL,
  migrations: { directory: "./knex/migrations" },
  seeds: { directory: "./knex/seeds" },
};

Loyiha "type": "module" — export default ishladi (Knex 3 CLI ESM knexfile'ni yukladi).

6.2 migrate:make va CommonJS tuzog'i

npx knex migrate:make eslatmalar

Created Migration: …\knex\migrations\20261025094252_eslatmalar.js

Yasalgan shablon:

/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */
exports.up = function(knex) {

};

exports.up — CommonJS. Bizning loyihada .js fayllar ESM, shuning uchun to'ldirilgan shablon bilan migrate:latest:

ReferenceError: exports is not defined in ES module scope
This file is being treated as an ES module because it has a '.js' file extension and '\\?\C:\…\package.json' contains "type": "module". To treat it as a CommonJS script, rename it to use the '.cjs' file extension.

Node aniq maslahat berdi. Ikki yo'l: faylni .cjs qilish yoki ESM shakliga o'girish:

export async function up(knex) {
  await knex.schema.createTable("eslatmalar", (t) => {
    t.bigIncrements("id");
    t.text("matn").notNullable();
    t.timestamp("vaqt", { useTz: true }).notNullable()
      .defaultTo(knex.fn.now());
  });
}

export async function down(knex) {
  await knex.schema.dropTable("eslatmalar");
}

migrate:rollback, keyin yana migrate:latest:

Batch 1 rolled back: 1 migrations
Batch 1 run: 1 migrations

Knex migratsiyalarni batch (to'plam) bilan qo'llaydi: bitta migrate:latest da qo'llangan hamma fayllar — bitta batch, rollback aynan shu to'plamni qaytaradi. Holat — knex_migrations (nom, batch) va knex_migrations_lock jadvallarida.

6.3 Tuzoq: "corrupt" papka

migrate:latest ni qo'llangan fayl yo'q papka bilan (--migrations-directory) ishga tushirdik — fayl o'chirilgan yoki nomi o'zgargandagi holatning o'zi:

Error: The migration directory is corrupt, the following files are missing: 20261025094252_eslatmalar.js

knex_migrations da yozuv bor, faylning o'zi yo'q — Knex ishlashdan bosh tortadi. Bu — himoya: qo'llangan migratsiya fayli o'zgarmaydi va o'chmaydi (Prisma va drizzle-kit uchun ham shu qoida — Migratsiya tushunchasi).

6.4 Seed

// knex/seeds/01_eslatmalar.js
export async function seed(knex) {
  await knex("eslatmalar").del();
  await knex("eslatmalar").insert([
    { matn: "Non olish" },
    { matn: "Kitob qaytarish" },
  ]);
}

npx knex seed:run ni ikki marta ishga tushirdik:

Ran 1 seed files
Ran 1 seed files
 id |      matn
----+-----------------
  3 | Non olish
  4 | Kitob qaytarish

Seed idempotent (avval o'chiradi) — ikki marta ishga tushirsa ham 2 qator. Lekin id 3 va 4: del() qatorlarni o'chiradi, sequence'ni emas. Testlar id = 1 ga tayansa, buziladi. Deterministik seed qoidalari (va nega prodda seed taqiqlanadi) — Seeding va test ma'lumotlari.

7. Taqqoslash

Kanon o'lchovi (katta, 500 000 vazifa, egasi 77, mediana 30; A — ro'yxat 50, B — 50 vazifa + izohlari, C — INSERT + ROLLBACK):

Hammasi millisekund ichida — tanlov tezlik bilan emas, tip xavfsizligi, migratsiya oqimi va jamoa tajribasi bilan qilinadi (ORM tanlash qo'llanmasi). Kanon Kysely'da bitta topilma yozgan: insert'da as never kerak bo'ldi (DB tipida trigger to'ldiradigan ustunlar) — biz yuqorida buni sql`DEFAULT` bilan yechdik.

8. Ko'p uchraydigan xatolar

9. Mashqlar

Mashqlar kurs/mashqlar/27/31-kysely-knex/ da. 2-mashq yechimi Node 24 da tekshirilgan (bazasiz); 3-mashqni biz bazada ishga tushirdik.

1-mashq (oson): qaysi biri?

• Bazadan Kysely turlarini yasaydigan vosita —
• Kysely'da ikki ustunni solishtirish — ("i.vazifa_id", "=", "v.id")
• Knex raw da ustun nomi o'rnidagi belgi —
• Knex'da bitta migrate:latest qo'llagan fayllar guruhi —

2-mashq (o'rta): kichik query builder

Query builder qanday ishlashini his qilish uchun eng kichigini yozing: jadval(nom) obyekt qaytarsin, uning .select(...ustunlar), .where(ustun, op, qiymat) (bir nechta — AND), .limit(n) metodlari yangi obyekt qaytarsin (asl o'zgarmasin), .compile() esa { sql, params } bersin. Operator — faqat =, <, >, ilike (oq ro'yxat); nomlar "…" ichida.

const OPLAR = new Set(["=", "<", ">", "ilike"]);
const nom = (s) => `"${s.replaceAll('"', '""')}"`;

function jadval(t, h = { ustunlar: [], shartlar: [], limit: null }) {
  const yangi = (o) => jadval(t, { ...h, ...o });
  return {
    select: (...u) => yangi({ ustunlar: [...h.ustunlar, ...u] }),
    where: (u, op, q) => {
      if (!OPLAR.has(op)) throw new Error(`operator: ${op}`);
      return yangi({ shartlar: [...h.shartlar, [u, op, q]] });
    },
    limit: (n) => yangi({ limit: n }),
    compile() {
      const params = [];
      const sel = h.ustunlar.length
        ? h.ustunlar.map(nom).join(", ") : "*";
      let sql = `select ${sel} from ${nom(t)}`;
      if (h.shartlar.length) {
        sql += " where " + h.shartlar.map(([u, op, q]) => {
          params.push(q);
          return `${nom(u)} ${op} $${params.length}`;
        }).join(" and ");
      }
      if (h.limit !== null) {
        params.push(h.limit);
        sql += ` limit $${params.length}`;
      }
      return { sql, params };
    },
  };
}

const asos = jadval("vazifalar")
  .select("id", "matn").where("egasi_id", "=", 2);
const q = asos.where("bajarildi", "=", false).limit(3);
console.log(q.compile());
console.log(asos.compile().params);
try {
  asos.where("id", "; DROP", 1);
} catch (e) {
  console.log(e.message);
}

{
  sql: 'select "id", "matn" from "vazifalar" where "egasi_id" = $1 and "bajarildi" = $2 limit $3',
  params: [ 2, false, 3 ]
}
[ 2 ]
operator: ; DROP

3-mashq (qiyin): eski Knex so'rovini Kysely'ga o'girish

Quyidagi eski Knex kodi o'chirilmagan vazifalarni egasi bo'yicha sanaydi va 5 tadan ko'plarini qoldiradi. Uni Kysely'ga o'giring; ikkala natija bir xil ekanini tekshiring.

const eski = await knex("vazifalar")
  .select("egasi_id")
  .count("* as jami")
  .whereNull("ochirilgan_vaqt")
  .groupBy("egasi_id")
  .havingRaw("count(*) > ?", [5])
  .orderBy("egasi_id");

const yangi = await db.selectFrom("vazifalar")
  .select((eb) => ["egasi_id", eb.fn.countAll<string>().as("jami")])
  .where("ochirilgan_vaqt", "is", null)
  .groupBy("egasi_id")
  .having((eb) => eb.fn.countAll(), ">", 5)
  .orderBy("egasi_id")
  .execute();
console.log(JSON.stringify(eski));
console.log(JSON.stringify(yangi));

[{"egasi_id":"1","jami":"12"},{"egasi_id":"2","jami":"6"},{"egasi_id":"3","jami":"6"},{"egasi_id":"4","jami":"9"},{"egasi_id":"5","jami":"6"}]
[{"egasi_id":"1","jami":"12"},{"egasi_id":"2","jami":"6"},{"egasi_id":"3","jami":"6"},{"egasi_id":"4","jami":"9"},{"egasi_id":"5","jami":"6"}]

10. Loyiha qadami: kanon tajribasi, loyihaga kirmaydi

vazifalar-api Kysely yoki Knex'ga ko'chmaydi. Kanon ikkalasini «Taqqoslash» bo'limidagi o'lchovda sinadi; qaror (ORM tanlash qo'llanmasi): API — Prisma + pg; Kysely — Drizzle bilan birga eng yaqin muqobil (SQL'ga to'liq nazorat, yaxshi turlar). Knex — yangi loyihaga tavsiya etilmaydi: turlari zaif, relation yo'q (B savolida 2 SQL va JS'da birlashtirish), lekin eski loyihalarni o'qish uchun bu darsdagi bilim yetadi.

11. Real ishda

• Kysely ko'pincha "ORM kerak emas, lekin tip kerak" degan jamoalarda: murakkab hisobot so'rovlari, mavjud katta baza ustida servis. NestJS bilan ulash — Drizzle va Kysely bilan NestJS (30-qism).
• Knex — ko'p eski Express loyihalarida va Objection.js ORM'ining ichida. Ish boshlaganingizda knexfile.js va migrations/ papkasini ko'rsangiz — bu dars kerak bo'ladi.
• Intervyuda: "Query builder va ORM farqi?", "Kysely turlarni qayerdan oladi?", "Knex'da ? va ?? farqi?", "toString() ni bazaga yuborsa nima bo'ladi?"

Xulosa

• Query builder — model yo'q, SQL satri ham yo'q: metod zanjiri, qiymatlar har doim parametr.
• Kysely: Kysely<DB>, turlar kysely-codegen dan; ColumnType, Generated; bigint — satr; trigger to'ldiradigan NOT NULL ustunga sql`DEFAULT`.
• Kysely 0.29: migratsiya — kysely/migration; Windows'da o'z provider'ingiz (pathToFileURL).
• Knex: tipsiz, ?/??, whereRaw yopishtirish — 48 qator; migrate:make shabloni CJS; batch va rollback; "corrupt" papka himoyasi; seed idempotent, sequence emas.
• Tezlik farqi millisekund ichida — tanlov tip, migratsiya va jamoa bo'yicha.

Keyingi dars: TypeORM — dekoratorli klassik ORM, uning eski loyihalardagi o'rni va kanon topgan take + relation tuzog'i.

Manbalar

• Kysely hujjati — Getting started, Generating types: https://kysely.dev/docs/getting-started (2026-10-25)
• Kysely hujjati — Migrations: https://kysely.dev/docs/migrations
• kysely-codegen: https://github.com/RobinBlomberg/kysely-codegen
• Knex hujjati — Query builder, Raw, Transactions: https://knexjs.org/guide/query-builder.html
• Knex hujjati — Migrations va Seeds: https://knexjs.org/guide/migrations.html
• Chiqishlar — biz ishga tushirdik: PostgreSQL 17.6, kysely 0.29.6, kysely-codegen 0.20.0, knex 3.3.0, pg 8.23.1, TypeScript 6.0.3, Node 24.21.0, Windows 11 (2026-10-25/26); taqqoslash — kanon (PostgreSQL 18.6).