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: