Love at first SELECT

I love sqlc.

It turns SQL queries into Go methods with the parameters and result types worked out, so I can read what the database will run without writing the scanning code myself.

I've used Hibernate and plenty of other ORMs, and the more I use them the less I want one, because I hate having to escape the thing that's supposed to help me write a query. Too often I end up overriding its SQL to get the query I wanted, and by then I'm learning how to get out of the tool as well as how to use it.

sqlc is still one of the best ways I know to work with SQL, but I'm having a few battles with it and building SQL myself inside the Go code. It's less quirky than escaping an ORM's world. I'm still writing SQL, without a complete jump to another environment. It's still frustrating.

Keep the SQL, keep its meaning

SQLite support is my main frustration. It feels much less comfortable than PostgreSQL once I start joining tables or using more complex expressions. Those operations shouldn't make the type information disappear.

Other people have hit these cases too, including a SQLite example combining an embedded record with a left join where the joined record can be absent. An issue with parameters disappearing inside joins and subqueries was recently closed.

PostgreSQL isn't immune either. Complex function calls can lose typing, and a nested COALESCE(SUM(...), 0) expression produces an interface{} result. Casting the whole expression gives the generator a concrete type, but I find it frustrating when the query needs extra instructions for the benefit of the generator.

I started reading the code because I figured I could contribute and help cover these cases, but to me too much of the analysis is arranged around PostgreSQL concepts. sqlc already has separate dialect parsers and code-generation plugins. Still PostgreSQL's model? I'm thinking about a contribution that's much more than a fix now. My design would let each dialect supply its own type rules and functions.

I want a generator without cgo. sqlc's PostgreSQL parser currently depends on it and brings a C toolchain into that build. Keeping my tool in Go would make that part simpler to package.

It all feels a little too big for a PR (at least one that lands politely).

Here's a small schema to work with:

CREATE TABLE authors (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author_id INTEGER NOT NULL REFERENCES authors(id)
);

An author should stay in this list even without a book in the database:

SELECT
    a.id AS author_id,
    a.name AS author_name,
    b.title AS book_title,
    COALESCE(b.title, 'No book yet') AS display_title
FROM authors AS a
LEFT JOIN books AS b ON b.author_id = a.id;

The book's title is declared NOT NULL, but the left join still returns an author without a book, so that row's book columns contain NULL and book_title must allow the missing value. Copying column declarations won't produce that record, because I have to account for what the query does to those columns. This is where I want the generator to earn its keep. COALESCE returns the first non-null argument, and it has 'No book yet' to fall back on here, so display_title has a string even when the raw title is missing. The raw title and the display title come from the same column, but need different Go types.

Result field	Can be NULL?	Intended Go type
`author_id`	No	`int64`
`author_name`	No	`string`
`book_title`	Yes, because of the join	`sql.NullString`
`display_title`	No, because of the fallback	`string`

For a database/sql target, the result record would be:

type AuthorBookRow struct {
    AuthorID     int64
    AuthorName   string
    BookTitle    sql.NullString
    DisplayTitle string
}

All I'm after is that little Go struct, and now I'm taking on SQL analysis to get it.

I need the schema, the tables visible at each point in the query, and the meaning of the expressions. That work belongs in the generator. I don't want to keep explaining the same join by hand wherever I use it.

The design separates an engine adapter, shared analysis and an emitter. These are the boundaries I'm working towards:

Schema and query SQL pass through an engine adapter, shared analysis and a Go emitter to produce typed parameters, result records and database calls. The engine adapter supplies dialect rules. The Go emitter selects a method strategy for database/sql or pgx. — **Figure 1 - Follow the information into Go.** Arrows carry the schema and query information through the design.

First I need a catalogue, which is the generator's description of the schema. Reading the migration SQL gives it tables with columns and constraints to refer to when it reads the queries. The database already has this information when it executes SQL, but my generator needs enough of it to work out the calls beforehand.

