Connect nocodb to AWS RDS Postgres

When building modern, scalable applications, you often need a robust database like PostgreSQL for storing large amounts of data. However, when it comes to providing simplified views for non-technical business users during the bootstrapping phase, solutions like Airtable often seem attractive due to their simplicity. But as the project scales, Airtable's limitations—such as lack of advanced features and API request throttling—can hinder progress.

This is where nocodb, a powerful open-source alternative, comes into play. Nocodb supports multiple databases right out of the box, offers various views (such as forms), and can even generate a comprehensive API with OpenAPI specifications. It’s flexible, stateless, and can store configurations in services like S3 buckets while relying on databases like PostgreSQL for larger configurations. With nocodb, scaling is no problem—whether you’re running it in Kubernetes clusters or using AWS AppRunner, you’re in control.

However, one issue we encountered was connecting nocodb to AWS RDS Postgres. AWS RDS enforces security using self-signed certificates, which nocodb doesn’t explicitly cover in its documentation. In this article, we’ll walk you through the process of securely connecting nocodb to AWS RDS Postgres.

Why NocoDB for Your Backend?

Before diving into the technicalities, let’s briefly highlight why nocodb is an excellent choice for integrating databases like PostgreSQL:

• Wide Database Support: Seamlessly integrates with various databases, including MySQL, PostgreSQL, and more.
• Forms and Views: Perfect for creating easy-to-use interfaces for non-technical users.
• API Generation: Automatically generates APIs, complete with OpenAPI spec, making backend development a breeze.
• Stateless Configuration: Store configurations in S3 or databases, enabling scalability.

Now, let's address the challenge: connecting nocodb to an AWS RDS Postgres instance.

Connecting NocoDB to AWS RDS Postgres: Step-by-Step

When deploying nocodb—whether in Docker, AWS AppRunner, or another environment—you can configure the database connection through the NC_DB_JSON environment variable. This variable requires a Knex-style JSON connection string, which provides an opportunity to include a self-signed certificate from AWS RDS.

Here’s how to make that connection secure and smooth:

Step 1: Download AWS RDS Certificates

First, visit the official AWS RDS documentation and download the appropriate certificate bundle for your region.

Step 2: Convert the Certificate into a Single Line

Once you have the certificate, open the bundle, and remove all line breaks. Replace each break with an escaped newline character (\n) to form a single-line string. This format is essential for embedding the certificate into a JSON structure.

Step 3: Create the Knex JSON Connection String

Now, craft your Knex-style JSON connection string. Here’s a template to get you started:

{
  "client": "pg",
  "connection": {
    "host": "YOUR_HOST",
    "port": "YOUR_PORT",
    "user": "YOUR_USER",
    "password": "YOUR_PASSWORD",
    "database": "YOUR_DATABASE",
    "ssl": {
      "ca": "THE_CA_CERT_CONTENT",
      "key": "",
      "cert": "",
      "rejectUnauthorized": false
    }
  }
}

 

Replace the placeholders with your AWS RDS details and place your single-line certificate string under the "ca" field.

Step 4: Convert the JSON to a Single Line

To pass the JSON in an environment variable, you’ll need to convert the entire JSON object into a single line. Simply remove all the extra white spaces and line breaks, turning the above JSON into one continuous string.

Step 5: Set the Environment Variable

Finally, set the environment variable NC_DB_JSON in your deployment configuration with the single-line JSON string. 

With this setup, nocodb will establish a secure and validated connection to your AWS RDS Postgres database.

Wrapping Up

Nocodb offers an incredibly flexible and powerful way to integrate robust databases like PostgreSQL while still offering simplified interfaces for non-technical users. By following the steps above, you can overcome the initial challenge of connecting to AWS RDS Postgres, ensuring a secure, scalable solution for your backend.