Query a Google cloud SQL instance in Node.js from a GKE pod with cloud sql proxy running as sidecar-CodePudding

I am tasked with adding a MySql database to a microservice application for work. I am the only person on this task and don't really have anyone to turn too for advice so I am reaching out to the internets for help. I have succesfully deployed a pod that is running a small test application and the cloud-sql-proxy. I have scoured the documentation trying to figure out how to connect to the db and perform a query and this is what I have come up with (but it doesn't work).

const express = require('express');
const mysql = require('mysql');
const bodyParser = require('body-parser')
const cors = require('cors');
const axios = require('axios');


const app = express();
app.use(bodyParser.json());
app.use(cors())
app.enable('trust proxy');

// Automatically parse request body as form data.
app.use(express.urlencoded({extended: false}));
// This middleware is available in Express v4.16.0 onwards
app.use(express.json());


// [START cloud_sql_mysql_mysql_create_tcp]
const createTcpPool = async config => {
  // Extract host and port from socket address
  const dbSocketAddr = process.env.DB_HOST.split(':');

  // Establish a connection to the database
  return mysql.createPool({
    user: process.env.DB_USER, // e.g. 'my-db-user'
    password: process.env.DB_PASS, // e.g. 'my-db-password'
    database: process.env.DB_NAME, // e.g. 'my-database'
    host: dbSocketAddr[0], // e.g. '127.0.0.1'
    port: dbSocketAddr[1], // e.g. '3306'
    // ... Specify additional properties here.
    ...config,
  });
};
// [END cloud_sql_mysql_mysql_create_tcp]
var pool = createTcpPool();

const stuff = pool.query('SELECT * FROM entries');

function getQuery() {
  console.log(stuff);
}

getQuery()

Here is a picture of the error I get when I deploy the pod and the logs from the proxy to verify it's running

I'm pretty new to MySql and GKE and trying to figure this out has been a huge struggle. I just want to know how I can actually query the db and would greatly appreciate some assistance or code sample to point me in the right direction, thanks internets.

CodePudding user response：

As mentioned in the thread1 ,

Handling such functions can be done through following example :

const mysql = require('mysql'); c
onst pool = mysql.createPool({ connectionLimit : 1, socketPath: '/cloudsql/'   '$PROJECT_ID:$REGION:$SPANNER_INSTANCE_NAME', 
user: '$USER', p
assword: '$PASS', 
database: '$DATABASE' }); 
exports.handler = function handler(req, res) 
{ //using pool instead of creating connection with function call 
pool.query(`SELECT * FROM table where id = ?`, 
req.body.id, function (e, results) { 
//made reply here
 }); };

For more information you can refer to the documentation related to TCP connection when using Node js.

const createTcpPool = async config => {
  // Extract host and port from socket address
  const dbSocketAddr = process.env.DB_HOST.split(':');

  // Establish a connection to the database
  return mysql.createPool({
    user: process.env.DB_USER, // e.g. 'my-db-user'
    password: process.env.DB_PASS, // e.g. 'my-db-password'
    database: process.env.DB_NAME, // e.g. 'my-database'
    host: dbSocketAddr[0], // e.g. '127.0.0.1'
    port: dbSocketAddr[1], // e.g. '3306'
    // ... Specify additional properties here.
    ...config,
  });
};

CodePudding user response：

So trying to create a pool by calling createTcpPool seems to have been the issue. I changed it to

  let pool = mysql.createPool({
    user: process.env.DB_USER, // e.g. 'my-db-user'
    password: process.env.DB_PASS, // e.g. 'my-db-password'
    database: process.env.DB_NAME, // e.g. 'my-database'
    host: '127.0.0.1', // e.g. '127.0.0.1'
    port: '3306'
  });

and got a succesful return from my db.