Database Seeder Implementation in the Go-Wordpress project

Overview

This article documents the design and implementation of a database seeder added to the go-wordpress project — a production-ready Go web service blueprint built on Clean Architecture, Uber FX, Gin, and SQLC.

The seeder system provides a safe, idempotent mechanism to populate initial data (websites and scraper configurations) on application startup, running automatically after database migrations without duplicating records across restarts.

Go-Wordpress

Git commit

Why Keep Seeds Separate from Migrations

A common question is whether to place initial data inside migration SQL files. This implementation intentionally separates them for three reasons:

Migrations are structural — they define schema changes, run once, and are versioned. Mixing data into them pollutes migration history and makes rollbacks painful.
Seeds are data — they should be idempotent, re-runnable, and potentially environment-specific. A dedicated seeder system makes this possible.
Consistency — the pattern mirrors how golang-migrate tracks applied migrations in schema_migrations, applying the same concept to data via a seed_history table.

Folder Structure

The following directories were added or modified:

text

internal/
└── storage/
    └── sql/
        ├── migrations/          ← schema only (existing)
        │   ├── 001_create_websites.up.sql
        │   └── 002_create_configs.up.sql
        ├── seeds/               ← data only (new)
        │   ├── 001_seed_websites.sql
        │   └── 002_seed_configs.sql
        └── migrate/
            ├── runner.go        ← existing migration runner
            └── seeder.go        ← new seeder

The Seeder — seeder.go

The seeder lives in the same package as the migration runner (internal/storage/sql/migrate) and follows the same structural pattern as RunMigrations.

Struct & Constructor

The Seeder holds a *sql.DB reference. Because Uber FX registers the database connection under the sqlc.DBTX interface, the constructor accepts that interface and type-asserts it to *sql.DB at runtime — the underlying type is always *sql.DB, so this is safe:

type Seeder struct {
    db *sql.DB
}
func NewSeeder(dbtx sqlc.DBTX) *Seeder {
    db, ok := dbtx.(*sql.DB)
    if !ok {
        log.Fatal("seeder requires *sql.DB, got incompatible type")
    }
    return &Seeder{
        db: db
    }
}

FX Invoke Function

RunSeeder mirrors RunMigrations exactly — it is skipped in test environments and calls panic on error to halt startup cleanly:

func RunSeeder(seeder *Seeder, cfg *config.Config) {
    if cfg.IsTest() {
        return
    }
    if err := seeder.SeederRun();
    err != nil {
        panic(err)
    }
}

The seed_history Table

On every startup, the seeder creates (if not exist) a tracking table:

sql

CREATE TABLE IF NOT EXISTS seed_history (
    id SERIAL PRIMARY KEY,
    seed_name TEXT NOT NULL UNIQUE,
    applied_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL
);

Before executing any seed file, the seeder queries this table. If the seed name already exists, it is skipped. This makes the system safe to invoke on every application startup.

Execution Flow

For each .sql file found in internal/storage/sql/seeds (sorted by filename):

Check seed_history — skip if already applied
Read the SQL file from disk using os.ReadFile
Open a database transaction
Execute the seed SQL inside the transaction
Insert the seed name into seed_history inside the same transaction
Commit — both the data insert and the history record succeed or fail together

Full Implementation

