Share
Embed
Sequel: When ActiveRecord is not Enough
As Rails developers, using ActiveRecord as our database library is a given. As our application grows, we start noticing that some of our new requirements aren't exactly supported by the ActiveRecord API. So, we pull up our sleeves and write that raw SQL snippet, re-create some queries without models to get that extra performance gain, or work around that bug in the library, and move on. But gaining this technical debt isn't necessary if you're using a library that supports your advanced use cases. Enter Sequel, an alternative ORM for Ruby. In this talk we will show some advantages that Sequel has over Active Record, covering topics like the query API, building SQL expressions (Arel), connection pools, immutability and more.
Janko Marohnić
September 16, 2020
Transcript
81 slides
-
Janko Marohnić • @janko on GitHub, @jankomarohnic on Twitter •
born in Croatia , live in Czechia • Ruby-off-Rails evangelist • gem author (Shrine, rodauth-rails, Down, ImageProcessing, ...) • 3+ years of Sequel in production -
• created by Sharon Rosner in 2007 • maintained by
Jeremy Evans since 2008 • implements Active Record pattern • powers rom-sql Sequel 2004 Active Record Sequel 2007 -
require "active_record" ActiveRecord!::Base.establish_connection( adapter: "postgresql", database: "rubyday") ActiveRecord!::Schema.define do create_table
:movies do |t| t.string :title t.integer :year end end class Movie < ActiveRecord!::Base end Movie.create(title: "Matrix", year: 1999) -
require "sequel" DB = Sequel.connect( adapter: "postgres", database: "rubyday") DB.create_table
:movies do primary_key :id String :title Integer :year end class Movie < Sequel!::Model end Movie.create(title: "Matrix", year: 1999) -
ActiveRecord!::Base.establish_connection( adapter: "postgresql", database: "mydb", ) class Article < ActiveRecord!::Base
end Article #!=> Article (call Article.connection to establish a connection) -
ActiveRecord!::Base.establish_connection( adapter: "postgresql", database: "mydb", ) class Article < ActiveRecord!::Base
end Article #!=> Article (call Article.connection to establish a connection) -
ActiveRecord!::Base.establish_connection( adapter: "postgresql", database: "mydb", ) class Article < ActiveRecord!::Base
end Article.connection Article #!=> Article(id: integer, title: string, body: text, !!...) -
ActiveRecord!::Base.establish_connection( adapter: "postgresql", database: "mydb", ) class Article < ActiveRecord!::Base
end Article.connection Article #!=> Article(id: integer, title: string, body: text, !!...) Article.method_defined?(:title) #!=> false Article.method_defined?(:body) #!=> false -
ActiveRecord!::Base.establish_connection( adapter: "postgresql", database: "mydb", ) class Article < ActiveRecord!::Base
end Article.connection Article #!=> Article(id: integer, title: string, body: text, !!...) Article.new Article.method_defined?(:title) #!=> true Article.method_defined?(:body) #!=> true -
# actually establishes the connection DB = Sequel.connect(adapter: "postgres", database:
"mydb") # parses the schema and defines column methods class Article < Sequel!::Model end Article.method_defined?(:title) #!=> true Article.method_defined?(:body) #!=> true -
# app/controllers/movies_controller.rb class MoviesController < ApplicationController def index @movies =
Movie .where("rating > ?", Movie!::MIN_RATING) .where("title LIKE ?", "!#{params[:query]}%") .order(released_at: :desc) end end # app/views/movies/index.erb <% @movies.each do |movie| %> <%# !!... %> <% end %> SQL query executed in the view -
movies = Movie.where(!!...) movies #!=> #<ActiveRecord!::Relation> movies.to_a # SELECT *
FROM movies !!... movies.to_a # (returns cached results) movies.count # SELECT count(*) FROM movies !!... movies.size # (counts cached results) movies.loaded? #!=> true -
movies = Movie.where(!!...) movies #!=> #<Sequel!::Dataset> movies.to_a # SELECT *
FROM movies !!... movies.to_a # SELECT * FROM movies !!... movies.count # SELECT count(*) FROM movies !!... movies.count # SELECT count(*) FROM movies !!... movies.frozen? #!=> true ❄ -
movie.reviews #!=> [#<Review id=1>, #<Review id=2>, !!...] movie.reviews_dataset #!=> #<Sequel!::Dataset
!!...> movie.add_review({!!...}) movie.remove_review(!!...) movie.remove_all_reviews -
ActiveRecord!::Base.establish_connection(!!...) ActiveRecord!::Base.logger = Logger.new($stdout) ActiveRecord!::Base.connection.execute "some SQL query" ActiveRecord!::Base.transaction {
!!... } ActiveRecord!::Base.connection.tables #!=> [!!...] ActiveRecord!::Base.connection.schema_cache.columns(:table) #!=> [!!...] ActiveRecord!::Base.remove_connection -
DB = Sequel.connect(!!...) DB.logger = Logger.new($stdout) DB.run "some SQL query"
DB.transaction { !!... } DB.tables #!=> [!!...] DB.schema(:table) #!=> {!!...} DB.disconnect -
DB = Sequel.connect(adapter: "postgres", database: "primarydb") DB.run "some SQL query"
DB2 = Sequel.connect(adapter: "mysql2", database: "anotherdb") DB2.run "another SQL query" -
# implicit database class Movie < Sequel!::Model # !!... end
# explicit database class Review < Sequel!::Model(DB) # !!... end # another database class Episode < Sequel!::Model(DB2) # !!... end -
Movie.where(!!...).order(!!...).select(!!...).all #!=> # [ # #<Movie @values={id: 13, title: "Foo",
!!...}>, # #<Movie @values={id: 52, title: "Bar", !!...}>, # !!... # ] -
DB[:movies].where(!!...).order(!!...).select(!!...).all #!=> # [ # { id: 13, title: "Foo",
!!... }, # { id: 52, title: "Bar", !!... }, # !!... # ] -
ActiveRecord!::Base.ancestors # ActiveRecord!::Persistence # ActiveRecord!::Associations # ActiveRecord!::Callbacks # ActiveRecord!::Dirty #
ActiveRecord!::Timestamp # ActiveRecord!::Transactions # ActiveRecord!::NestedAttributes # ActiveRecord!::SecureToken # ActiveRecord!::SecurePassword # ActiveRecord!::Serialization # ActiveRecord!::Locking!::Optimistic # ActiveRecord!::Locking!::Pessimistic # !!... -
Sequel!::Model.ancestors # Sequel!::Model!::SingleTableInheritance!::InstanceMethods # Sequel!::Model!::Serialization!::InstanceMethods # Sequel!::Model!::Dirty!::InstanceMethods # Sequel!::Model!::Associations!::InstanceMethods #
Sequel!::Model!::InstanceMethods Sequel!::Model.plugin :dirty Sequel!::Model.plugin :serialization Sequel!::Model.plugin :single_table_inheritance, :type -
require "sequel" ≈ 13k LOC require "active_record" ≈ 24k LOC
+ 18k LOC activerecord activemodel activesupport concurrent-ruby i18n tzinfo psych ... -
Movie.where(year: 2000!..2020, rated: ["PG", "R"]) Movie.order(released_at: :asc) Movie.select(:id, :title, :plot,
:rated) Movie.where("year !>= ? OR rating * 10 <", 2000, 65) Movie.where("plot ILIKE ?", "%crime%") Movie.order("lower(title) ASC") m = Movie.arel_table Movie.where(m[:year].gteq(2000).or(m[:rating] * 10).gt(65)) Movie.where(m[:plot].matches("%crime%")) Movie.order(Arel!::Nodes!::NamedFunction.new("lower", [m[:title]]).asc) -
Movie.where(year: 2000, rated: ["PG", "R"]) Movie.order(:released_at) Movie.select(:id, :title, :plot, :rated)
Movie.where((Sequel[:year] !>= 2000) | (Sequel[:rating] * 10 < 65)) Movie.where(Sequel[:plot].ilike("%crime%")) Movie.order(Sequel.function(:lower, :title).asc) Movie.where { (yeah !>= 2000) | (rating * 10 < 65) } Movie.where { plot.ilike("%crime%") } Movie.order { lower(title).asc } -
create_table :accounts do String :email, null: false index :email, unique:
true, where: Sequel[status: %w[verified unverified]] # !!... end -
comment_results = DB[:statuses] .join(:comments, status_id: :id) .select(Sequel[:statuses][:id].as(:searchable_id), Sequel["Status"].as(:searchable_id), Sequel[:comments][:body].as(:term)) status_results
= DB[:statuses] .select(Sequel[:statuses][:id].as(:searchable_id), Sequel["Status"].as(:searchable_type), Sequel[:statuses][:body].as(:term)) DB.create_view :search_results, comment_results.union(status_results) -
• more verbose • internal API • not documented •
supported somewhere • more streamlined • public API • well documented • supported everywhere Arel Sequel -
Movie.includes(:reviews).to_a # SELECT * FROM movies # SELECT * FROM
reviews WHERE (id IN (!!...)) Movie.preload(:reviews).to_a # SELECT * FROM movies # SELECT * FROM reviews WHERE (id IN (!!...)) Movie.eager_load(:reviews).to_a # SELECT !!... # FROM movies # LEFT OUTER JOIN reviews ON (reviews.movie_id = movies.id) -
Movie.eager(:reviews).all # SELECT * FROM movies # SELECT * FROM
reviews WHERE (id IN (!!...)) Movie.eager_graph(:reviews).all # SELECT !!... # FROM movies # LEFT OUTER JOIN reviews ON (reviews.movie_id = movies.id) -
Movie.eager( reviews: !-> (dataset) { dataset.where(approved: true) } ).all #
SELECT * FROM movies # SELECT * FROM reviews WHERE (id IN (!!...) AND approved IS TRUE) -
Movie.one_to_many :top_reviews, class: :Review, order: Sequel.desc(:upvote_count), limit: 10 Movie.eager(:top_reviews) #
SELECT * FROM movies # SELECT * FROM ( # SELECT * FROM reviews WHERE (23 = reviews.movie_id) LIMIT 10 # UNION ALL # SELECT * FROM reviews WHERE (81 = reviews.movie_id) LIMIT 10 # UNION ALL # SELECT * FROM reviews WHERE (94 = reviews.movie_id) LIMIT 10 # ) -
JSON / Array expressions DB.extension :pg_json, :pg_array Sequel.extension :pg_json_ops, :pg_array_ops
Movie.where{data.pg_jsonb["imdb_rating"] > 7} # SELECT * FROM "movies" WHERE (("data" !-> 'imdb_rating') > 7) Movie.where{genres.pg_array.contains(%w[Adventure])} # SELECT * FROM "movies" WHERE ("genres" @> ARRAY['Adventure']) -
# use cursors for faster pagination Comment.dataset.use_cursor.each { |record| !!...
} # use streaming with sequel_pg Comment.dataset.stream.each { |record| !!... } # will use either streaming, cursors, or limit/offset pagination Comment.paged_each { |record| !!... } Cursors & Streaming -
COPY DB.copy_table(:table, format: :csv) { |row| !!... } # COPY
"table" TO STDOUT (FORMAT csv) DB.copy_into(:table, format: :csv, columns: [:column1, :column2], data: CSV.foreach("data.csv")) # COPY "table"("column1", "column2") FROM STDIN (FORMAT csv) -
• sharding • primary / replica • multiple databases •
UPSERT • LISTEN / NOTIFY (PG) • common table expressions (CTEs) • prepared statements • window functions • CHECK / CONSTRAINT • UNION / INTERSECT / EXCEPT • table partitioning (PG) • constraint validations • date artihmetic • full-text search • (materialized) views • RETURNING • identity columns • ... -
Execution time PostgresSQL MySQL 17ms 22ms 26ms 45ms Active Record
Sequel http://bit.ly/rubyday2020-sequel-activerecord -
Objects allocated PostgresSQL MySQL ~7k ~7k ~15k ~19k Active Record
Sequel http://bit.ly/rubyday2020-sequel-activerecord -
# Active Record articles = Article.limit(1000).to_a articles.each(&:attributes) # Sequel articles
= Article.limit(1000).all articles.each(&:values) -
Execution time PostgresSQL MySQL 18ms 23ms 76ms 90ms Active Record
Sequel http://bit.ly/rubyday2020-sequel-activerecord -
Objects allocated PostgresSQL MySQL ~7k ~7k ~32k ~32k Active Record
Sequel http://bit.ly/rubyday2020-sequel-activerecord -
# Active Record article = Article.first # does some work
article.attributes # does remaining work # Sequel article = Article.first # does all the work article.values # just reads the instance variable -
class Article < Sequel!::Model %i[ touch dirty modification_detection timestamps nested_attributes
delay_add_association validate_associated after_initialize validation_helpers optimistic_locking typecast_on_load column_con!flicts accessed_columns serialization_modification_detection association_dependencies association_multi_add_remove association_pks dataset_associations forbid_lazy_load many_through_many blacklist_security whitelist_security boolean_readers input_transformer split_values class_table_inheritance subclasses insert_con!flict columns_updated update_or_create update_primary_key update_refresh active_model validation_contexts validation_class_methods ].each { |name| plugin name } plugin :single_table_inheritance, :type plugin :serialization, :json, :data end 1.4x slower fetching -
articles = Article .grep(:body, "%Sequel%") .order(created_at: :desc) .paged_each CSV.open("articles.csv") do
|csv| csv !<< ["id", "title", "body"] articles.each do |article| csv !<< [article.id, article.title, article.body] end end -
articles = Article .grep(:body, "%Sequel%") .order(created_at: :desc) .naked # skip
model overhead .paged_each CSV.open("articles.csv") do |csv| csv !<< ["id", "title", "body"] articles.each do |article| csv !<< [article[:id], article[:title], article[:body]] end end -
ActiveRecord!::Base.establish_connection( adapter: "postgresql", database: "rubyday", pool: 5, checkout_timeout: 5, )
6.times.map do Thread.new do ActiveRecord!::Base.connection.execute "SELECT 1" end end.each(&:join) -
SELECT 1 SELECT 1 SELECT 1 SELECT 1 SELECT 1
AR!::ConnectionTimeoutError: all pooled connections were in use -
ActiveRecord!::Base.establish_connection( adapter: "postgresql", database: "rubyday", pool: 5, checkout_timeout: 5, )
6.times.map do Thread.new do ActiveRecord!::Base.connection.execute "SELECT 1" end end.each(&:join) -
ActiveRecord!::Base.establish_connection( adapter: "postgresql", database: "rubyday", pool: 5, checkout_timeout: 5, )
6.times.map do Thread.new do ActiveRecord!::Base.connection_pool.with_connection do ActiveRecord!::Base.connection.execute "SELECT 1" end end end.each(&:join) ✅ -
DB = Sequel.connect( adapter: "postgresql", database: "rubyday", max_connections: 5, pool_timeout:
5, ) 6.times.map do Thread.new do DB.run "SELECT 1" end end.each(&:join) ✅ -
DB = Sequel.connect( adapter: "postgresql", database: "rubyday" ) DB connection
DB connection ActiveRecord!::Base.establish_connection( adapter: "postgresql", database: "rubyday" ) -
gem install sequel-activerecord_connection DB = Sequel.connect( adapter: "postgresql", database: "rubyday",
extensions: :activerecord_connection ) DB connection ActiveRecord!::Base.establish_connection( adapter: "postgresql", database: "rubyday" ) -
• database.yml • migration & model generators • database rake
tasks • ActiveSupport & ActionContoller logging • ActionDispatch rescue responses • I18n • .... gem install sequel-rails -
<%= form_with model: Article.new do %> <%= f.label :title %>
<%= f.text_field :title %> <%= f.label :body %> <%= f.text_area :body %> <%= f.submit "Save" %> <% end %> Sequel!::Model.plugin :active_model -
<%= simple_form_for Article.new do %> <%= f.input :title %> <%=
f.input :body, as: :text %> <%= f.button "Submit" %> <% end %> Sequel!::Model.plugin :active_model -
Rodauth jsonapi-serializer, jsonapi-rb Shrine Pundit Mobility Sequel (native view support)
Sequel (list, tree, rcte_tree) sequel-annotate ... Devise, Sorcery, Clearance, Authlogic ActiveModelSerializers, RABL ActiveStorage, CarrierWave, Paperclip CanCanCan Globalize Scenic Acts As List, Ancestry, Closure Tree Annotate ... -
Recap • layered design (database, dataset, model) • managed complexity
with plugin architecture • polished SQL expression builder • flexible eager loading • advanced (Postgres)SQL features • faster and lighter than Active Record • more reliable connection pool -
Links http://sequel.jeremyevans.net/documentation.html https://github.com/jeremyevans/sequel_pg https://github.com/TalentBox/sequel-rails https://github.com/janko/sequel-activerecord_connection https://rosenfeld.herokuapp.com/en/articles/ruby-rails/2013-12-18-sequel-is-awesome-and-much- better-than-activerecord https://janko.io/ode-to-sequel/ https://janko.io/activerecord-is-reinventing-sequel/ https://bits.theorem.co/building-sql-expressions-with-sequel/
https://devhints.io/sequel https://github.com/jeremyevans/simple_orm_benchmark