Edit Page

Low-level MySQL usage (advanced)

This tutorial steps through how to access the raw MySQL connection instance from the mysql package. This is useful for getting access to low level APIs available only in the raw client itself.

Note: Many Node.js / Sails apps using MySQL will never need the kind of low-level usage described here. If you find yourself running up against the limitations of the ORM, there is usually a workaround that does not involve writing code for the underlying database. Even then, if you're just looking to use custom native SQL queries, read no further-- instead, check out sendNativeQuery() instead.

Also, before we proceed, make sure you have a datastore configured to use a functional MySQL database.

Get access to an active MySQL connection

To obtain an active connection from the MySQL package you can call the .leaseConnection() method of a registered datastore object (RDI).

  1. Get the registered datastore instance for the connection:
// Get the named datastore
var rdi = sails.getDatastore('default');

// Get the datastore configured for a specific model
var rdi = Product.getDatastore();
  1. Call the leaseConnection() method to obtain an active connection:
rdi.leaseConnection(function(connection, proceed) {
  db.query('SELECT * from `user`;', function(err, results, fields) {
    if (err) {
      return proceed(err);
    }

    proceed(undefined, results);
  });
}, function(err, results) {
  // Handle results here after the connection has been closed
})

Get access to the low-level driver

To get access to the low-level driver and MySQL package in a Sails app, you can grab them from the registered datastore object (RDI).

  1. Get the registered datastore instance for the connection:
// Get the named datastore
var rdi = sails.getDatastore('default');

// Get the datastore configured for a specific model
var rdi = Product.getDatastore();
  1. Get the driver from the datastore instance which contains the MySQL module:
var mysql = rdi.driver.mysql;
  1. You can now use the module to make native requests and call other function native to the MySQL module:
// Get the named datastore
var rdi = sails.getDatastore('default');

// Grab the MySQL module from the datastore instance
var mysql = rdi.driver.mysql;

// Create a new connection
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : 'password',
  database: 'example_database'
});

// Make a query and pipe the results
connection.query('SELECT * FROM posts')
  .stream({highWaterMark: 5})
  .pipe(...);

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

Tutorials

  • Full-stack JavaScript with Sails
  • Low-level MySQL usage (advanced)
  • Using CoffeeScript
  • Using MongoDB
  • Using TypeScript

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-2023 The Sails Company. 
The Sails framework is free and open-source under the MIT License. 
Illustrations by Edamame.