Video #316: SQL Builders: Selects

Episode: Video #316 Date: Mar 10, 2025 Access: Members Only 🔒 URL: https://www.pointfree.co/episodes/ep316-sql-builders-selects

Description

We begin to build a type-safe SQL query builder from scratch by familiarizing ourselves with the SELECT statement. We will explore the SQLite documentation to understand the syntax, introduce a type that can generate valid statements, and write powerful inline snapshot tests for their output.

Video

Cloudflare Stream video ID: 002a649b3fc996e7b3321e0ea2ccf459 Local file: video_316_sql-builders-selects.mp4 *(download with --video 316)*

References

Transcript

— 0:05

We have now properly set up the problem that we want to solve. We want a simple Swift API for constructing SQL statements that can be executed by SQLite. We would like this API to mimic what one would do when writing SQL directly, but should have the added benefit of better discoverability thanks to autocomplete, better safety thanks to static types, and the ability to reuse and compose queries together. And further we have given a preview of what this Swift API can look like by taking a peek at our Structured Queries library, which has not yet been released. Brandon

— 0:29

And that will come in due time, but for now let’s take a look at what it takes to build a query building library like this. It turns out to require the full power of nearly every feature available to modern Swift, including existentials, protocols with primary associated types, key paths, and even variadic generics. This library simply would not be possible to build in a version from Swift from just a year ago.

— 0:55

And along we the way we are also going to use this as an opportunity to dive deep into the concepts of SQL as its own language. It is a powerful language, and you will be a better engineer for being familiar with it.

— 1:09

We are going to start by defining a schema for some data types so that we can construct some interesting queries, and then see what it takes to build a library that can handle those queries.

— 1:21

Let’s begin… SQLite SELECT

— 1:24

Let’s start by creating a fresh new SQLite database by invoking the sqlite3 command from terminal: $ sqlite3 db.sqlite

— 1:41

And it’s worth mentioning that SQLite is only one flavor of the possible SQL systems out there. There’s MySQL, Postgres, and a lot more out there. Most of those systems have a lot of compatibility between their syntax as they are loosely based on a SQL standard. But there are differences to be aware of, and that will mean even with the most well-designed query builder library you run the risk of accidentally writing SQL that is not valid for the database you are working with.

— 2:11

But either way, we are now in a SQLite console: SQLite version 3.43.2 2023-10-10 13:08:14 Enter ".help" for usage hints. sqlite> …where we can execute SQLite commands.

— 2:17

We don’t currently have any tables in the database so there isn’t much we can do. We can perform a

SELECT 2:34

Nothing too impressive so far.

SELECT 2:36

There are also a bunch of “pragma” statements that one can execute inside SQLite, which allow you to inspect and change the environment SQLite is running in. For example, you can get a list of tables in the database by running pragma table_list; : sqlite> pragma table_list; main|sqlite_schema|table|5|0|0 temp|sqlite_temp_schema|table|5|0|0

SELECT 3:02

That output is kind of hard to read, and so SQLite allows customizing the format of results by setting the .mode to box : sqlite> .mode box

SELECT 3:18

Re-running the query we see something much nicer: sqlite> pragma table_list; ┌────────┬────────────────────┬───────┬──────┬────┬────────┐ │ schema │ name │ type │ ncol │ wr │ strict │ ├────────┼────────────────────┼───────┼──────┼────┼────────┤ │ main │ sqlite_schema │ table │ 5 │ 0 │ 0 │ │ temp │ sqlite_temp_schema │ table │ 5 │ 0 │ 0 │ └────────┴────────────────────┴───────┴──────┴────┴────────┘

SELECT 3:21

And this shows that even though we haven’t personally created a table in the database yet, there are some hidden tables lurking in the shadows.

SELECT 3:30

OK, now let’s start creating some tables for us to play around with. We are going to imagine that we are working on a kind of reminders app, like the one that ships with iOS and macOS. And so we will start by creating a table that holds reminders, and later we will add a lists table so that we can group many reminders into lists.

SELECT 3:55

