SQL databases

The connection module

A global module owns the pool, so every feature can inject it. The connection is opened and pinged during boot, so a wrong URL stops the app before it listens instead of failing the first request.

package database

import (
    "context"
    "database/sql"
    "time"

    _ "github.com/jackc/pgx/v5/stdlib" // registers the "pgx" driver

    "github.com/dygoframework/dygo"
    "github.com/dygoframework/dygo/config"
    "github.com/dygoframework/dygo/health"
)

type Config struct {
    URL      string `env:"DATABASE_URL" required:"true"`
    MaxConns int    `env:"DATABASE_MAX_CONNS" default:"10"`
}

// DB wraps *sql.DB so the container can close it on shutdown.
type DB struct{ *sql.DB }

func Open(ctx context.Context, cfg *Config) (*DB, error) {
    db, err := sql.Open("pgx", cfg.URL)
    if err != nil {
        return nil, err
    }
    db.SetMaxOpenConns(cfg.MaxConns)
    db.SetConnMaxIdleTime(5 * time.Minute)
    if err := db.PingContext(ctx); err != nil { // fail the boot, not the first request
        db.Close()
        return nil, err
    }
    return &DB{db}, nil
}

// OnApplicationShutdown runs after in-flight requests have finished. A test
// can override *DB with &database.DB{}, which has nothing to close.
func (db *DB) OnApplicationShutdown(context.Context, string) error {
    if db.DB == nil {
        return nil
    }
    return db.Close()
}

var Module = &dygo.Module{
    Name:    "database",
    Global:  true,
    Imports: []*dygo.Module{config.ForFeature[Config](config.Files(".env"))},
    Providers: []dygo.Provider{
        dygo.Provide(Open),
        dygo.Provide(func(db *DB) health.Indicator { return health.Ping("db", db) }, dygo.Group(health.Indicators)),
    },
    Exports: []dygo.Token{dygo.TypeOf[*DB]()},
}

// InTx runs fn in a transaction: commit when fn returns nil, roll back otherwise.
func (db *DB) InTx(ctx context.Context, fn func(tx *sql.Tx) error) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    if err := fn(tx); err != nil {
        _ = tx.Rollback()
        return err
    }
    return tx.Commit()
}

OnApplicationShutdown runs after the HTTP server has drained in-flight requests, so no handler loses its connection mid-query. The health indicator makes GET /health/ready answer 503 when the database is down.

From memory to a database

.env decides where the data goes. An app made by dygo new starts with DATABASE_URL=memory:// and imports sql.Module(nil) (the database package in the modular layout), so the services dygo g crud and dygo g resource write keep their data in memory, and it is lost when the app stops. Those services use *sql.Repository[T], so the same code works on a real database:

1. dygo db init -driver postgres (once per app) adds the driver and puts the database URL in .env.
2. dygo db create, dygo g migration (it writes the CREATE TABLE for every entity) and dygo db migrate.
3. Start the app. The log says which database it uses: database connected url=postgres://... or database in memory.

Setting DATABASE_URL=memory:// again goes back to memory, with no code change. In memory, sql.Where clauses and raw SQL fail with sql.ErrMemory; they need a real database. Feature tests use sql.MemoryModule(), so they never need one.