# PostgreSQL Connection Strings in API Maker

> Learn how to format Postgres URI strings in API Maker—user, password, host, port, database name. Includes URL encoding tips for special chars.

Source: https://docs.apimaker.dev/v1/docs/Database-connection-string/postgres-connection-strings.html

## Introduction

When integrating PostgreSQL with **API Maker**, the connection string defines how API Maker connects to your PostgreSQL database.

It includes authentication details, host, port, database name, and optional parameters such as SSL, timezones, and advanced settings.

---

## PostgreSQL Connection String Formats

Below is a **visual breakdown** of the PostgreSQL connection string.

```text
postgres://username:password@host:port/DB_Name?options

 └───┬──┘ └───────┬───────┘ └───┬────┘ └──┬──┘ └──┬──┘
   Scheme      Credentials   Host + Port Database Options

```

## PostgreSQL Connection String Examples

### 🧩 Connection String Parts Explained

- **Protocol (scheme):**
    - `postgres://` → Standard PostgreSQL connection.
    - `postgresql://` → Alternative, fully valid scheme (interchangeable).

- **Credentials (optional):**
    - Format: `username:password@`.
    - Use **URL-encoding** for special characters (`my@pass` → `my%40pass`).

- **Host + Port:**
    - Single host → `localhost:5432`
    - Custom port if not default (5432).

- **Database (optional):**
    - After `/`, e.g., `/mydb`.
    - If omitted, PostgreSQL defaults to the **username** as the database.

- **Options (query params):**
    - After `?`, key-value pairs separated by `&`.
    - Examples: `?sslmode=require&application_name=apimaker`

---

### Basic Localhost Connection

```text
postgres://localhost:5432
```

### Localhost with Database Name

```text
postgres://localhost:5432/mydb
```

### Localhost with Username & Password

```text
postgres://user:password@localhost:5432/mydb
```

### Localhost with SSL Enabled

```text
postgres://user:password@localhost:5432/mydb?sslmode=require
```

### Localhost with Application Name

```text
postgres://user:password@localhost:5432/mydb?application_name=apimaker
```

### Connection with Search Path (Schema)

```text
postgres://user:password@localhost:5432/mydb?options=-csearch_path%3Dmyschema
```

### Connection with Timeouts

```text
postgres://user:password@localhost:5432/mydb?connect_timeout=10
```

### Connection with Pool Size (via pgpool / pgbouncer params)

```text
postgres://user:password@localhost:5432/mydb?application_name=MyApp&pool_size=20
```

### Multi-Host Failover (Cluster / HA Setup)

```text
postgres://user:password@host1:5432,host2:5432,host3:5432/mydb?target_session_attrs=read-write
```

### Replication Connection

```text
postgres://replica_user:password@replica_host:5432/mydb?replication=true
```

### Cloud Provider Connection (AWS RDS Example)

```text
postgres://user:password@mydb.xxxxxx.us-east-1.rds.amazonaws.com:5432/mydb
```

### Cloud with SSL Required (Azure Database for PostgreSQL)

```text
postgres://user:password@myserver.postgres.database.azure.com:5432/mydb?sslmode=require
```

### Cloud with Multi-Host Failover (Google Cloud SQL HA Setup)

```text
postgres://user:password@host1:5432,host2:5432/mydb?target_session_attrs=read-write&sslmode=require
```

---

## Connection String Parts Breakdown

