Local Databases

Files keep data as a sequence of bytes; finding a record inside, updating a field, or merging two records is the app’s job. If the data can be divided into rows and fields, and if it is searched and updated, a local database is a better tool. The most common option on a device is an embedded relational database; it lives in a single file and offers a query language. The question of this lesson is the same as in the earlier lessons: if this operation is repeated or reordered, does the result change? The difference is this: the database manages identity itself, and where that identity comes from decides the result.

// Replay.swift — classifies an operation's sensitivity to repetition and order
struct Trial<State: Equatable & CustomStringConvertible> {
    let label: String
    let once: State
    let twice: State
    let forward: State
    let backward: State

    var sensitivity: String {
        switch (once != twice, forward != backward) {
        case (false, false): return "robust"
        case (true, false): return "repeat-sensitive"
        case (false, true): return "order-sensitive"
        case (true, true): return "doubly-sensitive"
        }
    }

    func report() -> String {
        print("\(label) | once=\(once) twice=\(twice) ab=\(forward) ba=\(backward) -> \(sensitivity)")
        return sensitivity
    }
}

func replay<State>(_ label: String, from start: State, _ a: (State) -> State, then b: (State) -> State) -> Trial<State> {
    Trial(label: label, once: a(start), twice: a(a(start)), forward: b(a(start)), backward: a(b(start)))
}

func countSensitivities(_ results: [String]) {
    for name in ["robust", "repeat-sensitive", "order-sensitive", "doubly-sensitive"] {
        print("\(name): \(results.filter { $0 == name }.count)")
    }
}

once is the operation applied to the starting state one time, twice is two times; ab is a first and then b, and ba is the reverse order. The model below is the pure Swift counterpart of a table. The model is not a query engine; it only builds the result the operation leaves in the table.

Primary Key and Insertion

Every row has a primary key, and a table cannot hold two rows with the same key. When a row is inserted, the key comes in one of two ways. Either the app supplies the key itself; in that case a second insertion that comes with the same key either fails or, with the upsert syntax, replaces the existing row. Or the database generates the key automatically; in that case every insertion opens a new row.

import SQLite3

var database: OpaquePointer?
sqlite3_open("notes.sqlite", &database)

sqlite3_exec(database, "CREATE TABLE IF NOT EXISTS notes (id TEXT PRIMARY KEY, body TEXT)", nil, nil, nil)
sqlite3_exec(database, "INSERT INTO notes (id, body) VALUES ('k1', 'a1') ON CONFLICT(id) DO UPDATE SET body = excluded.body", nil, nil, nil)
sqlite3_exec(database, "SELECT id, body FROM notes ORDER BY id", nil, nil, nil)
sqlite3_close(database)

This fragment is SOURCE; it needs a database file, so it does not run here. The ON CONFLICT(id) DO UPDATE clause builds the upsert behavior: if the key does not exist it inserts the row, and if it exists it replaces the body field with the new one. ORDER BY id sorts the read by key. Without this clause, the order in which the rows come back is left to the query engine.

The Model of the Table

The model keeps the insertion order of the keys and a mapping from key to value. It carries two extra pieces of information: the next auto-increment key, and whether the read is sorted by key.

// TableModel.swift — a pure Swift model of a table
struct TableBox: Equatable, CustomStringConvertible {
    private(set) var keys: [String] = []
    private(set) var values: [String: String] = [:]
    private(set) var nextId = 1
    let sortedRead: Bool

    init(sortedRead: Bool = true) {
        self.sortedRead = sortedRead
    }

    var description: String {
        let order = sortedRead ? keys.sorted() : keys
        return order.isEmpty ? "-" : order.map { "\($0)=\(values[$0] ?? "-")" }.joined(separator: ",")
    }

    static func == (lhs: TableBox, rhs: TableBox) -> Bool {
        lhs.description == rhs.description
    }

    mutating func upsert(_ value: String, key: String) {
        if values[key] == nil { keys.append(key) }
        values[key] = value
    }

    mutating func insert(_ value: String) {
        let key = String(nextId)
        nextId += 1
        keys.append(key)
        values[key] = value
    }
}

func upserting(_ value: String, key: String) -> (TableBox) -> TableBox {
    { table in
        var next = table
        next.upsert(value, key: key)
        return next
    }
}

func inserting(_ value: String) -> (TableBox) -> TableBox {
    { table in
        var next = table
        next.insert(value)
        return next
    }
}

An upsert on an existing key leaves the row in place and changes only the value; its place in the insertion order is kept. The auto-increment key starts from 1 and goes up by one with every insertion.

PS9. The model assumes that rows come back in insertion order when no ordering is specified. A real query engine does not guarantee this order; the bounding measurement uses this assumption openly.

PS10. The model builds a single table and a single primary key column. Keys with multiple columns and relations were not built.

Measuring Four Operations

