Support for Writing to Views

[stevensJourney]

Hi there 👋

We’re exploring adding GRDB query support to PowerSync, which syncs remote data into a local SQLite database. Synced data is exposed through SQLite views, with INSTEAD OF triggers registered internally to allow standard INSERT, UPDATE, and DELETE operations.

Querying views already work in GRDB (see this discussion), but write operations currently fail. For example, when declaring a view as a PersistableRecord, calling .insert() results in:

no such table: [view name]

This happens because GRDB attempts to resolve a primary key from the underlying "table", which doesn’t exist for a view.

Would it make sense for GRDB to support writable views? One possible direction could be a new protocol such as PersistableViewRecord that allows .insert(), .update(), and .delete() without requiring table metadata.

Like PersistableRecord, the default view name could be derived from the struct name, but still be overridable for flexibility.

Here’s a sketch of what this might look like:

import GRDB

// Writable view backed by INSTEAD OF triggers in SQLite
struct Todo: Codable, Identifiable, FetchableRecord, PersistableViewRecord {
    var id: String
    var title: String
    var isDone: Bool

    // Override when the view name differs from the struct
    static let databaseViewName = "todos"

    enum Columns {
        static let id = Column(CodingKeys.id)
        static let title = Column(CodingKeys.title)
        static let isDone = Column(CodingKeys.isDone)
    }
}

// Usage
try dbQueue.write { db in
    let todo = Todo(id: "123", title: "Buy milk", isDone: false)
    try todo.insert(db)   // INSERT INTO todos view ...
    try todo.update(db)   // UPDATE todos view ...
    try todo.delete(db)   // DELETE FROM todos view ...
}

This would let developers work with views in the same way as tables, with sensible defaults but flexibility where needed, while relying on SQLite’s INSTEAD OF triggers to handle persistence behind the scenes.

We’d love your thoughts on whether writable views fit into GRDB’s design, or if you’d recommend a different approach.

[groue]

Hello @stevensJourney,

Thanks for opening this discussion! Indeed GRDB generously queries the database schema about primary keys. Views have none, hence those errors.

The topic was brought to me a few days ago, by K. Botha. I currently have a branch that provides great support for mutable views through record types: dev/persistable-views.

I invite you to check the new features in this branch, and see if they could match the needs of PowerSync.

---

Since I could not write extensive documentation yet, let me give a quick summary:

You can now provide a schema source to GRDB database connections. That schema source can instruct GRDB about the primary key of the views of your choice:

struct PowerSyncSchemaSource: DatabaseSchemaSource {
  func columnsForPrimaryKey(_ db: Database, inView view: DatabaseObjectID) throws -> [String]? {
    // If PowerSync owns the database file, then you can assume
    // that all views that need a primary key belong to PowerSync:
    return ["id"]

    // If PowerSync does not own the database file, you should
    // allow the host applications to deal with other writable views.
    // To do so, return nil for views that PowerSync does not own.
    // The host app will be able to chain schema sources if needed.
    // In this sample code I suppose that PowerSync uses some
    // kind of naming convention:
    let baseTable = view.name + "_sync"
    if try db.tableExists(baseTable),
       try db.columns(in: baseTable).contains(where: { $0.name == "syncTimestamp" })
    {
      // Very likely a PowerSync view
      return ["id"]
    } else {
      // Not a PowerSync view:
      // don't mess with user's schema
      return nil
    }
  }
}

var config = Configuration()
config.schemaSource = PowerSyncSchemaSource()
let dbQueue = try DatabaseQueue(path: "/path/to/db.sqlite", configuration: config)

That DatabaseSchemaSource protocol seems to me like a good starting point for future features on views (such as telling which are the unique keys, or the foreign keys, etc.). So far only the primary key can be defined.

The record protocols are not modified: they still specify their table (or view) with the databaseTableName static property.

Once the schema source is configured, and your INSTEAD OF triggers created in the schema, the record types whose databaseTableName is a view should work as expected, with a few known caveats listed below:

• Auto-incremented primary keys are not supported. To be precise, SQLite won't expose the rowid of rows inserted via an INSTEAD OF trigger, so GRDB won't know the id of newly inserted rows, with various nefarious consequences. I asked for more information on the SQLite forum, at https://sqlite.org/forum/forumpost/707e7ed932. I suppose you do not need those.
• INSTEAD OF triggers and the RETURNING clause do not play well together. Those are SQLite bugs. All GRDB record methods with andFetch in their name may return unexpected or wrong results, depending on the operating system version (iOS 17 exhibits more bugs than iOS 18).

[groue]

The above comment has been amended in order to enhance its accuracy and relevance for PowerSync.

[stevensJourney]

Thanks for this! This looks amazing. We'll try and test the branch in the next few weeks.

For PowerSync we don't use Auto-incremented primary keys. I believe we should be fine with those restrictions.

Thanks again for all the hard work and quick response.

[groue]

Hi @stevensJourney, the dev/persistable-views branch now contains extensive documentation. If you run in Xcode the command Product > Build Documentation, the Documentation window will display GRDB documentation. Look for the "View Records" document in the home page:

Please tell me if the doc leaves unanswered questions.