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?

Comments and Discussions!

Load comments ↻

Copyright © 2024 All rights reserved.