How to Add Postgres to a Gin App

Gin is one of the most widely used HTTP frameworks in Go, and it stays out of the database question entirely, so the first Postgres table in a Gin app means picking a driver, wiring a pool into the handlers and finding somewhere to run migrations. The part that takes longer is everything around the code: a Postgres for every developer laptop, a database per environment and a connection string that has to reach the app in each of them.

This guide adds Postgres to a small Gin todo API, first with Encore, which declares the database in Go and runs it locally and in your own AWS or GCP account, and then with pgx, golang-migrate and Amazon RDS wired by hand.

Postgres options for a Gin app at a glance

How do you add Postgres to Gin with Encore?

Encore combines an open source infrastructure SDK with a platform that provisions and deploys into your own AWS or GCP account. It works alongside Gin: the existing router is mounted in a single raw endpoint with a fallback route, and the Gin handlers query a database that is declared a few lines above them.

Set up the app

The first migration creates the table, and Encore applies migration files in the order of their number prefix:

-- todo/migrations/1_create_todos.up.sql
CREATE TABLE todos (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    done BOOLEAN NOT NULL DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

The service declares the database, builds the Gin router in initService, and forwards every request that no Encore endpoint matches to Gin:

// todo/todo.go
package todo

import (
	"errors"
	"net/http"
	"strconv"

	"encore.dev/storage/sqldb"
	"github.com/gin-gonic/gin"
)

// Postgres locally with `encore run`, Amazon RDS or Cloud SQL when deployed.
var db = sqldb.NewDatabase("todo", sqldb.DatabaseConfig{
	Migrations: "./migrations",
})

//encore:service
type Service struct {
	router *gin.Engine
}

func initService() (*Service, error) {
	r := gin.New()
	r.Use(gin.Recovery())
	r.POST("/todos", createTodo)
	r.GET("/todos/:id", getTodo)
	r.PATCH("/todos/:id/done", markDone)
	return &Service{router: r}, nil
}

// Fallback sends every request that no Encore endpoint matches to Gin.
//
//encore:api public raw path=/!fallback
func (s *Service) Fallback(w http.ResponseWriter, req *http.Request) {
	s.router.ServeHTTP(w, req)
}

type Todo struct {
	ID    int64  `json:"id"`
	Title string `json:"title"`
	Done  bool   `json:"done"`
}

type createRequest struct {
	Title string `json:"title" binding:"required"`
}

func createTodo(c *gin.Context) {
	var req createRequest
	if err := c.ShouldBindJSON(&req); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "title is required"})
		return
	}
	var t Todo
	err := db.QueryRow(c.Request.Context(), `
		INSERT INTO todos (title) VALUES ($1)
		RETURNING id, title, done
	`, req.Title).Scan(&t.ID, &t.Title, &t.Done)
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "could not create todo"})
		return
	}
	c.JSON(http.StatusCreated, t)
}

func getTodo(c *gin.Context) {
	id, err := strconv.ParseInt(c.Param("id"), 10, 64)
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "invalid id"})
		return
	}
	var t Todo
	err = db.QueryRow(c.Request.Context(), `
		SELECT id, title, done FROM todos WHERE id = $1
	`, id).Scan(&t.ID, &t.Title, &t.Done)
	if errors.Is(err, sqldb.ErrNoRows) {
		c.JSON(http.StatusNotFound, gin.H{"error": "todo not found"})
		return
	} else if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "could not load todo"})
		return
	}
	c.JSON(http.StatusOK, t)
}

func markDone(c *gin.Context) {
	id, err := strconv.ParseInt(c.Param("id"), 10, 64)
	if err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "invalid id"})
		return
	}
	res, err := db.Exec(c.Request.Context(), `
		UPDATE todos SET done = true WHERE id = $1
	`, id)
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "could not update todo"})
		return
	}
	if res.RowsAffected() == 0 {
		c.JSON(http.StatusNotFound, gin.H{"error": "todo not found"})
		return
	}
	c.Status(http.StatusNoContent)
}

