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.

Transcript

1. RubyDay 2020 Sequel When Active Record is not enough
2. 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
3. • 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
4. 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)
5. 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)
6. Active Record (blue) Sequel (teal)
7. Design
8. Confidence
9. ActiveRecord!::Base.establish_connection( adapter: "postgresql", database: "mydb", ) class Article < ActiveRecord!::Base end Article #!=> Article (call Article.connection to establish a connection)
10. ActiveRecord!::Base.establish_connection( adapter: "postgresql", database: "mydb", ) class Article < ActiveRecord!::Base end Article #!=> Article (call Article.connection to establish a connection)
11. ActiveRecord!::Base.establish_connection( adapter: "postgresql", database: "mydb", ) class Article < ActiveRecord!::Base end Article.connection Article #!=> Article(id: integer, title: string, body: text, !!...)
12. 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
13. 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
14. # 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
15. Explicitness
16. # 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
17. 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
18. 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 ❄
19. movie.reviews #!=> #<ActiveRecord!::Associations!::CollectionProxy> movie.reviews.where(!!...).order(!!...).to_a movie.reviews.records movie.reviews.build({!!...}) movie.reviews.create({!!...}) movie.reviews.delete(!!...) movie.reviews.clear(!!...)
20. movie.reviews #!=> [#<Review id=1>, #<Review id=2>, !!...] movie.reviews_dataset #!=> #<Sequel!::Dataset !!...> movie.add_review({!!...}) movie.remove_review(!!...) movie.remove_all_reviews
21. Layers
22. 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
23. DB = Sequel.connect(!!...) DB.logger = Logger.new($stdout) DB.run "some SQL query" DB.transaction { !!... } DB.tables #!=> [!!...] DB.schema(:table) #!=> {!!...} DB.disconnect
24. DB = Sequel.connect(adapter: "postgres", database: "primarydb") DB.run "some SQL query" DB2 = Sequel.connect(adapter: "mysql2", database: "anotherdb") DB2.run "another SQL query"
25. # implicit database class Movie < Sequel!::Model # !!... end # explicit database class Review < Sequel!::Model(DB) # !!... end # another database class Episode < Sequel!::Model(DB2) # !!... end
26. Movie.where(!!...).order(!!...).select(!!...).all #!=> # [ # #<Movie @values={id: 13, title: "Foo", !!...}>, # #<Movie @values={id: 52, title: "Bar", !!...}>, # !!... # ]
27. DB[:movies].where(!!...).order(!!...).select(!!...).all #!=> # [ # { id: 13, title: "Foo", !!... }, # { id: 52, title: "Bar", !!... }, # !!... # ]
28. Complexity
29. 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 # !!...
30. Sequel!::Model.ancestors # Sequel!::Model!::Associations!::InstanceMethods # Sequel!::Model!::InstanceMethods
31. Sequel!::Model.ancestors # Sequel!::Model!::Dirty!::InstanceMethods # Sequel!::Model!::Associations!::InstanceMethods # Sequel!::Model!::InstanceMethods Sequel!::Model.plugin :dirty
32. Sequel!::Model.ancestors # Sequel!::Model!::Serialization!::InstanceMethods # Sequel!::Model!::Dirty!::InstanceMethods # Sequel!::Model!::Associations!::InstanceMethods # Sequel!::Model!::InstanceMethods Sequel!::Model.plugin :dirty Sequel!::Model.plugin :serialization
33. 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
34. require "sequel" ≈ 13k LOC require "active_record" ≈ 24k LOC + 18k LOC activerecord activemodel activesupport concurrent-ruby i18n tzinfo psych ...
35. Features
36. SQL Expressions
37. 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)
38. 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 }
39. create_table :accounts do String :email, null: false index :email, unique: true, where: Sequel[status: %w[verified unverified]] # !!... end
40. 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)
41. • more verbose • internal API • not documented • supported somewhere • more streamlined • public API • well documented • supported everywhere Arel Sequel
42. Eager loading
43. 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)
44. 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)
45. Movie.eager( reviews: !-> (dataset) { dataset.where(approved: true) } ).all # SELECT * FROM movies # SELECT * FROM reviews WHERE (id IN (!!...) AND approved IS TRUE)
46. 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 # )
47. PostgreSQL
48. 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'])
49. # 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
50. 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)
51. • 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 • ...
52. Performance
53. Models
54. # Active Record Article.limit(1000).to_a # Sequel Article.limit(1000).all
55. Execution time PostgresSQL MySQL 17ms 22ms 26ms 45ms Active Record Sequel http://bit.ly/rubyday2020-sequel-activerecord
56. Objects allocated PostgresSQL MySQL ~7k ~7k ~15k ~19k Active Record Sequel http://bit.ly/rubyday2020-sequel-activerecord
57. # Active Record articles = Article.limit(1000).to_a articles.each(&:attributes) # Sequel articles = Article.limit(1000).all articles.each(&:values)
58. Execution time PostgresSQL MySQL 18ms 23ms 76ms 90ms Active Record Sequel http://bit.ly/rubyday2020-sequel-activerecord
59. Objects allocated PostgresSQL MySQL ~7k ~7k ~32k ~32k Active Record Sequel http://bit.ly/rubyday2020-sequel-activerecord
60. # 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
61. 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
62. 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
63. 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
64. Connection Pool
65. 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)
66. SELECT 1 SELECT 1 SELECT 1 SELECT 1 SELECT 1 AR!::ConnectionTimeoutError: all pooled connections were in use
67. 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)
68. 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) ✅
69. 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) ✅
70. SELECT 1 SELECT 1 SELECT 1 SELECT 1 SELECT 1 SELECT 1
71. Rails
72. Alongside Active Record
73. DB = Sequel.connect( adapter: "postgresql", database: "rubyday" ) DB connection DB connection ActiveRecord!::Base.establish_connection( adapter: "postgresql", database: "rubyday" )
74. 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" )
75. Instead of Active Record
76. • database.yml • migration & model generators • database rake tasks • ActiveSupport & ActionContoller logging • ActionDispatch rescue responses • I18n • .... gem install sequel-rails
77. <%= 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
78. <%= simple_form_for Article.new do %> <%= f.input :title %> <%= f.input :body, as: :text %> <%= f.button "Submit" %> <% end %> Sequel!::Model.plugin :active_model
79. 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 ...
80. 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
81. 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