Using Cloud SQL Proxy from Firebase function

Question

I'm running Google's Cloud SQL Proxy locally and it's working with locally served Firebase functions using a command like:

/cloud_sql_proxy -instances=my-project-12345:us-central1:my-instance=tcp:1433

However I don't really know how to have this work on deployed Firebase functions.

export const typeOrmConnectionOptions: ConnectionOptions = {
  name: 'primary',
  type: 'mssql',
  host: '127.0.0.1',
  port: 1433,
  username: 'sqlserver',
  password: 'my$trongPa$$word',
  database: 'TestDB',
  synchronize: true,
  logging: true,
  entities: ['lib/entity/**/*.js'],
  ...(prod && {
    extra: {
      socketPath:
        '/cloudsql/my-project-12345:us-central1:my-instance=tcp',
      credential_file: './admin-service-account-file-long-a1b2c3-hash.json'
    }
  })
};

I really am taking shots in the dark as far as passing the credential file as part of the extras object to TypeORM's connections object, however I feel like something like this must be necessary to link the service account I created following this step to database queries.

Another longshot idea I had was to use the environment variable to set the credentials using this JSON file:

process.env.GOOGLE_APPLICATION_CREDENTIALS = fs.readFileSync(
  './admin-service-account-file-long-a1b2c3-hash.json',
  'utf8'
)

No joy.

I don't think the error message is much help since I am certain the way I'm attempting this is fundamentally incorrect, but for what it's worth, the above gets the error

"Failed to connect to 127.0.0.1:1433 - connect ECONNREFUSED 127.0.0.1:1433"

How can I use the Cloud SQL Proxy to connect to a GCP database from Firebase?

Edit

I am not having luck connecting with either the socketPath property, or directly referencing the IP of the GCP RD instance with root username and password. I've seen various places that the cloud proxy is only needed in local development, and also that it is needed in production (that is where I got the idea about socketPath).

Further I have tried a test using MySql as was linked in an answer below. Formerly I had used this as a guide for SQL Server, but since that is in beta still, I thought I would give MySQL a try. Still failure, however when using that and using the services IP instead of cloud proxy, I get a timeout error.

I have also begun initializing the app with service account credentials I created from the GCP dashboard.

import { serviceAccount } from './service-account';
const adminConfig = JSON.parse(process.env.FIREBASE_CONFIG);
adminConfig.credential = admin.credential.cert(
  serviceAccount as admin.ServiceAccount
);
admin.initializeApp(adminConfig);

Priggish

Answer 1

cloud proxy is only needed when trying to connect from outside of google cloud network. From functions, you can directly connect using the host, port, username, and password.

I pass in those details during deployment through functions config.

firebase functions:config:set envs.db_host=$DB_HOST_PROD envs.db_user=$DB_USER_PROD envs.db_password=$DB_PASSWORD_PROD envs.db_name=$DB_NAME_PROD envs.db_use_ssl=false --project hello-world

firebase functions:config:set envs.node_env=production --project hello-world

firebase deploy --token=$FIREBASE_TOKEN --project hello-world --only functions,hosting

Refer to https://mcmap.net/q/236941/-how-do-you-setup-local-environment-variables-for-cloud-functions-for-firebase on how I use this to set the environment variables. I then use the environment variables to connect the database

Kym

Hmm I see, thank you. Where does FIREBASE_TOKEN come from? – Priggish

Those commands are from my CI system. Refer to this to get a new token github.com/firebase/firebase-tools#using-with-ci-systems – Kym

This does not seem to be working from firebase using db username/password and its IP as the connection host. Are you sure you have not enabled some service account or other permission or using for example admin.credential.cert( serviceAccountJson ); (That isn't working for me either, but just something I tried. – Priggish

This is not a good answer when security is important. Using host (IP) based connections means opening up the Cloud SQL instance either to the world (very bad) or to Google IP addresses (still bad). Cloud SQL Proxy is the recommended secure method of connecting to Cloud SQL. – Sprain

Answer 2

In order to connect to a Cloud SQL instance from a Cloud Function (or Firebase function) you can use UNIX domain sockets. The documentation only shows how to do this for MySQL and PosgreSQL, not for SQL Server. It may be because it's not supported yet. However, I encourage you to give it a try.

Either way, you can also connect your Cloud Functions to a SQL Server Cloud SQL instance using a Serverles VPC Connector and the instance's private IP. Quoting the docs:

By default, Cloud Functions does not support connecting to the Cloud SQL instance using TCP. Your code should not try to access the instance using an IP address (such as 127.0.0.1 or 172.17.0.1) unless you have configured Serverless VPC Access.

Debatable

Do the domain sockets require an instance of Cloud Proxy to be running within Firebase? From the docs you linked, socketPath: /cloudsql/${process.env.CLOUD_SQL_CONNECTION_NAME}``, /cloudsql... looks like it is a directory on the Firebase host. Does this need to be switched on somehow? Just setting up the connection as it is there gives me the same connection issue. – Priggish

No, you don't need to set up a Cloud SQL Proxy. The Proxy is used to connect to a Cloud SQL instance from outside of GCP or outside the VPC network in which the Cloud SQL instance is. You may be facing connectivity issues because it's possible that Domain Sockets are not available yet for SQL Server. You can still try setting up a Serverless VPC Connector as explained in the link on the answer – Debatable

The link is rather vague about how to select an IP range. Do you know about that? I tried to enter the range given for my region given by the recommended page but after creation, I received the error that my connector is in a "Bad state, manual deletion recommended." Also, for network, I am only given the option "Default", which seems also not quite what i'd expect. – Priggish

Are you still facing any issues with this setup? I would also like to mention that I edited your original question to hide a potential user password. If this happened to be your password, please replace it with a new password immediately. – Aweless

Can you cite the quote? I can't find it in any Google docs only blog posts. – Translocate