Accessing SQL Server in AWS Lambda – Part 3

Accessing SQL Server in AWS Lambda – Part 3

This is Part 3 on a series detailing everything you need to get going on your serverless architecture whilst using your existing SQL Server database. View the source on GitHub or checkout Parts 1 & 2 if you haven’t already.

Step 6: Handlers
We haven’t done anything related to AWS Lambda so no wonder you are still confused. So now we have mastered connecting to your SQL Server (even on RDS) in Part 2, we now need to be able to handle Lambda events.
To do this, add a new block of code above dependencies to your build.gradle, defining provided as a type of dependency and then add the following to your dependency list: provided ‘com.amazonaws:aws-lambda-java-core:1.0.0’. It should look something like this now:

Again refresh your idea module with ‘gradlew cIM iM’ (last time I promise).

We then need to be able to receive JSON data posted the Lambda event, this is done using AWS serialisation, so all we need to do is create a plain old Java object that matches the fields in the expected payload. So let’s create another Java class in your existing domain package, RequestClass.java:

Now, when we send {“value”:”1″} to our Lambda function, we will expect it to query our SQL Server database for the current date plus one day, then return this to the caller as {“currentTime”:”2016-10-09″}. Not that impressive for all that work, but you could expand this to do any data access queries and scale as large as your SQL Server can handle.

Step 7: Deploying to Lambda
In your build.gradle, at the very top, add the following

This is doing three things. Firstly, when gradle produces a .jar file with your code, it will suck up everything into a “fat jar” including any libraries you use. This is needed because we want to include the Microsoft SQL Server driver jar. Seconly it is telling the AWS plugin what credentials to use. Now this is where you have to remember the steps you did in Part 1, if you changed the credentials file to something other than default, you need to change it here. The final part is defining a new taks of type Lambda migration. You can change the name and timeout here and likewise if you changed the role name in IAM to something other than “lambda” then you have to change it here also. Once this is correct, you can run ‘gradlew migrateTimeQuery’ and it will push your code to AWS. Hopefully it looked a little like this:

Step 8: Testing your Lambda
Now you can go into the AWS Console and view your Lambda Functions. Click on query-date and then Test. You can try with any JSON and it should come back with the current date. You can also pass in {“value”:”-1″} and it should returns yesterdays. Success!
If you get a timeout issue, like I did, I had to go to Configuration tab, Advanced Settings, and selected a VPC, Subnets and Security Group that I had that allowed access to the RDS database.

Let me know if you found this tutorial useful, or you have any issues in the comments below.