---
# System prepended metadata

title: A Database Query Building Approach

---

# A Database Query Building Approach: Device Manager Services as Examples

## Outline

- data retrieving methods (using the traditional approach)
    - naming and specs
    - pros & cons
- Inspirations
    - query builder
    - spcification pattern
- Database query building approach
    - custom query builder
- Discussions

## Data Retrieving Methods (Using the Traditional Approach)

### Naming and Specs
- find and count methods (in a repository)
    - one target finding methods
        - .findOne-()
    - many targets finding methods
        - .findMany-()
    - targets counting methods
        - .count-()

    ```typescript=
    class TargetRepository {
      // ....

      async findOneTargetByColumnA(
        columnA: string
      ): Promise<TargetEntity> {
        const data = await targetModel.findOne({
          where: {
            columnA: { [Op.eq]: columnA },
          },
        });

        return EntityMap.toTargetEntity(data);
      }

      async findOneTargetByColumnAAndColumnB(
        columnA: string,
        columnB: number,
      ): Promise<TargetEntity> {
        const data = await targetModel.findOne({
          where: {
            columnA: { [Op.eq]: columnA },
            columnB: { [Op.gt]: columnB },
          },
        });

        return EntityMap.toTargetEntity(data);
      }

      // .... other find one methods .... //

      async findManyTargetsByColumnA(
        columnA: string[],
      ): Promise<TargetEntity[]> {
        const data = await targetModel.findAll({
          where: {
            columnA: { [Op.in]: columnA },
          },
          order,
          offset,
          limit,
        });

        return data.map(EntityMap.toTargetEntity);
      }

      async findManyTargetsByColumnAAndColumnB(
        columnA: string[],
        columnB: number,
      ): Promise<TargetEntity[]> {
        const data = await targetModel.findAll({
          where: {
            columnA: { [Op.notIn]: columnA },
            columnB: { [Op.gte]: columnB },
          },
          order,
          offset,
          limit,
        });

        return data.map(EntityMap.toTargetEntity);
      }

      // .... other find many methods .... //

      async countTargetsByColumnA(
        columnA: string[],
      ): Promise<number> {
        return targetModel.count({
          where: {
            columnA: { [Op.in]: columnA },
          },
        });
      }

      async countTargetsByColumnAAndColumnB(
        columnA: string[],
        columnB: number,
      ): Promise<number> {
        return targetModel.count({
          where: {
            columnA: { [Op.notIn]: columnA },
            columnB: { [Op.lt]: columnB },
          },
        });
      }

      // .... other count methods .... //
    }
    ```

- example
    > a repository with single device finding methods, multiple devices finding methods, and device counting methods

    ```typescript=
    class GroupRepository {
      // ....

      async findOneGroupByDeviceId(
        deviceId: string,
      ): Promise<GroupEntity> {
        const data = await groupModel.findOne({
          where: {
            deviceId: { [Op.eq]: deviceId }
          },
        });

        return EntityMap.toGroupEntity(data);
      }

      async findOneGroupByEntityIdAndName(
        entityId: string,
        name: string,
      ): Promise<GroupEntity> {
        const data = await groupModel.findOne({
          where: {
            entityId: { [Op.eq]: entityId },
            name: { [Op.eq]: name },
          },
        });

        return EntityMap.toGroupEntity(data);
      }

      // .... other find one methods .... //

      async findManyGroupsByEntityId(
        entityId: string,
      ): Promise<GroupEntity[]> {
        const data = await groupModel.findAll({
          where: {
            entityId: { [Op.eq]: entityId },
          },
          order: 'createTime',
          offset: 0,
          limit: 100,
        });

        return data.map(EntityMap.toGroupEntity);
      }

      async findManyGroupsByEntityIdAndDepth(
        entityId: string,
        depth: number,
      ): Promise<GroupEntity[]> {
        const data = await groupModel.findAll({
          where: {
            entityId: { [Op.eq]: entityId },
            depth: { [Op.gte]: depth },
          },
          order: '-modifyTime',
          offset: 0,
          limit: 10,
        });

        return data.map(EntityMap.toGroupEntity);
      }

      // .... other find many methods .... //

      async countGroupsByEntityId(
        entityId: string,
      ): Promise<number> {
        return groupModel.count({
          where: {
            entityId: { [Op.eq]: entityId },
          },
        });
      }

      async countGroupsByEntityIdAndDepth(
        entityId: string,
        depth: number,
      ): Promise<number> {
        return groupModel.count({
          where: {
            entityId: { [Op.eq]: entityId },
            depth: { [Op.lt]: depth },
          },
        });
      }

      // .... other count methods .... //
    }
    ```

