Building a Gin API with a Managed PostgreSQL Database

Most Go APIs ship as single binaries with no runtime dependencies except the database. This makes deployments simple — build the binary in a Docker image, point it at Postgres, and run. The hard part is managing the database: provisioning it, backing it up, rotating credentials, and keeping connection pools stable under load.

PandaStack's managed PostgreSQL instances handle the operational work so you can focus on writing the API. Create a database, link it to your Gin app, and the platform injects DATABASE_URL automatically. Backups run daily, connection limits are enforced, and storage auto-grows (on paid plans) when you approach capacity.

Create a Gin API with pgx connection pooling

Start with a minimal Gin app that reads from Postgres. Create main.go:

package main

import (
    "context"
    "log"
    "net/http"
    "os"

    "github.com/gin-gonic/gin"
    "github.com/jackc/pgx/v5/pgxpool"
)

var db *pgxpool.Pool

func main() {
    var err error
    db, err = pgxpool.New(context.Background(), os.Getenv("DATABASE_URL"))
    if err != nil {
        log.Fatalf("Unable to connect to database: %v", err)
    }
    defer db.Close()

    r := gin.Default()
    r.GET("/health", healthCheck)
    r.GET("/users", listUsers)

    port := os.Getenv("PORT")
    if port == "" {
        port = "8080"
    }
    r.Run("0.0.0.0:" + port)
}

func healthCheck(c *gin.Context) {
    err := db.Ping(context.Background())
    if err != nil {
        c.JSON(http.StatusServiceUnavailable, gin.H{"status": "unhealthy", "error": err.Error()})
        return
    }
    c.JSON(http.StatusOK, gin.H{"status": "healthy"})
}

func listUsers(c *gin.Context) {
    rows, err := db.Query(context.Background(), "SELECT id, email FROM users LIMIT 10")
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
        return
    }
    defer rows.Close()

    users := []map[string]interface{}{}
    for rows.Next() {
        var id int
        var email string
        rows.Scan(&id, &email)
        users = append(users, map[string]interface{}{"id": id, "email": email})
    }

    c.JSON(http.StatusOK, users)
}

Add dependencies in go.mod:

module github.com/yourname/gin-api

go 1.22

require (
    github.com/gin-gonic/gin v1.10.0
    github.com/jackc/pgx/v5 v5.5.0
)

The app reads DATABASE_URL from the environment and binds to 0.0.0.0 on the port specified by PORT (or 8080 if unset). PandaStack sets both variables when you link a database to the app.

Provision a managed PostgreSQL database

Go to the dashboard, navigate to Databases → Create, and choose PostgreSQL 16. Name it api-db, select a plan (free tier includes one database), and provision.

PandaStack uses KubeBlocks to orchestrate the database on Kubernetes. Provisioning takes 2–3 minutes. When complete, you get a connection string like:

postgresql://user:password@api-db.internal.pandastack.io:5432/api_db?sslmode=require

The free-tier database has a 50-connection limit and 7 days of backup retention. Paid plans increase this to 300 or 1000 connections, with 15 or 30 days of backups.

Link the database to the Gin app

Create a PandaStack project for the API:

panda projects create \
  --name gin-api \
  --repo github.com/yourname/gin-api \
  --branch main \
  --type container

The platform auto-detects Go projects (by finding go.mod) and uses a default Dockerfile if none is provided. The buildpack compiles the binary and runs it with ./main.

Link the database:

panda projects link-database <project-id> <database-id>

PandaStack injects DATABASE_URL as an environment variable. The next deploy reads that variable and connects to the database.

Deploy the app:

panda projects deploy <project-id>

The build logs show dependency resolution and binary compilation. When the container starts, it reads DATABASE_URL, connects to Postgres, and binds to port 8080. The health check endpoint (/health) pings the database to confirm connectivity.

Visit the deployed URL and hit /health:

curl https://gin-api-abc123.pandastack.app/health

You should see {"status": "healthy"}.

Run database migrations on startup

The /users endpoint fails with "relation does not exist" because the users table has not been created. Use a migration tool to initialize the schema.

Install golang-migrate:

go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest

Create a migrations directory:

mkdir -p migrations

Add an initial migration migrations/000001_create_users_table.up.sql:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO users (email) VALUES
    ('alice@example.com'),
    ('bob@example.com'),
    ('charlie@example.com');

Run migrations programmatically on startup by adding a function in main.go:

import (
    "github.com/golang-migrate/migrate/v4"
    _ "github.com/golang-migrate/migrate/v4/database/postgres"
    _ "github.com/golang-migrate/migrate/v4/source/file"
)

func runMigrations() {
    m, err := migrate.New(
        "file://migrations",
        os.Getenv("DATABASE_URL"),
    )
    if err != nil {
        log.Fatalf("Migration init failed: %v", err)
    }

    if err := m.Up(); err != nil && err != migrate.ErrNoChange {
        log.Fatalf("Migration failed: %v", err)
    }

    log.Println("Migrations applied successfully")
}

Call runMigrations() before starting the Gin server in main():

func main() {
    runMigrations()

    var err error
    db, err = pgxpool.New(context.Background(), os.Getenv("DATABASE_URL"))
    // ...
}

