search

denisenkom / go-mssqldb

Microsoft SQL server driver written in go language
BSD 3-Clause "New" or "Revised" License
1.81k stars 493 forks source link

readme

A pure Go MSSQL driver for Go's database/sql package

Go Reference Build status codecov

For more recent updates, see the Microsoft fork.

Install

Requires Go 1.8 or above.

Install with go get github.com/denisenkom/go-mssqldb .

Connection Parameters and DSN

The recommended connection string uses a URL format: sqlserver://username:password@host/instance?param1=value&param2=value Other supported formats are listed below.

Common parameters

Connection parameters for ODBC and ADO style connection strings

Less common parameters

The connection string can be specified in one of three formats

  1. URL: with sqlserver scheme. username and password appears before the host. Any instance appears as the first segment in the path. All other options are query parameters. Examples:

    • sqlserver://username:password@host/instance?param1=value&param2=value
    • sqlserver://username:password@host:port?param1=value&param2=value
    • sqlserver://sa@localhost/SQLExpress?database=master&connection+timeout=30 // `SQLExpress instance.
    • sqlserver://sa:mypass@localhost?database=master&connection+timeout=30 // username=sa, password=mypass.
    • sqlserver://sa:mypass@localhost:1234?database=master&connection+timeout=30 // port 1234 on localhost.
    • sqlserver://sa:my%7Bpass@somehost?connection+timeout=30 // password is "my{pass" A string of this format can be constructed using the URL type in the net/url package.
    
query := url.Values{}
query.Add("app name", "MyAppName")

u := &url.URL{
    Scheme:   "sqlserver",
    User:     url.UserPassword(username, password),
    Host:     fmt.Sprintf("%s:%d", hostname, port),
    // Path:  instance, // if connecting to an instance instead of a port
    RawQuery: query.Encode(),
}
db, err := sql.Open("sqlserver", u.String())

  2. ADO: key=value pairs separated by ;. Values may not contain ;, leading and trailing whitespace is ignored. Examples:

    • server=localhost\\SQLExpress;user id=sa;database=master;app name=MyAppName
    • server=localhost;user id=sa;database=master;app name=MyAppName

    ADO strings support synonyms for database, app name, user id, and server

    • server <= addr, address, network address, data source
    • user id <= user, uid
    • database <= initial catalog
    • app name <= application name

  3. ODBC: Prefix with odbc, key=value pairs separated by ;. Allow ; by wrapping values in {}. Examples:

    • odbc:server=localhost\\SQLExpress;user id=sa;database=master;app name=MyAppName
    • odbc:server=localhost;user id=sa;database=master;app name=MyAppName
    • odbc:server=localhost;user id=sa;password={foo;bar} // Value marked with {}, password is "foo;bar"
    • odbc:server=localhost;user id=sa;password={foo{bar} // Value marked with {}, password is "foo{bar"
    • odbc:server=localhost;user id=sa;password={foobar } // Value marked with {}, password is "foobar "
    • odbc:server=localhost;user id=sa;password=foo{bar // Literal {, password is "foo{bar"
    • odbc:server=localhost;user id=sa;password=foo}bar // Literal }, password is "foo}bar"
    • odbc:server=localhost;user id=sa;password={foo{bar} // Literal {, password is "foo{bar"
    • odbc:server=localhost;user id=sa;password={foo}}bar} // Escaped } with}}`, password is "foo}bar"

Azure Active Directory authentication

Azure Active Directory authentication uses temporary authentication tokens to authenticate. The mssql package does not provide an implementation to obtain tokens: instead, import the azuread package and use driver name azuresql. This driver uses azidentity to acquire tokens using a variety of credential types.

The credential type is determined by the new fedauth connection string parameter.


import (
  "database/sql"
  "net/url"

  // Import the Azure AD driver module (also imports the regular driver package)
  "github.com/denisenkom/go-mssqldb/azuread"
)

func ConnectWithMSI() (*sql.DB, error) {
  return sql.Open(azuread.DriverName, "sqlserver://azuresql.database.windows.net?database=yourdb&fedauth=ActiveDirectoryMSI")
}

