Simplify Database Operations in Golang with SQLX

Golang SQLX is a library used to facilitate database operations in Go programming language. SQLX is built on top of the standard Go database/sql package and provides more features and convenience.

In this article, we will perform some database operations using MySQL and SQLX with Go programing language.

We need to install sqlx package and sql driver. We will need mysql driver for this example.

Installation

go get github.com/jmoiron/sqlx
go get -u github.com/go-sql-driver/mysql

After installation we need to import these packages. We will import the mysql package with underscore. Because we will use it indirectly

import (
  _ "github.com/go-sql-driver/mysql"
  "github.com/jmoiron/sqlx"
)

Connection

// Database connection string
 dsn := "user:password@(localhost:3306)/database_name"

 // Initialize a mysql database connection
 db, err := sqlx.Connect("mysql", dsn)
 if err != nil {
  panic("Failed to connect to the database: " + err.Error())
 }

 // Verify the connection to the database is still alive
 err = db.Ping()
 if err != nil {
  panic("Failed to ping the database: " + err.Error())
 }

In the code above, we opened a MySQL database connection. Then we checked whether there was a connection error and whether connection was working seamlessly.

Database Compatibility

In this example we will use MySQL driver. But we can easily change this. All we need to do is install the driver we want and change the sql driver parameter in the connect function.

db, err := sqlx.Connect("postgres", "user=foo dbname=bar sslmode=disable")

// MySQL driver
import _ "github.com/go-sql-driver/mysql"

// PostgreSQL driver
import _ "github.com/lib/pq"

Automatic Struct Scanning

With the standard package we have to define all the variables and retrieve them in a for loop.

rows, err := db.Query("SELECT id, name, price FROM products")
 if err != nil {
  // Error handling
 }

 for rows.Next() {
  var id int
  var name string
  var price float64
  err = rows.Scan(&id, &name, &price)
  if err != nil {
   // Error handling
  }
  
  fmt.Println(id, name, price)
 }

However, things are easier with SQLX. It allows us to put the result returned from the database directly into the struct.

type Product struct {
 ID    int     `db:"id"`
 Name  string  `db:"name"`
 Price float64 `db:"price"`
}


 var products []Product
 // It fetches all products from database
 err = db.Select(&products, "SELECT id, name, price FROM products")
 if err != nil {
  // Error handling
 }
 fmt.Println(products)

If we want, we can get only one row with Get function. Let’s take a look at the example below.

 var product Product
 // It fetches only one row from database
 err = db.Get(&product, "SELECT id, name, price FROM products where id = 1")
 if err != nil {
  // Error handling
 }
 fmt.Println(product)

We can insert new rows by using structs.

// Create a new product
 var product Product = Product{
  Name:  "New Product",
  Price: 350,
 }

 // Insert it to products table by using struct scanning
 response, err := db.NamedExec("INSERT INTO products (name, price) VALUES (:name, :price)", &product)
 if err != nil {
  // Error handling
 }

 // Get the last inserted id
 lastId, err := response.LastInsertId()
 if err != nil {
  // Error handling
 }

 // Get affected rows count
 affectedRow, err := response.RowsAffected()
 if err != nil {
  // Error handling
 }

 fmt.Println(lastId, affectedRow)

Binding parameters for safety

SQLX supports parameter binding. This helps us prevent sql injection.

 var product Product
 // Bind id parameter
 err = db.Get(&product, "SELECT id, name, price FROM products where id = ?", 1)
 if err != nil {
  // Error handling
 }
 fmt.Println(product)

For more information you can check out the documentations below

https://jmoiron.github.io/sqlx/

https://github.com/jmoiron/sqlx/

Thank you for reading. Subscribe for more articles.