AWS SAM local with postgres

In this post we're going to create two super-basic lambda functions that read and write to a postgres table using AWS Severless Application Model (SAM), Docker(-compose) and postgres for fully local testing.

SAM + docker-compose enables fully local testing of lambda functions that interact with postgres (or anything else in docker-compose). Another great example of this is

SAM Local with Localstack

Vikas Garg ・ Mar 19 '20

#sam #localstack

Table of Contents

• Versions and Code
• Hello, Lambda
• Initializing Postgres
• Connecting the Lambda to Postgres
  • Basic SELECT
  • Basic INSERT
• Improvements

Versions and Code

$ sam --version
SAM CLI, version 0.45.0
$ docker --version
Docker version 19.03.8, build afacb8b
$ node --version
v12.13.1

The code for this post lives here:

martzcodes / sam-with-postgres

Hello, Lambda

Hello

In your project directory run sam init with the following options:

$ sam init

    SAM CLI now collects telemetry to better understand customer needs.

    You can OPT OUT and disable telemetry collection by setting the
    environment variable SAM_CLI_TELEMETRY=0 in your shell.
    Thanks for your help!

    Learn More: https://docs.aws.amazon.com/serverless-application-model/latest/developerguide/serverless-sam-telemetry.html

Which template source would you like to use?
    1 - AWS Quick Start Templates
    2 - Custom Template Location
Choice: 1

Which runtime would you like to use?
    1 - nodejs12.x
    2 - python3.8
    3 - ruby2.7
    4 - go1.x
    5 - java11
    6 - dotnetcore2.1
    7 - nodejs10.x
    8 - python3.7
    9 - python3.6
    10 - python2.7
    11 - ruby2.5
    12 - java8
    13 - dotnetcore2.0
    14 - dotnetcore1.0
Runtime: 1

Project name [sam-app]: sam-with-postgres

Cloning app templates from https://github.com/awslabs/aws-sam-cli-app-templates.git

-----------------------
Generating application:
-----------------------
Name: sam-with-postgres
Runtime: nodejs12.x
Dependency Manager: npm
Application Template: hello-world
Output Directory: .

Next steps can be found in the README file at ./sam-with-postgres/README.md

To verify it's running you can follow the directions in the README... generally I prefer to use sam local start-lambda and in another terminal...

$ sam local invoke HelloWorldFunction --event events/event.json
Invoking app.lambdaHandler (nodejs12.x)

Fetching lambci/lambda:nodejs12.x Docker container image..........................................................................................................................................................
Mounting /Users/mattmartz/Development/sam-postgres/sam-with-postgres/hello-world as /var/task:ro,delegated inside runtime container
START RequestId: cfad24b9-7ed9-1eac-d063-c0430d0c8862 Version: $LATEST
END RequestId: cfad24b9-7ed9-1eac-d063-c0430d0c8862
REPORT RequestId: cfad24b9-7ed9-1eac-d063-c0430d0c8862  Init Duration: 86.84 ms Duration: 3.04 ms   Billed Duration: 100 ms Memory Size: 128 MB Max Memory Used: 39 MB

{"statusCode":200,"body":"{\"message\":\"hello world\"}"}

Initializing Postgres

Now that we've got a basic HelloWorld lambda... let's get postgres up and running.

Let's create a docker-compose.yml file that includes:

version: '2'
services:
  sam-postgres-db:
    image: postgres:10.5-alpine
    container_name: sam-postgres-db
    environment:
      - POSTGRES_PASSWORD=martzcodesshouldhaveabetterpassword
    ports:
      - '5432:5432'
    networks:
      martzcodes:
    volumes:
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql

networks:
  martzcodes:

With an init.sql file that contains:

CREATE TABLE hello_table (
  id serial NOT NULL PRIMARY KEY,
  hello_source varchar(20),
  hello_target varchar(20)
);

INSERT INTO hello_table (hello_source, hello_target)
  VALUES ('Matt', 'Whit');

When you run docker-compose up -d postgres will install and initialize the postgres image with that sql script (this is a feature of the postgres image)... to verify you can check with docker exec -it sam-postgres-db psql -U postgres and then running select * from hello_table;

$ docker exec -it sam-postgres-db psql -U postgres
psql (10.5)
Type "help" for help.

postgres=# select * from hello_table;
 id | hello_source | hello_target 
----+--------------+--------------
  1 | Matt         | Whit
(1 row)

postgres=# \q

Connecting the Lambda to Postgres

🤔

Now let's get the lambda communicating with postgres. cd hello-world into the lambda directory and uninstall axios (we don't need it)... npm uninstall axios --save. Now install the node-postgres library using npm install pg --save.

Basic SELECT

Add node-postgres to our hello-world/app.js and connect to the db. My app.js file now looks like:

const { Client } = require("pg");
let response;

/**
...
*/
exports.lambdaHandler = async (event, context) => {
    const client = new Client({
      user: "postgres",
      host: "localhost",
      password: "martzcodesshouldhaveabetterpassword"
    });
    await client.connect();
    const res = await client.query("SELECT * from hello_table ORDER BY id DESC LIMIT 1");
    const hello = `${res.rows[0].hello_source} says hello to ${res.rows[0].hello_target}`;
    console.log(hello); // Shows "Matt says hello to Whit""
    await client.end();
    try {
        response = {
            'statusCode': 200,
            'body': JSON.stringify({
                message: hello,
            })
        }
    } catch (err) {
        console.log(err);
        return err;
    }

    return response
};