To create a new table one uses a CREATE TABLE statement in SQL. There are a lot of parts to a CREATE TABLE statement, but the SQLite documentation website has some very useful flow charts that make it clear everything that can go into such a statement. So, let’s open the docs to find the associated flow chart .

SELECT 4:30

This shows that a CREATE TABLE statement belongs with the word CREATE TABLE , optionally followed by

AS 5:10

And then finally an open parens, ( , followed by a list of columns separated by commas, and ended with a closing paren. There is further some table-options that can be added at the end, but we will not worry about that either. So, let’s write a SQL statement for creating a table for reminders. We can start with CREATE TABLE : CREATE TABLE Then we specify the name of the table: CREATE TABLE "reminders" We personally like to pluralize our table names, but there are those out there that prefer to use singular nouns. At the end of the day it’s up to you. Next we open up a kind of scope for the “CREATE” statement by using parentheses: CREATE TABLE "reminders" ( )

AS 5:43

Inside the parentheses we list out all of the columns we want in our table, such as an “id” column to uniquely identify the reminder: CREATE TABLE "reminders" ( "id" ) We can specify more information about the column, and this is documented with a sub-flow chart for table-name :

AS 5:48

So, we can specify a type of the column, which in this case is an integer: CREATE TABLE "reminders" ( "id" integer )

AS 5:51

It is worth mentioning that SQLite is a dynamically typed database, and so specifying this type is only a hint for SQLite. We are still allowed to insert strings, doubles and data blobs into this column, and SQLite will not complain a bit.

AS 6:09

We can further define “constraints” for the column. Since this ID is supposed to uniquely identify the reminder we can mark as NOT NULL to force it to always have a value, and PRIMARY KEY in order to enforce that the value is unique amongst all rows in the table: CREATE TABLE "reminders" ( "id" integer NOT NULL PRIMARY KEY )

AS 6:30

And finally, we annotate this column as being

AUTOINCREMENT 6:42

Next we can add a “title” column that will be of type “text”, it will also be non-null, but we will give it a default of an empty string: CREATE TABLE "reminders" ( "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "title" text NOT NULL DEFAULT '' );

AUTOINCREMENT 7:08

Next we will add a column for determining whether or not the reminder has been completed. We would like to represent this as a boolean, and we can even give SQLite a hint of this like so: CREATE TABLE "reminders" ( "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "title" text NOT NULL DEFAULT '', "isCompleted" boolean NOT NULL DEFAULT 0 );

AUTOINCREMENT 7:14

But in reality SQLite doesn’t actually have a boolean type. Internally it will model this as an integer using 0 as false and 1 as true.

AUTOINCREMENT 7:31

And just to have a bit more data in our table, let’s also allow specifying a priority for the reminder as an integer: CREATE TABLE reminders ( "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "title" text NOT NULL DEFAULT '', "isCompleted" boolean NOT NULL DEFAULT 0, "priority" integer );

AUTOINCREMENT 7:45

However, in this case we will allow NULL because we do want there to be the concept of a reminder that simply does not have a priority specified.

AUTOINCREMENT 8:24

With that done we can already insert some data into this table. An insertion is a special kind of SQL statement called an

INSERT 8:51

It’s pretty intense, but there’s a lot of this flow chart we can ignore for now.

INSERT 8:56

We will ignore the

VALUES 9:29

Then we specify all of the columns we will be providing data for. We can specify the title and allow SQLite to enter the defaults for every other column: INSERT INTO "reminders" ("title")

VALUES 9:47

Then you specify

VALUES 9:53

And finally you can list out as many rows as you want by providing a comma-separated list of tuples: INSERT INTO "reminders" ("title") VALUES ('Groceries'), ('Get haircut');

VALUES 10:39

Notice that we did not have to specify the id for this insertion because SQLite will generate that for us, thanks to the

NULL 10:53

If we did want to insert data for those columns we would specify the columns and construct tuples of that data to insert: INSERT INTO "reminders" ("title", "priority", "isCompleted") VALUES ('Take a walk', 1, true), ('Buy concert tickets', 2, true), ('Call accountant', 3, false);

NULL 11:40

Note that SQLite does allow true and false literals to represent 1 and 0 , though technically this is not standard SQL, so keep that in mind if you use other SQL libraries, like Postgres or MySQL.

NULL 12:09

And now that we have inserted some data into the table, we can query the database to select rows from the table. Such statements are called

SELECT 12:33

We will again ignore the

OFFSET 13:15

Instead, let’s just write a very simple

SELECT 13:57

That returns the following results: ┌────┬─────────────────────┬──────────┬─────────────┐ │ id │ title │ priority │ isCompleted │ ├────┼─────────────────────┼──────────┼─────────────┤ │ 1 │ Groceries │ │ 0 │ │ 2 │ Get haircut │ │ 0 │ │ 3 │ Take a walk │ 1 │ 1 │ │ 4 │ Buy concert tickets │ 2 │ 1 │ │ 5 │ Call accountant │ 3 │ 0 │ └────┴─────────────────────┴──────────┴─────────────┘

SELECT 14:05

There is also a shorthand for selecting all columns of a table: SELECT * FROM "reminders";

SELECT 14:19

This can be handy when writing queries by hand, but ideally you would specify each column explicitly.

SELECT 14:21

It is also possible to select a subset of columns in the query, such as just the reminder’s name: SELECT "title" FROM "reminders";

SELECT 14:29

That fetches the following result set: ┌─────────────────────┐ │ title │ ├─────────────────────┤ │ Groceries │ │ Get haircut │ │ Take a walk │ │ Buy concert tickets │ │ Call accountant │ └─────────────────────┘

SELECT 14:30

It’s also possible to compute aggregates of tables, such as counting all of the rows in a table: sqlite> SELECT count(*) FROM reminders; ┌──────────┐ │ count(*) │ ├──────────┤ │ 5 │ └──────────┘

SELECT 14:47

We will be exploring aggregate functions a lot more in the upcoming episodes, and so for now we will only show off this basic query. Query builder Select Brandon

SELECT 15:02

We have now seen the basics of connecting to a SQLite database from the command line, creating a table, inserting data into that table, and then selecting data from that table. And the SQLite website has been very helpful each step of the way, and so if you ever have questions about SQL syntax we highly recommend you refer to the docs. Stephen

SELECT 15:21

Now, all of this barely scratches the surface of what is available to us, but we we have been exposed to enough of the basics of SQL that I think it would be fun to start creating our query builder library. Let’s see what it takes to design a simple API that can can create these kinds of simple

SELECT 15:39

I’ve got a fresh SPM package opened with a library that will hold our query builder code, as well as a test target so that we can verify that the query builder is generating the valid SQL statements that we expect.

SELECT 15:49

But the question is… where do we even begin?!

SELECT 15:50

As we have seen with the various flow charts, SQL is quite a complex language, and it is hard to know where to begin when building a library for creating SQL strings. To keep things simple we will begin just with

INSERT 16:04

But even

SELECT 16:13

But, at a high level, the

FROM 16:25

This is a well-defined set of data that we can easily encode into a Swift type. It is just a struct with a string for the name of the table we are selecting from, and the columns we are selecting: public struct Select { var columns: [String] var from: String }

FROM 16:49

We now have a struct that represents the most basic kind of

SELECT 17:18

And with this done we can already write our first test: @Test func basics() { #expect( Select(columns: ["id", "title"], from: "reminders") .queryString == """ SELECT id, title FROM reminders """ ) }

SELECT 17:55

And this passes.

SELECT 18:00

However, this isn’t quite right. What happens if we pass an empty collection of columns? We can write a test to find out: @Test func emptyColumns() { #expect( Select(from: "reminders", columns: []).queryString == """

SELECT 18:16

This has now generated invalid SQL.

SELECT 18:23

Instead of naively joining the columns with commas, we can check if the columns are empty, and in that case we will decide to use * to select all columns: """ SELECT \(columns.isEmpty ? "*" : columns.joined(separator: ", ")) FROM \(tableName) """

SELECT 18:38

Now our test generates a valid SQL query: @Test func emptyColumns() { #expect( Select(columns: [], from: "reminders").queryString == """ SELECT * FROM reminders """ ) }

SELECT 18:46

But, even with that fixed, we of course do not want to write our queries like this. We don’t want to construct some gigantic Select value to describe our query. That would essentially be creating a completely separate language dialectic for specifying SQL queries.

SELECT 18:57

Instead we want to build a suite of Swift APIs that allow us to build up a SQL statement such that the Swift code itself mimics what SQL looks like. And we’d like these tools to work on Swift data types that we define so that we can have the compiler help us write safe queries.

SELECT 19:12

So, suppose we had a first class type to represent our reminders table: struct Reminder { let id: Int var title = "" var isCompleted = false var priority: Int? }

SELECT 19:29

It would be nice if we could do something like: Reminder.select("id", "title", "priority")

SELECT 19:49

…to represent the query: SELECT "id", "title", "priority" FROM "reminders" And if you wanted to select a subset of columns you would be free to: Reminder.select("title")

SELECT 20:04

Or if you wanted to select all columns you could simply do: Reminder.all()

SELECT 20:11

Let’s see what it takes to make this a reality. We can start very concretely by defining these static methods directly on the Reminder type: struct Reminder { … static func select(_ columns: String...) -> Select { Select(columns: columns, from: "reminders") } static func all() -> Select { Select(columns: [], from: "reminders") } } We can write two tests that confirm that these helpers work the way we expect: @Test func selectWithColumns() { #expect( Reminder.select("id", "title").queryString == """ SELECT id, title FROM reminders """ ) } @Test func selectAllColumns() { #expect( Reminder.all().queryString == """ SELECT * FROM reminders """ ) }

SELECT 20:59

And they do!

SELECT 21:00

But of course this is needlessly concrete. The moment we introduce another table to our database we are going to have to reimplement these helpers so that we can construct

SELECT 21:12

In order to allow new tables to opt into this behavior we need to introduce some abstraction. Our first unit of abstraction will be that of a table, and its only requirement is describing the name of the table as it exists in the database: protocol Table { static var tableName: String { get } }

SELECT 21:32

Then our Reminder type can conform to this protocol like so: struct Reminder: Table { static let tableName = "reminders" … }

SELECT 21:41

And already this allows us to generalize our concrete helpers by using Self.tableName instead of hard coding the string “reminders”: static func select(_ columns: String...) -> Select { Select(columns: columns, from: Self.tableName) } static func all() -> Select { Select(columns: [], from: Self.tableName) }

SELECT 21:49

And now we can see that these functions don’t use any concrete details about the Reminder type at all. This means we can move these to a protocol extension of Table : extension Table { static func select(_ columns: String...) -> Select { Select(columns: columns, from: Self.tableName, ) } static func all() -> Select { Select(columns: [], from: Self.tableName) } }

SELECT 22:04

Everything still compiles and tests still pass, but we now have a small bit of abstraction in place. It is now possible for us to introduce new types that represent tables, such as a Tag : struct Tag: Table { static let tableName = "tags" }

SELECT 22:24

And we will instantly get access to the all and select helpers on Team .

SELECT 22:34

We can even provide very basic support for count queries. It’s simply a Select whose column is the count(*) string: extension Table { static func count() -> Select { Select(columns: ["count(*)"], from: tableName) } }

SELECT 22:48

And we can write a test to confirm that this generates a valid SQL statement: @Test func count() { #expect( Tag.count().queryString == """ SELECT count(*) FROM tags """ ) }

SELECT 23:15

And it does. Type safe selection with key paths

SELECT 23:18

We’re now making some inroads in creating a query building library in Swift. The steps have been small so far, but we do have a Table protocol that abstracts the idea of a Swift type that represents a table in a SQLite database. Further, any type that conforms to that protocol immediately gets access to static methods all and select that allow one to construct simple

SELECT 23:43

However, our reliance on strings to describe the columns is of course not very safe. We are free to write anything in those strings, even if it does not correspond to a column in our table. And worse, if in the future we rename a column in the database and rename the corresponding field in the type, all of our queries will continue to compile even though they are no longer valid.

SELECT 24:04

We will add a bit of safety to our select function by making it so that you can only select columns that actually exist in your table.

SELECT 24:12

Let’s take a look.

SELECT 24:15

We may be tempted to naively allow selecting columns via key paths like so: extension Table { static func select(_ columns: PartialKeyPath<Self>...) -> Select { Select(columns: [<#???#>], from: tableName) } }

SELECT 24:51

Partial key paths are a way of statically describing a field on a type without having an instance of the type to work with. And this does indeed provide some type safety. We can add a new test and see that now select allows us to specify the columns via key paths: @Test func selectWithKeyPaths() { Reminder.select(\.id, \.title) }

SELECT 25:38

…which gives us autocomplete support and helps protect us against typos. For example, suppose we thought the isCompleted field was actually named completed : @Test func selectWithKeyPaths() { Reminder.select(\.id, \.title, \.completed) }

SELECT 26:01

We instantly get a compiler error, and that’s great.

SELECT 26:11

But there are still some problems with this. For one thing, its not just stored properties that get key paths. Computed properties do too. For example, if we add a computed property to Reminder : struct Reminder: Table { … var titleIsLong: Bool { title.count >= 100 } }

SELECT 26:35

…we are instantly allowed to select titleIsLong from the reminders table: @Test func selectWithKeyPaths() { Reminder.select(\.id, \.title, \.titleIsLong) }

SELECT 26:42

But no such column exists, and this is completely nonsensical. The SQL generated for this kind of select would be invalid.

SELECT 26:55

And further, computed properties on more primitive types, such as String , may lead you to believe it’s legitimate to do something like this: @Test func selectWithKeyPaths() { Reminder.select(\.id, \.title.count) }

SELECT 27:04

And you may even expect that perhaps this executes a count aggregate on the title field. However, that will not happen, and that makes allowing this kind of syntax very confusing.

SELECT 27:23

The other problem is that we haven’t actually completed the implementation of this select yet: static func select(_ columns: PartialKeyPath<Self>...) -> Select { Select(columns: [<#???#>], from: tableName) }

SELECT 27:36

We are providing an empty array of columns to Select . It turns out that Swift does not provide a way of turning key paths into strings that describe the property they are derived from. At least, not in a way that is usable to us.

SELECT 27:48

Key paths do conform to CustomDebugStringConvertible , so that we can do this: extension Table { static func select(_ columns: PartialKeyPath<Self>...) -> Select { Select(columns: columns.map(\.debugDescription), from: tableName) } }

SELECT 27:59

But if we write a test to assert on the query generated by this: @Test func selectWithKeyPaths() { #expect( Reminder.select(\.id, \.title).queryString == #""" SELECT id, title FROM reminders """# ) }

SELECT 28:31

…we will see that it fails: Expectation failed: (Reminder.select(\.id, \.title).queryString → "SELECT \Reminder.id, \Reminder.title FROM reminders") == (#""" SELECT id, title FROM reminders """## → "SELECT id, title FROM reminders")

SELECT 28:35

This message is really hard to understand. We wish test failures were formatted better in Swift Testing, but unfortunately they are often even worse than they were with XCTest.

SELECT 29:13

Luckily we have a variety of tools that provide better test failure messages. This includes Custom Dump, which provides a nice line-by-line diff output for two values when they aren’t equal.

SELECT 29:41

But also, we are manually asserting against these text blobs, over and over, and we have a testing tool which aids that, called “inline snapshot testing,” which even makes it easy to automatically re-record all these blobs of text should they change in the future.

SELECT 30:02

We just need to add the dependency to our Package.swift: dependencies: [ .package( url: "https://github.com/pointfreeco/swift-snapshot-testing", from: "1.0.0" ), ],

SELECT 30:22

…and depend on it in our test target: .testTarget( name: "QueryBuilderTests", dependencies: [ "QueryBuilder", .product( name: "InlineSnapshotTesting", package: "swift-snapshot-testing" ) ] )

SELECT 30:36

With that we can import the library: import InlineSnapshotTesting

SELECT 30:50

With that we can swap out the #expect macro for our assertInlineSnapshot function, with a string and a strategy for snapshotting it: assertInlineSnapshot( of: Reminder.select(\.id, \.title).queryString, as: .lines )

SELECT 31:18

And if we run this it will automatically record the result inline into the file: assertInlineSnapshot( of: Reminder.select(\.id, \.title).queryString, as: .lines ) { #""" SELECT \Reminder.id, \Reminder.title FROM reminders """# } And now the issue is very obvious.

SELECT 32:00

Before updating all of our tests, we can make this a bit nicer. Rather than repeat .queryString every single time, we can create a custom snapshot strategy that allows us to pass a Select value directly by wrapping the lines strategy. extension Snapshotting where Value == Select, Format == String { static var sql: Self { Snapshotting<String, String>.lines.pullback(\.queryString) } }

SELECT 31:18

Which simplifies the call site to: assertInlineSnapshot( of: Reminder.select(\.id, \.title), as: .sql ) { #""" SELECT \Reminder.id, \Reminder.title FROM reminders """# } But this is still not the valid SQL we want to generate.

SELECT 33:36

It seems that the debugDescription of key paths includes the backslash and the name of the type. We don’t want any of that, but we also shouldn’t try stripping that information from the string. We shouldn’t depend on what debugDescription returns as it could change someday in the future, or even not be available, such as in builds of Swift with reflection disabled.

SELECT 34:18

These two problems are big enough to cause us to look for an alternative way of describing the columns of our types. First of all, the fact that key path specified like this: static func select(_ columns: PartialKeyPath<Self>...) -> Select {

SELECT 34:34

…allows us to use computed properties in our query is the nail in the coffin when it comes to using Self as the base of the key path. We need to introduce a whole new type to represent the base of the key path, and that will make it impossible to use computed properties from the table type when selecting columns.

SELECT 34:50

We could even call this type Columns : static func select(_ columns: PartialKeyPath<Columns>...) -> Select {

SELECT 34:58

…and it would become a new requirement of the Table protocol: protocol Table { associatedtype Columns … }

SELECT 35:05

It would then be the responsibility of conforming types to introduce a Columns type from which key paths could be plucked off of. For example, the Reminder type isn’t currently compiling, but the simple introduction of an inner Columns type: struct Reminder: Table { … struct Columns {} }

SELECT 35:35

…immediately satisfies the compiler.

SELECT 35:39

However, our test that tries to select columns based on their key path no longer works: Reminder.select(\.id, \.title, \.titleIsLong) Cannot infer key path type from context; consider explicitly specifying a root type These key paths must be derivable from the Columns type, not the Reminder type, and currently our Columns type does not have any properties, and hence no key paths.

SELECT 35:50

So, let’s add some properties to it, one for each column: struct Columns { let id let title let isCompleted let priority }

SELECT 36:21

But what should the types of these properties be? Currently we just need a key path, and it’s even a partial key path which means the values have been erased. So, perhaps the types don’t even matter: struct Columns { let id: Void let title: Void let isCompleted: Void let priority: Void }

SELECT 36:46

Seems strange, but that does nearly get our select compiling: Reminder.select(\.id, \.title, \.titleIsLong)

SELECT 36:51

The only thing not compiling in this expression is the \.titleIsLong key path, which is exactly the kind of thing we want to prevent from compiling. So, if we remove it: Reminder.select(\.id, \.title).queryString, …it now compiles. So that solves one of the issues we were having, that of being about to select columns that weren’t actually columns in the table.

SELECT 37:12

But if we run the test it fails. Issue recorded: Snapshot did not match. Difference: … @@ −1,2 +1,2 @@ −SELECT \Reminder.id, \Reminder.title +SELECT \Columns.id, \Columns.title FROM reminders

SELECT 37:18

…where different key paths are being printed out, which still isn’t right. This is because we are still using the debugDescription of the key path to build the query: Select(columns: columns.map(\.debugDescription), from: tableName)

SELECT 37:28

We still need some way to get the name of the property from the key path, and we cannot extract this information from the key path directly. But now that we are maintaining this whole new Columns type we are free to add more info to it that could help us.

SELECT 37:49

So, maybe using Void for the properties of the Columns type wasn’t right. Maybe we should hold onto the names of the columns instead: struct Columns { let id = "id" let title = "title" let isCompleted = "isCompleted" let priority = "priority" }

SELECT 38:01

And then the select method could take a variadic list of key paths into strings instead of just partial key paths: static func select(_ columns: KeyPath<Columns, String>...) -> Select {

SELECT 38:14

These key paths can be used to extract the name of the column from a Columns value, but how do we get a hold of one of these Columns ?

SELECT 38:35

Well, we could make it another requirement of the protocol: protocol Table { … static var columns: Columns { get } } This will make it so that each conformance of Table needs to be able to provide a concrete instance of Columns , which we can then use to extract the name of a column using the key path: static func select(_ columns: KeyPath<Columns, String>...) -> Select { Select( columns: columns.map { Self.columns[keyPath: $0] } from: tableName ) }

SELECT 39:00

The library is building, but the tests are not because we need to provide this new requirement in our Reminder type. This is straightforward to do since the Columns type has all of its data already initialized for us: struct Reminder: Table { static let columns = Columns() … }

SELECT 39:12

And now the test fails but with output we expect: Issue recorded: Snapshot did not match. Difference: … @@ −1,2 +1,2 @@ −SELECT \Reminder.id, \Reminder.title +SELECT id, title FROM reminders

SELECT 39:55

To get the test passing, we could delete the existing inline snapshot so that it gets re-recorded, we could update the snapshot manually, or we could temporarily use a test trait that ships with the library to re-record all snapshots in a test: @Test(.snapshots(record: .all)) func selectWithKeyPaths() {

SELECT 40:25

Or even better, we could use the failed record mode to automatically record snapshots that don’t match, like the one we have currently. @Test(.snapshots(record: .failed)) func selectWithKeyPaths() {

SELECT 40:44

And this generates the exact snapshot we want: assertInlineSnapshot( of: Reminder.select(\.id, \.title), as: .sql ) { #""" SELECT id, title FROM reminders """# }

SELECT 41:04

And we can apply this test trait to our entire suite if we want this behavior by default: @Suite(.snapshots(record: .failed)) struct QueryBuilderTests { But we should update all our other tests to use inline snapshots.

SELECT 42:10

We’ve made a lot of progress, but there is of course quite a bit of boilerplate we had to write to make this syntax work: struct Reminder: Table { … static let tableName = "reminders" struct Columns { let id = "id" let title = "title" let isCompleted = "isCompleted" let priority = "priority" } static let columns = Columns() }

SELECT 42:26

And we would need to keep this boilerplate up-to-date if we made changes to this type, such as adding a new column. And every time we add a new table to the database we will also have to write all of the code again.

SELECT 42:53

Well, this is precisely the kind of problems that macros were made to solve. Imagine if we had a @Table macro that could generate all of this for us: @Table struct Reminder { let id: Int var title = "" var isCompleted = false var priority: Int? var titleIsLong: Bool { title.count >= 100 } } // Macro generates: extension Reminder: Table { static let tableName = "reminders" struct Columns { let id = "id" let title = "title" let isCompleted = "isCompleted" let priority = "priority" } static let columns = Columns() }

SELECT 43:32

That would be amazing, and it is totally possible, but we won’t be doing it right now. We’ve done a number of episodes on macros in the past, and if you want to learn more about them you can watch those. For now we will just write this code for ourselves manually, while knowing it could be generated by a macro with a little bit more work. Advanced selects

SELECT 43:59

We now have a statically checked, type-safe way to select columns from a table in Swift. We simply use the select static method on our table type, then provide a variadic list of key paths of the columns we want to select, and we will magically get a valid SQL statement written for us that can query the database. Stephen

SELECT 44:18

However, we can make things so, so much better. While the form of select we have built so far looks nice, and it’s how most SQL libraries out there handle things, but it unfortunately makes a lot of SQL functionality impossible to express. It turns out that

SELECT 44:30

Let’s see just how complex