Free Tier Managed SQL with AWS Lambda and Heroku Postgres

Free Tier Managed SQL with AWS Lambda and Heroku Postgres

If you want to use a managed SQL database instead of proprietary managed NoSQL databases like DynamoDB, while staying in a “free tier”, you can do that by connecting to Heroku Postgres from AWS Lambda. You’ll get 1M requests and 400,000 GB-seconds of compute time per month, and 10,000 PostgreSQL rows. This tutorial will show how.

Want to skip to the finished code? See mattwelke/serverless-managed-db-examples.

Serverless Architecture

Serverless architecture and FaaS (function-as-a-service) cloud products like AWS Lambda have established themselves as useful tools in web development. I’ve gotten a chance to play around with Serverless Framework, and many of the cloud hosts it supports, including AWS. I love the workflow and the scalability. But one pain point that I noticed was that when I was using a particular cloud provider, most tutorials and conventional wisdom I encountered recommended pairing their FaaS product with their managed NoSQL product.

For example, if I’m working with AWS, the common sales pitch is for me to pair Lambda with DynamoDB. If I’m using Google Cloud Platform, I’m recommended Cloud Datastore. There’s nothing inherently wrong with these database products. I’ve dabbled with these two examples and I’ve been impressed by their features. However, they have non-traditional data models that are hard to grok at first. They don’t treat relational data the same way an SQL database would, and even when used as a document store, there are issues with eventual consistency developers need to be aware of.

The Challenge - Using Previous Knowledge

A big part of being a productive developer is taking advantage of knowledge you already have. So, remembering my experience with SQL databases and the libraries built around them, I thought it would be a good idea to explore using an SQL database with AWS Lambda instead of DynamoDB. If I have another option in my tool belt for rapid prototyping within a free tier, I’m happy.

Additionally, I’m lazy and I’m frugal. Therefore, I don’t want to manage servers and I want to fall into a free tier while developing this example app. I only want to be expected to pay for function invocations and for my database if this were a real world app starting to get traffic.

I remember from previous experience that Heroku Postgres, the managed PostgreSQL service offered by Heroku, is publicly exposed. You can get your database credentials through their dashboard. An example looks like this:

Heroku Postgres credentials

Notice that the dashboard tells us that the credentials aren’t permanent. In the Heroku Postgres documentation, they tell you that if you’re connecting to Heroku Postgres from a non-Heroku app, you must handle the fact that they rotate the credentials periodically ourselves. This will be handled later in the tutorial after the connection is confirmed working with hard-coded credentials.

I also remember from previous experience that I was able to connect to my Heroku Postgres database using pgAdmin 4 with these credentials. That means that you can create a Node.js app hosted anywhere, including AWS Lambda, and use the client in the pg NPM package to connect to your database.

Start Building the App

Make sure you have an AWS account with access to an Access Key ID and Secret Access Key, and that your system has the following configured before starting:

Obtain your AWS Access Key ID and Secret Access key (ex. by logging in to the web management console), and set them in environment variables so that they will be available to the Serverless Framework tooling. For example, on Linux systems, in the .bashrc file:

export AWS_ACCESS_KEY_ID=<your-key-here>
export AWS_SECRET_ACCESS_KEY=<your-secret-key-here>

You will use Serverless Framework to make the app, since it makes it much easier to develop. You will be able to create the app’s boilerplate with one command and use one command to deploy it, which will perform all the steps needed to upload the code to AWS and set up API Gateway, etc. Run the following command to install it:

npm install -g serverless

Run the following command to create the app in the ./my-service directory:

serverless create --template aws-nodejs --path my-service

You should see output similar to:

Serverless: Generating boilerplate...
Serverless: Generating boilerplate in "/home/matt/my-service"
 _______                             __
|   _   .-----.----.--.--.-----.----|  .-----.-----.-----.
|   |___|  -__|   _|  |  |  -__|   _|  |  -__|__ --|__ --|
|____   |_____|__|  \___/|_____|__| |__|_____|_____|_____|
|   |   |             The Serverless Application Framework
|       |                           serverless.com, v1.35.1
 -------'

Serverless: Successfully generated boilerplate for template: "aws-nodejs"

All future serverless commands in the tutorial will be run from the app directory.

You’ll notice the serverless.yml file contains the following:

functions:
  hello:
    handler: handler.hello

You’ll also notice the handler.js file contains an HTTP handler ready to go:

