Goal for this post is to introduce how to start interacting with Azure SQL through Go (https://golang.org/), an open source programming language gaining lots of traction in developers’ community thanks to its simplicity and efficiency in scenarios like microservices and server apps (did I mention that Kubernetes itself is written in Go?).
Azure SQL provides full support for Go developers on both control plane (deploy, manage and configure Azure SQL servers and databases) and data plane activities (connect, execute commands and queries against Azure SQL instances) through Azure SDK for Go and Microsoft SQL Server Driver for Go.
A companion code sample for this article, written using VS Code, can be found here.
Management operations
To deploy and configure a brand-new Azure SQL server from your own code, you can use one of the various SDKs available in Azure. For Go language, Azure SDK provides a collection of packages that can be used to interact with various resource types you need (e.g. generic resources like Resource Groups, Azure SQL, authentication mechanisms, etc.). Most important ones to import are:
Notice that in the first row, we are using an alias for the sql package as name is conflicting with the database/sql package we’ll use later in our data access code.
Before being able to interact with Azure Resource Management (ARM) APIs, we need to retrieve a token that will be used to authenticate and authorize our operations.
Next is to create a client for the specific task required, for example creating a new Azure SQL server and pass the token to it:
Then we can call the CreateOrUpdate() method passing all the required parameters like location, admin credentials and so on:
It’s important to remember that ARM APIs are asynchronous in nature. That’s why in Go SDK to check for completion of long running operations a future is returned, and in our code we can wait until the operation is finished before proceeding with other steps:
In can apply the same exact pattern to all other management operations, like creating a new database or configuring specific capabilities like replication or security.
Data access layer
Let’s move to proper data access code! Like in many other programming languages, we have two main options when interacting with our databases: through direct SQL commands or by using an Object Relational Mapper (ORM).
Microsoft SQL Server Driver for Go can be added to our code project by importing the "github.com/denisenkom/go-mssqldb" package (in addition to standard "database/sql" for database interfaces).
Opening a database connection is similar to what we would do in any other programming language, by invoking Open() method passing a connection string and getting back a reference.
Executing a query is also straightforward and can be done through the QueryXXX() methods, depending if returning one or more rows and/or resultsets. Notice proper usage of sql.Named() for named parameters:
Iterating through results with rows.Next() is equally very familiar, in the following code fragment we are assigning values of individual columns in the result sets to variables:
In use cases where you need to execute multiple times the same command, but passing different sets of parameters, a common best practice is to call db.Prepare(tsql) and create a prepared statement, so that for every following execution you will force reuse of the compiled execution plan for a more efficient interaction with the database. In the following code fragment, you can see a practical implementation:
Use an Object Relational Mapper (ORM)
ORMs are packages designed to let developers interact with their relational databases through an object oriented paradigm. In essence, we have to define some basic mapping rules between application’s objects and database tables, and these libraries will read or persist instances of our objects as rows in database tables. One of the most commonly used in Go space is called Gorm.
First step is to declare types used in our application logic to contain entities to extract or persist in our database structure. In the following example, we can see how specific tags can be used to let Gorm know how to treat specific columns, like data types to use, column names and constrains like primary keys and such. We can also create “aliases” for object names pointing to database tables with different names using TableName() functions.
Next step is to open connection with the database, in a way very similar to what you’d do with a regular database driver:
Query database to extract a single entity is than quite natural, and we don’t need to specify any T-SQL command for that, but rather invoke the Where() method passing our predicate as parameter:
Other CRUD operations are equally similar:
We can also create queries that joins multiple entities and project results into new object structures in your application logic, again in a very natural way:
In summary
Go is a very popular programming language for developing microservices, Web APIs and other server-side applications, and Azure SQL can definitely be an option where to persist data for these applications in a scalable, reliable and modern way leveraging Microsoft SQL Server Driver for Go and ORM packages like Gorm. Give it a try!