Video #301: SQLite: The C Library

Episode: Video #301 Date: Nov 4, 2024 Access: Members Only 🔒 URL: https://www.pointfree.co/episodes/ep301-sqlite-the-c-library

Description

SQLite is one of the most well-crafted, battle-tested, widely-deployed pieces of software in history, and it’s a great fit for apps with more complex persistence needs than user defaults or a JSON file. Let’s get familiar with the library, starting with a crash course in interacting with C code from Swift.

Video

Cloudflare Stream video ID: e0f1e3f2de11f1075481534913ac4a69 Local file: video_301_sqlite-the-c-library.mp4 *(download with --video 301)*

References

Transcript

— 0:05

Today we are embarking on a series of episodes to show how to persist data in your applications using SQLite. SQLite is one of the most well-crafted and battle-tested pieces of software in the history of software. It’s installed on tens of billions of devices worldwide , and its test suite runs over 500 million tests every day!

— 0:27

However, SQLite is a C library, and C is a language that does not have many of the niceties that we have come to expect from our experience with Swift. There’s a very limited type system, no enums, no generics, and it has pervasive use of unsafe pointers. However, Swift has great interoperability with C and so with a little bit of work we can very easy call into any of SQLite’s functionality. Stephen

— 0:53

In the series we are going to accomplish 3 main goals:

— 0:57

First we are going to give a crash course in calling into C code from Swift, including how one deals with all the unsafe pointers that are necessary when dealing with C code. And along the way we will learn the basics of SQLite, including how to create a database, how to create a table, how to insert data into the table, and how to query the database. Brandon

— 1:18

Then we will show how to use one of the most popular 3rd party libraries for putting a nice Swift interface on top of SQLite, which is

GRDB 1:45

And the finally we will explore some advanced topics in SQLite and GRDB by showing off how to display data from the database in SwiftUI, how to observe changes to the database so that the view updates automatically when data is updated, and more. Brandon

GRDB 1:59

But, let’s start from the very beginning. Let’s see what it takes to interact with the SQLite C library that automatically comes on every iOS and macOS device. SQLite basics

GRDB 2:12

Let’s start with the very basics of SQLite. While it may seem like the promise of libraries and frameworks such as Swift Data and GRDB is that you don’t need to know anything about the underlying storage system, from our experience that has never been the case. Knowing how SQLite works can only help you wield those frameworks better, and so we highly recommend you know the basics.

GRDB 2:33

And to explore this we are going to see what it takes to interact with SQLite directly from Swift, without going through any layers of abstraction whatsoever. I’ve got a fresh project started right here, and we can start by importing SQLite3: import SQLite3

GRDB 2:49

Now don’t let this fancy, modern import statement fool you. This SQLite3 module is not a Swift module whatsoever. It is simply a Swift interface to a C library because SQLite is built entirely in C. We can even command-click SQLite3 to go to the interface file, and we will see the Swifty version of everything SQLite has to offer: public var SQLITE_VERSION: String { get } public var SQLITE_VERSION_NUMBER: Int32 { get } public var SQLITE_SOURCE_ID: String { get } public func sqlite3_libversion() -> UnsafePointer<CChar>! …

GRDB 3:18

This is not the actual C code from SQLite, but rather how Swift sees the C code. Remember that one of the great benefits of Swift is that it interoperates with C, which is what allows us to instantly use SQLite from a Swift app with no additional work.

GRDB 3:33

And because SQLite is written in C, its API does not get to take advantage of any of the powerful tools from Swift, such as generics, enums, closures, or anything. And so when we interact with SQLite from Swift we must go through a bunch of “unsafe” APIs, such as pointers. In fact, if we search for “pointer” in this interface we will find 1,141 matches!

GRDB 4:05

So, just because we are using Swift, it doesn’t mean we somehow magically have a safe way to interface with C libraries. We will be the ones responsible for making sure we interact with SQLite safely, and that’s just the price we pay in order to call into a C library. Creating a database

GRDB 4:21

