Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Sequel: When ActiveRecord is not Enough

Janko Marohnić
September 16, 2020

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
Tweet

More Decks by Janko Marohnić

Other Decks in Programming

Transcript

  1. 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
  2. • 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
  3. 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)
  4. 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)
  5. ActiveRecord!::Base.establish_connection( adapter: "postgresql", database: "mydb", ) class Article < ActiveRecord!::Base

    end Article.connection Article #!=> Article(id: integer, title: string, body: text, !!...)
  6. 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
  7. 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
  8. # 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
  9. # 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
  10. 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
  11. 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 ❄
  12. movie.reviews #!=> [#<Review id=1>, #<Review id=2>, !!...] movie.reviews_dataset #!=> #<Sequel!::Dataset

    !!...> movie.add_review({!!...}) movie.remove_review(!!...) movie.remove_all_reviews
  13. 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
  14. DB = Sequel.connect(!!...) DB.logger = Logger.new($stdout) DB.run "some SQL query"

    DB.transaction { !!... } DB.tables #!=> [!!...] DB.schema(:table) #!=> {!!...} DB.disconnect
  15. DB = Sequel.connect(adapter: "postgres", database: "primarydb") DB.run "some SQL query"

    DB2 = Sequel.connect(adapter: "mysql2", database: "anotherdb") DB2.run "another SQL query"
  16. # implicit database class Movie < Sequel!::Model # !!... end

    # explicit database class Review < Sequel!::Model(DB) # !!... end # another database class Episode < Sequel!::Model(DB2) # !!... end
  17. Movie.where(!!...).order(!!...).select(!!...).all #!=> # [ # #<Movie @values={id: 13, title: "Foo",

    !!...}>, # #<Movie @values={id: 52, title: "Bar", !!...}>, # !!... # ]
  18. 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 # !!...
  19. 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
  20. require "sequel" ≈ 13k LOC require "active_record" ≈ 24k LOC

    + 18k LOC activerecord activemodel activesupport concurrent-ruby i18n tzinfo psych ...
  21. 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)
  22. 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 }
  23. create_table :accounts do String :email, null: false index :email, unique:

    true, where: Sequel[status: %w[verified unverified]] # !!... end
  24. 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)
  25. • more verbose • internal API • not documented •

    supported somewhere • more streamlined • public API • well documented • supported everywhere Arel Sequel
  26. 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)
  27. 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)
  28. Movie.eager( reviews: !-> (dataset) { dataset.where(approved: true) } ).all #

    SELECT * FROM movies # SELECT * FROM reviews WHERE (id IN (!!...) AND approved IS TRUE)
  29. 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 # )
  30. 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'])
  31. # 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
  32. 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)
  33. • 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 • ...
  34. Execution time PostgresSQL MySQL 17ms 22ms 26ms 45ms Active Record

    Sequel http://bit.ly/rubyday2020-sequel-activerecord
  35. Objects allocated PostgresSQL MySQL ~7k ~7k ~15k ~19k Active Record

    Sequel http://bit.ly/rubyday2020-sequel-activerecord
  36. Execution time PostgresSQL MySQL 18ms 23ms 76ms 90ms Active Record

    Sequel http://bit.ly/rubyday2020-sequel-activerecord
  37. Objects allocated PostgresSQL MySQL ~7k ~7k ~32k ~32k Active Record

    Sequel http://bit.ly/rubyday2020-sequel-activerecord
  38. # 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
  39. 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
  40. 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
  41. 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
  42. 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)
  43. SELECT 1 SELECT 1 SELECT 1 SELECT 1 SELECT 1

    AR!::ConnectionTimeoutError: all pooled connections were in use
  44. 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)
  45. 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) ✅
  46. 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) ✅
  47. DB = Sequel.connect( adapter: "postgresql", database: "rubyday" ) DB connection

    DB connection ActiveRecord!::Base.establish_connection( adapter: "postgresql", database: "rubyday" )
  48. 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" )
  49. • database.yml • migration & model generators • database rake

    tasks • ActiveSupport & ActionContoller logging • ActionDispatch rescue responses • I18n • .... gem install sequel-rails
  50. <%= 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
  51. <%= simple_form_for Article.new do %> <%= f.input :title %> <%=

    f.input :body, as: :text %> <%= f.button "Submit" %> <% end %> Sequel!::Model.plugin :active_model
  52. 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 ...
  53. 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