Sequel

Introduction to the Sequel ORM, through comparison with ActiveRecod.

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/