Tweet
Tweet

Slide 1

Slide 1 text

RubyDay 2020 Sequel When Active Record is not enough

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

• 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

Slide 4

Slide 4 text

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)

Slide 5

Slide 5 text

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)

Slide 6

Slide 6 text

Active Record (blue) Sequel (teal)

Slide 7

Slide 7 text

Design

Slide 8

Slide 8 text

Confidence

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

# 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

Slide 15

Slide 15 text

Explicitness

Slide 16

Slide 16 text

# 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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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 ❄

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

Layers

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

DB = Sequel.connect(adapter: "postgres", database: "primarydb") DB.run "some SQL query" DB2 = Sequel.connect(adapter: "mysql2", database: "anotherdb") DB2.run "another SQL query"

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

Complexity

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

Features

Slide 36

Slide 36 text

SQL Expressions

Slide 37

Slide 37 text

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)

Slide 38

Slide 38 text

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 }

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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)

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

Eager loading

Slide 43

Slide 43 text

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)

Slide 44

Slide 44 text

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)

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

PostgreSQL

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

# 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

Slide 50

Slide 50 text

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)

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

Performance

Slide 53

Slide 53 text

Models

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

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

Slide 58

Slide 58 text

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

Slide 59

Slide 59 text

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

Slide 60

Slide 60 text

# 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

Slide 61

Slide 61 text

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

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

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

Slide 64

Slide 64 text

Connection Pool

Slide 65

Slide 65 text

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)

Slide 66

Slide 66 text

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

Slide 67

Slide 67 text

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)

Slide 68

Slide 68 text

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

Slide 69

Slide 69 text

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

Slide 70

Slide 70 text

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

Slide 71

Slide 71 text

Rails

Slide 72

Slide 72 text

Alongside Active Record

Slide 73

Slide 73 text

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

Slide 74

Slide 74 text

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

Slide 75

Slide 75 text

Instead of Active Record

Slide 76

Slide 76 text

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

Slide 77

Slide 77 text

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

Slide 78

Slide 78 text

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

Slide 79

Slide 79 text

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

Slide 80

Slide 80 text

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

Slide 81

Slide 81 text

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