Kysely ORM

README.md

Overview

This library provides an API similar to drizzle's relations so that kysely can be used as an ORM.

Usage

interface Database {
  users: { id: number; name: string };
  posts: { id: number; user_id: number; title: string };
  profiles: { id: number; user_id: number; bio: string };
  tags: { id: number; name: string };
  post_tags: { post_id: number; tag_id: number };
}

// Create a relation builder for your database schema
const relations = createRelationsBuilder<Database>();

// Define reusable relations for the users table
const userRelations = relations('users', ({ hasOne, hasMany }) => ({
  // hasOne: nullable one-to-one relation
  profile: hasOne('profile', {
    target: 'profiles',           // target table
    column: 'users.id',           // source column
    reference: 'profiles.user_id' // foreign key
  }),
  
  // hasMany: one-to-many with optional query customization
  americanPosts: hasMany('americanPosts', {
    target: 'posts',
    column: 'users.id',
    reference: 'posts.user_id'
  }, (qb) => qb.where('title', 'like', 'America%'))
}));

// Define relations for posts table
const postRelations = relations('posts', ({ hasManyThrough }) => ({
  // hasManyThrough: many-to-many via junction table
  tags: hasManyThrough('tags', {
    target: 'tags',
    column: 'posts.id',
    reference: 'tags.id',
    through: 'post_tags',
    throughSourceKey: 'post_tags.post_id',
    throughTargetKey: 'post_tags.tag_id'
  })
}));

// Use relations in queries - pass a query builder to specify selected fields
// Returns: Promise<{ id: number; profile: { id: number; bio: string } | null }[]>
db.selectFrom('users')
  .select(eb => [
    'id',
    userRelations.profile(qb => qb.select(['id', 'bio']))
  ])
  .execute();

// Nested relations: select posts with their tags
// Returns: Promise<{ id: number; posts: { title: string; tags: { name: string }[] }[] }[]>
db.selectFrom('users')
  .select(eb => [
    'id',
    userRelations.americanPosts(qb => qb.select([
      'title',
      postRelations.tags(tagQb => tagQb.select('name'))
    ]))
  ])
  .execute();

// Type error: userRelations only works with 'users' table
db.selectFrom('profiles')
  .select(eb => userRelations.profile(qb => qb.select('id')))
  .execute();

Download

You can download the helpers into your project by running the following command:

mkdir -p relations-builder \
  && curl -o relations-builder/index.js https://gist.githubusercontent.com/alexander-azizi-martin/4d81db00708c502776618e4e756b747c/raw/51da78dee1086467a475c2a8f38490c42cecf8f1/relations-builder.js \
  && curl -o relations-builder/index.d.ts https://gist.githubusercontent.com/alexander-azizi-martin/4d81db00708c502776618e4e756b747c/raw/51da78dee1086467a475c2a8f38490c42cecf8f1/relations-builder.d.ts

relations-builder.d.ts

import {
  ColumnType,
  type AliasedExpression,
  type AnyColumnWithTable,
  type Expression,
  type ExpressionBuilder,
  type SelectQueryBuilder,
} from "kysely";

/* eslint-disable @typescript-eslint/no-empty-object-type */

interface RelationConfig<
  DB,
  Table extends keyof DB & string,
  Target extends keyof DB & string,
  Column extends keyof DB[Table] & string,
> {
  target: Target;
  column: `${Table}.${Column}`;
  reference: AnyColumnWithTable<DB, Target>;
}

interface ThroughRelationConfig<
  DB,
  Table extends keyof DB & string,
  Through extends keyof DB & string,
  Target extends keyof DB & string,
  Column extends keyof DB[Table] & string,
> extends RelationConfig<DB, Table, Target, Column> {
  through: Through;
  throughColumn: AnyColumnWithTable<DB, Through>;
  throughReference: AnyColumnWithTable<DB, Through>;
}

type CustomizeQueryFunction<DB, Target extends keyof DB & string> = (
  eb: SelectQueryBuilder<DB, Target, {}>
) => SelectQueryBuilder<DB, Target, {}>;

type SelectFunction<DB, Target extends keyof DB & string, T> = (
  eb: SelectQueryBuilder<DB, Target, {}>
) => SelectQueryBuilder<DB, Target, T>;

type AliasedExpressionFactory<
  DB,
  Table extends keyof DB & string,
  T,
  RelationName extends string,
> = (eb: ExpressionBuilder<DB, Table>) => AliasedExpression<T, RelationName>;

