Video #322: SQL Builders: Advanced Joins

Episode: Video #322 Date: Apr 21, 2025 Access: Members Only 🔒 URL: https://www.pointfree.co/episodes/ep322-sql-builders-advanced-joins

Description

We close out our series on SQL query building with a library that can generate some seriously complex queries that select, join, group, aggregate, and filter data across tables. And we show how it can all play nicely with SQL strings by introducing a safe interface to SQL via a custom string interpolation.

Video

Cloudflare Stream video ID: 580db9dd1242b4fa91aadaa3e9139aab Local file: video_322_sql-builders-advanced-joins.mp4 *(download with --video 322)*

References

Transcript

— 0:05

We have now implemented a powerful syntax for representing joins in our query builder, and we are able to preserve the type information of each table involved so that we can write

WHERE 0:34

Let’s now push things even further. As we saw in last episode, the power of joins really starts to shine when it comes to aggregating data across joins. This is what allowed us to do things like selecting all lists from the database, along with a comma-separated list of the titles of reminders in each list. Another advanced query we previously considered was to select all lists from the database, along with a count of the reminders in each list.

WHERE 0:56

Let’s see what it takes to write such advanced queries with our builder. Aggregates across joins

WHERE 1:01

Let’s start by trying to write an aggregating query using the tools we already have, and see what is lacking. We will select all lists along with a comma-separated string of each list’s reminders’ titles.

WHERE 1:18

So, we will start by select all of the lists: RemindersList .all()

WHERE 1:24

Then we will join the “reminders” table along the foreign key: RemindersList .all() .join(Reminder.self) { $0.id == $1.remindersListID }

WHERE 1:35

And then we would like to select the list’s title: RemindersList .all() .join(Reminder.self) { $0.id == $1.remindersListID } .select { $0.title }

WHERE 1:40

Along with the concatenation of the titles of all reminders, which we can do by tupling-up the data and using the groupConcat function that we previously defined: RemindersList .all() .join(Reminder.self) { $0.id == $1.remindersListID } .select { ($0.title, $1.title.groupConcat()) }

WHERE 1:55

Let’s snapshot this query to see what it gives us: @Test func joinAggregate() { assertInlineSnapshot( of: RemindersList .all() .join(Reminder.self) { $0.id == $1.remindersListID } .select { ($0.title, $1.title.groupConcat()) }, as: .sql ) { """ SELECT remindersLists.title, group_concat(reminders.title) FROM remindersLists JOIN reminders ON remindersLists.id = reminders.remindersListID """ } }

WHERE 2:13

Well, it kinda works. It correctly generated the query we wrote, but if we run this query in the SQL console we will see the following: sqlite> SELECT remindersLists.title, group_concat(reminders.title) ...> FROM remindersLists ...> JOIN reminders ON (remindersLists.id = reminders.remindersListID); ┌────────┬──────────────────────────────────────────────────────────────┐ │ title │ group_concat(reminders.title) │ ├────────┼──────────────────────────────────────────────────────────────┤ │ Family │ Groceries,Get haircut,Take a walk,Buy concert tickets,Call a │ │ │ ccountant,Walk the dog │ └────────┴──────────────────────────────────────────────────────────────┘

WHERE 2:28

This is the same problem we saw a few episodes ago when exploring the

JOIN 2:33

All of the reminders are being aggregated together rather than it being done on a list-by-list basis. In order to aggregate the reminders on a per-list basis we need to add a GROUP BY clause to our query, but our builder does not yet support this.

JOIN 2:45

Let’s theorize the syntax first. It would be nice if we could have a group method that takes a trailing closure that is handed the columns of all the tables joined: RemindersList .all() .join(Reminder.self) { $0.id == $1.remindersListID } .select { ($0.title, $1.title.groupConcat()) } .group { $0, $1 }

JOIN 3:00

And then in this trailing closure we could determine which columns or expressions we want to group by. In this case we want to group by the lists’ ids so that we get a result per list: RemindersList .all() .join(Reminder.self) { $0.id == $1.remindersListID } .select { ($0.title, $1.title.groupConcat()) } .group { remindersLists, _ in remindersLists.id }

JOIN 3:20

