Edit Page

Waterline query language

The syntax supported by Sails' model methods is called Waterline Query Language. Waterline knows how to interpret this syntax to retrieve or mutate records from any supported database. Under the covers, Waterline uses the database adapter(s) installed in your project to translate this language into native queries and send those queries to the appropriate database. This means that you can use the same query with MySQL as you do with Redis or MongoDB. It also means that you can change your database with minimal (if any) changes to your application code.

Query language basics

The criteria objects are formed using one of four types of object keys. These are the top level keys used in a query object. They are loosely based on the criteria used in MongoDB, with a few slight variations.

Queries can be built using either a where key to specify attributes, or excluding it.

Using the where key allows you to also use query options, such as limit, skip, and sort.

var thirdPageOfRecentPeopleNamedMary = await Model.find({
  where: { name: 'mary' },
  skip: 20,
  limit: 10,
  sort: 'createdAt DESC'
});

Constraints can be further joined together in a more complex example.

var teachersNamedMaryInMaine = await Model.find({
  where: { name: 'mary', state: 'me', occupation: { contains: 'teacher' } },
  sort: [{ firstName: 'ASC'}, { lastName: 'ASC'}]
});

If where is excluded, the entire object will be treated as a where criteria.

var peopleNamedMary = await Model.find({
  name: 'mary'
});

Key pairs

A key pair can be used to search records for values matching exactly what is specified. This is the base of a criteria object where the key represents an attribute on a model and the value is a strict equality check of the records for matching values.

var peopleNamedLyra = await Model.find({
  name: 'lyra'
});

They can be used together to search multiple attributes.

var waltersFromNewMexico = await Model.find({
  name: 'walter',
  state: 'new mexico'
});

Complex constraints

Complex constraints also have model attributes for keys but they also use any of the supported criteria modifiers to perform queries where a strict equality check wouldn't work.

var peoplePossiblyNamedLyra = await Model.find({
  name : {
    'contains' : 'yra'
  }
});

In modifier

Provide an array to find records whose value for this attribute exactly matches any of the specified search terms.

This is more or less equivalent to "IN" queries in SQL, and the $in operator in MongoDB.

var waltersAndSkylers = await Model.find({
  name : ['walter', 'skyler']
});

Not-in modifier

Provide an array wrapped in a dictionary under a != key (like { '!=': [...] }) to find records whose value for this attribute ARE NOT exact matches for any of the specified search terms.

This is more or less equivalent to "NOT IN" queries in SQL, and the $nin operator in MongoDB.

var everyoneExceptWaltersAndSkylers = await Model.find({
  name: { '!=' : ['walter', 'skyler'] }
});

Or predicate

Use the or modifier to match any of the nested rulesets you specify as an array of query pairs. For records to match an or query, they must match at least one of the specified query modifiers in the or array.

var waltersAndTeachers = await Model.find({
  or : [
    { name: 'walter' },
    { occupation: 'teacher' }
  ]
});

Criteria modifiers

The following modifiers are available to use when building queries.

  • '<'
  • '<='
  • '>'
  • '>='
  • '!='
  • nin
  • in
  • contains
  • startsWith
  • endsWith

Note that the availability and behavior of the criteria modifiers when matching against attributes with JSON attributes may vary according to the database adapter you’re using. For instance, while sails-postgresql will map your JSON attributes to the JSON column type, you’ll need to send a native query in order to query those attributes directly. On the other hand, sails-mongo supports queries against JSON-type attributes, but you should be aware that if a field contains an array, the query criteria will be run against every item in the array, rather than the array itself (this is based on the behavior of MongoDB itself).

'<'

Searches for records where the value is less than the value specified.

Model.find({
  age: { '<': 30 }
});

'<='

Searches for records where the value is less or equal to the value specified.

Model.find({
  age: { '<=': 20 }
});

'>'

Searches for records where the value is greater than the value specified.

Model.find({
  age: { '>': 18 }
});

'>='

Searches for records where the value is greater than or equal to the value specified.

Model.find({
  age: { '>=': 21 }
});

'!='

Searches for records where the value is not equal to the value specified.

Model.find({
  name: { '!=': 'foo' }
});

in

Searches for records where the value is in the list of values.

Model.find({
  name: { in: ['foo', 'bar'] }
});

nin

Searches for records where the value is NOT in the list of values.

Model.find({
  name: { nin: ['foo', 'bar'] }
});

contains

Searches for records where the value for this attribute contains the given string.

var musicCourses = await Course.find({
  subject: { contains: 'music' }
});

For performance reasons, case-sensitivity of contains depends on the database adapter.

startsWith

Searches for records where the value for this attribute starts with the given string.

var coursesAboutAmerica = await Course.find({
  subject: { startsWith: 'american' }
});

For performance reasons, case-sensitivity of startsWith depends on the database adapter.

