38-Nodejs Course 2023: Query Builder
Tags: node, typescript, mongodb, fastify
Published: 2022-11-05 08:54:15

---
title: 38-Nodejs Course 2023: Query Builder
published: true
description: 
series: Nodejs Course 2023
tags: nodejs, typescript, mongodb, fastify
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/huhvmhei2qyeq3oektvv.png
# Use a ratio of 100:42 for best results.
---

So far so good, we updated our base code of the model and made a good space in our `Model` class, but we forgot to update some types in the previous article, which are the data that are being added, and  the filter options, just open the `crud-model` file and search for this string `Record<string, any>` you'll find it is duplicated like **12-13** times, let's make a type for the data that will be added and the filter options.

```ts
// src/core/database/types.ts
import { ObjectId, WithId } from "mongodb";
// ...

/**
 * Filter object
 */
export type Filter = Record<string, any>;

/**
 * Document data will be used in insertion, updates or replace
 */
export type Document = Record<string, any>;

/**
 * Model Document that contains the model with both mongodb _id and our custom id
 */
export type ModelDocument = WithId<{
  id: number;
  [key: string]: any;
}>;
```

We added here three types, the first one is for filters options, the second one is for the data that will be added or updated or replaced, and the third one is for the model document, which contains the model with both mongodb `_id` and our custom `id`.

The `Document` type is just an object that can contain any type of data.

The `ModelDocument` is a `Document` with MongoDb `_id` column and `id` column as well.

Now let's update it in the `crud-model` file.

```ts
// src/core/database/crud-model.ts
import { ObjectId } from "mongodb";
import BaseModel from "./bae-model";
import {
  ChildModel,
  Document,
  Filter,
  ModelDocument,
  PaginationListing,
  PrimaryIdType,
} from "./types";

export default abstract class CrudModel extends BaseModel {
  /**
   * Create a new record in the database for the current model (child class of this one)
   * and return a new instance of it with the created data and the new generated id
   */
  public static async create<T>(
    this: ChildModel<T>,
    // 👇🏻 Use the Document type here
    data: Document,
  ): Promise<T> {
    // 1- get the query of the collection
    const query = this.query();

    const modelData = { ...data };

    modelData.id = await this.generateNextId();

    // perform the insertion
    const result = await query.insertOne(modelData);

    modelData._id = result.insertedId;

    // 👇🏻 Cast the return data type as Model Document
    return this.self(modelData as ModelDocument);
  }

  /**
   * Update model by the given id
   */
  public static async update<T>(
    this: ChildModel<T>,
    id: PrimaryIdType,
    // 👇🏻 Use the Document type here
    data: Document,
  ): Promise<T> {
    // get the query of the current collection
    const query = this.query();

    // execute the update operation

    const filter = {
      [this.primaryIdColumn]: id,
    };

    const result = await query.findOneAndUpdate(
      filter,
      {
        $set: data,
      },
      {
        returnDocument: "after",
      },
    );

    // 👇🏻 Cast the return data type as Model Document
    return this.self(result.value as ModelDocument);
  }

  /**
   * Replace the entire document for the given document id with the given new data
   */
  public static async replace<T>(
    this: ChildModel<T>,
    id: PrimaryIdType,
    // 👇🏻 Use the Document type here
    data: Document,
  ): Promise<T> {
    const query = this.query();

    const filter = {
      [this.primaryIdColumn]: id,
    };

    const result = await query.findOneAndReplace(filter, data, {
      returnDocument: "after",
    });

    // 👇🏻 Cast the return data type as Model Document
    return this.self(result.value as ModelDocument);
  }

  /**
   * Find and update the document for the given filter with the given data or create a new document/record
   * if filter has no matching
   */
  public static async upsert<T>(
    this: ChildModel<T>,
    // 👇🏻 Use the filter type here
    filter: Filter,
    // 👇🏻 Use the Document type here
    data: Document,
  ): Promise<T> {
    // get the query of the current collection
    const query = this.query();

    // execute the update operation
    const result = await query.findOneAndUpdate(
      filter,
      {
        $set: data,
      },
      {
        returnDocument: "after",
        upsert: true,
      },
    );

    // 👇🏻 Cast the return data type as Model Document
    return this.self(result.value as ModelDocument);
  }

  /**
   * Find document by the given column and value
   */
  public static async findBy<T>(
    this: ChildModel<T>,
    column: string,
    value: any,
  ): Promise<T | null> {
    const query = this.query();

    const result = await query.findOne({
      [column]: value,
    });

    // 👇🏻 Cast the return data type as Model Document
    return result ? this.self(result as ModelDocument) : null;
  }

  /**
   * List multiple documents based on the given filter
   */
  public static async list<T>(
    this: ChildModel<T>,
    // 👇🏻 Use the filter type here
    filter: Filter = {},
  ): Promise<T[]> {
    const query = this.query();

    const documents = await query.find(filter).toArray();

    // 👇🏻 Cast the return data type as Model Document
    return documents.map(document => this.self(document as ModelDocument));
  }

  /**
   * Paginate records based on the given filter
   */
  public static async paginate<T>(
    this: ChildModel<T>,
    // 👇🏻 Use the filter type here
    filter: Filter,
    page: number,
    limit: number,
  ): Promise<PaginationListing<T>> {
    const query = this.query();

    const documents = await query
      .find(filter)
      .skip((page - 1) * limit)
      .limit(limit)
      .toArray();

    const totalDocumentsOfFilter = await query.countDocuments(filter);

    const result: PaginationListing<T> = {
      documents: documents.map(document =>
        this.self(document as ModelDocument),
      ),
      paginationInfo: {
        limit,
        page,
        result: documents.length,
        total: totalDocumentsOfFilter,
        pages: Math.ceil(totalDocumentsOfFilter / limit),
      },
    };

    return result;
  }

  /**
   * Delete single document if the given filter is an ObjectId of mongodb
   * Otherwise, delete multiple documents based on the given filter object
   */
  public static async delete<T>(
    this: ChildModel<T>,
    // 👇🏻 Use the filter type here
    filter: PrimaryIdType | Filter,
  ): Promise<number> {
    //
  }
}
```

