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

Sequel

 Sequel

Introduction to the Sequel ORM, through comparison with ActiveRecod.

Janko Marohnić

April 28, 2015
Tweet

More Decks by Janko Marohnić

Other Decks in Programming

Transcript

  1. 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, …}, … ]
  2. 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
  3. 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
  4. require “sequel” DB = Sequel.connect(...) #=> #<Sequel::Database> require “sequel” DB

    = Sequel.connect(...) #=> #<Sequel::Database> DB.tables #=> [:movies] DB.create_table(:directors) { ... } require “sequel” DB = Sequel.connect(...) #=> #<Sequel::Database> DB.tables #=> [:movies] DB.create_table(:directors) { ... } DB[:movies] #=> #<Sequel::Dataset> DB[“SELECT * FROM movies”] require “sequel” DB = Sequel.connect(...) #=> #<Sequel::Database> DB.tables #=> [:movies] DB.create_table(:directors) { ... } DB[:movies] #=> #<Sequel::Dataset> DB[“SELECT * FROM movies”] DB[:movies].where(year: 2015).to_a #=> [{…},{…},…] require “sequel” DB = Sequel.connect(...) #=> #<Sequel::Database> DB.tables #=> [:movies] DB.create_table(:directors) { ... } DB[:movies] #=> #<Sequel::Dataset> DB[“SELECT * FROM movies”] DB[:movies].where(year: 2015).to_a #=> [{…},{…},…] class Movie < Sequel::Model end Movie.where(year: 2015).to_a #=> [#<Movie>,#<Movie>,…]
  5. 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}
  6. 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
  7. 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
  8. DB.extension :pg_json DB.create_table(:movies) { column :data, :json } Movie.create(data: {rated:

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

    “R”, year: 2015}) Movie.all #=> [#<Movie data={rated: “R”, year: 2015}>] 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”))
  10. DB.extension :pg_json DB.create_table(:movies) { column :data, :json } Movie.create(data: {rated:

    “R”, year: 2015}) Movie.all #=> [#<Movie data={rated: “R”, year: 2015}>] 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”))
  11. Plugins/Extensions LOC(ActiveRecord) ≈ LOC(Sequel) ≈ 30 000 => 0.5 seconds

    => 0.1 seconds require “active_record” require “sequel” (+ autoloading)
  12. • 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