Sequel janko-m @jankomarohnic

Import 50 000 movies into a database Task [ {title: “Back to the Future”, year: 1985, …}, {title: “The Matrix”, year: 1999, …}, {title: “The Shawshank Redemption”, year: 1994, …}, … ]

ActiveRecord require “active_record” ActiveRecord::Base.establish_connection(“postgres:///test”) class Movie < ActiveRecord::Base end Movie.create(movies_data) => 1 minute #=> [#,#,…]

Arel require “active_record” ActiveRecord::Base.establish_connection(“postgres:///test”) movies_data.each do |hash| insert = Arel::Nodes::InsertStatement.new insert.relation = Arel::Table.new(:movies) insert.columns = hash.keys.map { |k| Arel::Table.new(:movies)[k] } insert.values = Arel::Nodes::Values.new(hash.values, insert.columns) ActiveRecord::Base.connection.execute(insert.to_sql) end => 20 seconds

Raw SQL require “active_record” ActiveRecord::Base.establish_connection(“postgres:///test”) columns = movies_data.first.keys values_list = movies_data.map do |hash| hash.values.map do |value| ActiveRecord::Base.connection.quote(value) end end ActiveRecord::Base.connection.execute <<-SQL INSERT INTO movies (#{columns.join(",")}) VALUES #{values_list.map { |values| "(#{values.join(",")})" }.join(", ")} SQL => 2 seconds

Sequel require “sequel” DB = Sequel.postgres(“test”) DB[:movies].multi_insert(movies_data) => 2 seconds

ActiveRecord Arel Raw SQL Sequel 2 2 20 60 !

Sequel::Database Sequel::Dataset Sequel::Model

Sequel::Database ≈ ActiveRecord::Base.connection Sequel::Dataset ≈ ActiveRecord::Relation Sequel::Model ≈ ActiveRecord::Base

require “sequel” DB = Sequel.connect(...) #=> # require “sequel” DB = Sequel.connect(...) #=> # DB.tables #=> [:movies] DB.create_table(:directors) { ... } require “sequel” DB = Sequel.connect(...) #=> # DB.tables #=> [:movies] DB.create_table(:directors) { ... } DB[:movies] #=> # DB[“SELECT * FROM movies”] require “sequel” DB = Sequel.connect(...) #=> # DB.tables #=> [:movies] DB.create_table(:directors) { ... } DB[:movies] #=> # DB[“SELECT * FROM movies”] DB[:movies].where(year: 2015).to_a #=> [{…},{…},…] require “sequel” DB = Sequel.connect(...) #=> # DB.tables #=> [:movies] DB.create_table(:directors) { ... } DB[:movies] #=> # DB[“SELECT * FROM movies”] DB[:movies].where(year: 2015).to_a #=> [{…},{…},…] class Movie < Sequel::Model end Movie.where(year: 2015).to_a #=> [#,#,…]

Querying

Movie.where{year >= 2010} Movie.where{year >= 2010} Movie.where{(title =~ “Batman”) | (year < 2010)} Movie.where{year >= 2010} Movie.where{(title =~ “Batman”) | (year < 2010)} Movie.where{rating >= avg(rating)} Movie.where{year >= 2010} Movie.where{(title =~ “Batman”) | (year < 2010)} Movie.where{rating >= avg(rating)} Movie.select{max(year) - 1} Virtual rows Movie.where{year >= 2010} Movie.where{(title =~ “Batman”) | (year < 2010)} Movie.where{rating >= avg(rating)} Movie.select{max(year) - 1} Movie.order{date.desc}

Movie.where{plot.like(’%future%’)} # WHERE (plot LIKE ‘%future%’) Movie.where{plot.like(’%future%’)} # WHERE (plot LIKE ‘%future%’) Movie.where{info.cast(String).like(‘%future%’)} Movie.where{plot.like(’%future%’)} # WHERE (plot LIKE ‘%future%’) Movie.where{info.cast(String).like(‘%future%’)} Movie.where{date.extract(:year) => 2015} # WHERE (extract(year FROM “date”) = 2015) Movie.where{plot.like(’%future%’)} # WHERE (plot LIKE ‘%future%’) Movie.where{info.cast(String).like(‘%future%’)} Movie.where{date.extract(:year) => 2015} # WHERE (extract(year FROM “date”) = 2015) Movie.select{avg(:rating).as(“average_rating”)} Virtual rows

Movie.where{title.like(‘%Batman%’)} Movie.where{title.like(‘%Batman%’)} Movie.where(title: /Batman/) # WHERE (title ~ ‘Batman’) Movie.where{title.like(‘%Batman%’)} Movie.where(title: /Batman/) # WHERE (title ~ ‘Batman’) Movie.where(title: /Batman/i) # WHERE (title ~* ‘Batman’) Regex

Movie.recent.except(Movie.horror) Movie.recent.except(Movie.horror) Movie.action.instersect(Movie.science_fiction) Movie.recent.except(Movie.horror) Movie.action.instersect(Movie.science_fiction) Movie.romance.union(Movie.adventure) EXCEPT INTERSECT UNION

Movie.inner_join(…) Movie.left_join(…) Movie.right_join(…) Movie.cross_join(…) ... Joins

DB.extension :pg_json DB.create_table(:movies) { column :data, :json } Movie.create(data: {rated: “R”, year: 2015}) Movie.all #=> [#] JSON (PostgreSQL) Movie.where(“(info ->> ‘rated’) = ‘R’”) Movie.select(“(info #>> ARRAY[‘episodes’,0,’name’]) AS pilot”) Movie.select(“(info ? ‘reviews’) AS reviewed”)

DB.extension :pg_json DB.create_table(:movies) { column :data, :json } Movie.create(data: {rated: “R”, year: 2015}) Movie.all #=> [#] JSON (PostgreSQL) Sequel.extension :pg_json_ops info = Sequel.pg_json(:info) Movie.where(info.get_text(‘rated’) => “R”) Movie.select(info.get_text([‘episodes’, 0, ‘name’]).as(“pilot”)) Movie.select(info.has_key?(‘reviews’).as(“reviewed”))

DB.extension :pg_json DB.create_table(:movies) { column :data, :json } Movie.create(data: {rated: “R”, year: 2015}) Movie.all #=> [#] JSON (PostgreSQL) Sequel.extension :pg_json_ops info = Sequel.pg_json(:info) Movie.where(info.get_text(‘rated’) => “R”) Movie.select(info.get_text([‘episodes’, 0, ‘name’]).as(“pilot”)) Movie.select(info.has_key?(‘reviews’).as(“reviewed”))

Plugins/Extensions Core list nested attributes dirty STI json serializer validation helpers pg_json pagination … … … …

Plugins/Extensions LOC(ActiveRecord) ≈ LOC(Sequel) ≈ 30 000 => 0.5 seconds => 0.1 seconds require “active_record” require “sequel” (+ autoloading)

Issues & pull requests Sequel ActiveRecord

• Advanced database interface (Postgres!) • Sequel::Model or Sequel::Dataset (your choice) • Plugins/Extensions design • Very similar to ActiveRecord, but much better (query interface) • 0 issues, awesome support Overview

• http://sequel.jeremyevans.net/ • https://github.com/jeremyevans/sequel • https://www.youtube.com/watch?v=toAcnwqlU1Q • https://mrbrdo.wordpress.com/2013/10/15/why- you-should-stop-using-activerecord-and-start- using-sequel/