Connect DB

Each Appizap project includes a Postgres database, a renowned open-source database known for its stability and advanced features globally.

Appizap provides several options for programmatically connecting to your Postgres database:

1. Programmatic access using the Data APIs

2. Connection pooling for scalable connections

3. Connection pooling for scalable connections

4. Using one of the many Client Libraries

Beginning on January 26, 2024, direct database connections will begin to resolve to an IPv6 address. There will be a gradual migration of projects to adhere to this new protocol. Those utilizing REST or GraphQL endpoints to connect to their database will not be impacted by this change. However, for those relying on database connection strings, it is advised to review the GitHub discussion on how to effectively prepare for the transition to IPv6.

Data APIs

Appizap offers Data APIs that automatically update, offering a straightforward method for managing data operations such as fetching, inserting, and updating. A variety of API options are available to accommodate individual preferences.

Connection pooler

A connection pool is an external system that effectively controls and organizes connections to Postgres database.

When a client submits a request, the pooler assigns an accessible connection to the client. Once the client's transaction or session concludes, the connection is released back to the pool and becomes available for another client to utilize.

Each Appizap project is equipped with a connection pooler designed to efficiently handle connections to the database. This tool offers two critical services:

• It oversees the handling of connections for applications that frequently establish and sever connections with the database. Instances include serverless functions and ORM frameworks like Prisma, Drizzle, and Kysely, which regularly create and terminate connections to the database. Without a pooling mechanism, these tools would rapidly deplete the memory resources of the database server if they connected directly each time. Utilizing a connection pooler is essential for efficiently managing database connections with such tools.

• It offers an IPv4 endpoint for connecting to the database in instances where the network provider does not accommodate IPv6. This feature distinguishes it from a direct database connection that typically directs to an IPv6 address unless the IPv4 add-on has been activated.

The connection pooler is available in 2 modes: Transaction and Session.

It is advisable to use the transaction mode when connecting from serverless environments. In this mode, a connection is dedicated to the client throughout the duration of a transaction. It is possible for two consecutive transactions from the same client to be carried out using different connections. Certain session-based Postgres functionalities, like prepared statements, are not accessible when using this option.

Session mode functions much like establishing a direct connection to your database. Prepared statements are fully supported in this mode. Upon a new client's connection, a dedicated connection is allocated to the client until it is terminated. It is possible to encounter constraints on pooler connections due to the persistent nature of the connection until the client disconnects.

The configuration for the connection pool can be located within the Database settings section on the dashboard.

1. Go to the Settings section.

2. Click Database.

3. Under Connection string, make sure Use connection pooling is checked. Copy the URI.

Pre-requisites

The current connection pooler in Appizap projects utilizes the pgbouncer Postgres role for establishing the initial connection to the postgres database. Should these privileges be withdrawn, access to the pooler will be restricted until the necessary permissions are reinstated.

SSL

Wherever feasible, it is recommended to establish a connection to your database utilizing SSL in order to mitigate the risk of unauthorized access and interception by malicious actors. The connection information and server root certificate can be acquired by accessing the dashboard of your application.

Troubleshooting

The following are common errors and their solutions:

Error	Solution
Max client connections reached	When there are an excessive amount of connections to Supavisor surpassing the authorized limit of the compute add-on, this error occurs. To resolve this issue, it is recommended to enhance the compute add-on of the database in order to expand the maximum allowable connections to Supavisor.
Connection failed {:error, :eaddrnotavail} to 'db.x.domain':5432	The Supavisor is unable to establish a connection with the customer database, a common occurrence that is typically the result of the target database being unresponsive.
Connection failed {:error, :nxdomain} to 'db.x.domain':5432	The Supavisor system is currently unable to establish a connection with the customer database. This issue typically arises when the intended database is experiencing difficulties in providing a response.
Connection closed when state was authentication	This error occurs when the database is nonexistent or when the user lacks the appropriate credentials.
Subscribe error: {:error, :worker_not_found}	When the client attempts to establish a connection with the database, a log event is generated indicating that Supavisor lacks the requisite details to direct the connection. It is recommended to reconnect to the database, as there may be a delay in the dissemination of project information to Supavisor.
Subscribe error: {:error, {:badrpc, {:error, {:erpc, :timeout}}}}	This error occurs when there is a delay in communication between various Supervisor nodes beyond the anticipated timeframe. It is advisable to attempt reconnecting to the database in such situations.
Terminating with reason :client_termination when state was :busy	This issue occurs when the client prematurely terminates the connection prior to the completion of the connection to the database.

Get Hostname

The hostname of your database plays a vital role in facilitating a direct connection by mapping to the specific IP address of the database server. To locate the hostname, you can access the Database Settings section.

1. It should be noted that the pooler, or connection pooler, possesses a distinct IP address compared to that of your database.

2. To obscure the database host and the direct connection details, the connection string for the pooler should be concealed.

Example hostname: db.<PROJECT_REF>.domain.ext

Managing IP

To ascertain your present IP address, you have the option of utilizing either an IP address lookup website or the terminal command:

nslookup db.<PROJECT_REF>.domain.ext

This command requests information from domain name servers in order to determine the IP address associated with the provided hostname.

Example IPv6 Address: 2a05:d014:1c06:5f0c:d7a9:8616:bee2:30df

Upon project creation, a static IPv6 address is assigned. This address might change when a project is paused or resumed and when the database is upgraded.

Choosing the static IPv4 add-on offers a more reliable connection address. The IPv4 address will remain unchanged unless the project is halted or restarted. Unlike the IPv6 address, upgrading your database doesn't affect the IPv4 address.