Add a Database to a Go API and Deploy It to AWS with an AI Coding Agent

"Add a products table and deploy it" is one of the most common things people ask a coding agent to do in a Go backend. The Go part is quick for a model, since it comes down to a struct and a pair of handlers around an INSERT ... RETURNING id.

Getting the change into AWS takes longer, because the database has to exist in the account, be reachable from the service, have credentials the service can read and have the migration applied before the new code serves traffic.

There is also a gap between how fast the change feels and how fast it is. In METR's 2025 randomized study, experienced open source developers took 19 percent longer on tasks when AI tools were allowed, while estimating afterwards that they had been 20 percent faster. A database change that compiles but fails on deploy is a common way to lose that time.

What "add a database and deploy" involves

pgx with RDS by hand

On the application side, the agent adds pgx and reads a connection string from the environment. This is the usual Go setup and works with any Postgres:

package plain

import (
	"context"
	"os"

	"github.com/jackc/pgx/v5/pgxpool"
)

// DATABASE_URL points at a local Postgres in development and at RDS in AWS.
func OpenDB(ctx context.Context) (*pgxpool.Pool, error) {
	return pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
}

func CreateProduct(ctx context.Context, db *pgxpool.Pool, name string, priceCents int) (int64, error) {
	var id int64
	err := db.QueryRow(ctx,
		`INSERT INTO products (name, price_cents) VALUES ($1, $2) RETURNING id`,
		name, priceCents).Scan(&id)
	return id, err
}

The AWS side is where most of the work sits. The agent writes an Amazon RDS instance in private subnets, a security group rule from the service, a Secrets Manager secret for the password and a policy on the task role so the container can read it. It also has to choose a migration tool and decide where migrations run, since running them from the agent's machine against production is exactly what most teams want to avoid. Every one of these steps is checked only when it is applied to a real account, which is slow feedback for an agent that would otherwise iterate in seconds.

Encore: the database as part of the Go service

Encore combines an open source Go SDK with a platform that provisions the infrastructure the code declares, locally and in your own AWS account. The agent declares the database next to the endpoints that use it, and the SQL database primitive handles the connection:

package catalog

import (
	"context"

	"encore.dev/storage/sqldb"
)

// Postgres locally under encore run, and RDS in your AWS account on deploy.
var db = sqldb.NewDatabase("catalog", sqldb.DatabaseConfig{
	Migrations: "./migrations",
})

type Product struct {
	ID         int64  `json:"id"`
	Name       string `json:"name"`
	PriceCents int    `json:"price_cents"`
}

type CreateParams struct {
	Name       string `json:"name"`
	PriceCents int    `json:"price_cents"`
}

//encore:api public method=POST path=/products
func Create(ctx context.Context, p *CreateParams) (*Product, error) {
	product := &Product{Name: p.Name, PriceCents: p.PriceCents}
	err := db.QueryRow(ctx, `
		INSERT INTO products (name, price_cents)
		VALUES ($1, $2)
		RETURNING id`, p.Name, p.PriceCents).Scan(&product.ID)
	if err != nil {
		return nil, err
	}
	return product, nil
}

//encore:api public method=GET path=/products/:id
func Get(ctx context.Context, id int64) (*Product, error) {
	product := &Product{ID: id}
	err := db.QueryRow(ctx, `
		SELECT name, price_cents FROM products WHERE id = $1`, id,
	).Scan(&product.Name, &product.PriceCents)
	if err != nil {
		return nil, err
	}
	return product, nil
}

The schema lives in catalog/migrations/1_create_products.up.sql:

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price_cents INTEGER NOT NULL CHECK (price_cents >= 0)
);

The CHECK constraint is worth asking the agent for, since Postgres check constraints reject bad rows no matter which code path writes them. encore run starts Postgres locally and applies the migration, and encore test ./... runs Go tests against a real test database, so the agent can confirm that Create and Get round-trip before it says the work is done. Through the local MCP server it can also read the schema and query the table directly.

Deploying is a push:

git add -A && git commit -m "Add products table"
git push encore

On deploy, Encore provisions RDS in your connected AWS account and runs the migration, giving each container its own database credentials, so the production password is not part of the agent's diff, and cloud access sits with Encore's IAM role, not the agent. Asked to "add a database and deploy," the agent produces a Go diff with a migration, which is easy to review in a pull request.

Good to know

Encore provisions PostgreSQL, so a MySQL requirement means using a driver directly against a database you run. Existing tools such as sqlc or Ent can be used on top of the connection Encore provides. Instance size and backups are set per environment outside the code.

How to choose

• Encore when you want the agent to add the database and test it locally, then ship the change to your own AWS account with a push.
• pgx on RDS by hand when a platform team already owns the database layer and the service only needs a connection string.

Related guides

• How to add a database to a Go API
• Go ORMs compared
• How to deploy a database to AWS without Terraform
• Best Go Web framework for AI coding agents
• Best Go framework for microservices