SQLite debugging with Gwendal Roué

The crash

A surprising crash was reported in our app. Some users would write a number in a text field, for instance "7". We expected the field to be saved as a string in the database, and read back as a string, but this is not what was happening.

I wrote a unit test to reproduce the bug. The Product record is saved to the database and read back:

    func testStoresQuantityUnitAsString() {
        modelDatabase.dbQueue.inDatabase { (db) in
            let product = PFProduct()
            product.uuid = "TEST"
            product.quantityUnit = "7" // This is a string
            try! product.insert(in: db)
            
            let reloaded = PFProduct.load(withPrimaryKey: product.uuid, from: db)
            XCTAssertEqual(reloaded?.quantityUnit, "7")
        }
    }

XCTAssertEqual failed: throwing "-[__NSCFNumber length]: unrecognized selector sent to instance

Our bug was reproduced: a string was written in the quantityUnit field as a string, but we got a NSNumber when it was read back from the database.

I ask Gwendal for help

I am very lucky: since my employer incwo shares its premises with Pierlis, I only had to walk 10 meters to talk to Gwendal Roué, who is the author of GRDB and knows a thing or two about databases and SQLite in particular.

What’s reassuring is that, in the beginning, Gwendal was perplex as well. The database migration did create the column as a STRING, and we could verify that by opening the database in DBBrowser For SQLite. And then, he had an idea. He opened a Terminal window and launched sqlite3:

> sqlite3
SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

With an in-memory database, it is now possible to execute SQL queries. Gwendal began by creating a table with a STRING column:

sqlite> CREATE TABLE t1(a STRING);

Then he inserted three rows with different types (a string which contains digits, a alphabetic string, and a number):

sqlite> INSERT INTO t1 (a) VALUES ('7');
sqlite> INSERT INTO t1 (a) VALUES ('TOTO');
sqlite> INSERT INTO t1 (a) VALUES (12);

The typeof() operator gives the actual type of storage of a field:

sqlite> SELECT typeof(a) FROM t1;
integer
text
integer

What? '7' —a string — was stored as an integer!

This actually confirms Gwendal’s intuition. In this page of SQLite’s documentation, you can see that STRING is not a storage class of SQLite. TEXT is. But as Gwendal tried to explain to me, SQLite tries to be compatible with SQL queries of other databases and will not complain if we use column types it does not know; instead it will do a guess, based on the actual type of the data. This is called “Type Affinity” in the document.

The fix

So we knew the problem: we ought not use STRING as a type for a column in SQLite. And the solution: use TEXT instead. But how can we fix the code? In SQLite, the type of a column can not be changed after its creation. This is a very usual problem and I know the steps:

disable foreign keys checks during the migration
create a products_temp table with the exact same fields as the products table, but the type of the quantityUnit column declared as TEXT instead of STRING.
copy data from the products table to the products_temp table.
delete the products table
rename products_temp to products

A question still remained. Will copy the data work? That is, we knew that some columns were stored as integer. Will copying them to a TEXT column convert them seamlessly to strings ?

sqlite> CREATE TABLE t2 (a TEXT);
sqlite> INSERT INTO t2 SELECT a FROM t1;
sqlite> SELECT a, typeof(a) FROM t2;
7|text
TOTO|text
12|text

Yes it will!

Conclusion

In this post my interest was more showing how Gwendal typed SQL queries to debug than the actual bug.

Gwendal told me that GRDB prevents this types of bugs because the usual way to create a table is using higher level functions, not using SQL. However our code base is still mainly written in Objective-C and we don’t use GRDB directly, but through its Objective-C binding. I look forward to the day that our whole database layer is re-written in Swift.