endsWith

Searches for records where the value for this attribute ends with the given string.

var historyCourses = await Course.find({
  subject: { endsWith: 'history' }
});

For performance reasons, case-sensitivity of endsWith depends on the database adapter.

Query options

Query options allow you refine the results that are returned from a query. They are used in conjunction with a where key. The current options available are:

  • limit
  • skip
  • sort

Limit

Limits the number of results returned from a query.

Model.find({ where: { name: 'foo' }, limit: 20 });

Note: if you set limit to 0, the query will always return an empty array.

Skip

Returns all the results excluding the number of items to skip.

Model.find({ where: { name: 'foo' }, skip: 10 });
Pagination

skip and limit can be used together to build up a pagination system.

Model.find({ where: { name: 'foo' }, limit: 10, skip: 10 });

Waterline

You can find out more about the Waterline API below:

  • Sails.js Documentation
  • Waterline README
  • Waterline Reference Docs
  • Waterline Github Repository

Sort

Results can be sorted by attribute name. Simply specify an attribute name for natural (ascending) sort, or specify an ASC or DESC flag for ascending or descending orders respectively.

// Sort by name in ascending order
Model.find({ where: { name: 'foo' }, sort: 'name' });

// Sort by name in descending order
Model.find({ where: { name: 'foo' }, sort: 'name DESC' });

// Sort by name in ascending order
Model.find({ where: { name: 'foo' }, sort: 'name ASC' });

// Sort by object notation
Model.find({ where: { name: 'foo' }, sort: [{ 'name': 'ASC' }] });

// Sort by multiple attributes
Model.find({ where: { name: 'foo' }, sort: [{ name:  'ASC'}, { age: 'DESC' }] });

Is something missing?

If you notice something we've missed or could be improved on, please follow this link and submit a pull request to the sails repo. Once we merge it, the changes will be reflected on the website the next time it is deployed.

Sails logo
  • Home
  • Get started
  • Support
  • Documentation
  • Documentation

For a better experience on sailsjs.com, update your browser.

Check out the full Sailsconf 2022 playlist on Youtube

Tweet Follow @sailsjs

Documentation

Reference Concepts App structure | Upgrading Contribution guide | Tutorials More

Concepts

  • Actions and controllers
    • Generating actions and controllers
    • Routing to actions
  • Assets
    • Default tasks
    • Disabling Grunt
    • Task automation
  • Blueprints
    • Blueprint actions
    • Blueprint routes
  • Configuration
    • The local.js file
    • Using `.sailsrc` files
  • Deployment
    • FAQ
    • Hosting
    • Scaling
  • E-commerce
  • Extending Sails
    • Adapters
      • Available adapters
      • Custom adapters
    • Custom responses
      • Adding a custom response
    • Generators
      • Available generators
      • Custom generators
    • Hooks
      • Available hooks
      • Events
      • Hook specification
        • .configure
        • .defaults
        • .initialize()
        • .registerActions()
        • .routes
      • Installable hooks
      • Project hooks
      • Using hooks
  • File uploads
    • Uploading to GridFS
    • Uploading to S3
  • Globals
    • Disabling globals
  • Helpers
    • Example helper
  • Internationalization
    • Locales
    • Translating dynamic content
  • Logging
    • Custom log messages
  • Middleware
    • Conventional defaults
  • Models and ORM
    • Associations
      • Many-to-many
      • One way association
      • One-to-many
      • One-to-one
      • Reflexive associations
      • Through associations
    • Attributes
    • Errors
    • Lifecycle callbacks
    • Model settings
    • Models
    • Query language
    • Records
    • Standalone Waterline usage
    • Validations
  • Policies
    • Access Control and Permissions
  • Programmatic usage
    • Tips and tricks
  • Realtime
    • Multi-server environments
    • On the client
    • On the server
  • Routes
    • Custom routes
    • URL slugs
  • Security
    • Clickjacking
    • Content security policy
    • CORS
    • CSRF
    • DDOS
    • P3P
    • Socket hijacking
    • Strict Transport Security
    • XSS
  • Services
  • Sessions
  • Shell scripts
  • Testing
  • Views
    • Layouts
    • Locals
    • Partials
    • View engines

Built with Love

The Sails framework is built by a web & mobile shop in Austin, TX, with the help of our contributors. We created Sails in 2012 to assist us on Node.js projects. Naturally we open-sourced it. We hope it makes your life a little bit easier!

Sails:
  • What is Sails?
  • Community
  • News
  • For business
About:
  • Our company
  • Security
  • Legal
  • Logos/artwork
Help:
  • Get started
  • Documentation
  • Docs
  • Contribute
  • Take a class

© 2012-2022 The Sails Company. 
The Sails framework is free and open-source under the MIT License. 
Illustrations by Edamame.