Go SQL Server: Inserting Data

Summary: in this tutorial, you will learn how to insert a row into a table from a Go program.

Insert a row into a table

Step 1. Create a new file author.go within the project directory with the following code:

package main

import (
    "database/sql"
    "time"
)

type Author struct {
    AuthorID  int
    FirstName string
    LastName  string
    BirthDate time.Time
}

func InsertAuthor(db *sql.DB, author *Author) (int64, error) {
    // Prepare the INSERT statement
    stmt, err := db.Prepare("INSERT INTO Authors (FirstName, LastName, BirthDate) VALUES (@FirstName, @LastName, @BirthDate);")
    if err != nil {
        return 0, err
    }

    defer stmt.Close()

    // Execute the prepared statement
    result, err := stmt.Exec(
        sql.Named("FirstName", author.FirstName),
        sql.Named("LastName", author.LastName),
        sql.Named("BirthDate", author.BirthDate),
    )
    if err != nil {
        return 0, err
    }

    // Get the number of row inserted
    rowInserted, err := result.RowsAffected()
    if err != nil {
        return 0, err
    }

    return rowInserted, nil

}Code language: JavaScript (javascript)

Step 2. Define the Author struct that maps to the Authors table in the SQL Server database in the author.go:

type Author struct {
    AuthorID  int
    FirstName string
    LastName  string
    BirthDate time.Time
}Code language: JavaScript (javascript)

Step 3. Create the InsertAuthor function that inserts a new row into the Authors table:

func InsertAuthor(db *sql.DB, author *Author) (int64, error) {
    // Prepare the INSERT statement
    stmt, err := db.Prepare("INSERT INTO Authors (FirstName, LastName, BirthDate) VALUES (@FirstName, @LastName, @BirthDate);")
    if err != nil {
        return 0, err
    }

    defer stmt.Close()

    // Execute the prepared statement
    result, err := stmt.Exec(
        sql.Named("FirstName", author.FirstName),
        sql.Named("LastName", author.LastName),
        sql.Named("BirthDate", author.BirthDate),
    )
    if err != nil {
        return 0, err
    }

    // Get the number of row inserted
    rowInserted, err := result.RowsAffected()
    if err != nil {
        return 0, err
    }

    return rowInserted, nil

}Code language: JavaScript (javascript)

How it works.

First, create a prepared statement with an SQL INSERT statement:

stmt, err := db.Prepare("INSERT INTO Authors (FirstName, LastName, BirthDate) VALUES (@FirstName, @LastName, @BirthDate);")Code language: JavaScript (javascript)

The @FirstName, @LastName, and @BirthDate are placeholders. When executing the prepared statement, you need to pass the corresponding values to substitute these parameters. By using these parameters, you can prevent SQL injection attacks.

Next, close the prepared statement once the function returns using the defer keyword:

defer stmt.Close()Code language: JavaScript (javascript)

Then, execute the prepared statement with the values that come from the Author struct:

result, err := stmt.Exec(
    sql.Named("FirstName", author.FirstName),
    sql.Named("LastName", author.LastName),
    sql.Named("BirthDate", author.BirthDate),
)Code language: JavaScript (javascript)

After that, return 0 and error if an error occurred:

if err != nil {
   return 0, err
}Code language: JavaScript (javascript)

Finally, return the number of rows inserted:

rowInserted, err := result.RowsAffected()
if err != nil {
    return 0, err
} else {
    return rowInserted, nil
}Code language: JavaScript (javascript)

Step 4. Modify the main.go to call the InsertAuthor function to insert a new row into the Authors table:

package main

import (
    "fmt"
    "os"
    "time"
)

func main() {
    // Load the database configuration
    config, err := LoadConfig()
    if err != nil {
        fmt.Println(err)
        os.Exit(1)
    }

    // Connect to the database
    db, err := Connect(config)
    if err != nil {
        fmt.Println(err)
        os.Exit(1)
    }

    // Close the database connection once the main function exits
    defer db.Close()

    // Insert an author
    author := Author{
        FirstName: "John",
        LastName:  "Doe",
        BirthDate: time.Date(1980, 1, 1, 0, 0, 0, 0, time.UTC),
    }

    rowInserted, err := InsertAuthor(db, &author)
    if err != nil {
        fmt.Println(err)
        os.Exit(1)
    }
    fmt.Printf("Row inserted: %d\n", rowInserted)
}Code language: JavaScript (javascript)

How it works.

First, initialize an author struct for insertion:

