1
\$\begingroup\$

I've written a lambda to perform simple steps to a redshift database. It runs multiple queries with transaction. I use pg-promise.

'use strict';
const config = require('./redshift_config_from_env');
const redshiftConn = `pg://${config.user}:${config.password}@${config.host}/${config.database}`;
const pgp = require('pg-promise')();

var tableCopyQuery = function(tableName) {
  return `
      ALTER TABLE ${tableName}_temp
      RENAME TO ${tableName}_process;
      CREATE TABLE ${tableName}_temp (LIKE ${tableName}_process);`;
};
var insertQuery = function(tableName) {
  return `
      INSERT INTO ${tableName}
      SELECT DISTINCT ${tableName}_process.*
        FROM ${tableName}_process
        LEFT JOIN ${tableName} USING (id)
       WHERE ${tableName}.id IS NULL
       ORDER BY ${tableName}_process.id;
      DROP TABLE ${tableName}_process;`;
};

exports.handler = function(event, context) {
  const client = pgp(redshiftConn);

  client.tx(function (t) {
    return t.batch([
      t.none(tableCopyQuery('active_connections')),
      t.none(insertQuery('active_connections')),
      t.none(tableCopyQuery('active_downloads')),
      t.none(insertQuery('active_downloads'))
    ]);
  })
    .then(function () {
      context.succeed(`Successfully Refreshed.`);
    })
    .catch(function (error) {
      context.fail(`Failed to run queries : ${JSON.stringify(error)}`);
    });
};

I think I may move sql queries into .sql file outside of the function.

Maybe the var could be turned into let because the scope will fit?

\$\endgroup\$
0

1 Answer 1

1
\$\begingroup\$

You shouldn't create the database object on every function call. Read this answer (and its question) for an example of this.

\$\endgroup\$
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.