To test this, make sure postgres is still running (docker-compose up -d) and start the lambda (sam local start-lambda) and then you can invoke the lambda to get:

sam local invoke HelloWorldFunction --event events/event.json --docker-network host
Invoking app.lambdaHandler (nodejs12.x)

Fetching lambci/lambda:nodejs12.x Docker container image......
Mounting /Users/mattmartz/Development/sam-postgres/sam-with-postgres/hello-world as /var/task:ro,delegated inside runtime container
START RequestId: 08885bb9-71db-1ef7-fdfe-6421b6bbbf1a Version: $LATEST
2020-03-19T17:56:05.307Z    08885bb9-71db-1ef7-fdfe-6421b6bbbf1a    INFO    Matt says hello to Whit
END RequestId: 08885bb9-71db-1ef7-fdfe-6421b6bbbf1a
REPORT RequestId: 08885bb9-71db-1ef7-fdfe-6421b6bbbf1a  Init Duration: 163.82 ms    Duration: 16.32 ms  Billed Duration: 100 ms Memory Size: 128 MB Max Memory Used: 45 MB

{"statusCode":200,"body":"{\"message\":\"Matt says hello to Whit\"}"}

Basic INSERT

Let's make it a little more interesting and have a write to the database using the lambda event.

Tweak the body of events/event.json to be: "body": "{\"source\": \"whit\", \"target\": \"matt\"}",.

Then add a new resource to the template.yml file in the project root:

  SayFunction:
    Type: AWS::Serverless::Function # More info about Function Resource: https://github.com/awslabs/serverless-application-model/blob/master/versions/2016-10-31.md#awsserverlessfunction
    Properties:
      CodeUri: hello-world/
      Handler: app.sayHandler
      Runtime: nodejs12.x

Along with the corresponding code... I put mine in hello-world/app.js but I generally prefer to keep everything in separate files (and have them be typescript...).

exports.sayHandler = async (event, context) => {
  const client = new Client({
    user: "postgres",
    host: "localhost",
    password: "martzcodesshouldhaveabetterpassword"
  });
  await client.connect();
  const body = JSON.parse(event.body);
  const queryText =
    "INSERT INTO hello_table(hello_source, hello_target) VALUES($1, $2) RETURNING (hello_source, hello_target)";
  await client.query(queryText, [body.source, body.target]);
  await client.end();
  return exports.lambdaHandler(event, context);
};

Now restart sam local start-lambda and run the invoker for the SayFunction:

$ sam local invoke SayFunction --event events/event.json --docker-network host
Invoking app.sayHandler (nodejs12.x)

Fetching lambci/lambda:nodejs12.x Docker container image......
Mounting /Users/mattmartz/Development/sam-postgres/sam-with-postgres/hello-world as /var/task:ro,delegated inside runtime container
START RequestId: 1abdf1fa-7962-1589-7682-cf939c2cf391 Version: $LATEST
2020-03-19T18:20:02.749Z    1abdf1fa-7962-1589-7682-cf939c2cf391    INFO    whit says hello to matt
END RequestId: 1abdf1fa-7962-1589-7682-cf939c2cf391
REPORT RequestId: 1abdf1fa-7962-1589-7682-cf939c2cf391  Init Duration: 155.42 ms    Duration: 25.71 ms  Billed Duration: 100 ms Memory Size: 128 MB Max Memory Used: 46 MB

{"statusCode":200,"body":"{\"message\":\"whit says hello to matt\"}"}

If you docker exec -it sam-postgres-db psql -U postgres into the database again and select... you'll see the newly added row:

docker exec -it sam-postgres-db psql -U postgres
psql (10.5)
Type "help" for help.

postgres=# select * from hello_table;
 id | hello_source | hello_target 
----+--------------+--------------
  1 | Matt         | Whit
  2 | whit         | matt
(2 rows)

postgres=# \q

And since we set up the HelloFunction to return the last entry its response will now reflect the right row:

sam local invoke HelloWorldFunction --event events/event.json --docker-network host

Invoking app.lambdaHandler (nodejs12.x)

Fetching lambci/lambda:nodejs12.x Docker container image......

Mounting /Users/mattmartz/Development/sam-postgres/sam-with-postgres/hello-world as /var/task:ro,delegated inside runtime container

START RequestId: c6be69ec-8aa0-1cc4-5ae2-69f41c5dd836 Version: $LATEST

2020-03-19T18:22:24.383Z    c6be69ec-8aa0-1cc4-5ae2-69f41c5dd836    INFO    whit says hello to matt

END RequestId: c6be69ec-8aa0-1cc4-5ae2-69f41c5dd836

REPORT RequestId: c6be69ec-8aa0-1cc4-5ae2-69f41c5dd836  Init Duration: 183.15 ms    Duration: 18.55 ms  Billed Duration: 100 ms Memory Size: 128 MB Max Memory Used: 45 MB

{"statusCode":200,"body":"{\"message\":\"whit says hello to matt\"}"}

Improvements

For brevity I removed the tests 😳... maybe the topic of a future blog.

• Use environment variables for the database connections
• Make everything typescript
• Write integration tests