Create a data API for TimescaleDB

This tutorial covers creating an API to fetch data from your TimescaleDB instance. It uses an API gateway to trigger a Lambda function, that then fetches the requested data from TimescaleDB and returns it in JSON format.

Connect to TimescaleDB from Lambda

To connect to the TimescaleDB instance, you need to use a database connector library. This tutorial uses psycopg2.

The psycopg2 database connector is not part of the standard Python library, and is not included in AWS Lambda, so you need to manually include the library in your deployment package to make it available to use. This tutorial uses Lambda Layers to include psycopg2. A Lambda Layer is an archive containing additional code, such as libraries or dependencies. Layers help you use external libraries in your function code that would not be available otherwise.

Additionally, psycopg2 needs to be built and compiled with statically linked libraries, something that you can’t do directly in a Lambda function or layer. A workaround to this issue is to download the compiled version of the library and use that as a Lambda Layer.

Adding the psycopg2 library as a Lambda layer

  1. Download and unzip the compiled psycopg2 library:

    1. wget https://github.com/jkehler/awslambda-psycopg2/archive/refs/heads/master.zip
    3. unzip master.zip

  2. In the directory you downloaded the library to, copy the psycopg2 files into a new directory called python. Make sure you copy the directory that matches your Python version:

    1. cd awslambda-psycopg2-master/
    3. mkdir python
    5. cp -r psycopg2-3.8/ python/

  3. Zip the python directory and upload the zipped file as a Lambda layer:

    1. zip -r psycopg2_layer.zip python/
    3. aws lambda publish-layer-version --layer-name psycopg2 \
    5. --description "psycopg2 for Python3.8" --zip-file fileb://psycopg2_layer.zip \
    7. --compatible-runtimes python3.8

  4. At the AWS Lambda console, check to see if your psycopg2 has been uploaded as a Lambda layer:

    aws layers

Create a function to fetch and return data from the database

When the layer is available to your Lambda function, you can create an API to return data from the database. This section shows you how to create the Python function that returns data from the database and uploads it to AWS Lambda.

Creating a function to fetch and return data from the database

  1. Create a new directory called timescaledb_api, to store the function code, and change into the new directory:

    1. mkdir timescaledb_api
    3. cd timescaledb_api

  2. In the new directory, create a new function called function.py, with this content:

    1. import json
    3. import psycopg2
    5. import psycopg2.extras
    7. import os
    9. def lambda_handler(event, context):
    11.   db_name = os.environ['DB_NAME']
    13.   db_user = os.environ['DB_USER']
    15.   db_host = os.environ['DB_HOST']
    17.   db_port = os.environ['DB_PORT']
    19.   db_pass = os.environ['DB_PASS']
    21.   conn = psycopg2.connect(user=db_user, database=db_name, host=db_host,
    23.                        password=db_pass, port=db_port)
    25.   sql = "SELECT * FROM stocks_intraday"
    27.   cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    29.   cursor.execute(sql)
    31.   result = cursor.fetchall()
    33.   list_of_dicts = []
    35.   for row in result:
    37.     list_of_dicts.append(dict(row))
    39.   return {
    41.    'statusCode': 200,
    43.    'body': json.dumps(list_of_dicts, default=str),
    45.    'headers': {
    47.        "Content-Type": "application/json"
    49.        }
    51.   }

Upload the function in AWS Lambda

When you have created the function, you can zip the Python file and upload it to Lambda using the create-function AWS command.

Uploading the function to AWS Lambda

  1. At the command prompt, zip the function directory:

    1. zip function.zip function.py

  2. Upload the function:

    1. aws lambda create-function --function-name simple_api_function \
    3. --runtime python3.8 --handler function.lambda_handler \
    5. --role <ARN_LAMBDA_ROLE> --zip-file fileb://function.zip \
    7. --layers <LAYER_ARN>

  3. You can check that the function has been uploaded correctly by using this command in the AWS console:

    aws lambda uploaded

  1. If you make changes to your function code, you need to zip the file again and use the update-function-code command to upload the changes:

    1. zip function.zip function.py
    3. aws lambda update-function-code --function-name simple_api_function --zip-file fileb://function.zip

