{"id":78874,"date":"2018-05-26T12:15:55","date_gmt":"2018-05-26T12:15:55","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=78874"},"modified":"2022-01-05T21:01:40","modified_gmt":"2022-01-05T21:01:40","slug":"combining-amazon-aurora-lambda-and-sqs-to-go-beyond-the-native-capabilities-of-mysql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/cloud\/aws\/combining-amazon-aurora-lambda-and-sqs-to-go-beyond-the-native-capabilities-of-mysql\/","title":{"rendered":"Combining Amazon Aurora, Lambda, and SQS to go Beyond the Native Capabilities of MySQL"},"content":{"rendered":"

Adoption of open-source database engines, such as MySQL and PostgreSQL, has become a major driver shaping the future of modern cloud-native data architectures. By combining open-source database technologies, alongside existing commercial database solutions, companies can essentially eat their cake and have it too – use the more expensive, commercial database solutions only for the specific applications that absolutely require it, all while enabling expansion and flexibility of the \u2018database fleet\u2019 using lower-cost open source database alternatives. This is the 80\/20 rule of modern data architectures. Yet the transition isn\u2019t always simple.<\/p>\n

Most commercial database systems are \u2018monolithic\u2019 and integrate a great deal of advanced functionality and capabilities directly into the database itself. As one example, both Microsoft and Oracle provide an asynchronous messaging\/queuing mechanism that is part of the database engine. Oracle calls it Advanced Queueing and Microsoft calls it Service Broker. Both are great implementations of queueing directly within the database, and many applications have been closely developed around specific database features.<\/p>\n

The problem is that PostgreSQL and MySQL lack some of the built-in capabilities you can find in a commercial relational database system forcing customers to either deploy 3rd party solutions (such as a dedicated queue system, external to the database) or develop custom functionality within the applications themselves.<\/p>\n

But luckily for us, when it comes to running MySQL and PostgreSQL in the cloud, there are simpler alternatives for mitigating missing functionality in the database itself. For example, when your run your MySQL databases as an Aurora MySQL cluster in Amazon Web Services, you also have access to the full set of powerful AWS ecosystem services and solutions which you can leverage where native database functionality is missing.<\/p>\n

While Aurora MySQL itself can provide increased performance and high-availability capabilities that go beyond what native MySQL can offer, there are also application-facing advantages. In this article I will provide one such example: using a combination of AWS Simple Queue Services (SQS) — which are fully managed message queues for microservices and distributed systems plus AWS Lambda — which allows you to run code without thinking about servers to provide seamless queueing capabilities in the MySQL database itself.<\/p>\n

Essentially, you will be performing a three-step process so that you can enqueue and dequeue messages directly from within an Amazon Aurora MySQL cluster:<\/p>\n

    \n
  1. \n

    You will create an Amazon SQS Queue<\/em> that will be used to store messages as JSON payloads.<\/p>\n<\/li>\n

  2. \n

    You will create a Lambda Function<\/em> which will use the SQS API to send and receive messages from Amazon SQS.<\/p>\n<\/li>\n

  3. \n

    You will invoke the Amazon Lambda Function from within an Aurora MySQL Cluster using mysql.lambda_async<\/em>. <\/strong><\/p>\n

    <\/p>\n<\/li>\n<\/ol>\n

    Time to get started!<\/p>\n

    Create an SQS Queue<\/h2>\n

    The first step is to create the Amazon SQS queue which will store the messages or events. From within the AWS web console –<\/p>\n

      \n
    1. \n

      Navigate to Services<\/em> and type SQS. <\/em>Select Simple Queue Service<\/em>.<\/p>\n

      <\/p>\n<\/li>\n

    2. \n

      From the next page, click on Create New Queue<\/em>. If this is your first Amazon SQS queue, you may need to click on the Get Started Now<\/em> button instead.<\/p>\n

      \"\"<\/p>\n<\/li>\n

    3. \n

      Next, you will have to name your queue. In this example, I\u2019ll aptly name my queue best_queue_ever<\/em>. You will also need to choose the type of queue you wish to create.<\/p>\n

      You have two options for queue type: Standard Queue<\/em> and FIFO Queue<\/em>. If you prioritize queue performance over atomicity of queue payload operations, choose a Standard Queue<\/em> type. If you need strict atomicity of operations on your queue with reduced total throughout, choose a FIFO Queue<\/em> type. For this example, select a Standard Queue<\/em> type.<\/p>\n

      <\/p>\n<\/li>\n

    4. \n

      Next, click on Quick Create Queue<\/em> to create your SQS Queue. For this simple example, you don\u2019t have to specify any additional advanced configuration options.<\/p>\n

      <\/p>\n<\/li>\n

    5. \n

      At this point, your SQS Queue has been created and should appear in the SQS Service main page. You will need to remember the Queue URL<\/em> so that you can use it inside your Amazon Lambda function. Select the queue and copy & paste the queue URL to a text editor.<\/p>\n

      <\/p>\n

      The queue URL will be in the following format:<\/p>\n

      https:\/\/xxxxxxxx\/xxxxxxxx\/best_queue_ever<\/pre>\n<\/li>\n
    6. \n
      You will also need to assign permissions on who can read or write to the new SQS queue. With the queue still selected, navigate to the Permissions tab and click on the Add a Permission<\/em> button.<\/p>\n
      <\/p>\n
      On the next page, you can add permissions for the newly created queue. Select the Everybody<\/em> checkbox under Principal <\/em>and All SQS Actions<\/em> under Actions<\/em>. Note that this gives everybody permissions to do anything with this queue which is great for keeping the demo simple and straightforward, but in a real production environment, you should probably be more granular with permissions on your SQS queues.<\/p>\n
      <\/p>\n<\/li>\n
    7. \n
      As a final step for queue creation, click on the Add Permission<\/em> button to apply the new permissions on your SQS queue.<\/p>\n<\/li>\n<\/ol>\n
      Create the Lambda Function<\/h2>\n
      With the SQS Queue configured, now it\u2019s time to create two Amazon Lambda Functions which will be used to enqueue and dequeue messages from the SQS queue. You will later see how to get your Aurora MySQL cluster to invoke the Lambda functions and interact with the queue.<\/p>\n
        \n
      1. \n
        To create the Lambda functions, navigate to Services<\/em> and type lambda. <\/em>Then select Lambda<\/em>.<\/p>\n
        <\/p>\n<\/li>\n
      2. \n
        In the Lambda Functions<\/em> screen, click Create Function<\/em> to create the first Lambda function. This one will be used to enqueue messages, i.e., send messages, to the SQS Queue.<\/p>\n
        <\/p>\n<\/li>\n
      3. \n
        Choose Author from scratch<\/em>, as you do not need to use a blueprint for this particular Lambda function.<\/p>\n
        <\/p>\n<\/li>\n
      4. \n
        Enter the Lambda function name, I used sqs_enqueue<\/em> but feel free to be as creative as you\u2019d like as long as you stick with letters, numbers, hyphens, or underscores.<\/p>\n
        <\/p>\n<\/li>\n
      5. \n
        Select Create a custom role<\/em> under the Role<\/em> dropdown list. This will open the IAM Management Console in a new tab in your browser. When you create a Lambda function, you must assign permissions on which resources inside your AWS Account the Lambda function can access. Lambda Functions use an IAM role that grants your code permissions to access the AWS resources it needs.<\/p>\n<\/li>\n
      6. \n
        In the new IAM Management Console page, select Create a new IAM Role<\/em> and specify the role name, such as lambda_role1<\/em>.<\/p>\n
        <\/p>\n<\/li>\n
      7. \n
        Click on Allow<\/em> at the bottom right of the screen once finished to create your new IAM Role.<\/p>\n<\/li>\n
      8. \n
        Back to the Lambda Function Author from Scratch<\/em> page, click the Create Function<\/em> button so that you can write the Lambda Function code which will send messages to the SQS Queue.<\/p>\n<\/li>\n
      9. \n
        You will modify the following<\/a> reference code to fit the requirements. In the snippet below, replace https:\/\/QUEUE_URL <\/strong>with the queue URL of your SQS queue. Then use it to replace the code in the code window.<\/p>\n
        \"..\/Desktop\/Screen%20Shot%202018-05-16%20at%204.42.00%20PM.png\"<\/p>\n
        var QUEUE_URL = 'https:\/\/QUEUE_URL<\/strong>';\r\nvar AWS = require('aws-sdk');\r\nvar sqs = new AWS.SQS({region : 'us-east-1'});\r\nexports.handler = function(event, context) {\r\n  var params = {\r\n    MessageBody: JSON.stringify(event),\r\n    QueueUrl: QUEUE_URL\r\n  };\r\n  sqs.sendMessage(params, function(err,data){\r\n    if(err) {\r\n      console.log('error:',\"** Failed to write to queue **\" + err);\r\n      context.done('error', \"** ERROR writing to SQS **\");  \t\/\/ ERROR when trying to enqueue to SQS\r\n    }else{\r\n      console.log('data:',data.MessageId);\r\n      context.done(null,'');  \r\n\/\/ SUCCESS - message written to SQS\r\n    }\r\n  });\r\n}<\/pre>\n
        The Lambda Function is pretty straightforward:<\/p>\n