Sequel for SQL Users¶ ↑
One of the main benefits of Sequel is that it doesn’t require the user to know SQL in order to use it, though SQL knowledge is certainly helpful. Unlike most other Sequel documentation, this guide assumes you know SQL, and provides an easy way to discover how to do something in Sequel given the knowledge of how to do so in SQL.
You Can Just Use SQL¶ ↑
With Sequel, it’s very easy to just use SQL for your queries. If learning Sequel’s DSL seems like a waste of time, you are certainly free to write all your queries in SQL. Sequel uses a few different methods depending on the type of query you are doing.
SELECT¶ ↑
For SELECT queries, you should probably use Database#fetch with a string and a block:
DB.fetch("SELECT * FROM albums") do |row| puts row[:name] end
Database#fetch will take the query you give it, execute it on the database, and yield a hash with column symbol keys for each row returned. If you want to use some placeholder variables, you can set the placeholders with ? and add the corresponding arguments to fetch:
DB.fetch("SELECT * FROM albums WHERE name LIKE ?", 'A%') do |row| puts row[:name] end
You can also use named placeholders by starting the placeholder with a colon, and using a hash for the argument:
DB.fetch("SELECT * FROM albums WHERE name LIKE :pattern", pattern: 'A%') do |row| puts row[:name] end
This can be helpful for long queries where it is difficult to match the question marks in the query with the arguments.
What Sequel actually does internally is two separate things. It first creates a dataset representing the query, and then it executes the dataset’s SQL code to retrieve the objects. Often, you want to define a dataset at some point, but not execute it until later. You can do this by leaving off the block, and storing the dataset in a variable:
ds = DB.fetch("SELECT * FROM albums")
Then, when you want to retrieve the rows later, you can call each on the dataset to retrieve the rows:
ds.each{|r| puts r[:name]}
You should note that Database#[] calls Database#fetch if a string is provided, so you can also do:
ds = DB["SELECT * FROM albums"] ds.each{|r| puts r[:name]}
However, note that Database#[] cannot take a block directly, you have to call each on the returned dataset. There are plenty of other methods besides each. For example, the all method returns all records in the dataset as an array:
DB["SELECT * FROM albums"].all # [{:id=>1, :name=>'RF', ...}, ...]
INSERT, UPDATE, DELETE¶ ↑
INSERT, UPDATE, and DELETE all work the same way. You first create the dataset with the SQL you want to execute using Database#[]:
insert_ds = DB["INSERT INTO albums (name) VALUES (?)", 'RF'] update_ds = DB["UPDATE albums SET name = ? WHERE name = ?", 'MO', 'RF'] delete_ds = DB["DELETE FROM albums WHERE name = ?", 'MO']
Then, you call the insert, update, or delete method on the returned dataset:
insert_ds.insert update_ds.update delete_ds.delete
update and delete generally return the number of rows affected, and insert generally returns the autogenerated primary key integer for the row inserted (if any), but not all adapters/databases support this behavior for datasets using custom SQL (notably it is not supported for insert on PostgreSQL).
Other Queries¶ ↑
All other queries such as TRUNCATE, CREATE TABLE, and ALTER TABLE should be executed using Database#run:
DB.run "CREATE TABLE albums (id integer primary key, name varchar(255))"
You can also use Database#<<:
DB << "ALTER TABLE albums ADD COLUMN copies_sold INTEGER"
Other Places¶ ↑
Almost everywhere in Sequel, you can drop down to literal SQL by providing a literal string, which you can create with Sequel.lit:
DB[:albums].select('name') # SELECT 'name' FROM albums DB[:albums].select(Sequel.lit('name')) # SELECT name FROM albums
For a simpler way of creating literal strings, you can also use the core_extensions extension, which adds the String#lit method, and other methods that integrate Sequel’s DSL with the Ruby language:
DB[:albums].select('name'.lit)
So you can use Sequel’s DSL everywhere you find it helpful, and fallback to literal SQL if the DSL can’t do what you want or you just find literal SQL easier.
Translating SQL Expressions into Sequel¶ ↑
The rest of this guide assumes you want to use Sequel’s DSL to represent your query, that you know how to write the query in SQL, but you aren’t sure how to write it in Sequel’s DSL.
This section will describe how specific SQL expressions are handled in Sequel. The next section will discuss how to create queries by using method chaining on datasets.
Database#literal¶ ↑
It’s important to get familiar with the Database#literal method, which will return the SQL that will be used for a given expression:
DB.literal(1) # => "1" DB.literal(:column) # => "\"column\"" DB.literal('string') # => "'string'"
Try playing around to see how different objects get literalized into SQL
Database Loggers¶ ↑
Some Sequel methods handle literalization slightly differently than Database#literal. If you want to see all SQL queries that Sequel is sending to the database, you should add a database logger:
DB.loggers << Logger.new($stdout)
Now that you know how to see what SQL is being used, let’s jump in and see how to map SQL syntax to Sequel syntax:
Identifiers¶ ↑
In Sequel, SQL identifiers are usually specified as Ruby symbols:
:column # "column"
As you can see, Sequel quotes identifiers by default. Depending on your database, it may uppercase them by default as well:
:column # "COLUMN" on some databases
A plain symbol is usually treated as an unqualified identifier. However, if you are using multiple tables in a query, and you want to reference a column in one of the tables that has the same name as a column in another one of the tables, you need to qualify that reference. Note that you can’t use a period to separate them:
:table.column # calls the column method on the symbol
Also note that specifying the period inside the symbol doesn’t work if you are quoting identifiers:
:"table.column" # "table.column" instead of "table"."column"
There are a few different Sequel methods for creating qualified identifier objects. The recommended way is to explicitly create a qualified identifier by using Sequel.[] to create an identifier and call [] or qualify on that, or by using the Sequel.qualify method with the table and column symbols:
Sequel[:table][:column] # "table"."column" Sequel[:column].qualify(:table) # "table"."column" Sequel.qualify(:table, :column) # "table"."column"
Another way to generate identifiers is to use Sequel’s virtual row support:
DB[:albums].select{name} # SELECT "name" FROM "albums" DB[:albums].select{albums[:name]} # SELECT "albums"."name" FROM "albums"
You can also use the symbol_aref extension for creating qualified identifiers:
Sequel.extension :symbol_aref :table[:column] # "table"."column"
Numbers¶ ↑
In general, Ruby numbers map directly to SQL numbers:
# Integers 1 # 1 -1 # -1 # Floats 1.5 # 1.5 # BigDecimals BigDecimal.new('1000000.123091029') # 1000000.123091029
Strings¶ ↑
In general, Ruby strings map directly to SQL strings:
'name' # 'name' "name" # 'name'
Aliasing¶ ↑
You can use the Sequel.as method to create an alias, and the as method on most Sequel-specific expression objects:
Sequel.as(:column, :alias) # "column" AS "alias" Sequel[:column].as(:alias) # "column" AS "alias" Sequel[:table][:column].as(:alias) # "table"."column" AS "alias" (Sequel[:column] + 1).as(:alias) # ("column" + 1) AS "alias"
You can also use the symbol_as extension for creating aliased identifiers:
Sequel.extension :symbol_as :column.as(:alias) # "column" AS "alias"
If you want to use a derived column list, you can provide an array of column aliases:
Sequel.as(:table, :alias, [:c1, :c2]) # "table" AS "alias"("c1", "c2")
Functions¶ ↑
The easiest way to use SQL functions is via a virtual row:
DB[:albums].select{func.function} # SELECT func() FROM "albums" DB[:albums].select{func(col1, col2)} # SELECT func("col1", "col2") FROM "albums"
You can also use the Sequel.function method on the symbol that contains the function name:
Sequel.function(:func) # func() Sequel.function(:func, :col1, :col2) # func("col1", "col2")
Aggregate Functions¶ ↑
Aggregate functions work the same way as normal functions, since they share the same syntax:
Sequel.function(:sum, :column) # sum(column)
To use the DISTINCT modifier to an aggregate function, call the distinct method on the function expression, which returns a new function expression:
DB[:albums].select{sum(:column).distinct} # SELECT sum(DISTINCT column) FROM albums
If you want to use the wildcard as the sole argument of the aggregate function, use the * method on the function expression:
Sequel.function(:count).* # count(*) DB[:albums].select{count.function.*} # SELECT count(*) FROM albums
Note that Sequel provides helper methods for aggregate functions such as count, sum, min, max, avg, and group_and_count, which handle common uses of aggregate functions.
Window Functions¶ ↑
If the database supports window functions, Sequel can handle them by calling the over method on a function expression:
DB[:albums].select{row_number.function.over} # SELECT row_number() OVER () FROM albums DB[:albums].select{count.function.*.over} # SELECT count(*) OVER () FROM albums DB[:albums].select{function(:col1).over(partition: col2, order: col3)} # SELECT function(col1) OVER (PARTITION BY col2 ORDER BY col3) FROM albums DB[:albums].select{function(c1, c2).over(partition: [c3, c4], order: [c5, c6.desc])} # SELECT function(c1, c2) OVER (PARTITION BY c3, c4 ORDER BY c5, c6 DESC) FROM albums DB[:albums].select{function(c1).over(partition: c2, order: :c3, frame: :rows)} # SELECT function(c1) OVER (PARTITION BY c2 ORDER BY c3 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM albums DB[:albums].select{function(c1).over(partition: c2, order: :c3, frame: {type: :range, start: 1, end: 1})} # SELECT function(c1) OVER (PARTITION BY c2 ORDER BY c3 RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM albums DB[:albums].select{function(c1).over(partition: c2, order: :c3, frame: {type: :groups, start: [2, :preceding], end: [1, :preceding]})} # SELECT function(c1) OVER (PARTITION BY c2 ORDER BY c3 GROUPS BETWEEN 2 PRECEDING AND 1 PRECEDING) FROM albums DB[:albums].select{function(c1).over(partition: c2, order: :c3, frame: {type: :range, start: :preceding, exclude: :current})} # SELECT function(c1) OVER (PARTITION BY c2 ORDER BY c3 RANGE UNBOUNDED PRECEDING EXCLUDE CURRENT ROW) FROM albums
Schema Qualified Functions¶ ↑
If the database supports schema qualified functions, Sequel can handle them by calling the function method on a qualified identifier:
DB[:albums].select{schema[:function].function} # SELECT schema.function() FROM albums DB[:albums].select{schema[:function].function(:col, 2, "a")} # SELECT schema.function(col, 2, 'a') FROM albums
Portable/Emulated Functions¶ ↑
Sequel offers some support for portable SQL functions, allowing you to call standard SQL functions, where Sequel will emulate support on databases that lack native support. Some examples are:
Sequel.char_length(:column) # char_length(column) Sequel.extract(:year, :column) # extract(year FROM column) Sequel.trim(:column) # trim(column)
Equality Operator (=)¶ ↑
Sequel uses hashes to specify equality:
{column: 1} # ("column" = 1)
You can also specify this as an array of two element arrays:
[[:column, 1]] # ("column" = 1)
For expression objects, you can also use the =~ method:
where{column =~ 1} # ("column" = 1)
Not Equal Operator (!=)¶ ↑
You can specify a not equals condition by inverting the hash or array of two element arrays using Sequel.negate or Sequel.~:
Sequel.negate(column: 1) # ("column" != 1) Sequel.negate([[:column, 1]]) # ("column" != 1) Sequel.~(column: 1) # ("column" != 1) Sequel.~([[:column, 1]]) # ("column" != 1)
The difference between the two is that negate only works on hashes and arrays of element arrays, and it negates all entries in the hash or array, while ~ does a general inversion. This is best shown by an example with multiple entries:
Sequel.negate(column: 1, foo: 2) # (("column" != 1) AND (foo != 2)) Sequel.~(column: 1, foo: 2) # (("column" != 1) OR (foo != 2))
You can also use the ~ method on an equality expression:
where{~(column =~ 1)} # ("column" != 1)
Or you can use the !~ method:
where{column !~ 1} # ("column" != 1)
The most common need for not equals is in filters, in which case you can use the exclude method:
DB[:albums].exclude(column: 1) # SELECT * FROM "albums" WHERE ("column" != 1)
Note that exclude does a generalized inversion, similar to Sequel.~.
Inclusion and Exclusion Operators (IN, NOT IN)¶ ↑
Sequel also uses hashes to specify inclusion, and inversions of those hashes to specify exclusion:
{column: [1, 2, 3]} # ("column" IN (1, 2, 3))
Sequel.~(column: [1, 2, 3]) # ("column" NOT IN (1, 2, 3))
As you may have guessed, Sequel switches from an = to an IN when the hash value is an array. It also does this for datasets, which easily allows you to test for inclusion and exclusion in a subselect:
{column: DB[:albums].select(:id)} # ("column" IN (SELECT "id" FROM "albums"))
Sequel.~(column: DB[:albums].select(:id)) # ("column" NOT IN (SELECT "id" FROM "albums"))
Similar to =, you can also use =~ with expressions for inclusion:
where{column =~ [1, 2, 3]} # ("column" IN (1, 2, 3))
and !~ for exclusion:
where{column !~ [1, 2, 3]} # ("column" NOT IN (1, 2, 3))
Sequel also supports the SQL EXISTS operator using Dataset#exists:
DB[:albums].exists # EXISTS (SELECT * FROM albums)
Identity Operators (IS, IS NOT)¶ ↑
Hashes in Sequel use IS if the value is true, false, or nil:
{column: nil} # ("column" IS NULL)
{column: true} # ("column" IS TRUE)
{column: false} # ("column" IS FALSE)
Negation works the same way as it does for equality and inclusion:
Sequel.~(column: nil) # ("column" IS NOT NULL) Sequel.~(column: true) # ("column" IS NOT TRUE) Sequel.~(column: false) # ("column" IS NOT FALSE)
Likewise, =~ works for identity and !~ for negative identity on expressions:
where{column =~ nil} # ("column" IS NULL) where{column !~ nil} # ("column" IS NOT NULL)
Inversion Operator (NOT)¶ ↑
Sequel’s general inversion operator is ~, which works on symbols and most Sequel-specific expression objects:
Sequel.~(:column) # NOT "column"
Note that ~ will actually apply the inversion operation to the underlying object, which is why
Sequel.~(column: 1)
produces (column != 1) instead of NOT (column = 1).
Inequality Operators (< > <= >=)¶ ↑
Sequel defines the inequality operators directly on most Sequel-specific expression objects:
Sequel[:table][:column] > 1 # ("table"."column" > 1) Sequel[:table][:column] < 1 # ("table"."column" < 1) Sequel.function(:func) >= 1 # (func() >= 1) Sequel.function(:func, :column) <= 1 # (func("column") <= 1)
If you want to use them on a symbol, you should call Sequel.[] with the symbol to get an expression object:
Sequel[:column] > 1 # ("column" > 1)
A common use of virtual rows is to handle inequality operators:
DB[:albums].where{col1 > col2} # SELECT * FROM "albums" WHERE ("col1" > "col2")
Standard Mathematical Operators (+ - * /)¶ ↑
The standard mathematical operates are defined on most Sequel-specific expression objects:
Sequel[:column] + 1 # "column" + 1 Sequel[:table][:column] - 1 # "table"."column" - 1 Sequel[:table][:column] * 1 # "table"."column" * 1 Sequel[:column] / 1 # "column" / 1 Sequel[:column] ** 1 # power("column", 1)
You can also call the operator methods directly on the Sequel module:
Sequel.+(:column, 1) # "column" + 1 Sequel.-(Sequel[:table][:column], 1) # "table"."column" - 1 Sequel.*(Sequel[:table][:column], 1) # "table"."column" * 1 Sequel./(:column, 1) # "column" / 1 Sequel.**(:column, 1) # power("column", 1)
Note that since Sequel implements support for Ruby’s coercion protocol, the following also works:
1 + Sequel[:column] 1 - Sequel[:table][:column]
Boolean Operators (AND OR)¶ ↑
Sequel defines the & and | methods on most Sequel-specific expression objects to handle AND and OR:
Sequel[:column1] & :column2 # ("column1" AND "column2") Sequel[{column1: 1}] | {column2: 2} # (("column1" = 1) OR ("column2" = 2)) (Sequel.function(:func) > 1) & :column3 # ((func() > 1) AND "column3")
Note the use of parentheses in the last statement. If you omit them, you won’t get what you expect. Because & has higher precedence than >
Sequel.function(:func) > 1 & :column3
is parsed as:
Sequel.function(:func) > (1 & :column3)
You can also use the Sequel.& and Sequel.| methods:
Sequel.&(:column1, :column2) # ("column1" AND "column2") Sequel.|({column1: 1}, {column2: 2}) # (("column1" = 1) OR ("column2" = 2))
You can use hashes and arrays of two element arrays to specify AND and OR with equality conditions:
{column1: 1, column2: 2} # (("column1" = 1) AND ("column2" = 2))
[[:column1, 1], [:column2, 2]] # (("column1" = 1) AND ("column2" = 2))
As you can see, these literalize with ANDs by default. You can use the Sequel.or method to use OR instead:
Sequel.or(column1: 1, column2: 2) # (("column1" = 1) OR ("column2" = 2))
As you can see in the above examples, Sequel.| and Sequel.or work differently. Sequel.| is for combining an arbitrary number of expressions using OR. If you pass a single argument, Sequel.| will just convert it to a Sequel expression, similar to Sequel.expr. Sequel.or is for taking a single hash or array of two element arrays and combining the elements of that single argument using OR instead of AND:
Sequel.|(column1: 1, column2: 2) # (("column1" = 1) AND ("column2" = 2)) Sequel.or(column1: 1, column2: 2) # (("column1" = 1) OR ("column2" = 2)) Sequel.|({column1: 1}, {column2: 2}) # (("column1" = 1) OR ("column2" = 2)) Sequel.or({column1: 1}, {column2: 2}) # ArgumentError
You’ve already seen the Sequel.negate method, which will use ANDs if multiple entries are used:
Sequel.negate(column1: 1, column2: 2) # (("column1" != 1) AND ("column2" != 2))
To negate while using ORs, the Sequel.~ operator can be used:
Sequel.~(column1: 1, column2: 2) # (("column1" != 1) OR ("column2" != 2))
Note again that Dataset#exclude uses ~, not negate:
DB[:albums].exclude(column1: 1, column2: 2) # SELECT * FROM "albums" WHERE (("column" != 1) OR ("column2" != 2))
Casts¶ ↑
Casting in Sequel is done with the cast method, which is available on most of the Sequel-specific expression objects:
Sequel[:name].cast(:text) # CAST("name" AS text) Sequel['1'].cast(:integer) # CAST('1' AS integer) Sequel[:table][:column].cast(:date) # CAST("table"."column" AS date)
You can also use the Sequel.cast method:
Sequel.cast(:name, :text) # CAST("name" AS text)
Bitwise Mathematical Operators (& | ^ << >> ~)¶ ↑
Sequel allows the use of bitwise mathematical operators on Sequel::SQL::NumericExpression objects:
Sequel[:number] + 1 # => #<Sequel::SQL::NumericExpression ...> (Sequel[:number] + 1) & 5 # (("number" + 1) & 5)
As you can see, when you use the + operator on a symbol, you get a NumericExpression. You can turn an expression a NumericExpression using sql_number:
Sequel[:number].sql_number | 5 # ("number" | 5) Sequel.function(:func).sql_number << 7 # (func() << 7) Sequel.cast(:name, :integer).sql_number >> 8 # (CAST("name" AS integer) >> 8)
Sequel allows you to do the cast and conversion at the same time via cast_numeric:
Sequel[:name].cast_numeric ^ 9 # (CAST("name" AS integer) ^ 9)
Note that &, |, and ~ are already defined to do AND, OR, and NOT on most expressions, so if you want to use the bitwise operators, you need to make sure that they are converted first:
~Sequel[:name] # NOT "name" ~Sequel[:name].sql_number # ~"name"
String Operators (||, LIKE, Regexp)¶ ↑
Sequel allows the use of the string concatenation operator on Sequel::SQL::StringExpression objects, which can be created using the sql_string method on an expression:
Sequel[:name].sql_string + ' - Name' # ("name" || ' - Name')
Just like for the bitwise operators, Sequel allows you to do the cast and conversion at the same time via cast_string:
Sequel[:number].cast_string + ' - Number' # (CAST(number AS varchar(255)) || ' - Number')
Note that similar to the mathematical operators, you cannot switch the order the expression and have it work:
'Name - ' + Sequel[:name].sql_string # raises TypeError
Just like for the mathematical operators, you can use Sequel.[] to wrap the object:
Sequel['Name - '] + :name # ('Name - ' || "name")
The Sequel.join method concatenates all of the elements in the array:
Sequel.join(['Name', :name]) # ('Name' || "name")
Just like Ruby’s String#join, you can provide an argument for a string used to join each element:
Sequel.join(['Name', :name], ' - ') # ('Name' || ' - ' || "name")
For the LIKE operator, Sequel defines the like and ilike methods on most Sequel-specific expression objects:
Sequel[:name].like('A%') # ("name" LIKE 'A%' ESCAPE '\') Sequel[:name].ilike('A%') # ("name" ILIKE 'A%' ESCAPE '\')
You can also use the Sequel.like and Sequel.ilike methods:
Sequel.like(:name, 'A%') # ("name" LIKE 'A%' ESCAPE '\') Sequel.ilike(:name, 'A%') # ("name" ILIKE 'A%' ESCAPE '\')
Note the above syntax for ilike, while Sequel’s default, is specific to PostgreSQL. However, most other adapters override the behavior. For example, on MySQL, Sequel uses LIKE BINARY for like, and LIKE for ilike. If the database supports both case sensitive and case insensitive LIKE, then like will use a case sensitive LIKE, and ilike will use a case insensitive LIKE.
Inverting the LIKE operator works like other inversions:
~Sequel.like(:name, 'A%') # ("name" NOT LIKE 'A%' ESCAPE '\')
Sequel also supports SQL regular expressions on MySQL and PostgreSQL (and SQLite when using the sqlite adapter with the :setup_regexp_function Database option). You can use these by passing a Ruby regular expression to like or ilike, or by making the regular expression a hash value:
Sequel.like(:name, /^A/) # ("name" ~ '^A') ~Sequel.ilike(:name, /^A/) # ("name" !~* '^A') {name: /^A/i} # ("name" ~* '^A') Sequel.~(name: /^A/) # ("name" !~ '^A')
Note that using ilike with a regular expression will always make the regexp case insensitive. If you use like or the hash with regexp value, it will only be case insensitive if the Regexp itself is case insensitive.
Order Specifications (ASC, DESC)¶ ↑
Sequel supports specifying ascending or descending order using the asc and desc method on most Sequel-specific expression objects:
Sequel[:column].asc # "column" ASC Sequel[:table][:column].desc # "table"."column" DESC
You can also use the Sequel.asc and Sequel.desc methods:
Sequel.asc(:column) # "column" ASC Sequel.desc(Sequel[:table][:column]) # "table"."column" DESC
On some databases, you can specify null ordering:
Sequel.asc(:column, nulls: :first) # "column" ASC NULLS FIRST Sequel.desc(Sequel[:table][:column], nulls: :last) # "table"."column" DESC NULLS LAST
All Columns (.*)¶ ↑
To select all columns in a table, Sequel supports the * method on identifiers and qualified identifiers without an argument:
Sequel[:table].* # "table".* Sequel[:schema][:table].* # "schema"."table".*
CASE statements¶ ↑
Sequel supports SQL CASE statements using the Sequel.case method. The first argument is a hash or array of two element arrays representing the conditions, the second argument is the default value (ELSE). The keys of the hash (or first element in each array) is the WHEN condition, and the values of the hash (or second element in each array) is the THEN result. Here are some examples:
Sequel.case({column: 1}, 0) # (CASE WHEN "column" THEN 1 ELSE 0 END) Sequel.case([[:column, 1]], 0) # (CASE WHEN "column" THEN 1 ELSE 0 END) Sequel.case({{column: nil}=>1}, 0) # (CASE WHEN (column IS NULL) THEN 1 ELSE 0 END)
If the hash or array has multiple arguments, multiple WHEN clauses are used:
Sequel.case({c: 1, d: 2}, 0) # (CASE WHEN "c" THEN 1 WHEN "d" THEN 2 ELSE 0 END) Sequel.case([[:c, 1], [:d, 2]], 0) # (CASE WHEN "c" THEN 1 WHEN "d" THEN 2 ELSE 0 END)
If you provide a 3rd argument to Sequel.case, it goes between CASE and WHEN:
Sequel.case({2=>1, 3=>5}, 0, :column) # (CASE column WHEN 2 THEN 1 WHEN 3 THEN 5 ELSE 0 END)
Subscripts/Array Access ([])¶ ↑
Sequel supports SQL subscripts using the sql_subscript method on most Sequel-specific expression objects:
Sequel[:column].sql_subscript(3) # column[3] Sequel[:table][:column].sql_subscript(3) # table.column[3]
You can also use the Sequel.subscript method:
Sequel.subscript(:column, 3) # column[3]
Just like in SQL, you can use any expression as a subscript:
Sequel.subscript(:column, Sequel.function(:func)) # column[func()]
Building Queries in Sequel¶ ↑
In Sequel, the SQL queries are build with method chaining.
Creating Datasets¶ ↑
You generally start creating a dataset by calling Dataset#[] with a symbol specifying the table name:
DB[:albums] # SELECT * FROM albums
If you want to select from multiple FROM tables, use multiple arguments:
DB[:albums, :artists] # SELECT * FROM albums, artists
If you don’t want to select from any FROM tables, just call dataset:
DB.dataset # SELECT *
Chaining Methods¶ ↑
Once you have your dataset object, you build queries by chaining methods, usually with one method per clause in the query:
DB[:albums].select(:id, :name).where(Sequel.like(:name, 'A%')).order(:name) # SELECT id, name FROM albums WHERE (name LIKE 'A%' ESCAPE '\') ORDER BY name
Note that the order of your method chain is not usually important unless you have multiple methods that affect the same clause:
DB[:albums].order(:name).where(Sequel.like(:name, 'A%')).select(:id, :name) # SELECT id, name FROM albums WHERE (name LIKE 'A%' ESCAPE '\') ORDER BY name
Using the Same Dataset for SELECT, INSERT, UPDATE, and DELETE¶ ↑
Also note that while the SELECT clause is displayed when you look at a dataset, a Sequel dataset can be used for INSERT, UPDATE, and DELETE as well. Here’s an example:
ds = DB[:albums] ds.all # SELECT * FROM albums ds.insert(name: 'RF') # INSERT INTO albums (name) VALUES ('RF') ds.update(name: 'RF') # UPDATE albums SET name = 'RF' ds.delete # DELETE FROM albums
In general, the insert, update, and delete methods use the appropriate clauses you defined on the dataset:
ds = DB[:albums].where(id: 1) ds.all # SELECT * FROM albums WHERE (id = 1) ds.insert(name: 'RF') # INSERT INTO albums (name) VALUES ('RF') ds.update(name: 'RF') # UPDATE albums SET name = 'RF' WHERE (id = 1) ds.delete # DELETE FROM albums WHERE (id = 1)
Note how update and delete used the where argument, but insert did not, because INSERT doesn’t use a WHERE clause.
Methods Used for Each SQL Clause¶ ↑
To see which methods exist that affect each SQL clause, see the “Dataset Basics” guide.