Add database configuration to AWS Lambda

Before you can use the functions, you need to ensure it can connect to the database. In the Python code above, you specified retrieving values from environment variables, and you also need to specify these within the Lambda environment.

Adding database configuration to AWS Lambda with environment variables

  1. Create a JSON file that contains the variables required for the function:

    1. {
    3.     "Variables": {"DB_NAME": "db",
    5.       "DB_USER": "user",
    7.       "DB_HOST": "host",
    9.       "DB_PORT": "5432",
    11.       "DB_PASS": "pass"}
    13. }

  2. Upload your connection details. In this example, the JSON file that contains the variables is saved at file://env.json:

    1. aws lambda update-function-configuration \
    3. --function-name simple_api_function --environment file://env.json

  3. When the configuration is uploaded to AWS Lambda, you can reach the variables using the os.environ parameter in the function:

    1. import os
    3. config = {'DB_USER': os.environ['DB_USER'],
    5.       'DB_PASS': os.environ['DB_PASS'],
    7.       'DB_HOST': os.environ['DB_HOST'],
    9.       'DB_PORT': os.environ['DB_PORT'],
    11.       'DB_NAME': os.environ['DB_NAME']}

Test the database connection

When your function code is uploaded along with the database connection details, you can check to see if it retrieves the data you expect it to.

Testing the database connection

  1. Invoke the function. Make sure you include the name of the function, and provide a name for an output file. In this example, the output file is called output.json:

    1. aws lambda invoke --function-name simple_api_function output.json

  2. If your function is working correctly, your output file looks like this:

    1. {
    3.   "statusCode": 200,
    5.   "body": "[
    7.             {
    9.               \"bucket_day\": \"2021-02-01 00:00:00\",
    11.               \"symbol\": \"AAPL\",
    13.               \"avg_price\": 135.32576933380264,
    15.               \"max_price\": 137.956910987,
    17.               \"min_price\": 131.131547781
    19.             },
    21.             {
    23.               \"bucket_day\": \"2021-01-18 00:00:00\",
    25.               \"symbol\": \"AAPL\",
    27.               \"avg_price\": 136.7006897398394,
    29.               \"max_price\": 144.628477898,
    31.               \"min_price\": 126.675666886
    33.             },
    35.             {
    37.               \"bucket_day\": \"2021-05-24 00:00:00\",
    39.               \"symbol\": \"AAPL\",
    41.               \"avg_price\": 125.4228325920157,
    43.               \"max_price\": 128.32,
    45.               \"min_price\": 123.21
    47.             },
    49.             ...
    51.           ]",
    53.   "headers": {
    55.   "Content-Type": "application/json"
    57.   }
    59. }

Create a new API gateway

Now that you have confirmed that the Lambda function works, you can create the API gateway. In AWS terms, you are setting up a custom Lambda integration.