encore run starts Postgres locally, creates the todo database and applies the migration before the first request, so a POST to localhost:4000/todos with a JSON title works on a fresh clone with no DATABASE_URL to set. The Gin handlers stay ordinary Gin code with c.ShouldBindJSON, c.Param and c.JSON, and the database handle is a package variable that Encore connects for you in every environment. The SQL database docs cover transactions and sharing a database between services.

On deploy, Encore provisions the database on Amazon RDS or Cloud SQL in your own account, places it in private subnets, gives the service its own database role and runs the migrations on deploy. An AI coding agent that adds a due_date column writes the migration and the handler change together and checks both against the local Postgres before anything reaches the cloud.

Routes can also move out of Gin one at a time: an Encore endpoint such as //encore:api public method=GET path=/todos/:id takes precedence over the fallback, and Encore traces and documents the routes you move.

Best fit: Gin services that should keep their router and get a Postgres database that runs the same way on a laptop, in preview environments and in your own AWS or GCP account.

pgx, golang-migrate and Amazon RDS

The common hand-wired setup is pgx for the driver and pool, golang-migrate for schema changes, and an RDS instance created in Terraform or the console. The pool is created once in main and passed to the handlers through a struct:

package main

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

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

type server struct{ db *pgxpool.Pool }

func main() {
	pool, err := pgxpool.New(context.Background(), os.Getenv("DATABASE_URL"))
	if err != nil {
		log.Fatal(err)
	}
	defer pool.Close()

	s := &server{db: pool}
	r := gin.Default()
	r.POST("/todos", s.createTodo)
	log.Fatal(r.Run(":8080"))
}

func (s *server) createTodo(c *gin.Context) {
	var req struct {
		Title string `json:"title" binding:"required"`
	}
	if err := c.ShouldBindJSON(&req); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{"error": "title is required"})
		return
	}
	var id int64
	err := s.db.QueryRow(c.Request.Context(),
		`INSERT INTO todos (title) VALUES ($1) RETURNING id`, req.Title).Scan(&id)
	if err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{"error": "could not create todo"})
		return
	}
	c.JSON(http.StatusCreated, gin.H{"id": id, "title": req.Title})
}

Locally that means a Postgres container in Docker Compose, a DATABASE_URL in a .env file and a migrate -path ./migrations -database "$DATABASE_URL" up step before go run. In AWS it means an RDS for PostgreSQL instance in private subnets, a DB subnet group, a security group that allows port 5432 from the app's security group, the password in Secrets Manager, and a way for CI to reach the private database to run migrations, such as a task inside the VPC. pgxpool defaults to the larger of four connections or the number of CPUs per process, so with several replicas the pool size also has to be tuned against the instance's max_connections.

This route gives full control over every setting and fits teams that already manage RDS in Terraform. The cost is the setup repeated for each environment, and a local database that drifts from the cloud one unless someone keeps the Compose file and the RDS parameter group in step.

Best fit: Gin apps whose database is already owned by a platform team, or that need RDS settings and extensions managed outside the application.

GORM

GORM is the most popular ORM in Go and works with Gin through the same handler pattern, with db.WithContext(c.Request.Context()) on each query. It brings models, associations, hooks and AutoMigrate, which is convenient in development and limited in production because it creates missing tables and columns but never drops unused ones or renames anything. GORM runs on the same Postgres, connection string and RDS setup as pgx. It can also sit on top of an Encore database by opening the connection with db.Stdlib(), as shown in the Atlas and GORM guide.

Best fit: CRUD-heavy apps where models and associations save more code than plain SQL.

How to choose

Pick Encore when you want to keep Gin and get Postgres declared next to the handlers, a local database that starts with the app, migrations applied for you, and RDS or Cloud SQL provisioned in your own cloud account. Pick pgx with RDS when the database lives in existing Terraform and the team wants every setting in its own hands. GORM works on top of either when the codebase is mostly models and CRUD.

Related guides

• How to add Redis caching to a Gin app
• How to add S3 file uploads to a Gin app
• How to add a database to a Go API
• Go ORMs compared
• Gin vs Echo vs Fiber
• Postgres migrations in a Go API