I was recently looking for a good query builder for a go application I am prototyping, and settled on Jet. Jet has a nice wiki, but limited introductory material as of this writing, so this post is an attempt to collect a set of small recipies to help you get started with it quicker.

Why Jet ?

Usually larger applications tend to adopt fully featured ORMs early on, where as smaller micro-services often eschew the complexity that comes with a full ORM and directly use the low level database adapters.

Query builders like jet sit somewhere between the two in the abstraction ladder.

Unlike a full ORM it doesn't have features like dirty tracking, model lifecycle hooks etc.

Also unlike directly using a database driver, it provides a type-safe API to construct SQL queries which I find much more ergonomic and less error-prone than raw parameterized queries or (ugh) constructing complex sql with string interpolations.

Jet fits a sweet spot for me, because:

1. It is not overly opinionated around how database should be accessed.
2. Whatever you can do with SQL, you can also do with Jet
3. It is performant, very little overhead over direct SQL
4. It is type-safe - no ugly string interpolations, no SQL syntax errors in corner cases.
5. The API closely matches the SQL syntax - so less learning curve is required

How to use ?

Jet is easy to get started with. It is designed to work with the database/sql package in go stdlib, and only offers a query builder. The database/sql package is used for actually executing the queries on the database.

In this post we will use a sqlite, but jet offers support for most mainstream databases.

Configuring the codegenerator

Jet offers what is called a database-first approach. In this approach, you update your database first (a migration system is recommended) and then generate code from the database for all your tables.

This ensures that the table mapping code is always up-to-date and you don't need to specify every column being added twice - once when updating the database, and once in application code.

Let us say we have following sql tables defined in test.sqlite3:

CREATE TABLE
  notebook (id varchar(36) not null primary key, title varchar(500));

CREATE TABLE
  note (
    id varchar(36) not null primary key,
    title varchar(500),
    notebook_id varchar(36) references notebook (id) on delete cascade on update restrict
  );

To generate the code, we'll first need to install the jet cli. We will also need to ensure that the specific database drivers we need are enabled through build tags:

go install -tags 'sqlite3' github.com/go-jet/jet/v2/cmd/jet@latest

Note that you will need to change the tags if you are using another database. Also if you are using sqlite, and need to use some of the sqlite extensions, you'll need additional build tags for those too, for example:

go install -tags 'json1 fts5 sqlite3' github.com/go-jet/jet/v2/cmd/jet@latest

Inside a go project, we can generate the jet mapper code for this database using a command like:

jet -source=sqlite -dsn="/tmp/test.sqlite3" -path=./gen/jet

if you use a migration system like dbmate, go-migrate or atlas, you'd typically want to exclude the tables used only for migration bookkeeping.

jet -source=sqlite -dsn="/tmp/test.sqlite3" -path=./gen/jet -ignore-tables=schema_migrations

Ok, so now let's look at the generated files: For every table we have a table mapper file and model file. For example for notebook we have the following table mapper file:

//
// Code generated by go-jet DO NOT EDIT.
//
// WARNING: Changes to this file may cause incorrect behavior
// and will be lost if the code is regenerated
//

package table

import (
	"github.com/go-jet/jet/v2/sqlite"
)

var Notebook = newNotebookTable("", "notebook", "")

type notebookTable struct {
	sqlite.Table

	//Columns
	ID    sqlite.ColumnString
	Title sqlite.ColumnString

	AllColumns     sqlite.ColumnList
	MutableColumns sqlite.ColumnList
}

type NotebookTable struct {
	notebookTable

	EXCLUDED notebookTable
}

// AS creates new NotebookTable with assigned alias
func (a NotebookTable) AS(alias string) *NotebookTable {
	return newNotebookTable(a.SchemaName(), a.TableName(), alias)
}

// Schema creates new NotebookTable with assigned schema name
func (a NotebookTable) FromSchema(schemaName string) *NotebookTable {
	return newNotebookTable(schemaName, a.TableName(), a.Alias())
}

// WithPrefix creates new NotebookTable with assigned table prefix
func (a NotebookTable) WithPrefix(prefix string) *NotebookTable {
	return newNotebookTable(a.SchemaName(), prefix+a.TableName(), a.TableName())
}

// WithSuffix creates new NotebookTable with assigned table suffix
func (a NotebookTable) WithSuffix(suffix string) *NotebookTable {
	return newNotebookTable(a.SchemaName(), a.TableName()+suffix, a.TableName())
}

func newNotebookTable(schemaName, tableName, alias string) *NotebookTable {
	return &NotebookTable{
		notebookTable: newNotebookTableImpl(schemaName, tableName, alias),
		EXCLUDED:      newNotebookTableImpl("", "excluded", ""),
	}
}

func newNotebookTableImpl(schemaName, tableName, alias string) notebookTable {
	var (
		IDColumn       = sqlite.StringColumn("id")
		TitleColumn    = sqlite.StringColumn("title")
		allColumns     = sqlite.ColumnList{IDColumn, TitleColumn}
		mutableColumns = sqlite.ColumnList{TitleColumn}
	)

	return notebookTable{
		Table: sqlite.NewTable(schemaName, tableName, alias, allColumns...),

		//Columns
		ID:    IDColumn,
		Title: TitleColumn,

		AllColumns:     allColumns,
		MutableColumns: mutableColumns,
	}
}

This file initializes the specific table objects for constructing SQL queries.

We also have a model file which is a simple go struct representing a row in a table:

