Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
While building real world applications we might need to persist our application data into persistent storage. This is the reason why almost all general purpose programming languages provide some sort of database programming support. Go, without exception, also includes a standard library for database access. The standard library is pretty minimal and helps with the heavy lifting of many of the repetitive tasks involved with database programming. This article introduces the basics concepts of accessing databases through Golang code.
Database Programming in Golang
Although Go is an excellent choice for system programming – such as building API servers, web servers and other high performance networked systems – it is also well-equipped to handle database programming chores such as CRUD operations, connection pooling, and error-handling quite elegantly. The library for database programming is called database/sql. Go provides excellent documentation and a very readable source code of the library that can be efficiently used by the programmer to get more insight into its intricacies.
Read: How to Work with Strings in Go.
The database/sql Package
The database/sql library is designed to work with relational databases but works pretty well with some of the non-relational databases as well. The functionality of this library is akin to the function provided by ODBC or Java’s JDBC. Despite their similarities, however, they do have differences, due to their different design concepts.
The database specific functionalities are actually provided by the open source drivers and have to be imported separately in Go code. For example, to connect with MySQL (4.1+), MariaDB, Percona Server, Google CloudSQL, or Sphinx (2.2.3+) we may use the lightweight driver that supports connections over TCP/IPv4, TCP/IPv6, Unix domain sockets, or custom protocols. It can automatically handle broken connections and supports connection pooling through the database/sql package. Complete details on this specific drive can be found in its github documentation.
The package database/sql actually handles those aspects of database communication that are not specific to a particular database – be it MySQL or any other. Its utility is to provide a common interface across many databases. As a result, these facilities are factored out into a uniform interface and dispensed to the programmer. The database agnostic design of the library decouples it from any specific database. This ensures that the Go database code written by the programmer can remain almost unchanged even if the underlying database or the driver is changed. Go code becomes much more maintainable due to this.
The key struct types that are used with database programming and found in the package database/sql are as follows:
- DB: This sql.DB type represents a database handle to zero or more database connections or in other words it represents the database itself as an object that we can manipulate. Unlike other languages this is not a representation of connection but an object. The internal connection pool manages the connections and creates and frees connection automatically. It is safe for concurrent use.
- Results: The type that embodies the results obtained from database interaction are sql.Rows and sql.Row type. The sql.Rows is used for fetching multiple rows from a query whereas sql.Row is used for fetching a single row result. The type sql.Result is used to examine the effect of the query statements by summarizing the result of the executed SQL command.
- Statements: The sq.stmt type is used to represent SQL commands and interact with them as prepared statements. It also enables indirect interaction using the convenience function on sql.DB variables.
- Transaction: The sql.tx represents transaction and its properties.
Database Programming in Go Code Example
Let us create a Go program which will provide a functional implementation of a database program. Here we will use MySQL as our underlying database. But before writing any code make sure that the MySQL driver for the database/sql package is installed properly. This is a lightweight, fast driver written in Go and connects over the TCP/IP protocol. The installation instructions can be found in the documentation.
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
func main() {
//connecting database
db, err := sql.Open("mysql", "root:kamikaze@tcp(127.0.0.1:3306)/mytestdb")
if err != nil {
panic(err.Error())
}
defer db.Close()
//creating table through SQL DDL commands
_, err = db.Exec("CREATE TABLE IF NOT EXISTS mytestdb.emp(id int, name varchar(50), email varchar(50))")
if err != nil {
panic(err.Error())
}
//inserting 5 records
result, err := db.Exec("INSERT INTO mytestdb.emp(id,name,email) VALUES (101,'mickey','mickey@gmail.com'),(102,'Donald','donald@gmail.com'),(103,'Tom','tom@gmail.com'),(104,'Jerry','jerry@gmail.com'),(105,'Minnie','minnie@gmail.com')")
if err != nil {
panic(err.Error())
}
rc, err := result.RowsAffected()
if err != nil {
panic(err.Error())
}
fmt.Printf("inserted %d rows\n", rc)
//simple select query to fetch all records
rows, err := db.Query("SELECT * FROM mytestdb.emp")
if err != nil {
panic(err.Error())
}
// use this struct to populate results returned from the database
type emp struct {
ID int
Name string
Email string
}
for rows.Next() {
var e emp
err = rows.Scan(&e.ID, &e.Name, &e.Email)
if err != nil {
panic(err.Error())
}
fmt.Printf("%d %s %s\n", e.ID, e.Name, e.Email)
}
}
Here are some highlights on what we have done in the above code example:
- Imported database/sql package and loaded MySQl driver
- Connection established with the database through sql.Open() which returns sql.DB object. The sql.Open() function takes the driver name and connection string.
- Used db.Exec() to create a table, insert row and get row count.
- Used db.Query() to fire an SQL query and fetch rows which is then iterated over using rows.Next(). We then used the rows.Scan() function to copy columns from the current row into a variable of custom struct type called emp.
- Finally, we used db.Close() to clean up the resource.
Not an ORM Database
Go adheres to the philosophy of not hiding behind many abstractions. This library is no exception and gives direct control of resource and memory management in the hands of the programmer. Note that this library is not an ORM (Object Relational Mapper). The problem with ORM is that there is too much abstraction that makes it more complex. In spite of all the complexity, ORM has its use but does not seem to go along well with Go philosophy. However, there are some third-party libraries which provide ORM-like features and can be used with Go database code.
Go Database Tutorial
Go provides several struct types to define the data model and can be seamlessly used to work with many different types of traditional databases like MySQL, PostgreSQL or NoSQL databases, including popular platforms such as MongoDB or RethinkDB, which typically are used to build real-time web applications or InfluxDB, a popular time series database written in Go. Here, in this Go tutorial, we introduced the basic concept of database programming in Go, keeping things as simple as possible. But note that it is just the tip of the iceberg and that there are many intricacies involved. We shall keep on exploring databases and Golang in future articles. Stay tuned!
Check out these helpful courses on The Complete Developers Guide, How to Learn Go and Building Modern Applications with Go.
