Home » AdonisJs

AdonisJs | Lucid Query Builders | Part 1

Here, we are going to discuss lucid query builder which is an important feature of AdonisJs & one of the greatest benefits of working with AdonisJs.
Submitted by Radib Kar, on February 13, 2021

In Adonis, you don't need to write database queries manually, rather you can use this query builder. Below are a few examples that we will also use further in our ToDo App backend as per needs.

Starting with a simple example of a query builder

Say, we need want to search users whose age is more than 24.

In general, we would write SQL query like,

const sql = 'SELECT * from users where `age` > 24  '

But with Lucid query builder,

const users = User.query().where('age','>', 24)

Where, User is the model name.

Query Builders

1. where
   

   const project = await Project.query().where('id', id)
   

2. whereNot
   

   const projects = await Project.query().whereNot('id', id)
   

3. whereIn
   

   const projects = await Project.query().whereIn('id', [id1, id2, id3])
   

4. whereNotIn
   

   const projects = await Project.query().whereNotIn('id', [id1, id2, id3])
   

You can see all the query builders and the details of their usage from the doc page here (Query Builder)

Static methods

1. find
   Find a record using the primary key
   

   const User = use('App/Models/User')
   await User.find(1)
   

2. findOrFail
   It throws a ModelNotFoundException when unable to find a record, otherwise similar like find
   

   const User = use('App/Models/User')
   await User.findOrFail(1)
   

3. findBy/findByOrFail
   It returns the first matching record using a <key,value> pair
   

   const User = use('App/Models/User')
   await User.findBy('email', '[email protected]')
   
   // or
   await User.findByOrFail('email', ' [email protected] ')
   

4. first / firstOrFail
   Returns the first row from the database or throws exception
   

   const User = use('App/Models/User')
   await User.first()
   
   // or
   await User.firstOrFail(
   

5. all
   This is equivalent to 'select * from tablename'
   

   const User = use('App/Models/User')
   await User.all()
   

This is it for this tutorial. In our next tutorial, we will see how to insert, update rows in the database via query builders?