As I discussed in a previous post, connecting to MSSQL with Python in Lambda can be tricky. While I had solved this earlier with pyodbc, using pymssql / FreeTDS is an another option and I wanted to similarly share the build process for creating its respective Lambda layer.
Here’s the multi-stage Dockerfile:
FROM public.ecr.aws/lambda/python:3.9 as builder
ENV FREETDS_VERSION=1.3.9
ENV INSTALLDIR='/tmp/freetds'
RUN yum update -y
RUN yum install wget tar gzip zip gcc make gcc gcc-c++ python39-devel unixODBC-devel -y
RUN mkdir $INSTALLDIR build
RUN wget ftp://ftp.freetds.org/pub/freetds/stable/freetds-${FREETDS_VERSION}.tar.gz && \
tar -xzf freetds-${FREETDS_VERSION}.tar.gz && \
cd freetds-${FREETDS_VERSION} && \
./configure --prefix=${INSTALLDIR} --with-tdsver=7.3 && \
make && \
make install
ENV CPPFLAGS="-I/usr/include/python3.9m"
RUN pip install --upgrade pip
RUN mkdir /opt/python/ && pip install pymssql -t /opt/python
FROM public.ecr.aws/lambda/python:3.9
COPY --from=builder /opt/python /opt/python
Lambda Layer
Similar to the previous post, you can extract the dependencies to your local file system by building the image and running the copy command.
docker build -t pymssql-3-9 . docker run --rm --entrypoint bash -v $PWD:/local pymssql-3-9 -c "cp -R /opt /local"
Everything needed to build and deploy the layer is now in ./opt.
Local Test
We can test this locally with an MSSQL container. Let’s first add a local app.py script to serve as the handler in the Lambda container above:
import os
import pymssql
DB_HOST = "db"
DB_USER = "sa"
DB_PASSWORD = os.getenv("SA_PASSWORD")
DB_NAME = "master"
def handler(event, context=None):
sql = "SELECT @@VERSION"
with pymssql.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) as conn:
with conn.cursor() as cursor:
cursor.execute(sql)
return str(list(cursor)[0])
return False
These can be wired up together in a docker-compose.yml like this:
version: "3.9"
services:
db:
image: mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04
ports:
- "1433:1433"
environment:
SA_PASSWORD: "Your_password123"
ACCEPT_EULA: "Y"
lambda:
ports:
- "9000:8080"
environment:
SA_PASSWORD: "Your_password123"
volumes:
- "$PWD/app.py:/var/task/app.py"
command:
- app.handler
build:
context: .
dockerfile: ./Dockerfile
depends_on:
- db
invoke:
depends_on:
- lambda
image: alpine/curl:3.14
entrypoint: >
/bin/sh -c "sleep 10 && curl -X POST 'http://lambda:8080/2015-03-31/functions/function/invocations' -d '{}'"
After 10 seconds, the invoke container will run an HTTP POST against the Lambda handler. You should see output like this:
lambda_1 | START RequestId: 9f5d1fcc-280a-4a36-8d00-bb192b9e58c6 Version: $LATEST
lambda_1 | END RequestId: 9f5d1fcc-280a-4a36-8d00-bb192b9e58c6
lambda_1 | REPORT RequestId: 9f5d1fcc-280a-4a36-8d00-bb192b9e58c6 Init Duration: 0.21 ms Duration: 89.99 ms Billed Duration: 90 ms Memory Size: 3008 MB Max Memory Used: 3008 MB
100 202 100 200 100 2 2185 21 --:--:-- --:--:-- --:--:-- 2195
invoke_1 | "('Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) \\n\\tSep 24 2019 13:48:23 \\n\\tCopyright (C) 2019 Microsoft Corporation\\n\\tDeveloper Edition (64-bit) on Linux (Ubuntu 16.04.6 LTS) <X64>',)"python3-9-pymssql_invoke_1 exited with code 0
As you can see, the the Lambda is able to successfully connect to the MSSQL container and run the SELECT @@VERSION query.
