Introduction
When developing an iOS app that uses SQLite for local data storage, one of the significant challenges is updating the structure of tables in the database. Unlike Core Data, which provides built-in mechanisms for versioning and schema migration, SQLite lacks a native, straightforward method for altering table structures. This can be particularly problematic when adding new columns, removing obsolete ones, or even renaming columns or changing their types.
In this article, we’ll explore how to safely update SQLite tables using Swift, focusing on a step-by-step approach. This will help you handle schema changes manually. You’ll learn how to migrate your data to new tables, back up important information, and ensure your app remains stable during these updates.
We will not focus on how to build a migration manager here, maybe I can talk about that in another article.
Why Updating SQLite Tables is Challenging
Updating the structure of SQLite tables presents several challenges, especially for developers working with mobile apps. Unlike higher-level database systems or frameworks such as Core Data, SQLite doesn’t natively support easy modifications to table structures. Here’s why it’s tricky:
Risk of Data Loss
When modifying table structures, there’s a constant risk of losing existing data, especially when columns are removed or replaced. Developers need to manually handle the migration of data from the old table structure to the new one, which can introduce bugs if not done carefully.
Handling Schema Versioning
In SQLite, there’s no built-in schema versioning system like Core Data’s lightweight migration. Developers must implement their own versioning strategies, such as keeping track of schema versions in a separate table or using SQLite’s PRAGMA user_version. This adds another layer of complexity to ensure smooth schema updates.
Performance Issues During Migration
Altering the structure of large tables can impact app performance during migration. If the app has a significant amount of data, copying all records into a new table can cause delays, especially on old devices.
Well, that’s enough talking, let’s get down to business!
Step-by-Step Guide to Updating SQLite Tables
Coding the table updater class
Writing the code responsible for updating the table is not actually that difficult. We only need to pay attention in the steps of the process. These steps are:
- We need to use
PRAGMAin SQLite to compare the table information with the last structure to check if it needs to be updated; - We need to create a temporary table with the new desired structure;
- Then we must insert the data of the old table into the new one;
- Drop the old table;
- Rename the temporary table with its correct name.
This is the class responsible for handling this:
final class TableUpdater {
private let dbInstance = DBHelper.shared.connection
private let tableStruct: NewTableStructure
init(tableStruct: NewTableStructure) {
self.tableStruct = tableStruct
}
func updateTable() {
if tableNeedToBeUpdated() {
self
.createTemporaryTable()
.insertDataIntoTemporaryTable()
.dropOldTable()
.renameTemporaryTable()
}
}
func tableNeedToBeUpdated() -> Bool {
return !tableStruct.newColumns.isEmpty
}
func createTemporaryTable() -> Self {
do {
try dbInstance?.run(tableStruct.buildCreateTemporaryTableQuery())
} catch {
print("createTemporaryTable failed \(error.localizedDescription)")
}
return self
}
func insertDataIntoTemporaryTable() -> Self {
do {
try dbInstance?.run(tableStruct.buildInsertDataIntoTableQuery())
} catch {
print("insertDataIntoTemporaryTable failed \(error.localizedDescription)")
}
return self
}
func dropOldTable() -> Self {
let query = "DROP TABLE \"main\".\"\(tableStruct.tableName)\";"
do {
try dbInstance?.run(query)
} catch {
print("dropOldTable failed \(error.localizedDescription)")
}
return self
}
func renameTemporaryTable() {
let query = """
ALTER TABLE \"main\".\"temp_\(tableStruct.tableName)_001\"
RENAME TO \"\(tableStruct.tableName)\";
"""
do {
try dbInstance?.run(query)
} catch {
print("renameTemporaryTable failed \(error.localizedDescription)")
}
}
}
You probably noticed that this class depends on some other classes and structs, so let’s focus on that now.
Creating object to hold new column structure
The first thing we will do is to create an object to hold column data. This object will have some methods to provide query parts and to check if the column already exists in the database.
An important thing I need to mention is that, in this case, I am using a singleton class to connect with the database. So, I am not passing the database instance to this object since I access that globally.
struct Column: Equatable {
let table: String
let name: String
let type: SQLitePrimitiveType
let newColumn: Bool
let primaryKey: Bool
let defaultValue: (any Equatable)?
static func == (lhs: Column, rhs: Column) -> Bool {
lhs.name == rhs.name && lhs.primaryKey == rhs.primaryKey
}
}
As you can see, the attribute type expects a value of type SQLitePrimitiveType, so let’s create it.
enum SQLitePrimitiveType {
case int
case varchar(length: Int)
case intNullable
case varcharNullable(length: Int)
case text
var sqliteParameter: String {
switch self {
case .int:
return "INT"
case .varchar(let length):
return "VARCHAR(\(length))"
case .intNullable:
return "INT"
case .varcharNullable(let length):
return "VARCHAR(\(length))"
case .text:
return "TEXT"
}
}
}
This enum handle the primitive types of SQLite. I will not write all the cases, you can add the other cases if you need to. It also implements an attribute that will return the query part for us.
After creating the object, let’s get back to the Column object and start adding some methods to it.
func getQueryPartForInsert() -> String {
return "\(alreadyExistsInDataBase() ? .init() : type.defaultValue) \"\(name)\""
}
This first method is responsible for generating the query part for when the updater start inserting content in the new table.
func getQueryPartForCreate() -> String {
return "\"\(name)\" \(type.sqliteParameter) \(handleDefaultValue())"
}
And this other method handle the query part for creating the new column.
func handleDefaultValue() -> String {
guard newColumn, let defaultValue = defaultValue else { return String() }
switch type {
case .int:
return "DEFAULT \(defaultValue)"
case .varchar:
return "DEFAULT \"\(defaultValue)\""
case .intNullable:
return "DEFAULT \(defaultValue)"
case .varcharNullable:
return "DEFAULT \"\(defaultValue)\""
case .text:
return "DEFAULT \"\(defaultValue)\""
}
}
And this one is responsible for giving the query part that is related to the default value (you can move this to the SQLitePrimitiveType if you want to, but I am not doing that here).
Finally, we will implement the last method, responsible for checking if the column already exists in the table:
func alreadyExistsInDataBase() -> Bool {
guard newColumn else { return true }
let query = "PRAGMA table_info(\(table));"
var response = false
do {
try DBHelper.shared.connection.prepare(query).forEach { row in
if (row[1] as? String) == "\(name)" && (row[2] as? String) == "\(type.sqliteParameter)" {
response = true
}
}
} catch {
print("alreadyExistsInDataBase failed \(error.localizedDescription)")
}
return response
}
We use the PRAGMA to get the table info, and then we compare with the column name and type, If the table info contains the table name and the type is the same of the new structure, then we return true, because it already exists in the database. I also added an early escape. If the flag new column is false, then we skip the PRAGMA and return true. That happens because we are assuring that the column already exists and its type is still the same.
Creating the Table structure object
Now we need to think about the whole table. To handle the table, we will create a struct that will have as attributes: The table name, an array for the new columns, and another array for all columns.
struct NewTableStructure {
let tableName: String
let newColumns: Array<String>
let columns: Array<Column>
}
And now let’s implement the methods responsible for generating the queries for the table creation and for data insertion.
func buildCreateTemporaryTableQuery() -> String {
var query = "CREATE TABLE \"temp_\(tableName)_001\" ("
columns.forEach { column in
query += "\n\(column.getQueryPartForCreate()),"
}
query += "\n PRIMARY KEY(\"\(getPrimaryKeyColumnName())\") \n );"
return query
}
func buildInsertDataIntoTableQuery() -> String {
var query = "INSERT INTO \"main\".\"temp_\(tableName)_001\" ("
// Adding column names for insert into
columns.forEach { column in
query += "\n \(column.name) \(column == columns.last ? "" : ",")"
}
query += ") \n SELECT"
// Adding column names where the values to be inserted comes from
columns.forEach { column in
query += "\n \(column.getQueryPartForInsert()) \(column == columns.last ? "" : ",")"
}
query += "FROM \"main\".\"\(tableName)\";"
return query
}
Now to finish, let’s create two other methods. One to get the primary key column name, and the other to get the list of new columns (columns that needs to be updated).
func getNewColumns() -> Array<String> {
return newColumns
}
func getPrimaryKeyColumnName() -> String {
guard let columnName = columns.first(where: { $0.primaryKey })?.name else {
preconditionFailure("A primary key column need to be defined")
}
return columnName
}
Creating the table structure builder
The builder is pretty simple, it has the same structure as the object, but without the query methods.
final class NewTableStructureBuilder {
private(set) var tableName: String
private var newColumns: Array<String>
private var columns: Array<Column>
init(tableName: String) {
self.tableName = tableName
self.newColumns = .init()
self.columns = .init()
}
}
Now we will make it conform to the Builder design pattern. For that, let’s implement a method that will help us build this object. This method will create and append columns to the arrays, and it will always return the instance of the NewTableStructure.
@discardableResult
func addColumn(name: String, type: SQLitePrimitiveType, newColumn: Bool, defaultValue: (any Equatable)? = nil, primaryKey: Bool = false) -> Self {
let column = Column(table: tableName, name: name, type: type, newColumn: newColumn, primaryKey: primaryKey, defaultValue: defaultValue)
columns.append(column)
if column.alreadyExistsInDataBase() {
newColumns.append(name)
}
return self
}
The method will always return the class instance, then we can call the method again and again. At some point we will finish adding columns and the compiler will complain about the unused result. Because of that we need to mark the method with @discardableResult. Next, we’ll implement the method that will build the NewTableStructure object.
func build() -> NewTableStruct {
return NewTableStruct(tableName: tableName, newColumns: newColumns, columns: columns)
}
Creating tasks to update the tables
Now that we implemented all the necessary classes and methods to update the tables, we need to create the tasks that will be called to perform this updates. First, let’s create a protocol, so all the tasks will have the same methods.
protocol TableUpdaterTask {
func createTableStruct() -> NewTableStruct
func run()
}
And finally let’s create a task.
final class TablePersonUpdater: TableUpdaterTask {
init() { }
internal static func createTableStruct() -> NewTableStruct {
return NewTableStructBuilder(tableName: "Person")
.addColumn(name: "id", type: .text, newColumn: false, primaryKey: true)
.addColumn(name: "name", type: .int, newColumn: false)
.addColumn(name: "age", type: .int, newColumn: false)
.addColumn(name: "address", type: .varchar(length: 100), newColumn: true)
.build()
}
static func run() {
let table = createTableStruct()
TableUpdater(tableStruct: table).updateTable()
}
}
Handle Column Drop
Is it looking good? I think so! But there is still one piece of code that I forget to implement: Make the updater run when we are just deleting a column. The current code will return true for the tableNeedToBeUpdated() only if the column type is different or if the column is new. We are not checking if we want do delete a column.
We have a gazillion ways to solve this problem, but I am going with a simple solution: comparing the new structure column count with the current structure column count. We already have everything we need to check the new column count, so let’s focus on figure out how many columns do we have in the old structure.
In order to count the old structure table, first I will run the PRAGMA query and then will count the rows with reduce method. Once we have the counting of the columns of the old structure, we need to compare it with the columns.count of the new structure. It is important to keep in mind that you shouldn’t compare old with new columns. The new columns array will only contains columns that were added or edited, but not the ones that will not change.
func tableNeedToBeUpdated() -> Bool {
let query = "PRAGMA table_info(\(tableStruct.tableName));"
let currentColumnCount = (try? dbInstance?.prepare(query).reduce(0) { count, _ in count + 1 }) ?? 0
let newColumnCount = tableStruct.columns.count
let hasNewColumns = !tableStruct.newColumns.isEmpty
let willDeleteColumns = newColumnCount < currentColumnCount
return hasNewColumns || willDeleteColumns
}
Updating the tables
Now that everything is set, we only need to call the run() method of the classes responsible for updating the tables. For example: TablePersonUpdater.run().
Conclusion
Updating SQLite columns with Swift may seem complex at first, but once you understand the process and structure, it’s a powerful tool for managing persistent data in your applications. Whether you’re adding new columns, modifying existing ones, or handling schema changes, the combination of Swift and SQLite offers flexibility and efficiency. By applying these techniques, you can ensure your app remains scalable and responsive to future changes.
While this post covers the essentials, I also plan to guide you through building a migration schema manager to streamline handling these updates. However, to keep this post concise, I’ll save that for another time. Remember, it’s not that hard, and with practice, you’ll master it!
Coding With Renan 