Rails Best Practice — Persisting Addresses in PostgreSQL

The ‘hstore’ extension implements the ‘hstore’ data type for storing sets of key/value pairs

Abstract: propose a solution that offers a consistent, performant, and reliable method of storing and retrieving complex data-types from an SQL database.

What’s in an address?

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?”


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”
enable_extension “hstore”

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.

Notice the ‘address’ field is an array of symbols, [:street_address, :city, :state, :zip]

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:

Notice the comments where the address field begins and ends

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!

Above form displayed in browser

We can quickly create a user and see how the “Address” field looks:

The address field is saved as a hash of key/value pairs, perfect!

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.

Developer / Instructor