# Seeking Optimal SQL Patterns with Go

One of the challenges software engineers face when working with SQL in Go is managing boilerplate. It can be cumbersome and repetitive, detracting from the application’s core logic. Recently, I’ve embarked on a journey exploring sqlc, a tool designed to generate type-safe code directly from SQL, seemingly promising an efficient way to handle SQL in Go.

The sqlc tool is available on GitHub, and my initial impression was positive. It offered an appealing solution for many of the standard SQL boilerplate in Go.

You can provide *.sql files to sqlc to generate the code. This is great because copying and pasting SQL queries to and from a database UI is pretty simple. sqlc requires meta information in the form of comments before each query.

For example, this query would get value from a table:

-- name: Get :one
SELECT value
FROM keys
WHERE name = @name;

The name specifies how to name the function that sqlc will generate. The :one directive says how many rows are returned from the query. You can see more in the sqlc playground of this directive affect the code generation.

However, as with any tool, the more I delved into its capabilities, the more nuances I discovered. It became evident that while sqlc is optimized for PostgreSQL, it supports MySQL and SQLite, but not some advanced features (like JSON).

I began to face challenges with sqlc when building queries that leveraged sophisticated database features, particularly those related to JSON manipulation. In these scenarios, sqlc couldn’t always maintain its contract of generating the corresponding Go boilerplate. This limitation meant reverting to manual code-writing for specific functions.

For example, the query that required custom generation:

-- name: ListRange :many
	SELECT json_each.value
	FROM keys,
		json_each(keys.value)
	WHERE keys.name = ?1
	AND json_each.key >= IIF(?2 >=0, ?2, json_array_length(keys.value) + ?2)
	AND json_each.key <= IIF(?3 >=0, ?3, json_array_length(keys.value) + ?3);

Inspired by sqlc’s premise, I considered creating my code generation. The goal was to automate the process as much as possible, allowing me to focus on the SQL logic. One of the valuable features sqlc offered was its ability to infer return types and handle type casting automatically for specific queries. In my code generation attempts, I wanted to integrate this feature, and thus, I explored leveraging ChatGPT for code generation, specifically to infer return types.

For example, give the following SQL query:

-- name: ListLength :one
SELECT json_array_length(value)
FROM keys
WHERE name = ?1

sqlc could not infer the data type given from json_array_length function. (Related to the before-mentioned missing JSON integration.) With ChatGPT, I prompted, Please fix the return type of this Go function that is an SQL query wrapper so that it returns the correct type.

So this:

func (q *Queries) ListLength(ctx context.Context, name string) (interface{}, error) {
	row := q.queryRow(ctx, q.listLengthStmt, listLength, name)
	var json_array_length interface{}
	err := row.Scan(&json_array_length)
	return json_array_length, err
}

Became:

func (q *Queries) ListLength(ctx context.Context, name string) (int64, error) {
	row := q.queryRow(ctx, q.listLengthStmt, listLength, name)
	var json_array_length int64
	err := row.Scan(&json_array_length)
	return json_array_length, err
}

The results were interesting. ChatGPT was indeed able to modify the generated code to determine return types. However, the speed left much to be desired. Using ChatGPT for code generation in real-time proved too slow for my needs, elongating the feedback loop significantly.

Despite this setback, my exploration didn’t end there. I further expanded my investigations into integrating ChatGPT with SQL queries to Go boilerplate conversion, culminating in a tool found here. This is going to require its own post because of the depth of the prompt. It works, but again, it needs to be faster to be a viable alternative for a fast feedback loop.

While my exploration with sqlc and Go was enlightening, my interest in efficient SQL handling naturally led me to consider other languages and tools. Rust’s sqlx caught my attention. This Rust SQL toolkit offers compile-time checked queries without a DSL, supporting a range of databases, which can be explored here. I’ll be working with this tool to learn Rust, too.

Tools like sqlc hold great promise, but they also underscore the complexities of integrating SQL and programming languages. As the landscape of software development tools evolves, I remain excited and optimistic about the future possibilities for efficient database management and integration.