module.exports.hello = async (event, context) => {
  return {
    statusCode: 200,
    body: JSON.stringify({
      message: 'Go Serverless v1.0! Your function executed successfully!',
      input: event,
    }),
  };

  // Use this code if you don't use the http event with the LAMBDA-PROXY integration
  // return { message: 'Go Serverless v1.0! Your function executed successfully!', event };
};

Deploy the App with Serverless

Run the serverless deploy command from the app directory. You should see output similar to:

Serverless: Packaging service...
Serverless: Excluding development dependencies...
Serverless: Creating Stack...
Serverless: Checking Stack create progress...
.....
Serverless: Stack create finished...
Serverless: Uploading CloudFormation file to S3...
Serverless: Uploading artifacts...
Serverless: Uploading service .zip file to S3 (387 B)...
Serverless: Validating template...
Serverless: Updating Stack...
Serverless: Checking Stack update progress...
.................
Serverless: Stack update finished...
Service Information
service: my-service
stage: dev
region: us-east-1
stack: my-service-dev
api keys:
  None
endpoints:
  None
functions:
  hello: my-service-dev-hello
layers:
  None

Behind the scenes, the AWS SDK was invoked to manage every AWS service needed to deploy the hello function. However, because the template only defined a Serverless Framework function and not an event for it, there was no configuration of API Gateway done to set up the function for HTTP access.

Change the section of the serverless.yml file referenced above to the following:

functions:
  hello:
    handler: handler.hello
    events:
      - http: GET hello

Then, run the serverless deploy command again and observe that an endpoint will now be available for your function. Contained in the command’s output should be similar to:

endpoints:
  GET - https://lpo949101b.execute-api.us-east-1.amazonaws.com/dev/hello

At this point, you can hit that endpoint and see a JSON response. If you do, then your setup is working and you can continue with the tutorial.

Using Express to Develop Better

The code that the Serverless template created for you is specific to AWS Lambda. There are nuances to working this way, such as knowing what is safe to return from that function and what isn’t. Furthermore, the fact that you’re returning data instead of working with res.send and res.json functions makes things hard to get used to if you’re already familiar with Express’s programming model. Chances are, if you found this blog post, you already know Express as well, so if you can take advantage of that prior knowledge, you should.

You can do that with the serverless-http npm package. It’s an adapter that lets you create an Express app, with middleware, routers, etc and export the app as your handler instead of the proprietary AWS Lambda style handler. At run time, everything will be converted over and it will just work.

Turn your Serverless Framework service into an NPM project by running the npm init -f command. Run the npm i express serverless-http command to install the packages you’ll need, and rearrange your handler.js file to the following:

// handler.js
'use strict';

const express = require('express');
const serverless = require('serverless-http');

const app = express();

app.get('/hello', async function (req, res) {
    res.json({
        result: 'Hello, World!',
    });
    return;
});

module.exports = {
    app,
    hello: serverless(app),
};

Run the serverless deploy command again to deploy this new code. When you query your endpoint you should get the following JSON response:

{
  "result": "Hello, World!"
}

This works because at the end of the day, you’re still exporting hello from your module, and the object being exported is still in the format AWS expects. The serverless-http adapter ensured this. Note: At the time of writing (Jan 6th, 2019), I am not aware of such adapter packages for other cloud providers such as Google Cloud.

One really cool thing about what you have right now is that the Express app itself is decoupled from AWS Lambda. Notice that the other object exported from the module is the app itself. This provides an opportunity to create a new entry point to use for local debugging. For example, you could use this:

// index.js
'use strict';

const { app } = require('./handler');

app.listen(3000, () => {
    console.info('Listening on port 3000.');
});

Now, your app is available when running locally at localhost:3000/hello.

If you run this in an IDE such as Visual Studio Code, you can test changes to your handler much quicker, not having to wait for the serverless deploy command to finish. You can also get a better debugging experience by using breakpoints and adding more console.logs. The interface between the Express app and the Serverless Framework is so simple (serverless(app), in your handler file) that it arguably doesn’t need to be tested, and you can develop locally trusting that when it’s time to deploy, things will just work.

Connecting the function to Heroku Postgres

Now it’s time to use the other existing knowledge you have - SQL. You must have a Heroku app created to be associated with the Heroku Postgres service you will use. Create your app and choose the United States region, which will put your database geographically near the AWS region used to deploy your functions:

Creating the Heroku app

You now need to create the Heroku Postgres resource. On your app’s “Resources” tab, under “Add-ons”, type postgres and when “Heroku Postgres” appears as an option, select it:

Selecting Heroku Postgres resource

In the modal that appears, select “Provision” with the free tier selected:

Selecting free tier Heroku Postgres

