Video #319: SQL Builders: Filtering

Episode: Video #319 Date: Mar 31, 2025 Access: Members Only 🔒 URL: https://www.pointfree.co/episodes/ep319-sql-builders-filtering

Description

We tackle one of SQL’s most important aspects in our query builder: the WHERE clause, which filters the results of a query. And we will do so in a type-safe manner that prevents us from writing nonsensical queries in Swift even when they are syntactically valid in SQL.

Video

Cloudflare Stream video ID: 7883e35218fb048c69ef98cd27f7b616 Local file: video_319_sql-builders-filtering.mp4 *(download with --video 319)*

References

Transcript

— 0:05

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:

— 0:20

We can write

SELECT 0:31

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

SELECT 0:40

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. Stephen

SELECT 0:52

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 1:05

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.

WHERE 1:26

It is possible to support

WHERE 1:45

Let’s start by getting familiar with what one can do in a

WHERE 1:52

Let’s go back to the fantastic SQLite documentation and again look at the

SELECT 1:57

Immediately after one specifies a

WHERE 2:10

And this is a beast. This just represents that there is a choice of a whole bunch of things you can do in a

WHERE 2:40

We can also apply a unary operator before a column name, such as selecting incomplete reminders: sqlite> SELECT * FROM reminders WHERE NOT isCompleted; ┌────┬─────────────────┬─────────────┬──────────┐ │ id │ name │ isCompleted │ priority │ ├────┼─────────────────┼─────────────┼──────────┤ │ 1 │ Groceries │ 0 │ │ │ 2 │ Get haircut │ 0 │ │ │ 5 │ Call accountant │ 0 │ 3 │ └────┴─────────────────┴─────────────┴──────────┘

WHERE 2:54

We can also chain together two expressions with a binary operator, such as selecting the high priority reminders using the equality operator: sqlite> SELECT * FROM reminders WHERE priority = 3; ┌────┬─────────────────┬─────────────┬──────────┐ │ id │ name │ isCompleted │ priority │ ├────┼─────────────────┼─────────────┼──────────┤ │ 5 │ Call accountant │ 0 │ 3 │ └────┴─────────────────┴─────────────┴──────────┘

WHERE 3:06

And to select all reminders that are not high priority, we can use the unequal operator, <> : sqlite> SELECT * FROM reminders WHERE priority <> 3; ┌────┬─────────────────────┬─────────────┬──────────┐ │ id │ name │ isCompleted │ priority │ ├────┼─────────────────────┼─────────────┼──────────┤ │ 3 │ Take a walk │ 1 │ 1 │ │ 4 │ Buy concert tickets │ 1 │ 2 │ └────┴─────────────────────┴─────────────┴──────────┘

WHERE 3:14

And there are other binary operators, such as

AND 3:30

And if

OR 3:38

You can also use SQL functions in the

WHERE 3:55

There are also text-based operators we can perform for searching text columns. For example, we can use the

LIKE 4:24

This can be a useful kind of query for live searching the table as the user enters text into a search field: sqlite> SELECT * FROM reminders WHERE title LIKE '%ta%'; ┌────┬─────────────────┬─────────────┬──────────┐ │ id │ title │ isCompleted │ priority │ ├────┼─────────────────┼─────────────┼──────────┤ │ 3 │ Take a walk │ 1 │ 1 │ │ 5 │ Call accountant │ 0 │ 3 │ └────┴─────────────────┴─────────────┴──────────┘

LIKE 4:37

And as we’ve already seen a few times in this series,

NULL 4:50

This means if you want to find reminders whose priority is

NULL 4:58

…you will get an empty result set.

NULL 5:00

As well as this: sqlite> SELECT * FROM reminders WHERE priority <> NULL;

NULL 5:04

In order to select rows that have a particular column as

IS 5:15

That now properly returns all rows whose priority is

NULL 5:21

…selects all rows whose priority is not

NULL 5:24

And so already we can see how complex these

WHERE 5:21

And so we can see that

WHERE 5:46

But before showing any more power of

