We have now created a type-safe Swift API for constructing a small subset of the SQL language. We have built tools that allow us to get a static description of the tables in our SQLite database, and that has unlocked all kinds of cool things:

• We can write SELECT statements that allow us to select a subset of columns from the table, and we can be sure we only select actual stored columns, and never computed properties that would lead to invalid SQL.

• We can compute aggregates of table columns, such as counts, averages, concatenations of strings, and more.

• And now we can order the rows returned by sorting on any number of columns, in any direction, and we can even sort by expressions such as the length of a string column.

But so far we have not tackled what might be the most important of the SQL language, and might be seen as the most complex part when trying to create a type-safe API for SQL. And that is the WHERE clause.

This is what lets you filter out the rows that are returned from the database. For example, we may want to only fetch the incomplete reminders. Or maybe we just want the high priority reminders. Or maybe we want to search the names of the reminders for a particular substring. Or maybe we want to combine a bunch of these things into one gigantic predicate that is run on every single row of the reminders table.

It is possible to support WHERE clauses in our SQL builder and to even add some type-safety while we are at it so that we don’t do non-sensical things such as filter all reminders whose isCompleted column is equal to the string “dog”. We would never want to write such a query, and so we will make it so that the query builder API does not allow it.

Let’s start by getting familiar with what one can do in a WHERE clause in SQL.