type RelationFunctions<DB, Table extends keyof DB & string> = {
  hasOne: <
    RelationName extends string,
    Target extends keyof DB & string,
    Column extends keyof DB[Table] & string,
  >(
    relationName: RelationName,
    config: RelationConfig<DB, Table, Target, Column>,
    customizeQuery?: CustomizeQueryFunction<DB, Target>
  ) => <T>(
      selectFunc: SelectFunction<DB, Target, T>
    ) => AliasedExpressionFactory<DB, Table, T | null, RelationName>;
  hasOneNotNull: <
    RelationName extends string,
    Target extends keyof DB & string,
    Column extends keyof DB[Table] & string,
  >(
    relationName: RelationName,
    config: RelationConfig<DB, Table, Target, Column>,
    customizeQuery?: CustomizeQueryFunction<DB, Target>
  ) => <T>(
      selectFunc: SelectFunction<DB, Target, T>
    ) => AliasedExpressionFactory<DB, Table, T, RelationName>;
  hasMany: <
    RelationName extends string,
    Target extends keyof DB & string,
    Column extends keyof DB[Table] & string,
  >(
    relationName: RelationName,
    config: RelationConfig<DB, Table, Target, Column>,
    configureQuery?: CustomizeQueryFunction<DB, Target>
  ) => <T>(
      selectFunc: SelectFunction<DB, Target, T>
    ) => AliasedExpressionFactory<DB, Table, T[], RelationName>;
  hasManyThrough: <
    RelationName extends string,
    Target extends keyof DB & string,
    Through extends keyof DB & string,
    Column extends keyof DB[Table] & string,
  >(
    relationName: RelationName,
    config: ThroughRelationConfig<DB, Table, Through, Target, Column>,
    configureQuery?: CustomizeQueryFunction<DB, Target>
  ) => <T>(
      selectFunction: SelectFunction<DB, Target, T>
    ) => AliasedExpressionFactory<DB, Table, T[], RelationName>;
};

declare function createRelationsBuilder<DB>(): <
  Table extends keyof DB & string,
  T,
>(
  table: Table,
  relationBuilder: (relationFunctions: RelationFunctions<DB, Table>) => T
) => T;

export { createRelationsBuilder };

relations-builder.js

import { expressionBuilder, sql } from "kysely";
import { jsonArrayFrom, jsonObjectFrom } from "kysely/helpers/postgres";

export function createRelationsBuilder() {
  return function relations(table, relationBuilder) {
    return relationBuilder({ hasOne, hasOneNotNull, hasMany, hasManyThrough });
  };
}

function hasOne(relationName, config, customizeQuery) {
  const query = relationQueryFactory(config, customizeQuery);
  const queryWithColumn = query(sql.ref(config.column));
  const include = (selectFunc) => {
    return () => jsonObjectFrom(selectFunc(queryWithColumn)).as(relationName);
  };

  return include;
}

function hasOneNotNull(relationName, config, customizeQuery) {
  const query = relationQueryFactory(config, customizeQuery);
  const queryWithColumn = query(sql.ref(config.column));
  const include = (selectFunc) => {
    return () => jsonObjectFrom(selectFunc(queryWithColumn)).as(relationName);
  };

  return include;
}

function hasMany(relationName, config, configureQuery) {
  const query = relationQueryFactory(config, configureQuery);
  const queryWithColumn = query(sql.ref(config.column));
  const include = (selectFunc) => {
    return () => jsonArrayFrom(selectFunc(queryWithColumn)).as(relationName);
  };

  return include;
}

function hasManyThrough(relationName, config, configureQuery) {
  const query = throughRelationQueryFactory(config, configureQuery);
  const queryWithColumn = query(sql.ref(config.column));
  const include = (selectFunc) => {
    return () => jsonArrayFrom(selectFunc(queryWithColumn)).as(relationName);
  };

  return include;
}

function relationQueryFactory(config, customizeQuery) {
  customizeQuery = customizeQuery ?? ((v) => v);
  const baseQuery = customizeQuery(
    expressionBuilder().selectFrom(config.target)
  );

  return (expression) => baseQuery.where(config.reference, "=", expression);
}

function throughRelationQueryFactory(config, customizeQuery) {
  customizeQuery = customizeQuery ?? ((v) => v);
  const baseQuery = customizeQuery(
    expressionBuilder().selectFrom(config.target)
  );
  const baseSubQuery = expressionBuilder()
    .selectFrom(config.through)
    .select(config.throughReference);

  return (expression) =>
    baseQuery.where(config.reference, "in", () =>
      baseSubQuery.where(config.throughColumn, "=", expression)
    );
}