Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Best practices on relations and "Parent/children" objects. #87

Closed
freak4pc opened this issue Jul 24, 2016 · 11 comments

Comments

Projects
None yet
2 participants
@freak4pc
Copy link

commented Jul 24, 2016

Hey All,

I'm currently using Realm and have been thinking of migrating to SQLite in favour of some added control and some better handling of thread safety. I'm just trying to make sure SQLite and GRDB are capable of what I'm trying to achieve and would also love to hear how members of the community are handling this situation.

I'm looking to do something that seems very basic to me, like so:

class User: Record {
   public var id: String? 
   public var orders = [Order]()
}

class Order: Record {
  public var id: String? 
  public var userId: String? // In realm this isn't needed afaik 
}

Then, do something similar to (in a pseudo kind of way):

let user = User(...some data...)
let o1 = Order(...)
let o2 = Order(...)
user.orders.append(o1)
user.orders.append(o2)

do { 
 try user.save()
} 

From discussion on twitter with @groue, it seems relations aren't a possibility as of today but I am sure it's a very common use case, so I'm trying to understand what's the common usage pattern on this.

Would you actually start sort of a "transaction" and insert all of these items in that single transaction ? And same for fetching back the data... A sort of computed property to fetch the data per relation would probably be needed, which also breaks uniformity (because data fetched from JSON would be in an orders field, while data I want to get from SQLite will be in ordersRequest, for example).

Clearly it would be great if the "parent" could be inserted and fetched as a whole, but I'm guessing the lack of relations makes this impossible.

I'm just trying to grasp the "best practices" of relations in this situation as it seems like there will be a lot of boilerplate that needs to be written.

Would love the community's thoughts on how you handle these situations.

Shai.

@groue

This comment has been minimized.

Copy link
Owner

commented Jul 24, 2016

Hello @freak4pc

It had to happen, eventually. OK, let's dive in the topic of associations.

Let's start with a little introduction. Let's say it's the draft of a future chapter of the documentation.

  • Records are not "uniqued", and do not auto-update.

    This means that two subsequent fetches will return different and independent record instances, and that records won't automatically update their properties.

    Why is that so?

    • it was simpler to implement, obviously
    • fetching methods are not proper to the Record class, but to the RowConvertible protocol. This means that value types can be fetched as well, and value types can not be uniqued by definition.
    • records are immutable (at least GRDB won't mutate them on your back), and this makes them trivial to share between various threads.

    A famous ORM that provides uniquing is Core Data. It makes Core Data a wonderfully well suited framework for OSX development, which makes heavy use of bindings and key-value coding and observing. But iOS has no bindings, Swift doesn't like KVC/O, and Core Data is very difficult to use properly in a multi-threaded application.

    A famous ORM that provides auto-updating objects is Realm. A consequence is that records can't be shared between threads, just like in Core Data.

  • GRDB wants controllers to control.

    In the "fat controller" vs "fat model" debate, GRDB is on the side of fat controllers.

    The reason is that models usually encapsulate a partial view of the database, and don't have the slightest idea of the application threading architecture. Only controllers have the big picture. Particularly: only controllers know about the best scope for database transactions (the basic unit for changes that should be stored on disk as a whole), their content, and timing.

    To foster this architecture, GRDB provides database access classes that guarantee isolated and consistent database contexts (DatabaseQueue and DatabasePool), and forbids reentrancy when using those classes. Controllers own (or know) the database queues or pool, and models expose methods that take a Database connection as an argument:

    class MyController {
        func myJob() {
            try! dbQueue.inDatabase { db in
                try myModel1.insert(db)
                try myModel2.delete(db)
                try myModel3.doSomeWork(db, with: 1, and: "foo")
            }
        }
    }

    Models can still provide critical database sections:

    class MoneyTransfer {
        func perform(db: Database) throws {
            try db.inSavepoint {
                // Should an error happen in this closure, the whole closure
                // will be rollbacked.
                try sourceAccount.debit(db, amount)
                try destinationAccount.credit(db, amount)
            }
        }
    }

    I personally push this controller empowerment so far that NSOperation that modify the database don't actually modify the database: instead they produce a closure that performs those database changes. The controller that spawns the operation eventually schedules this closure in the way it wants (maybe wrapped in a transaction that groups together the changes made by other operations).

  • It can help thinking of GRDB as a server-inspired application framework.

    Since records are not uniqued, you can architecture your application around independent data flows. Synchronisation points are eventually unavoidable: your application knows better than GRDB when they are needed, and what should be synchronized, and when. Database observation tools like FetchedRecordsController and transaction observers can help.

  • GRDB wants the developer to be able to control.

    Whenever you write high-level code with GRDB, SQL is never very far. And especially, it's always easy to step down one stair and go back to SQL without sacrificing anything:

    let persons = Person.filter(...).fetchAll(db)
    let persons = Person.fetchAll(db, "SELECT ...")
    
    let count = Person.filter(...).fetchCount(db)
    let count = Int.fetchOne(db, "SELECT COUNT...")!
    
    let personController = FetchedRecordsController<Person>(db, request: ...)
    let personController = FetchedRecordsController<Person>(db, sql: ...)
    

    This is the result of years of frustration using nannying database frameworks.

    This has a consequence on associations: whenever GRDB provides support for them eventually, they will be built on top of the support for JOIN queries. The work has begun on one-to-one associations, around the row adapters that you can already use today:

    for book in Book.fetch(db, sql, adapter: adapter) {
        book.title        // Moby-Dick
        book.author?.name // Melville
    }

    It works, but is still very low-level. Lifting this up to a high-level API for records is an unachieved work in progress.

  • GRDB and the rule of three

    The rule of three states: "A pattern can be called a pattern only if it has been applied to a real world solution at least three times."

    GRDB is very seriously driven by this rule. This is why I'm rather confident that GRDB won't prevent you from performing any of your tasks.

    I also usually advise applying the rule of three in applications themselves. More on that below.

Now that a litte context has been exposed, we can talk about associations.

Array proxies

You wrote:

let user = User(...some data...)
let o1 = Order(...)
let o2 = Order(...)
user.orders.append(o1)
user.orders.append(o2)

do { 
 try user.save()
} 

I'll assume you were inspired by Realm.

This works because User.orders is not an array, but some kind of object that acts like an array, an array proxy: List<Order>.

What I know from my experience is that array proxies are strange beasts and are usually surprising and frustrating. I don't know any of them which is implemented in order to match the developer mental model. ActiveRecord associations has many weird behaviors whenever you mutate the array, or individual elements. Core Data associations used to have (I think it's better now) weird behaviors whenever you mutate the array or individual elements. I have no experience about Realm's List, but Stack Overflow is full of questions about it, and I'm thus not very confident about its lack of weirdness.