Note that we need to give a named argument to remindersLists because we do not need to use the second argument, and by not mentioning $1 Swift will not be able to compiler our expression.

JOIN 3:30

We now need to add this group method. We have done this repeatedly during this series, and so it should be quite fast. We will start by adding the state to our Select type to represent the GROUP BY clauses: struct Select<From: Table, each Join: Table>: QueryExpression { var groups: [String] = [] … } Now that we have new state in Select we need to make sure to pass it along when constructing new Select s in our various operations…

JOIN 3:50

And then we can use this information in the queryString computed property to add the GROUP BY clauses after the

WHERE 4:09

Next we will add a group method to Select so that we can append values to this newly created groups state: func group( by groups: () -> <#???#> ) -> Select { }

WHERE 4:27

This method is going to be quite similar to the order method. Just like ORDER BY clauses, GROUP BY clauses are allowed to group by multiple things. For example, we could group by reminders’ isCompleted status as well as their priority : // GROUP BY (reminders.isCompleted, reminders.priority)

WHERE 4:44

This creates a group for each combination of completed status and priority, and then aggregates results in each of those buckets.

WHERE 4:52

And because of this we will want the groups trailing closure to accept the columns of the From table and Join tables, and return any number of groupings: func group<each Grouping: QueryExpression>( by groups: (From.Columns, repeat (each Join).Columns) -> (repeat each Grouping) ) -> Select {

WHERE 5:23

To implement this method we can return a new Select value that passes along the existing columns, joins, orders and wheres, and further needs to concatenate some new groups onto the existing groups: Select( columns: columns, groups: groups + <#???#>, joins: joins, orders: orders, wheres: wheres )

WHERE 5:39

To compute the new groups we can repeat what we have done a few times. We apply the groups trailing closure on our table columns to get a variadically-sized tuple of query expressions, and then we destructure that tuple with for - in repeat each to get the query string for each expression: let groups = groups(From.columns, repeat (each Join).columns) var groupStrings: [String] = [] for group in repeat each groups { groupStrings.append(group.queryString) } return Select( columns: columns, groups: self.groups + groupStrings, joins: joins, orders: orders, wheres: wheres )

WHERE 6:35

And that is all it takes. our tests are now compiling, and we can run the test to see it generates the query we expect: @Test func joinAggregate() { assertInlineSnapshot( of: RemindersList .all() .join(Reminder.self) { $0.id == $1.remindersListID } .select { ($0.title, $1.title.groupConcat()) } .group { remindersLists, _ in remindersLists.id }, as: .sql ) { """ SELECT remindersLists.title, group_concat(reminders.title) FROM remindersLists JOIN reminders ON (remindersLists.id = reminders.remindersListID) GROUP BY remindersLists.id """ } }

WHERE 6:49

And we can copy-and-paste this query into our SQL console just to make sure it works: sqlite> SELECT remindersLists.title, group_concat(reminders.title) ...> FROM remindersLists ...> JOIN reminders ON (remindersLists.id = reminders.remindersListID) ...> GROUP BY remindersLists.id; ┌──────────┬──────────────────────────────────────┐ │ title │ group_concat(reminders.title) │ ├──────────┼──────────────────────────────────────┤ │ Personal │ Get haircut,Take a walk,Walk the dog │ │ Work │ Call accountant │ │ Family │ Groceries,Buy concert tickets │ └──────────┴──────────────────────────────────────┘

WHERE 6:53

And it does!

WHERE 7:02

Now, it was a bit of a bummer that we need to complicate the group operation with a named argument since we only need access to the columns of the lists table: RemindersList .all() .join(Reminder.self) { $0.id == $1.remindersListID } .select { ($0.title, $1.title.groupConcat()) } .group { remindersLists, _ in remindersLists.id }

WHERE 7:12

But our query builder is so flexible that we can choose to upfront the grouping logic before we even join tables: RemindersList .all() .group { remindersLists in remindersLists.id } .join(Reminder.self) { $0.id == $1.remindersListID } .select { ($0.title, $1.title.groupConcat()) }

WHERE 7:27

And now we can drop the named argument: RemindersList .all() .group { $0.id } .join(Reminder.self) { $0.id == $1.remindersListID } .select { ($0.title, $1.title.groupConcat()) }

WHERE 7:30

And we can even use a key path syntax if we want: RemindersList .all() .group(by: \.id) .join(Reminder.self) { $0.id == $1.remindersListID } .select { ($0.title, $1.title.groupConcat()) }

WHERE 8:17

This test still generates the same valid query, but we have a lot of flexibility to localize clauses where they make sense. This is the kind of affordance that regular SQL does not provide us. SQL is strict in requiring the

WHERE 8:48

And now that we have the ability to group our queries we can write another test that selects all lists along with a count of the reminders in each list: @Test func joinAggregateCount() { assertInlineSnapshot( of: RemindersList .all() .group(by: \.id) .join(Reminder.self) { $0.id == $1.remindersListID } .select { ($0.title, $1.id.count()) }, as: .sql ) { """ SELECT remindersLists.title, count(reminders.id) FROM remindersLists JOIN reminders ON (remindersLists.id = reminders.remindersListID) GROUP BY remindersLists.id """ } }

WHERE 9:09

This generates exactly what I expect, and we can copy-and-paste the query into the SQL console to confirm it is correct: sqlite> SELECT remindersLists.title, count(reminders.id) ...> FROM remindersLists ...> JOIN reminders ON (remindersLists.id = reminders.remindersListID) ...> GROUP BY remindersLists.id; ┌──────────┬─────────────────────┐ │ title │ count(reminders.id) │ ├──────────┼─────────────────────┤ │ Personal │ 3 │ │ Work │ 1 │ │ Family │ 2 │ └──────────┴─────────────────────┘

WHERE 9:21

And this is indeed correct! The “Personal” list does have 3 reminders, “Work” has 1, and “Family” has 2. Aggregate expressions and left joins

WHERE 9:26

We now have the full power of joins and aggregates at our disposal. We can join any two tables together, and then aggregate the data in one table on a per-row basis in the other table. These kinds of queries are incredible powerful, and where SQL really starts to shine. Brandon

WHERE 9:39

But there is still something not quite right about the queries we have written so far. Remember that we had a “Secret Project” list we previously created, and it does not yet have any reminders. And because of that, it did not show up in the results of your query.

WHERE 9:55

In order to make sure that all lists show up in the final result set, even if they don’t have any reminders, we need to make use of “left” joins. Let’s explore that now.

WHERE 10:06

Here’s the query you ran just a moment ago: sqlite> SELECT remindersLists.title, count(reminders.id) ...> FROM remindersLists ...> JOIN reminders ON (remindersLists.id = reminders.remindersListID) ...> GROUP BY remindersLists.id; ┌──────────┬─────────────────────┐ │ title │ count(reminders.id) │ ├──────────┼─────────────────────┤ │ Personal │ 3 │ │ Work │ 1 │ │ Family │ 2 │ └──────────┴─────────────────────┘

WHERE 10:13

But if we quickly select all of the lists in our database: sqlite> SELECT * FROM remindersLists; ┌────┬────────────────┐ │ id │ title │ ├────┼────────────────┤ │ 1 │ Personal │ │ 2 │ Work │ │ 3 │ Family │ │ 4 │ Secret Project │ └────┴────────────────┘

WHERE 10:18

…we will see that there is indeed an extra list that did not show up in the aggregate. This is happening because a regular

ON 10:42

And since “Secret Project” does not have any reminders, there are no rows that satisfy this condition.

ON 10:46

In order to force the “Secret Project” to appear in the final data set, even if it has no associated data, we need to use a LEFT JOIN . Our query builder does not currently support such joins, but it is quite straightforward to add.

ON 11:01

In fact, we can just copy-and-paste our current join method, rename it to leftJoin , and update the query string generated to say LEFT JOIN instead of just

JOIN 11:19

That is all it takes, and we can now update our joinAggregateCount query to use a leftJoin instead of a regular join : @Test func joinAggregateCount() { assertInlineSnapshot( of: RemindersList .all() .group(by: \.id) .leftJoin(Reminder.self) { $0.id == $1.remindersListID } .select { ($0.title, $1.id.count()) }, as: .sql ) { """ SELECT remindersLists.title, count(reminders.id) FROM remindersLists LEFT JOIN reminders ON (remindersLists.id = reminders.remindersListID) GROUP BY remindersLists.id """ } }

JOIN 11:43

This query generated is exactly what I would expect, and we can paste it into the SQL console to confirm it now works: sqlite> SELECT remindersLists.title, count(reminders.id) ...> FROM remindersLists ...> LEFT JOIN reminders ON (remindersLists.id = reminders.remindersListID) ...> GROUP BY remindersLists.id; ┌────────────────┬─────────────────────┐ │ title │ count(reminders.id) │ ├────────────────┼─────────────────────┤ │ Personal │ 3 │ │ Work │ 1 │ │ Family │ 2 │ │ Secret Project │ 0 │ └────────────────┴─────────────────────┘

JOIN 11:57

…and it does!

JOIN 12:02

And that is basically all that is needed for left joins. But there is another SQL concept that is closely related to joins and groups. Recall that if you wanted to further filter this final data set by the reminders count, such as take only the lists that have exactly one reminder, then you cannot use a

WHERE 12:38

In SQL, the way one filters on aggregated data is with the

HAVING 13:31

And now we need to make this syntax a reality. This is a process we’ve now done 4 times and so at this point its a cinch.

HAVING 13:44

We will start by adding some state to the Select to represent the “HAVING” clause: struct Select<From: Table, each Join: Table>: QueryExpression { … var havings: [String] = [] … }

HAVING 13:53

And then we will update the queryString property to render the “HAVING” clause right after the GROUP BY clause: if !havings.isEmpty { sql.append("\nHAVING \(havings.joined(separator: " AND "))") }

HAVING 14:22

And we will need to make sure to pass along this state to every Select we construct in our operations, otherwise we risk losing this information on accident…

HAVING 14:45

Next we will add a having method to the Select type: func having() {} But this method is basically identical to what we do for the where method. So, let’s just copy-and-paste where , rename it to having , and append to the havings state instead of the wheres state: func having( _ predicate: (From.Columns, repeat (each Join).Columns) -> some QueryExpression<Bool> ) -> Select { Select( columns: columns, groups: groups, havings: havings + [predicate(From.columns, repeat (each Join).columns).queryString], joins: joins, orders: orders, wheres: wheres ) }

HAVING 17:00

That is all it takes, and now our tests are compiling, and running them generates a new query that is correct: @Test func joinAggregateCountHaving() { assertInlineSnapshot( of: RemindersList .all() .group(by: \.id) .leftJoin(Reminder.self) { $0.id == $1.remindersListID } .select { ($0.title, $1.id.count()) } .having { $1.id.count() == 1 }, as: .sql ) { """ SELECT remindersLists.title, count(reminders.id) FROM remindersLists LEFT JOIN reminders ON (remindersLists.id = reminders.remindersListID) GROUP BY remindersLists.id HAVING (count(reminders.id) = 1) """ } }

HAVING 17:22

And if we paste this query into the SQL console we will see it works: sqlite> SELECT remindersLists.title, count(reminders.id) ...> FROM remindersLists ...> LEFT JOIN reminders ON (remindersLists.id = reminders.remindersListID) ...> GROUP BY remindersLists.id ...> HAVING (count(reminders.id) = 1); ┌───────┬─────────────────────┐ │ title │ count(reminders.id) │ ├───────┼─────────────────────┤ │ Work │ 1 │ └───────┴─────────────────────┘

HAVING 17:33

It’s honestly incredible to see how easy it is to support more and more of SQL in our builder. Safe SQL strings

HAVING 17:41

We now have left joins integrated into our query builder, as well as “HAVING” clauses so that we can filter results by their aggregated data. And of course it wouldn’t take much work to support other kinds of joins, such as right joins or outer joins, which we haven’t even discussed in this series but they are pretty straightforward to understand once you know how left joins work. Stephen

HAVING 18:03

We are nearing the end of our series on query building in Swift, but we have one last topic we want to discuss. While it is great to be able to write such complex SQL queries in Swift, while getting the benefit of some light type-safety and even safety that we are using the actual schema of our tables, there is inevitably going to be SQL statements out there that are just too complex to try to write in our builder library.

HAVING 18:27

This especially happens when needing to invoke many SQL functions on columns or perform logic in expressions, such as using the SQL iif function we used a few episodes ago. In those situations we would like there to be a simple escape hatch to write small fragments of SQL that can be plugged into our query builder. And even though we are describing this as an “escape hatch”, we still want to retain as much safety as possible. We’d love to still use our static description of the table schema in such queries, and we would never want to open ourselves up to SQL injection attacks either.

HAVING 19:01

It turns out this is quite easy to accomplish, and quite amazing to see. Let’s dig in.

HAVING 19:08

To motivate why we may sometimes want to insert raw SQL into our query builder, let’s recall what it’s like to select all lists from the database, along with the count of their incomplete reminders. This small subtly, that of aggregating a subset of associated rows, turned out to be tricky.

HAVING 19:24

The way we constructed the query was to join the lists and reminders together without a

WHERE 20:29

This is quite a complex SQL fragment: count(iif(reminders.isCompleted, NULL, reminders.id))

WHERE 20:33

How would we build the tools in our query builder to reconstruct this fragment? Maybe boolean query expressions have an if method that allow you to determine what to do in the true case and false case: $1.isCompleted.if(_, _)

WHERE 21:12

Then we would somehow need to represent

NULL 21:24

And then we need to count the whole thing: $0.isCompleted.if(NULL, $0.id).count()

NULL 21:29

This seems pretty complicated. One way to simplify would be to have an overload of if that makes you specify only a true condition, and the false will be assumed to be

NULL 21:51

It’s a little shorter I suppose, but it seems even harder to read.

NULL 21:57

Comparing this theoretical Swift builder syntax to the original SQL: count(iif(reminders.isCompleted, NULL, reminders.id))

NULL 22:08

…I’m not quite sure the Swift code is demonstrably better. It seems to be leaning too far into idiomatic Swift at the cost of making it look unrecognizable to anyone familiar with SQL.

NULL 22:19

And so in situations like this it would be nice to have a small escape hatch to insert little bits of raw SQL. Let’s write a test that computes this aggregate with a syntax that we like, and then we will try to make that syntax a reality.

NULL 22:26

The simplest way to introduce raw SQL would be a new type that allows us to specify a fragment of a SQL string: @Test func rawSQL() { assertInlineSnapshot( of: RemindersList .all() .group(by: \.id) .leftJoin(Reminder.self) { $0.id == $1.remindersListID } .select { ( $0.title, SQL("count(iif(reminders.isCompleted, NULL, reminders.id))") ) } as: .sql ) }

NULL 22:39

It’s a little noisy, but it at least makes it very clear when we are exiting our builder’s safety guards to write some raw SQL.

NULL 22:46

Well, a

SQL 23:04

It just lets you put any literal SQL code you want directly into a builder expression.

SQL 23:13

Our test is nearly compiling, but since we are no longer referencing $1 anywhere we must give an explicit name for the first argument: @Test func rawSQL() { assertInlineSnapshot( of: RemindersList .all() .group(by: \.id) .leftJoin(Reminder.self) { $0.id == $1.remindersListID } .select { remindersLists, _ in ( remindersLists.title, SQL("count(iif(reminders.isCompleted, NULL, reminders.id))") ) }, as: .sql ) }

SQL 23:26

Now this compiles and does generate valid SQL: @Test func rawSQL() { assertInlineSnapshot( of: RemindersList .all() .group(by: \.id) .leftJoin(Reminder.self) { $0.id == $1.remindersListID } .select { remindersLists, _ in ( remindersLists.title, SQL("count(iif(reminders.isCompleted, NULL, reminders.id))") ) }, as: .sql ) { """ SELECT remindersLists.title, count(iif(reminders.isCompleted, NULL, reminders.id)) FROM remindersLists LEFT JOIN reminders ON (remindersLists.id = reminders.remindersListID) GROUP BY remindersLists.id """ } }

SQL 23:50

So, this technically does work, but it’s clearly not ideal. We had to resort to hard coding the names of our table and columns in a string. One of the big benefits to our query library is that it has information about the columns of the tables, giving us a static way to reference to columns in Swift.

SQL 24:13

Wouldn’t it be nice if we could use this raw SQL escape hatch while still being able to refer to the static description of our tables? What if you could just interpolate the columns defined on $1 directly into the SQL string: @Test func rawSQL() { assertInlineSnapshot( of: RemindersList .all() .group(by: \.id) .leftJoin(Reminder.self) { $0.id == $1.remindersListID } .select { ( $0.title, SQL("count(iif(\($1.isCompleted), NULL, \($1.id))") ) } as: .sql ) }

SQL 24:43

That’d be pretty incredible. But, if we run this test we will see that it generates bad SQL: @Test func rawSQL() { assertInlineSnapshot( of: RemindersList .all() .group(by: \.id) .leftJoin(Reminder.self) { $0.id == $1.remindersListID } .select { ( $0.title, SQL("count(iif(\($1.isCompleted), NULL, \($1.id))") ) }, as: .sql ) { """ SELECT remindersLists.title, count(iif(Column<Bool>(name: "isCompleted", table: "reminders"), NULL, Column<Int>(name: "id", table: "reminders")) FROM remindersLists LEFT JOIN reminders ON (remindersLists.id = reminders.remindersListID) GROUP BY remindersLists.id """ } } It’s interpolating the description of the column into the string, including its type name and properties.

SQL 24:55

In order to support this kind of syntax we need to leverage a feature of Swift that is not often used, and something we have never demonstrated on Point-Free in all of our years. And that’s StringInterpolationProtocol . It allows you to get access to each of these interpolations: SQL("count(iif(\($1.isCompleted), NULL, \($1.id))")

SQL 25:09

…so that you can perform custom logic for inserting these values into the string that is different from naively getting their default description and inserting it into the string.

SQL 25:17

To accomplish this we will make

SQL 25:27

And there are a few things we need to do to conform to this protocol.

SQL 25:29

First, ExpressibleByStringInterpolation requires an associated type named StringInterpolation that conforms to StringInterpolationProtocol . A default can be provided by ExpressibleByStringInterpolation , but it is inside this type that we perform the actual logic for interpolating values into the string, so we will create a whole new type nested inside

SQL 25:57

To conform to StringInterpolationProtocol there are a few things we need to provide. First we must provide an initializer that gives us size of the literal string that interpolations are being inserted into, as well as the number of interpolations being performed: init(literalCapacity: Int, interpolationCount: Int) { }

SQL 26:08

So, for example, this interpolated string: "count(iif(\($1.isCompleted), NULL, \($1.id))" …has 19 as the literalCapacity count because there are 19 non-interpolation characters in the string, and then interpolationCount is 2. This information can be used to reserve the capacity of data held in this type, but that’s only needed for performance reasons and so we will not worry about it now.

SQL 26:29

But, this does make it clear that we do need to hold onto some data in this type. Soon we will implement more methods that will process each literal and interpolation segment, and we will need to accumulate that data somewhere. So, let’s have StringInterpolation hold onto the underlying string: struct StringInterpolation: StringInterpolationProtocol { var rawValue: String init(literalCapacity: Int, interpolationCount: Int) { rawValue = "" } }

SQL 26:53

Next we need to implement at least two methods in StringInterpolation . One is called for each segment that consists of a string literal: mutating func appendLiteral(_ literal: String) { }

SQL 27:05

So, going back to our example interpolated string: "count(iif(\($1.isCompleted), NULL, \($1.id))" This method would be called with the string “count(iif(”, then again with the string “, NULL, “, and then finally with the string “)”. In all of these situations we simply want to append the string literal to our underlying storage: mutating func appendLiteral(_ literal: String) { rawValue.append(literal) }

SQL 27:20

The other requirement is the method that is called for each interpolated value in the string: mutating func appendInterpolation(_ value: <#???#>) { }

SQL 27:35

Again going back to our example interpolated string: "count(iif(\($1.isCompleted), NULL, \($1.id))" This will be called for the value $1.isCompleted and $1.id . And note that the appendInterpolation function takes a function that is capable of taking any value. It can even be generic. And this allows us to restrict what kinds of values are interpolated into the string.

SQL 27:54

In our situation, we only want to be able to interpolate query expressions, and when done so we will just turn that expression into a queryString and append that to the underlying storage: mutating func appendInterpolation(_ value: some QueryExpression) { rawValue.append(value.queryString) }

SQL 28:19

That is all it takes for the StringInterpolation type, but there is still a bit more work to be done for the

SQL 28:25

The ExpressibleByStringInterpolation protocol requires that this type be initializable from a literal string: init(stringLiteral: String) { queryString = stringLiteral }

SQL 28:33

…as well as a StringInterpolation value: init(stringInterpolation: StringInterpolation) { queryString = stringInterpolation.rawValue }

SQL 28:48

And that is all it takes.

SQL 28:51

We can now re-run our test to record a new snapshot of our query, and now it generates the SQL I would expect: @Test func rawSQL() { assertInlineSnapshot( of: RemindersList .all() .group(by: \.id) .leftJoin(Reminder.self) { $0.id == $1.remindersListID } .select { ( $0.title, SQL("count(iif(\($1.isCompleted), NULL, \($1.id)))") ) }, as: .sql ) { """ SELECT remindersLists.title, count(iif(reminders.isCompleted, NULL, reminders.id)) FROM remindersLists LEFT JOIN reminders ON (remindersLists.id = reminders.remindersListID) GROUP BY remindersLists.id """ } }

SQL 30:07

And if we ever have a type in our column name, such as if we thought the “isCompleted” column was named just “completed”: SQL("count(iif(\($1.completed), NULL, \($1.id)))")

SQL 30:13

…we immediately get a compiler error: Value of type ‘Reminder.Columns’ has no member ‘completed’

SQL 30:19

And further, because we only allow QueryExpression s to be interpolated, we are also protected from SQL injection attacks. If we had some user input lying around that held malicious SQL: let userInput = "; DROP TABLE reminders; --"

SQL 30:44

If we try to interpolate this value into our

SQL 30:48

…we are immediately met with a compiler error letting us know this is not possible: Instance method ‘appendInterpolation’ requires that ‘String’ conform to ’QueryExpression’

SQL 30:51

And that is pretty incredible. Conclusion

SQL 31:00

We can now dip our toes into raw SQL for those times that the SQL is just too complex for us to write in the query builder. But even when we do escape into raw SQL we don’t have to give up on all safety. Sure we lose some of the type-safety, but we don’t lose out on the schema-safety afforded to us by the fact that Swift knows about the schema of each table. Nor do we have to open ourselves up to potential SQL injection attacks. Brandon

SQL 31:22

We are finally at the end of our SQL building series. It took us 7 episodes to get here, but it’s pretty incredible what we have accomplished. We now have a very capable library for building complex SQL queries, including

JOIN 31:49

There is still of course a lot more work to do to have a final version of this query building library. We haven’t discussed how one can bind data into these queries, which is necessary for dynamic queries. Nor have we talked about how to decode the actual results from a SQLite database into our Swift data types. All of that is possible, and it is done in the final version of our library, which will be released soon. But we don’t think we need to waste anymore of your time covering those topics.

JOIN 32:16

Instead, our next series will finally dive into what we are calling “Modern Persistence”. This is a series we teased at the end of last year because we thought we were ready to cover it. Little did we know we wanted to create an entire SQL building library first! But now that we have we can finally discuss how we think a modern application can deal with persistence in SQLite. This will include domain modeling in a database schema, complex queries for fetching data in the most efficient manner, isolating features to operate on the small data set necessary, and of course, how do you test the whole thing.

JOIN 32:57

Until next time! References SQLite The SQLite home page https://www.sqlite.org Downloads Sample code 0322-sql-building-pt9 Point-Free A hub for advanced Swift programming. Brought to you by Brandon Williams and Stephen Celis . Content Become a member The Point-Free Way Beta previews Gifts Videos Collections Free clips Blog More About Us Community Slack Mastodon Twitter BlueSky GitHub Contact Us Privacy Policy © 2026 Point-Free, Inc. All rights are reserved for the videos and transcripts on this site. All other content is licensed under CC BY-NC-SA 4.0 , and the underlying source code to run this site is licensed under the MIT License .