Database Development

The Go Template includes built-in PostgreSQL support with migrations (goose) and type-safe query generation (sqlc).

Database Stack

The template uses:

PostgreSQL 18 - Alpine-based container for local development
goose - Migration tool for versioning schema changes
sqlc - Generates type-safe Go code from SQL queries
pgx/v5 - High-performance PostgreSQL driver and toolkit

PostgreSQL Setup

Starting the Database

The PostgreSQL service is opt-in. You can use the bundled Docker container or connect to an external database.

Docker (Local)
External Database

Start the PostgreSQL container:

make db

This starts PostgreSQL on localhost:5432 with:

User: postgres
Password: postgres
Database: app

Stop the database:

make db-down

To use an external PostgreSQL instance, skip make db and configure the DATABASE_URL in your .env file:

DATABASE_URL=postgres://user:password@hostname:5432/dbname?sslmode=require

Database Configuration

Configure your database connection in .env:

# Local Docker database
DATABASE_URL=postgres://postgres:postgres@localhost:5432/app?sslmode=disable

# External database with SSL
DATABASE_URL=postgres://user:password@db.example.com:5432/production?sslmode=require

Database Migrations

The template uses goose for managing database schema changes.

Migration Files

Migrations are stored in pkg/db/migrations/. Each migration is a SQL file with up and down sections:

-- +goose Up
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- +goose Down
DROP TABLE users;

Creating Migrations

Create a new migration

make migrate-new

You’ll be prompted for a migration name:

Migration name: create_users_table

Edit the migration file

Open the generated file in pkg/db/migrations/ and add your SQL:

-- +goose Up
-- Add your schema changes here

-- +goose Down
-- Add rollback logic here

Run the migration

make migrate

This applies all pending migrations.

Migration Commands

Command	Description
`make migrate`	Run all pending migrations
`make migrate-down`	Roll back the last migration
`make migrate-new`	Create a new migration file

Always test rollback migrations (-- +goose Down) to ensure they work correctly. This is critical for safe deployments.

Type-Safe Queries with sqlc

The template uses sqlc to generate type-safe Go code from SQL queries.

Configuration

The sqlc configuration is in pkg/db/sqlc.yaml:

version: "2"

sql:
  - engine: "postgresql"
    queries: "./queries"
    schema: "./migrations"
    gen:
      go:
        package: "sqlc"
        out: "./sqlc"
        sql_package: "pgx/v5"
        emit_json_tags: true
        json_tags_case_style: camel
        emit_interface: true
        emit_prepared_queries: true

Key settings:

queries: ./queries - SQL query files
schema: ./migrations - Migration files (for schema awareness)
out: ./sqlc - Generated Go code output directory
sql_package: pgx/v5 - Uses pgx driver

Writing Queries

Create a query file

Add SQL queries to pkg/db/queries/. For example, users.sql:

-- name: GetUserByEmail :one
SELECT id, email, created_at
FROM users
WHERE email = $1;

-- name: CreateUser :one
INSERT INTO users (email)
VALUES ($1)
RETURNING id, email, created_at;

-- name: ListUsers :many
SELECT id, email, created_at
FROM users
ORDER BY created_at DESC;

Generate Go code

make generate

This runs sqlc generate which creates type-safe Go methods in pkg/db/sqlc/.

Use the generated code

import "github.com/you/myproject/pkg/db"

// Create database connection
database, err := db.New(ctx, os.Getenv("DATABASE_URL"))
if err != nil {
    log.Fatal(err)
}
defer database.Close()

// Use generated queries
user, err := database.GetUserByEmail(ctx, "user@example.com")
if err != nil {
    log.Fatal(err)
}

Query Annotations

sqlc uses special comments to generate code:

:one - Returns a single row (error if not found)
:many - Returns a slice of rows
:exec - Executes without returning rows
:execrows - Returns number of affected rows

Database Package

The pkg/db package wraps pgxpool with additional features.

Connection Pool

import "github.com/you/myproject/pkg/db"

