(Addendum - I recently ran into an issue with the query of the database in the custom_DB_functions.go package where calls returned an invalid object. It looks like you have to call it with the schema as well [mydatabase.myschema.mytable] to get the call to work. See this StackOverflow question for details.)
I created a small utility recently that pulled data from a text file to display on a web page. I showed it to my manager, and he said we might be able to integrate it with a more useful bit of our infrastructure already in place, but in order to do that, it would have to talk to an MSSQL server for content instead of the text file.
I'd never really worked with testing that setup. In searching the Internet for examples, there are lots of fragments providing hints how to do it...but nothing really spelled out an example in tutorial form. So I kept notes and now I'm sharing what I learned for other beginners that want to experiment with integrating an MSSQL database with their Go application.
I'm only going to hit some highlights in the source; the source code has quite a bit of commenting and is pretty self-documented (but if you have questions...or suggestions/corrections...please leave a comment! In the blog comments, that is.)
Creating a Test Database Server
I wanted to set up an accessible test environment for anyone, not just people who happen to have a full-on SQL Server available. I fired up my Windows VM and downloaded MS SQL Server Express 2014. Free for most purposes!
I ran the installer (the 64 bit version with tools) keeping the defaults.
I had to enable network access to the database engine.
- Open the SQL Server 2014 Configuration Manager
- Click SQL Server Network Configuration in the left hand column
- Open Protocols for SQLEXPRESS
- Make sure TCP/IP is "enabled"
- Right click TCP/IP, click Properties
- Click the IP Addresses tab
- Check that IP2 is in the local subnet
- Check that TCP Dynamic Ports is blank in the IPALL section
- Check that the TCP Port is set to 1433
- Restart the SQL Server (SQLEXPRESS) service
At this point a quick NMap scan of the VM showed that port 1433 was open (when I used the -Pn flag.)
There's another setting to change coming up...
There's another setting to change coming up...
Prepare Your Go Project to Talk to The SQL Server
Go projects talking to a SQL server need two components; a driver, and a library that abstracts that driver from the programmer. The driver depends on what type of server you're talking to, but the abstraction layer is pretty standard.
Since we're talking to MSSQL, we'll use the go-mssqldb driver. From your Go workspace run:
go get github.com/denisenkom/go-mssqldb
I decided I was going to create a test application that imported a custom package of functions that specifically accessed the database; that way I could import the resulting package to my existing application and make necessary alterations at that point.
In the package I imported the driver with the line
_ "github.com/denisenkom/go-mssqldb"
WHAT IS THAT UNDERSCORE?
The problem is that I don't use the package identifier for anything in the program; when I tried to compile it, the compiler will error. What I really needed from the driver was the initialization method; the underscore will run the init() function but ignore everything else, and the compiler won't complain.
The abstraction part...the generic SQL Go functions to interact with the database...are imported with
"database/sql"
That import is in my package (custom_DB_functions.go and sqltest.go.) The driver is only imported in the package.
The test program was literally a "add functions as I go along and verify they work" thing. Add a function, code it in the package, recompile...repeat until I had most of the functions I wanted to work.
In order to make the test program flexible, I imported the flag package and created entries for connecting to the database from the command line. Import with
import (
"flag"
)
In main(), I added a series of flags using
// Flags
ptrVersion := flag.Bool("version", false, "Display program version")
ptrDeleteIt := flag.Bool("deletedb", false, "Delete the database")
ptrServer := flag.String("server", "localhost", "Server to connect to")
ptrUser := flag.String("username", "testuser", "Username for authenticating to database; if you use a backslash, it must be escaped or in quotes")
ptrPass := flag.String("password", "", "Password for database connection")
ptrDBName := flag.String("dbname", "test_db", "Database name")
flag.Parse()
You can probably divine the meaning from the syntax, but these are in the form of
<variable> := flag.<type>("flagname", default setting if not provided at command line, "Help explanation")
The variable created is a pointer, and flag.Parse() evaluates the flags at runtime. The default values are set to the second argument if they're not changed at the command line, so you can use them as variables without having to set them to something before referring to them.
The first function I wanted to test was the creation of a database handle. It seems that the behavior of Open relies on the underlying driver; it may, as the docs say, validate arguments without a connection to the database, so it may be necessary to actually do something with the handle before active connections are made. At any rate, here's the function in sqltest.go:
db, err := sql.Open("mssql", "server="+*ptrServer+";user id="+*ptrUser+";password="+*ptrPass)
if err != nil {
fmt.Println("From Open() attempt: " + err.Error())
}
defer db.Close()
Now db is a handle to the database. Because of the way database connections are handled, you don't want to keep opening and closing them. Just defer the Close() until the program exits and that way you won't get goofy pooling/caching issues.
From what I can tell of the documentation the db handle must be kept open for the length of time that you're using it (don't close it until the program exits.) This lets the driver manage a pool of connections; when you operate on the database you use a connection from the pool. Those connection(s) you'll want to close so the connection gets returned to the connection pool. In this program I deferred the close of the db handle because the scope of the test program should keep it open until sqltest ends.
Basically Open() needs the type of database (MSSQL), the server IP or DNS name, the username, and password. I should also note that the username, if you're using Windows auth, the backslash must be entered twice so it's escaped properly or the username must be encased in quotes. for example, the connection at the command line might look like:
From what I can tell of the documentation the db handle must be kept open for the length of time that you're using it (don't close it until the program exits.) This lets the driver manage a pool of connections; when you operate on the database you use a connection from the pool. Those connection(s) you'll want to close so the connection gets returned to the connection pool. In this program I deferred the close of the db handle because the scope of the test program should keep it open until sqltest ends.
Basically Open() needs the type of database (MSSQL), the server IP or DNS name, the username, and password. I should also note that the username, if you're using Windows auth, the backslash must be entered twice so it's escaped properly or the username must be encased in quotes. for example, the connection at the command line might look like:
./sqltest -password=HelloThere -server=192.168.254.222 -username=MySystem\\testuser
Notice there's two backslashes in the username?
Skipping ahead a little, sqltest.go makes a call to PingServer(). In the package, PingServer looks like:
func PingServer(db *sql.DB) string {
err := db.Ping()
if err != nil {
return ("From Ping() Attempt: " + err.Error())
}
return ("Database Ping Worked...")
}
Skipping ahead a little, sqltest.go makes a call to PingServer(). In the package, PingServer looks like:
func PingServer(db *sql.DB) string {
err := db.Ping()
if err != nil {
return ("From Ping() Attempt: " + err.Error())
}
return ("Database Ping Worked...")
}
It's a rather simple function, and all it does is run a call to db.Ping and returns a string with an error or an affirmation that it's working. This created a working connection to the database (as discussed before), and also tested the ability to pass the database handle to a package function.
Something else to note in sqltest.go is that I called the initial Open() using sql.Open(), while the call to PingServer was simply PingServer(). The trick to that is in the import statement.
import (
"database/sql"
"flag"
"fmt"
"os"
. "custom_DB_functions"
"strconv"
)
The import for "custom_DB_functions" is preceded by a period. That allows me to refer to the functions without the preceding library name; if I preceded "fmt" with a period I should be able to use lines like Println("I'm printing this to the console!") instead of fmt.Println("I'm printing this to the console!"). I don't know what happens if I had multiple functions with the same name in different packages imported with the period...I would think the compiler would insist on the proper namespace for referencing those specific cases, but I haven't tested it.
Skipping ahead a little more there's a spot where I create the database if it doesn't already exist:
// If it doesn't exist, let's create the base database
if !boolDBExist {
CreateDBAndTable(db, *ptrDBName)
fmt.Println("********************************")
}
In the library, the call looks like this:
func CreateDBAndTable(db *sql.DB, strDBName string) error {
// Create the database
_, err := db.Exec("CREATE DATABASE [" + strDBName + "]")
if err != nil {
return (err)
}
// Let's turn off AutoClose
_, err = db.Exec("ALTER DATABASE [" + strDBName + "] SET AUTO_CLOSE OFF;")
if err != nil {
return (err)
}
// Create the tables
_, err = db.Exec("USE " + strDBName + "; CREATE TABLE testtable (source nvarchar(100) NOT NULL, timestamp bigint NOT NULL, content nvarchar(4000) NOT NULL)")
if err != nil {
return (err)
}
return nil
}
It was here that I encountered an error from the database.
Next Database Configuration Change: "CREATE DATABASE permission denied in database 'master'"
The actual SQL calls aren't all that difficult if you already understand SQL (the big notes involve not constantly opening and closing the database handle, and to use Query() when getting information back to process and Exec() when the reply is more or less either "this worked" or "error!"
In the above calls, you can see that the function creates a database, alters the AUTO_CLOSE setting on the database so it doesn't throw goofball errors when trying later queries, and then creates a table with particular attributes.
In the above calls, you can see that the function creates a database, alters the AUTO_CLOSE setting on the database so it doesn't throw goofball errors when trying later queries, and then creates a table with particular attributes.
What I got back the first time was the CREATE DATABASE permission denied in database 'master' error. That required some more tinkering with the database engine.
- Open SQL Server 2014 Management Studio and connect to the test database
- Click on your SQL EXPRESS instance
- Expand the Security folder
- Expand the Logins folder
- I created a local user on my system for testing, so I right clicked on BUILTIN\Users and select Properties
- Click on "Server Roles" on the left side
- Select "dbcreator" from the list of roles; this should be enough access
Clicking okay and re-running the database/table creation calls should work. CreateDBAndTable() and DropDB() were created mostly for testing purposes so I could periodically work with a fresh database without having to futz with Management Studio or other interface (and of course I learned how to do these tasks programmatically.)
Any Other Notes?
Between heavy commenting and naming functions and variables in a (hopefully) mostly obvious manner I think that the code is semi-obvious; most of what I would explain in text here would seem redundant or obvious. If you have questions feel free to leave a blog comment!
The only thing that comes to mind from the design point of view is the use of returning an error from the library functions instead of dumping something to the console. This means the caller is responsible for the presentation of messages; the application can decide if the returned message goes to the console or redirected to a file or possibly ignored.
Here's the source code! Hope it is somewhat helpful to someone! (Probably it will be most useful to me as a reference while trying to tune what I've been working on...)
Source Code
custom_DB_functions.go
// Package custom_DB_functions contains functions customized to manipulate MSSQL databases/tables // for our application // // Version 0.15, 8-13-2015 package custom_DB_functions import ( "database/sql" _ "github.com/denisenkom/go-mssqldb" "strconv" ) // PingServer uses a passed database handle to check if the database server works func PingServer(db *sql.DB) string { err := db.Ping() if err != nil { return ("From Ping() Attempt: " + err.Error()) } return ("Database Ping Worked...") } // CheckDB checks if the database "strDBName" exists on the MSSQL database engine. func CheckDB(db *sql.DB, strDBName string) (bool, error) { // Does the database exist? result, err := db.Query("SELECT db_id('" + strDBName + "')") defer result.Close() if err != nil { return false, err } for result.Next() { var s sql.NullString err := result.Scan(&s) if err != nil { return false, err } // Check result if s.Valid { return true, nil } else { return false, nil } } // This return() should never be hit... return false, err } // CreateDBAndTable creates a new content database on the SQL Server along with // the necessary tables. Keep in mind the user credentials that opened the database // connection with sql.Open must have at least dbcreator rights to the database. The // table (testtable) will have columns source (nvarchar), timestamp (bigint), and // content (nvarchar). func CreateDBAndTable(db *sql.DB, strDBName string) error { // Create the database _, err := db.Exec("CREATE DATABASE [" + strDBName + "]") if err != nil { return (err) } // Let's turn off AutoClose _, err = db.Exec("ALTER DATABASE [" + strDBName + "] SET AUTO_CLOSE OFF;") if err != nil { return (err) } // Create the tables _, err = db.Exec("USE " + strDBName + "; CREATE TABLE testtable (source nvarchar(100) NOT NULL, timestamp bigint NOT NULL, content nvarchar(4000) NOT NULL)") if err != nil { return (err) } return nil } // DropDB deletes the database strDBName. func DropDB(db *sql.DB, strDBName string) error { // Drop the database _, err := db.Exec("DROP DATABASE [" + strDBName + "]") if err != nil { return err } return nil } // AddToContent adds new content to the database. func AddToContent(db *sql.DB, strDBName string, strSource string, int64Timestamp int64, strContent string) error { // Add a record entry _, err := db.Exec("USE " + strDBName + "; INSERT INTO testtable (source, timestamp, content) VALUES ('" + strSource + "','" + strconv.FormatInt(int64Timestamp, 10) + "','" + strContent + "');") if err != nil { return err } return nil } // RemoveFromContentBySource removes a record from the database with source strSource. The // int64 returned is a message indicating the number of rows affected. func RemoveFromContentBySource(db *sql.DB, strSource string) (int64, error) { // Remove entries containing the source... result, err := db.Exec("DELETE FROM testtable WHERE source=$1;", strSource) if err != nil { return 0, err } // What was the result? rowsAffected, _ := result.RowsAffected() return rowsAffected, nil } // Query the content in the database and return the source (string), timestamp (int64), and // content (string) as slices func GetContent(db *sql.DB) ([]string, []int64, []string, error) { var slcstrContent []string var slcint64Timestamp []int64 var slcstrSource []string // Run the query rows, err := db.Query("SELECT source, timestamp, content FROM testtable") if err != nil { return slcstrSource, slcint64Timestamp, slcstrContent, err } defer rows.Close() for rows.Next() { // Holding variables for the content in the columns var source, content string var timestamp int64 // Get the results of the query err := rows.Scan(&source, ×tamp, &content) if err != nil { return slcstrSource, slcint64Timestamp, slcstrContent, err } // Append them into the slices that will eventually be returned to the caller slcstrSource = append(slcstrSource, source) slcstrContent = append(slcstrContent, content) slcint64Timestamp = append(slcint64Timestamp, timestamp) } return slcstrSource, slcint64Timestamp, slcstrContent, nil }
package main // Notice in the import list there's one package prefaced by a ".", // which allows referencing functions in that package without naming the library in // the call (if using . "fmt", I can call Println as Println, not fmt.Println) import ( "database/sql" "flag" "fmt" "os" . "custom_DB_functions" "strconv" ) const strVERSION string = "0.18 compiled on 8/11/2015" // sqltest is a small application for demonstrating/testing/learning about SQL database connectivity from Go func main() { // Flags ptrVersion := flag.Bool("version", false, "Display program version") ptrDeleteIt := flag.Bool("deletedb", false, "Delete the database") ptrServer := flag.String("server", "localhost", "Server to connect to") ptrUser := flag.String("username", "testuser", "Username for authenticating to database; if you use a backslash, it must be escaped or in quotes") ptrPass := flag.String("password", "", "Password for database connection") ptrDBName := flag.String("dbname", "test_db", "Database name") flag.Parse() // Does the user just want the version of the application? if *ptrVersion == true { fmt.Println("Version " + strVERSION) os.Exit(0) } // Open connection to the database server; this doesn't verify anything until you // perform an operation (such as a ping). db, err := sql.Open("mssql", "server="+*ptrServer+";user id="+*ptrUser+";password="+*ptrPass) if err != nil { fmt.Println("From Open() attempt: " + err.Error()) } // When main() is done, this should close the connections defer db.Close() // Does the user want to delete the database? if *ptrDeleteIt == true { boolDBExist, err := CheckDB(db, *ptrDBName) if err != nil { fmt.Println("Error running CheckDB: " + err.Error()) os.Exit(1) } if boolDBExist { fmt.Println("(sqltest) Deleting database " + *ptrDBName + "...") DropDB(db, *ptrDBName) os.Exit(0) } else { // Database doesn't seem to exist... fmt.Println("(sqltest) Database " + *ptrDBName + " doesn't appear to exist...") os.Exit(1) } } // Let's start the tests... fmt.Println("********************************") // Is the database running? strResult := PingServer(db) fmt.Println("(sqltest) Ping of Server Result Was: " + strResult) fmt.Println("********************************") // Does the database exist? boolDBExist, err := CheckDB(db, *ptrDBName) if err != nil { fmt.Println("(sqltest) Error running CheckDB: " + err.Error()) os.Exit(1) } fmt.Println("(sqltest) Database Existence Check: " + strconv.FormatBool(boolDBExist)) fmt.Println("********************************") // If it doesn't exist, let's create the base database if !boolDBExist { CreateDBAndTable(db, *ptrDBName) fmt.Println("********************************") } // Enter a test record boolDBExist, err = CheckDB(db, *ptrDBName) if err != nil { fmt.Println("(sqltest) CheckDB() error: " + err.Error()) os.Exit(1) } if boolDBExist == true { err := AddToContent(db, *ptrDBName, "Bob", 1437506592, "Hello!") if err != nil { fmt.Println("(sqltest) Error adding line to content: " + err.Error()) os.Exit(1) } err = AddToContent(db, *ptrDBName, "user", 1437506648, "Now testing memory") if err != nil { fmt.Println("(sqltest) Error adding line to content: " + err.Error()) os.Exit(1) } err = AddToContent(db, *ptrDBName, "user", 1437503394, "test, text!") if err != nil { fmt.Println("(sqltest) Error adding line to content: " + err.Error()) os.Exit(1) } err = AddToContent(db, *ptrDBName, "Bob", 1437506592, "Hope this works!") if err != nil { fmt.Println("(sqltest) Error adding line to content: " + err.Error()) os.Exit(1) } } fmt.Println("(sqltest) Completed entering test records.") fmt.Println("********************************") fmt.Println("(sqltest) Deleting records from a particular source.") // Delete from a source int64Deleted, err := RemoveFromContentBySource(db, "user") if err != nil { fmt.Println("(sqltest) Error deleting records by source: " + err.Error()) os.Exit(1) } else { // How many records were removed? fmt.Println("Removed " + strconv.FormatInt(int64Deleted, 10) + " records") fmt.Println("********************************") } // Get the content slcstrSource, slcint64Timestamp, slcstrContent, err := GetContent(db) if err != nil { fmt.Println("(sqltest) Error getting content: " + err.Error()) } // Now read the contents for i := range slcstrContent { fmt.Println("Entry " + strconv.Itoa(i) + ": " + strconv.FormatInt(slcint64Timestamp[i], 10) + ", from " + slcstrSource[i] + ": " + slcstrContent[i]) } }
Disappointing that nearly ten years after Little Bobby Tables satirised this we still see SQL statements constructed like:
ReplyDelete"DELETE FROM testtable WHERE source='" + strSource + "';"
The golang routines do accept parameterised arguments to the SQL, so a better way would be:
sql.Exec("DELETE FROM testtable WHERE source=$1;", strSource)
You're right; the source I quoted was something that should have been updated from a hasty test bit I had tossed in for the example. I'll update the line so the usafe version doesn't propagate.
DeleteThanks for the catch!
This comment has been removed by a blog administrator.
Delete