Adding a database to a Go API typically involves Docker setup, connection string management, migration tooling, and production provisioning. This guide shows a faster approach where the database is provisioned automatically based on your code.

The Traditional Setup

A typical PostgreSQL setup for a Go API looks like this:

# Start PostgreSQL with Docker
docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=myapp \
  -p 5432:5432 \
  postgres:15

# Set environment variable
export DATABASE_URL=postgres://postgres:secret@localhost:5432/myapp

# Install dependencies
go get github.com/lib/pq

# Run migrations (after setting up a migration tool like golang-migrate)
migrate -path ./migrations -database $DATABASE_URL up

Then in your code:

import (
    "database/sql"
    "log"
    "os"

    _ "github.com/lib/pq"
)

db, err := sql.Open("postgres", os.Getenv("DATABASE_URL"))
if err != nil {
    log.Fatal(err)
}

// Hope the connection string is correct...
var name string
err = db.QueryRow("SELECT name FROM users WHERE id = $1", id).Scan(&name)

This works, but involves:

• Docker configuration and management
• Environment variables across environments
• Connection string errors
• Separate migration tooling setup (golang-migrate, goose, etc.)
• Production database provisioning

The 5-Minute Approach

With infrastructure-from-code, you declare the database in Go and it's provisioned automatically.

Step 1: Create the Project

Install the CLI and create a new project:

# Install (macOS)
brew install encoredev/tap/encore

# Or Linux/Windows
curl -L https://encore.dev/install.sh | bash

# Create project
encore app create myapp --example=go/hello-world
cd myapp

Step 2: Define the Database

Create a service directory and declare the database. In Encore.go, a service is simply a Go package with at least one API endpoint:

// users/db.go
package users

import "encore.dev/storage/sqldb"

var db = sqldb.NewDatabase("users", sqldb.DatabaseConfig{
    Migrations: "./migrations",
})

Step 3: Add a Migration

Create the migrations directory and your first migration:

mkdir users/migrations

-- users/migrations/001_create_users.up.sql
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Step 4: Build the API

Create endpoints that use the database:

// users/api.go
package users

import (
    "context"

    "encore.dev/beta/errs"
)

type User struct {
    ID        int64  `json:"id"`
    Email     string `json:"email"`
    Name      string `json:"name"`
    CreatedAt string `json:"createdAt"`
}

type CreateUserParams struct {
    Email string `json:"email"`
    Name  string `json:"name"`
}

//encore:api public method=POST path=/users
func Create(ctx context.Context, p *CreateUserParams) (*User, error) {
    var user User
    err := db.QueryRow(ctx,
        `INSERT INTO users (email, name)
         VALUES ($1, $2)
         RETURNING id, email, name, created_at`,
        p.Email, p.Name,
    ).Scan(&user.ID, &user.Email, &user.Name, &user.CreatedAt)
    if err != nil {
        return nil, err
    }
    return &user, nil
}

//encore:api public method=GET path=/users/:id
func Get(ctx context.Context, id int64) (*User, error) {
    var user User
    err := db.QueryRow(ctx,
        "SELECT id, email, name, created_at FROM users WHERE id = $1", id,
    ).Scan(&user.ID, &user.Email, &user.Name, &user.CreatedAt)
    if err != nil {
        return nil, &errs.Error{Code: errs.NotFound, Message: "user not found"}
    }
    return &user, nil
}

type ListResponse struct {
    Users []*User `json:"users"`
}

//encore:api public method=GET path=/users
func List(ctx context.Context) (*ListResponse, error) {
    rows, err := db.Query(ctx,
        "SELECT id, email, name, created_at FROM users ORDER BY created_at DESC",
    )
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var users []*User
    for rows.Next() {
        var u User
        if err := rows.Scan(&u.ID, &u.Email, &u.Name, &u.CreatedAt); err != nil {
            return nil, err
        }
        users = append(users, &u)
    }
    return &ListResponse{Users: users}, nil
}

Step 5: Run It

encore run

That's it. Encore:

• Provisions a local PostgreSQL database
• Runs your migrations automatically
• Starts the API server
• Opens a local dashboard at localhost:9400

You skip the usual Docker setup and connection string configuration.

Test it:

# Create a user
curl -X POST http://localhost:4000/users \
  -H "Content-Type: application/json" \
  -d '{"email": "[email protected]", "name": "Alice"}'