OK all developers can get experience, eventually become experts, and gain points on Stack Overflow answering noob questions about the weird idiosyncrasies of the technology they use.

That's not what I want to give. There is no noob and expert above, just APIs that improperly match their domain, and have failed empathy, the rule of three, and saving my time looking for magical workarounds.

There aren't much Stack Overflow questions about GRDB, and there is a reason for that. Instead, GRDB has nice and interesting issues like this one ;-)

OK the current state of GRDB regarding your sample code is:

try dbQueue.inDatabase { db in
    let user = User(...some data...)
    try user.save()
    try Order(userId: user.id, ...).save()
    try Order(userId: user.id, ...).save()
} 

A more complex example

Let's explore another use case: listing users with the number of orders they have passed.

I don't quite know how Realm does this. With GRDB, this is how I do that today:

class UsersController : UITableViewController {
    var users: [UserViewModel] = []

    private class UserViewModel : User {
        var orderCount: Int
        init(_ row: Row) {
            orderCount = row.value(named: "orderCount")
            super.init(row)
        }
    }

    override func viewWillAppear(animated: Bool) {
        super.viewWillAppear(animated)
        fetchUsers()
        tableView.reloadData()
    }

    private func fetchUsers() {
        users = dbQueue.inDatabase { db in
            UserViewModel.fetchAll(db,
                "SELECT users.*, COUNT(order.id) AS orderCount " +
                "FROM users " +
                "LEFT JOIN orders ON orders.userID = users.id " +
                "GROUP BY users.id " +
                "ORDER BY users.name")
        }
    }

    private func configureCell(cell: UITableViewCell, atIndexPath indexPath: NSIndexPath) {
        let user = users[indexPath.row]
        cell.textLabel.text = user.name
        cell.detailTextLabel.text = "\(user.orderCount) orders"
    }
}

