How To Call Any Query Builder in Go’s Squirrel Library

If you came here looking to find a way to pass a generic Squirrel query builder to an sql driver you came to the right place.

Photo by Caleb Martin on Unsplash

Sort answer: You can’t

But there is a work-around. Scroll to the Bottom for the Solution

The Problem

First let’s start with the problem. Here’s an example of using squirrel to get a user.

func GetUser(id int64) (*User, error) {
	create() // creates the database pointed to by DB.
	user := new(User)
	query := sq.Select(
		"id", "username",
	).From(
		"User",
	).Where(
		sq.Eq{"id": id},
	)
	stmt, args, err := query.ToSql()
	if err != nil {
		return nil, err
	}
	row, err := DB.Query(stmt, args...)
	if err != nil {
		return nil, err
	}
	err = row.Scan(&user.id, &user.username)
	if err != nil {
		return nil, err
	}
	return user, nil
}

OK. So far so good. Let’s create a function that inserts a user.

func InsertUser(username string, password string) (*User, error) {
	create() // creates the database pointed to by DB.
	query := sq.Insert(
		"User",
	).Columns(
		"username",
		"password",
	).Values(
		username,
		password,
	)
	result, err := DB.Exec(query.ToSql())
	if err != nil {
		return nil, err
	}
	id, err := result.LastInsertId()
	if err != nil {
		return nil, err
	}
	return GetUser(id)
}

Already we see there are some common actions we take that we can extract.

• Creating the database.
• Converting the squirrel query to SQL.
• Fetching a record by ID.

Yes, these Read/Write operations accept and return different “shapes.” (i.e., if we delete, we don’t need to return a User). But just imagine — for the sake of argument — that we need to perform these same Read/Write actions with another model, say Group. We'd essentially be copying and pasting the User functions.

Wouldn’t it be cool if we separate out the “Read” and “Write” functions into generic functions?

Reader note: I’m not talking about go generics, but a generic function that takes a squirrel query as an argument.

For inserting a user this would look like…

func InsertUser(username string, password string) (*User, error) {
    // 1. Construct an insert squirrel query based on username or password
    // 2. Pass the insert query to a generic exec function. If any occurs with
    //    compiling the statement OR executing, the generic exec function would catch it.
    //    the return value is would be a sql.Result.
    // 3. Using the db.Result, fetch the row created by sql.Result.LastInsertId
}

We’ll want to return an sq.Result because if we update a user there won't be any ID to fetch and we'll need to simply get the ID we used to specify updating the user.

func XExecOne(sqStmt *sq.InsertBuilder) (*sql.Result, error) {
	create()
	raw, args, err := sqStmt.ToSql()
	if err != nil {
		return nil, err
	}
	result, error := DB.Exec(raw, args...)
	if error != nil {
		return nil, err
	}
	return &result, nil
}

Isn’t this cool? This does, like, 3–5 layers of error checking (if we count what happens in create ), making the receiving function MUCH cleaner!

But what if we wanted to pass an sq.UpdateBuilder into the function?

Here we come to the crux of the issue…

You see, sq.UpdateBuilder and sq.InsertBuilder have only 1 shared base interface: builder.Builder.

The problem with this? builder.Builder does not have a ToSql function. There is a discussion (as of writing this article) to derive the WRITE operations into its own base class, but for now, none of the builders share a class that has ToSql as a function.

And, unfortunately, based on go’s strict type rules you can’t cast…

func XExecOne(sqStmt *builder.Builder) (*sql.Result, error) {
    raw, args, err := (InsertBuilder *) sqStmt.ToSql() // You wish! -- INVALID SYNTAX!!!
}

…And can’t create a shim struct for “duck typing”…

type SqrlShim {
    builder.Builder
}
func (sqs *SquirrelShim) ToSql() {}
func XExecOne(sqStmt *SqrlShim) (*sql.Result, error) {
    raw, args, err := sqStmt.ToSql() // so far so good
}
func InsertUser(username string, password string) (*user.User, error) {
 stmt := sql.Insert(...)
 result, err := XExecOne(stmt) // This is where things go bad!
}

This won’t work because sq.Insert doesn't share the same inheritance with SqrlShim. So the program won't even compile.

However, there is a solution!

The Solution

If you take a look at squirrel’s ToSql functions, you'll notice they all have the same signature:

// InsertStatement.ToSql() => Takes nothing => returns (string, *any[], error)
// SelectStatement.ToSql() => Takes nothing => returns (string, *any[], error)
// UpdateStatement.ToSql() => Takes nothing => returns (string, *any[], error)
// DeleteStatement.ToSql() => Takes nothing => returns (string, *any[], error)

So then instead of passing in a shim that doesn’t work (and throwing out this whole “generic sql function” idea), we’ll instead pass in a ToSql function:

// This makes a type out of a function
type ToSqlFunc func() (string, []interface{}, error) 

func XExecute(toSqlFunc ToSqlFunc) (sql.Result, error) {
   raw, args, err := toSqlFunc() // Simply call it.
   // Do all of the tedious error checking...
   // ...
}
func InsertUser(username string, password string) (*user.User, error) {
 stmt := sql.Insert(...)
 // Don't add () after ToSql. We're passing in the FUNCTION INSTANCE
 result, err := XExecOne(stmt.ToSql)
}

Isn’t that cool?

This also means that if — for whatever reason — we wanted to add our own builder, (like the laws of the universe change to allow for a CRUD operation beyond CREATE, READ, UPDATE, and DELETE), then all we need to do is add our own ToSql method that follows the same signature as above.

type BobsExestentialBuilder struct {
    builder.Builder
}

func (beb *BobsExestentialBuilder) ToSql() (string, *any[], error) {
    // ...
}

👉‍‍ Did you like this article? Share it with 3 of your friends or colleagues?

📢 Comment below: What’s been your experience with squirrel and go? Have you encountered this quirk?

💓 Subscribe to DamnGoodTech on Ko-Fi. Help support more of these articles.

🤝🏼 Hire Me as a lead dev on your team.