Click on your “Heroku Postgres” that appears in the list, and you’ll be brought to a screen with statistics such as the number of active connections and rows in the database right now. Click on the “Settings” tab and then the “View Credentials…” button.

You’ll need to note these credentials for modifying the Serverless Framework HTTP handler to connect to the database:

  • Host
  • Database
  • User
  • Password

You now need to run the npm install pg command to install the PostgreSQL client NPM module so that the function can use it.

Replace handler.js with the following, replacing the database credentials with the ones you obtained for your Heroku Postgres service:

// handler.js
'use strict';

const express = require('express');
const serverless = require('serverless-http');
const pg = require('pg');

const pgConfig = {
    max: 1,
    host: 'ec2-54-235-178-189.compute-1.amazonaws.com',
    user: 'qsbopjxcfgwilw',
    password: 'd5174fc1904863c0c009976fa08cf0ad6d1f15ad1e83f6cc8279a5ed1748fb62',
    database: 'd27dlg20nd9k2o',
    ssl: true,
};

let pgPool;

const app = express();

const createConn = () => {
    console.log('Creating PG connection.');
    pgPool = new pg.Pool(pgConfig);
};

const performQuery = async () => {
    const client = await pgPool.connect();
    const result = await client.query('SELECT now()');
    client.release();
    return result;
};

app.get('/hello', async function (req, res) {
    if (!pgPool) {
        // Cold start. Get Heroku Postgres creds and create pool.
        createConn();
    } else {
        console.log('Using existing PG connection.');
    }

    try {
        const result = await performQuery();

        res.json({
            result: `According to Heroku Postgres, the time is: ${result.rows[0].now}`,
        });
        return;
    } catch (e) {
        res.json({
            error: e.message,
        });
        return;
    }
});

module.exports = {
    app,
    hello: serverless(app),
};

There are a few points to explain in this code:

  • The createConn function is to exploit a way AWS Lambda works that allows you to keep a PostgreSQL TCP connection open for subsequent requests instead of every HTTP request having to create a fresh connection which would then be immediately closed. The pgPool variable is declared outside of the handler code, so it is kept around as long as the Lambda function is kept warm. It will only create a new connection for a “cold start”.
  • A connection pool is used so that, if AWS allows a particular instance of the container backing your function to accept two requests at the same time, one will block until the other finishes, and only one connection is opened. This is rare, but it can occur, and this prevents the Heroku Postgres connection limit (20 at once) from being exhausted.
  • Rather than talk about how to access logs on AWS, if an error is encountered, it becomes the body of a 200 response so you can see what’s going on without needing to know too much about AWS.

Deploy your update with the serverless deploy command again, and visit the endpoint in your web browser. You’ll get a response like:

{
    "result": "According to Heroku Postgres, the time is: Tue Jan 06 2019 06:32:13 GMT+0000 (UTC)",
}

You’ll also notice that there is now a connection open to your Heroku Postgres service (though, of course, no rows or tables yet) if you visit your Heroku dashboard:

Heroku Postgres open connection

This is a boring app, but I’m sure you could expand this further with your own ideas. I won’t go further in terms of adding more endpoints or doing more complex queries to Heroku Postgres, since the plumbing is all there.

Managing automatic rotation of Heroku Postgres credentials

The only remaining issue is to deal with the fact that every now and then, the Heroku Postgres credentials will change and your Lambda function would begin producing 500 error responses because it won’t be able to connect anymore. This is by design. You want the ability to rotate your heroku Postgres credentials in case you accidentally expose them publicly, and Heroku themselves will do this automatically if they experience a data breach.

Heroku’s platform deals with this automatically for you if you run your app on Heroku. They restart your dyno (aka container) and the newest credentials are present in an environment variable. Your app starts up just as it always would. However, your Lambda function has no idea when this rotation will happen. Heroku has webhooks for you to react to some events like an add-on being provisioned, but not when a credential rotation happens.

There are multiple ways to deal with this problem. Here, you’ll do it by adding an api:release webhook to your app to respond to your app’s release events. These are triggered automatically as a side effect of the automatic credential rotation. You can take advantage of this to add a handler for this webhook which will fetch the new Heroku Postgres credentials using the Heroku API and create a new PostgreSQL connection pool. Subsequent requests to your Lambda will be successful automatically. The only requests to your Lambda that may fail will be ones that occur while the credential rotation is in progress.

You will need a Heroku API key to do this. You can create a token specifically for this app using the Heroku CLI, or you can grab the API Key associated with your account by going into your account settings on the dashboard and noting the “API Key”. You will also need to note the ID of your Heroku Postgres service, available in the Heroku dashboard. For example, my app’s is postgresql-acute-28210.

