Skip to main content
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.
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

​
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:[email protected]: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

1

Create a new migration

make migrate-new
You’ll be prompted for a migration name:
Migration name: create_users_table
2

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
3

Run the migration

make migrate
This applies all pending migrations.

​
Migration Commands

CommandDescription
make migrateRun all pending migrations
make migrate-downRoll back the last migration
make migrate-newCreate 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

1

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;
2

Generate Go code

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

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, "[email protected]")
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:

​
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 ./...
or 
go generate ./pkg/db

​
Best Practices

Use InTx to ensure atomicity:
err := database.InTx(ctx, func(q sqlc.Querier) error {
    // All operations succeed or fail together
    return nil
})
Ensure migrations can be rolled back:
make migrate        # Apply migration
make migrate-down   # Test rollback
make migrate        # Apply again
Good:
  • create_users_table
  • add_email_index_to_users
  • add_status_column_to_orders
Avoid:
  • migration1
  • update
  • fix
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

Build docs developers (and LLMs) love

Get started for freeTalk to us