What do we see in the sample code above?

  • a private UserViewModel class, dedicated to the particular need of the UsersController class. This is the rule of three in application: there is no need to touch the User class for a single particular need.

    Since UserViewModel is a subclass of User, other controllers that expect User are happy:

    override func prepareForSegue(segue: UIStoryboardSegue, sender: AnyObject?) {
        if segue.identifier == "EditUser" {
            let userEditionController = segue.destinationViewController...
            let selectedIndex = tableView.indexPathForSelectedRow!
            let user = users[selectedIndex]
            userEditionController.user = user // UserEditionController expects a User
        }
    }
  • an efficient SQL request. I plan to extend the query interface so that it helps generating this request. When it ships eventually, I expect a single line of the code above to change:

    users = dbQueue.inDatabase { db in
        // Expected API (User.orders is a definition of the relation)
        UserViewModel.annotate("orderCount": User.orders.count).fetchAll(db)
    }

    Still the point here is that the developer is in control, and is able to leverage the best tool for the job, namely a single JOIN request.

  • a controlled refreshing of data. The controller reloads its data in its viewWillAppear method, and makes sure it matches the content of its tableView. Everybody knows that UITableView doesn't like data sources that do not control their data and change their mind about the row count.

    FetchedRecordsController can provide an auto-updating list of users that is safely tied to the main thread, but the code above is simpler like that.

OK. What did I want to show in this long answer?

First, clearing my ideas :-)

Last, associations are a wide topic, with many possible scenarios around. GRDB does not provide much support for them: there is much much much room for improvement here. I hope to ship something eventually. Until then, I encourage developers to focus on their actual needs, instead of waiting for generic tools that fit more or less well their needs.

@groue groue added the question label Jul 24, 2016

@freak4pc

This comment has been minimized.

Copy link
Author

commented Jul 24, 2016

Hey @groue , This is great!
Many thanks for the in-depth write up. Very interesting read :)

I am aware of how every tool is opinionated and as we discussed earlier I agree that it should be easily solved currently using a computed property that could execute a separate query to fetch the "related data".

The only issue with that is the fact there are two ways the Model is populated.
For example, for an Order with Product(s) , that data could be populated from the local SQLite database (GRDB), but it could also be populated using a JSON dictionary from some network request like most apps do, for example.

This means if I use some sorts of a computed property (like Order.products that will do a query fetching product.orderID == order.ID), it will need to be in a separate variable that represents that local database products, while data populated using JSON will be in a different variable that represents data parsed through JSON, for example. I'm a bit worried of that ambiguity and confusion.

A possible solution could be something like this but I'm not 100% sure that is very "smart" and I'm not sure I'm the greatest fan of this somewhat "Hacky" solution:

public class Order: Record {
    public var id: String! 

    /// Will return either the provided "data" from a previous JSON parsing, 
    /// or will query for local data matching that relation. 
    public var products: [Product] {
        return self.productsData ?? Products
                                      .filter(sql: "orderID = '\(self.id)'")
                                      .fetchAll(db)
    }

    /// Represents the JSON data parsed on a new object 
    internal var productsData: [Product]?
}

This way I could always hit Order.products in a unified way and get either the stored data or, if none was set from some external data source, hit the local database. But as I said this feels "hacky". Can't think of a "nicer" method to have a Model that can both store data and retrieve data in a unified sort of way.

I also feel that If I need to create "logic" to know where am I fetching data from (JSON vs. DB), then I'm introducing state into the Model which isn't the best way to go in my opinion.

I agree that associations of data are a vast subject and also, every developer and app are looking for something quite "different" when it comes to that.

I also agree on "uniqued" records being dangerous (as I can definitely say from using Realm), but I don't think that should prevent a more "ORM" style implementation if you would feel that's a right fit specifically for GRDB.

Again, thanks for writing up and enriching my knowledge on this :) And for your efforts in answering and developing this library.

Shai.

@groue

This comment has been minimized.

Copy link
Owner

commented Jul 24, 2016

The only issue with that is the fact there are two ways the Model is populated.
For example, for an Order with Product(s) , that data could be populated from the local SQLite database (GRDB), but it could also be populated using a JSON dictionary, for example.

This means if I use some sorts of a computed property (like Order.products that will do a query fetching product.orderID == order.ID), it will need to be in a separate variable that represents that local database products, while data populated using JSON will be in a different variable that represents data parsed through JSON, for example. I'm a bit worried of that ambiguity and confusion.

Yes. I would also be worried of ambiguity and confusion.

