This means we can model the assignees of an issue as an array of JSON objects, instead of using a join table: SQlite JSON support to the rescue!Īs mentioned in the intro of the article, SQlite has built-in support to query columns that contain JSON documents (support was added in SQlite 3.9) through the JSON1 extension. ORM systems do a fair amount of work under the covers to deduplicate these results and map them into a single object. That happens because of our JOIN statement, which leads the query to return the entire issue for each issue assignee found in the issue_assignee table. But the result of this query is a lot harder to consume than in the past:Įven though we queried an invididual issue, we now receive two result rows. SELECT * from issues JOIN issueassignees ON issueassignees. Here’s the query to fetch an individual issue by its UUID: The relational approach reduces data locality (not all information about an issue is located in one place anymore) which adds complexity to our application. In the past it was sufficient to select all columns ( SELECT *), now we need to join with the issue_assignee table to get a full representation of an issue into memory. The real additional complexity lies in building a query that servers our typical query pattern: fetching an entire record by its UUID. I briefly prototyped an implementation of relationships that joined the underlying tables to fetch multiple records at a time ( probably a great use case for the new KeyPath API), but I quickly realized that this approach would add a lot of complexity to our DB layer. Further, the query pattern of our iOS app tends to be such that we only fetch one type of record per query, which makes modeling relationships unnecessary. Creating a simple, intuitive API for modeling DB relationships is hard ( you might have struggled to understand Core Data faults in the past). That system doesn’t support modeling relationships. On iOS we’re not using an existing ORM system, instead we have a small library for mapping Swift structs to tables. Modelling relationships in the iOS appĪll of our mobile apps use SQlite as a local database. For these reason we chose to use a join table on our backend Postgres database. Additionally we can work with UNIQUE constraints to ensure the database doesn’t allow the same assignee to be added to an issue multiple times. No information about users or issues is duplicated. The advantage of this approach is that the data is entirely normalized. In a relational database one would typically add multiple assignees by introducing a join table: As of today the assignee is a single field that stores a UUID which references the assigned user. In my current project I’m working extending the PlanGrid app to support multiple assignees on issues. Let’s jump right into the practical example I am facing to demonstrate these tradeoffs. Throughout this post I want to show the tradeoffs between a traditional relational data model that relies on joins and one that combines relations and document-oriented storage to improve data locality. The tree shaped structure of documents is often more suitable to represent serialized objects. More recently, document-oriented database systems have been on the rise. Many applications map an object graph into a relational database model using some form of Object-relational mapping system. In software development (specifically in object oriented programming) developers often face an impedance mismatch between the way data is modelled in code and how it is persisted on disk. He touches on interesting trend: Some of the most popular database systems such as PostgreSQL and SQlite now offer APIs to store and query JSON documents, making them multi-paradigm and bringing a lot of the benefits of document based database systems to traditionally relational ones.Ĭoincidentally I’m currently working on a feature that requires me to model a one-to-many relationship in SQlite, which lead me to try it’s JSON support. In the earlier chapters of the book the author (one of the main contributors to Apache Kafka) discusses the history of database models and tradeoffs between document, relational and graph databases. I’m currently reading a great book on designing data intensive applications. Modeling one-to-many in SQlite using the JSON1 extension
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |