Skip to content

PostgreSQL Connection Strings in API Maker

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.

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

postgres://localhost:5432

Localhost with Database Name

postgres://localhost:5432/mydb

Localhost with Username & Password

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

Localhost with SSL Enabled

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

Localhost with Application Name

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

Connection with Search Path (Schema)

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

Connection with Timeouts

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

Connection with Pool Size (via pgpool / pgbouncer params)

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

Multi-Host Failover (Cluster / HA Setup)

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

Replication Connection

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

Cloud Provider Connection (AWS RDS Example)

postgres://user:[email protected]:5432/mydb

Cloud with SSL Required (Azure Database for PostgreSQL)

postgres://user:[email protected]:5432/mydb?sslmode=require

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

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:[email protected]:5432/mydb.

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

  • 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

  • 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

  • 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

  • Azure Database

    • Managed PostgreSQL with enterprise-grade security.
    • Automatic scaling, HA, and backups included.
    • Best for workloads in Azure ecosystem.
    • azure.microsoft.com/postgresql

  • Heroku Postgres

    • One-click PostgreSQL provisioning.
    • Easy scaling for startups and SaaS apps.
    • Deep integration with Heroku platform.
    • 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

  • Crunchy Bridge

    • Reliable managed PostgreSQL clusters.
    • Monitoring, HA, and migration tooling included.
    • Suited for enterprises and production apps.
    • crunchydata.com/bridge

  • EDB BigAnimal

    • Enterprise-ready PostgreSQL service.
    • Adds resilience, compliance, and Oracle migration.
    • Designed for large-scale workloads.
    • enterprisedb.com/biganimal

  • ScaleGrid