The engine adapter handles the dialect's syntax and supplies its types and built-in functions. A function can have several signatures describing accepted arguments, the return type and treatment of NULL, so the analyser needs argument types to choose between them. The engine also supplies conversion and promotion rules. Can an integer be passed where a function expects a decimal? Which type should be used when two numeric types need combining? The shared code should ask the engine instead of carrying a growing collection of dialect exceptions.

Suppose I put the book title in lower case before choosing the fallback:

COALESCE(LOWER(b.title), 'No book yet')

The analyser needs to work from the inside out. Here b refers to books. The analyser follows that alias to find title in the schema, then accounts for the left join. That gives it text which can be NULL. The dialect's signature for LOWER says it returns text and passes NULL through, so the inner call still has a nullable text result.

That answer becomes an input to COALESCE. Its other argument is a non-null text literal, which gives the outer expression a non-null text result. The generated display field can still be a Go string.

That's the recursion through the expression tree. Each call gets the results of analysing its arguments, even when an argument is another call. Adding one function shouldn't send me back to sorting out the Go types by hand.

In my smaller services, an adapter keeps a cloud provider's details out of the core. The SQL dialect needs that boundary here.

Generating Go has differences of its own

When analysis has parameters and result columns, an emitter can turn them into Go. It chooses the Go representations and writes the records, methods and scanning code.

There are differences even within that output. A call through database/sql uses QueryContext, while a call through pgx uses Query.

For those calls a small part of a strategy interface can look like this:

type MethodStrategy interface {
    QueryMethod() string
    QueryRowMethod() string
    ExecMethod() string
}

I supply a strategy for the Go database API, and the shared method builder asks it how to write each operation. The names are easy.

Suppose the generated method updates a book's title and reports how many rows changed. With database/sql, ExecContext returns a sql.Result whose RowsAffected method returns (int64, error), while pgx's Exec returns a command tag whose RowsAffected method gives me the count directly. Did the update succeed? Both execution calls can fail, so I still need to check that error before asking for the count.

Both versions can return (int64, error). After checking the execution error, the database/sql path can return result.RowsAffected(). The pgx path needs return tag.RowsAffected(), nil. So the strategy supplies the statements which run the update and report its result. The shared builder keeps the method's name, parameters and return type. I can put up with slightly different calls for pgx, but keeping two almost identical generators up to date would get on my nerves quite quickly.

Choosing a SQL dialect and choosing a Go database API are separate decisions. The engine adapter handles the SQL, while the method strategy handles the Go calls, so I can change either without copying the analyser.

Now let somebody change the filters

Our author list can have either ending after the same SELECT and left join, one ordering the names without a filter and the other adding a bound author value before that ordering. Both return the same four columns with the same types. I shouldn't have to throw away that information because the caller has added a filter:

ORDER BY a.name ASC;

WHERE a.name = ?
ORDER BY a.name ASC;

Which parts may the builder change?

A list might have an optional author filter and a choice of sort order. Writing a separate query for every combination of controls would get tedious fast.

There's already a request for dynamic ordering in sqlc. It's a good example of the gap between passing a value and changing the query itself.

My idea is to generate a query-specific builder around that known SQL. The base query still selects columns and defines joins. The builder adds permitted conditions and ordering, keeping the generated result record.

Values stay in bind parameters. If an author's name contains a quote, it is still a name, not another bit of SQL. Column names and sort directions need different treatment. Binding "a.name" as a value won't make it choose that column for ORDER BY.

So the builder needs a known set of columns, operators and directions it can insert. The caller picks from that menu. Arbitrary SQL fragments aren't on it. This gives the generator something useful to check before any query reaches the database.

A builder taking Where(column, operator, value) with an any value gives up compile-time checking of that argument, even if the generated result record is typed.

One more filter, another operator, a convenient little expression.... I can also see myself getting carried away and building another language for querying the database. Which would be a fairly ironic outcome, given why I started. SQL should remain where I decide what the query does, which is what attracted me to sqlc.