QueryModel

Why Query Model

Do you have used the Object Relational Mapping (ORM) before? The ORM resolve the code model type with table defination in database. That would make the database operate easy with code.

But the problem is the simple table selection is enough? If we in a data project the ORM seems to be too simple for our needs. so we still need to write sqls in code, most time the sql would be a string that not easy to be review and reuse.

Also in typescript, the query with sql string may need data struct defination or use any type. that make the coding duplicate and boring.

So if we need to defined the return data, why not just defined the query with it. That is the query model. focuse on the query and return type, not the table in database.

How To

Install

npm install --save querymodel

HOW TO USE

Defined The Model With Query

• We could use querymodel like ORM to defined the simple query of a table

import {Column, Entity} from 'querymodel';

@Entity('user')
export class User {
  @Column()
  id: number;

  @Column('first_name')
  firstName: string;

  @Column('last_name')
  lastName: string;

  @Column()
  age: number;
}

Entity is the simple selection with select ... from ${table_name}

Column is the colum as select ${column_name} as ${filedName} ...

• But we may need to do some selection

import {Column, Entity, Select, concat} from 'querymodel';

@Entity('user')
export class User {
  @Column()
  id: number;

  @Column('first_name')
  firstName: string;

  @Column('last_name')
  lastName: string;
  
  @Select(concat('fist_name', '\' \'', 'last_name'))
  fullName: string;

  @Column()
  age: number;
}

Select allow to return new field.

• Or we use it as a totoally query

import {Query, SelectQueryBuilder,  Select, coalesce} from 'querymodel';

@Query((qb: SelectQueryBuilder) => {
  return qb.from(Order, 'order')
  .leftJoin(Payment, 'payment', {
    'payment.order_id': 'order.id'
  })
  .leftJoin(Transfer, 'trans', {
    'trans.order_id': 'order.id'
  })
})
export class OrderWithStatus {
  @Select('order.id')
  order_id: number;

  @Select('payment.status')
  payment_status: string;

  @Select('transfer.status')
  transfer_status: string;

  @Select(coalesce('trans.update_at', 'payment.update_at', 'order.update_at'))
  updateAt: Date;
}

that a commmon sence that we need to join two or more tabel to fetch the data back in one sql. Use Query decorator to defined how the data composed, and use Select to defined how the fields to be computed.

Also The from and join would be a simpel table or a defined query. Then you could defined the common query would be used in multi query and resue it in the defination of multi query models.

Query With DataSource

• First add the datasource.

export class MySql extends DataSource {
  constructor() {
    super();
    this.sourceType = 'MySql';
    this.runner = new SqlRunner();
  }
}

• Implement the Runner

export class SqlRunner implements IQueryRunner {
  query<T>(builder: SelectQueryBuilder): Promise<T[]> {
    //the select query executed here
  }

  execute(builder: DeleteQueryBuilder|InsertQueryBuilder|UpdateQueryBuilder): Promise<ExecuteResult> {
    // delete, insert, update query executed here
  }
}

• Add your bussiness query code

const source = new MySql();
const user = await source.getOne(User);
const orders = await source.getMany(OrderWithStatus, {status: IN('waiting', 'paying'), userId: user.id});

EXAMPLES

view the model defined in tests/models and tests/models/queries

SUPPORT

mail to jaxzhou@hotmail.com

LICENSE

MIT