Replace MongoDB with Postgres

[monfresh]

One of our goals this year is to reduce dependencies. We originally used MongoDB because of its ease of use, especially when we trying out different ways to model the data. Now that we've settled on a structure, we think Postgres is the best tool for the situation, given the relational nature of the data.

[monfresh]

The initial migration is complete and is available in the postgres branch. However, this is not yet ready to be merged to master because I want to take advantage of the native Postgres array type, which is only supported in Rails 4. Therefore, the next steps are:

• Upgrade from FriendlyId 4 to 5
• Upgrade to Rails 4
• Add new migrations for all the array fields
• Remove old array fields
• Rename new array fields

[migurski]

This is great work!

What will be in the array fields?

[spara]

There are a number fields that are enumerations such as service areas or disability services. Basically anything that is an enumeration.

[monfresh]

Yep, all attributes that are just an array of strings. Look for "array of strings" in this Wiki: https://github.com/codeforamerica/ohana-api/wiki/Populating-the-Postgres-database-from-a-JSON-file

For example, a Location might have many admin_emails. Instead of creating an additional table for Admin Email that would only have one column (besides the foreign key), I think it's better to just use the Postgres array type for that attribute.

[monfresh]

I should note that in Rails, you aren't limited to choosing between using Postgres array and having an association table. You can also serialize the text field, which is what I'm currently doing in the postgres branch with Rails 3.2. I could continue doing that, but from what I've read, Postgres array is better in terms of database design and performance.

[migurski]

Serializing may be better in terms of portability.

[monfresh]

So, it turns out pg arrays + Rails is not quite ready for prime time yet. I found a bug, which seems to have been acknowledged by the Rails team as they haven't dismissed it: rails/rails#14716

So for now, we'll just stick with serialization and can convert to Postgres arrays later if we want to.

[monfresh]

This is now finally done and merged. I also created a release for this milestone: https://github.com/codeforamerica/ohana-api/releases/tag/v2.0.0

[migurski]

🍻

[jasonlally]

Great work @monfresh do I follow the same deployment local & heroku documentation for the new postgres version?

[jasonlally]

Nevermind, I think I answered my own question...

[monfresh]

Hi @jasonlally. You will need to start over with all the repos. Please read all the READMEs carefully as many things have changed. As far as loading the Postgres DB, the process hasn't changed that much. You can still use the same script you used during the Hacktivation, but with a few changes to some field names. This Wiki article covers it all: https://github.com/codeforamerica/ohana-api/wiki/Populating-the-Postgres-database-from-a-JSON-file

Let me know how it goes!