@ts-awesome/rest-query-to-orm

TypeScript simple-query to ORM compiler middleware

It does what its name says.

Base use

import {ISimpleQuery} from "@ts-awesome/simple-query";
import {IOrderBy} from "@ts-awesome/rest-query";
import {compileWhereFor, compileOrderFor} from "@ts-awesome/rest-query-to-orm/dist/compiler";
import {filterable} from "./decorators";
import {IEntityService} from "@ts-awesome/orm";

@dbTable('some_table')
class Model {
  @dbField a: number;
  @dbField b: string;
}

const query: ISimpleQuery = {
  $and: [
    {a: 5},
    {$neq: {b: "test"}}
  ]
};

const orderBy: IOrderBy[] = [
  {a: 'DESC'}
];

// prepare filter
const whereClause = compileWhereFor(Model, query);
// prepare sorter
const orderByClause = compileOrderFor(Model, orderBy);

const compiled = Select(Model)
  .where(whereClause)
  .orderBy(orderByClause);

Use with @ts-awesome/rest

import {Route, httpGet, queryParam} from "@ts-awesome/rest";
import {WhereBuilder, OrderBuilder} from "@ts-awesome/orm";
import {QueryParserMiddlewareFor} from "@ts-awesome/rest-query-to-orm";
import {GetListQueryInput} from "./query-parser-middleware";

@httpGet('/test', QueryParserMiddlewareFor(Model))
export class TestRoute extends Route {
  @inject(GetListQuerySymbol)
  protected model!: GetListQueryInput<Model>

  @inject(SomeEntityServiceSymbol)
  protected entityService!: ISomeEntityService;

  async handle() {
    const baseQuery = this.entityService
      .select()
      // ensure visibility rules
      .where(x => x.ownerId.eq(this.autheUser.id));

    if (this.model.query) {
      baseQuery.where(this.model.query);
    }

    if (this.model.countOnly) {
      // return count
      return this.jsonAsync(baseQuery.count());
    }

    // include default order to ensure stable default ordering
    baseQuery.orderBy(model.orderBy ?? (['id'] as never))
    baseQuery.offset(model.offset ?? 0);
    baseQuery.limit(model.limit ?? 100);

    const results: readonly Model[] = await baseQuery.fetch();
  }
}

Use DescribeQueryParams a helper function to provide useful description via OpenApi schemas

Note: the middleware mutates the incoming query object, replacing raw query params with compiled builders (query, orderBy, offset, limit, countOnly).

Filterable model

There are cases when raw DB model has sensitive fields that should not be exposed for filtering and sorting. In such cases filterable model comes to rescue

import {filterable} from "@ts-awesome/rest-query-to-orm";

// works over DB model
@filterable(Model)
class FilterableModel {
  // plain filterable field, should match field in DB Model
  @filterable a?: number;
  @filterable('b') alias?: string;
  
  // foreign keys
  @filterable<Model>({
    match: 'b', // field in DB model,
    table: OtherModel,
    key: 'id', // field to match to
    value: 'name', // field to filter on 
  })
  author?: string;
}

Custom logic for filtering

@filterable also supports fully custom queries for each operation (eq, neq, gt, gte, lt, lte, in, contains, like)

import {exists, Select} from "@ts-awesome/orm";
import {filterable, Compiler} from "@ts-awesome/rest-query-to-orm";

const filterBuilder: Compiler<unknown> = (primary, op, value) => {
  return exists(Select(OtherModel).where(({authorId, name}) => and(
    authorId.eq(primary),
    name[op](value),
  )));
}

@filterable(Model)
class FilterableModel {
  @filterable<Model>({
    eq: filterBuilder,
    neq: filterBuilder,
  })
  authorName?: string;
}

Sortable model

@sortable mirrors @filterable but for order-by fields, including plain fields, relations, and custom query expressions.

import {sortable} from "@ts-awesome/rest-query-to-orm";

@sortable(Model)
class SortableModel {
  @sortable a?: number;
  @sortable('b') alias?: string;
}

Dependencies

• @ts-awesome/simple-query provides the ISimpleQuery types used by the compiler.
• Ensure reflect-metadata is loaded before using decorators.

License

May be freely distributed under the MIT license.

Copyright (c) 2022 Volodymyr Iatsyshyn and other contributors