Free Tier Managed SQL with AWS Lambda and Heroku Postgres
January 6th, 2019
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 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:
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:
- Node version 8+ installed
- NPM installed (often comes with Node)
- AWS SDK installed
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
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
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"
serverless commands in the tutorial will be run from the app directory.
You’ll notice the
serverless.yml file contains the following:
You’ll also notice the
handler.js file contains an HTTP handler ready to go:
Deploy the App with Serverless
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:
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.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:
serverless deploy command again to deploy this new code. When you query your endpoint you should get the following JSON response:
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:
Now, your app is available when running locally at
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:
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:
In the modal that appears, select “Provision” with the free tier selected:
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:
You now need to run the
npm install pg command to install the PostgreSQL client NPM module so that the function can use it.
handler.js with the following, replacing the database credentials with the ones you obtained for your Heroku Postgres service:
There are a few points to explain in this code:
createConnfunction 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
pgPoolvariable 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:
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:
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
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
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:
A few important points about this code:
- Creating a connection is an asynchronous operation, so the
asynckeyword is added to the
createConnfunction, and it is
awaited in the handler.
- There is now an
app.postroute, 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:
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
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
View webhooks. Then, click
Create Webhook and choose a
Webhook Name name. In my example, I chose
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
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:
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:
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:
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:
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
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
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.