# 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.