That ambiguity and confusion comes from the fact that you want the model to do the controllers' jobs.

User does not need to load orders from the database. A controller does.

User does not need to translate JSON into orders. A controller does (maybe via an NSOperation, or an Alamofire JSON request).

More: "orders" that come from JSON may only partially match the concept of "orders" stored in your application. The first come from a server, with its particular needs and way to see the world. The last go to the SQLite database, with its particular needs and way to see the world. They may match. Sometimes they don't.

Coupling those two realms too much can prevent the independent maintenances and evolutions of both your application and its peer server. Remeber that in a client/server architecture, the relation is a one-to-many: the fact that your server has a single client should not make you forget that there can be several clients, each with its own particular use of the server API, and its own storage needs. Your client's world is not your server's world.

Should I really really want to give users a concept of orders, I would thus keep both User and Order in the same realm: the SQLite/GRDB one. After all, aren't they both Record subclasses? And User would not know anything about the JSON orders.

(I'll answer other points below)

@freak4pc

This comment has been minimized.

Copy link
Author

commented Jul 24, 2016

That ambiguity and confusion comes from the fact that you want the model to do the controllers' jobs.

User does not need to load orders from the database. A controller does.

User does not need to translate JSON into orders. A controller does (maybe via an NSOperation, or an Alamofire JSON request).

I agree about the Model not needing to do any fetched, that is why it's a Hacky™ solution ;-)

I do not agree thought on the fact the Model shouldn't be able to translate JSON related to itself.
I think any Model should be able to have methods / initializers to populate itself using various data sources. It is very common to use something like ObjectMapper with a custom init to have a model accept JSON data and popular a "Model".

But that is definitely a matter of taste and preference and I totally respect and value your opinion on fields of responsibility for a Model :) It just seems to me that if the Model can't parse JSON practically, I would need a separate Model for network responses and for database responses which is something i absolutely do not want to go through :)

I agree about the local-vs-remote matching but that should (or could) actually be client logic (in my opinion) though.

I have changed the example to Order<->>Products just to make things easier, so User wasn't part of this specific example :)

@groue

This comment has been minimized.

Copy link
Owner

commented Jul 24, 2016

A possible solution could be something like this but I'm not 100% sure that is very "smart" and I'm not sure I'm the greatest fan of this somewhat "Hacky" solution:

public class Order: Record {
    public var id: String! 

    /// Will return either the provided "data" from a previous JSON parsing, 
    /// or will query for local data matching that relation. 
    public var products: [MenuItemModifier] {
        return self.productsData ?? Products
                                      .filter(sql: "orderID = '\(self.id)'")
                                      .fetchAll(db)
    }

    /// Represents the JSON data parsed on a new object 
    internal var productsData: [Product]?
}

This way we could I could always hit Order.products in a unified way and get either the stored data or, if none was set from some external data source, hit the local database. But as I said this feels "hacky". Can't think of a "nicer" method to have a Model that can both store data and retrieve data in a unified sort of way.

You already see an issue in the products property: where does this db come from?

Let me propose an alternative:

Option 1:

class Order: Record {
    var id: String!
    var products: QueryInterfaceRequest<Product> {
        return Product.filter(SQLColumn("orderId") == id)
    }
}

// Usage:
if let order = Order.fetchOne(db, key: 1) {
    let products = order.products.fetchAll(db) // [Product]
}

Option 2:

class Order: Record {
    var id: String!
    var products: [Product]?

    class func fetchOneWithProducts(db: Database, key: String) -> Order? {
        guard let order = Order.fetchOne(db, key: key) else { return nil }
        order.products = Product.filter(SQLColumn("orderId") == key).fetchAll(db)
        return order
    }
}

// Usage:
if let order = Order.fetchOneWithProducts(db, key: 1) {
    let products = order.products // [Product]
}

I'd actually go with option 1, because the products fetch request can be refined (unlike the fetchOneWithProducts method which is a dead-end):

let products = order.products.order(nameColumn).fetchAll(db)
let products = order.products.order(priceColumn).fetchAll(db)

This pattern allows controllers to choose when products should be loaded, a freedom that you'll get used to love.

A use case that is not addressed at all (and I feel deeply sorry about it), is loading many orders along with their products. I'll be happy discussing this topic in more details with you if you want.

I agree that associations of data are a vast subject and also, every developer and app are looking for something quite "different" when it comes to that.

