Abstract: propose a solution that offers a consistent, performant, and reliable method of storing and retrieving complex data-types from an SQL database.
Application planning is a crucial and often overlooked step in building successful web applications. The Rails framework makes it so easy to create and bootstrap an application that it’s tempting for developers to skip planning and jump right to development. However, generating application code before properly planning the domain can lead to sloppy or even malformed applications. Consider, for instance, the best method to handle the “address” field in an application.
What’s in an address?
Here’s what a typical address looks like in the United States:
Address: [Street: “123 Happy Days Lane” StringCity: “Wonderland” StringState: “PA” StringZip: 10102 String]
The “Address” value consists of an array of key/value pairs, where the value is a string. It’s a very common field to see in applications. So, what’s the best way to represent the “Address” value in an SQL database? To figure this out let’s look at what other developers do in this situation.
Option 1: Store the value in a single field as a string, for example:
Address: “123 Happy Days Ln. Wonderland, PA 10102”
This is a poor solution. As we have seen in the section above, an address is an array of key/value pairs. To treat the address like any other scalar field would be inconsistent. This inconsistency becomes apparent when we want to extend the application at some point in the future. Let’s say we want to find all of the customers from a given “City” or “State”. Because the entire value of “Address” has been squished inside a string there’s no longer an easy way to do this.
Option 2: Break the “Address” value into distinct fields and store those fields, for example:
Street: “123 Happy Days Ln”, City: “Wonderland”, State: “PA”, Zip: “10102”
Here we have solved our previous hypothetical extension of finding all customers of a particular “City” or “State”. However, we’re now representing the “Address” field as four separate and distinct fields when, in fact, the fields should be combined together.
Option 3: Create a separate Address table and foreign key association
Several blog posts and StackOverflow answers conclude that the best way to store addresses in Rails is to use a separate model and a
belongs_to association. In this way, a User or Business model could
has_many addresses and not be limited to a single address. Also, the addresses would be properly stored as each “Address” model would consist of its keys and respective values. This isn’t a poor solution, assuming that your application model, for example, “Users”, calls for several addresses. However, if each model needs only one address (as is the case for the majority of people, a mailing address) then by adding additional tables and associations you’re needlessly increasing the complexity of the application. It’s quite simple to generate tables and associations in Rails, which is likely the reason behind this third option. To create a new "Address” table with the proper association to “User” is as simple as:
rails g model Address street:string city:string state:string zip:string user:references The question then becomes, “is this a best practice for your application, or is this a simple solution to a more complex issue?”
Rails/PostgreSQL is a common technology stack used in development and production. There is a method to store addresses in their proper form by enabling the ‘hstore’ extension. This extension allows us to save data as the ‘hstore’ (hash store) type. The first step is to enable the ‘hstore’ module. To do this, we’ll write a migration:
When we run ‘rails db:migrate’ from the command line, Postgres will enable the ‘hstore’ extension. This can be verified by checking the
schema.rb file. Near the top of the file you should now see:
# These are extensions that must be enabled in order to support this databaseenable_extension “plpgsql”
Next we’ll create a migration for the User model:
Notice how we use the data type ‘hstore’ for the address field. This allows us to store key/value pairs within “address”.
Aside: The ‘hstore’ field can be used to store any key/value pairs. In this article we are exploring an “Address” but it is just as useful for storing “Preferences” or any other hash data type.
So, now we have a single field that we know can store data in the form of, City => “Wonderland” and State => “PA”. We can create the ‘User’ model and ‘Users’ controller but how do we actually save key/value pairs to a single database field? There is one other consideration to make, how can we persist the key/value pair of City => “Wonderland” to the field of “address”?
From the perspective of the controller, we must first whitelist any parameters before they can be mass assigned. This is done in the private method ‘user_params’, the same as any other database field.
By whitelisting what can be assigned under address, we’ve provided specific instructions as to the data we are expecting. The address field can have a city/value pair, or a state/value pair, but not a monkey/value pair.
In our User form, we provide the proper fields to collect the data:
The entire form is shown for demonstration. However, the start and end of the address field are marked with html comments. Notice how we use the form helper to create fields for the address. Each field corresponds to a whitelisted parameter we specified in the controller. Though it isn’t pertinent to this article, the form is styled with classes from the Semantic-UI library — which means it’s going to be responsive and look awesome!
We can quickly create a user and see how the “Address” field looks:
In the image above, the term “subject” is synonymous with “user”. As you can see the “Address” field has saved our key/value pairs from the controller just like we expected. If we want to find the value of a particular user’s city we can use,
@user.address['city'] and this would return “Wonderland” in our example use case.
In this article we discovered the optimum method for saving and querying complex data types in PostgreSQL via the ‘hstore’ extension. We’ve determined that as a best practice the first steps in creating a web application are planning and gathering information. We also learned the value of discovering information ourselves by researching and reading documentation. Just because another developer or company does something one way doesn’t mean that we should blindly follow. It’s important to question “why” and to use our own cognitive abilities to logically draw conclusions. I hope you found this article helpful. For more free information and tutorials on web technologies visit Learn2Code.