Executing Stored Procedures

To run a stored procedure, set the query text to the procedure name:


var account = "abc"
_, err := db.ExecContext(ctx, "sp_RunMe",
    sql.Named("ID", 123),
    sql.Named("Account", sql.Out{Dest: &account}),
)

Reading Output Parameters from a Stored Procedure with Resultset

To read output parameters from a stored procedure with resultset, make sure you read all the rows before reading the output parameters:


sqltextcreate := `
CREATE PROCEDURE spwithoutputandrows
    @bitparam BIT OUTPUT
AS BEGIN
    SET @bitparam = 1
    SELECT 'Row 1'
END
`
var bitout int64
rows, err := db.QueryContext(ctx, "spwithoutputandrows", sql.Named("bitparam", sql.Out{Dest: &bitout}))
var strrow string
for rows.Next() {
    err = rows.Scan(&strrow)
}
fmt.Printf("bitparam is %d", bitout)

Caveat for local temporary tables

Due to protocol limitations, temporary tables will only be allocated on the connection as a result of executing a query with zero parameters. The following query will, due to the use of a parameter, execute in its own session, and #mytemp will be de-allocated right away:

conn, err := pool.Conn(ctx)
defer conn.Close()
_, err := conn.ExecContext(ctx, "select @p1 as x into #mytemp", 1)
// at this point #mytemp is already dropped again as the session of the ExecContext is over

To work around this, always explicitly create the local temporary table in a query without any parameters. As a special case, the driver will then be able to execute the query directly on the connection-scoped session. The following example works:

conn, err := pool.Conn(ctx)

// Set us up so that temp table is always cleaned up, since conn.Close()
// merely returns conn to pool, rather than actually closing the connection.
defer func() {
    _, _ = conn.ExecContext(ctx, "drop table #mytemp")  // always clean up
    conn.Close() // merely returns conn to pool
}()

// Since we not pass any parameters below, the query will execute on the scope of
// the connection and succeed in creating the table.
_, err := conn.ExecContext(ctx, "create table #mytemp ( x int )")

// #mytemp is now available even if you pass parameters
_, err := conn.ExecContext(ctx, "insert into #mytemp (x) values (@p1)", 1)

Return Status

To get the procedure return status, pass into the parameters a *mssql.ReturnStatus. For example:


var rs mssql.ReturnStatus
_, err := db.ExecContext(ctx, "theproc", &rs)
log.Printf("status=%d", rs)

or

var rs mssql.ReturnStatus
_, err := db.QueryContext(ctx, "theproc", &rs)
for rows.Next() {
    err = rows.Scan(&val)
}
log.Printf("status=%d", rs)

Limitation: ReturnStatus cannot be retrieved using QueryRow.

Parameters

The sqlserver driver uses normal MS SQL Server syntax and expects parameters in the sql query to be in the form of either @Name or @p1 to @pN (ordinal position).


db.QueryContext(ctx, `select * from t where ID = @ID and Name = @p2;`, sql.Named("ID", 6), "Bob")

Parameter Types

To pass specific types to the query parameters, say varchar or date types, you must convert the types to the type before passing in. The following types are supported:

Important Notes

Features

Tests

go test is used for testing. A running instance of MSSQL server is required. Environment variables are used to pass login information.

Example:

    env SQLSERVER_DSN=sqlserver://user:pass@hostname/instance?database=test1 go test

AZURESERVER_DSN environment variable provides the connection string for Azure Active Directory-based authentication. If it's not set the AAD test will be skipped.

Deprecated

These features still exist in the driver, but they are are deprecated.

Query Parameter Token Replace (driver "mssql")

If you use the driver name "mssql" (rather then "sqlserver") the SQL text will be loosly parsed and an attempt to extract identifiers using one of

will be used. This is not recommended with SQL Server. There is at least one existing won't fix issue with the query parsing.

Use the native "@Name" parameters instead with the "sqlserver" driver name.

Known Issues