Commit the migrations/ directory and redeploy. On startup, the app runs the migration, creates the users table, seeds it with three rows, and starts the HTTP server. The /users endpoint now returns data.

Use a custom Dockerfile for more control

The auto-detected buildpack works for simple Go apps, but if you need multi-stage builds or custom base images, commit a Dockerfile:

FROM golang:1.22-alpine AS builder
WORKDIR /app
COPY go.mod go.sum ./
RUN go mod download
COPY . .
RUN go build -o main .

FROM alpine:latest
RUN apk add --no-cache ca-certificates
WORKDIR /app
COPY --from=builder /app/main .
COPY migrations ./migrations
CMD ["./main"]

This compiles the binary in a Go image and runs it in a minimal Alpine image with CA certificates (required for TLS database connections). The final image is under 20 MB.

PandaStack detects the Dockerfile and builds it with rootless BuildKit. No Docker socket access, no privilege escalation — the build runs in an ephemeral Kubernetes Job pod and pushes the image to Google Artifact Registry.

Scale horizontally with connection pooling

pgx's pgxpool handles connection pooling internally. By default, it opens one connection per CPU core, up to the max_conns setting. For production, tune this based on your database's connection limit.

Free-tier databases have a 50-connection limit. If you run three replicas of the API, each should use at most 15 connections (leaving headroom for manual queries and background jobs).

Set pool limits in the connection string or via environment variables:

config, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))
if err != nil {
    log.Fatalf("Unable to parse DATABASE_URL: %v", err)
}
config.MaxConns = 15

db, err = pgxpool.NewWithConfig(context.Background(), config)

Deploy this change, and each replica opens at most 15 connections. If you scale to five replicas, reduce MaxConns to 10 to stay under the database limit.

Paid plans increase the connection limit to 300 or 1000, so high-traffic APIs can scale without hitting database connection exhaustion.

Handle connection failures gracefully

Databases restart during maintenance windows, failovers, or plan upgrades. The API should reconnect automatically instead of crashing.

pgx's connection pool retries failed queries by default, but you should add health checks and readiness probes to tell the platform when the app is ready for traffic.

Update healthCheck to return 503 if the database is unreachable:

func healthCheck(c *gin.Context) {
    ctx, cancel := context.WithTimeout(context.Background(), 2*time.Second)
    defer cancel()

    err := db.Ping(ctx)
    if err != nil {
        c.JSON(http.StatusServiceUnavailable, gin.H{"status": "unhealthy", "error": err.Error()})
        return
    }
    c.JSON(http.StatusOK, gin.H{"status": "healthy"})
}

Configure a health check path in pandastack.json:

{
  "type": "container",
  "name": "gin-api",
  "healthCheckPath": "/health"
}

PandaStack pings /health every 10 seconds. If it returns 503, the container is marked unhealthy and removed from the load balancer. Traffic shifts to other replicas while the failing one restarts.

This prevents users from hitting a replica with a broken database connection.

Backup and restore

PandaStack runs daily backups for all managed databases. Backups are retained for 7 days on the free tier, 15 days on Pro, and 30 days on Premium.

To restore from a backup, go to the dashboard Databases → Backups, select a backup, and click Restore. This creates a new database instance from the snapshot. Point your app at the new instance by updating DATABASE_URL and redeploying.

Manual backups are also supported: click Create Backup to snapshot the database on demand before running a risky migration or schema change.

Monitor query performance

PandaStack collects database metrics (CPU, memory, query latency) on paid plans. View them in the dashboard under Databases → Metrics. This shows slow queries, connection pool saturation, and disk I/O spikes.

For deeper query profiling, enable pg_stat_statements and query it directly:

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

This shows which queries are consuming the most time. Optimize them with indexes, query rewrites, or caching.

Deploy from CI with the API

Script the deploy in GitHub Actions:

name: Deploy Gin API

on:
  push:
    branches: [main]

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - name: Trigger deploy
        run: |
          curl -X POST https://api.pandastack.io/v1/projects/${{ secrets.PANDASTACK_PROJECT_ID }}/deploy \
            -H "Authorization: Bearer ${{ secrets.PANDASTACK_TOKEN }}" \
            -f

Store PANDASTACK_TOKEN (a psk_live_ token) and PANDASTACK_PROJECT_ID in GitHub Actions secrets. Every push to main triggers a rebuild and deploy.

For staging and production environments, create separate projects and databases. Deploy to staging on every push, and to production only on tagged releases.

Why managed Postgres beats self-hosting

Self-hosting Postgres on a VM requires you to handle backups, monitoring, replication, and security patches. Managed instances offload this work. PandaStack runs PostgreSQL on Kubernetes via KubeBlocks, with automated failover, daily backups, and one-click restores.

Free-tier databases are suitable for development and low-traffic apps. For production workloads with strict uptime requirements, use a paid plan with higher connection limits, storage auto-grow, and longer backup retention.

The cost difference between self-hosting and a managed instance is small when you account for the operational time saved.

References

• [Gin framework documentation](https://gin-gonic.com/docs/)
• [pgx connection pooling guide](https://github.com/jackc/pgx)
• [PandaStack managed databases](https://docs.pandastack.io)