Using SQLite in Your iOS Apps

This is a meat-and-potatoes introduction to using SQLite in your iOS apps without importing an external framework. This course will teach you why you would do it, and it builds an app in steps.
Course info
Level
Intermediate
Updated
Aug 18, 2017
Duration
1h 14m
Table of contents
Course Overview
Introduction and Sample App
SQLite, SQL, and the Command Line
Using the SQLite API
Adding a New Feature
Using Custom SQLite Functions
Using JSON in SQLite
Safe Multithreading in SQLite
Course Wrapup
Description
Course info
Level
Intermediate
Updated
Aug 18, 2017
Duration
1h 14m
Description

SQLite is a fast, reliable, and flexible embedded SQL database engine with a C API that works on iOS, Android, macOS, and other operating systems. In this course, Using SQLite in Your iOS Apps, you'll learn how to build an app that uses the SQLite engine for data cache and will strive for independence by calling the SQLite API directly from Swift 3. First, you'll explore prototyping SQL queries in the command line. Next, you'll discover how to execute SQL queries with the SQLite API in a custom data cache. Then, you'll learn how to write and use custom SQLite functions. Finally, you'll be working with SQLite in a multithreaded environment. By the end of this course, you'll know how to write your own SQLite caches for your apps and how to use some advanced features of the SQLite engine.

About the author
About the author

Chris is an iOS Developer, adjunct college instructor, PHP developer, former visual perception researcher and 30+ year software developer across multiple platforms from the Mac+ to mainframes.

Section Introduction Transcripts
Section Introduction Transcripts

Course Overview
Hi everyone. My name is Chris Woodard, and I'm a senior iOS developer at Actsoft Inc. Welcome to my course, Using SQLite in your iOS Apps. SQLite is a fast, reliable, and flexible embedded SQL database engine with a CAPI that works on iOS, Android, macOS, and other operating systems. In this course, we'll build an app that uses the SQLite engine for a data cache and we'll strive for independence by calling the SQLite API directly from Swift instead of using a third-party library or system framework. Some of the major topics that we'll cover include prototyping SQL queries in the command line, executing SQL queries with the SQLite API, writing and using custom SQLite functions, and working with SQLite in a multithreaded environment. By the end of this course, you will know how to write your own SQLite caches for your apps and use some advanced features of the SQLite engine. Before beginning this course, you should be familiar with Xcode, Swift 3, and operating in the Terminal command line. I hope you'll join me on this journey to learn SQLite, with the Using SQLite in Your iOS Apps course, at Pluralsight.

Introduction and Sample App
I'm Chris Woodard, and I'm going to show you how to use SQLite, pronounced SQL Lite because spelling it takes too long, for data persistence in your iOS apps. In this course, we're going to build an app that manages to-do list items and in building that we'll see how to design, create, update, and populate the SQLite database directly from Swift. I've been a developer for a long time and I've learned that there is no such thing as a free lunch. The more work a framework does for you, the less direct control you have over its operation and the more time you will eventually spend working around its limitations and requirements. In this course, I'm going to show you how to do it yourself and cut out the middleware. This course will cover designing a SQLite database for your app's data, creating a SQLite database, populating it with data, fetching that data, and updating and deleting that data from the SQLite command line, calling SQLite API functions from Swift, updating the database when you add features to your app, adding custom functions that could be called from within SQLite, adding a more up-to-date SQLite version to your app, safe multithreading with SQLite, and using SQLite's JSON capabilities in your app.

SQLite, SQL, and the Command Line
Welcome to the second module in the course, SQLite, SQL, and the Command Line. In this module, I'll go over the basics of SQL and develop the queries we'll need in future modules. In this module, I'm going to show you how to create the Errands database from the command line. We'll use the sqlite3 command that gets installed with Xcode. Then, from inside the sqlite3 command we will create the Errands table, add some errands, fetch different sets and subsets of errands, and update and delete errands from the Errands table. As I do all this, I'll introduce the queries we need and explain how to use them. We will end up with the SQL statements and the queries we will need to use in later modules. Let's do a recap and introduction into how data is organized in SQLite databases. SQLite databases live in individual files. Within each database file there are one or more named tables. Each table is composed of rows and columns. Each column has a name, a type, and a set of modifiers. The type and modifiers control what kind of data can be entered in each column and how it's treated, and the name lets the column be addressed specifically when updating or fetching data.

