Skip to content

Solizardking/clawd-bot / .agents/skills/golang-database

golang-database

Verified

Comprehensive guide for Go database access — parameterized queries, struct scanning, NULLable columns, transactions, isolation levels, SELECT FOR UPDATE, connection pool, batch processing, context propagation, and migration tooling. Use when writing, reviewing, or debugging Golang code that interacts with PostgreSQL, MariaDB, MySQL, or SQLite; for database testing; or for questions about database/sql, sqlx, or pgx. Does NOT generate database schemas or migration SQL.

FileKindSize
SKILL.mdskill md11.3 KB
evals/evals.jsonconfig15.0 KB
references/performance.mdmarkdown8.2 KB
references/scanning.mdmarkdown2.2 KB
references/testing.mdmarkdown6.2 KB
references/transactions.mdmarkdown1.5 KB

references/testing.md

View on GitHub
# Testing Database Code

## Unit Tests with Mocks

Define a repository interface so business logic can be tested without a database. Mock the interface with `testify/mock`:

```go
// Repository interface — the contract
type UserRepository interface {
    GetByID(ctx context.Context, id int64) (*User, bool, error)
    Create(ctx context.Context, user *User) error
}

// Production implementation
type pgUserRepository struct {
    db *sqlx.DB
}

func (r *pgUserRepository) GetByID(ctx context.Context, id int64) (*User, bool, error) {
    var user User
    err := r.db.GetContext(ctx, &user, "SELECT id, name, email FROM users WHERE id = $1", id)
    if err != nil {
        if errors.Is(err, sql.ErrNoRows) {
            return nil, false, nil
        }
        return nil, false, fmt.Errorf("querying user %d: %w", id, err)
    }
    return &user, true, nil
}
```

### Mock for service-layer tests

```go
type mockUserRepo struct {
    mock.Mock
}

func (m *mockUserRepo) GetByID(ctx context.Context, id int64) (*User, error) {
    args := m.Called(ctx, id)
    if args.Get(0) == nil {
        return nil, args.Error(1)
    }
    return args.Get(0).(*User), args.Error(1)
}

func TestUserService_GetUser(t *testing.T) {
    repo := new(mockUserRepo)
    svc := NewUserService(repo)

    expected := &User{ID: 1, Name: "Alice", Email: "alice@example.com"}
    repo.On("GetByID", mock.Anything, int64(1)).Return(expected, nil)

    user, err := svc.GetUser(context.Background(), 1)
    require.NoError(t, err)
    assert.Equal(t, expected, user)
    repo.AssertExpectations(t)
}

func TestUserService_GetUser_NotFound(t *testing.T) {
    repo := new(mockUserRepo)
    svc := NewUserService(repo)

    repo.On("GetByID", mock.Anything, int64(999)).Return(nil, ErrUserNotFound)

    user, err := svc.GetUser(context.Background(), 999)
    assert.Nil(t, user)
    assert.ErrorIs(t, err, ErrUserNotFound)
}
```

Unit tests verify business logic, not SQL correctness. They run fast and without external dependencies.

## sqlmock for Query-Level Testing

