Setting up MS SQL Waterline Adapter

Summary

In this lesson, we set up Microsoft SQL Server with Sails using the sails-sql adapter.

1. Installing the Adapter

   • Run npm i sails-sql to install the required adapter.

2. Configuring the Datastore

   • In config/datastores.js, set the adapter to sails-sql.

   • Use the correct connection string format:

     adapter: 'sails-sql',
     url: 'mssql://root:Demo1@localhost:8181/demo'

   • Replace root with your database username and Demo1 with a strong password.

3. Connecting to Microsoft SQL Server

   • Ensure Microsoft SQL Server is running locally or remotely (e.g., on Azure).

   • The default MS SQL port is 8181.

   • If using a newer MS SQL version, the default admin user is SA.

4. Troubleshooting & Notes

   • This lesson doesn’t include a live demo due to compatibility issues with Apple’s M1 chip.

   • If you already have MS SQL running, the setup process mirrors other databases like PostgreSQL and MongoDB.

With this, your Sails app should be ready to connect to Microsoft SQL! 🚀

Transcript

Okay, so we are down to the last lesson in this Waterline adapters chapter of this course. To close out this chapter, we're going to look at setting up Microsoft SQL with Sails.

A little disclaimer: I wasn't able to set up a Microsoft SQL Server locally on my machine due to some compatibility issues with the M1 Apple chip. I'm going to assume that you have a Microsoft SQL Server up and running if you want to use Microsoft SQL. I don't use it in my day-to-day development, but if you do, I assume you have one running, and I'll show you how to proceed with setting it up to talk to your Sails application.

Alright, first, we need to grab the adapter. The adapter we're using is not the mssql adapter, but sails-sql. This adapter supports Microsoft SQL, making it the go-to adapter if you want to use MS SQL in production or even for local development.

To install it, run:

npm i sails-sql

Once that's installed, go to your datastore.js file. Here, all you need to do is specify that the adapter is sails-sql, then configure your connection details:

module.exports.datastores = {
  default: {
    adapter: 'sails-sql',
    url: 'mssql://root:Demo.1@localhost:8181/demo'
  }
}

Breakdown of the Connection String:

• Protocol: mssql://

• User: The default user for MS SQL, often SA or a custom user you set up (e.g., root in this example).

• Password: MS SQL requires a strong password, so something like Demo.1.

• Host: localhost if running locally.

• Port: The default MS SQL port, 8181.

• Database: The database name, demo in this case.

And that’s it! Even though I wasn’t able to demo setting it up like I did for PostgreSQL or MongoDB due to compatibility issues, the process remains straightforward. If you're using MS SQL, chances are you already have it set up locally or have an instance in Azure or another cloud provider.

This is how you would set up a Microsoft SQL database with the sails-sql adapter in Sails.