Sequel Cheat Sheet
Tags: sequel, cheatsheet
Published: 2021-05-13 11:20:32


### Unrestrict primary key

```
    Category.create id: 'travel'   # error
    Category.unrestrict_primary_key
    Category.create id: 'travel'   # ok
```
### Schema
```
    class Deal < Sequel::Model
      set_schema do
        primary_key :id
        primary_key [:id, :title]
        String :name, primary_key: true
        
        String  :title
        Numeric :price
        DateTime :expires

        unique :whatever
        check(:price) { num > 0 }

        foreign_key :artist_id
        String :artist_name, key: :id

        index :title
        index [:artist_id, :name]
        full_text_index :title

        # String, Integer, Fixnum, Bignum, Float, Numeric, BigDecimal,
        # Date, DateTime, Time, File, TrueClass, FalseClass
      end
    end
```

### Callbacks

```
    before_create
    after_create

    before_validation
    after_validation
    before_save
    before_update
    UPDATE QUERY
    after_update
    after_save

    before_destroy
    DELETE QUERY
    after_destroy
```

### Model stuff
```
    deal = Deal[1]
    deal.changed_columns
    deal.destroy  # Calls hooks
    deal.delete   # No hooks
    deal.exists?
    deal.new?
    deal.hash  # Only uniques
    deal.keys  #=> [:id, :name]
    deal.modified!
    deal.modified?
    deal.lock!
```

### Validations
```
      def validate
        super
        errors.add(:name, 'cannot be empty') if !name || name.empty?

        validates_presence [:title, :site]
        validates_unique :name
        validates_format /\Ahttps?:\/\//, :website, :message=>'is not a valid URL'
        validates_includes %w(a b c), :type
        validates_integer :rating
        validates_numeric :number
        validates_type String, [:title, :description]

        validates_integer :rating  if new?

        # options: :message =>, :allow_nil =>, :allow_blank =>,
        #          :allow_missing =>,

        validates_exact_length 17, :isbn
        validates_min_length 3, :name
        validates_max_length 100, :name
        validates_length_range 3..100, :name
        
        # Setter override
        def filename=(name)
          @values[:filename] = name
        end
      end
    end

    deal.errors
```

### Model associations
```
    class Deal < Sequel::Model

      # Us (left) <=> Them (right)
      many_to_many  :images,
        left_id:    :deal_id,
        right_id:   :image_id,
        join_table: :image_links

      one_to_many   :files,
        key:        :deal_id,
        class:      :DataFile,

      many_to_one   :parent, class: self
      one_to_many   :children, key: :parent_id, class: self

      one_to_many :gold_albums, class: :Album do |ds|
        ds.filter { copies_sold > 50000 }
      end
```
Provided by many_to_many
```
    Deal[1].images
    Deal[1].add_image
    Deal[1].remove_image
    Deal[1].remove_all_images
```
### Alter table
```
    database.alter_table :deals do
      add_column :name, String
      drop_column :column_name
      rename_column :from, :to

      add_constraint :valid_name, :name.like('A%')
      drop_constraint :constraint

      add_full_text_index :body
      add_spacial_index [columns]

      add_index :price
      drop_index :index

      add_foreign_key :artist_id, :table
      add_primary_key :id
      add_unique_constraint [columns]
      set_column_allow_null :foo, false
      set_column_default :title, ''

      set_column_type :price, 'char(10)'
    end
```
### Documents

http://sequel.rubyforge.org/rdoc/files/doc/association_basics_rdoc.html
    http://sequel.rubyforge.org/rdoc/classes/Sequel/Schema/Generator.html
    http://sequel.rubyforge.org/rdoc/files/doc/validations_rdoc.html
    http://sequel.rubyforge.org/rdoc/classes/Sequel/Model.html

### Miscellaneous:
```
    dataset.sql # "SELECT * FROM items"
    dataset.delete_sql # "DELETE FROM items"
    dataset.where(:name => 'sequel').exists # "EXISTS ( SELECT * FROM items WHERE name = 'sequel' )"
    dataset.columns #=> array of columns in the result set, does a SELECT
    DB.schema(:items) => [[:id, {:type=>:integer, ...}], [:name, {:type=>:string, ...}], ...]

----------------------------------------------------------------------------------------------------------------------------------------------------------------
```

### Transactions
```
    DB.transaction do
      dataset.insert(:first_name => 'Inigo', :last_name => 'Montoya')
      dataset.insert(:first_name => 'Farm', :last_name => 'Boy')
    end # Either both are inserted or neither are inserted
```
Database#transaction is re-entrant:
```
    DB.transaction do # BEGIN issued only here
      DB.transaction
        dataset << {:first_name => 'Inigo', :last_name => 'Montoya'}
      end
    end # COMMIT issued only here
```
Transactions are aborted if an error is raised:
```
    DB.transaction do
      raise "some error occurred"
    end # ROLLBACK issued and the error is re-raised
```
Transactions can also be aborted by raising Sequel::Rollback:
```
    DB.transaction do
      raise(Sequel::Rollback) if something_bad_happened
    end # ROLLBACK issued and no error raised
```
Savepoints can be used if the database supports it:
```
    DB.transaction do
      dataset << {:first_name => 'Farm', :last_name => 'Boy'} # Inserted
      DB.transaction(:savepoint=>true) # This savepoint is rolled back
        dataset << {:first_name => 'Inigo', :last_name => 'Montoya'} # Not inserted
        raise(Sequel::Rollback) if something_bad_happened
      end
      dataset << {:first_name => 'Prince', :last_name => 'Humperdink'} # Inserted
    end
```
### Reference
* [Sequel Cheat Sheet](https://cheatsheetmaker.com/sequel) - [Cheat Sheet Maker](https://cheatsheetmaker.com)