Insert into Google Cloud SQL from Node.js Cloud Function

Question

I have a Google Cloud SQL instance running MySQL Second Generation, and a Node.js Cloud Function.

index.js (below) produces the following error when run. How can a Google Cloud SQL database insert be performed from a Node.js environment. From what I understand my syntax is correct, but I must be missing something. connectionName, dbUser, dbPassword, and dbName have been omitted for obvious reasons.

{"code":"ER_PARSE_ERROR","errno":1064,"sqlMessage":"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO entries (guestName, content) values (first guest, I got here!);I' at line 1","sqlState":"42000","index":0,"sql":"CREATE TABLE entries (guestName VARCHAR(255), content VARCHAR(255),entryID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(entryID));INSERT INTO entries (guestName, content) values (first guest, I got here!);INSERT INTO entries (guestName, content) values (second guest, Me too!);"}

index.js

const mysql = require('mysql');

const connectionName = process.env.INSTANCE_CONNECTION_NAME || '';
const dbUser = process.env.SQL_USER || '';
const dbPassword = process.env.SQL_PASSWORD || '';
const dbName = process.env.SQL_NAME || '';

const mysqlConfig = {
  connectionLimit: 1,
  user: dbUser,
  password: dbPassword,
  database: dbName
};

if (process.env.NODE_ENV === 'production') {
  mysqlConfig.socketPath = `/cloudsql/${connectionName}`;
}

let mysqlPool;

exports.populateDatabase = (req, res) => {
  if (!mysqlPool) {
    mysqlPool = mysql.createPool(mysqlConfig);
  }

  mysqlPool.query('CREATE TABLE entries (guestName VARCHAR(255), content VARCHAR(255),' +
                  'entryID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(entryID));' + 
                  'INSERT INTO entries (guestName, content) values (`first guest`, `I got here!`);' + 
                  'INSERT INTO entries (guestName, content) values (`second guest`, `Me too!`);', (err, results) => {
    if (err) {
      console.error(err);
      res.status(500).send(err);
    } else {
      res.send(JSON.stringify(results));
    }
  });

};

You're passing several queries to your .query() method call at once. Have you tried passing only the 'CREATE TABLE' query? — Christopher P
I did, and that worked. How can multiline queries be performed? — crayden

Christopher P Christopher P · Accepted Answer · 2018-11-30T08:34:01

You're passing several statements to your .query() method call. By default, multiple statements aren't permitted.

Multiple statement queries can be enabled, but be aware that this poses a potential security risk.

Insert into Google Cloud SQL from Node.js Cloud Function

1 Answers