38-Nodejs Course 2023: Query Builder

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.

// 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.

// 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.

// 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:

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.

// 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.

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.

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.

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.

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

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

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 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

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.

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.

// ...
  /**
   * 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

😍 Join our community

Join our community on Discord 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, 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
• Best Practices For Case Styles: Camel, Pascal, Snake, and Kebab Case In Node And Javascript
• After 6 years of practicing MongoDB, Here are my thoughts on MongoDB vs MySQL

Packages & Libraries

• Collections: Your ultimate Javascript Arrays Manager
• Supportive Is: an elegant utility to check types of values in JavaScript
• Localization: An agnostic i18n package to manage localization in your project

React Js Packages

• useFetcher: easiest way to fetch data in React Js

Courses (Articles)

• React Js: Let"s Create File Manager With React Js and Node Js