Now let’s actually interact with SQLite. We can start by trying to open a database, which we can do right in the entry point of the app using the sqlite3_open_v2 function: @main struct SQLitePersistenceApp: App { init() { sqlite3_open_v2( <#filename: UnsafePointer<CChar>!#>, <#ppDb: UnsafeMutablePointer<OpaquePointer?>!#>, <#flags: Int32#>, <#zVfs: UnsafePointer<CChar>!#> ) } … }

GRDB 4:38

And already we something quite stark.

GRDB 4:40

First of all, because all functions in C are global free functions, and because C doesn’t have argument labels or overloaded functions, C APIs need to be very descriptive. That means the names tend to be long, and its common for C API designers to use abbreviations which can look quite cryptic.

GRDB 5:03

And second, calling into a C function basically has no type information and, as we mentioned a moment ago, no argument labels. So it is not clear at all what we are pass to these 4 arguments. In order to figure this out we have no choice but to reference the documentation online. int sqlite3_open_v2( const char *filename, /* Database filename (UTF-8) */ sqlite3 **ppDb, /* OUT: SQLite db handle */ int flags, /* Flags */ const char *zVfs /* Name of VFS module to use */ );

GRDB 6:16

This is helpful, but also still pretty cryptic.

GRDB 6:29

We can start by constructing a path to where we want the SQLite database stored as a Swift String : let databasePath = URL.documentsDirectory.appending(path: "db.sqlite").path()

GRDB 6:49

And this will bridge directly to C as an unsafe pointer when passed as the first argument of sqlite3_open_v2 : sqlite3_open_v2( databasePath, <#ppDb: UnsafeMutablePointer<OpaquePointer?>!#>, <#flags: Int32#>, <#zVfs: UnsafePointer<CChar>!#> )

GRDB 6:53

The next argument is represents the database connection. It is a mutable pointer because the act of calling sqlite3_open_v2 will set up the connection and mutate some of our own state for us, and it’s a mutable pointer to an opaque pointer because the connection itself is represented as an opaque pointer.

GRDB 7:21

So, let’s upfront declare an opaque pointer for the database connection: var db: OpaquePointer?

GRDB 7:27

…and this is what can be fed into the second argument as a mutable pointer using inout syntax: sqlite3_open_v2( databasePath, &db, <#flags: Int32#>, <#zVfs: UnsafePointer<CChar>!#> )

GRDB 7:34

Note that we passed it in using & to bridge the opaque pointer to a mutable pointer that points to it. This allows sqlite3_open_v2 to mutate our value. In particular it will populate it with the connection if a connection is successfully made. Then we will use this db value anytime we want to interact with the database.

GRDB 7:51

The next argument is a set of flags for establishing the connection to the database, but because this is C we don’t get any of the niceties that Swift has, such as option sets. Instead there is a huge number of constants defined in SQLite that we bitwise combine together to form a set of flags: public var SQLITE_OK: Int32 { get } public var SQLITE_ERROR: Int32 { get } public var SQLITE_INTERNAL: Int32 { get } public var SQLITE_PERM: Int32 { get } public var SQLITE_ABORT: Int32 { get } public var SQLITE_BUSY: Int32 { get } public var SQLITE_LOCKED: Int32 { get } public var SQLITE_NOMEM: Int32 { get } public var SQLITE_READONLY: Int32 { get } public var SQLITE_INTERRUPT: Int32 { get } …

GRDB 8:23

The flags we will use are SQLITE_OPEN_READWRITE to represents that we want to open the database for reading and writing, and further SQLITE_OPEN_CREATE to represent that if the database does not exist we want to create it: sqlite3_open_v2( databasePath, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, <#zVfs: UnsafePointer<CChar>!#> )

GRDB 8:44

And finally, the last argument has to do with “VFS”, or “virtual file system”, which is a foundational module in SQLite that makes it portable across many operating systems. We don’t need to worry about that and so we will use nil : sqlite3_open_v2( databasePath, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nil )

GRDB 8:59

We have filled in all the parameters of this function, but we’re not done yet. Because this is C, there is no concept of throwing functions in order to represent something went wrong. Instead, this function returns a kind of status code that must be checked to see if everything was OK.

GRDB 9:21

Of course, it’s on us to write this code, and the compiler can’t force us to handle errors like Swift can do with throwing functions. And so we will guard that this function returns a status of OK before moving on: guard sqlite3_open_v2( databasePath, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nil ) == SQLITE_OK else { fatalError("Could not open database at \(databasePath)") }

GRDB 9:57

Phew! That took quite a bit to do something that should be quite easy. But, if we run the app, we will see that we did not get caught in the fatalError , and so that must mean the database was created.

GRDB 10:21

In fact, we can print the location of the database file: print("open", databasePath) …to see that it is located here: open …/Containers/Data/Application/…/Documents/db.sqlite

GRDB 10:36

…and we can open this file to see that indeed there is a SQLite database on disk. Creating a table

GRDB 11:51

We have now gotten our feet wet with SQLite. We see what it takes to open a connection to a database, and so far it isn’t too pretty. We needed to use pointers, and even pointers to pointers, and we had to figure out how to use cryptic looking C functions. Stephen

GRDB 12:06

Next let’s create a table in the SQLite database. Suppose we want a players table that holds onto the name and creation date for some players in a game. The way one does is to create a SQL statement, which is a language all on its own, and is represented as a raw string that is sent to the database client to be processed.

GRDB 12:25

Let’s take a look.

GRDB 12:29

The SQL statement we need to use is a CREATE TABLE statement: """ CREATE TABLE IF NOT EXISTS "players" ( ) """

GRDB 12:41

We are using IF NOT EXISTS so that this query does not error if this table already exists, which is handy for being able to run this query every time the app starts up.

GRDB 12:53

Then we list out all the columns we want this table to have, as well as its data type and some extra properties. For example, the name and created date: "name" TEXT NOT NULL, "createdAt" DATETIME NOT NULL

GRDB 13:19

Most tables will also need a primary key so that there is a unique identifier for referencing a particular player in the table: "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,

GRDB 13:43

That’s the entirety of the SQL statement. But now the question is how do we execute it in SQLite?

GRDB 13:48

Well, there’s another C function that can be used called sqlite3_exec : sqlite3_exec( <#OpaquePointer!#>, <#sql: UnsafePointer<CChar>!#>, <#callback: ((UnsafeMutableRawPointer?, Int32, UnsafeMutablePointer<UnsafeMutablePointer<CChar>?>?, UnsafeMutablePointer<UnsafeMutablePointer<CChar>?>?) -> Int32)!##(UnsafeMutableRawPointer?, Int32, UnsafeMutablePointer<UnsafeMutablePointer<CChar>?>?, UnsafeMutablePointer<UnsafeMutablePointer<CChar>?>?) -> Int32#>, <#UnsafeMutableRawPointer!#>, <#errmsg: UnsafeMutablePointer<UnsafeMutablePointer<CChar>?>!#> )

GRDB 13:56

This function is even more complicated than the other one. We can again look at the docs to see what all these arguments represent:

GRDB 13:59

The first argument is the opened database, which is the opaque pointer we created a moment ago: sqlite3_exec( db, <#UnsafePointer<CChar>!#>, <#((UnsafeMutableRawPointer?, Int32, UnsafeMutablePointer<UnsafeMutablePointer<CChar>?>?, UnsafeMutablePointer<UnsafeMutablePointer<CChar>?>?) -> Int32)!##(UnsafeMutableRawPointer?, Int32, UnsafeMutablePointer<UnsafeMutablePointer<CChar>?>?, UnsafeMutablePointer<UnsafeMutablePointer<CChar>?>?) -> Int32#>, <#UnsafeMutableRawPointer!#>, <#UnsafeMutablePointer<UnsafeMutablePointer<CChar>?>!#> )

GRDB 14:06

The second argument is the SQL statement we want to execute, which is that multiline string we just created: sqlite3_exec( db, """ CREATE TABLE IF NOT EXISTS "players" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, "name" TEXT NOT NULL, "createdAt" DATETIME NOT NULL ) """, <#((UnsafeMutableRawPointer?, Int32, UnsafeMutablePointer<UnsafeMutablePointer<CChar>?>?, UnsafeMutablePointer<UnsafeMutablePointer<CChar>?>?) -> Int32)!##(UnsafeMutableRawPointer?, Int32, UnsafeMutablePointer<UnsafeMutablePointer<CChar>?>?, UnsafeMutablePointer<UnsafeMutablePointer<CChar>?>?) -> Int32#>, <#UnsafeMutableRawPointer!#>, <#UnsafeMutablePointer<UnsafeMutablePointer<CChar>?>!#> )

GRDB 14:19

And the last 3 arguments are not important for us, and so we can use nil : sqlite3_exec( db, """ CREATE TABLE IF NOT EXISTS "players" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, "name" TEXT NOT NULL, "createdAt" DATETIME NOT NULL ) """, nil, nil, nil )

GRDB 14:35

And we should probably do another guard here to check that the status returned from this function is also OK, but instead let’s just print it to the console: print( "Create table", sqlite3_exec( db, """ CREATE TABLE IF NOT EXISTS "players" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, "name" TEXT NOT NULL, "createdAt" DATETIME NOT NULL ) """, nil, nil, nil ) == SQLITE_OK )

GRDB 14:59

And now when we run the app again we will see the following printed to the console: Create table true

GRDB 15:08

…and if we refresh our database app we will see that our SQLite database now holds onto a single table with 3 columns. Inserting data into a table

GRDB 15:26

So, we are making progress in adding a SQLite database to our app. It’s pretty difficult since we need to interact with a C library, but it’s not so bad once you get used to it. Brandon

GRDB 15:36

Next let’s see what it takes to actually insert some data into the database. This brings new complications because we have to figure out how to bind data to the SQL statement being executed, and that can open you up to potential security problems.

GRDB 15:52

Let’s take a look.

GRDB 15:54

First let’s focus just on the SQL statement that we want to execute to accomplish this. In SQL, one inserts rows into a table using an INSERT INTO statement and specifying the table you want to insert into: """ INSERT INTO "players" """

GRDB 16:11

Then you specify the names of the columns for which you have data you will be providing: ("name", "createdAt") Note that it is not necessary to provide every column of the table because some columns have default values, such as the id column which is assigned an auto-incrementing integer value when a row is inserted.

GRDB 16:21

Next you specify

VALUES 17:17

And technically we can just execute this statement just as we did the CREATE TABLE : print( "Insert Blob", sqlite3_exec( db, """ INSERT INTO "players" ("name", "createdAt")

VALUES 17:45

When we run this we will see the following printed to the console: Insert Blob true

VALUES 18:03

…and we can check the database to see that indeed a row was added to the table.

VALUES 18:17

However, this is not how you should ever insert data into a table. Putting the data directly in the query leaves you open to what is known as “SQL injection” attacks. This is where someone from the outside can craft malicious data that is submitted to your server, and if you naively interpolate that data into a SQL statement you can inadvertently execute any statement the attacker wants.

VALUES 18:45

For example, typically you would interpolate the data into a SQL statement since the data usually comes from the outside: let name = "Blob" let createdAt = Date() print( "insert blob", sqlite3_exec( db, """ INSERT INTO "players" ("name", "createdAt")

VALUES 19:07

Currently this query works exactly as it did a moment ago. If we ran this, another player would be inserted into the players table.

VALUES 19:14

However, the input string is coming from the outside, and the user of our app can put any text they want. For example, they could enter a name of a quotation mark: let name = "Blob \"Blobby\" McBlob" We should of course allow this if this is how our player wants to be referred to, but unfortunately it breaks the query.

VALUES 19:40

If we run the app we will see the following printed to the console: near “Blobby”: syntax error in INSERT INTO "players" ("name", "createdAt")

VALUES 20:05

But the user can do something much more nefarious. If they provide a string that completes the SQL statement we are trying to perform, they can then execute any statement that they want, including dropping the entire table: let name = "\", 0); DROP TABLE \"players\"; --"

VALUES 20:57

When we run this we will see it “succeeded”: Insert Blob true

VALUES 21:03

…but if we check the database our “players” table is completely missing. That seems really bad!

VALUES 21:19

And so in general one should never interpolate values into a string in order to form a SQL statement. That is why SQLite provides what is known as “prepared statements”, and has the following C function: sqlite3_prepare_v2( <#db: OpaquePointer!#>, <#zSql: UnsafePointer<CChar>!#>, <#nByte: Int32#>, <#ppStmt: UnsafeMutablePointer<OpaquePointer?>!#>, <#pzTail: UnsafeMutablePointer<UnsafePointer<CChar>?>!#> )

VALUES 21:46

The first argument is the database we want to use to execute this statement: sqlite3_prepare_v2( db, <#zSql: UnsafePointer<CChar>!#>, <#nByte: Int32#>, <#ppStmt: UnsafeMutablePointer<OpaquePointer?>!#>, <#pzTail: UnsafeMutablePointer<UnsafePointer<CChar>?>!#> )

VALUES 21:50

The second argument is the SQL statement we want to execute, but this time we use question marks “?” for the data that we want to insert into the query rather than performing string interpolation: sqlite3_prepare_v2( db, """ INSERT INTO "players" ("name", "createdAt")

VALUES 22:04

The third argument is the number of bytes in the SQL statement we are providing. We can supply -1 if we are providing a NULL-terminated string, which we are because that is how Swift strings are bridged to C: sqlite3_prepare_v2( db, """ INSERT INTO "players" ("name", "createdAt")

VALUES 22:28

The fourth argument is another one of those “pointers to a pointer” like we dealt with when establishing a connection to our database. This “pointer to a pointer” represents the prepared statement we are creating, and we need to get a handle to it after invoking this function because there is additional work that we need to perform with it: var statement: OpaquePointer? sqlite3_prepare_v2( db, """ INSERT INTO "players" ("name", "createdAt")

VALUES 23:12

And the final argument is not important for us so we can return nil : var statement: OpaquePointer? sqlite3_prepare_v2( db, """ INSERT INTO "players" ("name", "createdAt")

VALUES 23:17

OK, all of that now creates what is known as a “prepared statement” that is capable of binding two pieces of data to it so that we can execute it. To bind data to a statement we use yet another C function: sqlite3_bind_

VALUES 23:30

And actually it’s a whole family of functions. Since C doesn’t have generics, there is a C function defined for each kind of data type you can bind to this query.

VALUES 23:40

The first piece of data we want to bind is a string, and so we can use the following C function: sqlite3_bind_text( <#OpaquePointer!#>, <#Int32#>, <#UnsafePointer<CChar>!#>, <#Int32#>, <#(UnsafeMutableRawPointer?) -> Void#> ) And we can refer to the documentation to figure out what all of these arguments represent: int sqlite3_bind_text( sqlite3_stmt*, int, const char*, int, void(*)(void*) );

VALUES 23:45

The first argument is the statement we are binding the text to, which is the opaque pointer we created just a moment ago: sqlite3_bind_text( statement, <#Int32#>, <#UnsafePointer<CChar>!#>, <#Int32#>, <#(UnsafeMutableRawPointer?) -> Void#> )

VALUES 23:49

The second argument is the position of the value that we are binding to, and this positional value starts at 1, not at 0: sqlite3_bind_text( statement, 1, <#UnsafePointer<CChar>!#>, <#Int32#>, <#(UnsafeMutableRawPointer?) -> Void#> )

VALUES 23:58

The third argument is the data you want to bind, in this case it’s the name of the player we want to insert: sqlite3_bind_text( statement, 1, name, <#Int32#>, <#(UnsafeMutableRawPointer?) -> Void#> )

VALUES 24:10

The fourth argument is related to how many bytes are in the data we are binding, and we can provide a value of -1 to represent that the string can be read until its NULL terminator: sqlite3_bind_text( statement, 1, name, -1, <#(UnsafeMutableRawPointer?) -> Void#> )

VALUES 24:18

And finally, the last argument deals with how memory ownership is handled. It represents a destructor to be used, and there are some constants provided by SQLite that are provided that handle the most common cases. In particular, if we would like the data to be copied by SQLite so that it is responsible for managing that piece of memory, then we can pass a constant known as SQLITE_TRANSIENT , which the docs mention: Note The constant, SQLITE_TRANSIENT , may be passed to indicate that the object is to be copied prior to the return from sqlite3_bind_*() . The object and pointer to it must remain valid until then. SQLite will then manage the lifetime of its private copy.

VALUES 24:49

However, the SQLITE_TRANSIENT constant is not visible to Swift from SQLite unfortunately, but we can look up how it is defined: #define SQLITE_TRANSIENT ((sqlite3_destructor_type)-1)

VALUES 24:57

That shows it is just the integer -1 cast to the type sqlite3_destructor_type . This means we can define our own version of SQLITE_TRANSIENT : let SQLITE_TRANSIENT = unsafeBitCast( -1, to: sqlite3_destructor_type.self )

VALUES 25:25

It does require performing an unsafe bit-cast, and this is probably why this symbol is not visible to Swift by default. It is completely safe to do for the current version of SQLite, but any time SQLite updates its version we might want to check that this cast still works.

VALUES 25:51

And with that constant defined we can finish binding our first bit of data to the SQL statement we are building: sqlite3_bind_text( statement, 1, "Blob", -1, SQLITE_TRANSIENT )

VALUES 25:59

And we can also bind an integer for the createdAt field: sqlite3_bind_int64( statement, 2, Int64(createdAt.timeIntervalSince1970) )

VALUES 26:43

Further, each of these invocations returns a SQLite status code, which we could check each step of the way to make sure everything is kosher.

VALUES 26:49

For now, we will just print that the status is OK: print( "Binding name", sqlite3_bind_text( statement, 1, name, -1, SQLITE_TRANSIENT ) == SQLITE_OK ) print( "Binding createdAt", sqlite3_bind_double( statement, 2, Date().timeIntervalSince1970 ) == SQLITE_OK )

VALUES 27:05

…but in a real world app you would of course want to do some error handling here.

VALUES 27:10

We have now bound all the data to our statement, and we are ready to execute it. The way one does this is by using the sqlite3_step method: sqlite3_step(<#OpaquePointer!#>)

VALUES 27:26

It takes one argument, which is the statement we just prepared: sqlite3_step(statement)

VALUES 27:32

This function is called “step” because sometimes you can execute it multiple times, such as if you are querying the database and a collection of rows is returned to you. When the steps are finished, the function will return SQLITE_DONE , and since we are just inserting a row and not retrieving a bunch of rows, we expect our statement to be done after the first step: print( "Insert Blob", sqlite3_step(statement) == SQLITE_DONE )

VALUES 28:08

And finally, we need to finalize the statement in order to prevent memory leaks: print( "Finalize insert", sqlite3_finalize(statement) == SQLITE_OK )

VALUES 28:29

That’s everything that needs to be done to insert a row. It’s quite a bit, but if we run this we will see the following logs: Binding name true Binding createdAt true Insert Blob true Finalize insert true

VALUES 28:55

…which shows that everything successfully executed. And if we check our database we will see that we do indeed have a new row in the players table. Querying the database

VALUES 29:44

OK, we now know how to connect to a database, create a table in the database, and insert data into the table. There is only one major task one does with databases that we have not yet covered, and that’s querying the database for information. Stephen

VALUES 29:58

SQLite supports some very powerful tools for querying, including joins, aggregations, subqueries, and a lot more. With a well-designed database that makes good use of indices, which is something we will not be discussing in this series, one can search through hundreds of thousands, and even millions, of rows in mere milliseconds.

VALUES 30:17

Let’s see what it takes to execute a query.

VALUES 30:22

We will fetch all players that have been registered in the past 10 minutes. To execute this query we will need a prepared statement since we will be interpolating data into the query: sqlite3_prepare_v2( And to do that we need a statement again, which we can reused our previous pointer: statement = nil

VALUES 30:45

And now we can prepare the statement: sqlite3_prepare_v2( db, """ SELECT * FROM "players" WHERE "createdAt" > ? """, -1, &statement, nil )

VALUES 31:24

Note that technically we don’t need to interpolate a value in for this ? because we could have used date functions in SQLite to compute the date for 10 minutes ago, but let’s do it this way so that we can show off binding data again: sqlite3_bind_int64( statement, 1, Int64(Date().addingTimeInterval(-10*60).timeIntervalSince1970) )

VALUES 32:07

Next we want to step through the statement in order to fetch data, but unlike last time we expect to retrieve multiple rows. We do this by performing a while loop until sqlite3_step returns something other than SQLITE_ROW : while sqlite3_step(statement) == SQLITE_ROW { }

VALUES 32:36

Then, within this while loop we can use a new family of functions, sqlite_column_* , in order to retrieve data from the row. For example, the ID of the player: let id = Int(sqlite3_column_int64(statement, 0))

VALUES 32:54

…and the name of the player: let name = String(cString: sqlite3_column_text(statement, 1))

VALUES 33:29

…and finally their registration date: let createdAt = Date( timeIntervalSince1970: Double(sqlite3_column_int64(statement, 2)) )

VALUES 33:54

Finally let’s introduce a data type to bundle up all this information: struct Player { let id: Int64 let name: String let createdAt: Date } let player = Player(id: id, name: name, createdAt: createdAt)

VALUES 34:15

And let’s print it to the console: print(player)

VALUES 34:17

And then after the while loop we need to make sure to finalize the statement: sqlite3_finalize(statement) …so that we do not accidentally leak resources.

VALUES 34:25

And in a real world situation we should be checking the status codes returned from all these functions, but we’ll skip that for now.

VALUES 34:30

That is all it takes, and if we run the app again we will see some players were fetched: Player(id: 1, name: Blob, createdAt: 2024-11-04 14:06:33 +0000) Player(id: 2, name: Blob, createdAt: 2024-11-04 14:06:41 +0000)

VALUES 34:48

And if we tweak the time a bit we will see that we fetch fewer players: sqlite3_bind_Int64( statement, 1, Int64(Date().addingTimeInterval(-1*60).timeIntervalSince1970) )

VALUES 34:58

Now this only prints a single player to the console: Player(id: 2, name: Blob, createdAt: 2024-11-04 14:06:41 +0000) Next time: GRDB

VALUES 35:01

And so that is the basics of interacting with the SQLite C library from Swift. We now know how to create a database connection, create tables, insert data into tables, and query tables for data. And really this was also a lesson in how one interacts with C libraries from Swift, in general. Long story short: it ain’t pretty. But it is also amazing that it is possible at all. Brandon

VALUES 35:23

And because of the pain we witnessed when trying to interact with a C library, it’s no surprise that many people have tried to put a friendlier and “Swift-ier” interface over SQLite. There are a number of libraries out there, but no doubt the most popular is GRDB by Gwendal Roué. It’s a very well-maintained project, it’s been around for a long time, and we don’t think anyone should have qualms about adding it as a dependency if you need to interact with SQLite.

VALUES 35:54

So, let’s import GRDB into our project and see what it takes to create tables, insert data into tables and query tables. And we will also show off a couple of advanced features too, such as subscriptions and relations…next time! References SQLite The SQLite home page https://www.sqlite.org Most Widely Deployed and Used Database Engine Note SQLite is likely used more than all other database engines combined. Billions and billions of copies of SQLite exist in the wild. https://www.sqlite.org/mostdeployed.html How SQLite Is Tested Note As of version 3.42.0 (2023-05-16), the SQLite library consists of approximately 155.8 KSLOC of C code. (KSLOC means thousands of “Source Lines Of Code” or, in other words, lines of code excluding blank lines and comments.) By comparison, the project has 590 times as much test code and test scripts - 92053.1 KSLOC. https://www.sqlite.org/testing.html Downloads Sample code 0301-sqlite-pt1 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 .