When you need to verify exact SQL without a real database, use [DATA-DOG/go-sqlmock](https://github.com/DATA-DOG/go-sqlmock):

```go
func TestGetByID_sqlmock(t *testing.T) {
    db, mock, err := sqlmock.New()
    require.NoError(t, err)
    defer db.Close()

    sqlxDB := sqlx.NewDb(db, "postgres")
    repo := &pgUserRepository{db: sqlxDB}

    rows := sqlmock.NewRows([]string{"id", "name", "email"}).
        AddRow(1, "Alice", "alice@example.com")
    mock.ExpectQuery("SELECT id, name, email FROM users WHERE id = \\$1").
        WithArgs(1).
        WillReturnRows(rows)

    user, err := repo.GetByID(context.Background(), 1)
    require.NoError(t, err)
    assert.Equal(t, "Alice", user.Name)
    assert.NoError(t, mock.ExpectationsWereMet())
}
```

sqlmock is useful for verifying query structure and error handling paths, but it does not validate that your SQL is correct against a real database schema.

## Integration Tests

Integration tests run against a real database. Gate them with build tags so `go test ./...` skips them by default:

```go
//go:build integration

package repository_test

import (
    "testing"
    "github.com/stretchr/testify/suite"
)

type UserRepoSuite struct {
    suite.Suite
    db *sqlx.DB
    tx *sqlx.Tx
}

func (s *UserRepoSuite) SetupSuite() {
    dsn := os.Getenv("TEST_DATABASE_URL") // e.g., postgres://test:test@localhost:5432/testdb?sslmode=disable
    db, err := sqlx.Connect("postgres", dsn)
    s.Require().NoError(err)
    s.db = db
    // Run migrations here if needed
}

func (s *UserRepoSuite) TearDownSuite() {
    s.db.Close()
}

func (s *UserRepoSuite) SetupTest() {
    tx, err := s.db.Beginx()
    s.Require().NoError(err)
    s.tx = tx
}

func (s *UserRepoSuite) TearDownTest() {
    s.tx.Rollback() // rolls back all changes — each test starts clean
}

func (s *UserRepoSuite) TestCreateAndGet() {
    repo := NewUserRepository(s.tx)
    user := &User{Name: "Alice", Email: "alice@example.com"}

    err := repo.Create(context.Background(), user)
    s.Require().NoError(err)
    s.NotZero(user.ID)

    got, err := repo.GetByID(context.Background(), user.ID)
    s.Require().NoError(err)
    s.Equal("Alice", got.Name)
}

func TestUserRepoSuite(t *testing.T) {
    suite.Run(t, new(UserRepoSuite))
}
```

Run integration tests:

```bash
go test -tags=integration -v ./internal/repository/...
```

### Test database with testcontainers-go

For CI environments without a pre-existing database:

```go
func (s *UserRepoSuite) SetupSuite() {
    ctx := context.Background()
    container, err := postgres.Run(ctx, "postgres:16-alpine",
        postgres.WithDatabase("testdb"),
        postgres.WithUsername("test"),
        postgres.WithPassword("test"),
        testcontainers.WithWaitStrategy(
            wait.ForLog("database system is ready to accept connections").
                WithOccurrence(2).
                WithStartupTimeout(30*time.Second),
        ),
    )
    s.Require().NoError(err)
    s.container = container

    connStr, err := container.ConnectionString(ctx, "sslmode=disable")
    s.Require().NoError(err)
    s.db, err = sqlx.Connect("postgres", connStr)
    s.Require().NoError(err)
}
```

## What to Test

| What                      | Unit test (mock) | Integration test |
| ------------------------- | :--------------: | :--------------: |
| Business logic            |        ✓         |                  |
| SQL correctness           |                  |        ✓         |
| Error paths (not found)   |        ✓         |        ✓         |
| Transaction boundaries    |                  |        ✓         |
| NULL handling round-trips |                  |        ✓         |
| Constraint violations     |                  |        ✓         |
| Query performance         |                  | ✓ (with EXPLAIN) |

Unit tests MUST use mocks (interface mocks or sqlmock) — no real database connections. Integration tests MUST use build tags (`//go:build integration`) to separate from unit tests. Integration tests SHOULD use testcontainers-go for reproducible database environments in CI. NEVER test against production databases.

→ See `samber/cc-skills-golang@golang-testing` skill for general test patterns and CI configuration.
finishing-a-development-branchobra/superpowersUse when implementation is complete, all tests pass, and you need to decide how to integrate the workStars: 322kUpdated Claude CodeCodexVerifiedInstall
systematic-debuggingobra/superpowersUse when encountering any bug, test failure, or unexpected behavior, before proposing fixesStars: 322kUpdated Claude CodeCodexVerifiedInstall
test-driven-developmentobra/superpowersUse when implementing any feature or bugfix, before writing implementation codeStars: 322kUpdated Claude CodeCodexVerifiedInstall
skill-creatoranthropics/skillsCreate new skills, modify and improve existing skills, and measure skill performance. Use when users want to create a skill from scratch, edit, or optimize an existing skill, run evals to test a skill, benchmark skill performance with variance analysis, or optimize a skill's description for better triggering accuracy.Stars: 197kUpdated Claude CodeCodexVerifiedInstall