I am trying to connect to an AWS postgres database using either N8N hosted or N8N cloud hosted on Hostinger to upload/insert a CSV into a Postgres Database/table on a scheduled basis pulling from an SFTP site.
I read in the N8N documention that there were limitations to only connecting to a local/self hosted N8N and postgres Databse.
Then I saw these posts:
Could someone help with a few more details on how to do this and can it be done before I go down the route of having to create a Lamda Function, etc.?
To connect to an Amazon RDS PostgreSQL database using an HTTP API request, you’ll need to leverage the RDS Data API. This API provides an HTTP endpoint for executing SQL statements on your PostgreSQL database. You can then use a server-side framework (like Node.js with pg module, Django with psycopg2 , or Flask with SQLAlchemy) to handle the HTTP requests and interact with the database via the Data API.
Here’s a breakdown of the process:
- Enable RDS Data API:
- Navigate to the RDS console in the AWS Management Console.
- Select your PostgreSQL DB instance.
- Click “Modify” and then “Enable RDS Data API”.
- Save the changes.
- Create an API Endpoint and IAM Role (if needed):
- If you haven’t already, create an AWS API Gateway API.
- Create a Lambda function to handle the API requests.
- Set up an IAM role for the Lambda function with permissions to access the RDS Data API and Secrets Manager (if using stored credentials).
- Code the Lambda Function (Example using Python):
import boto3
import json
rds_data_api = boto3.client(‘rds-data’)
def lambda_handler(event, context):
try:
Get the SQL query from the API request
sql_query = event[‘queryStringParameters’][‘query’]
Get the database credentials from Secrets Manager (if applicable)
Example assuming credentials are stored in a secret named “my-rds-secret”
secrets_manager = boto3.client(‘secretsmanager’)
secret_value_response = secrets_manager.get_secret_value(SecretId=“my-rds-secret”)
secret = json.loads(secret_value_response[‘SecretString’])
username = secret[‘username’]
password = secret[‘password’]
Replace with your database and credentials (or use Secrets Manager)
db_instance_id = ‘your-db-instance-id’
db_name = ‘your-database-name’
username = ‘your-username’
password = ‘your-password’
Execute the SQL query using RDS Data API
response = rds_data_api.execute_statement(
resourceARN=f"arn:aws:rds:{region}:{account_id}:cluster:{db_instance_id}“, # Replace with your RDS cluster ARN
secretARN=f"arn:aws:secretsmanager:{region}:{account_id}
my-secret”, # Replace with your secrets manager secret ARN
database=db_name,
sql=sql_query
)
Format the results for the API response
results =
if response[‘records’]:
for record in response[‘records’]:
results.append(record)
return {
‘statusCode’: 200,
‘body’: json.dumps(results),
‘headers’: {
‘Content-Type’: ‘application/json’
}
}
except Exception as e:
print(e)
return {
‘statusCode’: 500,
‘body’: json.dumps(str(e)),
‘headers’: {
‘Content-Type’: ‘application/json’
}
}
- Configure API Gateway:
- Create an API endpoint in API Gateway.
- Integrate the endpoint with the Lambda function.
- Configure the API Gateway to accept HTTP requests and pass the SQL query as a parameter (e.g.,
?query=<your_sql_query>).
- Test the API:
- Use a tool like Postman or curl to send HTTP requests to the API endpoint.
- Example:
https://your-api-endpoint.execute-api.your-region.amazonaws.com/your-stage?query=SELECT+*+FROM+your_table.
- Configure API Gateway:
- Create an API endpoint in API Gateway.
- Integrate the endpoint with the Lambda function.
- Configure the API Gateway to accept HTTP requests and pass the SQL query as a parameter (e.g.,
?query=<your_sql_query>).
- Test the API:
- Use a tool like Postman or curl to send HTTP requests to the API endpoint.
- Example:
https://your-api-endpoint.execute-api.your-region.amazonaws.com/your-stage?query=SELECT+*+FROM+your_table.
–