1

I'm trying to figure out how to use UUIDs as primary keys and I'm close but am having trouble generating the UUIDs for inserts.

I use the following column definition in my migration

t.column("id", .text).notNull().defaults(sql: generateUuidSql())

where

static func generateUuidSql() -> String {
	return "(lower(hex(randomblob(4))) || '-' || lower(hex(randomblob(2))) || '-4' || substr(lower(hex(randomblob(2))),2) || '-' || substr('89ab',abs(random()) % 4 + 1, 1) || substr(lower(hex(randomblob(2))),2) || '-' || lower(hex(randomblob(6))))"
}

I also have

mutating func didInsert(with rowId: UUID, for _: UUID?) {
	id = rowId
}

in the record's extension for MutablePersistableRecord.

If I run this migration and open up the database in sqlite3 then I'm able to insert rows successfully. When I try to insert a record from GRDB though it includes the id column in the insert statement with a value of null and sqlite throws a not null constraint error.

Is there some way to customise which columns are used for the insert statement?

Alternatively, how might I populate the id using a UUID() column prior to insert? (I went looking for a willInsert)

2

Hello @opsb,

GRDB is currently not able to insert a subset of columns, and trigger default SQL clauses. Even if it could, it would be necessary to perform an extra SQLite request, after the insert, in order to grab the newly generated UUID. This is a limitation of SQLite.

Conclusion: using default values for generating uuids is not the recommended SQLite technique.

I suggest this alternative:

  1. Define a text primary key:

    try db.create(table: "myRecord") { t in
    t.column("id", .text).primaryKey()
    ...
}

    Don't forget the .primaryKey() part. SQLite won't allow NULLs in this primary key column: you don't have to add the .notNull() constraint.

  2. Generate a UUID, from Swift, when the record is inserted:

    struct MyRecord: MutablePersistableRecord {
    var id: String?
    
    mutating func insert(_ db: Database) throws {
        if id == nil {
            id = UUID().uuidString
        }
        try performInsert(db)
    }
}

    See Customizing the Persistence Methods for more information.

  3. Do not implement the didInsert(with:for:) method. It is only useful for auto-generated numerical row ids (Int64). It has no relation with other kinds of primary keys, and no relation with default values.

    See SQLite Autoincrement for a general discussion about the "ROWID".

    See PersistableRecord Protocol for GRDB documentation about didInsert(with:for:).

  4. Enjoy:

    var record = MyRecord(id: nil, ...)
print(record.id) // nil
try dbQueue.write { db in
    try record.insert(db)
}
print(record.id) // some unique id
3

That's really helpful, many thanks!

1 Like
4

You're welcome. I always hope that learning bits of GRDB gives some information about SQLite as well. After all, maybe you'll use this wonderful database through another library or programming language, one day ;-)

2 Likes
5

Being close to the database is the reason I use GRDB so I definitely appreciate the information.

6

For the sake of completeness, here is a sample code which handles your initial setup, @opsb: a database table with a primary key which as a default value.

Some apps may want to insert rows without caring much about their default columns (log entries, for example). In this case, ignoring the columns with default values is useful.

And if they want, sometimes, to pay the price of performing two requests (insert + fetch) in order to get the generated column, why not?

/// The main record
struct Player: Codable, FetchableRecord, PersistableRecord {
    var id: String
    var name: String
}

/// An "unsaved player" does not have any id
struct UnsavedPlayer: Encodable, PersistableRecord {
    // UnsavedPlayer uses the same table as Player
    static let databaseTableName = Player.databaseTableName
    
    // UnsavedPlayer has the same properties as Player, minus `id`.
    var name: String
    
    /// Inserts and returns a player with its generated id.
    func insertReturningPlayer(_ db: Database) throws -> Player {
        // If some other connection messes with the database, it could
        // break our guarantee to return a full Player.
        // The savepoint prevents such a bug.
        var player: Player!
        try db.inSavepoint {
            try insert(db)
            player = try Player.filter(Column.rowID == db.lastInsertedRowID).fetchOne(db)
            return .commit
        }
        return player
    }
}

// Set up a database
var configuration = Configuration()
configuration.prepareDatabase = { db in db.trace { event in print(event) } }
let dbQueue = DatabaseQueue(configuration: configuration)

