Sequel: When ActiveRecord is not Enough

RubyDay 2020
Sequel
When Active Record is not enough

View Slide

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

• 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

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

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

Active Record
(blue)
Sequel
(teal)

View Slide

Design

View Slide

Confidence

View Slide

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

View Slide

database: "mydb",
)
end
Article.connection
Article #!=> Article(id: integer, title: string, body: text, !!...)

View Slide

database: "mydb",
)
end
Article.connection
Article.method_defined?(:title) #!=> false
Article.method_defined?(:body) #!=> false

View Slide

database: "mydb",
)
end
Article.connection
Article.new
Article.method_defined?(:title) #!=> true
Article.method_defined?(:body) #!=> true

View Slide

# 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

Explicitness

View Slide

# 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

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

movies = Movie.where(!!...)
movies #!=> #
movies.frozen? #!=> true
❄

View Slide

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

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

View Slide

Layers

View Slide

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

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

View Slide

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

# 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

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

View Slide

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

View Slide

Complexity

View Slide

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

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

View Slide

# Sequel!::Model!::Dirty!::InstanceMethods
Sequel!::Model.plugin :dirty

View Slide

# Sequel!::Model!::Serialization!::InstanceMethods
Sequel!::Model.plugin :serialization

View Slide

# Sequel!::Model!::SingleTableInheritance!::InstanceMethods
# Sequel!::Model!::Serialization!::InstanceMethods
Sequel!::Model.plugin :serialization
Sequel!::Model.plugin :single_table_inheritance, :type

View Slide

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

View Slide

Features

View Slide

SQL Expressions

View Slide

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

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

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

View Slide

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

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

View Slide

Eager loading

View Slide

Movie.includes(:reviews).to_a
# SELECT * FROM movies
# SELECT * FROM reviews WHERE (id IN (!!...))
Movie.preload(:reviews).to_a
Movie.eager_load(:reviews).to_a
# SELECT !!...
# FROM movies
# LEFT OUTER JOIN reviews ON (reviews.movie_id = movies.id)

View Slide

Movie.eager(:reviews).all
Movie.eager_graph(:reviews).all
# SELECT !!...
# FROM movies
# LEFT OUTER JOIN reviews ON (reviews.movie_id = movies.id)

View Slide

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

View Slide

Movie.one_to_many :top_reviews, class: :Review,
order: Sequel.desc(:upvote_count), limit: 10
Movie.eager(:top_reviews)
# SELECT * FROM (
# SELECT * FROM reviews WHERE (23 = reviews.movie_id) LIMIT 10
# UNION ALL
# UNION ALL
# )

View Slide

PostgreSQL

View Slide

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

# 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

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

• 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

Performance

View Slide

Models

View Slide

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

View Slide

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

View Slide

Objects allocated
PostgresSQL MySQL
~7k
~7k
~15k
~19k

View Slide

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

View Slide

Execution time
PostgresSQL MySQL
18ms
23ms
76ms
90ms

View Slide

Objects allocated
PostgresSQL MySQL
~7k
~7k
~32k
~32k

View Slide

# 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

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

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

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

Connection Pool

View Slide

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

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

View Slide

pool: 5,
)
6.times.map do
Thread.new do
end
end.each(&:join)

View Slide

pool: 5,
)
6.times.map do
Thread.new do
ActiveRecord!::Base.connection_pool.with_connection do
end
end
end.each(&:join)
✅

View Slide

max_connections: 5,
pool_timeout: 5,
)
6.times.map do
Thread.new do
DB.run "SELECT 1"
end
end.each(&:join)
✅

View Slide

SELECT 1
SELECT 1
SELECT 1
SELECT 1
SELECT 1
SELECT 1

View Slide

Rails

View Slide

Alongside Active Record

View Slide

adapter: "postgresql", database: "rubyday"
)
DB connection
DB connection
)

View Slide

gem install sequel-activerecord_connection
adapter: "postgresql", database: "rubyday",
extensions: :activerecord_connection
)
DB connection
)

View Slide

Instead of Active Record

View Slide

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

View Slide

<%= 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

<%= 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

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

Recap
• layered design (database, dataset, model)

• managed complexity with plugin architecture

• polished SQL expression builder

• ﬂexible eager loading

• advanced (Postgres)SQL features

• faster and lighter than Active Record

• more reliable connection pool

View Slide

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

Sequel: When ActiveRecord is not Enough

Sequel: When ActiveRecord is not Enough

Janko Marohnić

More Decks by Janko Marohnić

Other Decks in Programming

Featured

Transcript