When it comes to SQL the Golang ecosystem offers a lot of choices. You can choose between full-featured object-relational mappers like gorm, the Golang standard library or sqlx for instance. This article will show you how you can use named parameters together with sqlx.

The code snippets related to this article can also be found here.

sqlx is a library which provides a set of extensions on top of Go’s standard database/sql library. One of the benefits it offers is support for named parameters. So instead of using positional parameters using the ? syntax you can refer to your SQL parameters using actual names.

This allows you to build queries which are easier to maintain and understand. Take a look at the book repository for example:

// file golang-sqlx-named-parameters/golang/repository/bookrepo/bookrepo.go
package bookrepo

import (
	"github.com/jmoiron/sqlx"
	"main/model"
	"strings"
)

func buildCreateQuery() string {

	const createQuery = `
INSERT INTO books
(
 	title
)
VALUES
(
	:title
)
RETURNING *
`
	return createQuery
}

func Create(db *sqlx.DB, book *model.Book) (*model.Book, error) {

	rows, err := db.NamedQuery(buildCreateQuery(), book)

	if err != nil {
		return nil, err
	}
	defer rows.Close()

	if !rows.Next() {
		return nil, rows.Err()
	}

	createdBook := &model.Book{}
	err = rows.StructScan(createdBook)
	return createdBook, err

}

func buildSearchQuery(searchParams *SearchParams) string {
	sb := strings.Builder{}
	sb.WriteString(`SELECT * FROM books WHERE (1 = 1) `)

	if searchParams.Title != nil {
		sb.WriteString(`AND (title = :title)`)
	}

	return sb.String()
}

func Search(db *sqlx.DB, searchParams *SearchParams) ([]model.Book, error) {
	var books []model.Book
	searchQuery := buildSearchQuery(searchParams)
	rows, err := db.NamedQuery(searchQuery, *searchParams)
	if err != nil {
		return books, err
	}
	defer rows.Close()

	for rows.Next() {
		var book model.Book
		err = rows.StructScan(&book)
		if err != nil {
			return books, err
		}
		books = append(books, book)
	}

	return books, rows.Err()
}

Instead of using the question mark as a placeholder for SQL parameters we are using named parameters here. sqlx maps the named parameters to the Golang fields by taking a look at the db tags:

// file golang-sqlx-named-parameters/golang/model/book.go
package model

type Book struct {
	ID    string `db:"id"`
	Title string `db:"title"`
}

// file golang-sqlx-named-parameters/golang/repository/bookrepo/searchParams.go
package bookrepo

type SearchParams struct {
	Title *string `db:"title"`
}

I think you kind of get the idea by just looking at the provided source code ;-) You can even customize this mapping behaviour by configuring your own mapper function by executing db.MapperFunc(…):

db.MapperFunc(func(name string) string {
	// You can implement any mapping magic you want here...
	return name
})

But by default sqlx will try to do the mapping by using the provided db tag of course. If there is no tag the mapped name will be just the name of the field.

Execute the example code

First you have to startup the Postgres Database: postgres$ docker-compose up --build. As soon as the DB has started up change into the folder containing the Golang source code and execute the following command: golang$ go run main.go. The following output should then be printed to the console:

user@pc:~/repos/codesandbox/golang-sqlx-named-parameters/golang$ go run main.go 
2022/09/19 16:15:52 Creating DB conn host:localhost port:5432 username:postgres
2022/09/19 16:15:52 DB conn created
2022/09/19 16:15:52 Created book: &{ID:98c6f825-44e2-4e95-a629-f872c4759894 Title:alice in wonderland}
2022/09/19 16:15:52 Now going to search for books...
2022/09/19 16:15:52 Found books: [{ID:98c6f825-44e2-4e95-a629-f872c4759894 Title:alice in wonderland}]
2022/09/19 16:15:52 Finished successfully
user@pc:~/repos/codesandbox/golang-sqlx-named-parameters/golang$

Conclusion

If the Golang standard library does not offer enough for your needs when it comes to SQL you can take a look at sqlx. It offers some handy features on top of the standard library when it comes to marshalling / unmarshalling or named parameters. But I would also suggest that you consider using an object-relational mapper (ORM) like gorm. It allows you to implement many use cases (e.g. support for simple CRUD operations) with even far less boilerplate code that sqlx.

Besides the libraries which were already covered there exist a lot of other solutions in the Golang ecosystem. Here you can find a curated list of object-relational mappers (ORMs) and over here you can find a curated list of libraries which can be compared to sqlx.

In the end gorm can be compared to Hibernate / Spring Data JPA while sqlx is more like the Spring JdbcTemplate. And the SQL support of the Go standard library could then be compared to JDBC itself of course.

The code snippets related to this article can also be found here.