WHERE 6:07

Let’s dive in.

WHERE 6:09

Let’s start by getting a test into place that demonstrates the ideal syntax we would like to achieve, and then we will make that syntax a reality. We’ll start simple. Suppose we want to query for all completed reminders. We we hope we can leverage the trailing closure syntax we have come to love due to the power it affords us: @Test func whereClause() { assertInlineSnapshot( of: Reminder.all().where { $0.isCompleted }, as: .sql ) }

WHERE 7:27

This where method is going to exist on the Select type, just as the order method does, and so we can start by getting a stub of a method in place: func where() { }

WHERE 7:54

We do need to surround where in backticks since where is a reserved keyword in Swift. But we do not need to use backticks when invoking this method. Only when defining it.

WHERE 8:07

The signature of this function is going to be quite similar to that of order . It will take a trailing closure that takes the table’s Columns as an argument: func where( _ predicate: (From.Columns) -> <#???#> ) -> Select { }

WHERE 8:30

This predicate needs to return something, but what? The order method returned a variadic tuple of QueryExpression s because we wanted to express the idea of being able to sort by any number of columns or expressions. But that doesn’t really seem relevant here. We don’t envision needing to return a tuple of expressions in the where trailing closure: Reminder.all().where { ($0.isCompleted, $0.title) }

WHERE 9:25

What would that even mean?

WHERE 9:42

So this time we get to simplify things a bit by just having the trailing closure return some QueryExpression : func where( _ predicate: (From.Columns) -> some QueryExpression ) -> Select { }

WHERE 9:48

And then to implement this we need to construct a Select value, and we can pass along the columns and orders to do so: func where( _ predicate: (From.Columns) -> some QueryExpression ) -> Select { Select(columns: columns, orders: orders) }

WHERE 9:58

Now this gets things compiling, and even the test suite is compiling, but of course this can’t possible be correct. We aren’t actually doing any work to generate the

WHERE 10:27

That currently happens in the queryString property on Select : var queryString: String { … }

WHERE 10:46

But this means that the Select type needs to hold onto some data to represent the

ORDER 11:09

And then when constructing the Select value we will need to pass along the wheres : func order<each Q: QueryExpression>( _ orders: (From.Columns) -> (repeat each Q) ) -> Select { … return Select( columns: columns, orders: orderStrings, wheres: wheres ) } … func where( _ predicate: (From.Columns) -> some QueryExpression ) -> Select { Select( columns: columns, orders: orders, wheres: wheres + [predicate(From.columns).queryString] ) }

ORDER 11:50

And now we are in a position to add

AND 12:33

And amazingly, that is all it takes. Our test suite is now compiling, and if we run tests we will see it generates a valid SQL query: @Test func whereClause() { assertInlineSnapshot( of: Reminder.all().where { $0.isCompleted }, as: .sql ) { """ SELECT * FROM reminders WHERE isCompleted """ } } Predicate logic

AND 12:56

Well, it didn’t take much to unlock the very basics of

WHERE 13:09

Of course, filtering by just a single column isn’t very useful. Typically we want to perform all kinds of predicate logic in the

OR 13:26

Let’s see what it takes to build up complex predicates in the where closure.

OR 13:30

Let’s start simple by constructing a query that selects all incomplete reminders. Ideally we could just use the ! operator to negate the isCompleted column: @Test func whereClauseNegate() { assertInlineSnapshot( of: Reminder.all().where { !$0.isCompleted }, as: .sql ) }

OR 13:47

You may not know this about Swift, but it is possible to overload operators, such as this negation operator. To do so you can define a prefix func ! : prefix func ! () { }

OR 14:04

The argument of this function is the thing that is prefixed by ! , which in our case is some QueryExpression : prefix func ! (expression: some QueryExpression) { }

OR 14:11

And this operator will return another QueryExpression with the negation built into it: prefix func ! ( expression: some QueryExpression ) -> some QueryExpression { }

OR 14:19

In order to implement this we need to follow the pattern we have done a few times now. We define a whole new concrete type that implements the QueryExpression protocol: struct Negate: QueryExpression { }

OR 14:30

Further, this type will hold onto the base query expression that it is negating, and to preserve types we will even make the type generic over that base: struct Negate<Base: QueryExpression>: QueryExpression { let base: Base }

OR 14:41

And finally we can implement the queryString by prefixing a

NOT 14:58

We have also gone ahead and wrapped the base in parentheses so that we negate the entire expression.

NOT 15:00

And we can construct and return this type from the function: prefix func ! ( expression: some QueryExpression ) -> some QueryExpression { Negate(base: expression) }

NOT 15:10

Believe it or not, that is all it takes. Our test is now compiling, and when run it records the correct SQL string: @Test func whereClauseNegate() { assertInlineSnapshot( of: Reminder.all().where { !$0.isCompleted }, as: .sql ) { """ SELECT * FROM reminders WHERE NOT (isCompleted) """ } }

NOT 15:33

That was surprisingly easy! Let’s write another test for a more complex query. Let’s select all reminders that have a high priority. That is, a priority of 3: @Test func whereHighPriority() { assertInlineSnapshot( of: Reminder.all().where { $0.priority == 3 }, as: .sql ) }

NOT 15:53

It would be really nice if could use the plain equality operator from Swift, == , and have that translate into the equivalent SQL. We can accomplish this syntax in almost the exact way we did for the negation operator.

NOT 16:05

We can overload the == operator by simply defining a global function named == : func == () { }

NOT 16:15

The exact situation we have in our test is that the left-hand side is a query expression, and the right-hand side is an integer: func == (lhs: some QueryExpression, rhs: Int) { }

NOT 16:29

But this is needlessly concrete. It prevents something as simple flipping the order of the arguments: Reminder.all().where { 3 == $0.priority }

NOT 16:39

Then we would need an overload of == that works when the first argument is an integer. And not to mention that there are other types we want to compare, such as booleans, strings, and doubles.

NOT 16:47

So, we will genericize this equality operator by allowing it to take any two query expressions: func == ( lhs: some QueryExpression, rhs: some QueryExpression ) { }

NOT 16:54

And just like with the negation operator, it will return a brand new QueryExpression that will bake into the equality check: func == ( lhs: some QueryExpression, rhs: some QueryExpression ) -> some QueryExpression { }

NOT 17:02

Then, to implement this we need to cook up a concrete conformance of QueryExpression : struct Equals: QueryExpression { }

NOT 17:06

This type will hold onto both the left- and right-hand side queries, and will even be generic over those types: struct Equals< LHS: QueryExpression, RHS: QueryExpression >: QueryExpression { let lhs: LHS let rhs: RHS }

NOT 17:24

And then the queryString requirement can be defined as simply comparing the left and right sides with the = operator in SQL: var queryString: String { "(\(lhs.queryString) = \(rhs.queryString))" }

NOT 17:38

And we have gone ahead and parenthesized the whole expression too.

NOT 17:50

We are getting close, but our test does not yet compile because Int does not conform to QueryExpression : Operator function ‘==’ requires that ‘Int’ conform to ‘QueryExpression’

NOT 17:59

Well, this is easy enough to do: extension Int: QueryExpression { var queryString: String { "\(self)" } }

NOT 18:12

This allows us to use a plain integer anywhere a query expression is needed. And with that change our test is now compiling, and running the test records the correct SQL statement: @Test func whereHighPriority() { assertInlineSnapshot( of: Reminder.all().where { $0.priority == 3 }, as: .sql ) { """ SELECT * FROM reminders WHERE (priority = 3) """ } }

NOT 18:27

And this is looking pretty incredible. With just a few functions defined and a few new conformances to QueryExpression we can define even more complex predicates for our queries. We can even make use of some of the functions we previously defined to construct even more complex predicates.

NOT 18:41

For example, let’s select all reminders whose title has 3 characters: @Test func whereTitleLengthEqual3() { assertInlineSnapshot( of: Reminder.all().where { $0.title.length() == 3 }, as: .sql ) { """ SELECT * FROM reminders WHERE (length(title) = 3) """ } }

NOT 19:09

And incredibly that already works.

NOT 19:11

We can even do more complex things, such as use the equality operator to compare two different columns. It’s a little nonsensical, but suppose we wanted to select all reminders whose title’s character count is equal to the reminder’s priority. That can be done like so: @Test func whereTitleLengthEqualsPriority() { assertInlineSnapshot( of: Reminder.all().where { $0.name.length() == $0.priority }, as: .sql ) { """ SELECT * FROM reminders WHERE (length(name) = priority) """ } } And that’s pretty incredible.

NOT 19:44

But where things really start to take off is when once we build the operators that allow combining two query expressions with

OR 20:15

Well, this isn’t our first rodeo. We know exactly how it will go to implement this. We need to define an overload of the || operator, it will take two QueryExpression s and return a QueryExpression , and in particular it will return a concrete QueryExpression conformance that generates the query fragment for

OR 21:06

That is all it takes and our test is now compiling, and when run it generates the correct SQL: @Test func whereIncompleteOrHighPriority() { assertInlineSnapshot( of: Reminder.all().where { !$0.isCompleted || $0.priority == 3 }, as: .sql ) { """ SELECT * FROM reminders WHERE (NOT (isCompleted) OR (priority = 3)) """ } } Absolutely incredible.

OR 21:21

And of course we would want to support

AND 21:23

We can basically copy-and-paste what we did for

OR 22:02

But, with that our test suite is compiling, and running the tests generates the SQL we would expect: @Test func whereIncompleteAndHighPriority() { assertInlineSnapshot( of: Reminder.all().where { !$0.isCompleted && $0.priority == 3 }, as: .sql ) { """ SELECT * FROM reminders WHERE (NOT (isCompleted) AND (priority = 3)) """ } } Type-safety

OR 22:07

This is pretty incredible. With very little work we have now unlocked the ability to write complex predicate logic in Swift that is then translated into a SQL statement string. And each step of the way we have the compiler helping us by making sure we only reference columns that are actually defined in our schema. Brandon

OR 22:20

However, the compiler does currently have one big blind spot. It doesn’t know the type of data represented by each column, which means we are allowed to do some really non-sensical things. For example, we can construct a

WHERE 22:44

And even though SQLite has a very weak type system, and technically any value can be coerced into any kind of data type, Swift is a strongly typed language and we have first class Swift data types representing our database tables. So, the information is available to Swift to prevent us from making silly mistakes that would lead to syntactically valid SQL statements, but that could be hiding bugs from us.

WHERE 23:11

So, let’s see how we can add a light layer of type-safety to our current query builder.

WHERE 23:18

Let’s first write a few tests that show the problems we have now. It is possible to write some queries that are just plain nonsense, but our library will currently allow them. For example, what if we constructed a

WHERE 23:37

This is treating title as a boolean even though technically that column is a string. The SQL it generates: @Test func nonsense() { assertInlineSnapshot( of: Reminder.all().where { $0.title }, as: .sql ) { """ SELECT * FROM reminders WHERE name """ } }

WHERE 24:02

…is valid. It simply selects all reminders whose name is not

NULL 25:26

We can also check if the title of a reminder is equal to the literal integer of 3: assertInlineSnapshot( of: Reminder.all().where { $0.title == 3 }, as: .sql )

NULL 25:37

This makes no sense. If we want to check if a reminder’s title is literally the string “3”, then we should construct the query as such: 0.title == "3"

NULL 25:48

This makes it clear that we know that title is a string column and we want to compare it to a string.

NULL 25:52

But this query compiles just fine and generates the following query: assertInlineSnapshot( of: Reminder.all().where { $0.title == 3 }, as: .sql ) { """ SELECT * FROM reminders WHERE (title = 3) """ }

NULL 25:59

This is perfectly valid SQL, but it just may not be what you intend.

NULL 26:05

We are also allowed to compare two columns of the reminders table even when they are different types. For example, we could query for all reminders whose title is equal to its priority: assertInlineSnapshot( of: Reminder.all().where { $0.title == $0.priority }, as: .sql ) { """ SELECT * FROM reminders WHERE (title = priority) """ }

NULL 26:17

Again this makes no sense, but the SQL generated is technically valid. It just probably isn’t doing what we might think it is doing.

NULL 26:35

And so these are the types of things that we would like to make impossible to write. At the end of the day SQLite is the source of truth for our data, and so we cannot enforce 100% strictness in the type system. But we do want just a little bit of help from the Swift compiler so that we catch the most egregious problems.

NULL 26:53

To support some type safety in our expressions we need to know the type of data that is represented by our QueryExpression . Right now it is a protocol with a single requirement for generating the SQL string that can be used to query the database: protocol QueryExpression { var queryString: String { get } }

NULL 27:27

But now we need to enhance this protocol with a primary associated type: protocol QueryExpression<QueryValue> { associatedtype QueryValue var queryString: String { get } } …that represents the type of data one gets from the queryString .

NULL 27:47

And we can default it to Void so that the library keeps compiling: protocol QueryExpression<QueryValue> { associatedtype QueryValue = () var queryString: String { get } }

NULL 28:00

…as we incrementally update all of our types and helpers to take advantage of this new power.

NULL 28:36

The first place we will take advantage of this power is in the where function of our Select type. Currently the trailing closure allows one to return any kind of QueryExpression , but now we will limit it to only QueryExpression s of booleans: func where( _ predicate: (From.Columns) -> some QueryExpression<Bool> ) -> Select { … }

NULL 29:16

This guarantees that the work being done in the where trailing closure ultimately returns a boolean. And with that change the library compiles, but the tests do not.

NULL 29:29

That is because every where we have constructed is technically using a query expression of a Void value, since that is the default: Reminder.all().where { $0.isCompleted } Instance method ‘where’ requires the types ‘()’ and ‘Bool’ be equivalent

NULL 29:50

We now need to somehow encode the information of the type of this column into the Column type itself.

NULL 30:23

Currently the Column type is quite simple: public struct Column: Sendable, QueryExpression { let name: String var queryString: String { name } } Its underlying QueryValue is Void because that’s the default. We need some way to encode any type of QueryValue into this type. And the way to do this is to introduce a generic: public struct Column<QueryValue>: Sendable, QueryExpression { let name: String var queryString: String { name } }

NULL 30:34

This allows columns to not only describe their name, but also the type of data they represent.

NULL 30:43

This breaks only one thing in the library, which is the select method that can select any number of columns by specifying a variadic list of key paths: static func select( _ columns: KeyPath<Columns, Column>... ) -> Select<Self> { Select( columns: columns.map { Self.columns[keyPath: $0].name } ) }

NULL 30:57

Honestly, this is not really an API we want to support anymore. While it may seem at first that key paths are a wonderful way of selecting columns from a table, we have seen over and over that our closure-based syntax is far more powerful.

NULL 31:18

So we are going to get rid of this method: // static func select( // _ columns: KeyPath<Columns, Column>... // ) -> Select<Self> { // Select( // columns: columns.map { // Self.columns[keyPath: $0].name // } // ) // }

NULL 31:21

Now the library is compiling.

NULL 31:23

Tests still are not compiling, but a new problem that has popped up is that Swift cannot figure out the generic for the Column values we construct for our table: let id = Column(name: "id") Generic parameter ‘QueryValue’ could not be inferred

NULL 31:32

Here is our chance to give this information to the Column type so that our APIs will know what data is associated with each column: struct Columns { let id = Column<Int>(name: "id") let title = Column<String>(name: "title") let isCompleted = Column<Bool>(name: "isCompleted") let priority = Column<Int?>(name: "priority") } And of course this is code that we do not want to have to write manually or maintain for each little change we make to our data types. In the future this will all be generated by a macro, but we aren’t going to get sidetracked into building that macro right now.

NULL 32:15

Amazingly, one of the compiler errors we had a moment ago is now fixed: @Test func whereClause() { assertInlineSnapshot( of: Reminder.all().where { $0.isCompleted }, as: .sql ) { """ SELECT * FROM reminders WHERE isCompleted """ } } Previously the isCompleted column defaulted to having a Void query value, but now it is properly encoded to be a Bool , and that is exactly the kind of query expression it is appropriate to use in the where trailing closure.

NULL 32:38

The next error we have is when using the ! unary operator to negate the isCompleted column: Reminder.all().where { !$0.isCompleted } Although Swift knows that isCompleted is a boolean column, that information is lost once we hit it with the ! operator. We now need to preserve that information.

NULL 32:49

Currently the ! prefix function is written like so: prefix func ! ( expression: some QueryExpression ) -> some QueryExpression { … }

NULL 33:06

This needs to be enhanced so that the operator only works on boolean expressions, and returns a boolean expression: prefix func ! ( expression: some QueryExpression<Bool> ) -> some QueryExpression<Bool> { … }

NULL 33:10

And then we have to update Negation to only work with boolean expressions: struct Negation< Base: QueryExpression<Bool> >: QueryExpression { typealias QueryValue = Bool … }

NULL 33:25

With that done the negation test is now compiling: @Test func whereClauseNegate() { assertInlineSnapshot( of: Reminder.all().where { !$0.isCompleted }, as: .sql ) { """ SELECT * FROM reminders WHERE NOT (isCompleted) """ } }

NULL 33:42

Next we have a compiler error querying for reminders with their priority equal to 3: @Test func whereHighPriority() { assertInlineSnapshot( of: Reminder.all().where { $0.priority == 3 }, as: .sql ) { """ SELECT * FROM reminders WHERE (priority = 3) """ } } Instance method ‘where’ requires the types ‘(some QueryExpression).QueryValue’ and ‘Bool’ be equivalent

NULL 33:45

We just need to update the == overload to return a QueryExpression<Bool> : func == ( lhs: some QueryExpression, rhs: some QueryExpression ) -> some QueryExpression<Bool> { … }

NULL 34:03

And then update the Equals type to specify that it is a QueryExpression<Bool> : struct Equals< LHS: QueryExpression, RHS: QueryExpression >: QueryExpression { typealias QueryValue = Bool … }

NULL 34:34

That change alone nearly fixed every remaining compiler error we have in the test suite. Next that is left is the queries that use the && and || operators. They too need to be updated to only work with QueryExpression<Bool> : func || ( lhs: some QueryExpression, rhs: some QueryExpression ) -> some QueryExpression<Bool> { … } func && ( lhs: some QueryExpression, rhs: some QueryExpression ) -> some QueryExpression<Bool> { … } And we will update the Or and And types to also deal with QueryExpression<Bool> : struct Or< LHS: QueryExpression, RHS: QueryExpression >: QueryExpression { typealias QueryValue = Bool … } struct And< LHS: QueryExpression, RHS: QueryExpression >: QueryExpression { typealias QueryValue = Bool … }

NULL 35:30

And with those changes we are down to just one single compiler error, but this one we do think should be an error: assertInlineSnapshot( of: Reminder.all().where { $0.title }, as: .sql ) { """ SELECT * FROM reminders WHERE title """ } It is no longer valid to construct a query like this, and that’s great! More type-safety

NULL 35:56

We have now baked a tiny type system directly into our SQL builder library. We are allowed to associate a type with each query expression, even if it’s just a small fragment embedded in a larger overall expression. And that allows us to do things like require the where trailing closure to return a query expression of a boolean, since the whole point of

WHERE 36:28

But we can push things even further. There are some malformed queries that are still compiling, and we would prefer if they did not. To make that so we just need to sprinkle in a few more types throughout the library code.

WHERE 36:39

Let’s take a look.

WHERE 36:42

However, these queries are still compiling even though we would like them to not: assertInlineSnapshot( of: Reminder.all().where { $0.title == 3 }, as: .sql ) { """ SELECT * FROM reminders WHERE (title = 3) """ } assertInlineSnapshot( of: Reminder.all().where { $0.title == $0.priority }, as: .sql ) { """ SELECT * FROM reminders WHERE (title = priority) """ }

WHERE 36:58

The problem is that the overload of == that we introduced currently does not have constraints on the query expressions provided to the left and right side: func == ( lhs: some QueryExpression, rhs: some QueryExpression ) -> some QueryExpression<Bool> { … }

WHERE 37:14

This allows any kind of query expression to be used on the left and right.

WHERE 37:19

We can strengthen this by requiring that the underlying QueryValue of the left and right sides have to match: func == <T>( lhs: some QueryExpression<T>, rhs: some QueryExpression<T> ) -> some QueryExpression<Bool> { … }

WHERE 37:35

The library compiles with these changes, but the tests do not. Two of the errors are exactly what we want: assertInlineSnapshot( of: Reminder.all().where { $0.title == 3 }, as: .sql ) { """ SELECT * FROM reminders WHERE (title = 3) """ } assertInlineSnapshot( of: Reminder.all().where { $0.title == $0.priority }, as: .sql ) { """ SELECT * FROM reminders WHERE (title = priority) """ }

WHERE 37:46

We do not want these to compile, and so let’s comment them out.

WHERE 37:50

However, our change has caused other compiler errors that we do want to work. For example, the simple act of checking if the priority of a reminder equals 3 no longer compiles: @Test func whereHighPriority() { assertInlineSnapshot( of: Reminder.all().where { $0.priority == 3 }, as: .sql ) { """ SELECT * FROM reminders WHERE (priority = 3) """ } } Operator function ‘==’ requires the types ‘Int?’ and ‘()’ be equivalent

WHERE 38:10

This is happening because the QueryValue associated type of the Int conformance to QueryExpression is using the default of Void . We need to update that so that it’s query value is Int : extension Int: QueryExpression { typealias QueryValue = Int … }

WHERE 38:28

But that still doesn’t fix the problem. We now have a new error: Operator function ‘==’ requires the types ‘Int?’ and ‘Int.QueryValue’ (aka ‘Int’) be equivalent And this is happening because although the QueryValue of the left and right side of: $0.priority == 3 …are closely related, they are not quite equal. The left-hand side is an optional Int , and the right is an honest Int .

WHERE 38:40

Unfortunately users of Swift do expect non-optionals to be implicitly upgraded to optionals, and so we have no choice but to define a new overload of == that works when the left-hand side is optional and the right-hand side is not: func == <T>( lhs: some QueryExpression<T?>, rhs: some QueryExpression<T> ) -> some QueryExpression<Bool> { Equals(lhs: lhs, rhs: rhs) }

WHERE 39:03

Now that test is back to compiling. The next test that is not compiling queries for reminders whose name is 3 characters long: @Test func whereNameLengthEqual3() { assertInlineSnapshot( of: Reminder.all().where { $0.title.length() == 3 }, as: .sql ) { """ SELECT * FROM reminders WHERE (length(name) = 3) """ } } Binary operator ‘==’ cannot be applied to operands of type ‘some QueryExpression’ and ‘Int’

WHERE 39:11

This is happening because we have not yet updated the length() function to define its QueryValue . It is still defaulting to Void . We can improve this by saying that length returns a QueryExpression<Int> : func length() -> some QueryExpression<Int> { … } struct LengthFunction<Base: QueryExpression>: QueryExpression { typealias QueryValue = Int … }

WHERE 39:34

And now that test is back to compiling.

WHERE 39:37

And finally we have a test that is not compiling that queries for reminders whose name’s length is equal to its priority. It’s a bizarre query, but it should compile: @Test func whereNameLengthEqualsPriority() { assertInlineSnapshot( of: Reminder.all().where { $0.title.length() == $0.priority }, as: .sql ) { """ SELECT * FROM reminders WHERE (length(name) = priority) """ } } Binary operator ‘==’ cannot be applied to operands of type ‘some QueryExpression ’ and ‘Column<Int?>’ However it is not, and it’s again due to an impedance mismatch between comparing an optional and non-optional. But this time the optional is on the right side, and so we need yet another overload of == : func == <T> ( lhs: some QueryExpression<T>, rhs: some QueryExpression<T?> ) -> some QueryExpression<Bool> { … }

WHERE 40:01

And now everything is building, and while we’ve addressed many of the nonsensical queries, there are still more out there that we could write. For example, nothing is stopping us from applying the SQLite length() function to an integer, even though it’s supposed to work with strings: assertInlineSnapshot( of: Reminder.all().where { $0.priority.length() == 3 }, as: .sql ) { """ SELECT * FROM reminders WHERE (length(priority) = 3) """ }

WHERE 40:26

This compiles and generates SQL because the length function is currently on an unconstrained protocol extension: extension QueryExpression { … func length() -> some QueryExpression<Int> { … } }

WHERE 40:33

So instead, we will only allow length , as well as collate and groupConcat to work with string expressions: extension QueryExpression<String> { func groupConcat( separator: String? = nil ) -> some QueryExpression { … } func length() -> some QueryExpression<Int> { … } func collate( _ collation: Collation ) -> some QueryExpression { … } }

WHERE 40:51

And now that nonsensical test is no longer building: // assertInlineSnapshot( // of: Reminder.all().where { $0.priority.length() == 3 }, // as: .sql // ) { // """ // SELECT * // FROM reminders // WHERE (length(priority) = 3) // """ // }

WHERE 41:02

Let’s fix one more. It is currently possible to use && and || in a nonsensical fashion: assertInlineSnapshot( of: Reminder.all().where { $0.title && $0.priority }, as: .sql ) { """ SELECT * FROM reminders WHERE (title AND priority) """ }

WHERE 41:17

While these operators have been constrained to return boolean expressions, they haven’t constrained their operands to be boolean expressions. So let’s fix that: func || ( lhs: some QueryExpression<Bool>, rhs: some QueryExpression<Bool> ) -> some QueryExpression<Bool> { … } func && ( lhs: some QueryExpression<Bool>, rhs: some QueryExpression<Bool> ) -> some QueryExpression<Bool> { … }

WHERE 41:19

And with that, our final nonsensical query is no longer compiling. // assertInlineSnapshot( // of: Reminder.all().where { $0.title && $0.priority }, // as: .sql // ) { // """ // SELECT * // FROM reminders // WHERE (title AND priority) // """ // }

WHERE 41:47

So we can layer more and more type-safety onto our builder and prevent more and more nonsensical, but syntactically valid queries at compile time. Next time: Joins

WHERE 41:56

We have now accomplished some amazing things. We can build SQL queries that can express complex

WHERE 42:12

And we have now tackled 3 of the major parts of a typical SQL query. We can select columns and expressions, we can order by columns and expressions, and now we can filter results by predicates. We are going to move onto the next major part of SQL queries, and this is a big one. Brandon

WHERE 42:28

SQLite is what is known as “relational” database, as are other databases such as Postgres and MySQL. The “relational” adjective refers to the database’s ability to relate the rows of one table with the rows of another. Relationships are formed so that you can join tables together along those relations. This opens up tons of power, such as fetching data from multiple tables in one single query, as well as aggregating the results in one table based on the relationship to another table. Stephen

WHERE 43:05

A prototypical example of this is if we had a “reminders lists” table that represented, well, lists of reminders. Then each reminder would belong to exactly one list. And we may find ourselves wanting to count how many reminders are in each list. We could of course perform one query to fetch all of the lists, and then perform a query for each list to count the number of reminders for each list. We have actually learned enough SQL so far to write those queries. But it is incredibly inefficient. If we have hundreds of lists we will need to execute hundreds of queries. Brandon

WHERE 43:38

Luckily SQL provides a wonderful calculating this kind of aggregate data in one single, efficient query. And it really can be efficient. It is possible to execute such queries with thousands of lists and tens of thousands of rows.

WHERE 43:58

This tool is called