package migrate
import (
"database/sql"
"fmt"
"go-wordpress/internal/storage/sql/sqlc"
"log"
"os"
"sort"
"strings"
)
type Seeder struct {
    db *sql.DB
}
func NewSeeder(dbtx sqlc.DBTX) *Seeder {
    db, ok := dbtx.(*sql.DB)
    if !ok {
        log.Fatal("seeder requires *sql.DB, got incompatible type")
    }
    return &Seeder{
        db: db
    }
}
func RunSeeder(seeder *Seeder, cfg *config.Config) {
    if cfg.IsTest() {
        return
    }
    if err := seeder.SeederRun();
    err != nil {
        panic(err)
    }
}
func (s *Seeder) SeederRun() error {
    _, err :=
    s.db.Exec(`        CREATE TABLE IF NOT EXISTS seed_history (            id         SERIAL PRIMARY KEY,            seed_name  TEXT NOT NULL UNIQUE,            applied_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL        )    `)
    if err != nil {
        return fmt.Errorf("failed to create seed_history table: %w", err)
    }
    entries, err := os.ReadDir("internal/storage/sql/seeds")
    if err != nil {
        return fmt.Errorf("failed to read seeds directory: %w", err)
    }
    sort.Slice(entries, func(i, j int) bool {
        return entries[i].Name() < entries[j].Name()
    })
    for _, entry := range entries {
        if !strings.HasSuffix(entry.Name(), ".sql") {
            continue
        }
        seedName := entry.Name(
        )
        var exists bool err :=
        s.db.QueryRow("SELECT EXISTS(SELECT 1 FROM seed_history WHERE seed_name = $1)",
        seedName,).Scan(&exists) if err != nil {
            return fmt.Errorf("failed to check seed history for %s: %w", seedName, err)
        }
        if exists {
            log.Printf("⏭️  Seed already applied, skipping: %s", seedName) continue
        }
        content, err := os.ReadFile("internal/storage/sql/seeds/" + seedName) if err != nil {
            return fmt.Errorf("failed to read seed file %s: %w", seedName, err)
        }
        tx, err := s.db.Begin() if err != nil {
            return fmt.Errorf("failed to begin transaction for %s: %w", seedName, err)
        }
        if _, err := tx.Exec(string(content));
        err != nil {
            tx.Rollback() return fmt.Errorf("failed to execute seed %s: %w", seedName, err)
        }
        if _, err := tx.Exec("INSERT INTO seed_history (seed_name) VALUES ($1)", seedName,);
        err != nil {
            tx.Rollback() return fmt.Errorf("failed to record seed %s: %w", seedName, err)
        }
        if err := tx.Commit();
        err != nil {
            return fmt.Errorf("failed to commit seed %s: %w", seedName, err)
        }
        log.Printf("✅ Seed applied: %s", seedName)
    }
    log.Println("✅ All seeds applied successfully")
    return nil
}

Why os.ReadFile Instead of embed

The //go:embed directive does not allow .. in paths, which would be required given the package location. Since the binary is always run from the project root (go run cmd/server/main.go), os.ReadFile with a relative path is consistent with how the existing migration runner references its files using file://internal/storage/sql/migrations.

Uber FX Wiring — app.go

The seeder is registered in the FX container in NewApp(). Two things are required: providing the Seeder via fx.Provide, and invoking RunSeeder via fx.Invoke after RunMigrations. FX processes Invoke calls in declaration order, guaranteeing migrations run before seeding:

fx.Provide(    migrate.NewRunner,  // migration runner    migrate.NewSeeder,  // seeder
...),fx.Invoke(    migrate.RunMigrations,  // schema first    migrate.RunSeeder,      // data second
...),

Seed SQL Files

001_seed_websites.sql

Inserts two websites. The ON CONFLICT (domain) DO NOTHING clause uses the UNIQUE constraint on the domain column as a second safety net, independent of seed_history:

sql

INSERT INTO websites (name, domain, status, created_at, updated_at)
VALUES
    ('hosseinibrothers', 'hosseinibrothers.ir', 'active', NOW(), NOW()),
    ('badomjip', 'badomjip.com', 'active', NOW(), NOW())
ON CONFLICT (domain) DO NOTHING;

002_seed_configs.sql

Inserts scraper configuration JSON for each website. Rather than hardcoding integer IDs, a SELECT ... FROM websites WHERE domain = ... subquery resolves the correct website_id dynamically. This means seed order matters — websites must be seeded before configs, which the numeric filename prefix (001_, 002_) guarantees.

Each config record stores a full JSON scraper definition under the key scraper_config, including:

