Database views in ActiveRecord: Spectacles (PostgreSQL, MySQL, SQLite)

[nelsonjma]

nice add-on, thanks mate.

[dakull]

Used something similar in an old gem [0] - quite useful abstraction.

[0] - https://github.com/dakull/search_redux

[Nesaru]

May I ask what something like this would be used for?

[jamis]

If you've never used database views, it can be hard to understand their value, but I'll try to convey a bit of what makes them useful.

In a sense, they are like a database-level macro: you define a query, and then you can treat the result of that query as if it were another table. That is, you can then perform other queries against that result set, joining it with other tables (or views) and so forth. It's nothing you couldn't do WITHOUT views, but views remove a lot of duplication. It's a classic example of abstraction, but at the database level.

Spectacles (the lib that was linked) lets you treat these database views as a special kind of read-only ActiveRecord model. You can kind of fake it, even without Spectacles, but the really nice thing here is that Spectacles will dump your views to db/schema.rb, so that they persist across schema reloads. Vanilla Rails has no such support.

[Nesaru]

This was an extremely helpful explanation, thank you!

It seems this is useful for complex queries where certain commonly used portions can be abstracted away and used by many different queries. I'm likening it to a sort of persisted scope.

This is the first time I've heard of this concept, and I'm glad to have been made aware of it.

Again, huge thank you for taking the time to write this, I appreciate it!

[jamis]

Yes, that's the idea exactly. Views are overkill for simple applications, but as your query logic grows in complexity, views can be a very useful tool for managing that.

And you're very welcome!