Sequel: When ActiveRecord is not Enough

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.

376e4eb9dc6c2e33d1330262edc4f109?s=128

Janko Marohnić

September 16, 2020
Tweet

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