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. Sequel
    janko-m @jankomarohnic

    View Slide

  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, …},

    ]

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  10. 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 #=> [#,#,…]

    View Slide

  11. Querying

    View Slide

  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}

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

  17. 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”)

    View Slide

  18. 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”))

    View Slide

  19. 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”))

    View Slide

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




    View Slide

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

    View Slide

  22. Issues & pull requests
    Sequel
    ActiveRecord

    View Slide

  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

    View Slide

  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/

    View Slide