We replaced all filters and data that will be used in the insert/update or delete to use the `Document` type which is just a plain object.

The `Filter` is also a plain object, just has a fancy name.

When we return a new model instance, we cast the return data type as `ModelDocument` which is the type of the document that we get from the database as it will for sure contains a `_id` column and `id` column as well.

Now one more thing to do is to update the constructor data type to use the `BaseModel` in the constructor.

```ts
// src/core/database/model/base-model.ts

import { ModelDocument } from "./types";

export default abstract class BaseModel {
  // ...
  /**
   * Constructor
   */
  public constructor(public data: Partial<ModelDocument> = {}) {
    //
  }
}
```

So the data in the model may or may not contain the `id` and/or `_id` but also may contain other columns as well.

We labeled it with `Partial` type as it can be an empty object, for example if we did something like this:

```ts
const user = new User({
  name: 'hasan',
});
```

## Query Builder

Now let's do another good database Class, which is `QueryBuilder`, the purpose of that class is to isolate the database query logic from the model class so the crud model can call it directly or even we can use directly from outside the model, this will give us much flexibility.

So let's create a new file in the `src/core/database/query-builder` folder and name it `query-builder.ts`.

```ts
// src/core/database/query-builder/query-builder.ts
import { WithId } from "mongodb";
import connection, { Connection } from "../connection";

export class QueryBuilder {
  /**
   * Connection
   */
  protected connection: Connection = connection;

  /**
   * Get query of the given collection name
   */
  public query(collectionName: string) {
    return this.connection.database.collection(collectionName);
  }
}

const queryBuilder = new QueryBuilder();

export default queryBuilder;
```

Mostly the same as the `BaseModel` code, we injected the connection, and add a method called `query` to get the query of the given collection name.

Later we exported an instance of the `QueryBuilder` class as `queryBuilder` so we can use it directly.

## Create Method

Now let's add a `create` method, that method receives two arguments, the first one is the collection name, and the second one is the data that we want to insert.

