While the preceding chapters of this book have looked at data storage within the context of iOS-based apps, this coverage has been limited to basic file and directory handling. However, in many instances, the most effective data storage and retrieval strategy require using some form of database management system.
To address this need, the iOS SDK includes everything necessary to integrate SQLite-based databases into iOS apps. Therefore, this chapter aims to provide an overview of how to use SQLite to perform basic database operations within your iOS app. Once the basics have been covered, the next chapter (An Example SQLite-based iOS 17 App using Swift and FMDB) will work through creating an actual app that uses an SQLite database to store and retrieve data.
What is SQLite?
SQLite is an embedded, relational database management system (RDBMS). Most relational databases (Oracle and MySQL being prime examples) are standalone server processes that run independently and cooperate with apps requiring database access. SQLite is referred to as embedded because it is provided as a library linked to apps. As such, there is no standalone database server running in the background. Instead, all database operations are handled internally within the app through calls to functions contained in the SQLite library.
The developers of SQLite have placed the technology into the public domain with the result that it is now a widely deployed database solution.
SQLite is written in the C programming language; therefore, using SQLite from within Swift code either requires some complex handling of C function calls, data types and pointers or the more straightforward approach of using an existing SQLite wrapper as a layer between SQLite and Swift. In this chapter, we will look at one such wrapper, FMDB. For additional information about SQLite, refer to https://www.sqlite.org.
Structured Query Language (SQL)
Data is accessed in SQLite databases using a high-level language known as Structured Query Language. This is usually abbreviated to SQL and pronounced sequel. SQL is a standard language used by most relational database management systems. SQLite conforms mainly to the SQL-92 standard.
While some basic SQL statements will be used within this chapter, a detailed overview of SQL is beyond the scope of this book. However, many other resources provide a far better overview of SQL than we could ever hope to provide in a single chapter here.
Trying SQLite on macOS
For readers unfamiliar with databases and SQLite, diving right into creating an iOS app that uses SQLite may seem a little intimidating. Fortunately, macOS is shipped with SQLite pre-installed, including an interactive environment for issuing SQL commands from within a Terminal window. This is a helpful way to learn about SQLite and SQL and an invaluable tool for identifying problems with databases created by apps in the iOS simulator.
To launch an interactive SQLite session, open a Terminal window on your macOS system, change directory to a suitable location, and run the following command:
sqlite3 ./mydatabase.db SQLite version 3.6.12 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite>
At the sqlite> prompt, commands may be entered to perform tasks such as creating tables and inserting and retrieving data. For example, to create a new table in our database with fields to hold ID, name, address, and phone number fields, the following statement is required:
create table contacts (id integer primary key autoincrement, name text, address text, phone text);
Note that each row in a table must have a primary key that is unique to that row. In the above example, we have designated the ID field as the primary key, declared it as being of type integer, and asked SQLite to increment the number each time a row is added automatically. This is a common way to ensure that each row has a unique primary key. The remaining fields are each declared as being of type text.
To list the tables in the currently selected database, use the .tables statement:
sqlite> .tables contacts
To insert records into the table:
sqlite> insert into contacts (name, address, phone) values ("Bill Smith", "123 Main Street, California", "123-555-2323"); sqlite> insert into contacts (name, address, phone) values ("Mike Parks", "10 Upping Street, Idaho", "444-444-1212");
To retrieve all rows from a table:
sqlite> select * from contacts; 1|Bill Smith|123 Main Street, California|123-555-2323 2|Mike Parks|10 Upping Street, Idaho|444-444-1212
To extract a row that meets specific criteria:
sqlite> select * from contacts where name="Mike Parks"; 2|Mike Parks|10 Upping Street, Idaho|444-444-1212
To exit from the sqlite3 interactive environment:
sqlite> .exit
When running an iOS app in the iOS Simulator environment, any database files will be created on the file system of the computer on which the simulator is running. This has the advantage that you can navigate to the location of the database file, load it into the sqlite3 interactive tool and perform tasks on the data to identify possible problems in the app code. If, for example, an app creates a database file named contacts.db in its documents directory, the file will be located on the host system in the following folder:
/Users/<user>/Library/Developer/CoreSimulator/Devices/<simulator id>/data/Containers/Data/Application/<id>/Documents
Where <user> is the login name of the user logged into the macOS system, <simulator id> is the id of the, and <id> is the unique ID of the app.
Preparing an iOS App Project for SQLite Integration
By default, the Xcode environment does not assume that you will include SQLite in your app. When developing SQLite-based apps, a few additional steps are required to ensure the code will compile when the app is built. First, the project needs to be configured to include the libsqlite3.tbd dynamic library during the link phase of the build process. To achieve this, select the target entry in the Xcode project navigator (the top entry with the product name) to display the summary information. Next, select the Build Phases tab to display the build information.
The Link Binary with Libraries section lists the libraries and frameworks already included in the project. To add another library or framework, click the ‘+’ button to display the complete list. From this list, select the required item (in this case, libsqlite3.tbd) and click Add.
SQLite, Swift, and Wrappers
As previously discussed, SQLite is written in the C programming language. While it was still possible to use the C-based SQLite API from within Objective-C code with relative ease, this is not the case when programming in Swift without dealing with complex issues when bridging the gap between C and Swift. A standard solution to this dilemma involves using an SQLite “wrapper.” Several wrappers are now available for this purpose, many of which show considerable potential. For this book, however, we will work with the FMDB wrapper. Although this is essentially an Objective-C wrapper, it can be used efficiently within Swift code. FMDB has been chosen for the examples in this book because it has been available for some time, is considered stable and feature-rich, and will be familiar to the many developers who have previously used it with Objective-C. In addition, FMDB is an open-source project released under the terms of the MIT license.
Details on how to obtain FMDB and incorporate it into an iOS Xcode project are covered in detail in the next chapter (An Example SQLite-based iOS 17 App using Swift and FMDB).
Key FMDB Classes
When implementing a database using SQLite with FMDB, utilizing several FMDB classes contained within the wrapper will be necessary. A summary of the most commonly used classes is as follows:
- FMDatabase – Used to represent a single SQLite database. The object on which SQL statements are executed from within code.
- FMResultSet – Used to hold the results of a SQL query operation on an FMDatabase instance.
- FMDatabaseQueue – A version of FMDatabase designed to allow database queries to be performed from multiple threads.
For more detailed information, the FMDB Class Reference documentation is available online at:
http://ccgus.github.io/fmdb/html/Classes/FMDatabase.html
Creating and Opening a Database
Before work can commence on a database, it must first be created and opened. The following code opens the database file at the path specified by <database file path>. If the database file does not already exist, it will be created when the FMDatabase instance is initialized:
let myDatabase = FMDatabase(path: <database file path>) if (myDatabase.open()) { // Database is ready } else { print("Error: \(myDatabase.lastErrorMessage())") }
Creating a Database Table
Database data is organized into tables. Before data can be stored in a database, therefore, a table must first be created. This is achieved using the SQL CREATE TABLE statement. The following code example illustrates the creation of a table named contacts using FMDB:
let sql_stmt = "CREATE TABLE IF NOT EXISTS CONTACTS (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, ADDRESS TEXT, PHONE TEXT)" if !myDatabase.executeStatements(sql_stmt) { // Table creation failed }
Extracting Data from a Database Table
Those familiar with SQL will know that data is retrieved from databases using the SELECT statement. Depending on the criteria defined in the statement, it is typical for more than one data row to be returned. It is essential, therefore, to learn how to retrieve data from a database using the SQLite FMDB wrapper.
In the following code excerpt, a SQL SELECT statement is used to extract the address and phone fields from all the rows of a database table named contacts via a call to the executeQuery method of the FMDatabase instance:
let querySQL = "SELECT address, phone FROM CONTACTS WHERE name = '\(name.text!)'" do { let results:FMResultSet? = try myDatabase.executeQuery(querySQL, values: nil) } catch { print("Error: \(error.localizedDescription)") }
On completion of the query execution, the FMResults object returned from the method call contains the query results. Regardless of whether one or more results are expected, the next method of the returned FMResultSet object must be called. A false return value from the next method call indicates either that no results were returned or that the end of the result set has been reached.
If results were returned, the data can be accessed using the column name as a key. The following code, for example, outputs the “address” and “phone” values for all of the matching records returned as the result of the above query operation:
while results?.next() == true { print(results?.stringForColumn("address")) print(results?.stringForColumn("phone")) }
Closing an SQLite Database
The database must be closed when an app has finished working on the database. This is achieved with a call to the close method of the FMDatabase instance:
myDatabase.close()
Summary
In this chapter, we have looked at the basics of implementing a database within an iOS app using the embedded SQLite relational database management system and the FMDB wrapper to make access to the database possible from within Swift code. In the next chapter, we will put this theory into practice and work through an example that creates a functional iOS app designed to store data in a database.