Four operations are built. The first is applying an upsert to two different keys. The second is writing two different values to the same key. The third is inserting the same value twice with an auto-increment key. The fourth is inserting two different values the same way.

// main.swift — primary key and auto-increment key
let distinctKeys = replay("upsert-distinct-keys", from: TableBox(), upserting("a1", key: "k1"), then: upserting("b2", key: "k2")).report()
let sameKey = replay("upsert-same-key", from: TableBox(), upserting("a1", key: "k1"), then: upserting("b2", key: "k1")).report()
let autoSame = replay("auto-same-value", from: TableBox(), inserting("a1"), then: inserting("a1")).report()
let autoTwo = replay("auto-two-values", from: TableBox(), inserting("a1"), then: inserting("b2")).report()

countSensitivities([distinctKeys, sameKey, autoSame, autoTwo])

upsert-distinct-keys | once=k1=a1 twice=k1=a1 ab=k1=a1,k2=b2 ba=k1=a1,k2=b2 -> robust
upsert-same-key | once=k1=a1 twice=k1=a1 ab=k1=b2 ba=k1=a1 -> order-sensitive
auto-same-value | once=1=a1 twice=1=a1,2=a1 ab=1=a1,2=a1 ba=1=a1,2=a1 -> repeat-sensitive
auto-two-values | once=1=a1 twice=1=a1,2=a1 ab=1=a1,2=b2 ba=1=b2,2=a1 -> doubly-sensitive
robust: 1
repeat-sensitive: 1
order-sensitive: 1
doubly-sensitive: 1

In the first line, two different keys give the same table whichever order they are written in, and repeating the same write does not change the table. Because the read is sorted by key, the insertion order is not visible either. The class is robust. This is the only robust line measured in this lesson, and the reason is two things being present together: the app supplies the key and the read is sorted.

In the second line, two different values are written to the same key. Repetition is harmless; but ab is b2, ba is a1. The last writer wins. The primary key prevents duplicate rows; it does not say who wins in a collision. The class is order-sensitive.

In the third line, the database generates the key. Inserting the same value twice leaves two rows: 1=a1 and 1=a1,2=a1. Since the two operations are identical, the order is meaningless. The class is repeat-sensitive. An insert call that is retried is duplicated, because the database counts it as two separate records.

In the fourth line, when two different values are inserted in two different orders, the keys are distributed to the values differently as well: 1=a1,2=b2 and 1=b2,2=a1. The class is doubly sensitive.

The count is one of each class.

Who Supplies the Key

The measurement gives a practical rule. If the records have a natural identity, for example an identity given by the server or a digest derived from the content, the app should supply the key and the insertion should be written as an upsert. That makes a retry safe. If there is no natural identity, an auto-increment key is used; but a retry is duplicated, and something that prevents it, for example a separate unique column, is added to the design.

Common Mistakes

• Relying on an auto-increment key in an insert call that is retried. The row is duplicated.
• Reading without specifying an ordering and assuming that the insertion order is preserved.
• Writing a two-step path that reads first and then inserts, instead of an upsert. Another write can slip in between the two steps.
• Updating the same row from two sources and assuming that the winner is known. The last writer wins.
• Opening a table for every small value. The preference store is enough for that.
• Writing many rows one by one, in separate transactions. If they are all written in a single transaction, consistency and speed are gained.

PS11. The model assumes that every call runs without interruption and from a single connection. The case where two connections write at the same time was not built.

The Bounding Measurement

The robustness of the first line rested on the read order. Next, we measure the same two operations in a table that reads in insertion order, and put it side by side with the sorted one for comparison.

// main.swift — read order
let sorted = replay("key-order-read", from: TableBox(sortedRead: true), upserting("a1", key: "k1"), then: upserting("b2", key: "k2")).report()
let insertion = replay("insertion-order-read", from: TableBox(sortedRead: false), upserting("a1", key: "k1"), then: upserting("b2", key: "k2")).report()

countSensitivities([sorted, insertion])

key-order-read | once=k1=a1 twice=k1=a1 ab=k1=a1,k2=b2 ba=k1=a1,k2=b2 -> robust
insertion-order-read | once=k1=a1 twice=k1=a1 ab=k1=a1,k2=b2 ba=k2=b2,k1=a1 -> order-sensitive
robust: 1
repeat-sensitive: 0
order-sensitive: 1
doubly-sensitive: 0

The operation and the data are the same; only the read changed. Reading sorted by key is robust, and reading in insertion order is order-sensitive: k1,k2 and k2,k1. The content of the table is the same two rows in both cases; the difference is how the rows are listed. This is a mistake seen everywhere a set is read like an ordered sequence: the result affects not the set but the listing.

The limit has two sides. The first is where PS9 is used: reading in insertion order is not guaranteed in a real query engine, and here it is only an option of the model. The second is what the rule does not see: the rule says that the two states are not equal; it does not say that the difference comes from the read order. A person supplies the cause.