// Define the `generateUUID` function
dbQueue.add(function: DatabaseFunction("generateUUID") { _ in
    UUID().uuidString
})

try! dbQueue.write { db in
    // Create the table
    try db.create(table: "player") { t in
        t.column("id", .text).primaryKey().defaults(sql: "(generateUUID())")
        t.column("name", .text).notNull()
    }
    
    // Insert and ignore the generated UUID
    let alice = UnsavedPlayer(name: "Alice")
    try alice.insert(db)
        
    // Insert and grab the generated UUID
    let bob = UnsavedPlayer(name: "Bob")
    let fullBob = try bob.insertReturningPlayer(db)
    
    // Inserted player: Player(id: "B45E9FFD-6D76-43E8-A136-A4C8845976BD", name: "Bob")
    print("Inserted player: \(fullBob)")
}
1 Like
7

I like the idea of modelling an UnsavedPlayer. I was planning on using a returning clause so I was curious to see you do a separate fetch, but I see now that sqlite3 doesn't support returning so this makes a lot of sense. I'd seen savepoints mentioned but hadn't connected them with a practical use yet so thanks for sharing that in the example.

I've just spotted the dbQueue.add(function: DatabaseFunction("generateUUID"), is this handled in the GRDB integration layer or are you actually able to extend sqlite using swift functions? (which would be pretty rad!)

8

Yes, the "returning" word looked like a good match. Since SQLite closely follows PostgreSQL, we may get it eventually :-)

