Using Go (Golang) to Transfer 200,000 User Records Between SQL Databases

Transferring 200,000 user records from one SQL database to another using Go (Golang) involves a similar process, but with SQL operations and Go's database/sql package. The following demonstration uses MySQL as an example, but it can be adapted for other SQL databases.

First, make sure you have the necessary MySQL driver package:

go get -u github.com/go-sql-driver/mysql

Here's an example script in Go:

package main

import (
    "database/sql"
    "fmt"
    "log"
    _ "github.com/go-sql-driver/mysql"
)

const batchSize = 1000

func main() {
    sourceDsn := "user:password@tcp(sourceHost:sourcePort)/sourceDatabase"
    targetDsn := "user:password@tcp(targetHost:targetPort)/targetDatabase"

    sourceDb, err := sql.Open("mysql", sourceDsn)
    if err != nil {
        log.Fatal(err)
    }
    defer sourceDb.Close()

    targetDb, err := sql.Open("mysql", targetDsn)
    if err != nil {
        log.Fatal(err)
    }
    defer targetDb.Close()

    transferUsers(sourceDb, targetDb, batchSize)
}

func transferUsers(sourceDb, targetDb *sql.DB, batchSize int) {
    // Count total users in source database
    var totalUsers int
    err := sourceDb.QueryRow("SELECT COUNT(*) FROM users").Scan(&totalUsers)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("Total users to transfer: %d\n", totalUsers)

    offset := 0
    for offset < totalUsers {
        rows, err := sourceDb.Query("SELECT * FROM users LIMIT ? OFFSET ?", batchSize, offset)
        if err != nil {
            log.Fatal(err)
        }

        tx, err := targetDb.Begin()
        if err != nil {
            log.Fatal(err)
        }

        stmt, err := tx.Prepare("INSERT INTO users (column1, column2, ...) VALUES (?, ?, ...)")
        if err != nil {
            log.Fatal(err)
        }

        var rowCount int
        for rows.Next() {
            var col1, col2, ... sql.NullString // Adjust columns as needed
            err = rows.Scan(&col1, &col2, ...)
            if err != nil {
                log.Fatal(err)
            }

            _, err = stmt.Exec(col1, col2, ...)
            if err != nil {
                log.Fatal(err)
            }

            rowCount++
        }

        err = tx.Commit()
        if err != nil {
            log.Fatal(err)
        }

        fmt.Printf("Transferred batch of %d users\n", rowCount)
        offset += batchSize
    }

    fmt.Println("Transfer complete")
}

Explanation:

1. Setup: Import necessary packages and define constants. For MySQL, we use the github.com/go-sql-driver/mysql driver.
2. Connection: Establish connections to both source and target databases using the sql.Open function.
3. Batch Process:
   • Determine the total number of users in the source database using QueryRow and Scan.
   • Iterate over the user records in batches. The batch size is set as a constant (1000 in this example) but can be adjusted.
   • Fetch a batch of users using the Query method with LIMIT and OFFSET to control pagination.
   • For each batch, prepare an SQL statement for insertion in the target database and execute it for each row.
   • Use transactions (Begin) to ensure consistency and improve performance.
   • Increment the offset to fetch the next batch.
4. Error Handling and Cleanup: Properly handle potential errors and ensure the database connections are closed with defer.

This method efficiently handles the transfer using batching and transactions, ensuring it operates within memory limits and performs optimally. Adjust the batchSize as necessary to optimize for your specific use case.