External Import for a Large OAuth2 Data API

Table of Contents

Scenario
#

The source system exposes an OAuth2-protected data API, and the data volume is too large for the built-in import tools. Direct in-system import can run into timeouts, stuck pages, hard-to-debug field mapping, and poor failure visibility.

The solution is to write a small external import program:

Get an access_token through OAuth2 client_credentials.
Pull the full data set page by page using the returned cursor.
Parse each page into a structured model and map it to fixed target columns.
Batch-write rows into MySQL with ordinary multi-value INSERT.
Support dry-run first, so the program can fetch and parse without writing to the database.
Stop on failure and avoid overwrite, ignore, or automatic table cleanup behavior.

Program Structure
#

text
cmd/importer/main.go        # CLI flags, HTTP client, database connection
internal/importer/api.go    # OAuth2 token, paged API, retry, token refresh
internal/importer/model.go  # JSON model and target column conversion
internal/importer/runner.go # cursor loop, logging, counters, stop conditions
internal/importer/writer.go # batch INSERT writer for MySQL

Configuration
#

Sensitive values are provided through command-line flags instead of being hard-coded.

go
type Config struct {
    TokenURL        string
    DataURL         string
    ClientID        string
    ClientSecret    string
    BatchNo         string
    RequestTimeout  time.Duration
    Retries         int
    InsertBatchSize int
}

BatchNo stays unchanged for one full import run, which makes API-side and local logs easier to correlate.

OAuth2 Token
#

go
func (c *APIClient) RefreshToken(ctx context.Context) error {
    form := url.Values{}
    form.Set("grant_type", "client_credentials")
    form.Set("client_id", c.cfg.ClientID)
    form.Set("client_secret", c.cfg.ClientSecret)

    req, err := http.NewRequestWithContext(ctx, http.MethodPost, c.cfg.TokenURL, strings.NewReader(form.Encode()))
    if err != nil {
        return err
    }
    req.Header.Set("Content-Type", "application/x-www-form-urlencoded")

    var token struct {
        AccessToken string `json:"access_token"`
    }
    if err := c.doJSON(req, &token); err != nil {
        return err
    }
    if token.AccessToken == "" {
        return fmt.Errorf("token response missing access_token")
    }
    c.token = token.AccessToken
    return nil
}

If a data page request returns 401 or 403, the program refreshes the token and retries the current page once.

Page Fetching
#

go
func (c *APIClient) FetchPage(ctx context.Context, cursor string) (APIResponse, error) {
    if c.token == "" {
        if err := c.RefreshToken(ctx); err != nil {
            return APIResponse{}, err
        }
    }

    page, err := c.fetchPageWithToken(ctx, cursor)
    if err == nil {
        return page, nil
    }
    if !isAuthError(err) {
        return APIResponse{}, err
    }

    if err := c.RefreshToken(ctx); err != nil {
        return APIResponse{}, err
    }
    return c.fetchPageWithToken(ctx, cursor)
}

Each page request only needs two key parameters:

batchNo: the import batch number.
cursor: the next-page cursor returned by the previous page; empty for the first page.

If the API returns hasMore=true without nextCursor, stop with an error to avoid an infinite loop or missing data.

Main Loop
#

go
func (r Runner) Run(ctx context.Context, opts RunOptions) error {
    var cursor string
    var totalInserted int64

    for pageNo := 1; ; pageNo++ {
        page, err := r.api.FetchPage(ctx, cursor)
        if err != nil {
            return fmt.Errorf("fetch page=%d cursor=%q inserted=%d: %w", pageNo, cursor, totalInserted, err)
        }

        inserted, err := r.writer.Insert(ctx, page.Data.Items)
        if err != nil {
            return fmt.Errorf("insert page=%d cursor=%q inserted=%d pageItems=%d: %w",
                pageNo, cursor, totalInserted, len(page.Data.Items), err)
        }
        totalInserted += inserted

        if !page.Data.HasMore {
            return nil
        }
        cursor = page.Data.NextCursor
    }
}

The log should include page number, item count, inserted count, total inserted count, hasMore, nextCursor, elapsed time, and the API trace ID. When a failure happens, this gives enough context to locate the page and cursor.

Batch Write
#

The writer only performs ordinary INSERT. It does not run UPDATE, REPLACE, INSERT IGNORE, or ON DUPLICATE KEY UPDATE.

go
func BuildInsertSQL(table string, rows []Row) (string, []any) {
    cols := InsertColumns()
    placeholders := "(" + strings.TrimRight(strings.Repeat("?,", len(cols)), ",") + ")"

    var b strings.Builder
    b.WriteString("INSERT INTO ")
    b.WriteString(quoteIdent(table))
    b.WriteString(" (")
    for i, col := range cols {
        if i > 0 {
            b.WriteString(", ")
        }
        b.WriteString(quoteIdent(col))
    }
    b.WriteString(") VALUES ")

    args := make([]any, 0, len(rows)*len(cols))
    for i, row := range rows {
        if i > 0 {
            b.WriteString(",")
        }
        b.WriteString(placeholders)
        args = append(args, row.Values()...)
    }
    return b.String(), args
}

Table and column identifiers are restricted to letters, digits, and underscores, then quoted with backticks. This avoids injecting unsafe identifiers into SQL.

Dry Run
#

Before the real import, run a dry-run for one page and print only the first item field names, not the values.

sh
./data-import \
  --dry-run \
  --limit-pages 1 \
  --print-first-item-fields \
  --batch-no 202607020001 \
  --client-id 'CLIENT_ID' \
  --client-secret 'CLIENT_SECRET'

After field parsing is confirmed, pass database parameters and run the real insert. Start with an insert batch size such as 500 or 1000, then adjust based on API and database pressure.

Notes
#

Do not commit API URLs, client secrets, or database passwords.
If the target table must be cleared, do it manually and confirm it outside the program.
Let duplicate primary keys fail fast at the database level instead of silently ignoring them.
Normalize numeric and date fields so empty strings, null, string numbers, and common date formats are handled consistently.
For large imports, always keep logs for batch number, page number, cursor, and total inserted count.

Scenario#

Program Structure#

Configuration#

OAuth2 Token#

Page Fetching#

Main Loop#

Batch Write#

Dry Run#

Notes#