SQL generation: Golang's builder pattern vs Clojure's persistent map
Tags: sql, datamapping, orm, builder
Published: 2024-06-08 10:34:13

---
title: SQL generation: Golang's builder pattern vs Clojure's persistent map
published: true
description: 
tags: sql,datamapping,orm,builder
# cover_image: https://direct_url_to_image.jpg
# Use a ratio of 100:42 for best results.
# published_at: 2024-06-08 09:25 +0000
---

I worked on a TODO code assignment for showing off my skills, and more importantly, showing my weak points. I coded in Golang and Masterminds/squirrel. Later, I ported only the SQL generation part to Clojure to compare and discuss why I prefer Clojure, which I have usually been asked about or even met with opposition for. I will discuss function by function and type by type. The first function is _makeStatement_.

```Go
func (repo *TodoRepoPg) makeStatement(orders []entity.Order, filters []entity.Filter) (string, []any, error) {
	builder := repo.Builder.Select("id, title, description, created, image, status")
	if err := buildOrders(&builder, orders); err != nil {
		return "", nil, err
	}
	if err := buildFilters(&builder, filters); err != nil {
		return "", nil, err
	}
	return builder.From("task").ToSql()
}
```

The _makeStatement_ function's name clearly indicates it utilizes the builder pattern. However, to improve readability and avoid cluttering the function with too many details, it delegates order and filter information building to separate functions: _buildOrders_ and _buildFilters_. Next one is the make-statement function in Clojure with HoneySQL.

```Clojure
(defn make-statement [orders filters]
  (sql/format (merge {:select [:id :description :status]
                      :from [:task]}
                     (filters->map filters)
                     (orders->map orders))))
```
In Clojure version, the main difference is that _filters->map_ and _orders->map_ are pure functions, which won't mutate or change their inputs like _buildOrders_ and _buildFilters_ do with the builder in Golang. The next one I will show contract or type or spec.

```Go
const (
	ID = iota
	Title
	Description
	Date
	Status
)

const (
	ASC = iota
	DESC
)

type Order struct {
	Field        int
	SortingOrder int
}

type Filter struct {
	Field int
	Value string
}
```

In Golang, to complement function definitions, I define custom types for conveying order and filter information. While using strings for this purpose is also acceptable, I prefer using types to leverage Go's static analysis and prevent typos.

```Clojure
(s/def :db1/orders (s/coll-of (s/tuple #{:title :created :status} #{:+ :-})))
(s/def :db1/filters (s/coll-of (s/tuple #{:title :description} any?)))
```

On the other hand, in Clojure, I defined similar contracts using Clojure Spec. Here, the information about orders and filters being collections of tuples resides within the Spec definition itself, unlike the separate function definitions in Golang.

```Go
func buildOrders(builder *squirrel.SelectBuilder, orders []entity.Order) error {
	for _, order := range orders {
		var fieldName string
		switch order.Field {
		case entity.Title:
			fieldName = "title"
		case entity.Date:
			fieldName = "created"
		case entity.Status:
			fieldName = "status"
		default:
			return fmt.Errorf("invalid field: %d", order.Field)
		}
		var sortOrder string
		switch order.SortingOrder {
		case entity.ASC:
			sortOrder = "ASC"
		case entity.DESC:
			sortOrder = "DESC"
		default:
			return fmt.Errorf("invalid sorting order: %d", order.SortingOrder)
		}
		orderExpr := fieldName + " " + sortOrder
		*builder = builder.OrderBy(orderExpr)
	}
	return nil
}
```

_buildOrders_ looks very familiar. It reminds me of Pascal, which I learned 30 years ago. This suggests that the code utilizes a well-established approach, making it understandable to most programmers even without prior Go experience. However, I've identified potential code duplication between the type definition and the switch-case within this function.

```Clojure
(defn orders->map [orders] 
  (when-not (s/valid? :db1/orders orders)
    (throw (ex-info "Invalid input orders" (s/explain-data :db1/orders orders))))

  (->> orders
       (mapv #(let [[field order-dir] %] 
                [field (case order-dir
                         :+ :asc
                         :- :desc)]))
       (array-map :order-by)))
```

The Clojure function _orders->map_ might have surprised my younger self from 30 years ago. However, it leverages Clojure Spec to its full potential. Spec validates the input to the function, and provide clear explanations when validation fails. Furthermore, _orders->map_ is a pure function, meaning it doesn't modify its input data. Both the input and output data leverage Clojure's persistent maps, a fundamental data structure known for immutability. Therefore,
unit testing for the _orders->map_ function is relatively straightforward. I have no idea how to write a unit test for _buildOrders_ in Go. 

```Clojure
(deftest generate-orders-maps
  (is (= {:order-by []}
         (orders->map [])))
  (is (= {:order-by [[:title :desc]]}
         (orders->map [[:title :-]])))
  (is (= {:order-by [[:status :asc]]}
         (orders->map [[:status :+]])))
  (is (thrown-with-msg? Exception 
                        #"Invalid input orders"
                        (orders->map [[:id :+]]))))
```

In conclusion, Go's main advantage lies in its familiarity for programmers from various languages like Pascal, Java, JavaScript, Python, and C. This familiarity extends to the builder pattern, which offers the additional benefit of auto-completion in IDEs and smart editors. On the other hand, Clojure and HoneySQL emphasize using data structures, especially persistent maps, for building queries. 

While auto-completion is less important for Clojure programmers who are comfortable manipulating basic data structures, Clojure Spec offers significant advantages in data validation.

Spec can explain what happens when data fails to meet the requirements, promoting better error handling and adherence to the open-closed principle (where code can be extended without modifying existing functionality). Additionally, Clojure Spec is not part of the function definition itself, allowing for greater flexibility and potential separation of concerns.

More importantly, writing unit tests in Clojure with HoneySQL is significantly more efficient. Because orders->map is based on persistent data structures, it avoids modifying the input data. This immutability, along with the ease of comparing maps, makes them ideal for testing.