start_url — the entry point for the scraper
product_list — CSS selectors for listing pages (item container, title, price, link, image)
pagination — selector for the next-page link
product_detail — selectors for detail page content (description)

The ::json cast ensures PostgreSQL stores the value in the JSON column with proper type validation.

sql

INSERT INTO configs (
    website_id,
    key,
    value,
    status,
    created_at,
    updated_at
)
SELECT
    w.id,
    'scraper_config',
    '{
        "domain": "badomjip.com",
        "start_url": "https://badomjip.com/",
        "product_list": {
            "item": "div.product-grid-item",
            "fields": {
                "title": {
                    "selector": "h3.wd-entities-title a",
                    "attr": ""
                },
                "price": {
                    "selector": "span.price",
                    "attr": ""
                },
                "link": {
                    "selector": "a.product-image-link",
                    "attr": "href"
                },
                "image": {
                    "selector": "a.product-image-link img",
                    "attr": "src"
                }
            }
        },
        "pagination": {
            "next": "a.next.page-numbers"
        },
        "product_detail": {
            "description": {
                "selector": "div.woocommerce-product-details__short-description, div.woocommerce-Tabs-panel--description"
            }
        }
    }'::json,
    'active',
    NOW(),
    NOW()
FROM websites AS w
WHERE w.domain = 'badomjip.com'
ON CONFLICT DO NOTHING;

INSERT INTO configs (
    website_id,
    key,
    value,
    status,
    created_at,
    updated_at
)
SELECT
    w.id,
    'scraper_config',
    '{
        "domain": "hosseinibrothers.ir",
        "start_url": "https://hosseinibrothers.ir/",
        "product_list": {
            "item": "div.js-product-miniature",
            "fields": {
                "title": {
                    "selector": "a.stsb_mini_product_name",
                    "attr": ""
                },
                "price": {
                    "selector": "div.stsb_pm_price",
                    "attr": ""
                },
                "link": {
                    "selector": "a.stsb_mini_product_name",
                    "attr": "href"
                },
                "image": {
                    "selector": "img.stsb_pm_image",
                    "attr": "src"
                }
            }
        },
        "pagination": {
            "next": "a[rel=''next'']"
        },
        "product_detail": {
            "description": {
                "selector": "div.stsb_pro_summary p, div.stsb_read_more_box p"
            }
        }
    }'::json,
    'active',
    NOW(),
    NOW()
FROM websites AS w
WHERE w.domain = 'hosseinibrothers.ir'
ON CONFLICT DO NOTHING;

Key Design Decisions

Idempotency at Two Levels

Seeds are protected against duplication at both the application level (seed_history table check before execution) and the database level (ON CONFLICT clauses in the SQL itself). Even if seed_history were dropped manually. rerunning seeds would not create duplicate rows.

Transactional Integrity

Each seed file runs inside a single transaction that also includes the INSERT into seed_history. A partial failure — where data inserts but the history record fails, or vice versa — is impossible. Either both succeed or both are rolled back.

Ordered Execution

Seed files are sorted alphabetically before execution. The numeric prefix convention (001_, 002_) guarantees that dependency order is respected — websites are always seeded before configs that reference them by website_id.

Test Safety

The IsTest() Check matches the pattern already used by RunMigrations. Neither migrations nor seeds run during automated tests, keeping the test database in a predictable state controlled entirely by the test setup.

Dynamic ID Resolution

Config seeds reference websites by domain rather than by a hardcoded integer ID. This makes seeds portable across environments where auto-increment IDs may differ, and removes the fragile assumption that website ID 1 is always badomjip.com.

Conclusion

The seeder implementation adds production-grade initial data loading to go-wordpress with minimal complexity. It integrates naturally into the existing FX dependency graph, follows the same lifecycle patterns as the migration runner, and provides strong guarantees against duplicate data through layered idempotency.

Adding new seed data in the future requires only creating a new numbered .sql file in internal/storage/sql/seeds/ — no Go code changes needed.