# Filter dates
DB[:users].where { created_at > Date.today << 1 } # 1 month
DB[:users].where { created_at > (Date.today - 10) } # 10 days
# > WHERE created_at (`created_at` > '2016-12-11')
DB[:users].where(created_at: ((Date.today - 10)..(Date.today)))
# Sequel.~ == NOT
# Sequel.& == AND
DB[:users].where { Sequel.~(login: nil, email: nil) }
# WHERE ((`login` IS NOT NULL) OR (`email` IS NOT NULL))
DB[:users].where { Sequel.&(login: nil, email: nil) }
DB[:users].exclude { Sequel.~(login: nil, email: nil) }
# WHERE ((`login` IS NULL) AND (`email` IS NULL))
DB[:users].exclude { Sequel.&(login: nil, email: nil) }
# WHERE NOT ((`login` IS NULL) AND (`email` IS NULL))
DB[:users].exclude { Sequel.&(Sequel.~(login: nil, email: nil)) }
# WHERE NOT ((`login` IS NOT NULL) OR (`email` IS NOT NULL))
DB[:users].where { Sequel.&(Sequel.~(login: nil), Sequel.~(email: nil)) }
# WHERE ((`login` IS NOT NULL) AND (`email` IS NOT NULL))
Name select
DB[:zipcodes]
.join(:zones, id: :zone_id)
.join(:countries, id: Sequel[:zipcodes][:country_id])
.where(:zones__name => /namur/)
.select(:zipcodes__name___zipcode_name,
:zones__name___zone_name,
:countries__name___country_name)
# SELECT "zipcodes"."name" AS "zipcode_name",
# "zones"."name" AS "zone_name",
# "countries"."name" AS "country_name"
# FROM "zipcodes"
# INNER JOIN "zones"
# ON ( "zones"."id" = "zipcodes"."zone_id" )
# INNER JOIN "countries"
# ON ( "countries"."id" = "zipcodes"."country_id" )
# WHERE ( "zones"."name" ~ 'namur' )
# REGEX DB[:users].where(login: /Admin/i) # WHERE (`login` REGEXP 'Admin')
Postgres' specs (search "@db.create_table!(:items)")
DB.extension :pg_array, :pg_json, :pg_json_ops
DB.create_table :movies do
column :genres, 'text[]' # Create an Array column
column :actors, 'jsonb' # jsonb column
end
DB[:movies].insert(genres: Sequel.pg_array(["Comedy", "Documentary"], :text))
DB[:movies].insert(actors: Sequel.pg_jsonb({"leading" => "Chuck Norris"}))
# Store files in a PG database
DB.alter_table :paintings do
add_column :painting, File
end
painting_image = File.open("guernica.png").read
DB[:paintings].insert(painting: Sequel.blob(painting_image))
Copy databases
sequel -C postgres://my_username@localhost/my_pg_db sqlite://my_sqlite_database.sqlite3