Comparing with Kysely

Kysely is a query builder, not an ORM, in a sense that by design it is not responsible for relations and other features that you can find in ORMs. Yet, it makes sense to compare OrchidORM with it because both libraries provide ability to construct complex queries, with a big respect to type-safety.

OrchidORM took inspiration from Kysely. Being not less flexible and type-safe than Kysely is one of the project goals. Not all features of Kysely are covered yet, for example, case-when SQL builder.

OrchidORM only supports PostgreSQL database, while Kysely supports many different ones.

Select

A single columnColumn with a tableMultiple columnsAliasesComplex selectionsNot nullFunction callsDistinctAll columnsAll columns of tableNested arrayNested object

Where

Simple where clauseWhere inWhere in with multiple columnsObject filterOr whereConditional where callsComplex where clause

Join

Simple inner joinAliased inner joinComplex joinSubquery join

Insert

Single rowMultiple rowsReturning dataComplex valuesInsert subquery

Update

Single rowComplex values

Delete

Single row

Transactions

Simple transaction

CTE

Simple selectsInserts, updates, and deletions

OrchidORM returns many records by default, so no need for execute().

You can use take, takeOptional for executeTakeFirstOrThrow and executeTakeFirst of Kysely.

There are also get for a single value, pluck for a flat array, and other options.

OrchidORM

1

2

3

4

5

6

7

8

import { db } from './tables';

const persons = await db.person

  .select('id')

  .where({ firstName: 'Arnold' })

SELECT
  "person"."id"
FROM
  "person"
WHERE
  "person"."firstName" = $1;
-- Parameters:
-- $1: "Arnold"

Kysely

1

2

3

4

5

6

7

8

import { db } from './tables';

const persons = await db

  .selectFrom('person')

  .select('id')

  .where('first_name', '=', 'Arnold')

  .execute()

select
  "id"
from
  "person"
where
  "first_name" = $1;
-- Parameters:
-- $1: "Arnold"

VIM mode