| Part                        | Description                                                 | Example                                                |
|-----------------------------|-------------------------------------------------------------|--------------------------------------------------------|
| **Scheme**                  | Protocol prefix for PostgreSQL.                             | `postgres://` or `postgresql://`                       |
| **Username** (optional)     | Database username for authentication.                       | `user`                                                 |
| **Password** (optional)     | Password for authentication (URL-encoded if special chars). | `p%40ss` for `p@ss`                                    |
| **@**                       | Separator between credentials and host(s).                  | `user:password@`                                       |
| **Host(s)**                 | One or more PostgreSQL server addresses.                    | `localhost`, `mydb.xxxxxx.us-east-1.rds.amazonaws.com` |
| **Port** (optional)         | Port number (default: `5432`).                              | `:5432`                                                |
| **Comma-separated Hosts**   | Multiple hosts for HA / load balancing.                     | `host1:5432,host2:5432`                                |
| **/** (slash after hosts)   | Separator between hosts and default database.               | `/`                                                    |
| **Default Database**        | Database name to connect to if none specified.              | `mydb`                                                 |
| **?options** (query params) | Connection options in `key=value` format (joined with `&`). | `?sslmode=require&connect_timeout=10`                  |

---

## Connection String Options Breakdown and Explanation

| Parameter                | Description                                                                 | Example Value                       |  
|--------------------------|-----------------------------------------------------------------------------|-------------------------------------|  
| **sslmode**              | Controls SSL/TLS usage (`disable`, `require`, `verify-ca`, `verify-full`).  | `sslmode=require`                   |  
| **sslrootcert**          | Path to the root CA certificate for SSL validation.                         | `sslrootcert=/etc/ssl/ca.pem`       |  
| **sslcert**              | Path to client SSL certificate.                                             | `sslcert=/etc/ssl/client.crt`       |  
| **sslkey**               | Path to client SSL key.                                                     | `sslkey=/etc/ssl/client.key`        |  
| **connect_timeout**      | Maximum wait time (in seconds) for a new connection.                        | `connect_timeout=10`                |  
| **application_name**     | Sets a name for the application, shown in PostgreSQL logs/pg_stat_activity. | `application_name=MyApp`            |  
| **options**              | Runtime parameters passed at session start.                                 | `options='-c search_path=myschema'` |  
| **target_session_attrs** | Ensures connection only to certain servers (`read-write`, `any`).           | `target_session_attrs=read-write`   |  
| **keepalives**           | Enables TCP keepalives (`1` = on, `0` = off).                               | `keepalives=1`                      |  
| **keepalives_idle**      | Seconds of idle time before keepalive probes are sent.                      | `keepalives_idle=30`                |  
| **keepalives_interval**  | Interval (seconds) between keepalive probes.                                | `keepalives_interval=10`            |  
| **keepalives_count**     | Number of failed keepalive probes before dropping connection.               | `keepalives_count=5`                |  
| **tcp_user_timeout**     | Timeout (ms) for unacknowledged TCP packets before connection is closed.    | `tcp_user_timeout=5000`             |  
| **gssencmode**           | GSSAPI encryption (`disable`, `prefer`, `require`).                         | `gssencmode=prefer`                 |  
| **krbsrvname**           | Kerberos service name for authentication (default: `postgres`).             | `krbsrvname=postgres`               |  
| **replication**          | Used for replication connections (`true` or `database`).                    | `replication=true`                  |  

---

## Secure Connection Best Practices

- Always enable **SSL/TLS (`sslmode=require`)** in production.

- Use **environment variables** instead of hardcoding credentials.

- Leverage **API Maker Secrets Management** to store and rotate sensitive keys.

- Restrict PostgreSQL users to **least privilege access**.

- Use **firewall rules** or `pg_hba.conf` to allow only trusted IP addresses.

- Enable **connection timeouts** to prevent hanging connections.

---

## Connecting PostgreSQL in API Maker

1. Open **API Maker Dashboard** → **Secret Management** → **Default**.

2. Select **PostgreSQL** as the database type.

3. Paste your PostgreSQL connection string (with DB and SSL parameters).

4. Click **Test Connection** to verify connectivity.

5. Save the configuration.

Once connected, you can:

- Define **schemas** for your PostgreSQL tables.

- Query data using `/api/schema/...` endpoints.

- Perform **cross-database joins** with MySQL, MongoDB, or SQL Server.

---
## Troubleshooting

When connecting to PostgreSQL (local, self-hosted, or managed services like RDS/Azure/CloudSQL), you may encounter several common errors.  
Below is a categorized list with explanations:

---

### 🔑 Authentication & Authorization Errors

| Error Code | Name                  | Description                                         | Common Fix                                              |  
|------------|-----------------------|-----------------------------------------------------|---------------------------------------------------------|  
| 28P01      | InvalidPassword       | Wrong password for given user.                      | Verify username/password in connection string.          |  
| 28000      | InvalidAuthorization  | Role does not exist or is not permitted to connect. | Grant role access with `CREATE ROLE` / `GRANT CONNECT`. |  
| 42501      | InsufficientPrivilege | User does not have privileges on the object.        | Grant required privileges using `GRANT`.                |  
| 53300      | TooManyConnections    | Connection limit exceeded.                          | Increase `max_connections` or close idle sessions.      |  

---

### 🌐 Network & Connectivity Errors

| Error Code | Name                                 | Description                          | Common Fix                                 |  
|------------|--------------------------------------|--------------------------------------|--------------------------------------------|  
| 08001      | SQLClientUnableToEstablishConnection | Client could not connect to server.  | Verify hostname, port, and firewall rules. |  
| 08006      | ConnectionFailure                    | Connection unexpectedly terminated.  | Check server logs, network stability.      |  
| 08003      | ConnectionDoesNotExist               | Tried to use a closed connection.    | Reconnect before executing queries.        |  
| 57P01      | AdminShutdown                        | Server shutting down.                | Wait for restart and reconnect.            |  
| 57P03      | CannotConnectNow                     | Server is starting up / in recovery. | Retry after startup completes.             |  

---

### 🗂️ Duplicate & Constraint Errors

| Error Code | Name                | Description                                 | Common Fix                                                  |  
|------------|---------------------|---------------------------------------------|-------------------------------------------------------------|  
| 23505      | UniqueViolation     | Duplicate value violates unique constraint. | Ensure unique values or handle conflict with `ON CONFLICT`. |  
| 23503      | ForeignKeyViolation | Insert/update violates foreign key.         | Ensure referenced key exists before inserting/updating.     |  
| 23514      | CheckViolation      | Value violates a `CHECK` constraint.        | Insert valid values matching constraint rules.              |  
| 23P01      | ExclusionViolation  | Insert conflicts with exclusion constraint. | Adjust values or change exclusion policy.                   |  

---

### ⚡ Write & Transaction Errors

| Error Code | Name                 | Description                                    | Common Fix                                              |  
|------------|----------------------|------------------------------------------------|---------------------------------------------------------|  
| 40001      | SerializationFailure | Concurrent transaction conflict.               | Retry the transaction.                                  |  
| 40P01      | DeadlockDetected     | Deadlock occurred between transactions.        | Redesign queries/locking, or retry after backoff.       |  
| 23502      | NotNullViolation     | Tried to insert NULL into NOT NULL column.     | Provide a value or alter column to allow NULL.          |  
| 53100      | DiskFull             | Server ran out of disk space.                  | Free disk space or increase storage.                    |  

---

### 🗄️ Query & Syntax Errors

| Error Code | Name               | Description                                | Common Fix                                  |  
|------------|--------------------|--------------------------------------------|---------------------------------------------|  
| 42601      | SyntaxError        | Invalid SQL syntax.                        | Fix query syntax.                           |  
| 42703      | UndefinedColumn    | Column does not exist.                     | Check column name spelling or schema.       |  
| 42P01      | UndefinedTable     | Table does not exist.                      | Create table or use correct table name.     |  
| 42883      | UndefinedFunction  | Function/operator not defined.             | Define function or cast arguments properly. |  
| 42P02      | UndefinedParameter | Parameter not found in prepared statement. | Use correct placeholder ($1, $2, …).        |  

---

### 🔒 TLS/SSL & Security Errors

| Error Code  | Name                 | Description                                 | Common Fix                                                 |  
|-------------|----------------------|---------------------------------------------|------------------------------------------------------------|  
| 08001 (SSL) | SSLHandshakeFailure  | TLS/SSL negotiation failed.                 | Ensure certificates, SSL mode (`require`, `verify-full`).  |  
| FATAL       | No pg_hba.conf entry | Client not allowed by pg_hba.conf.          | Update `pg_hba.conf` to allow IP/user, then reload server. |  
| 28000       | InvalidAuthorization | User not allowed for SSL/host restrictions. | Grant access or adjust SSL-related configs.                |  

---

### 🛠️ Miscellaneous Errors

| Error Code | Name                   | Description                                  | Common Fix                                |  
|------------|------------------------|----------------------------------------------|-------------------------------------------|  
| 54000      | ProgramLimitExceeded   | Query exceeded an internal limit.            | Optimize query, reduce joins/columns.     |  
| 54001      | StatementTooComplex    | Query too complex for planner.               | Break query into smaller parts.           |  
| 22001      | StringDataRightTrunc   | Value too long for column type.              | Increase column size or truncate data.    |  
| XX000      | InternalError          | Unexpected internal error.                   | Check server logs, update PostgreSQL.     |  

---

### 🧩 Error Types Summary

| Category                           | Typical Causes                                   | Example Error Codes        |     |
|------------------------------------|--------------------------------------------------|----------------------------|-----|
| **Authentication & Authorization** | Invalid login, role issues, too many connections | 28P01, 28000, 42501, 53300 |     |
| **Network & Connectivity**         | Host unreachable, server shutdown/recovery       | 08001, 08006, 57P01, 57P03 |     |
| **Constraints & Duplicates**       | Unique, foreign key, check constraint violations | 23505, 23503, 23514, 23P01 |     |
| **Write & Transaction**            | Deadlocks, serialization failures, null inserts  | 40001, 40P01, 23502, 53100 |     |
| **Query & Syntax**                 | Invalid SQL syntax, undefined columns/functions  | 42601, 42703, 42P01, 42883 |     |
| **TLS/SSL & Security**             | SSL handshake errors, pg_hba.conf misconfig      | 08001 (SSL), FATAL, 28000  |     |
| **Miscellaneous**                  | Data truncation, internal errors, program limits | 22001, 54000, 54001, XX000 |     |

- **Authentication** covers codes like `28P01 (InvalidPassword)` and `28000 (InvalidAuthorizationSpecification)`.
- **Network** extended with connection shutdowns (`57P02`) and database dropped errors (`57P04`).
- **Transactions** expanded with concurrency issues like `40001 (SerializationFailure)` and `40P01 (DeadlockDetected)`.
- **Query** includes limits and parsing errors (`54001 StatementTooComplex`, `42601 SyntaxError`).
- **Constraints** extended with unique and check violations (`23505`, `23513`).
- **Miscellaneous** now includes disk space issues (`53100`) and internal errors (`XX000`).

---

## FAQ

**Q1:** _Is `sslmode=require` mandatory?_  
Not strictly, but it’s strongly recommended for production to secure data in transit.

**Q2:** _Can I use PostgreSQL cloud services like AWS RDS, Azure Database, or GCP Cloud SQL?_  
Yes. Just provide the full connection string from your provider and whitelist API Maker’s IP.

**Q3:** _How do I connect to a PostgreSQL instance running in Docker?_  
Use the container’s IP or host machine IP, e.g.,  
`postgres://user:pass@172.17.0.2:5432/mydb`.

---

## Related Links

- [PostgreSQL Connection String Documentation](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING)

- [API Maker Schemas Overview](https://docs.apimaker.dev/v1/docs/schema/schema.html)

- [API Maker Secrets Management](https://docs.apimaker.dev/v1/docs/secrets/secrets.html)

---

## PostgreSQL Cloud Providers

- **API Maker Cloud**
    - Built-in PostgreSQL with instant APIs and schema management.
    - Ideal for API-first development without server setup.
    - Fast integration with API Maker features.
    - [apimaker.dev](https://apimaker.dev)

- **Amazon RDS**
    - Managed PostgreSQL with automated backups and patching.
    - Best for AWS apps using EC2, Lambda, or S3.
    - High availability and monitoring built-in.
    - [aws.amazon.com/rds/postgresql](https://aws.amazon.com/rds/postgresql/)

- **Amazon Aurora**
    - PostgreSQL-compatible with high performance.
    - Built for scaling with read replicas and HA.
    - Fully managed by AWS with minimal ops.
    - [aws.amazon.com/rds/aurora](https://aws.amazon.com/rds/aurora/postgresql-features/)

- **Google Cloud SQL**
    - Fully managed PostgreSQL with HA and failover.
    - Integrates with BigQuery, GKE, and Cloud Storage.
    - Auto-scaling and monitoring included.
    - [cloud.google.com/sql/postgresql](https://cloud.google.com/sql/postgresql)

- **Azure Database**
    - Managed PostgreSQL with enterprise-grade security.
    - Automatic scaling, HA, and backups included.
    - Best for workloads in Azure ecosystem.
    - [azure.microsoft.com/postgresql](https://azure.microsoft.com/en-us/products/postgresql)

- **Heroku Postgres**
    - One-click PostgreSQL provisioning.
    - Easy scaling for startups and SaaS apps.
    - Deep integration with Heroku platform.
    - [heroku.com/postgres](https://www.heroku.com/postgres)

- **Aiven**
    - PostgreSQL on AWS, GCP, Azure, and others.
    - Managed service with monitoring and HA.
    - Flexible for multi-cloud strategies.
    - [aiven.io/postgresql](https://aiven.io/postgresql)

- **Crunchy Bridge**
    - Reliable managed PostgreSQL clusters.
    - Monitoring, HA, and migration tooling included.
    - Suited for enterprises and production apps.
    - [crunchydata.com/bridge](https://www.crunchydata.com/products/crunchy-bridge)

- **EDB BigAnimal**
    - Enterprise-ready PostgreSQL service.
    - Adds resilience, compliance, and Oracle migration.
    - Designed for large-scale workloads.
    - [enterprisedb.com/biganimal](https://www.enterprisedb.com/biganimal)

- **ScaleGrid**
    - Managed PostgreSQL with root access.
    - Automation + full customization balance.
    - Supports multiple clouds with flexibility.
    - [scalegrid.io/postgresql-hosting](https://scalegrid.io/postgresql-hosting/)