Month: July 2022

Querying SQL Server with Golang

Words: 333

Time to read: ~ 2 minutes

I’ve been looking into Golang recently. Hey, everyone has to go with what interests them!

It’s all very well and good to open up Golang and write a FizzBuzz (note to self: write a FizzBuzz), but I still work with databases.

So before I do anything with Golang, I’d like to know: can it interact with databases. 

Granted, everything can, but how easy is it? And does trying to interact with databases kill any interest I have in the language.

So, let’s give it a go.

First, some caveats.

  1. You have to install Go (shocking, I’m aware).
  2. This post is effectively me reading the tutorials on the Golang website and converting it to work with SQL Server.
  3. You have to do some funky stuff to get the driver for SQL Server; granted, funky things basically mean run go get <Github link>. I’ve done funkier…
  4. There’s also some better way to do this, but who cares. This is exploratory.
  5. When setting up my SQL instances, I did some weird thing where I now have to specify a non-standard port when connecting. So I have to open up the error log in SQL Server, see what port it’s listening to, and put that into the code. Yes, I’m aware that I’m a DBA, and I should be able to fix this, but it’s my personal time & my personal test instance. I’ll fix it when I want to.

Anyway, here’s the code. 

Do with it what you will; I’m just happy it works, and I’m delighted that I’m excited about it!

package main
import (
"database/sql"
"fmt"
"log"
// this is the driver & yes, even though we're throwing
// it away to `_`, it's still needed
_ "github.com/denisenkom/go-mssqldb"
)
var (
db *sql.DB
server = "localhost"
// Yes, I hate this too…
port = 60515
)
func main() {
// Build connection string
connString := fmt.Sprintf(
"server=%s;port=%d;Trusted_Connection=True;AppName='Golang'",
server,
port,
)
// I want the line that the error occurs in my logs, please & thank you
log.SetFlags(log.Lshortfile)
// Create connection pool
var err error
db, err = sql.Open("sqlserver", connString)
if err != nil {
log.Fatalln("Creating connection pool failed:", err.Error())
}
stringDate, err := getDate()
if err != nil {
log.Fatalln("getDate() failed:", err.Error())
}
fmt.Println("Read:", stringDate)
fmt.Println("Read rows successfully!")
fmt.Println("Finished.")
}
func getDate() (string, error) {
var dte string
// We're "scanning" the values from the query into the `dte`
// variable using `&<var name>`. Different, but interesting…
if err := db.QueryRow("SELECT dt = SYSDATETIME();").Scan(&dte); err != nil {
return "false", fmt.Errorf("getDate() function failed! %s", err.Error())
}
return dte, nil
}
view raw db.go hosted with ❤ by GitHub

First of all, what happens when it works?

go run main.go

And what happens when it fails, say cause the default port actively refuses connections?!

// changing `port = 60515` -> `port = 60514`

Happy out! Now to see what else we can do. Thankfully, I’ve somehow managed to get some ideas marinating in creative juices. Let’s see if I have the time and the energy to see them through!

T-SQL Tuesday #152

Words: 375

Time to read: ~ 3 minutes

I don’t know if this post is a rage against “database-ubiquitous” SQL or Object-Relational Mapping tools (ORMs)

I’m going to say that it’s a rant against the first because if you know what you are doing, then ORMs won’t be a performance concern

But people use ORMs instead of Stored Procedures because
Them: “what happens if we need to change databases? We don’t have to worry about that now cause they’ll be baked in the application, and we won’t have to recreate the Stored Procedures in the new database cause ORMs just work!”

Everything just works until it doesn’t

I’ve worked with several companies and heard this argument many times from developers

Part of why I’m ranting about database-ubiquitousness SQL instead of ORMs is that I’ve also witnessed SQL code that has to work everywhere

I’m going to call this SQL “DUh SQL” from now on since I only have so many vowels on my keyboard and shouldn’t waste them re-writing the word “ubiquitousness”

Them: “Nope, we can’t use DATEFROMPARTS; it’s not available in other databases! Yeah, we use GETDATE() everywhere; what’s your point? WHAT!?!”

I’ve moved across different cloud providers more than I’ve moved databases, and guess what? Each cloud provider move, we’ve kept the same database

I know that knowledge sharing “in the wild” says that you should use ORMs or DUh SQL
The route to a 10x engineer is paved in DRY, KISS, and SWALK
Well, maybe not SWALK

Them: “But the leading minds out there say that you should keep it DUh since that means it’ll be a breeze if we have to move databases”

Sure, and I’ve been told that ice baths help in recovery
I’m not going to take them, though, since I know there’s a difference between me trying to do a 5k and dedicated athletes!

I love arguing with people in my blog posts; I rarely lose

So, don’t use DUh SQL cause of reasons that may not apply to you
Don’t refuse performance tuning efforts if they’ll add database-specific code
And, please, learn to use your tools

Oh, and apologies for the lack of full-stops, I can’t use them in case I have to turn this post into regex