Where It Shows Up in a Real App

An automatically generated key is the default choice in most tables, and the auto-same-value and auto-two-values lines of the measurement show its price. When the code that saves a record runs again, the database gives a new identity each time, and the same record sits as two rows. Pressing the submit button of an order form twice, retrying the request after a network error, or the app restarting a half-finished job produces this duplication. The list screen shows two identical orders; the user sees this as a bug, while the app sees two separate valid records.

When the caller supplies the key, the table refuses the duplication, but a different problem appears: if two operations write two different values to the same key, the value that remains depends on which one ran last. The upsert-same-key line shows this. It tells us that order sensitivity continues even in a place where tables are counted as robust. Operations writing to separate keys do not affect each other, and the upsert-distinct-keys line comes out robust.

So in a database, the label “robust” depends not on the table but on which keys the operation touches. The same table is robust when working with non-colliding keys and order-sensitive when writing to the same key. That this lesson’s sensitivity label is robust rests on the most protected form of operation in tables, that is, the keyed update.

A Wrong Setup and Its Fix

The most common wrong setup is to query first whether the record exists and then insert. First it is checked “does this record exist”, and if not, it is inserted. If two operations ask this question at the same time, both get the answer “no” and both insert. The gap between the check and the insertion is where repetition leaks in.

The fix is to have the table itself do the check instead of leaving it as a separate step. The key is supplied by the caller, as a stable value derived from the record, and the table refuses a second insertion of the same key or updates the existing row. That way “does it exist, otherwise insert” becomes a single operation and there is no gap left to slip into. In the language of the measurement: the operation stops being repeat-sensitive.

The second mistake is trusting the order of a query result. For a query with no ordering specified, the order in which it returns rows is not guaranteed. Even if the order of a screen coincides with the insertion order today, it can change when an index is added to the table or when the data grows. The insertion-order line in the bounding measurement used exactly this, and PS9 recorded that this assumption is an option of the model. The fix is to specify the ordering explicitly in every query where order matters; key order is the form of reading that came out robust in the measurement.

Reading the SOURCE Fragment

The unnamed fragment at the start of the lesson is a sequence of connection calls that defines a table, inserts a row into it, and then queries it. A real connection opens a file, prepares the query as a statement, binds the values to the statement, and runs it step by step. The single call in our model is, in reality, the sum of these four steps; PS10 records this simplification and the table being limited to a single primary key column.

The values in the fragment are written directly into the query text for brevity; if text coming from the user is joined into the query this way, it can change the query itself. In real code the values are bound to the prepared statement, and the user’s text is never joined into the query text. This is an important habit from a security point of view and has nothing to do with the measurements of this lesson; it is still noted so that its place in real code is known. The assumption that the connection works without interruption in every call is PS11: the case where two connections write at the same time was not measured, and in real databases this case enters separate topics such as locks and transaction isolation.

From Measurement to Design

The result of this lesson says why tables are a safe default and how far that safety extends. A keyed update is robust, but only when writing to separate keys. If two different values are written to the same key, the order is still decisive. What the table adds is that the collision becomes visible and the rule is written in the table: does the last writer win, or the first, or is the second write refused. Expressing this rule in the schema instead of hiding it in code makes every write path obey the same rule.

Three questions are enough for design. Does the record have a natural identity? If it does, deriving the key from it makes the table refuse repetition. If it does not, a generated identity always carries a duplication risk, and this risk is managed on the operation side, for example with a key generated once for each request. Can two operations write to the same record? If they can, the rule must be written down. Does the read order matter? If it does, the ordering must be specified explicitly.

None of these three questions depends on the choice of database. However powerful the query language is, how the operation behaves against repetition and order depends on how the record is defined. This is what the measurement teaches: what is robust is not the table, but the operation in which the table is used correctly.

A small extra warning: the measurement looks at a single table. In a real database, two tables are connected by a relation, and an operation has to write to both. In that case a write left half done can leave inconsistent data; this topic belongs to transaction isolation and is outside the scope of this lesson’s measurement.

Summary

• If the data is divided into rows and fields and searched, a local database is chosen instead of a file.
• An upsert with a primary key is robust with two different keys and with a sorted read.
• Writing two different values to the same key is order-sensitive: ab is b2, ba is a1.
• Inserting with an auto-increment key grows the table from 1=a1 to 1=a1,2=a1; with two different values it is doubly sensitive.
• The same two operations are robust if the read is sorted by key and order-sensitive if it is done in insertion order.
• This lesson’s sensitivity: robust.

Next Step

Instead of using the database directly with queries, there are frameworks that work with objects. In the next lesson, Object Persistence Frameworks, we measure that creating and saving an object in a context is repeat-sensitive, and how a unique constraint changes that.