Description
Scenic adds methods to ActiveRecord::Migration to create and manage database
views in Rails.
Using Scenic, you can bring the power of SQL views to your Rails application
without having to switch your schema format to SQL. Scenic provides a convention
for versioning views that keeps your migration history consistent and reversible
and avoids having to duplicate SQL strings across migrations. As an added bonus,
you define the structure of your view in a SQL file, meaning you get full SQL
syntax highlighting in the editor of your choice and can easily test your SQL in
the database console during development.
Scenic ships with support for PostgreSQL. The adapter is configurable (see
Scenic::Configuration) and has a minimal interface (see
Scenic::Adapters::Postgres) that other gems can provide.
Scenic alternatives and similar gems
Based on the "Database Tools" category.
Alternatively, view Scenic alternatives based on common mentions on social networks and blogs.
-
Database Cleaner
Strategies for cleaning databases in Ruby. Can be used to ensure a clean state for testing. -
Active Record Doctor
Identify database issues before they hit production. -
Lol DBA
lol_dba is a small package of rake tasks that scan your application models and displays a list of columns that probably should be indexed. Also, it can generate .sql migration scripts. -
Foreigner
Adds foreign key helpers to migrations and correctly dumps foreign keys to schema.rb -
Seed Fu
Advanced seed data handling for Rails, combining the best practices of several methods together. -
Seedbank
Seedbank gives your seed data a little structure. Create seeds for each environment, share seeds between environments and specify dependencies to load your seeds in order. All nicely integrated with simple rake tasks. -
DatabaseConsistency
The tool to avoid various issues due to inconsistencies and inefficiencies between a database schema and application models. -
Polo
Polo travels through your database and creates sample snapshots so you can work with real world data in development. -
SchemaPlus
SchemaPlus provides a collection of enhancements and extensions to ActiveRecord -
Upsert
Upsert on MySQL, PostgreSQL, and SQLite3. Transparently creates functions (UDF) for MySQL and PostgreSQL; on SQLite3, uses INSERT OR IGNORE. -
OnlineMigrations
Catch unsafe PostgreSQL migrations in development and run them easier in production (code helpers for table/column renaming, changing column type, adding columns with default, background migrations, etc). -
QueryTrack
Find time-consuming database queries for ActiveRecord-based Rails Apps -
Ruby Whatsapp SDK
A lightweight, efficient Ruby gem for interacting with Whatsapp Cloud API. -
Ruby PG Extras
Ruby PostgreSQL database performance insights. Locks, index usage, buffer cache hit ratios, vacuum stats and more. -
ocran
Turn ruby files into .exe files on windows (supported safe fork of ocran) -
PgDriveBackup
Simple solution to make encrypted with ccrypt PostgreSQL backups and storing on Google Drive API -
Perfect Shape
Perfect Shape is a collection of geometric algorithms that are mostly useful for GUI manipulation like checking containment of a point in popular geometric shapes such as rectangle, square, arc, circle, polygon, and paths containing lines, quadratic bézier curves, and cubic bezier curves. Also, some general math algorithms like IEEE-754 Remainder. -
Slack Smart Bot
Create a Slack bot that is smart and so easy to expand, create new bots on demand, run ruby code on chat, create shortcuts... The main scope of this gem is to be used internally in the company so teams can create team channels with their own bot to help them on their daily work, almost everything is suitable to be automated!! slack-smart-bot can create bots on demand, create shortcuts, run ruby code... just on a chat channel. You can access it just from your mobile phone if you want and run those tests you forgot to run, get the results, restart a server... no limits. -
ActiveRecord Setops
Union, Intersect, and Difference set operations for ActiveRecord (also, SQL's UnionAll). -
PgDice
Postgres partitioning built on top of https://github.com/ankane/pgslice -
ActiveRecord::DataIntegrity
Check data integrity for your ActiveRecord models -
Rapidity
Simple but fast Redis-backed distributed rate limiter. Allows you to specify time interval and count within to limit distributed operations. -
bundler-download
Bundler plugin for auto-downloading specified extra files after gem install -
PRY-BYETYPO 👋
A Pry plugin that captures exceptions that may arise from typos and deduces the correct command.
PopRuby: Clothing and Accessories for Ruby Developers
* Code Quality Rankings and insights are calculated and provided by Lumnify.
They vary from L1 to L5 with "L5" being the highest.
Do you think we are missing an alternative of Scenic or a related project?
Popular Comparisons
README
Scenic
Scenic adds methods to ActiveRecord::Migration
to create and manage database
views in Rails.
Using Scenic, you can bring the power of SQL views to your Rails application without having to switch your schema format to SQL. Scenic provides a convention for versioning views that keeps your migration history consistent and reversible and avoids having to duplicate SQL strings across migrations. As an added bonus, you define the structure of your view in a SQL file, meaning you get full SQL syntax highlighting in the editor of your choice and can easily test your SQL in the database console during development.
Scenic ships with support for PostgreSQL. The adapter is configurable (see
Scenic::Configuration
) and has a minimal interface (see
Scenic::Adapters::Postgres
) that other gems can provide.
So how do I install this?
If you're using Postgres, Add gem "scenic"
to your Gemfile and run bundle
install
. If you're using something other than Postgres, check out the available
third party adapters.
Great, how do I create a view?
You've got this great idea for a view you'd like to call search_results
. You
can create the migration and the corresponding view definition file with the
following command:
$ rails generate scenic:view search_results
create db/views/search_results_v01.sql
create db/migrate/[TIMESTAMP]_create_search_results.rb
Edit the db/views/search_results_v01.sql
file with the SQL statement that
defines your view. In our example, this might look something like this:
SELECT
statuses.id AS searchable_id,
'Status' AS searchable_type,
comments.body AS term
FROM statuses
JOIN comments ON statuses.id = comments.status_id
UNION
SELECT
statuses.id AS searchable_id,
'Status' AS searchable_type,
statuses.body AS term
FROM statuses
The generated migration will contain a create_view
statement. Run the
migration, and baby, you got a view going. The migration is reversible
and the schema will be dumped into your schema.rb
file.
$ rake db:migrate
Cool, but what if I need to change that view?
Here's where Scenic really shines. Run that same view generator once more:
$ rails generate scenic:view search_results
create db/views/search_results_v02.sql
create db/migrate/[TIMESTAMP]_update_search_results_to_version_2.rb
Scenic detected that we already had an existing search_results
view at version
1, created a copy of that definition as version 2, and created a migration to
update to the version 2 schema. All that's left for you to do is tweak the
schema in the new definition and run the update_view
migration.
What if I want to change a view without dropping it?
The update_view
statement used by default will drop your view then create
a new version of it.
This is not desirable when you have complicated hierarchies of views, especially when some of those views may be materialized and take a long time to recreate.
You can use replace_view
to generate a CREATE OR REPLACE VIEW SQL statement instead by adding the --replace_view
option to the generate command:
$ rails generate scenic:view search_results --replace_view
create db/views/search_results_v02.sql
create db/migrate/[TIMESTAMP]_update_search_results_to_version_2.rb
See Postgres documentation on how this works: http://www.postgresql.org/docs/current/static/sql-createview.html
The migration will look something like this:
class UpdateSearchResultsToVersion2 < ActiveRecord::Migration
def change
replace_view :search_results, version: 2, revert_to_version: 1
end
end
You can run the migration and the view will be replaced instead.
Can I use this view to back a model?
You bet! Using view-backed models can help promote concepts hidden in your relational data to first-class domain objects and can clean up complex ActiveRecord or ARel queries. As far as ActiveRecord is concerned, a view is no different than a table.
class SearchResult < ApplicationRecord
belongs_to :searchable, polymorphic: true
# this isn't strictly necessary, but it will prevent
# rails from calling save, which would fail anyway.
def readonly?
true
end
end
Scenic even provides a scenic:model
generator that is a superset of
scenic:view
. It will act identically to the Rails model
generator except
that it will create a Scenic view migration rather than a table migration.
There is no special base class or mixin needed. If desired, any code the model generator adds can be removed without worry.
$ rails generate scenic:model recent_status
invoke active_record
create app/models/recent_status.rb
invoke test_unit
create test/models/recent_status_test.rb
create test/fixtures/recent_statuses.yml
create db/views/recent_statuses_v01.sql
create db/migrate/20151112015036_create_recent_statuses.rb
What about materialized views?
Materialized views are essentially SQL queries whose results can be cached to a table, indexed, and periodically refreshed when desired. Does Scenic support those? Of course!
The scenic:view
and scenic:model
generators accept a --materialized
option for this purpose. When used with the model generator, your model will
have the following method defined as a convenience to aid in scheduling
refreshes:
def self.refresh
Scenic.database.refresh_materialized_view(table_name, concurrently: false, cascade: false)
end
This will perform a non-concurrent refresh, locking the view for selects until
the refresh is complete. You can avoid locking the view by passing
concurrently: true
but this requires both PostgreSQL 9.4 and your view to have
at least one unique index that covers all rows. You can add or update indexes for
materialized views using table migration methods (e.g. add_index table_name
)
and these will be automatically re-applied when views are updated.
The cascade
option is to refresh materialized views that depend on other
materialized views. For example, say you have materialized view A, which selects
data from materialized view B. To get the most up to date information in view A
you would need to refresh view B first, then right after refresh view A. If you
would like this cascading refresh of materialized views, set cascade: true
when you refresh your materialized view.
I don't need this view anymore. Make it go away.
Scenic gives you drop_view
too:
def change
drop_view :search_results, revert_to_version: 2
drop_view :materialized_admin_reports, revert_to_version: 3, materialized: true
end
FAQs
Why do I get an error when querying a view-backed model with find
, last
, or first
?
ActiveRecord's find
method expects to query based on your model's primary key,
but views do not have primary keys. Additionally, the first
and last
methods
will produce queries that attempt to sort based on the primary key.
You can get around these issues by setting the primary key column on your Rails model like so:
class People < ApplicationRecord
self.primary_key = :my_unique_identifier_field
end
Why is my view missing columns from the underlying table?
Did you create the view with SELECT [table_name].*
? Most (possibly all)
relational databases freeze the view definition at the time of creation. New
columns will not be available in the view until the definition is updated once
again. This can be accomplished by "updating" the view to its current definition
to bake in the new meaning of *
.
add_column :posts, :title, :string
update_view :posts_with_aggregate_data, version: 2, revert_to_version: 2
When will you support MySQL, SQLite, or other databases?
We have no plans to add first-party adapters for other relational databases at this time because we (the maintainers) do not currently have a use for them. It's our experience that maintaining a library effectively requires regular use of its features. We're not in a good position to support MySQL, SQLite or other database users.
Scenic does support configuring different database adapters and should be extendable with adapter libraries. If you implement such an adapter, we're happy to review and link to it. We're also happy to make changes that would better accommodate adapter gems.
We are aware of the following existing adapter libraries for Scenic which may meet your needs:
Please note that the maintainers of Scenic make no assertions about the quality or security of the above adapters.
About
Scenic is maintained by Derek Prior, Caleb Hearth, and you, our contributors.