Connecting to SQL Server from AWS Lambda docker container

Published in

Globant

3 min readAug 27, 2021

Introduction

In recent years, more and more companies are deploying multi-cloud solutions. Some of our clients are looking for a cost-effective solution, and they want to connect some AWS services to Azure databases (or internal AWS IaaS virtual machines with Microsoft SQL Server Installed).

A serverless function service allows you to build and run applications and services without thinking about servers. On AWS this service is called Lambda Functions. You can check our article about Serverless applications with Azure Functions, Python and Docker.

In this article, I want to explain how to create a Python AWS Lambda docker image for connecting a compatible Microsoft SQL Server without dying in the effort.

Background

AWS provides some base Python Lambda Functions docker images. For this exercise, we will use the public.ecr.aws/lambda/python:3.9 (check https://hub.docker.com/r/amazon/aws-lambda-python ). This is an Amazon Linux 2 distribution image with the Python 3.9 runtime installed.

We want to use PyODBC, which is a Python module that makes accessing databases through the ODBC API simple, but our base image doesn't have installed the necessary gcc libraries and the SQL Server drivers.

Ingredients

Let's start creating the Python example files.

readme.txt with the pyodbc module

app.py

Replace lines 5 to 8 with your database information.

This is the dockerfile.

Line 1: Our base docker image.

Lines 2–3: Are our Python project example files.

Lines 7–13: Installs the Microsoft SQL Server ODBC drivers and unixODBC application (as Amazon Linux 2 is a CentOs based distribution, we use the Red Hat packages).

Line 14: Installs gcc application for compiling the pyodbc module.

Line 15: Clean yum cache.

Line 16: Updates python PIP and setuptools modules.

Line 17: Installs project python modules from requirements.txt

Line 19: Sets the CMD argument to specify the Lambda function handler.

Cooking the docker image

As we have all the necessary ingredients, we can cook (build) our docker image.

On a command-line shell window run the following command.

docker build --pull --rm -f "dockerfile" -t lambdasql:latest "."

You can replace lambdasql:lastest for the tag name that you want for your image.

In order to test your image, you can create a container running this command:

docker run -p 9000:8080 lambdasql:latest

Remember: change lambdasql:latest with your tag.

You can use Postman or your favorite HTTP client for calling the Lambda function. The URL is http://localhost:9000/2015-03-31/functions/function/invocations and you must add an empty JSON {} to the body.

The lambda function must respond with the SQL Server version, for example:

“Microsoft SQL Azure (RTM) — 12.0.2000.8 \n\tJul 23 2021 13:14:19 \n\tCopyright © 2019 Microsoft Corporation\n”

Remember that you must Tag your image to match your repository name, and deploy the image to Amazon ECR using the docker push command.

docker tag  lambdasql:latest 123456789012.dkr.ecr.us-east-1.amazonaws.com/lambdasql:latestdocker push 123456789012.dkr.ecr.us-east-1.amazonaws.com/lambdasql:latest

You can check the complete code from https://github.com/csotomon/python-lambda-sql

Conclusion

The provided AWS docker images provide just the basic implementation for running a Python application but is easy to build a new docker image with the necessary Linux applications and drivers for connecting to a SQL Server database.