I’m writing a Magic: The Gathering API with GraphQL right now and ran across the need to do some full text searching against card names in Magic.

SQLite3 has been a surprisingly robust embedded database for this use case, so I wanted to see if I could accomplish this with SQLite without having to boot up and add the complexity of a Postgres database. I had specifically chosen SQLite3 to be lightweight and quick to work on, so switching was something I didn’t really want to do.

That being said, I quickly found a few StackOverflow answers about the LIKE SQL query keyword in SQLite3.

So here’s the code snippet. I’ll comment where possible.

// Search will search for card names in the database.
func (s *graphQLServer) Search(ctx context.Context, name string) ([]*Card, error) {
	if name == "" {
		return nil, nil
	}
	// In Go FMT, `%%` prints a single `%` in the string.
	name = fmt.Sprintf("%%%s%%", name)
	fmt.Printf("searching for name: %s", name)
	rows, err := s.cardDB.Query("SELECT id, name, colors FROM cards WHERE name LIKE ?", name)
	if err != nil {
		log.Printf("error querying database: %s", err)
		return nil, errs.New("failed to search cardDB", err)
	}

	cards := []*Card{}
	for rows.Next() {
		var (
			id     *int
			name   *string
			colors *string
		)

		if err := rows.Scan(&id, &name, &colors); err != nil {
			log.Printf("failed to scan card into struct: %s", err)
			continue
		}

		parsedID := strconv.Itoa(*id)
		card := &Card{
			ID:     parsedID,
			Name:   *name,
			Colors: colors,
		}

		fmt.Printf("searched card: %+v\n", card)

		cards = append(cards, card)
	}

	return cards, nil
}

Here’s the SQL query pulled out

"SELECT id, name, colors FROM cards WHERE name LIKE ?"

For the args, we want to pass a string with wildcards wrapping it. The percent wildcard symbol will match any or zero characters in the column you’re searching. In Go, we use a %% to print a % literal in the string. So we’ll format the search term before passing it as an arg to the query.

name = fmt.Sprintf("%%%s%%", name)

And there you have it! Rough full-text search with no plugins in Golang.