// Create a connection pool
database, err := db.New(ctx, databaseURL)
if err != nil {
    return fmt.Errorf("connect to database: %w", err)
}
defer database.Close()

Transactions

The package provides two ways to work with transactions:

InTx (Recommended)
Begin (Manual)

Automatic transaction management with rollback on error:

err := database.InTx(ctx, func(q sqlc.Querier) error {
    user, err := q.CreateUser(ctx, email)
    if err != nil {
        return err // Automatically rolls back
    }
    
    _, err = q.CreateProfile(ctx, user.ID)
    return err // Commits if nil, rolls back on error
})

Manual transaction control for complex cases:

q, tx, err := database.Begin(ctx)
if err != nil {
    return err
}
defer tx.Rollback(ctx)

user, err := q.CreateUser(ctx, email)
if err != nil {
    return err
}

if err := tx.Commit(ctx); err != nil {
    return err
}

Advisory Locks

For distributed coordination, the package supports PostgreSQL advisory locks:

conn, err := database.AcquireConn(ctx)
if err != nil {
    return err
}
defer conn.Release()

locked, err := db.TryAdvisoryLock(ctx, conn, 123456)
if err != nil {
    return err
}
if !locked {
    return fmt.Errorf("could not acquire lock")
}

// Perform exclusive work
// Lock is released when connection is released

Null Handling

The template uses null/v9 for nullable database fields:

import "github.com/aarondl/null/v9"

type User struct {
    ID        int32
    Email     string
    Name      null.String // Nullable
    Age       null.Int32  // Nullable
    UpdatedAt null.Time   // Nullable
}

// Check if value is present
if user.Name.IsSet() {
    fmt.Println("Name:", user.Name.Get())
}

// Set a value
user.Age = null.IntFrom(25)

Code Generation

The database package includes a go:generate directive:

//go:generate sqlc generate -f sqlc.yaml

You can regenerate code using either:

make generate  # Runs go generate ./...

go generate ./pkg/db

Best Practices

Always use transactions for multi-step operations

Use InTx to ensure atomicity:

err := database.InTx(ctx, func(q sqlc.Querier) error {
    // All operations succeed or fail together
    return nil
})

Test both up and down migrations

Ensure migrations can be rolled back:

make migrate        # Apply migration
make migrate-down   # Test rollback
make migrate        # Apply again

Use descriptive migration names

Good:

create_users_table
add_email_index_to_users
add_status_column_to_orders

Avoid:

migration1
update
fix

Keep queries in separate files by domain

Organize queries by feature:

queries/users.sql
queries/orders.sql
queries/products.sql

Next Steps

Testing

Learn about testing database code

Local Setup

Set up your development environment

Getting Started

Core Concepts

Packages

Development

Database Stack

PostgreSQL Setup

Starting the Database

Database Configuration

Database Migrations

Migration Files

Creating Migrations

Migration Commands

Type-Safe Queries with sqlc

Configuration

Writing Queries

Query Annotations

Database Package

Connection Pool

Transactions

Advisory Locks

Null Handling

Code Generation

Best Practices

Next Steps

Testing

Local Setup

Build docs developers (and LLMs) love

Getting Started

Core Concepts

Packages

Development

Documentation Index

​Database Stack

​PostgreSQL Setup

​Starting the Database

​Database Configuration

​Database Migrations

​Migration Files

​Creating Migrations

​Migration Commands

​Type-Safe Queries with sqlc

​Configuration

​Writing Queries

​Query Annotations

​Database Package

​Connection Pool

​Transactions

​Advisory Locks

​Null Handling

​Code Generation

​Best Practices

​Next Steps

Testing

Local Setup

Build docs developers (and LLMs) love

Database Stack

PostgreSQL Setup

Starting the Database

Database Configuration

Database Migrations

Migration Files

Creating Migrations

Migration Commands

Type-Safe Queries with sqlc

Configuration

Writing Queries

Query Annotations

Database Package

Connection Pool

Transactions

Advisory Locks

Null Handling

Code Generation

Best Practices

Next Steps