Skip to content

db.md

Latest commit

 

History

History
199 lines (158 loc) · 5.38 KB

db.md

File metadata and controls

199 lines (158 loc) · 5.38 KB

DB Addon

addons/db is experimental 0.x database/sql plumbing. It does not own schemas, migrations, sqlc output, repositories, domain validation, tenants, or resource authorization.

Open A Database

Import the driver in app-owned Go, then open the database through the helper:

package data

import (
	"database/sql"
	"os"

	gowdkdb "github.com/cssbruno/gowdk/addons/db"
	_ "github.com/jackc/pgx/v5/stdlib"
)

func Open() (*sql.DB, error) {
	return gowdkdb.OpenWithOptions("pgx", os.Getenv("DATABASE_URL"), gowdkdb.Options{
		MaxOpenConns: 10,
		MaxIdleConns: 5,
	})
}

The DB addon imports no SQL driver. Keep driver dependencies in the application module or in an optional nested module.

Apply SQL Migrations

Embed user-authored SQL files and apply them in lexical order:

package data

import (
	"context"
	"database/sql"
	"embed"

	gowdkdb "github.com/cssbruno/gowdk/addons/db"
)

//go:embed migrations/*.sql
var migrationFiles embed.FS

func ApplyMigrations(ctx context.Context, database *sql.DB) error {
	_, err := gowdkdb.ApplyMigrations(ctx, database, migrationFiles, gowdkdb.MigrationOptions{
		Dir:         "migrations",
		Placeholder: gowdkdb.DollarPlaceholder,
	})
	return err
}

Tracking contract:

  • The default tracking table is gowdk_schema_migrations.
  • Each row stores migration file name, SHA-256 checksum, and applied timestamp. Migration names are limited to 255 characters so the tracking table can use a bounded primary key on MySQL-compatible drivers.
  • Re-running the same file with the same checksum skips it.
  • Re-running the same file name with different content fails with a checksum mismatch.
  • Before user SQL runs, ApplyMigrations reserves the migration name in the tracking table and finalizes the checksum after the SQL succeeds. Concurrent runners are serialized by that primary-key reservation; a runner that sees an incomplete reservation fails closed instead of executing the same file again.
  • MigrationResult is returned only after the transaction commits. If a migration, reservation, tracking update, or commit fails, the returned result is empty.
  • MigrationOptions.Table can rename the tracking table, but only simple SQL identifiers are accepted.
  • QuestionPlaceholder is the default. Use DollarPlaceholder for PostgreSQL-style drivers.

GOWDK does not split SQL files, generate migration files, infer schema state, or own rollback/down semantics. Keep those policies in normal Go and SQL tooling.

Transactions

Use WithTx when an action, API, command, or service method needs one transaction:

func CreatePatient(ctx context.Context, database *sql.DB, input CreatePatientInput) error {
	return gowdkdb.WithTx(ctx, database, nil, func(ctx context.Context, tx *sql.Tx) error {
		queries := patientdb.New(tx)
		return queries.CreatePatient(ctx, patientdb.CreatePatientParams{
			Name: input.Name,
		})
	})
}

WithTx begins with the provided context, commits when the function returns nil, rolls back when it returns an error, and rolls back before re-panicking if the function panics. Context cancellation before BeginTx prevents the function from running.

Readiness

Generated apps already expose /_gowdk/health for process and artifact status. Database readiness is app-owned because DSNs, tenants, credentials, and failure policy are app-specific. Expose it through a normal generated API when needed:

api DatabaseReady GET "/api/ready"

func DatabaseReady(ctx context.Context, _ *http.Request) (response.Response, error) {
	readiness := gowdkdb.CheckReadiness(ctx, database)
	status := http.StatusOK
	if !readiness.Ready {
		status = http.StatusServiceUnavailable
	}
	return response.JSONValue(status, readiness)
}

Do not return DSNs, credentials, tenant IDs, or query results from readiness endpoints. CheckReadiness returns a generic public error string on failure; use Ping directly when server-side startup or logging code needs the wrapped database error.

sqlc Walkthrough

Install and run sqlc in the application module; GOWDK does not run it for you:

go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest
sqlc generate
go test ./...

Minimal app layout:

db/schema.sql
db/query.sql
internal/patientdb/   # generated by sqlc
src/patients.go       # GOWDK action/API handlers call patientdb

sqlc.yaml:

version: "2"
sql:
  - engine: "postgresql"
    schema: "db/schema.sql"
    queries: "db/query.sql"
    gen:
      go:
        package: "patientdb"
        out: "internal/patientdb"

db/query.sql:

-- name: CreatePatient :one
INSERT INTO patients (name) VALUES ($1)
RETURNING id, name;

Handler usage:

func SavePatient(ctx context.Context, values form.Values) (response.Response, error) {
	name := strings.TrimSpace(values.First("name"))
	err := gowdkdb.WithTx(ctx, database, nil, func(ctx context.Context, tx *sql.Tx) error {
		queries := patientdb.New(tx)
		_, err := queries.CreatePatient(ctx, name)
		return err
	})
	if err != nil {
		return response.Response{}, err
	}
	return response.RedirectTo("/patients"), nil
}

Test the GOWDK helper package and the isolated real-driver module from this repository:

go test ./addons/db
(cd addons/db/sqlitetest && go test ./...)
scripts/test-go-modules.sh

scripts/test-go-modules.sh includes addons/db/sqlitetest, so CI covers the real-driver SQLite smoke without adding that driver to the root module graph.