Using the SQLite API
Hello, and welcome to module 3, Using the SQLite API. You saw the Errands app design in the first module and you saw how to use SQL to manage data in the second module. In this module, I'm going to show you how to create and execute those same SQL statements from Swift. We're going to write the class that manages errands for the app my team is writing; it'll be called ErrandsCache. I'm going to show you the data structures and function calls that make up the SQLite API, how to use those data structures and function calls and how to interpret their return values and get error message strings, how to use the SQLite API to execute the queries and commands we saw in the previous module to create the Errands table, insert, update, select, and delete rows from the Errands table, and all calling SQLite API from Swift.

Adding a New Feature
Welcome to module 4, Adding a New Feature. So far you've learned how to call the SQLite API from the ErrandsCache singleton to create a database and then add, update, and retrieve errands from that database. In this module, I will cover changing the schema to include a new table to accommodate that feature, joining rows from two tables using nested SELECTS, building an IN operator expression from arrays of strings to test array membership, and adding methods to ErrandsCache to assign tags, filter errands by tags, and retrieve tags to filter with.

Using Custom SQLite Functions
Welcome to module 5, Custom SQLite Functions. In this module, I'll explain how to use SQLite's out-of-the-box functions and I'll show you how to extend SQLite's capabilities by by creating a custom function that can be called from within SQLite queries. This lets your app apply WHERE clause filtering to reduce the amount of data you have to bring in the Swift code in your app, which reduces the amount of memory your app will use. In this module I will cover using functions in SQLite including some of SQLite's available out-of-the-box functions, how to add your own Swift functions that can be called from SQLite queries, and integrating your custom function into the Errands app.

Using JSON in SQLite
Welcome to module 6, Using JSON in SQLite. In this module, I will explain how to use SQLite's JSON1 extensions to let you store JSON text in the ErrandsCache and query it at run time. In this module I will cover using JSON for serializing and deserializing data, SQLite's JSON1 extension and how to enable it and use it, and integrating JSON1 into Errands.

Safe Multithreading in SQLite
Welcome to module 7, Safe Multithreading in SQLite. This module will cover how to safely use SQLite in the multithreaded environment to avoid crashes and hangs in your apps. In this module, I'm going to show you the basics of race conditions, how to mitigate race conditions in Swift, how SQLite works to mitigate those issues, how to configure SQLite for thread safety, and how to work with SQLite to ensure thread safety. Dan tells us that a new feature is on its way, dispatching errands to a user from a network REST API. The app will need to fetch errands and update the errands list even while a user is on an errand. Following best practices, we're going to run the network query on an async background queue, which means an ErrandsCache is going to be accessed from different threads. This means that we will have code on different threads calling methods on ErrandsCache and accessing a shared resource, which means that our app has to pay attention to multithreading.

Course Wrapup
Welcome to the end of course wrap up. Here I'll talk about what we covered. Dan and our customers like what we've done so far, and he's especially happy that the Android team can use our schema and queries in their development. We've got code we can see all the way into, so we can fix or extend it pretty easily. It's clear and easy to read and everything a new team member needs to understand it is right there with no black boxes to have to work around and no extra dependencies to have to update. In this course we've covered how to prototype your SQL queries in the SQLite command line utility, how to invoke those queries from your Swift code using the SQLite API, how to build a transparent data cache that your Swift code can use including algorithms to transfer the Swift dictionary into insert and update queries, one way to combine two database tables in a query, how to add a custom function to SQLite, how to use SQLite's JSON1 extension in your code, and how to handle threading issues in your code in SQLite. Along the way, I've shown you what you need to know to get started using SQLite in your own projects, and the code for the sample app should give you food for thought. I hope I've made SQLite less daunting if you've never used it before, and if you have used it before I hope I've shown you something new. For everyone who took this course, thanks, and see you next time.