# Get a user by ID
curl http://localhost:4000/users/1

# List users
curl http://localhost:4000/users

How It Works

The sqldb.NewDatabase declaration tells Encore your service needs PostgreSQL:

var db = sqldb.NewDatabase("users", sqldb.DatabaseConfig{
    Migrations: "./migrations",
})

During local development, Encore provisions a PostgreSQL instance automatically. The database is isolated per project, so multiple projects don't conflict.

For production, you provide your own PostgreSQL connection string when running the compiled application. Encore handles credential injection and connection pooling.

Type-Safe Queries

Encore's database API mirrors database/sql, so there's very little new to learn if you already know Go's standard database patterns. Queries use $1, $2 parameter placeholders for SQL injection protection:

// Single row lookup
var user User
err := db.QueryRow(ctx,
    "SELECT id, email, name FROM users WHERE email = $1", email,
).Scan(&user.ID, &user.Email, &user.Name)

For aggregate queries, you use the same pattern:

type UserStats struct {
    TotalUsers  int64 `json:"totalUsers"`
    ActiveToday int64 `json:"activeToday"`
}

var stats UserStats
err := db.QueryRow(ctx, `
    SELECT
        COUNT(*) AS total_users,
        COUNT(*) FILTER (WHERE last_active > NOW() - INTERVAL '1 day') AS active_today
    FROM users
`).Scan(&stats.TotalUsers, &stats.ActiveToday)

For operations that don't return rows, use Exec:

_, err := db.Exec(ctx, "DELETE FROM users WHERE id = $1", id)

Adding More Tables

Add migrations as your schema evolves:

-- users/migrations/002_add_profile.up.sql
ALTER TABLE users ADD COLUMN bio TEXT;
ALTER TABLE users ADD COLUMN avatar_url TEXT;

Migrations run automatically on startup, both locally and in production.

Multiple Databases

For larger applications, you might want separate databases per service. In Encore.go, each service is a Go package, and each can declare its own database:

// users/db.go
package users

var db = sqldb.NewDatabase("users", sqldb.DatabaseConfig{
    Migrations: "./migrations",
})

// orders/db.go
package orders

var db = sqldb.NewDatabase("orders", sqldb.DatabaseConfig{
    Migrations: "./migrations",
})

Each database is provisioned independently. Services can only access their own databases by default, enforcing clean boundaries.

Using ORMs

If you prefer an ORM, you can use GORM, ent, or any other Go database library alongside Encore's database primitives. Use the Stdlib() method to get a standard *sql.DB connection that works with any ORM:

package users

import (
    "encore.dev/storage/sqldb"
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

var db = sqldb.NewDatabase("users", sqldb.DatabaseConfig{
    Migrations: "./migrations",
})

var orm *gorm.DB

func init() {
    var err error
    orm, err = gorm.Open(postgres.New(postgres.Config{
        Conn: db.Stdlib(),
    }))
    if err != nil {
        panic(err)
    }
}

You get Encore's automatic provisioning with your preferred query interface.

Inspecting the Database

Access your local database directly:

# Open psql shell
encore db shell users

# Get connection string (useful for external tools)
encore db conn-uri users

The local dashboard also provides database inspection tools.

Production Deployment

Self-Hosted

Build a Docker image and deploy anywhere:

# Build Docker image
encore build docker myapp:latest

# Run with your PostgreSQL
docker run -e DB_USERS_CONN_STRING="postgres://..." myapp:latest

You can deploy to any container platform: Kubernetes, ECS, Cloud Run, Fly.io, or your own servers. Provide your database connection string as an environment variable.

Managed Deployment (Encore Cloud)

For automatic infrastructure provisioning, Encore Cloud can deploy to your AWS or GCP account:

encore app link
git push encore

Encore Cloud provisions RDS or Cloud SQL automatically with appropriate security groups, backups, and credential management. See the deployment docs for details.

What You Skipped

By using infrastructure-from-code for your database:

• No Docker setup: database runs automatically
• No connection strings: credentials managed for you
• No environment variables: same code works everywhere
• No migration tooling setup: migrations run on startup
• No production provisioning: database created on deploy

The database is part of your application, not a separate infrastructure concern.

Related Reading

• Database Documentation
• Using ORMs with Encore
• How to Build a REST API with Go
• How to Build Microservices with Go

Have questions? Join our Discord community where developers help each other daily.