Run the npm install axios pg-connection-string command to add an easy to use HTTP client with Promise support for your project. The pg-connection-string library provides an easy way to parse the PostgreSQL connection string that the Heroku API returns into the config object needed for the pg library. It’s just missing the max and ssl properties, which can be added to the config object before it’s used to create the connection pool.

Change the handler code to the following, including your Heroku API key:

// handler.js
'use strict';

const express = require('express');
const serverless = require('serverless-http');
const pg = require('pg');
const axios = require('axios');
const parsePgConnStr = require('pg-connection-string').parse;

const herokuApiKey = 'd261eecb-9962-4263-bb78-8715ee0ff087';
const herokuPostgresId = 'postgresql-acute-28210';
const herokuClient = axios.create({
    baseURL: 'https://api.heroku.com/',
    headers: {
        'Authorization': `Bearer ${herokuApiKey}`,
        'Accept': 'application/vnd.heroku+json; version=3',
    },
});

let pgConfig;
let pgPool;

const app = express();

const createConn = async () => {
    console.log('Creating PG connection.');

    const credsResponse = await herokuClient.get(`addons/${herokuPostgresId}/config`);
    const pgConnStr = credsResponse.data[0]['value'];

    pgConfig = {
        ...parsePgConnStr(pgConnStr), ...{
            max: 1,
            ssl: true,
        },
    };

    pgPool = new pg.Pool(pgConfig);
};

const performQuery = async () => {
    const client = await pgPool.connect();
    const result = await client.query('SELECT now()');
    client.release();
    return result;
};

app.get('/hello', async function (req, res) {
    if (!pgPool) {
        // Cold start. Get Heroku Postgres creds and create pool.
        await createConn();
    } else {
        console.log('Using existing PG connection.');
    }

    try {
        const result = await performQuery();

        res.json({
            result: `According to PostgreSQL, the time is: ${result.rows[0].now}`,
            pgConfigUsed: pgConfig,
        });
        return;
    } catch (e) {
        res.json({
            error: e.message,
        });
        return;
    }

});

app.post('/onrelease', async function (req, res) {
    // Get Heroku Postgres creds and replace pool with new one.
    await createConn();

    // Response with 2xx response so Heroku knows webhook was successful.
    // Response body doesn't matter.
    res.status(204).send();
});

module.exports = {
    app,
    hello: serverless(app),
};

A few important points about this code:

  • Creating a connection is an asynchronous operation, so the async keyword is added to the createConn function, and it is awaited in the handler.
  • There is now an app.post route, which will be used to wire up the Heroku webhook. The requests sent by the webhook will be handled by this Express route.
  • For demonstration purposes, the pgConfig is made a top level variable and is included in the GET route’s output so that you can see it changing later in the tutorial as you test how your Lambda reacts to credential rotation. Don’t do this in real world apps!

You’ll also need to change your serverless.yml file to include the following configuration for your handler:

functions:
  hello:
    handler: handler.hello
    events:
      - http: GET hello
      - http: POST onrelease

This looks a bit weird. What does “hello” have to do with “onrelease” in the real world? This is as short an example as possible. It involves the least configuration changes needed to make it work. Here, there’s still only one Lambda and one Serverless Framework handler called “hello”. Inside it, Express routing is used to handle both the GET request you made earlier and the POST request for the Heroku webhook.

In real world apps, feel free to separate this out into multiple Lambdas. If you do, you won’t be able to simply replace the pgPool variable in the same JavaScript file when the webhook fires. You’ll need to implement things differently, likely having the Heroku Postgres credentials passed into the first Lambda through environment variables, and having a second Lambda use the Heroku token or API key to react the webhook by fetching new credentials and then use the AWS SDK library to change the first Lambda’s configuration which will cause it to restart with fresh credentials.

Creating the Heroku webhook

Continuing with the single Lambda solution, you must now create the webhook. Go to your app’s dashboard on the Heroku website, and in the top right corner click on More -> View webhooks. Then, click Create Webhook and choose a Webhook Name name. In my example, I chose onrelease. The Payload URL is the URL of your new route in your handler. You don’t have that yet, because you haven’t deployed your changes. Run the serverless deploy command again, and note the POST endpoint in the command’s output. Mine was https://lpo949101b.execute-api.us-east-1.amazonaws.com/dev/onrelease. Use this as the Payload URL and click Add Webhook.

