Rapid Data Modeling Using ActiveRecord and the JSON Data Type

by David Furber

In this video presentation titled "Rapid Data Modeling Using ActiveRecord and the JSON Data Type," David Furber discusses how to efficiently handle extensive data modeling in Rails applications by utilizing PostgreSQL's JSON column feature. The discussion begins with Furber sharing his background and his experiences with various projects, particularly in academic settings where Rails is frequently employed.

Key points covered include:

Foundational Tools: Furber emphasizes the importance of tools like Simple Form for simplifying forms and Slim for templating, which reduce clutter in Rails applications.
Introduction to JSON in PostgreSQL: He describes how using PostgreSQL's JSON data type allows developers to create flexible, schema-less data models that avoid the limitations of traditional relational schemas.
Case Study: Era GATS Project: Furber shares his experience working on a project for archaeologists collecting artifacts in Armenia, which started with Microsoft Access and Excel. He highlights how a proper database was necessary for their growing data needs and how he implemented a JSON structure to efficiently manage numerous artifact attributes.
Creating a Flexible Model: He explains how this JSON functionality allows for a unified model that can handle varying attributes without creating multiple columns, leading to simplified searches and easier data manipulation.
Integration with ActiveRecord: Furber demonstrates how to combine JSON attributes with traditional ActiveRecord querying, thus providing developers with powerful methods for managing data.
Additional Use Cases: Further, he talks about another project involving the Inclusive Recreation Resource Center, where the JSON column allowed for quick adaptation of assessment forms, showcasing the versatility of this approach.

In conclusion, Furber highlights the significance of adapting to evolving project needs while maintaining efficient data organization. He encourages developers to explore PostgreSQL and Rails' capabilities for rapid data modeling and invites feedback on his own solutions for improving JSON data handling in Rails applications. This presentation underscores the benefits of using modern database features to enhance development workflows and manage complex data relationships more effectively.

00:00:11.420 Thank you for coming to my talk. I am David Furber and I live in Ithaca, New York. I work for a software consulting company called Gorgeous.

00:00:16.529 Ithaca is a small city in upstate New York, a bus ride away from New York City, and is the home of Cornell University and Ithaca College. We often get a lot of business from there, mainly academic projects which tend to be small to medium-sized, perfect for Rails development.

00:00:39.420 Today, I'm going to talk about rapid data modeling in ActiveRecord, particularly using the JSON data type in PostgreSQL. Alternatively, the title could be how I sped up my Rails development by fitting a document store into my ActiveRecord models. I'll tell a story of some projects that led me to this approach.

00:01:01.879 Before diving into rapid data modeling, I want to mention some foundational tools and practices I find essential. I am a big fan of Simple Form and exploring how to go beyond the basics in the README to make forms simpler. I also appreciate Slim for templating as it reduces clutter and allows for the inheritance of views in Rails, minimizing the number of view files. Furthermore, I like to encapsulate common components, such as CKEditor, in engines so that they can be reused across projects without the build lag often encountered when deploying to Heroku.

00:02:00.210 When discussing rapid data modeling with PostgreSQL, I approach it not merely for prototyping but to create usable, substantial applications. It's crucial to keep learning, even if you've been using Rails for a long time, as the framework continually evolves. I have found that keeping informed about new additions in Rails 4, for example, has greatly aided in solving specific problems.

00:02:21.850 Lately, I got involved with a project called Era GATS. Like many projects I encounter, it originated from a grant at Cornell. The project is linked to the archaeology department, where two archaeologists have been visiting Armenia annually since 1995 to conduct fieldwork and collect artifacts. Initially, they managed their findings through Microsoft Access and Excel, but it quickly became evident that a proper database system was needed to handle their growing volumes of data.

00:02:56.680 These archaeologists wanted to describe and search their collected data more efficiently and make it publicly accessible to fellow researchers. As we worked on this project, we discovered that they had taken a drone to Armenia to help document their findings. The drones were particularly useful in mapping archaeological landscapes, allowing them to survey potential dig sites and select which ones to excavate each year.

00:03:43.660 This application would be used to catalog artifacts collected from various sites. Archaeologically, they gather items like pottery and other artifacts, placing them into bags and boxes at the dig site before later cataloging them at camp. They would analyze their findings, make observations about the artifacts (like if they were from the Bronze Age), and store related metadata.

00:05:01.520 Each artifact requires detailed documentation, with numerous attributes being relevant, such as material type, dimensions, and any unique physical features. Realizing this from initial project setups, I recognized the need to create a flexible, schema-less system using PostgreSQL's JSON column.

00:05:37.750 I began by exploring how to effectively create a model that could handle a single table with diverse attributes using ActiveRecord. At first, the proposed schema placed each attribute in its own table, but I soon learned about using JSON data types in PostgreSQL that would inherently allow for more flexibility and ease of access.

00:06:10.840 Using this JSON functionality, I was able to wrap it around ActiveRecord and create a common base model for the artifacts without needing to create multiple columns for each variable property. This design allowed me to maintain the relationships and inherit traits while utilizing the advantages of JSON.

00:06:57.820 With the JSON column approach, I can implement methods for performing searches based on key-value pairs in the data, effectively leveraging PostgreSQL's capabilities.

00:07:16.140 After settling into this pattern with the Rails environment, I began developing a comprehensive search interface using Ransack, which allowed me to dynamically create queries based on the filter criteria applied to the JSON data.

00:07:43.520 This way, I achieved a high-level integration of the JSON document store while maintaining the functionalities one would expect from traditional ActiveRecord querying. I noticed here that I can store attributes like dimensions, material types, and other data in a flexible, typecasted manner.

00:08:55.300 Moving forward, integrating both a standard set of attributes and the flexible JSON structure allows for simplifying forms related to the documents. This became evident in handling the multiple types of attributes associated with archaeological findings.

00:09:38.499 I reflected on how the models should be structured, preserving the principles of domain-driven design while crafting an architecture that allows easy manipulation of interrelated data components.

00:10:29.620 Ultimately, this plan culminated in an adaptable system capable of evolving with future project needs while remaining streamlined and efficient in handling various data forms.

00:10:59.200 As I further explored, the intersection of JSON capabilities and ActiveRecord structures paved the way for more complex relationships.

00:11:50.300 Later, while working on another project for the Inclusive Recreation Resource Center based out of SUNY Cortland, I observed how quickly forms could be adapted to include new data fields through the JSON column.

00:12:42.880 The project involved a significant amount of assessment forms for various physical locations and infrastructures, each possessing unique attributes already identified during previous iterations.

00:13:05.680 This experience underscored the importance of organizing data in a way that makes it easier to manage recurrent themes across diverse document types.

00:13:52.300 Interfacing the JSON fields with accessible models fostered a seamless user experience, particularly in how the forms designed around values tied to outdoor accessibility information were processed.

00:14:30.300 In summary, by leveraging JSON and combined models, I can enhance how developers manage data attributes and efficiently fulfill project requirements across different scenarios.

00:15:03.000 As I conclude, I'm keen to integrate feedback on the docstore gem I developed, as this encapsulates my learnings and experiences from these projects, facilitating a well-structured JSON data handling within Rails.

00:15:39.000 Thank you for joining my discussion on rapid data modeling with ActiveRecord and the JSON data type. I encourage you to explore the possibilities within PostgreSQL and Rails, and I welcome your thoughts or questions on the matter.