//
// Code generated by go-jet DO NOT EDIT.
//
// WARNING: Changes to this file may cause incorrect behavior
// and will be lost if the code is regenerated
//

package model

type Notebook struct {
	ID    string `sql:"primary_key"`
	Title *string
}

We will have this pair generated for each table in our db.

Executing queries

Now that we have our code generated, we can use the generated API to query our database.

Jet's api closely matches the SQL syntax. So to fetch a note with known title we can do something like:

import (
	m "example.com/rts/gen/jet/model"
	t "example.com/rts/gen/jet/table"
	jet "github.com/go-jet/jet/v2/sqlite"
)

query := t.Note.
    SELECT(t.Notebook.AllColumns).
    WHERE(t.Notebook.Title.EQ("Test"))

Note that this just creates the query. To actually execute it, we need a sql.DB instance. This would be created through the database/sql package in go stdlib and is not jet specific.

For SQLite all we need is the path to the database. For other database we can pass connection strings.

db, err := sql.Open("sqlite3", dbFilePath)

Now we can run the query through the db instance and map the rows to our generated model structs:

var notebooks []m.Notebook

err := t.Notebook.
    SELECT(t.Notebook.AllColumns).
    WHERE(t.Notebook.Title.EQ("Test")).
    Query(db, &notebooks)

If the query succeeds our notebooks slice will be populated with the fetched rows.

Note that we didn't have to deal with untyped data at any point.

We are also not restricted to using the generated model types. The generated model types are provided as a convenience for common crud operations, but we will often need to define custom types when fetching subset of columns or when fetching data from multiple tables.

Let's say we want to fetch not only the notebook, but all associated notes. To represent a note along with member notes, we can define following struct which composes the generated models:

type NotebookWithNotes struct {
    m.Notebook,
    Notes []m.Note
}

and use that in our query:

var notebooks []m.NotebookWithNotes

err := t.Notebook.
    INNER_JOIN(t.Note, t.Note.NotebookID.EQ(t.Notebook.ID))
    SELECT(t.Notebook.AllColumns).
    WHERE(t.Notebook.Title.EQ("Test")).
    Query(db, &notebooks)

The join syntax is pretty similar to what we would have expected from SQL. Also we didn't have to do anything special here to support mapping the flattened rows to hierarchical model. jet is able to automatically identify the Notebook fields from embedded struct and the Note fields in the member slice and populate them all from the rows in database.

Integration with context propagation

Almost all web frameworks integrate with Go's context propagation system, and it is useful to take advantage of it so that when the context gets cancelled, unnecessary operations can be avoided in the backend. This DO post goes into more details about the context mechanism in go.

jet makes it easier to integrate this mechanism. If we have a context instance at our disposal (likely coming as an argument in our request handler from a web framework), we can pass that to the QueryContext function instead of using the Query function as above:

var notebooks []m.NotebookWithNotes

err := t.Notebook.
    INNER_JOIN(t.Note, t.Note.NotebookID.EQ(t.Notebook.ID))
    SELECT(t.Notebook.AllColumns).
    WHERE(t.Notebook.Title.EQ("Test")).
    Query(db, &notebooks)

Query Logging

It is clear now that jet enables us to construct complex queries dynamically in our application. It is often useful to log these queries in development so that we can see what exactly is getting executed. This is also useful for DBA audits.

To make this possible, jet provides a SetQueryLogger API, which can be used to log operations through any logging library:

jet.SetQueryLogger(func(ctx context.Context, info jet.QueryInfo) {
	query, args := info.Statement.Sql()
	zerolog.Debug().
		Int64("DurationMS", info.Duration.Milliseconds()).
		Msgf("SQL Query: SQL: %s\nArgs: %+v", query, args)
})

Above example uses zerolog, but we are free to use any logging library available for go.

Insert/Update

Insert/update APIs are also pretty similar to what we'd expect from SQL. We can pass raw values or make use of model structs generated for us (recommended). Following example illustrates the latter approach for insertion:

_, err := t.Notebook.
	INSERT(t.Notebook.AllColumns).
	MODEL(&m.Notebook{
		Title: &title,
	}).
	Exec(db)

The wiki goes into more detail about available APIs, but it is particularly notable that jet has good support for on conflict update APIs which are often useful for preventing get-check-insert race-conditions:

_, err := t.Notebook.
	INSERT(t.Notebook.AllColumns).
	MODEL(&m.Notebook{
		ID:    someId,
		Title: &title,
	}).
	ON_CONFLICT(t.Notebook.ID).
	DO_UPDATE(jet.SET(t.Notebook.Title.SET(t.Notebook.EXCLUDED.Title))).
	Exec(db)

In contrast, if did a select to check whether a notebook exists, and issued an insert if it didn't, the insert could still fail because a notebook could have been inserted in the meanwhile.

Subqueries

As we may expect by now, jet has good support for subqueries too.

_, err = t.Note.DELETE().
	WHERE(
		t.Note.ID.IN(
			t.Note.LEFT_JOIN(t.NoteFile, t.NoteFile.NoteID.EQ(t.Note.ID)).
				SELECT(t.Note.ID).
				WHERE(t.Note.NotebookID.EQ(jet.String(notebookId)).
					AND(t.NoteFile.ID.IS_NULL())),
		),
	).
	ExecContext(ctx, store.db)

Hopefully this provides a high-level overview that can jumpstart your go project that needs database access.

This post has, however, just scratched the surface of what is possible with jet, and the aforementioned jet wiki goes into a lot more detail.