$30 off During Our Annual Pro Sale. View Details »

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. RubyDay 2020
    Sequel
    When Active Record is not enough

    View Slide

  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

    View Slide

  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

    View Slide

  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)

    View Slide

  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)

    View Slide

  6. Active Record
    (blue)
    Sequel
    (teal)

    View Slide

  7. Design

    View Slide

  8. Confidence

    View Slide

  9. ActiveRecord!::Base.establish_connection(
    adapter: "postgresql",
    database: "mydb",
    )
    class Article < ActiveRecord!::Base
    end
    Article #!=> Article (call Article.connection to establish a connection)

    View Slide

  10. ActiveRecord!::Base.establish_connection(
    adapter: "postgresql",
    database: "mydb",
    )
    class Article < ActiveRecord!::Base
    end
    Article #!=> Article (call Article.connection to establish a connection)

    View Slide

  11. ActiveRecord!::Base.establish_connection(
    adapter: "postgresql",
    database: "mydb",
    )
    class Article < ActiveRecord!::Base
    end
    Article.connection
    Article #!=> Article(id: integer, title: string, body: text, !!...)

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  15. Explicitness

    View Slide

  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

    View Slide

  17. movies = Movie.where(!!...)
    movies #!=> #
    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

    View Slide

  18. movies = Movie.where(!!...)
    movies #!=> #
    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

    View Slide

  19. movie.reviews #!=> #
    movie.reviews.where(!!...).order(!!...).to_a
    movie.reviews.records
    movie.reviews.build({!!...})
    movie.reviews.create({!!...})
    movie.reviews.delete(!!...)
    movie.reviews.clear(!!...)

    View Slide

  20. movie.reviews #!=> [#, #, !!...]
    movie.reviews_dataset #!=> #
    movie.add_review({!!...})
    movie.remove_review(!!...)
    movie.remove_all_reviews

    View Slide

  21. Layers

    View Slide

  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

    View Slide

  23. DB = Sequel.connect(!!...)
    DB.logger = Logger.new($stdout)
    DB.run "some SQL query"
    DB.transaction { !!... }
    DB.tables #!=> [!!...]
    DB.schema(:table) #!=> {!!...}
    DB.disconnect

    View Slide

  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"

    View Slide

  25. # implicit database
    class Movie < Sequel!::Model
    # !!...
    end
    # explicit database
    class Review < Sequel!::Model(DB)
    # !!...
    end
    # another database
    class Episode < Sequel!::Model(DB2)
    # !!...
    end

    View Slide

  26. Movie.where(!!...).order(!!...).select(!!...).all #!=>
    # [
    # #,
    # #,
    # !!...
    # ]

    View Slide

  27. DB[:movies].where(!!...).order(!!...).select(!!...).all #!=>
    # [
    # { id: 13, title: "Foo", !!... },
    # { id: 52, title: "Bar", !!... },
    # !!...
    # ]

    View Slide

  28. Complexity

    View Slide

  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
    # !!...

    View Slide

  30. Sequel!::Model.ancestors
    # Sequel!::Model!::Associations!::InstanceMethods
    # Sequel!::Model!::InstanceMethods

    View Slide

  31. Sequel!::Model.ancestors
    # Sequel!::Model!::Dirty!::InstanceMethods
    # Sequel!::Model!::Associations!::InstanceMethods
    # Sequel!::Model!::InstanceMethods
    Sequel!::Model.plugin :dirty

    View Slide

  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

    View Slide

  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

    View Slide

  34. require "sequel"
    ≈ 13k LOC
    require "active_record"
    ≈ 24k LOC + 18k LOC
    activerecord
    activemodel
    activesupport
    concurrent-ruby
    i18n
    tzinfo
    psych
    ...

    View Slide

  35. Features

    View Slide

  36. SQL Expressions

    View Slide

  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)

    View Slide

  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 }

    View Slide

  39. create_table :accounts do
    String :email, null: false
    index :email, unique: true,
    where: Sequel[status: %w[verified unverified]]
    # !!...
    end

    View Slide

  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)

    View Slide

  41. • more verbose
    • internal API
    • not documented
    • supported somewhere
    • more streamlined
    • public API
    • well documented
    • supported everywhere
    Arel Sequel

    View Slide

  42. Eager loading

    View Slide

  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)

    View Slide

  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)

    View Slide

  45. Movie.eager(
    reviews: !-> (dataset) {
    dataset.where(approved: true)
    }
    ).all
    # SELECT * FROM movies
    # SELECT * FROM reviews WHERE (id IN (!!...) AND approved IS TRUE)

    View Slide

  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
    # )

    View Slide

  47. PostgreSQL

    View Slide

  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'])

    View Slide

  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

    View Slide

  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)

    View Slide

  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
    • ...

    View Slide

  52. Performance

    View Slide

  53. Models

    View Slide

  54. # Active Record
    Article.limit(1000).to_a
    # Sequel
    Article.limit(1000).all

    View Slide

  55. Execution time
    PostgresSQL MySQL
    17ms
    22ms
    26ms
    45ms
    Active Record Sequel
    http://bit.ly/rubyday2020-sequel-activerecord

    View Slide

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

    View Slide

  57. # Active Record
    articles = Article.limit(1000).to_a
    articles.each(&:attributes)
    # Sequel
    articles = Article.limit(1000).all
    articles.each(&:values)

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  64. Connection Pool

    View Slide

  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)

    View Slide

  66. SELECT 1
    SELECT 1
    SELECT 1
    SELECT 1
    SELECT 1
    AR!::ConnectionTimeoutError: all pooled connections were in use

    View Slide

  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)

    View Slide

  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)

    View Slide

  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)

    View Slide

  70. SELECT 1
    SELECT 1
    SELECT 1
    SELECT 1
    SELECT 1
    SELECT 1

    View Slide

  71. Rails

    View Slide

  72. Alongside Active Record

    View Slide

  73. DB = Sequel.connect(
    adapter: "postgresql", database: "rubyday"
    )
    DB connection
    DB connection
    ActiveRecord!::Base.establish_connection(
    adapter: "postgresql", database: "rubyday"
    )

    View Slide

  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"
    )

    View Slide

  75. Instead of Active Record

    View Slide

  76. • database.yml
    • migration & model generators
    • database rake tasks
    • ActiveSupport & ActionContoller logging
    • ActionDispatch rescue responses
    • I18n
    • ....
    gem install sequel-rails

    View Slide

  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

    View Slide

  78. <%= simple_form_for Article.new do %>
    <%= f.input :title %>
    <%= f.input :body, as: :text %>
    <%= f.button "Submit" %>
    <% end %>
    Sequel!::Model.plugin :active_model

    View Slide

  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
    ...

    View Slide

  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

    View Slide

  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

    View Slide