Sequel

 Sequel

Introduction to the Sequel ORM, through comparison with ActiveRecod.

376e4eb9dc6c2e33d1330262edc4f109?s=128

Janko Marohnić

April 28, 2015
Tweet

Transcript

  1. Sequel janko-m @jankomarohnic

  2. 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, …}, … ]
  3. ActiveRecord require “active_record” ActiveRecord::Base.establish_connection(“postgres:///test”) class Movie < ActiveRecord::Base end Movie.create(movies_data)

    => 1 minute #=> [#<Movie…>,#<Movie…>,…]
  4. 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
  5. 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
  6. Sequel require “sequel” DB = Sequel.postgres(“test”) DB[:movies].multi_insert(movies_data) => 2 seconds

  7. ActiveRecord Arel Raw SQL Sequel 2 2 20 60 !

  8. Sequel::Database Sequel::Dataset Sequel::Model

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

  10. 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>,…]
  11. Querying

  12. 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}
  13. 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
  14. 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
  15. 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

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

  17. 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”)
  18. 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”))
  19. 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”))
  20. Plugins/Extensions Core list nested attributes dirty STI json serializer validation

    helpers pg_json pagination … … … …
  21. Plugins/Extensions LOC(ActiveRecord) ≈ LOC(Sequel) ≈ 30 000 => 0.5 seconds

    => 0.1 seconds require “active_record” require “sequel” (+ autoloading)
  22. Issues & pull requests Sequel ActiveRecord

  23. • 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
  24. • 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/