The use of a savepoint is a side effect of the slight paranoia I have grown after a few years of making GRDB as robust as possible against SQLite concurrency traps. Practically speaking, the standard write method is already serialized (so there is no in-app concurrency hazard), it already creates a transaction (making the savepoint pointless against external connections), and very few apps involve concurrent writers: the chances that the savepoint turns out actually useful are pretty low :sweat_smile: The savepoint version is 100% robust, but frankly, this is just as good (let's say 99%):

/// Inserts and returns a player with its generated id.
func insertReturningPlayer(_ db: Database) throws -> Player {
    try insert(db)
    return try Player
        .filter(Column.rowID == db.lastInsertedRowID)
        .fetchOne(db)! // <- this bang is the low but non-zero concurrency risk
}

I've just spotted the dbQueue.add(function: DatabaseFunction("generateUUID") , is this handled in the GRDB integration layer or are you actually able to extend sqlite using swift functions? (which would be pretty rad!)

It's an actual app-defined SQLite function! You can inject C functions into SQLite, and SQLite calls you back when it has to evaluate it from SQL statements :+1:

See SQLite: Create Or Redefine SQL Functions, and GRDB: Custom SQL Functions and Aggregates. It was an excellent opportunity to teach myself a little Swift/C integration as well as Swift pointers (but I'm far from sure the code is correct) :wink:

1 Like
9

Beware that a database schema which uses a user-defined function will throw errors if it is used in another environment which does not define this function. For example, you may want to play with the database in a general-purpose SQLite application. Or in an Android/Windows app, whatever.

10

Right, that would definitely complicate things if I've downloaded the sqlite database for a play.

11

Regarding the save point there is a part of the app where I may have results streaming in from a bluetooth device and the backend at the same time so there is a possibility of concurrent writes. I'm not sure I'm following how save points offer greater protection over transactions though. According to Savepoints they offer nested transactions with named rollbacks but I don't see how this would give greater protection with multiple writers. Could you expand on this little? (having worked with concurrency in other languages I know you can get bitten in subtle ways)

12

Savepoints offer the same protection as transactions. The difference is that savepoints can be nested, when transactions can't. When you want to make sure some statements are protected by a transaction, but don't know if a transaction is already active or not, savepoints are perfect.

Most methods that write and accept a Database should use a savepoint:

func doStuff(_ db: Database) throws {
    try db.inSavepoint { ... }
}

// 3 × Fine
try dbQueue.write(doStuff)                   // implicit transaction
try dbQueue.writeWithoutTransaction(doStuff) // no transaction
try dbQueue.inTransaction { db in            // explicit transaction
    try doStuff(db)
    return .commit
}

GRDB documentation fosters the simple write, which opens an implicit transaction and brings the most safety.

Other writing methods allow manual transaction handling, and target advanced use cases.

If you run a single process, and this process uses a single instance of DatabaseQueue or DatabasePool, then concurrent writes are serialized. Practically speaking, all threads can call a writing method, but there is never two threads that write in the database at the same time. Some threads will just wait until other writes are completed. There is no parallel write (SQLite does not support this).

If you run multiple processes that write in the database, then you should carefully read the Sharing a Database guide.

If you run a single process, and this process creates several instances of DatabaseQueue or DatabasePool on the same database file, then this is most probably a misuse. Refactor your app so that you share a single instance of DatabaseQueue or DatabasePool. See the Concurrency Guide for more information.

In our (insert + fetch) group, the risk is THERE:

func insertReturningPlayer(_ db: Database) throws -> Player {
    try insert(db)
    // <- THERE
    return try Player
        .filter(Column.rowID == db.lastInsertedRowID)
        .fetchOne(db)!
}

THERE, a concurrent writer (another process, or another instance of DatabaseQueue or DatabasePool) could delete the freshly inserted player. A transaction brings this risk to zero.

13

Thanks that makes a lot of sense. I'm sharing a single dbpool at the moment so it sounds like I should be pretty safe.

I'm curious what you're referring to by "process" here, I guess we're talking about additional processes outside of an app, but would some examples of these be?

14

Yes, other processes such as an iOS extension, an old version of your macOS app that your user has kept, or even a simple sqlite3 unix command run from the terminal :-)

15

Ah of course, my app uses bluetooth so I'm not able to use a simulator. Makes a lot of sense if it's sitting there on your mac :slight_smile:

Thanks for taking the time to answer my questions, I'm new to iOS dev so lots of questions at the moment.

1 Like
16

Hello,

I used quite the same code, and the protocol function insert() in my struct is never called during the save process. I have a general error during the saving.

My Model:

struct Book: Identifiable, Codable, FetchableRecord, TableRecord, PersistableRecord  {

		var id: String?	// stores the UUID as string	
		var name: String

		// new
		static func new() -> Book {
				return Book(name: "")
		}
	
}

Then, I added this extension to my model to manage the adding of the UUID in the persistence method insert(). I used the MutablePersistableRecord protocol.

extension Book: MutablePersistableRecord {

		// MutablePersistableRecord protocol
		mutating func insert(_ db: Database) throws {
				if id == nil {
						id = UUID().uuidString
				}
				try performInsert(db)
		}

}

Then the code to create the new book:

func saveBook(book: Book) throws {
				try dbWriter.write { db in
						try book.save(db)
				}
		}

The problem: when I save a new book, the general error "An error occured" is catched. So I added breakpoints so see the code process during the save: the methods that are called are the ones of PertistableRecord protocol.
=> The method added in my model: mutating func insert(_ db: Database) is never called.
=> The id as uuid is never added.
=> The saving can't work.

I also tried to save with try book.insert(db) directly instead of try book.save(db). I read the GRDB documentation about customizing the persistence methods.

My guess was maybe I just need to use the MutablePersistableRecord and not the PersistableRecord, but if I do that, I have a general swift error on the try book.save(db) method:

Cannot use mutating member on immutable value: 'book' is a 'let' constant

Any idea is welcome.

17

Hello @alex.bour. Your post is somewhat confusing because you save Book but generate uuid in Wine, juggle with protocols in an unclear way, and talk about many things at the same time. Could you instead provide a clear and self-contained example, straight to the point, along with a description of the observed behavior vs. the behavior you expect? This is called a proper request for support, and it saves time for everybody. Thank you in advance!

18

Hello Gwendal,

I add more infos to the first post. I hope it's more clear now.
Tell me if you need anything more.

Alexandre

19

It looks that you declare both PersistableRecord and MutablePersistableRecord conformances. Pick only one. I guess it has to be MutablePersistableRecord, because insertion modifies the book by setting its uuid. Can you tell if this fixes your issue?

20

Thanks for the proposal. It's why I was thinking, however, as soon as I remove the PersistableRecord protocol in my model, and keep MutablePersistableRecord instead, I have this error on the book saving line:

Cannot use mutating member on immutable value: 'book' is a 'let' constant

on this line:

try book.save(db)

struct Book: Identifiable, Codable, FetchableRecord, TableRecord, MutablePersistableRecord  {
		
		// database fields
		var id: String?		
		var name: String
		var countryId: Int64?
}

I have no fields with let in my struct model.