Skip to content

Latest commit

 

History

History
310 lines (239 loc) · 9.04 KB

sql-database-connect-query-go.md

File metadata and controls

310 lines (239 loc) · 9.04 KB
title description services ms.service ms.subservice ms.custom ms.devlang ms.topic author ms.author ms.reviewer manager ms.date
Use Go to query Azure SQL Database | Microsoft Docs
Use Go to create a program that connects to an Azure SQL Database, and use Transact-SQL statements to query and modify data.
sql-database
sql-database
development
go
quickstart
David-Engel
v-daveng
MightyPen
craigg
11/01/2018

Quickstart: Use Go to query an Azure SQL database

This quickstart demonstrates how to use Go to connect to an Azure SQL database. Transact-SQL statements to query and modify data are also demonstrated.

Prerequisites

To complete this quickstart, make sure you have the following prerequisites:

[!INCLUDE prerequisites-create-db]

  • A server-level firewall rule for the public IP address of the computer you use for this quickstart.

  • You have installed Go and related software for your operating system:

    • MacOS: Install Homebrew and GoLang. See Step 1.2.
    • Ubuntu: Install GoLang. See Step 1.2.
    • Windows: Install GoLang. See Step 1.2.

SQL server connection information

[!INCLUDE prerequisites-server-connection-info]

Create Go project and dependencies

  1. From the terminal, create a new project folder called SqlServerSample.

    mkdir SqlServerSample

  2. Change directory to SqlServerSample and get and install the SQL Server driver for Go:

    cd SqlServerSample
go get github.com/denisenkom/go-mssqldb
go install github.com/denisenkom/go-mssqldb

Create sample data

  1. Using your favorite text editor, create a file called CreateTestData.sql in the SqlServerSample folder. Copy and paste the following the T-SQL code inside it. This code creates a schema, table, and inserts a few rows.

    CREATE SCHEMA TestSchema;
GO

CREATE TABLE TestSchema.Employees (
  Id       INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  Name     NVARCHAR(50),
  Location NVARCHAR(50)
);
GO

INSERT INTO TestSchema.Employees (Name, Location) VALUES
  (N'Jared',  N'Australia'),
  (N'Nikita', N'India'),
  (N'Tom',    N'Germany');
GO

SELECT * FROM TestSchema.Employees;
GO

  2. Connect to the database using sqlcmd and run the SQL script to create the schema, table, and insert some rows. Replace the appropriate values for your server, database, username, and password.

    sqlcmd -S your_server.database.windows.net -U your_username -P your_password -d your_database -i ./CreateTestData.sql

Insert code to query SQL database

  1. Create a file named sample.go in the SqlServerSample folder.

  2. Open the file and replace its contents with the following code. Add the appropriate values for your server, database, username, and password. This example uses the GoLang Context methods to ensure that there is an active connection to the database server.

    package main

import (
    _ "github.com/denisenkom/go-mssqldb"
    "database/sql"
    "context"
    "log"
    "fmt"
    "errors"
)

var db *sql.DB

var server = "your_server.database.windows.net"
var port = 1433
var user = "your_username"
var password = "your_password"
var database = "your_database"

func main() {
    // Build connection string
    connString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d;database=%s;",
        server, user, password, port, database)

    var err error

    // Create connection pool
    db, err = sql.Open("sqlserver", connString)
    if err != nil {
        log.Fatal("Error creating connection pool: ", err.Error())
    }
    ctx := context.Background()
    err = db.PingContext(ctx)
    if err != nil {
        log.Fatal(err.Error())
    }
    fmt.Printf("Connected!\n")

    // Create employee
    createID, err := CreateEmployee("Jake", "United States")
    if err != nil {
        log.Fatal("Error creating Employee: ", err.Error())
    }
    fmt.Printf("Inserted ID: %d successfully.\n", createID)

    // Read employees
    count, err := ReadEmployees()
    if err != nil {
        log.Fatal("Error reading Employees: ", err.Error())
    }
    fmt.Printf("Read %d row(s) successfully.\n", count)

    // Update from database
    updatedRows, err := UpdateEmployee("Jake", "Poland")
    if err != nil {
        log.Fatal("Error updating Employee: ", err.Error())
    }
    fmt.Printf("Updated %d row(s) successfully.\n", updatedRows)

    // Delete from database
    deletedRows, err := DeleteEmployee("Jake")
    if err != nil {
        log.Fatal("Error deleting Employee: ", err.Error())
    }
    fmt.Printf("Deleted %d row(s) successfully.\n", deletedRows)
}

