One really nice thing AdonisJS allows us to do is extend the query builders within our project. This includes the model, database, and insert query builder. For the most part, the process is similar for all three, so we’ll be focusing on the Model Query Builder.

We’ll start off by following along with the example provided within the documentation. Then, we’ll create our own macro-based off Laravel’s firstOr.

Where We’ll Be Working

In order to extend the Model Query Builder, we’ll want to define a macro onto the ModelQueryBuilder that we can import from Adonis/Lucid/Database. Additionally, we’ll want to define this macro during the boot process of our application.

So, one place we can register these query builder extensions is within a provider. Let’s go ahead and create a provider specifically for this.

node ace make:provider QueryBuilder

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Next, we’ll want to specifically work within boot method so that the macros are registered while our application is booting. Let’s also go ahead and import the ModelQueryBuilder from Adonis/Lucid/Database.

export default class QueryBuilderProvider {
  constructor(protected app: ApplicationContract) {}

  public async boot() {
    // All bindings are ready, feel free to use them
    const { ModelQueryBuilder } = this.app.container.resolveBinding('Adonis/Lucid/Database')
  }
}

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Remember, since we’re using the IoC Container to import this, we want to import it directly within the boot method and not at the top of the file.

Defining The Macro

Next, we’re ready to define our macro. The first argument is the name we want the query builder method to be called and the second argument is a callback function. Within that callback function, we’ll be able to perform whatever action we need with our query builder and return whatever response we want.

One important thing to note is that you’ll want your function to be scoped, not an arrow function, because this will be the query builder.

export default class QueryBuilderProvider {
  public async boot() {
    // All bindings are ready, feel free to use them
    const { ModelQueryBuilder } = this.app.container.resolveBinding('Adonis/Lucid/Database')

    ModelQueryBuilder.macro('getCount', async function() {
      const result = await this.count('* as total')
      return BigInt(result[0].$extras.total)
    })
  }
}

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

So, following the example in the documentation, we’re creating a macro called getCount that will count the total number of rows returned from our query and get the result. Then, from the result, it’ll specifically grab just the count value, convert it to a BigInt and return that as the return value for this method.

Essentially, we’ll be able to call this method like this:

const count = await Post.query().where('isPublished', true).getCount()

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Here count will be a BigInt number of the count of the total number of published posts within our database.

Inform TypeScript

As always, when we extend something within our application, we need to inform TypeScript. To do this, let’s create a new contract file called orm.ts within our contracts directory.

Then, within the directory, we’ll extend the ModelQueryBuilderContract within the Adonis/Lucid/Orm namespace to include our new method.

declare module '@ioc:Adonis/Lucid/Orm' {
  interface ModelQueryBuilderContract<
    Model extends LucidModel, 
    Result = InstanceType<Model>
  > {
    getCount(): Promise<BigInt>
  }
}

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Here we’re also getting type arguments stating that Model extends LucidModel, which all models within our project do, and Result is the type of the Model itself.

Then, within the ModelQueryBuilderContract, we define our getCount method and define that it returns a promise that’ll eventually resolve to a BigInt.

With that, we’re now free to use our getCount method on any of our model’s query builders!

Adding FirstOr

Now, let’s run through another example by adding a firstOr method. The goal here is that our method should attempt to return the first query result if there is one. If there isn’t one, then it’ll take a callback function and allow us to define a fallback value.

Defining the Macro

First, let’s add the macro down below our getCount macro.

export default class QueryBuilderProvider {
  public async boot() {
    // All bindings are ready, feel free to use them
    const { ModelQueryBuilder } = this.app.container.resolveBinding('Adonis/Lucid/Database')

    ModelQueryBuilder.macro('getCount', async function() {
      const result = await this.count('* as total')
      return BigInt(result[0].$extras.total)
    })

    ModelQueryBuilder.macro('firstOr', async function<T = undefined>(orFunction: () => T) {
      const result = await this.first()

      if (!result) {
        return orFunction()
      }

      return result
    })
  }
}

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Here we’ve named our macro firstOr. Then, we define our callback function, and we’ll use type arguments to allow each use to define its fallback value type. Then inside the callback function, we’ll attempt to get the first result. If there isn’t one, we’ll call the orFunction argument and return its value. Otherwise, we’ll return the result.

Inform TypeScript

Next, we’ll want to inform TypeScript about the method and that it accepts a callback function.

declare module '@ioc:Adonis/Lucid/Orm' {
  interface ModelQueryBuilderContract<
    Model extends LucidModel, 
    Result = InstanceType<Model>
  > {
    getCount(): Promise<BigInt>
    firstOr<T = undefined>(orFunction: () => T): Promise<Result> | T
  }
}

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Here we’re defining our firstOr method and defining a type argument, T, and we’ll default that to undefined. We state that it accepts a callback function as its only argument, and we’ll have TypeScript infer the return type as T for that function. Lastly, it returns a promise of type Result or T, the type argument.

Big thanks to Aman Virk for providing and explaining an improved TypeScript definition for this function!

Putting It To Use

Lastly, let’s put our new fancy method to use!

// post will always be of type Post
const post = await Post.query().where('id', 1).firstOr(() => new Post())

// post can be of type Post or string
const post = await Post.query().where('id', 1).firstOr(() => 'working')

// use a fallback query!
const post = await Post.query()
  .where('id', 1)
  .firstOr(() => Post.query()
    .where('id', 2)
    .first()
  )

  

    

      Copied!
      

        

      
    
  
  
    
    
    
    
    
  

Pretty cool, huh?