Creating a new API gateway

  1. Create the API. In this example, the new API is called TestApiTimescale. Take note of the id field in the response, you need to use this to make changes later on:

    1. aws apigateway create-rest-api --name 'TestApiTimescale' --region us-east-1
    3. {
    5.   "id": "4v5u26yw85",
    7.   "name": "TestApiTimescale2",
    9.   "createdDate": "2021-08-23T13:21:13+02:00",
    11.   "apiKeySource": "HEADER",
    13.   "endpointConfiguration": {
    15.       "types": [
    17.           "EDGE"
    19.       ]
    21.   },
    23.   "disableExecuteApiEndpoint": false
    25. }

  2. Retrieve the id of the root resource, to add a new GET endpoint:

    1. aws apigateway get-resources --rest-api-id <API_ID> --region us-east-1
    3. {
    5.   "items": [
    7.     {
    9.         "id": "hs26aaaw56",
    11.         "path": "/"
    13.     },
    15.     {
    17.         "id": "r9cakv",
    19.         "parentId": "hs26aaaw56",
    21.         "pathPart": "ticker",
    23.         "path": "/ticker",
    25.         "resourceMethods": {
    27.             "GET": {}
    29.         }
    31.     }
    33.   ]
    35. }

  3. Create a new resource. In this example, the new resource is called ticker:

    1. aws apigateway create-resource --rest-api-id <API_ID> \
    3. --region us-east-1 --parent-id <RESOURCE_ID> --path-part ticker
    5. {
    7.   "id": "r9cakv",
    9.   "parentId": "hs26aaaw56",
    11.   "pathPart": "ticker",
    13.   "path": "/ticker"
    15. }

  4. Create a GET request for the root resource:

    1. aws apigateway put-method --rest-api-id <API_ID> \
    3. --region us-east-1 --resource-id <RESOURCE_ID> \
    5. --http-method GET --authorization-type "NONE" \
    7. --request-parameters method.request.querystring.symbol=false

  5. Set up a 200 OK response to the method request of GET /ticker?symbol={symbol}:

    1. aws apigateway put-method-response --region us-east-1 \
    3. --rest-api-id <API_ID> --resource-id <RESOURCE_ID> \
    5. --http-method GET --status-code 200

  6. Connect the API Gateway to the Lambda function:

    1. aws apigateway put-integration --region us-east-1 \
    3. --rest-api-id <API_ID> --resource-id <RESOURCE_ID> \
    5. --http-method GET --type AWS --integration-http-method POST \
    7. --uri <ARN_LAMBDA_FUNCTION> \
    9. --request-templates file://path/to/integration-request-template.json

  7. Pass the Lambda function output to the client as a 200 OK response:

    1. aws apigateway put-integration-response --region us-east-1 \
    3. --rest-api-id <API_ID> / --resource-id <RESOURCE_ID> \
    5. --http-method GET --status-code 200 --selection-pattern ""

  8. Deploy the API:

    1. aws apigateway create-deployment --rest-api-id <API_ID> --stage-name test

Test the API

You can test the API is working correctly by making a GET request to the endpoint with curl:

  2. curl 'https://hlsu4rwrkl.execute-api.us-east-1.amazonaws.com/test/ticker?symbol=MSFT'
  4. [
  6.    {
  8.      "time": "2021-07-12 20:00:00",
  10.      "price_open": 277.31,
  12.      "price_close": 277.31,
  14.      "price_low": 277.31,
  16.      "price_high": 277.31,
  18.      "trading_volume": 342,
  20.      "symbol": "MSFT"
  22.    }
  24. ]

If everything is working properly, you see the output of the Lambda function. In this example, it’s the latest stock price of MSFT (Microsoft) in JSON format.

Create a Lambda function to insert data into the database

When you have created the GET API for your database, you can create a POST API. This allows you to insert data into the database with a JSON payload.