// CreateEmployee inserts an employee record
func CreateEmployee(name string, location string) (int64, error) {
    ctx := context.Background()
    var err error

    if db == nil {
        err = errors.New("CreateEmployee: db is null")
        return -1, err
    }

    // Check if database is alive.
    err = db.PingContext(ctx)
    if err != nil {
        return -1, err
    }

    tsql := "INSERT INTO TestSchema.Employees (Name, Location) VALUES (@Name, @Location); select convert(bigint, SCOPE_IDENTITY());"

    stmt, err := db.Prepare(tsql)
    if err != nil {
       return -1, err
    }
    defer stmt.Close()

    row := stmt.QueryRowContext(
        ctx,
        sql.Named("Name", name),
        sql.Named("Location", location))
    var newID int64
    err = row.Scan(&newID)
    if err != nil {
        return -1, err
    }

    return newID, nil
}

// ReadEmployees reads all employee records
func ReadEmployees() (int, error) {
    ctx := context.Background()

    // Check if database is alive.
    err := db.PingContext(ctx)
    if err != nil {
        return -1, err
    }

    tsql := fmt.Sprintf("SELECT Id, Name, Location FROM TestSchema.Employees;")

    // Execute query
    rows, err := db.QueryContext(ctx, tsql)
    if err != nil {
        return -1, err
    }

    defer rows.Close()

    var count int

    // Iterate through the result set.
    for rows.Next() {
        var name, location string
        var id int

        // Get values from row.
        err := rows.Scan(&id, &name, &location)
        if err != nil {
            return -1, err
        }

        fmt.Printf("ID: %d, Name: %s, Location: %s\n", id, name, location)
        count++
    }

    return count, nil
}

// UpdateEmployee updates an employee's information
func UpdateEmployee(name string, location string) (int64, error) {
    ctx := context.Background()

    // Check if database is alive.
    err := db.PingContext(ctx)
    if err != nil {
        return -1, err
    }

    tsql := fmt.Sprintf("UPDATE TestSchema.Employees SET Location = @Location WHERE Name = @Name")

    // Execute non-query with named parameters
    result, err := db.ExecContext(
        ctx,
        tsql,
        sql.Named("Location", location),
        sql.Named("Name", name))
    if err != nil {
        return -1, err
    }

    return result.RowsAffected()
}

// DeleteEmployee deletes an employee from the database
func DeleteEmployee(name string) (int64, error) {
    ctx := context.Background()

    // Check if database is alive.
    err := db.PingContext(ctx)
    if err != nil {
        return -1, err
    }

    tsql := fmt.Sprintf("DELETE FROM TestSchema.Employees WHERE Name = @Name;")

    // Execute non-query with named parameters
    result, err := db.ExecContext(ctx, tsql, sql.Named("Name", name))
    if err != nil {
        return -1, err
    }

    return result.RowsAffected()
}

Run the code

  1. At the command prompt, run the following commands:

    go run sample.go

  2. Verify the output:

    Connected!
Inserted ID: 4 successfully.
ID: 1, Name: Jared, Location: Australia
ID: 2, Name: Nikita, Location: India
ID: 3, Name: Tom, Location: Germany
ID: 4, Name: Jake, Location: United States
Read 4 row(s) successfully.
Updated 1 row(s) successfully.
Deleted 1 row(s) successfully.

Next steps