Edit Page

.query()

Execute a raw SQL query using the specified model's datastore.

SomeModel.query(sql, valuesToEscape, function(err, rawResult) {

});

WARNING: Unlike other Waterline model methods, .query() supports neither promise-based usage nor the use of .exec(). In other words, it does not utilize Waterline's normal deferred object mechanism. Instead, it provides raw access directly to the underlying database driver.

Usage

.query() is only available on Sails/Waterline models that are configured to use a SQL database (e.g. PostgreSQL or mySQL). Its purpose is to perform raw SQL queries. Note that exact usage and result format varies between adapters, so you'll need to refer to the documentation for the underlying database driver. (See below for a couple of simple examples to help get you started.)

	Argument	Details
1	sql	A SQL string written in the appropriate dialect for this model's database. Allows template syntax, (e.g. `?`, `$1`) the exact style of which depends on the underlying database adapter. (See examples below.)
2	valuesToEscape	An array of dynamic, untrusted strings to SQL-escape and inject within the SQL string using the appropriate template syntax for this model's database. (If you have no dynamic values to inject, then just use an empty array here.)
3	done	A callback function that will be triggered when the query completes successfully, or if the adapter encounters an error.

Callback

	Argument	Type	Details
1	err		The error that occurred, or a falsy value if there were no errors. (The exact format of this error varies depending on the SQL query you passed in and the database adapter you're using. See examples below for links to relevant documentation.)
2	rawResult		The raw result from the adapter. (The exact format of this raw result data varies depending on the SQL query you passed in and the database adapter you're using. See examples below for links to relevant documentation.)

Example

Remember: Usage and result data vary depending on the SQL query you send, as well as the adapter you're using. Below, you'll find two examples: one for PostgreSQL and one for MySQL.

PostgreSQL example

Communicate directly with pg, an NPM package used for communicating with PostgreSQL databases:

Pet.query('SELECT pet.name FROM pet WHERE pet.name = $1', [ 'dog' ] ,function(err, rawResult) {
  if (err) { return res.serverError(err); }

  sails.log(rawResult);
  // (result format depends on the SQL query that was passed in, and the adapter you're using)

  // Then parse the raw result and do whatever you like with it.

  return res.ok();

});

MySQL example

Assuming the Pet model is configured to use the sails-mysql adapter, the following code will communicate directly with mysql, an NPM package used for communicating with MySQL databases:

Pet.query('SELECT pet.name FROM pet WHERE pet.name = ?', [ 'dog' ] ,function(err, rawResult) {
  if (err) { return res.serverError(err); }

  sails.log(rawResult);
  // ...grab appropriate data...
  // (result format depends on the SQL query that was passed in, and the adapter you're using)

  // Then parse the raw result and do whatever you like with it.

  return res.ok();

});

Notes

This method only works with SQL databases. To get access to the raw MongoDB collection, use .native().

This method does not support .exec() or .then(), and it does not return a promise. If you want to "promisify" .query(), have a look at this.

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-docs repo. Once we merge it, the changes will be reflected on the website the next time it is deployed.

Documentation

Reference

Built with Love

The Sails framework is maintained by a web & mobile studio 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!