Hacker News Reader

Comment by lukes386 | Hacker News Reader

On a somewhat related note, I'd love to see a more intermediate resource on how to really leverage PostgreSQL in your applications.

As someone who primarily works with Rails, I feel like I use about 1% of PostgreSQL's power.

View on HN

Which is how Rails wants you to use PostgreSQL. Their opinion is that logic belongs in the app, not the database.

In general this is true, but with Rails 4, we added a lot of Postgres specific features to ActiveRecord, so you can actually take advantage of all the awesomeness Postgres has to offer.

Check out this blog post: http://blog.remarkablelabs.com/2012/12/a-love-affair-with-po...

Has the actual philosophy changed though?

When I first got to Rails it seemed that databases were considered as a bothersome but unavoidable necessity; flat files with a funny accent, instead of an essential and powerful ally in the fight against entropy and error.

In the past several years Postgres and NoSQL data stores have gained in popularity and become mainstream, and Rails now supports them well. The Rails ecosystem historically focused on MySQL, but that's not the case anymore. For example, Engine Yard now uses Postgres by default, and Heroku always has.

I basically remember head-scratching about the worthiness of exotic features like foreign keys.

Some poking around in the current documentation seems to suggest that FKs have been absorbed into ActiveRecord.

That looks mostly like you've added support for postgres "extras", not (inherent) "postgres goodness". Not that adding support for "extras" is a bad thing -- but in the context of "adding support for postgres sql" -- that seems somewhat orthogonal? (I'm not saying it is wrong for rails to approach postgres this way, just that the approach doesn't seem to change the way rails (afaik) approach databases: logic should be in rails, not in stored procedures and views (which is where it would make sense to place logic, if you design with "database first")).

Interesting link, either way.

Yes, that's true. The approach has it's drawbacks though (validates_uniqueness_of being a prime example).

As much as I like Rails, I sometimes wish it were designed with the database playing more of a role than just dumb storage. Defining things like validations in Ruby is certainly nice and easy, but the database is much better suited to actually enforcing such constraints.

Can I a question I haven't been able to answer myself?

In your webapp using POSTGRESQL, are you actually using SQL statements in your code to retrieve the data? Or is there some other way to get at it?

I think the search term you are looking for is O.R.M. Object Relational Mapping.

Rather than me explaining it here, the rails guides[1] give you a good overview.

[1] http://guides.rubyonrails.org/active_record_querying.html

A few ways I leverage Postgres in Rails:

- CHECK constraints. I have a gem for setting up foreign keys and CHECK constraints in Rails:

    https://github.com/pjungwir/db_leftovers

- named scopes. You can include SQL snippets like this:

    class Publisher
      scope :with_no_books, -> {
        where <<-EOQ
          NOT EXISTS (SELECT 1
                      FROM   books b
                      WHERE  publishers.id = b.publisher_id)
        EOQ
      }
    end

Of course you can do this with MySQL too, but it's one way to nicely incorporate arbitrary SQL with your ORM, so that you can take advantage of any Postgres feature you want.

- PostGIS: http://blog.daniel-azuma.com/archives/60

- hstore/json columns.

Reply on news.ycombinator.com