### Pros & Cons
- pros
    - simple and intuitive syntax
        - fast delivery
- cons
    - duplicate or similar lines of code
        - high repetition
    - method naming without complete semantics
        - poor understandability
    - complex or trivial logic
        - low readability and maintainability


## Inspirations

### Query Builder
<!-- sample code -->

- Query Builder in Laravel [ref](https://laravel.com/docs/11.x/queries)

![query-builder-in-laravel](https://hackmd.io/_uploads/HkcfFCwIR.jpg)



### Specification Pattern

- Specifications in Spring Data JPA [ref](https://docs.spring.io/spring-data/jpa/reference/jpa/specifications.html)

![specification-spring-data-jpa](https://hackmd.io/_uploads/rJudT2DIR.jpg)


<!-- https://docs.spring.io/spring-data/jpa/reference/jpa/specifications.html -->


## Database Query Building Approach

### Custom Query Builder

- to consider and deal with
    - table columns
    - table relations
    - pagination

    ```typescript=
    class CustomQueryBuilder {
      toWhereOptions() WhereOptions {}

      toOrder(): Literal {}

      toLimit(): number {}

      toOffset(): number {}
    }
    ```

- to make data retrieving methods more general (widely used)
    - one target finding method
        - .findOne-ByQueryBuilder()
    - many targets finding method
        - .findMany-ByQueryBuilder()
    - targets counting method
        - .count-ByQueryBuilder()

    ```typescript=
    class TargetRepository {
      async findOneTargetByQueryBuilder(
        queryBuilder: CustomQueryBuilder,
      ): Promise<TargetEntity> {
        const data = await targetModel.findOne({
          where: queryBuilder.toWhereOptions(),
        });

        return EntityMap.toTargetEntity(data);
      }

      async findManyTargetsByQueryBuilder(
        queryBuilder: CustomQueryBuilder,
      ): Promise<TargetEntity[]> {
        const data = await targetModel.findAll({
          where: queryBuilder.toWhereOptions(),
          order: queryBuilder.toOrder(),
          offset: queryBuilder.toOffset(),
          limit: queryBuilder.toLimit(),
        });

        return data.map(EntityMap.toTargetEntity);
      }

      async countTargetsByQueryBuilder(
        queryBuilder: CustomQueryBuilder,
      ): Promise<number> {
        return this.targetModel.count({
          where: queryBuilder.toWhereOptions(),
        });
      }  
    }
    ```

- example
    > query devices using custom query builder

    ```typescript=
    class DeviceRepository {
      async findOneDeviceByQueryBuilder(queryBuilder: DeviceQueryBuilder) {
        const data = await deviceModel.findOne({
          where: queryBuilder.toWhereOptions(),
        });

        return EntityMap.toDeviceEntity(data);
      }

      async findManyDevicesByQueryBuilder(queryBuilder: DeviceQueryBuilder) {
        const data = await deviceModel.findAll({
          where: queryBuilder.toWhereOptions(),
          order: queryBuilder.toOrder(),
          offset: queryBuilder.toOffset(),
          limit: queryBuilder.toLimit(),
        });

        return data.map(EntityMap.toDeviceEntity);
      }

      async countDevicesByQueryBuilder(queryBuilder: DeviceQueryBuilder) {
        return deviceModel.count({
          where: queryBuilder.toWhereOptions(),
        });
      }  
    }
    ```

### Table Columns in Custom Query Builder

- set criteria
    - UUID string
        - eq-, ne-, in-, notIn-, ....
    - non-UUID string
        - eq-, ne-, in-, notIn-, iLike-, ....
    - number
        - eq-, ne-, lt-, lte-, gt-, gte-, ....

    ```typescript=
    class CustomQueryBuilder {
      // columnA: string // UUID
      eqColumnA: string;
      neColumnA: string;
      inColumnA: string[];
      notInColumnA: string[];

      // columnB: string // non-UUID
      eqColumnB: string;
      iLikeColumnB: string;

      // columnC: number // integer, float
      eqColumnC: number;
      neColumnC: number;
      ltColumnC: number;
      lteColumnC: number;
      gtColumnC: number;
      gteColumnC: number;

      // ....
    } 
    ```

- generate predicate
    - .toWhereOptions() method
    
    ```typescript=
    class CustomQueryBuilder {
      // ....

      toWhereOptions(): WhereOptions {
        const whereOptions: WhereOptions<ModelAttributes> = {};

        if (this.eqColumnA) {
          whereOptions.columnA = { [Op.eq]: this.eqColumnA };
        } else if (this.inColumnA) {
          whereOptions.columnA = { [Op.in]: this.inColumnA };
        }

        if (this.eqColumnB) {
          whereOptions.columnB = { [Op.eq]: this.eqName };
        } else if (this.iLikeColumnB) {
          whereOptions.columnB = { [Op.iLike]: `%${this.iLikeColumnB}%` };
        } else if (this.inColumnB) {
          whereOptions.columnB = { [Op.in]: this.inColumnB };
        }

        if (Number.isInteger(this.eqColumnC)) {
          whereOptions.columnC = { [Op.eq]: this.eqColumnC };
        } else if (this.neColumnC) {
          whereOptions.columnC = { [Op.ne]: this.neColumnC };
        } else if (this.lteColumnC) {
          whereOptions.columnC = { [Op.lte]: this.lteColumnC };
        } else if (this.gtColumnC) {
          whereOptions.columnC = { [Op.gt]: this.gtColumnC };
        }

        return whereOptions;
      }
    }
    ```

### Pagination in Custom Query Builder

- set criteria
    - sort
        - type: string
        - sort by [column] in [ascending/descending] order
            > e.g.
                sort by createTime in ascending order: 'createTime'
                sort by depth in descending order: '-depth'
    - count
        - type: number
        - page size
    - page
        - type: number
        - page index, 0-indexed

    ```typescript=
    class PaginationQueryBuilder {
      sort: string;
      count: number;
      page: number;

      // ....
    }
    ```

- generate predicate
    - .toOrder() method
    - .toOffset() method
    - .toLimit() method
    
    ```typescript=
    class PaginationQueryBuilder {
      sort: string;
      count: number;
      page: number;

      toOrder(): Literal {
        if (this.sort) {
          const field = this.sort.startsWith("-")
            ? this.sort.slice(1)
            : this.sort;
          const order = this.sort.startsWith("-")
            ? "DESC"
            : "ASC";

          return literal(`${field} ${order}`);
        }
        return null;
      }

      toLimit(): number {
        const limit = this.count;
        return Number.isInteger(limit) ? limit : null;
      }

      toOffset(): number {
        const offset = this.count * this.page;
        return Number.isInteger(offset) ? offset : null;
      }
    }
    ```

- example 
    > [use case]
        find folders of a given entity and in depth no less than 1
        sort folders by depth in descending order
        skip the first (10 * 2) ones
        retrieve the next 10 ones

    ```typescript=
    // ....
    class PaginationQueryBuilder {
      sort: string;
      count: number;
      page: number;

      // ....
    }

    class FolderQueryBuilder extends PaginationQueryBuilder {
      eqEntityId: string;
      gteDepth: string;

      // ....

      toWhereOptions() {
        if (this.eqEntityId) {
          whereOptions.entityId = { [Op.eq]: this.eqEntityId };
        }

        if (this.gteDepth) {
          whereOptions.depth = { [Op.gte]: this.gtDepth };
        }
      }
    }

    const queryBuilder = new FolderQueryBuilder();
    queryBuilder.entityId = targetEntityId;
    queryBuilder.gteDepth = 1;
    queryBuilder.sort = '-depth';
    queryBuilder.count = 10;
    queryBuilder.page = 2;

    const folderEntities = await folderRepository.findManyFoldersByQueryBuilder(
      queryBuilder
    );
    ```

## Discussions

- Is custom query builder a MUST?
    - No, use it if needed. Decisions are yours.
- Can we use traditional data retrieving approach after custom query builder is applied? 
    - Yes, use the traditional one when the extremely complex queries are rarely used.

<!--

https://refactoring.guru/design-patterns/builder

https://docs.spring.io/spring-data/jpa/reference/jpa/specifications.html

https://medium.com/@pawel_klimek/domain-driven-design-specification-pattern-82867540305c

https://hackmd.io/c/codimd-documentation/%2F%40codimd%2Fmarkdown-syntax

-->