Accessing SQL Server in AWS Lambda – Part 1

AWS Lambda is great, but I had many difficulties finding a way to connect into my RDS SQL Server instance. I started with Python and ODBC (both pyodbc and pypyodbc), but ran into so many issues with missing libraries (LD_LIBRARY_PATH of death).
I turned to Java, but I think SQL Server and Java are a bit like oil and water, so there wasn’t any one place to turn to. Instead I pieced together lots of bits of information and collated it together, into what I think is a pretty compact but extendable project. This is a three part post, guiding you through every step of the way. You can skip ahead to Part 2 if you are already familiar with basic Java project structure, Gradle, AWS IAM and have your AWS credentials already setup on your machine. You can also just go ahead and view the entire source of this project on my GitHub account. It also includes a few enhancements that aren’t covered, like multiple Lambda handlers in the one project.

Step 1: Pre-Reqs
Firstly I went and downloaded IntelliJ Community Edition. Next I went and created a new role in Identity and Access Management (IAM), this will be the access that your function has when it is executed. For this example I have named it simply ‘lambda’. Finally, I created a new user that will be used to upload the project, lazily giving it the AWSLambdaFullAccess policy. Importantly you need to record the access key and it’s secret for the next step. One last thing, if you don’t have Gradle and you aren’t using a Gradle wrapper (like the one in my GitHub project) you will need to go and grab that as well.

Step 2: Setup your Environment
Remember that key & secret? Create a text file named credentials in a folder ‘.aws’ in your home directory (e.g. C:\Users\Mannan\.aws\credentials). The format should be:

[default]
aws_access_key_id=AAAAAAAAAAAAAAA
aws_secret_access_key=BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

You can change the name from default to lambda if you prefer, but remember to change it layer in the build.gradle (more on what that is soon). You can also create a ‘config’ file in the same directory with similiar contents to this:

[default]
region=ap-southeast-2
output=json

However I’m pretty sure this is optional for this but might be handy in the future. Next create a folder where you want to develop your Lambda SQL Server application. Open a command prompt window and navigate to your new folder. Inside run ‘gradle init’, if it errors saying gradle is not recognised, you will have to reference the full path of where you extract gradle from step 1. E.g. c:\dev\tools\gradle-3.1\bin\gradle init. You should see some new files created, open the one named build.gradle and delete the multi-line comments /* and */. Finally below the apply plugin: ‘java’ add an apply plugin: ‘idea’. Then run ‘gradlew idea’. Note: you can use gradlew now instead of gradle as it has created a wrapper for your project.

Step 3: The Query Code
You can now double click on the .ipr file in that folder and IntelliJ should start up. Once loaded, right click on the top folder in the project view and select New Directory. In the popup, add src/main/java/com/mannanlive/query then select OK. On the newly created query folder right click again and select New File, Query.java and OK. This will be the interface for our query object that we will use to extract data.

package com.mannanlive.query;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public interface Query {
    String getQuery();
    List translate(ResultSet rowSet) throws SQLException;
}

Next repeat the New File step again but this time create TimeQuery.java.

package com.mannanlive.query;

import com.mannanlive.domain.TimeResponseClass;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TimeQuery implements Query {
    private int daysToAdd = 0;

    public TimeQuery(int daysToAdd) { this.daysToAdd = daysToAdd; }

    @Override
    public String getQuery() { return String.format("SELECT GETDATE() + %d;", daysToAdd); }

    @Override
    public List translate(ResultSet rowSet) throws SQLException {
        List result = new ArrayList();
        while (rowSet.next()) {
            result.add(new TimeResponseClass(rowSet.getDate(1)));
        }
        return result;
    }
}

The third file to create this time is going to be in src/main/java/com/mannanlive/domain and will be named TimeResponseClass.java.

package com.mannanlive.domain;

import java.sql.Date;

public class TimeResponseClass {
    private java.sql.Date currentTime;
    public TimeResponseClass() { }
    public TimeResponseClass(Date currentTime) { this.currentTime = currentTime; }
    public Date getCurrentTime() { return currentTime; }
    public void setCurrentTime(Date currentTime) { this.currentTime = currentTime; }
}

Finally you can run in the command prompt window ‘gradlew cleanIdea idea build’ and this will refresh your IntelliJ project and test everything is ok so far.

About the Author

Mannan

Mannan is a software engineering enthusiast and has been madly coding since 2002. When he isn't coding he loves to travel, so you will find both of these topics on this blog.

2 thoughts on “Accessing SQL Server in AWS Lambda – Part 1

Leave a Reply

Your email address will not be published. Required fields are marked *