How to add "FETCH FIRST n ROWS ONLY" using Kysely DB query builder?

Question

My favorite DB toolkit for use with TypeScrypt-based projects is Prisma. However, one of the projects is using Kysely type-safe DB query builder.

I am trying to prepare a query that would handle keyset pagination using Kysely query builder, but I can't figure out how to add FETCH FIRST n ROWS ONLYinto the query if that is even possible.

Kysely docs: https://kysely.dev/docs/category/select

Any ideas or suggestions?

import { Kysely, sql } from "kysely";

const DB = new Kysely<Database>({...});

const query = DB
  .selectFrom("cl")
  .selectAll()
  .where(sql`(created_at, id)`, '>', `('${keyFromCreatedAt}', '${keyFromId}')`)
  // ¿¿¿ Something to insert FETCH FIRST n ROWS ONLY? ???
  ;

const res = query.execute();

Answer 1

First of all, you can use LIMIT clause (SelectQueryBuilder.limit(n)) as an alternative for FETCH FIRST n ROWS ONLY depending on the dialect you are using. If you have to use FETCH FIRST n ROWS ONLY, you should implement you own QueryCompiler.

It doesn't take a lot of time. Everything is already implemented in DefaultQueryCompiler. All you should do is extend it and override a single method. For example, PostgresQueryCompiler overrides single method from DefaultQueryCompiler.

export class PostgresQueryCompiler extends DefaultQueryCompiler {
  protected override sanitizeIdentifier(identifier: string): string {
    return identifier.replace(ID_WRAP_REGEX, '""')
  }
}

---

Defined your custom QueryCompiler and override visitLimit method to change the behavior of limit().

class MyQueryCompiler extends PostgresQueryCompiler {
  protected visitLimit(node: LimitNode) {
    this.append("fetch next ")
    this.visitNode(node.limit);
    this.append(" rows only")
  }
}

Then extends a Dialect class and override createQueryCompiler() method to use the QueryCompiler.

class MyDialect extends PostgresDialect {
  createQueryCompiler(): QueryCompiler {
    return new MyQueryCompiler();
  }
}

Pass it to Kysely constructor to use it.

const db = new Kysely<DB>({
  dialect:new MyDialect(...),
})

PostgresQueryCompiler and PostgresDialect are just examples. You can override any dialect you want.

Full example:

import {
  Kysely, LimitNode,
  PostgresDialect, PostgresQueryCompiler,
  QueryCompiler
} from "kysely";

class MyQueryCompiler extends PostgresQueryCompiler {
  protected visitLimit(node: LimitNode) {
    this.append("fetch next ")
    this.visitNode(node.limit);
    this.append(" rows only")
  }
}

class MyDialect extends PostgresDialect {
  createQueryCompiler(): QueryCompiler {
    return new MyQueryCompiler();
  }
}

const db = new Kysely<any>({
  dialect: new MyDialect({} as any),
})

const {sql} = db.selectFrom("table").select(["a", "b"]).limit(10).offset(20).compile()
console.log(sql) // select "a", "b" from "table" fetch next $1 rows only offset $2