I also agree on "uniqued" records being dangerous (as I can definitely say from using Realm), but I don't think that should prevent a more "ORM" style implementation if you would feel that's a right fit specifically for GRDB.

Again, thanks for writing up and enriching my knowledge on this :) And for your efforts in answering and developing this library.

And thank you for your understanding :-) I really hope to ship association support soon, but it's really hard to do well. See #73 (comment) for a glimpse into the future.

I do not agree thought on the fact the Model shouldn't be able to translate JSON. I think any Model should be able to have methods / initializers to populate itself using various data sources. It is very common to use something like ObjectMapper with a custom init to have a model accept JSON data and popular a "Model".

Sure. And you suffer, in your own words, from "ambiguity and confusion".

@groue

This comment has been minimized.

Copy link
Owner

commented Jul 24, 2016

(I may have answered an outdated comment before your last revisions)

@freak4pc

This comment has been minimized.

Copy link
Author

commented Jul 24, 2016

Sure. And you suffer, in your own words, from "ambiguity and confusion".

I don't currently suffer from ambiguity and confusion, since in Realm I have associations, meaning I can have a single object that creates itself by parsing data and also creates itself from a database without anything "Special" to bridge that gap.

I agree that for SQLite and GRDB this method won't be a good fit.

I like the two options you stated above, though, I'm just not completely sure on the overhead.

The first option is nice because it gives control, but it also means that I fetch data from the database every time I want to get the products, which is great for making sure I always have the latest data, but not great if I want an Order at a specific state and want to "pass it around" between ViewControllers (I'll have store the products somewhere else when fetching them, instead as part of the model itself).

Anyways now I get more clarity on what GRDB should and should not be used for, and for sure this is a very different approach than a more "Managed" solution, but I can see the appeal of it :) I'll find some time to dig deeper and see if it would be feasible to convert our current Realm usage to GRDB and see how easy it is to maintain.

A use case that is not addressed at all (and I feel deeply sorry about it), is loading many orders along with their products. I'll be happy discussing this topic in more details with you if you want.

I guess that could be done using a subquery for the products, but that is definitely a trickier situation. We also have that use case being used currently so it would be interesting to see how it would work to solve it. I'll give it some spike time later and mess with it :)

Thanks again @groue. Great discussion! 💯

@groue

This comment has been minimized.

Copy link
Owner

commented Jul 24, 2016

But that is definitely a matter of taste and preference and I totally respect and value your opinion on fields of responsibility for a Model.

More or less. I claim that you will have less problems and frustrations following the GRDB way to doing thing: this lib addresses years of problems I had with other database APIs (don't worry, I'm much more humble than I sound). GRDB surely gives a little more work to the developer - especially since there are gaping holes, like associations, in the API. Yet, quoting yourself on Twitter:

Is it possible contacting you directly for a question or two before I go ahead and test GRDB more in depth?

Right on time :-) depth is the distinct advantage of GRDB, compared to its peer Swift database libs ;-)

The first option is nice because it gives control, but it also means that I fetch data from the database every time I want to get the products, which is great for making sure I always have the latest data, but not great if I want an Order at a specific state and want to "pass it around" between ViewControllers (I'll have store the products somewhere else when fetching them, instead as part of the model itself).

Yes, you're right. Hmmm... I think I'd just store the fetched products in the order, and move on :-)

Anyways now I get more clarity on what GRDB should and should not be used for, and for sure this is a very different approach than a more "Managed" solution, but I can see the appeal of it :) I'll find some time to dig deeper and see if it would be feasible to convert our current Realm usage to GRDB and see how easy it is to maintain.

I'm happy if I were able to clarify things. Questions like yours are great opportunities to settle concepts, pratices, and present the lib as a coherent whole :-) Migrating from Realm to GRDB will not be a light task. I'd be very proud if you would eventually migrate!

Thanks again @groue. Great discussion! 💯

Compliment returned :bowtie:

@groue

This comment has been minimized.

Copy link
Owner

commented Jul 27, 2016

@groue groue closed this Jul 27, 2016

@freak4pc

This comment has been minimized.

Copy link
Author

commented Jul 28, 2016

Love it ! Thanks @groue !

@groue groue added the best practices label Dec 8, 2016

@groue

This comment has been minimized.

Copy link
Owner

commented Jun 3, 2018

A new guide has shipped in GRDB 3: Good Practices for Designing Record Types.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.