Creating a Lambda function to insert data into the database

  1. Create a new function called insert_function.py, with this content:

    1. import json
    3. import psycopg2
    5. import psycopg2.extras
    7. from psycopg2.extras import execute_values
    9. import os
    11. from typing import Dict
    13. def lambda_handler(event, context):
    15.     db_name = os.environ['DB_NAME']
    17.     db_user = os.environ['DB_USER']
    19.     db_host = os.environ['DB_HOST']
    21.     db_port = os.environ['DB_PORT']
    23.     db_pass = os.environ['DB_PASS']
    25.     conn = psycopg2.connect(user=db_user, database=db_name, host=db_host,
    27.                           password=db_pass, port=db_port)
    29.     cursor = conn.cursor()
    31.     sql = "INSERT INTO stocks_intraday VALUES %s"
    33.     records = json.loads(event["body"]).get("records")
    35.     if  isinstance(records, Dict):
    37.         values = [[value for value in records.values()], ]
    39.     else:
    41.         values = [[value for value in item.values()] for item in records]
    43.     execute_values(cursor, sql, values)
    45.     conn.commit()
    47.     conn.close()
    49.     return {
    51.         'statusCode': 200,
    53.         'body': json.dumps(event, default=str),
    55.         'headers': {
    57.             "Content-Type": "application/json"
    59.             }
    61.     }

  2. Upload the function to AWS Lambda:

    1. zip insert_function.zip insert_function.py
    3. aws lambda create-function --function-name insert_function \
    5. --runtime python3.8 --handler function.lambda_handler \
    7. --role <ARN_LAMBDA_ROLE> --zip-file fileb://insert_function.zip

  3. Create a new API Gateway, called InsertApi:

    1. aws apigateway create-rest-api --name 'InsertApi' --region us-east-1

  4. Retrieve the id of the root resource, and add a new POST endpoint:

    1. aws apigateway get-resources --rest-api-id <API_ID> --region us-east-1
    3. {
    5.   "items": [
    7.     {
    9.         "id": "hs26aaaw56",
    11.         "path": "/"
    13.     },
    15.     {
    17.         "id": "r9cakv",
    19.         "parentId": "hs26aaaw56",
    21.         "pathPart": "ticker",
    23.         "path": "/ticker",
    25.         "resourceMethods": {
    27.             "GET": {}
    29.         }
    31.     }
    33.   ]
    35. }

  5. Create a new resource. In this example, the new resource is called insert:

    1. aws apigateway create-resource --rest-api-id <API_ID> --region us-east-1
    3. --parent-id <RESOURCE_ID> --path-part insert
    5. {
    7.     "id": "arabc2",
    9.     "parentId": "r5fc0ufn0h",
    11.     "pathPart": "insert",
    13.     "path": "/insert"
    15. }

  6. Create a POST request for the insert resource:

    1. aws apigateway put-method --rest-api-id <API_ID> --region us-east-1 \
    3. --resource-id <RESOURCE_ID> --http-method POST --authorization-type "NONE"

  7. Set up a 200 OK response to the method request of POST /insert:

    1. aws apigateway put-method-response --region us-east-1 \
    3. --rest-api-id <API_ID> --resource-id <RESOURCE_ID> \
    5. --http-method POST --status-code 200

  8. Connect the API Gateway to the Lambda function:

    1. aws apigateway put-integration --region us-east-1 \
    3. --rest-api-id <API_ID> --resource-id <RESOURCE_ID> \
    5. --http-method POST --type AWS --integration-http-method POST \
    7. --uri <ARN_LAMBDA_FUNCTION> \
    9. --request-templates '{ "application/json": "{\"statusCode\": 200}" }'

  9. Pass the Lambda function output to the client as a 200 OK response:

    1. aws apigateway put-integration-response --region us-east-1 \
    3. --rest-api-id <API_ID> / --resource-id <RESOURCE_ID> \
    5. --http-method POST --status-code 200 --selection-pattern ""

  10. Deploy the API:

    1. aws apigateway create-deployment --rest-api-id <API_ID> --stage-name test_post_api

Test the API with a JSON payload

You can test the API by making a POST request with a JSON payload.

Create a new payload file, called post.json:

  2. {
  4.     "records": [
  6.         {
  8.             "time": "2021-11-12 15:00:00",
  10.             "symbol": "AAPL",
  12.             "price_open": 149.8,
  14.             "price_close": 149.81,
  16.             "price_low": 149.73,
  18.             "price_high": 149.73,
  20.             "trading_volume": 17291
  22.         },
  24.         {
  26.             "time": "2021-11-12 15:00:00",
  28.             "symbol": "MSFT",
  30.             "price_open": 337.15,
  32.             "price_close": 337.15,
  34.             "price_low": 337.15,
  36.             "price_high": 337.15,
  38.             "trading_volume": 562
  40.         },
  42.         {
  44.             "time": "2021-11-12 15:00:00",
  46.             "symbol": "FB",
  48.             "price_open": 341.35,
  50.             "price_close": 341.3,
  52.             "price_low": 341.3,
  54.             "price_high": 341.35,
  56.             "trading_volume": 556
  58.         }
  60.     ]
  62. }

Use curl to make the request:

  2. curl -X POST -H "Content-Type: application/json" -d @./post.json
  4. https://h45kwepq8g.execute-api.us-east-1.amazonaws.com/test_post_api/insert_function

If everything is working properly, the content of your JSON payload file gets inserted into the database.

timesymbolprice_openprice_highprice_lowprice_closetrading_volume
2021-11-12 21:00:00AAPL149.8149.73149.73149.8117291
2021-11-12 21:00:00MSFT337.15337.15337.15337.15562
2021-11-12 21:00:00FB341.35341.35341.3341.3556