With the webhook created, you can now test that your serverless app will continue working even after Heroku Postgres credentials are rotated. Hit your GET endpoint in your web browser, and you will see output similar to the following:

{
  "result": "According to PostgreSQL, the time is: Sat Jan 12 2019 23:43:23 GMT+0000 (UTC)",
  "pgConfigUsed": {
    "port": "5432",
    "host": "ec2-23-21-86-22.compute-1.amazonaws.com",
    "database": "dbcan7kct6ee8i",
    "user": "xfyxcnhjywzkkz",
    "password": "a8d483ea04548288097efd3387eda6b11e432d1825b8b456ef5d99d1d5c52010",
    "max": 1,
    "ssl": true
  }
}

Then, use the following Heroku CLI command to manually rotate your Heroku Postgres credentials, which will fire off that webhook. Replace your app name and Heroku Postgres resource name in the command with yours:

heroku pg:credentials:rotate postgresql-acute-28210 -a serverless-sql-example --confirm serverless-sql-example

Finally, hit the GET endpoint in your web browser again, and after a few seconds, the output should change to something similar to the following, where the credentials in the pgConfigUsed will have changed:

{
  "result": "According to PostgreSQL, the time is: Sat Jan 12 2019 23:44:59 GMT+0000 (UTC)",
  "pgConfigUsed": {
    "port": "5432",
    "host": "ec2-23-21-86-22.compute-1.amazonaws.com",
    "database": "dbcan7kct6ee8i",
    "user": "zrjuxhcqwmqnbz",
    "password": "601e58b691e527d5e4c3bd16db4866abf4d3a1c782179ab21fc8dd5b7f471d68",
    "max": 1,
    "ssl": true
  }
}

You can also verify that your webhook fired by viewing the webhooks page in your Heroku app’s dashboard. There will be a list of your app’s webhooks. If you select the webhook you created (called onrelease), you’ll see the times it fired:

onrelease webhook firing list showing it fired

If you click on a particular webhook firing event in the list, you can see details including what data was sent to your app when it fired:

onrelease webhook firing details

In this case, the data in the request body is irrelevant, because it doesn’t contain the new Heroku Postgres credentials anyways. The webhook handler route reacts to the request, regardless of what’s in its body, by using the Heroku API to fetch the new credentials it needs to create a new pgPool.

Your app is ready to go into production once you take care of hiding your Heroku token or API key in an environment variable so that people seeing your app’s source code won’t gain access to your Heroku account, and of course you no longer respond with your pgConfig object in the handler.

If you are comfortable with every route being inside a single Lambda, you can continue developing from this starter by adding more routes to your Express app. The webhook handling route will prepare a new pgPool for any other route in the function. An advantage of a single Lambda design is that any of the routes in your app can share one pgPool and it will be more likely that all routes will be handled by a Lambda that is kept warm (to avoid cold starts), since the HTTP traffic will be spread among them but contained in one Lambda.

Examples and Other Cloud Product Combinations

The completed code for this tutorial can be found in the mattwelke/serverless-managed-db-examples GitHub repository in the aws-lambda-heroku-postgres directory.

One reason I like the idea of this particular pairing of FaaS product and managed database product is that if you can’t afford an extensive DevOps team, you get so much for your money. AWS and Heroku aren’t paying me to say this - I just think it makes sense. AWS Lambda will scale considerably high before billing kicks in. When you want more than 10,000 PostgreSQL rows, you can get 10,000,000 rows for USD$9/mo. Later, you can bump it up to “4 GB RAM, 64 GB storage, 120 connections” for USD$50/mo. Complete plan details can be found here. Unless you’re running a big data type system, and as long as you’re using object storage products like AWS S3 instead of storing binary data in the database, that’s more than enough to build something great.

Because this pattern extends to all managed database products, other examples with different combinations of FaaS products and databases are possible. For example, another option for a managed SQL database would be connecting to AWS RDS from AWS Lambda. There are also smaller managed SQL database providers out there like jawsDB and Aiven. I don’t endorse these providers, I’m just aware of them.

If a provider allows you to host the managed database in a particular cloud, this also gives you the option to work with other FaaS products like Google Cloud, Azure Functions, or IBM Cloud Functions, because you’ll be able to run the FaaS product and the managed database geographically close to each other to achieve good performance. Check out the other directories in the examples repository to see them. Note that any credentials you see hard-coded in this tutorial or in the examples repository are rotated out and will not work for you.

Cleanup Cloud Services

To delete all the AWS resources created during the tutorial, run the serverless remove command. To delete the Heroku resources, visit the Heroku dashboard in your web browser and delete the Heroku app you created.