Node.js: Connecting to MySQL on a socket with Sequelize

According to its official description, “Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server.” Sequelize is widely used in Node.js web applications to abstract the database layer. I recently found a “corner case” for Sequelize which is possible to accomplish, but is not well documented anywhere on the web. I need to use Sequelize to connect to a MySQL server via a UNIX socket. I’m developing some plugins for Confluence that use the atlassian-connect-express toolkit, which has a datastore that wraps Sequelize. The way Atlassian has chosen to wrap Sequelize is rather unfortunate, probably because they are trying to maintain backwards compatibility with JugglingDB. Essentially, the Atlassian Connect Express only looks at the URL, and ignores any option passed to Sequelize. Therefore, you have to pass everything you need via the URL, and this is where it’s tricky. Here’s the form of the URL that you need to connect to MySQL via a UNIX socket with Sequelize:

mysql://my_user_name:my_password@127.0.0.1:optional_port/database_name?socketPath=/full/absolute/path/to/socket"

Explanation of the Database URL

Sequelize parses the URL with Javascript’s url library. I will refer you to Dmitri Pavlution’s explanation of URL parsing in Javascript, especially the diagram of URL structure shown in Section 1. The database URL follows the schema exactly. The port is only required if your database is listening on a non-standard port (3306 is the default for MySQL).

The trick for using UNIX sockets with MySQL is that you have to pass the absolute pathto the socket as the socketPath parameter. Note that this approach is different from PostGreSQL, which allows you to pass the socket path in the host parameter. That approach won’t work with MySQL. How did I discover this information? It was quite a deep dive…* Sequelize uses the mysql2 connector library

Use Case: SQL Socket for Local Development

If you have a Cloud SQL database in Google Cloud Platform (GCP), you can connect directly from your local dev environment if:

  1. The instance must have a public lP address (this is not very clear in the docs!)
  2. Install the Cloud SQL Auth Proxy on your local dev environment
  3. Run the proxy with a socket (Linux/Mac only; Windows does not have socket files) as shown below:
cloud_sql_proxy -dir=/Users/your_username/cloudsql -instances=your-gcp-project:your-region:your-instance-name

Note that you must have the cloud_sql_proxy binary in your path or provide the full path to the cloud_sql_proxy binary on your local workstation. On a Mac running Catalina or later, the root file system is read-only, so even root cannot create a directory there. It must be in your home directory. The instance name can be copied from the Google Cloud Console or from the connectionName field of the output from the command line tool:

gcloud sql instances describe your-instance-name

Run the proxy in one shell, and start a new shell to connect to the database (assuming the same socket path and instance shown above).

mysql -u sql-user-name -p -S /Users/your_username/cloudsql/your-gcp-project:your-region:your-instance-name

Use Case: Google App Engine, Standard Environment

Applications running on App Engine (part of Google Cloud Platform) can only connect to Cloud SQL instances with private IP addresses via a UNIX socket. Therefore, if you write an Atlassian app and want to run on App Engine Standard, you must use the Sequelize URL I described above.