Management operations and data access for Azure SQL Database with Golang

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

 

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:

 

Pic1.png

 

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.

 

Pic2.png

 

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:

 

Pic3.png

 

Then we can call the CreateOrUpdate() method passing all the required parameters like location, admin credentials and so on:

 

Pic4.png

 

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:

 

Pic5.png

 

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.

 

Pic6.png

 

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:

 

Pic7.png

 

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:

 

Pic8.png

 

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:

 

Pic9.png

 

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.

 

Pic10.png

 

Next step is to open connection with the database, in a way very similar to what you’d do with a regular database driver:

 

Pic11.png

 

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:

 

Pic12.png

 

Other CRUD operations are equally similar:

 

Pic13.png

 

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:

 

Pic14.png

 

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!

 

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.