```ts
import { WithId } from "mongodb";
import connection, { Connection } from "../connection";
import { Document } from "../model/types";

export class QueryBuilder {
  /**
   * Connection
   */
  protected connection: Connection = connection;

  /**
   * Make a create operation
   */
  public async create(
    collectionName: string,
    data: Document,
  ): Promise<WithId<Document>> {
    const query = this.query(collectionName);

    const result = await query.insertOne(data);

    return {
      ...data,
      _id: result.insertedId,
    };
  }

  /**
   * Get query of the given collection name
   */
  public query(collectionName: string) {
    return this.connection.database.collection(collectionName);
  }
}

const queryBuilder = new QueryBuilder();

export default queryBuilder;
```

As we can see we return the same passed document with the `_id` column added to it and made its return type is the document with the `_id` column.

## Update Method

Now let's add the `update` method, that method receives three arguments, the first one is the collection name, the second one is the filter, and the third one is the data that we want to update.

```ts
import { Document, Filter } from "../model/types";
import { WithId } from "mongodb";

export class QueryBuilder {
  // ...
  /**
   * Update document for the given filter
   */
  public async update(
    collectionName: string,
    filter: Filter,
    data: Document,
  ): Promise<WithId<Document> | null> {
    const query = this.query(collectionName);

    const result = await query.findOneAndUpdate(
      filter,
      {
        $set: data,
      },
      {
        returnDocument: "after",
      },
    );

    if (result.ok) {
      return result.value;
    }

    return null;
  }
}
```

Here we'll return two type of values, the first one is the updated document, and the second one is `null` if the update operation failed.

## Upsert Method

Now let's add the `upsert` method, that method receives three arguments, the first one is the collection name, the second one is the filter, and the third one is the data that we want to update.

```ts
import { Document, Filter } from "../model/types";
import { WithId } from "mongodb";

export class QueryBuilder {
  // ...
  
  /**
   * Update Or create document for the given filter
   */
  public async upsert(
    collectionName: string,
    filter: Filter,
    data: Document,
  ): Promise<WithId<Document> | null> {
    const query = this.query(collectionName);

    const result = await query.findOneAndUpdate(
      filter,
      {
        $set: data,
      },
      {
        returnDocument: "after",
        upsert: true,
      },
    );

    if (result.ok) {
      return result.value;
    }

    return null;
  }
}
```

Here we'll return two type of values, the first one is the updated document, and the second one is `null` if the update operation failed.

## Replace method

Now let's add the `replace` method, that method receives three arguments, the first one is the collection name, the second one is the filter, and the third one is the data that we want to replace.

```ts
import { Document, Filter } from "../model/types";
import { WithId } from "mongodb";

export class QueryBuilder {
  // ...
  /**
   * Replace the entire document for the given filter
   */
  public async replace(collectionName: string, filter: Filter, data: Document) {
    const query = this.query(collectionName);

    const result = await query.findOneAndReplace(filter, data, {
      returnDocument: "after",
    });

    return result.ok ? result.value : null;
  }
}
```

Here we'll return two type of values, the first one is the updated document, and the second one is `null` if the update operation failed.

## First Method (Find Single Document)

Now let's add the `first` method, this method will return the first matched filter, that method receives three arguments, the first one is the collection name, the second one is the filter, and the third one [Find Options Of MongoDB](https://mongodb.github.io/node-mongodb-native/4.0//interfaces/findoptions.html)

Why would we add such an option? because we need to make this query builder more general and abstract.

```ts
import { Document, Filter } from "../model/types";
import { FindOptions, WithId } from "mongodb";

export class QueryBuilder {
  // ...
  /**
   * Find first matched document for the given filter
   */
  public async first(
    collectionName: string,
    filter: Filter,
    filterOptions?: FindOptions,
  ) {
    const query = this.query(collectionName);

    return await query.findOne(filter, filterOptions);
  }
```

## List Method (Find Multiple Documents)

Now let's add the `list` method, this method will return the list of matched filter, that method receives four arguments, the first one is the collection name, the second one is the filter, the third one is a [FindCursor](https://mongodb.github.io/node-mongodb-native/4.11/classes/FindCursor.html) callback to allow us perform additional operations, for example to use the `skip` and `limit` methods and the last one is [Find Options Of MongoDB](https://mongodb.github.io/node-mongodb-native/4.0//interfaces/findoptions.html)

