typeorm-linq

typeorm-linq is a library that support linq base on typeorm SelectQueryBuilder. SqlServer,Postgres and MySql is supported. Oracle is on-going.

Install

• install typeorm

npm install typeorm --save

• install typeorm-linq

npm install typeorm-linq --save

Usage

 //init config when code start
 init({dbType:'postgres'});
 //query db by QueryBuilder
 const query =await new LinqInferQueryBuilder<SchoolEntity>(dataSource)
      .create(SchoolEntity, 'sc')
      .innerJoinAndSelect(
        ClassesEntity,
        'cla',
        ({ sc, cla }) => sc.id == cla.schoolId,
      )
      .leftJoinAndSelect(
        StudentEntity,
        'stu',
        ({ stu, cla }) => stu.classesId == cla.id,
      )
      .leftJoinAndSelect(
        TeacherEntity,
        'te',
        ({ cla, te }) => cla.headTeacherId == te.id,
      )
      .where(
        ({ cla, stu }) =>
          (!stu.isMale && cla.name == '8') || stu.isMonitor == true,
      )
      .andWhere(({ sc }) => sc.id == schoolFilter.id, {
        schoolFilterid: schoolFilter.id, //just remove '.' as name
      })
      .orderBy(({ sc }) => sc.name, 'DESC')
      .select(({ sc, cla, stu, te }) => {
        schoolName: sc.name;
        className: cla.name;
        headTeacher: te.firstName + ' ' + te.lastName;
        stuName: stu.firstName + ' ' + stu.lastName;
      })
      .getRawMany<{
        schoolName: string;
        className: string;
        headTeacher: string;
        stuName: string;
      }>();

Feature

• Type Inference typeorm-linq can dynamic infer function type. eg:cla and stu is a entity alias,but in where function,the types of cla and stu are ClassesEntity and StudentEntity

  //
  where(
    ({ cla, stu }) => (!stu.isMale && cla.name == '8') || stu.isMonitor == true,
  );

• Cache:Arrow Function will be complied sql string when it is invoked for the first time.The default cache store is ExpressionCacheMemoryStore.
  • ExpressionCacheMemoryStore(default)
  • ExpressionCacheFileStore
  • ExpressionCacheStore(custom store base class)
• Support one to many for the relation of alias and entity

  queryBuilder
    .create(ClassesEntity, 'cla1')
    .innerJoinAndSelect(
      ClassesEntity,
      'cla2',
      ({ cla1, cla2 }) => sc.id == cla.parentId,
    );

• Support join with relations

const query =
  (await new LinqInferQueryBuilder()) <
  StudentEntity >
  dataSource
    .create(StudentEntity, 'student')
    .innerJoinAndSelect(
      ClassesEntity,
      'classes1',
      null,
      ({ student }) => student.classes,
      null,
    )
    .getMany();

• Support variable
  • Object:the max deep level is 2.eg:a.b

    //param label just remove '.' and connect to camelCase after 1.1.0
    const school = { id: 2 };
    queryBuilder
      .create(SchoolEntity, 'sc')
      .where(({ sc }) => sc.id == school.id, {
        schoolId: school.id,
      });

  • Basic Type

    const id = 2;
    queryBuilder.create(SchoolEntity, 'sc').where(({ sc }) => sc.id == id, {
      id,
    });

  • Array:must be end with Array

    const idArray = [2, 3];
    queryBuilder
      .create(SchoolEntity, 'sc')
      .where(({ sc }) => idArray.includes(sc.id), {
        id,
      });

  • constant

    queryBuilder.create(SchoolEntity, 'sc').where(({ sc }) => sc.id == 5);

• Support JS Syntax
  • Array.includes must be end with Array

    const idArray = [2, 3];
    queryBuilder
      .create(SchoolEntity, 'sc')
      .where(({ sc }) => idArray.includes(sc.id), {
        idArray,
      });

  • string.includes/string.startsWith/string.endsWith

  const test = 'test';
  queryBuilder
    .create(SchoolEntity, 'sc')
    .where(({ sc }) => sc.name.includes(test), {
      test: OperatorConvertMapping.currentConvert.like(test),
    });

  • (), ==、===、!==、!=、||、&&

    const test = 'test';
    const test2 = 'test2';
    const idsArray = [2, 4, 5, 7];
    queryBuilder
      .create(SchoolEntity, 'sc')
      .where(
        ({ sc }) =>
          sc.name.includes(test) ||
          (sc.name.includes(test2) && idArray.includes(sc.id)),
        {
          test,
          test2,
          idsArray,
        },
      );

  • constant

    //case1
    queryBuilder.create(SchoolEntity, 'sc').where(({ sc }) => sc.id == 5);
    //case2
    queryBuilder
      .create(SchoolEntity, 'sc')
      .where(({ sc }) => sc.isDeleted == true);

  • unary operator

    queryBuilder.create(SchoolEntity, 'sc').where(({ sc }) => !sc.isDeleted);

  • + operator

    queryBuilder.create(TeacherEntity, 'te').select(({ te }) => {
      name: te.firstName + ' ' + te.lastName;
    });

  • >,<,>=,<=

    queryBuilder
      .create(TeacherEntity, 'te')
      .where(({ cla, stu }) => ExpressionAggregateFunc.len(cla.name) >= 3);

  • Aggregate Function:ExpressionAggregateFunc

    //subQuery
    ExpressionAggregateFunc.subQuery(
      'case when a.name is null then "test" else a.name',
    );
    //len
    queryBuilder
      .create(SchoolEntity, 'sc')
      .where(({ sc }) => ExpressionAggregateFunc.len(sc.name));

  • complex use

    queryBuilder.create(TeacherEntity, 'te').select(({ te }) => {
      name: ExpressionAggregateFunc.len(te.firstName + ' ' + te.lastName);
    });

typeorm other feature

• transaction support

  queryBuilder.transaction(
    TeacherEntity,
    'te',
    async (manager, queryBuilder) => {
      //if you need ,you can use queryBuilder to select some data.
      //business code
    },
  );

• from

  new LinqInferQueryBuilder() <
    SchoolEntity >
    dataSource
      .create()
      .from((qb: LinqInferQueryBuilder<TeacherEntity>) => {
        //TeacherEntity as the return type,it isn't necessary
        const subQuery = qb
          .from(TeacherEntity, 'subTech')
          .where(({ subTech }) => subTech.id == 1);
        return subQuery;
      }, 'from1')
      .where(({ from1 }) => from1.firstName == 'test');

  • select/addSelect

  (await new LinqInferQueryBuilder()) <
    StudentEntity >
    dataSource
      .create(StudentEntity, 'student1')
      .leftJoinAndSelect(
        ClassesEntity,
        'classes1',
        null,
        ({ student1 }) => student1.classes,
        null,
      )
      .select([
        ({ student1 }) => student1.id,
        ({ student1 }) => student1.firstName,
        ({ classes1 }) => classes1.id,
        ({ classes1 }) => classes1.name,
      ])
      .addSelect([
        ({ student1 }) => student1.address,
        ({ classes1 }) => classes1.schoolId,
      ])
      .getMany();

OnGoing

• support Oracle

Besides

The Reason of choosing Object Type as Arrow Function Params instead of Tuple is below: Tuple just can infer type,but cannot infer variable name.