AWS SAM local with postgres
Tags: aws, serverless, docker, postgres
Published: 2020-03-19 18:57:05

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)](https://aws.amazon.com/serverless/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 {% link https://dev.to/vikasgarghb/sam-local-with-localstack-4285 %}

## Table of Contents <a name="toc"></a>

* [Versions and Code](#versions)
* [Hello, Lambda](#lambda)
* [Initializing Postgres](#postgres)
* [Connecting the Lambda to Postgres](#connecting)
  * [Basic SELECT](#select)
  * [Basic INSERT](#insert)
* [Improvements](#improvements)

## Versions and Code <a name="versions"></a>

```bash
$ 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:

{% github martzcodes/sam-with-postgres no-readme %}

## Hello, Lambda <a name="lambda"></a>

![hello](https://media.giphy.com/media/xT9IgG50Fb7Mi0prBC/giphy.gif)
<figcaption>Hello</figcaption>

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...

```bash
$ 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 <a name="postgres"></a>

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:

```yml
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:

```sql
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 <a name="connecting"></a>

![connecting](https://media.giphy.com/media/xT5LMXnJKjJHTUyCQ0/giphy.gif)
<figcaption>🤔</figcaption>

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](https://node-postgres.com/) library using `npm install pg --save`.

### Basic SELECT <a name="select"></a>

Add [node-postgres](https://node-postgres.com/) to our `hello-world/app.js` and connect to the db.  My app.js file now looks like:

```js
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:

```bash
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 <a name="insert"></a>

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:

```yml
  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...).

```js
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:

```bash
$ 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:

```bash
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:

```bash
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 <a name="improvements"></a>

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

![improvements](https://media.giphy.com/media/l2Je4zlfxF6z0IWZi/giphy.gif)
<figcaption></figcaption>

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