Nov 16, 20268 min read

How to Add Postgres to a Fiber App

Connect a Go Fiber v3 API to PostgreSQL with migrations and a transaction that holds a capacity limit, using Encore locally and on RDS or Cloud SQL, or pgx and Amazon RDS by hand.

Illustration for How to Add Postgres to a Fiber App

Fiber gives Go developers an Express-style router on top of fasthttp, and it leaves the database to you. The Go side is one pgxpool.New call, and the parts around it are a Postgres for every developer, a migration step that runs before the app, an RDS or Cloud SQL instance per environment, and credentials that reach the app without landing in the repo.

This guide adds Postgres to a Fiber v3 API for event RSVPs where capacity has to hold under concurrent requests. It starts with Encore, which declares the database in Go next to the Fiber handlers and runs it locally and in your own AWS or GCP account, then shows the same handler with pgx and a hand-provisioned RDS instance.

Postgres options for a Fiber app at a glance

Encorepgx + Amazon RDSGORM + RDS
Database declared inGo code (sqldb.NewDatabase)Terraform or the AWS consoleTerraform or the AWS console
Local PostgresStarted by encore runDocker Compose or a local installDocker Compose or a local install
MigrationsSQL files applied on run and deployA separate tool such as golang-migrateAutoMigrate or a separate tool
Connection stringWired in per environmentDATABASE_URL you manageDSN you manage
Production databaseRDS on AWS, Cloud SQL on GCP, in your accountRDS you provisionRDS you provision

How do you add Postgres to Fiber with Encore?

Encore

Encore works alongside Fiber here, keeping the routes and handlers as Fiber code while the database becomes a Go declaration with a migrations folder next to it. The Fiber app is mounted through Fiber's own adaptor.FiberApp in a single fallback route, and handlers query the database through the Encore database API, which wraps a pgx pool.

Set up the app

Get started
brew install encoredev/tap/encore &&
encore app create
Copy

Run in your terminal to get started locally.

~
$ encore app create rsvp-api --example=hello-world$ cd rsvp-api && go get github.com/gofiber/fiber/v3@v3.7.0$ mkdir -p events/migrations

The schema puts a CHECK on capacity and a UNIQUE (event_id, email) constraint on RSVPs, so the database rejects a double registration even if a bug in the handler lets one through:

