Seagull

Let me write the query

If you're like me, you know the SQL you want. Seagull takes your schema migrations and queries and generates the Go around them, with typed parameters, result records and calls to the database.

I built it to keep control of the query. Hibernate and other ORMs have often meant the same routine for me, persuading the tool to produce a query, finding an escape hatch, then writing the SQL myself anyway. I hate spending time learning how to get out of a tool.

Which is why I love sqlc. SQL in, Go out. That's an absolutely great arrangement and it's the part I wanted to keep.

The trouble came with more complicated queries. SQLite in particular. Joins and nested expressions could lose type information, leaving me to explain things to the generator that were already in the query. I started reading its code with contributing in mind. But the architecture I wanted put each database's type rules and functions behind interfaces and that felt like much more than a fix to send upstream.

I liked sqlc so much that I ended up building a SQL analyser of my own.

A location that isn't there

One of Seagull's examples keeps track of gulls and their sightings. A gull has a ring identifier and an optional nickname. A sighting's place is declared NOT NULL in the database. The query uses a left join to include gulls that haven't been spotted:

-- sgl.query(ListSightings, many)
SELECT gulls.ring, gulls.nickname, sightings.place
FROM gulls
LEFT JOIN sightings ON sightings.gull_id = gulls.id
ORDER BY gulls.id, sightings.seen_on;

No sighting, no place. Copying the column's NOT NULL declaration into the generated type would miss that. Here is the record Seagull generates:

type ListSightingsRow struct {
    Ring     string  `json:"ring"`
    Nickname *string `json:"nickname"`
    Place    *string `json:"place"`
}

The nickname can be missing in the table itself. The place can be missing because of the join. Both need a pointer here, while the ring stays a plain string. The generated ListSightings method returns a slice of these records. Making place nullable in the result doesn't change the schema. I still can't insert a sighting without a place, but I can get a gull without a sighting out of this query, so the record needs to say which one it represents. This is the work I want the generator to take off my hands. Changing a join shouldn't leave me remembering which fields to change in Go as well.

Getting that record right means understanding the query beyond recognising its syntax. SQLite shouldn't need to fit PostgreSQL's idea of a type before I can generate some Go. Each database's version of SQL is called a dialect. Each supported dialect supplies parsing, type rules and function definitions. The shared analyser asks those rules what an expression returns, including whether it can be NULL. A nested function call is worked from the inside out. If I've worked out what the inner call returns, I don't want to lose that answer as soon as another function uses it. The analyser carries both the type and whether it can be NULL into the next call.

Choosing PostgreSQL for the database and pgx for the Go driver are two different decisions to me. database/sql uses calls such as QueryContext, while pgx uses Query. A separate strategy supplies those differences to the code generator so changing the Go driver doesn't send me back into SQL analysis.

The supported dialects include PostgreSQL, SQLite, MySQL and ClickHouse. Their rules can vary without making one database's types the model for all the others.

I also wanted to embed the generator in Piko without bringing in a C toolchain. sqlc required cgo when I started this work. Seagull's generator is pure Go, though the driver an application chooses can have requirements of its own.

The filter changes, the result doesn't

A list on a page soon acquires filters, a choice of sort order, pagination.... Writing a query for every combination of controls doesn't appeal. Going back to assembling SQL strings in the handler would undo a good deal of what I came here for.

Seagull can generate a runtime builder around a query. The SQL still defines the selected columns and joins, while the caller adds filters, ordering and pagination. The result keeps its generated Go type.

Column names, operators and sort directions come from a permitted set. Values go through driver parameters so a quote in somebody's name stays part of their name.

Filter values are any. Their type isn't checked at compile time.

I'm trying to escape an ORM, yet there is quite a temptation to add enough features to end up maintaining one.

Keeping the original SQL in charge of what gets selected helps me draw the line. The runtime builder reference shows the calls and their checks.

From Piko's querier to Seagull

This work became the query generator inside Piko. Its typed records can flow straight into the Go used by a page and then into its template.

Getting a query shouldn't mean adopting Piko. Seagull now has its own home and Piko connects to it through a bridge. Other Go applications can use the command-line tool or embed the generator in their own tooling.

The generated queries call the database driver directly and include the helpers they need. They run without the generator linked into the application.

Seagull is open source under Apache 2.0 and in alpha with APIs still changing. The first-query guide gets a small project running and the examples go further into the queries it can handle.

I'd start with a query you already know well, something where you can picture the rows it returns, then look at the Go it gives you back.