Golang – Package – denisenkom – go-mssqldb – Sample – Microsoft

Background

Let us utilize one of Microsoft’s Sample Code to better understand Go and DeniseKom – go-mssqldb package.

Outline

  1. Database
    • Data Definition Language ( DDL )
      • Create Database Schema
      • Create Database Table
    • Security/Permission
      • Users
        • Create Database User
      • Permission
        • Grant permission to User
    • Data Manipulation Language ( DML )
      • Remove existing data
      • Add data
  2. Source Code
    • Get Source Code
    • Review Source Code
    • Execute Source Code

 

Database

Data Definition Language ( DDL )

Schemas

Schema – TestSchema


if schema_id('TestSchema') is null
begin

    exec('CREATE SCHEMA [TestSchema] authorization [dbo]')

end
go


Tables

Table – TestSchema.Employees



if object_id('TestSchema.Employees') is null
begin

    CREATE TABLE [TestSchema].[Employees] 
	(
        Id       INT IDENTITY(1,1) NOT NULL
				
      , Name     NVARCHAR(50)

      , Location NVARCHAR(50)

	  , constraint [PK_Employees]
	    primary key
		(
			[Id]
		)
    );

end
GO


Security/Permission

Users

Create Database User

Database User – golang

USE [master]
GO

declare @principal sysname

set @principal = 'golang';

if suser_id(@principal) is null
begin

    CREATE LOGIN [golang] 
          WITH PASSWORD=N'ZoveDo9H5DABlt'
        , DEFAULT_DATABASE=[master]
        , CHECK_EXPIRATION=OFF
        , CHECK_POLICY=OFF

end

use [lab]

if user_id(@principal) is null
begin

    CREATE USER [golang] 
    FROM LOGIN  [golang];

end

GO


Permissions

Grant Permission to Principal

Database Object / Principal – TestSchema.Employees / golang

grant select, insert, delete, update on TestSchema.Employees to [golang]
go


Data Manipulation Language ( DML )

Tables

Table – TestSchema.Employees

Truncate

truncate table [TestSchema].[Employees];
Add Data

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

Source Code

Identify Source Code

Here is our sample source code:-

Azure / Azure SQL / SQL Database
Quickstart: Use Golang to query a database in Azure SQL Database or Azure SQL Managed Instance
Link

 

Get Source Code


/*

	Quickstart: Use Golang to query a database in Azure SQL Database or Azure SQL Managed Instance

	https://docs.microsoft.com/en-us/azure/azure-sql/database/connect-query-go

*/
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()
}

Review Source Code

  1. Imported the following packages
    • github.com/denisenkom/go-mssqldb
    • database/sql
    • context
    • log
    • fmt
    • errors
  2. Declare variables
    • Variable db of type *sql.DB
  3. Declare & Set Database Connection Profile
    • server = “<your_server.database.windows.net>”
    • var port = 1433
    • var user = “<your_username>”
    • var password = “<your_password>”
    • var database = “<your_database>”
  4. Build Database Connection ( variable connString )
  5. Declare variables
    • err as type error
  6. Connect to database
    • Function call – sql.Open
    • Arguments
      • Database Type:- sqlserver
      • Database Connection String:- connString
  7. If unable to connect to database
    • Raise error
      • Function call – log.Fatal
  8. Create Context
    • context.Background ( Link )
      • As this is happening on our main thread, please use context.Background
      • Store context in ctx
  9. Validate Database Context
    • db.PingContext ( Link )
      • Validate that our database connection is still valid
      • If not, please reconnect
  10. Add a new employee
  11. Read all employees
  12. Update specific employee
  13. Remove specific employee

 

Execute Source Code

Syntax


go run [source-file]

Sample


go run

 

Output

Output – Image

Output – Text


>go run database.microsoft.edited.go
Connected!
Inserted 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.


 

Source Code Control

  1. Folder

References

  1. Denisenkom
    • GitHub – denisenkom
      Link
    • GitHub – denisenkom – Repository – go-mssqldb
      Link
  2. Microsoft
    • Azure / Azure SQL / SQL Database
      • Quickstart: Use Golang to query a database in Azure SQL Database or Azure SQL Managed Instance
        Link
  3. GoLang.Org
    • Packages
      • Package – SQL
        Link
      • Package – Context
        Link

One thought on “Golang – Package – denisenkom – go-mssqldb – Sample – Microsoft

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s