-- events/migrations/1_create_events.up.sql CREATE TABLE events ( id BIGSERIAL PRIMARY KEY, title TEXT NOT NULL, starts_at TIMESTAMPTZ NOT NULL, capacity INT NOT NULL CHECK (capacity > 0), created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE TABLE rsvps ( id BIGSERIAL PRIMARY KEY, event_id BIGINT NOT NULL REFERENCES events(id) ON DELETE CASCADE, email TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE (event_id, email) );

The service declares the database, mounts Fiber and handles RSVPs in a transaction. SELECT ... FOR UPDATE locks the event row, so when two requests arrive for the last seat the second one waits, sees the first insert once it commits, and gets a 409 instead of overbooking the event:

// events/events.go package events import ( "errors" "net/http" "strconv" "strings" "time" "encore.dev/storage/sqldb" "encore.dev/storage/sqldb/sqlerr" "github.com/gofiber/fiber/v3" "github.com/gofiber/fiber/v3/middleware/adaptor" ) // `encore run` starts Postgres locally and applies the migrations. // Deployed, this is RDS on AWS or Cloud SQL on GCP in your own account. var db = sqldb.NewDatabase("events", sqldb.DatabaseConfig{ Migrations: "./migrations", }) //encore:service type Service struct { handler http.HandlerFunc } func initService() (*Service, error) { app := fiber.New() app.Post("/events", createEvent) app.Get("/events/:id", getEvent) app.Post("/events/:id/rsvps", rsvp) return &Service{handler: adaptor.FiberApp(app)}, nil } //encore:api public raw path=/!fallback func (s *Service) Fallback(w http.ResponseWriter, req *http.Request) { s.handler(w, req) } type Event struct { ID int64 `json:"id"` Title string `json:"title"` StartsAt time.Time `json:"starts_at"` Capacity int `json:"capacity"` Going int `json:"going"` } func createEvent(c fiber.Ctx) error { var in Event if err := c.Bind().Body(&in); err != nil || in.Title == "" || in.Capacity <= 0 || in.StartsAt.IsZero() { return c.Status(fiber.StatusBadRequest).JSON(fiber.Map{"error": "title, starts_at and a positive capacity are required"}) } err := db.QueryRow(c.Context(), ` INSERT INTO events (title, starts_at, capacity) VALUES ($1, $2, $3) RETURNING id`, in.Title, in.StartsAt, in.Capacity).Scan(&in.ID) if err != nil { return err } return c.Status(fiber.StatusCreated).JSON(in) } func getEvent(c fiber.Ctx) error { id, err := strconv.ParseInt(c.Params("id"), 10, 64) if err != nil { return fiber.ErrNotFound } var ev Event err = db.QueryRow(c.Context(), ` SELECT e.id, e.title, e.starts_at, e.capacity, count(r.id) FROM events e LEFT JOIN rsvps r ON r.event_id = e.id WHERE e.id = $1 GROUP BY e.id`, id).Scan(&ev.ID, &ev.Title, &ev.StartsAt, &ev.Capacity, &ev.Going) if errors.Is(err, sqldb.ErrNoRows) { return fiber.ErrNotFound } else if err != nil { return err } return c.JSON(ev) } func rsvp(c fiber.Ctx) error { id, err := strconv.ParseInt(c.Params("id"), 10, 64) if err != nil { return fiber.ErrNotFound } var in struct { Email string `json:"email"` } if err := c.Bind().Body(&in); err != nil || !strings.Contains(in.Email, "@") { return c.Status(fiber.StatusBadRequest).JSON(fiber.Map{"error": "email is required"}) } email := strings.ToLower(strings.TrimSpace(in.Email)) ctx := c.Context() tx, err := db.Begin(ctx) if err != nil { return err } defer tx.Rollback() // no-op after Commit // Lock the event row so two RSVPs for the last seat cannot both pass the check. var capacity, going int err = tx.QueryRow(ctx, `SELECT capacity FROM events WHERE id = $1 FOR UPDATE`, id).Scan(&capacity) if errors.Is(err, sqldb.ErrNoRows) { return fiber.ErrNotFound } else if err != nil { return err } if err := tx.QueryRow(ctx, `SELECT count(*) FROM rsvps WHERE event_id = $1`, id).Scan(&going); err != nil { return err } if going >= capacity { return c.Status(fiber.StatusConflict).JSON(fiber.Map{"error": "event is full"}) } _, err = tx.Exec(ctx, `INSERT INTO rsvps (event_id, email) VALUES ($1, $2)`, id, email) if sqldb.ErrCode(err) == sqlerr.UniqueViolation { return c.Status(fiber.StatusConflict).JSON(fiber.Map{"error": "already registered"}) } else if err != nil { return err } if err := tx.Commit(); err != nil { return err } return c.Status(fiber.StatusCreated).JSON(fiber.Map{"event_id": id, "email": email, "seat": going + 1}) }

encore run starts Postgres locally, creates the events database and applies the migration before the app takes traffic, and a fresh clone works without a .env file. The SQL was also run on its own against Postgres 17 with six concurrent RSVPs for a two-seat event: two succeeded with seats 1 and 2, four got "event is full", and a repeat RSVP on a roomy event hit the unique constraint. Unique violations come back through sqldb.ErrCode(err) == sqlerr.UniqueViolation, which keeps the handler free of raw Postgres error codes.

On deploy, Encore provisions RDS on AWS or Cloud SQL on GCP in your own account, runs the migrations and connects the service with its own database role, so there is no connection string to copy between environments. For an AI coding agent, a new column is a migration file in the same folder as the handler, and encore run applies it to a local database before the change reaches review.

Best fit: Fiber APIs that want Postgres with migrations set up the same way on every laptop and in their own AWS or GCP account.

pgx and Amazon RDS

The hand-built route uses pgx directly with pgxpool, and the transaction looks almost the same. Unique violations are detected with errors.As on *pgconn.PgError and code 23505:

package main import ( "context" "errors" "log" "os" "github.com/gofiber/fiber/v3" "github.com/jackc/pgx/v5" "github.com/jackc/pgx/v5/pgconn" "github.com/jackc/pgx/v5/pgxpool" ) func main() { // DATABASE_URL points at RDS, e.g. postgres://app:...@host:5432/events?sslmode=verify-full pool, err := pgxpool.New(context.Background(), os.Getenv("DATABASE_URL")) if err != nil { log.Fatal(err) } defer pool.Close() app := fiber.New() app.Post("/events/:id/rsvps", func(c fiber.Ctx) error { var in struct { Email string `json:"email"` } if err := c.Bind().Body(&in); err != nil { return fiber.ErrBadRequest } ctx := c.Context() tx, err := pool.Begin(ctx) if err != nil { return err } defer tx.Rollback(ctx) var capacity, going int err = tx.QueryRow(ctx, `SELECT capacity FROM events WHERE id = $1 FOR UPDATE`, c.Params("id")).Scan(&capacity) if errors.Is(err, pgx.ErrNoRows) { return fiber.ErrNotFound } else if err != nil { return err } if err := tx.QueryRow(ctx, `SELECT count(*) FROM rsvps WHERE event_id = $1`, c.Params("id")).Scan(&going); err != nil { return err } if going >= capacity { return c.Status(fiber.StatusConflict).JSON(fiber.Map{"error": "event is full"}) } _, err = tx.Exec(ctx, `INSERT INTO rsvps (event_id, email) VALUES ($1, $2)`, c.Params("id"), in.Email) var pgErr *pgconn.PgError if errors.As(err, &pgErr) && pgErr.Code == "23505" { return c.Status(fiber.StatusConflict).JSON(fiber.Map{"error": "already registered"}) } else if err != nil { return err } if err := tx.Commit(ctx); err != nil { return err } return c.SendStatus(fiber.StatusCreated) }) log.Fatal(app.Listen(":8080")) }

The pool defaults to the greater of 4 and the number of CPUs per process, so set pool_max_conns in the URL when several replicas share one instance. Migrations need their own step, usually golang-migrate run from CI or a startup job. In AWS you create the RDS instance in private subnets with a DB subnet group and a security group that allows port 5432 from the app, store the password in Secrets Manager and inject it into the task, and use sslmode=verify-full with the RDS certificate bundle if you want the client to check the server certificate.

Best fit: teams that already run RDS through Terraform and want plain pgx with no layer in between.

GORM and RDS

GORM is a widely used Go ORM, and with its Postgres driver it works in Fiber handlers through db.WithContext(c.Context()). It can create tables with AutoMigrate, which is convenient early on and harder to review later, since the schema change is implied by struct tags instead of written as SQL. The capacity check needs an explicit transaction with a row lock (tx.Clauses(clause.Locking{Strength: "UPDATE"})), and provisioning RDS is the same work as the pgx route.

Best fit: teams that prefer models and associations over hand-written SQL.

How to choose

Pick Encore when you want the database declared next to the Fiber handlers, running locally with encore run and provisioned as RDS or Cloud SQL in your own account with migrations applied on deploy. Pick pgx with RDS when the database is already managed in Terraform and you want full control of the pool and the driver. GORM fits teams who would rather work with models than SQL, with the same provisioning work as pgx.

Frequently asked questions

How do I connect Fiber to PostgreSQL?

Create one pgxpool.Pool at startup from a connection string and use it in Fiber handlers with c.Context(). Run schema changes with a migration tool before the app starts, and use pool.Begin for anything that reads and writes in one step, such as checking capacity before inserting a booking.

How do I add Postgres to a Fiber app with Encore?

Declare the database with sqldb.NewDatabase and a migrations directory, mount the Fiber app in a raw endpoint with path=/!fallback through adaptor.FiberApp, and query it with db.QueryRow, db.Exec and db.Begin from Fiber handlers. encore run starts Postgres locally and applies the migrations, and deploys provision RDS on AWS or Cloud SQL on GCP in your own account.

How do I stop two requests from taking the last seat in Postgres?

Lock the parent row inside the transaction with SELECT ... FOR UPDATE, count the existing rows, and only insert when there is room. A second transaction waits on the lock and sees the first insert once it commits, and a UNIQUE constraint on event and email stops double registration.

What connection pool size does pgxpool use by default?

pgxpool defaults MaxConns to the greater of 4 and runtime.NumCPU(). Set pool_max_conns in the connection string when several replicas share one RDS instance so the total stays under the database's max_connections.

Deploy to your cloud on AWS and Google Cloud
without managing infrastructure

Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.

~/orders — claude
Claude Code
Claude Code v2.1.180Opus 5.5~/orders