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.
Share this: