/waterline

Waterline Query Language

Criteria Object Structure

Queries accept a criteria object with an optional explicit where key alongside query options:

// Explicit where
var results = await User.find({
  where: { name: 'mary', occupation: { contains: 'teacher' } },
  sort: [{ firstName: 'ASC' }, { lastName: 'ASC' }],
  limit: 10,
  skip: 20
})

// Shorthand (no where key) -- for simple equality
var results = await User.find({ name: 'mary' })

Equality (Key Pairs)

Multiple keys at the same level are combined with AND:

// name = 'walter' AND state = 'new mexico'
await User.find({ name: 'walter', state: 'new mexico' })

Comparison Operators

// Less than
await User.find({ age: { '<': 30 } })

// Less than or equal
await User.find({ age: { '<=': 20 } })

// Greater than
await User.find({ age: { '>': 18 } })

// Greater than or equal
await User.find({ createdAt: { '>=': new Date('2024-01-01') } })

// Not equal
await User.find({ status: { '!=': 'banned' } })

// Range (combine multiple operators on one attribute)
await User.find({ age: { '>=': 18, '<': 65 } })

In / Not In

// Value in array
await User.find({ emailStatus: { in: ['verified', 'confirmed'] } })

// Array shorthand (same as 'in')
await User.find({ emailStatus: ['verified', 'confirmed'] })

// Not in array
await User.find({ id: { nin: [1, 2, 3] } })

// Not equal with array (also means not-in)
await User.find({ name: { '!=': ['walter', 'skyler'] } })

String Operators

// Contains substring
await User.find({ email: { contains: '@example.com' } })

// Starts with
await User.find({ emailStatus: { startsWith: 'ver' } })

// Ends with
await User.find({ fullName: { endsWith: 'Doe' } })

Case-sensitivity of string operators depends on the database adapter.

OR Clause

An array of criteria objects. A record matches if it satisfies any of them:

await User.find({
  or: [
    { emailStatus: 'verified' },
    { emailStatus: 'confirmed' },
    { emailStatus: 'pending' }
  ]
})

// Combine OR with other criteria (AND)
await Screencast.findOne({
  or: [{ id: idOrSlug }, { slug: idOrSlug }],
  published: true
})

AND Clause

Explicit AND (usually implicit via multiple keys):

await User.find({
  where: {
    or: [{ emailStatus: 'verified' }, { emailStatus: 'confirmed' }],
    and: [{ createdAt: { '>=': new Date('2024-01-01') } }]
  }
})

Deeply Nested Conditions

await User.find({
  where: {
    or: [
      {
        and: [
          { emailStatus: 'verified' },
          { createdAt: { '>=': new Date('2024-01-01') } }
        ]
      },
      {
        and: [{ emailStatus: 'confirmed' }, { updatedAt: { '<=': new Date() } }]
      }
    ]
  }
})

Sort

Multiple syntax forms:

// String: ascending (default)
await User.find({ where: { active: true }, sort: 'name' })

// String: descending
await User.find({ where: { active: true }, sort: 'name DESC' })

// String: ascending (explicit)
await User.find({ where: { active: true }, sort: 'name ASC' })

// Chained method
await User.find({ active: true }).sort('createdAt DESC')

// Array of objects (multi-field sort)
await User.find().sort([{ fullName: 'ASC' }, { createdAt: 'DESC' }])

Limit and Skip (Pagination)

// In criteria object
await User.find({ where: { active: true }, limit: 10, skip: 20 })

// Chained methods
await User.find({ active: true }).limit(10).skip(20)

// Using .paginate(pageNumber, perPage) -- zero-indexed page number
await Invoice.find({ creator: userId, status: 'paid' })
  .sort('updatedAt DESC')
  .paginate(page, perPage)

Select (Projection)

Include only specific attributes. The record id is always included regardless.

// In criteria
await User.find({
  where: { emailStatus: 'verified' },
  select: ['id', 'email', 'fullName', 'createdAt']
})

// Chained method
await User.find({ emailStatus: 'verified' }).select([
  'fullName',
  'email',
  'emailStatus'
])

Omit (Exclusion)

Exclude specific attributes:

// In criteria
await User.find({
  emailStatus: 'confirmed',
  omit: ['password', 'emailProofToken', 'emailProofTokenExpiresAt']
})

// Chained method
await User.find({ id: { '>': 10 } })
  .omit(['password', 'updatedAt'])
  .limit(10)

Complex Chained Query

All modifiers can be chained together:

await User.find()
  .where({ emailStatus: { in: ['verified', 'confirmed'] } })
  .select(['id', 'email', 'fullName'])
  .sort('createdAt DESC')
  .limit(50)
  .skip(10)

Quick Reference Table