Golang Database Library Orm Example - Where IN

In this post, we will look at selecting records based on a column with multiple conditions. To keep it simple, we will select users whose last names are ‘Donovan’ and ‘Campbell’.

In this post, we will look at how deleting a single record in a table works for these various libraries and ORMs.

In general, this operation is simple because we only care about deleting and checking for an error.

Table of Contents

sqlx

We cannot pass in a slice into sqlx because the sql driver does not understand Go slice. Instead, given a series of IDs, we need to create a series of ? depending on the length of that slice. In sqlx, a helper function called sqlx.In() has to be called before doing the main query.

err = r.db.SelectContext(ctx, &dbScan, "SELECT * FROM users WHERE last_name IN (?)", f.LastNames...)

The above code won’t work. First, SelectContext() only accepts a slice of interface, not a slice of strings. Next, there is only one question mark placeholder. Given that we are using Postgres, we need to use dollar placeholders instead.

So the above query won’t generate a valid sql query. The reason is I think sqlx touches your query as little as possible. So it needs to be a valid sql query first.

query, args, err := sqlx.In("SELECT * FROM users WHERE last_name IN (?)", f.LastNames)

The In() method expands the ? bindvar to the length of f.LastNames slice. By default, it turns into a series of ?. For postgres, we must yet do another transformation so that it uses dollar signs.

query = sqlx.Rebind(sqlx.DOLLAR, query)

This finally gives us the correct query:

SELECT * FROM users WHERE last_name IN ($1, $2)

Now, we can execute the query

err = r.db.SelectContext(ctx, &dbScan, query, args...)

So three code blocks to achieve this.

sqlc

Doing dynamic queries is tricky with sqlc. First of all, there is no support for a WHERE IN() support for mysql from sqlc. There is an open issue for this.

I suppose there is a dodgy way by extrapolating the number of ? placeholders

mysqlQuery := fmt.Sprintf(`SELECT * FROM users WHERE last_name IN (?%v);`, strings.Repeat(",?", len(f.LastNames)-1))

Only issue here is that f.LastNames slice variable need to be sanitized because it originates from user input and thus potentially can cause a sql injection.

Anyhow, since we are demonstrating for Postgres, we can make use postgres array! When writing the SQL query, we declare that we accept an array of text for last_name using last_name = ANY($1::text[].

SELECT id, first_name, middle_name, last_name, email, password, favourite_colour 
FROM users 
WHERE last_name = ANY($1::text[])

The code that gets generated by sqlc now accepts an array using pq.Array

func (q *Queries) SelectWhereInLastNames(ctx context.Context, lastName []string) ([]User, error) {
	rows, err := q.db.QueryContext(ctx, selectWhereInLastNames, pq.Array(lastName))
...	

So we end up with just calling the generated SelectWhereInLastNames() method.

users, err := r.db.SelectWhereInLastNames(ctx, f.LastNames)
if err != nil {
    return nil, errors.New("error getting users")
}

for _, val := range users {
    result = append(result, db.UserResponse{
        ID:              uint(val.ID),
        FirstName:       val.FirstName,
        MiddleName:      val.MiddleName.String,
        LastName:        val.LastName,
        Email:           val.Email,
        FavouriteColour: string(val.FavouriteColour),
    })
}

squirrel

Building a query looks like the following:

rows, err := r.db.
	Select("*").
	From("users").
	Where(sq.Eq{"last_name": f.LastNames}).
	QueryContext(ctx)

We use sq.Eq{} struct by giving it the column name we want, and then the slice of arguments. Using sq.Eq{} may be unintuitive because we would expect to use WHERE IN wording somewhere. Moreover, sq.Eq{} struct expects a map of string with any type as its value. Since we supply a list of strings, squirrel will generate a WHERE IN sql statement. We will later see in the Get section where sq.Eq{} can accept a single integer value to get one record.

By default, squirrel will create question mark placeholders. Since we are using postgres, we need dollar placeholders instead. We can add a method to each query builder or, we can avoid repetition by setting it during repository initialisation.

func NewRepo(db *sqlx.DB) *repository {
	return &repository{
		db: sq.StatementBuilder.
		PlaceholderFormat(sq.Dollar).
		RunWith(db.DB),
	}
}

Squirrel does not have StructScan() or SelectContext() convenience method like sqlx, so we need to do manual scanning.

var dbScan []*userDB
for rows.Next() {
	var u userDB
	err := rows.Scan(&u.ID, &u.FirstName, &u.MiddleName, &u.LastName, &u.Email, &u.Password, &u.FavouriteColour)
	if err != nil {
		return nil, err
	}
	dbScan = append(dbScan, &u)

gorm

Gorm will happily take a slice for your WHERE IN(?). It even correctly use the correct dollar placeholders because we have imported "gorm.io/driver/postgres" somewhere in our code.

err = r.db.WithContext(ctx).
		Where("last_name IN ?", f.LastNames).
		Find(&users).
		Error

Unlike sqlx, you do not need to put a parentheses around the question mark placeholder.

sqlboiler

Sqlboiler has a WhereIn() query modifier which takes column_name IN ?. In spite of using this method, you still need to type in IN ? as a string in the argument. I imagine the API can be simpler by accepting something like WhereIn(last_name, args...).

The variadic arguments requires an interface type instead. Since sqlboiler is not generic yet, we need to transform our string slice into a slice of interface{}.

import "github.com/samber/lo"

args := lo.Map(f.LastNames, func(t string, _ int) any {
	return t
})

sqlboiler will take the dollar placeholder without a parentheses.

all, err := models.Users(
	//qm.WhereIn("last_name", args...),                           // Does not work. Needs IN operator
	//qm.WhereIn("last_name IN ($1, $2)", "Donovan", "Campbell"), // Is what we want
	qm.WhereIn("last_name IN ?", args...),                        // instead, just give it a `?`.
).
	All(ctx, r.db)

ent

Doing a Where IN(?,...) in ent is as easy as Gorm, but with the added advantage of having no magic strings whatsoever.

return r.db.User.Query().
	Where(user.LastNameIn(f.LastNames...)).
	All(ctx)

LastNameIn() function is generated by the ent cli based on the schema we have defined. If we look at its function signature, LastNameIn(vs ...string) predicate.User, we can clearly see that it requires none or a slice of strings.

Like all others, by default ent will add edges{} to the response even when you did not load any relationship.

{
    "id": 15,
    "first_name": "Bruce",
    "last_name": "Campbell",
    "email": "bruce@example.com",
    "favourite_colour": "blue",
    "edges": {}
}

Ent is again the easiest to use amongst all with Gorm coming in close second. There’s too much work involved in sqlx. Squirrel is quite ok. Usage of a sq.Eq{} struct is interesting but scanning can be a major headache. Sqlboiler syntax can be a little more friendly.

Next > Golang Database Library ORM Example Get