How to Connect PostgreSQL to Connection Pools in App Platform

App Platform is a Platform-as-a-Service (PaaS) offering that allows developers to publish code directly to DigitalOcean servers without worrying about the underlying infrastructure.

Note

Connecting your App Platform app to a database connection pool is currently only available for PostgreSQL databases.

Instead of forcing database clients to open and close connections each time they send a request, you can set up connection pools that allow clients to open a set number of connections, keep them open, and reuse them over the course of their session with the database. This helps reduce latency, lower resource usage, and create parallelism in your application.

You can create connection pools for your application in App Platform using PostgreSQL and bindable environment variables.

Before adding a PostgreSQL database with a connection pool to your app, you need to complete the following:

• Deploy your app on App Platform.
• Create a PostgreSQL database.
• Add a connection pool to the database.

Once you have completed these prerequisites, you can add the database as a component of your app.

Add Database as a Component

To add the database as a component of your app from the DigitalOcean Control Panel, select Apps from the left-hand menu and then select your app from the Apps screen.

From the app’s Overview page, click the Create button and select Create/Attach Database from the dropdown menu.

On the Configure your database page, select the Previously Created DigitalOcean Database option, then select the database cluster with the connection pool from the Database Cluster dropdown menu. Once you’ve selected the target database, additional dropdown menus appear.

In the Database field, select the database in the cluster you want the application to connect to. In the User field, select the database user that you want the application to connect to the database with. After configuring these settings, click Attach Database. App Platform redeploys your app with the attached database.

Configure Bindable Environment Variables

Once the app redeploys, you can define environment variables that your application can use to connect to the database and its pool. App Platform provides several bindable environment variables that contain sensitive information about your app and its components. For example, you can use the ${your_database_name.DATABASE_URL} bindable variable to create an environment variable that contains your database’s URL information. This section explains how to set up bindable environment variables for your database’s URL and the connection pool’s URL.

To add environment variables to your application, first go to the DigitalOcean Control Panel and select your app from the Apps screen.

From the app’s Overview page, click the Settings tab and then scroll down to the App-Level Environment Variables section.

In the Keys field, enter a name for the database’s URL environment variable. The name can be anything. In the Values field, enter the bindable variable value for the database’s URL in the following syntax, substituting the your_database_name placeholder value with your database’s name:

${your_database_name.DATABASE_URL}

For example, if your database’s name is example-db, the variable value would be:

${example-db.DATABASE_URL}

Once you’ve enter the values for each field, click the + button to add a second variable.

In Keys field, enter a name for the variable. In the Values field, enter the value of your database connection pool in the following syntax:

${your_database_name.your_database_connection_pool_name.DATABASE_URL}

Once you’ve entered the values for each field, click Save. This redeploys the app with the new environment variables.

To verify that the environment variables are now available and referencing the correct information about your database, scroll back to the top of the Settings page and then click the Console tab.

From the console’s command line, use the echo utility to call the variables using the values you entered into the Keys fields in the prior step:

echo $your_database_URL_variable_name $your_database_connection-pool_variable_name

For example, if you named your database URL variable DATABASE_URL and the database’s connection pool variable DATABASE_POOL_URL, the command would look like this:

echo $DATABASE_URL $DATABASE_POOL_URL

The command returns the respective value of each environment variable.

You can now use these variables in your application’s code.