```ts
import { Document, Filter } from "../model/types";
import { FindOptions, WithId } from "mongodb";

export class QueryBuilder {
  // ...
  
  /**
   * Find list of documents based on the given filter
   */
  public async list(
    collectionName: string,
    filter: Filter,
    filterQuery?: (cursor: FindCursor) => void,
    filterOptions?: FindOptions,
  ) {
    const query = this.query(collectionName).find(filter, filterOptions);

    if (filterQuery) {
      filterQuery(query);
    }

    return await query.toArray();
  }
```

We'll see in a bit a practical usage for it.

## Count

Now let's add another one, we didn't do it before separately, but we added it in the `paginate` method which is the `countDocuments`, this one is also useful to count documents, so let's create a `count` method that receives two arguments, the first one is the collection name, and the second one is the filter.

```ts
import { Document, Filter } from "../model/types";
import { FindOptions, WithId } from "mongodb";

export class QueryBuilder {
  // ...
  /**
   * Count documents based on the given filter
   */
  public async count(
    collectionName: string,
    filter: Filter,
  ): Promise<number> {
    const query = this.query(collectionName);

    return await query.countDocuments(filter);
  }
```

## Deleting

Let's create now two methods, one for deleting a single document, and the other one for deleting multiple documents.

```ts
// ...
  /**
   * Delete Single document
   */
  public async deleteOne(
    collectionName: string,
    filter: Filter,
  ): Promise<number> {
    const query = this.query(collectionName);

    const result = await query.deleteOne(filter);

    return result.deletedCount;
  }

  /**
   * Delete multiple documents
   */
  public async delete(collectionName: string, filter: Filter): Promise<number> {
    const query = this.query(collectionName);

    const result = await query.deleteMany(filter);

    return result.deletedCount;
  }
```

## 🎨 Conclusion

In this article, we've created a query builder that will help us to perform CRUD operations on MongoDB in general, we've also added some useful methods like `paginate`, `count`, `delete`, `deleteOne`, `first`, `list`, `replace`, `upsert`, `update`, `create`.

## 🚀 Project Repository

You can find the latest updates of this project on [Github](https://github.com/hassanzohdy/nodejs-2023)

## 😍 Join our community

Join our community on [Discord](https://discord.gg/pb2vmdfhGf) to get help and support (Node Js 2023 Channel).

## 🎞️ Video Course (Arabic Voice)

If you want to learn this course in video format, you can find it on [Youtube](https://www.youtube.com/playlist?list=PLGO8ntvxgiZMJc7RN2lIq9WmMOlWZGzmz), the course is in Arabic language.

## 💰 Bonus Content 💰

You may have a look at these articles, it will definitely boost your knowledge and productivity.

General Topics

- [Event Driven Architecture: A Practical Guide in Javascript](https://dev.to/hassanzohdy/event-driven-architecture-the-best-paradigm-that-i-love-to-work-with-in-javascript-and-node-js-1gnk)
- [Best Practices For Case Styles: Camel, Pascal, Snake, and Kebab Case In Node And Javascript](https://dev.to/hassanzohdy/best-practices-for-case-styles-camel-pascal-snake-and-kebab-case-in-node-and-javascript-55oi)
- [After 6 years of practicing MongoDB, Here are my thoughts on MongoDB vs MySQL
](https://dev.to/hassanzohdy/after-6-years-of-practicing-mongodb-here-are-my-thoughts-on-mongodb-vs-mysql-574b)

Packages & Libraries

- [Collections: Your ultimate Javascript Arrays Manager](https://dev.to/hassanzohdy/collections-your-ultimate-javascript-array-handler-3o15)
- [Supportive Is: an elegant utility to check types of values in JavaScript](https://dev.to/hassanzohdy/supportive-is-an-elegant-utility-to-check-types-of-values-in-javascript-1b3e)
- [Localization: An agnostic i18n package to manage localization in your project](https://dev.to/hassanzohdy/mongez-localization-the-simplest-way-to-translate-your-website-regardless-your-favorite-framework-4gi3)

 React Js Packages

- [useFetcher: easiest way to fetch data in React Js](https://dev.to/hassanzohdy/usefetcher-the-easiest-way-to-fetch-data-in-react-45o9)

Courses (Articles)

- [React Js: Let"s Create File Manager With React Js and Node Js](https://dev.to/hassanzohdy/lets-create-a-file-manager-from-scratch-with-react-and-typescript-chapter-i-a-good-way-to-expand-your-experience-5g4k)