author := Author{
    FirstName: "John",
    LastName:  "Doe",
    BirthDate: time.Date(1980, 1, 1, 0, 0, 0, 0, time.UTC),
}Code language: JavaScript (javascript)

Second, insert a new row into the Authors table and print out the number of rows inserted:

rowInserted, err := InsertAuthor(db, &author)
if err != nil {
    fmt.Println(err)
    os.Exit(1)
}
fmt.Printf("Row inserted: %d\n", rowInserted)Code language: JavaScript (javascript)

Step 5. Open your terminal and run the program using the following command:

go run main.go db.go author.goCode language: JavaScript (javascript)

Output:

Row inserted: 1Code language: JavaScript (javascript)

Step 6. Launch SQL Server Management Studio (SSMS), connect to the database, and select data from the Authors table:

SELECT * FROM authorsCode language: JavaScript (javascript)

Output:

Inserting a row and returning the inserted ID

Step 1. Modify the InsertAuthor function to insert a new row into the Authors table and return the inserted author ID:

func InsertAuthor(db *sql.DB, author *Author) (int64, error) {
    // Prepare the INSERT statement
    stmt, err := db.Prepare(`INSERT INTO Authors (FirstName, LastName, BirthDate) 
                             OUTPUT inserted.AuthorID
                             VALUES (@FirstName, @LastName, @BirthDate);`)
    if err != nil {
        return 0, err
    }

    defer stmt.Close()

    // Execute the prepared statement and return the query result as rows
    rows, err := stmt.Query(
        sql.Named("FirstName", author.FirstName),
        sql.Named("LastName", author.LastName),
        sql.Named("BirthDate", author.BirthDate),
    )
    if err != nil {
        return 0, err
    }

    // Get the author ID
    var authorID int64
    for rows.Next() {
        err = rows.Scan(&authorID)
        if err != nil {
            return 0, err
        }
    }

    return authorID, nil
}Code language: JavaScript (javascript)

How it works (We’ll focus on what changes):

First, prepare an insert statement that inserts a new row into a table and returns the inserted author ID:

stmt, err := db.Prepare(`INSERT INTO Authors (FirstName, LastName, BirthDate) 
                         OUTPUT inserted.AuthorID
                         VALUES (@FirstName, @LastName, @BirthDate);`)
if err != nil {
    return 0, err
}Code language: JavaScript (javascript)

Second, call the Query() method to execute the prepared statement and return the query result as rows:

rows, err := stmt.Query(
    sql.Named("FirstName", author.FirstName),
    sql.Named("LastName", author.LastName),
    sql.Named("BirthDate", author.BirthDate),
)Code language: JavaScript (javascript)

Third, iterate over the rows, read the author ID, and return it:

var authorID int64
for rows.Next() {
    err = rows.Scan(&authorID)
    if err != nil {
        return 0, err
    }
}

return authorID, nilCode language: JavaScript (javascript)

Step 2. Modify the main.go to insert a new row into the Authors table and print out the inserted row:

package main

import (
    "fmt"
    "os"
    "time"
)

func main() {
    // Load the database configuration
    config, err := LoadConfig()
    if err != nil {
        fmt.Println(err)
        os.Exit(1)
    }

    // Connect to the database
    db, err := Connect(config)
    if err != nil {
        fmt.Println(err)
        os.Exit(1)
    }

    // Close the database connection once the main function exits
    defer db.Close()

    // Insert an author
    author := Author{
        FirstName: "Jane",
        LastName:  "Doe",
        BirthDate: time.Date(1990, 12, 15, 0, 0, 0, 0, time.UTC),
    }

    authorID, err := InsertAuthor(db, &author)
    if err != nil {
        fmt.Println(err)
        os.Exit(1)
    }

    fmt.Printf("Author ID: %d\n", authorID)
}Code language: JavaScript (javascript)

Step 3. Open your terminal and run the program:

Code language: JavaScript (javascript)

go run main.go db.go author.goCode language: JavaScript (javascript)

Output:

Author ID: 2Code language: JavaScript (javascript)

The output indicates that the author with ID 2 has been inserted into the Authors table.

Step 4. Launch SQL Server Management Studio (SSMS), connect to the database, and select data from the Authors table:

SELECT * FROM authorsCode language: JavaScript (javascript)

Output:

Summary

• Use the Exec() method to execute a prepared statement to insert a new row into a table and return the number of rows inserted.
• Use the Query() method to execute a prepared statement to insert a new row into a table and return the inserted ID.