class Onyx::SQL::Query(T)

Overview

Type-safe SQL query builder.

Cheatsheet

For your convenience, here is the full list of Query methods:

Basics

Query is a generic class and it can be only of a Model type (not bare Serializables). The model must have Model::Options annotation with both :table and :primary_key options set.

Query implements most of the SQL syntax. It does not make queries to a DB itself, it only builds the SQL string with SQL-ready params. Its main purposes are to make the application less error-prone with type-safety and reduce the usage of raw SQL within the code-base. However, it's not a complete replacement for SQL. You have to know SQL and may be would have to still use it in some cases.

class User
  include Onyx::SQL::Model

  schema users do
    pkey id : Int32
  end
end

query = Query(User).new  # OK
query = Query(Int32).new # Totally not OK

To build a query, call #build on it:

query.select("*")
query.build # => {"SELECT users.* FROM users", <EmptyParams>}

Once the query is built, pass it either to a raw DB instance:

sql, params = query.build
result_set = db.query(sql, params)

# Or you can *splat* the two-element Tuple resulting from Query#build
result_set = db.query(*query.build)

users = User.from_rs(result_set)
pp typeof(users) # => Array(User)

Or give it to a Repository instance so it builds it and extracts its generic type implicitly:

users = repo.query(query)

# query has User as its generic type, and the repo knows it
pp typeof(users) # => Array(User)

As #build returns a pair of raw SQL string and params, you can combine its usage as you want:

db.scalar(*query.build)
db.exec(query.build[0]) # Will omit params
repo.scalar(query)
repo.query(User, query.build[0], query.build[1])
repo.exec(*query)

Type safety

Most of the Query methods are type-safe. For example, #where:

query = Query(User).new.where(name: "John")

In this example, the query would look for an instance variable with getter called #name in the User class. And if it does not find it, it raises in compilation time with meaningful error message:

Class `User` has neither field nor reference with key `:name` eligible for `Query(User)#where` call

And if you pass the wrong type to the #where call, it would raise as well:

query.where(name: 42)
Invalid compile-time type `Int32` for argument `:name` in `Query(User)#where` call. Expected: `(String | Nil)`

Other methods, such as #select or #join without block are a bit hacky and rely on Crystal's enum autocasting feature. If you mistyped a field or reference name, you'll see a error like No overload matches ... with types Symbol. If you get such an error, then you are very much likely to have a typo in your code, check the query call arguments.

Model shortucts

Model has both class and instance shortcuts. Class shortcuts are available for almost every Query method, for example:

User.where(id: 42) == Query(User).new.where(id: 42)

See Model::ClassQueryShortcuts. Instance shortucts are Model#insert, Model#update and Model#delete.

Defined in:

onyx-sql/query/delete.cr
onyx-sql/query/group_by.cr
onyx-sql/query/having.cr
onyx-sql/query/insert.cr
onyx-sql/query/join.cr
onyx-sql/query/limit.cr
onyx-sql/query/offset.cr
onyx-sql/query/order_by.cr
onyx-sql/query/returning.cr
onyx-sql/query/select.cr
onyx-sql/query/set.cr
onyx-sql/query/update.cr
onyx-sql/query/where.cr
onyx-sql/query/wherish.cr
onyx-sql/query.cr

Instance Method Summary

Instance Method Detail

def ==(other : self) #

Compare queries.


[View source]
def all #

Alias of #limit(nil).


[View source]
def and(clause : String) #

A shorthand for calling #and_where or #and_having depending on the latest call (#where by default).

BUG It will raise in runtime if called after #having.


[View source]
def and(clause : String, *params) #

A shorthand for calling #and_where or #and_having depending on the latest call (#where by default).

BUG It will raise in runtime if called after #having.


[View source]
def and(**args : **U) forall U #

A shorthand for calling #and_where or #and_having depending on the latest call (#where by default).

BUG It will raise in runtime if called after #having.


[View source]
def and_having(clause : String, *params) #

Add AND clause with params to HAVING.

query.and_having("foo = ?", "bar") # => "HAVING (...) AND (foo = ?)"

[View source]
def and_having(clause : String) #

Add AND clause to HAVING.

query.and_having("foo IS NULL") # => "HAVING (...) AND (foo IS NULL)"

[View source]
def and_having(**values : **U) forall U #

This method will raise in compilation-time, because having a HAVING query with a Model's attributes makes no sense.


[View source]
def and_having_not(clause : String) #

Add AND NOT clause to HAVING.

query.and_having_not("foo IS NULL") # => "HAVING (...) AND NOT (foo IS NULL)"

[View source]
def and_having_not(clause : String, *params) #

Add AND NOT clause with params to HAVING.

query.and_having_not("foo = ?", "bar") # => "HAVING (...) AND NOT (foo = ?)"

[View source]
def and_having_not(**values : **U) forall U #

This method will raise in compilation-time, because having a HAVING query with a Model's attributes makes no sense.


[View source]
def and_not(clause : String) #

A shorthand for calling #and_where_not or #and_having_not depending on the latest call (#where by default).

BUG It will raise in runtime if called after #having.


[View source]
def and_not(clause : String, *params) #

A shorthand for calling #and_where_not or #and_having_not depending on the latest call (#where by default).

BUG It will raise in runtime if called after #having.


[View source]
def and_not(**args : **U) forall U #

A shorthand for calling #and_where_not or #and_having_not depending on the latest call (#where by default).

BUG It will raise in runtime if called after #having.


[View source]
def and_where(**values : **U) forall U #

Add AND clause with values to WHERE.

and_where(id: 42) # => "WHERE (...) AND (id = ?)"

[View source]
def and_where(clause : String, *params) #

Add AND clause with params to WHERE.

query.and_where("foo = ?", "bar") # => "WHERE (...) AND (foo = ?)"

[View source]
def and_where(clause : String) #

Add AND clause to WHERE.

query.and_where("foo IS NULL") # => "WHERE (...) AND (foo IS NULL)"

[View source]
def and_where_not(clause : String) #

Add AND NOT clause to WHERE.

query.and_where_not("foo IS NULL") # => "WHERE (...) AND NOT (foo IS NULL)"

[View source]
def and_where_not(**values : **U) forall U #

Add AND NOT clause with values to WHERE.

and_where_not(id: 42) # => "WHERE (...) AND NOT (id = ?)"

[View source]
def and_where_not(clause : String, *params) #

Add AND NOT clause with params to WHERE.

query.and_where_not("foo = ?", "bar") # => "WHERE (...) AND NOT (foo = ?)"

[View source]
def build(index_params = false) : Tuple(String, Enumerable(DB::Any)) #

Build this query, returning its SQL representation and Enumerable of DB-ready params. Pass true to replace "?" query arguments with "$n", which would work for PostgreSQL.


[View source]
def delete #

Mark this query as a DELETE one. It's recommended to call #where as well:

query = User.delete.where(id: 17)
query.build # => {"DELETE FROM users WHERE id = ?", {17}}

Models have a handy Model#delete shortcut:

user.delete == User.delete.where(id: user.id)

[View source]
def group_by(values : Enumerable(String)) #

Add GROUP_BY clause.


[View source]
def group_by(*values : String) #

Add GROUP_BY clause.


[View source]
def having(or : Bool = false, not : Bool = false, **values : **U) : self forall U #

This method will raise in compilation-time, because having a HAVING query with a Model's attributes makes no sense.


[View source]
def having(clause : String, params : Enumerable(DB::Any), or : Bool = false, not : Bool = false) #

Add HAVING clause with params.

query = User.having("foo = ?", "bar")
query.build # => {"HAVING (foo = ?)", {"bar"}}

Multiple calls concatenate clauses with AND:

query = User.having("foo = ?", "bar").having("baz = ?", 42)
query.build # => {"HAVING (foo = ?) AND (baz = ?)", {"bar", 42}}

[View source]
def having(clause : String, or : Bool = false, not : Bool = false) #

Add HAVING clause without params.

query = User.having("foo IS NULL")
query.build # => {"HAVING (foo IS NULL)", {}}

Multiple calls concatenate clauses with AND:

query = User.having("foo IS NULL").having("bar IS NOT NULL")
query.build # => {"HAVING (foo IS NULL) AND (bar IS NOT NULL)", {}}

[View source]
def having(clause : String, *params : DB::Any, or : Bool = false, not : Bool = false) #

Add HAVING clause with params.

query = User.having("foo = ?", "bar")
query.build # => {"HAVING (foo = ?)", {"bar"}}

Multiple calls concatenate clauses with AND:

query = User.having("foo = ?", "bar").having("baz = ?", 42)
query.build # => {"HAVING (foo = ?) AND (baz = ?)", {"bar", 42}}

[View source]
def having_not(clause, *params) #

Add NOT clause with params to HAVING.

query.having_not("foo = ?", "bar") # => "HAVING (...) AND NOT (foo = ?)"

[View source]
def having_not(clause) #

Add NOT clause to HAVING.

query.having_not("foo IS NULL") # => "HAVING (...) AND NOT (foo IS NULL)"

[View source]
def insert(name : T::Field | T::Reference | String, value : String) #

[View source]
def insert(**values : **U) : self forall U #

Mark this query as INSERT one and insert the arguments. It's a type-safe method. However, it will call .not_nil! on references' primary keys, thus it can raise NilAssertionError in runtime:

Post.insert(content: "foo", author: user) # Will raise NilAssertionError in runtime if `user.id` is `nil`

TODO Consider inserting explicit reference keys instead, e.g. Post.insert(author_id: user.id.not_nil!) (when Model.db_values allows to).

Example:

query = User.insert(name: "John", age: 18)
query.build # => {"INSERT INTO users (name, age) VALUES (?, ?)", {"John", 18}}

Models have a handy Model#insert shortcut. But it is less type-safe (regarding to not_null variables, see in Model#insert docs):

user.insert == User.insert(id: user.id, name: user.name.not_nil!)

[View source]
def join(table : String, on : String, as _as : String? = nil, type : JoinType = :inner) #

Add explicit JOIN clause. If the query hasn't had any #select calls before, then #select(T) is called on it.

query.join("a", on: "a.id = b.id", as: "c", :right) # => RIGHT JOIN a ON a.id = b.id AS c

[View source]
def join(*, on : String? = nil, as _as : String? = nil, type : JoinType = :inner, **values : **U, &block) : self forall U #

Add JOIN clause by a model reference. Yields another Query instance which has the reference's type. It then merges the yielded query with the main query.

class User
  include Onyx::SQL::Model

  schema users do
    pkey id : Int32
    type username : String
    type authored_posts : Array(Post), foreign_key: "author_id"
  end
end

class Post
  include Onyx::SQL::Model

  schema posts do
    pkey id : Int32
    type body : String
    type author : User, key: "author_id"
  end
end

query = Post
  .join(author: true) do |q|
    pp typeof(q)        # => Query(User)
    q.select(:username) # :username is looked up in User in compilation time
    q.where(id: 42)
  end

query.build # => {"SELECT posts.*, author.username FROM posts INNER JOIN users ON posts.author_id = author.id AS author WHERE author.id = ?", {42}}

In fact, the resulting SQL slightly differs from the example above:

SELECT posts.*, '' AS _author, author.username, '' AS _author FROM posts ...

The "AS _author" thing is a marker, which is used to preload references on Serializable.from_rs call:

posts = repo.query(query)
pp posts # => [<Post @author=<User @id=42 @username="John">>, <Post @author=<User @id=42 @username="John">>, ...]

Read more about preloading references in Serializable docs.

If parent query hasn't had any #select calls before, then #select(T) is called on it.

NOTE The syntax is about to be improved from join(author: true) to join(:author). See the relevant forum topic.


[View source]
def join(reference : T::Reference, on : String? = nil, as _as : String = reference.to_s.underscore, type : JoinType = :inner) #

Add JOIN clause by a model reference without yielding a sub-query. If the query hasn't had any #select calls before, then #select(T) is called on it.

query = Post.join(:author).where(id: 17)
query.build # => {"SELECT posts.* FROM posts INNER JOIN users AS author ON posts.author_id = author.id WHERE posts.id = ?", {17}}

Note that in this case there are no markers, so a post's @author reference would not have @username variable filled. However, the reference itself would still present, as a post row itself contains the "author_id" column, which would be put into a post's @author instance upon calling Serializable.from_rs:

post = repo.query(Post.select(Post, "author.username").join(:author)).first
# SELECT posts.*, author.username FROM posts ...
pp post # => <Post @author=<User @id=17 @username=nil>>

[View source]
def limit(limit : Int32?) #

Add LIMIT clause. nil argument cancels it.


[View source]
def offset(offset : Int32?) #

Add OFFSET clause. nil argument cancels it.


[View source]
def one #

Alias of #limit(1).


[View source]
def or(clause : String, *params) #

A shorthand for calling #or_where or #or_having depending on the latest call (#where by default).

BUG It will raise in runtime if called after #having.


[View source]
def or(clause : String) #

A shorthand for calling #or_where or #or_having depending on the latest call (#where by default).

BUG It will raise in runtime if called after #having.


[View source]
def or(**args : **U) forall U #

A shorthand for calling #or_where or #or_having depending on the latest call (#where by default).

BUG It will raise in runtime if called after #having.


[View source]
def or_having(clause : String) #

Add OR clause to HAVING.

query.or_having("foo IS NULL") # => "HAVING (...) OR (foo IS NULL)"

[View source]
def or_having(clause : String, *params) #

Add OR clause with params to HAVING.

query.or_having("foo = ?", "bar") # => "HAVING (...) OR (foo = ?)"

[View source]
def or_having(**values : **U) forall U #

This method will raise in compilation-time, because having a HAVING query with a Model's attributes makes no sense.


[View source]
def or_having_not(**values : **U) forall U #

This method will raise in compilation-time, because having a HAVING query with a Model's attributes makes no sense.


[View source]
def or_having_not(clause : String, *params) #

Add OR NOT clause with params to HAVING.

query.or_having_not("foo = ?", "bar") # => "HAVING (...) OR NOT (foo = ?)"

[View source]
def or_having_not(clause : String) #

Add OR NOT clause to HAVING.

query.or_having_not("foo IS NULL") # => "HAVING (...) OR NOT (foo IS NULL)"

[View source]
def or_not(clause : String) #

A shorthand for calling #or_where_not or #or_having_not depending on the latest call (#where by default).

BUG It will raise in runtime if called after #having.


[View source]
def or_not(clause : String, *params) #

A shorthand for calling #or_where_not or #or_having_not depending on the latest call (#where by default).

BUG It will raise in runtime if called after #having.


[View source]
def or_not(**args : **U) forall U #

A shorthand for calling #or_where_not or #or_having_not depending on the latest call (#where by default).

BUG It will raise in runtime if called after #having.


[View source]
def or_where(clause : String) #

Add OR clause to WHERE.

query.or_where("foo IS NULL") # => "WHERE (...) OR (foo IS NULL)"

[View source]
def or_where(**values : **U) forall U #

Add OR clause with values to WHERE.

or_where(id: 42) # => "WHERE (...) OR (id = ?)"

[View source]
def or_where(clause : String, *params) #

Add OR clause with params to WHERE.

query.or_where("foo = ?", "bar") # => "WHERE (...) OR (foo = ?)"

[View source]
def or_where_not(clause : String) #

Add OR NOT clause to WHERE.

query.or_where_not("foo IS NULL") # => "WHERE (...) OR NOT (foo IS NULL)"

[View source]
def or_where_not(clause : String, *params) #

Add OR NOT clause with params to WHERE.

query.or_where_not("foo = ?", "bar") # => "WHERE (...) OR NOT (foo = ?)"

[View source]
def or_where_not(**values : **U) forall U #

Add OR NOT clause with values to WHERE.

or_where_not(id: 42) # => "WHERE (...) OR NOT (id = ?)"

[View source]
def order_by(value : T::Field | String, order : Order? = nil) #

Add ORDER BY clause by either a model field or explicit String value.

q = User.all.order_by(:id, :desc)
q.build # => {"SELECT users.* FROM users ORDER BY id DESC"}

q = User.all.order_by("foo(bar)")
q.build # => {"SELECT users.* FROM users ORDER BY foo(bar)"}

[View source]
def returning(values : Enumerable(T::Field | T::Reference | Char | String)) #

Add RETURNING clause by either model field or reference or explicit Char or String.

NOTE All RETURNING clauses are removed on Repository#exec(query) call.

NOTE SQLite does not support RETURNING clause.

q = user.insert.returning(:id, :name)
q.build # => {"INSERT INTO users ... RETURNING id, name"}

q = user.insert.returning("foo")
q.build # => {"INSERT INTO users ... RETURNING foo"}

[View source]
def returning(klass : T.class) #

Add RETURNING asterisk clause for the whole T table.

NOTE All RETURNING clauses are removed on Repository#exec(query) call.

NOTE SQLite does not support RETURNING clause.

Post.returning(Post) # => RETURNING posts.*

[View source]
def returning(klass : T.class, *values : T::Field | T::Reference | Char | String) #

Add RETURNING asterisk clause for the whole T table and optionally values.

NOTE All RETURNING clauses are removed on Repository#exec(query) call.

NOTE SQLite does not support RETURNING clause.

Post.returning(Post, :id) # => RETURNING posts.*, posts.id

[View source]
def returning(*values : T::Field | T::Reference | Char | String) #

Add RETURNING clause by either model field or reference or explicit Char or String.

NOTE All RETURNING clauses are removed on Repository#exec(query) call.

NOTE SQLite does not support RETURNING clause.

q = user.insert.returning(:id, :name)
q.build # => {"INSERT INTO users ... RETURNING id, name"}

q = user.insert.returning("foo")
q.build # => {"INSERT INTO users ... RETURNING foo"}

[View source]
def select(values : Enumerable(T::Field | T::Reference | Char | String)) #

Add SELECT clause by either model field or reference or explicit Char or String.

If no #select is called on this query, then it would select everything ("*"). Otherwise it would select those columns only which were specified explicitly.

q = User.all
q.build # => {"SELECT * FROM users"}

q = User.select(:id, :name)
q.build # => {"SELECT users.id, users.name FROM users"}

q = User.select("foo")
q.build # => {"SELECT foo FROM users"}

# Note that in this case the author reference would not be
# actually preloaded, because the resulting query is missing markers
q = Post.join(:author)
q.build # => {"SELECT * FROM posts JOIN users ..."}

# That's better
q = Post.select(Post).join(author: true) { |x| x.select(:name) }
q.build # => {"SELECT posts.*, '' AS _author, author.name ..."}

[View source]
def select(klass : T.class) #

Add SELECT asterisk clause for the whole T table.

Post.select(Post, :id) # => SELECT posts.*, posts.id

[View source]
def select(nil_value : Nil) #

Reset current query selects. This will lead to empty select clause for this partucular query. It's useful when on joining. However, it would raise in runtime if the resulting query has no actual selects.

Post.select(nil).build # "Cannot build a query with empty SELECT clause" runtime error

q = Post.select(nil).join(author: true) { |x| x.select(:username) }
q.build # => {"SELECT author.username FROM posts JOIN user AS author ..."}

NOTE If you call it after join, then it will erase everything:

Post.join(author: true) { |x| x.select(:username) }.select(nil).build
# Cannot build a query with empty SELECT clause

[View source]
def select(*values : T::Field | T::Reference | Char | String) #

Add SELECT clause by either model field or reference or explicit Char or String.

If no #select is called on this query, then it would select everything ("*"). Otherwise it would select those columns only which were specified explicitly.

q = User.all
q.build # => {"SELECT * FROM users"}

q = User.select(:id, :name)
q.build # => {"SELECT users.id, users.name FROM users"}

q = User.select("foo")
q.build # => {"SELECT foo FROM users"}

# Note that in this case the author reference would not be
# actually preloaded, because the resulting query is missing markers
q = Post.join(:author)
q.build # => {"SELECT * FROM posts JOIN users ..."}

# That's better
q = Post.select(Post).join(author: true) { |x| x.select(:name) }
q.build # => {"SELECT posts.*, '' AS _author, author.name ..."}

[View source]
def select(klass : T.class, *values : T::Field | T::Reference | Char | String) #

Add SELECT asterisk clause for the whole T table and values.

Post.select(Post, :id) # => SELECT posts.*, posts.id

[View source]
def set(clause : String) #

Add explicit SET clause and mark this query as UPDATE one.

query.set("salary = salary * 2")

[View source]
def set(clause : String, *params : DB::Any) #

Add explicit SET clause with params and mark this query as UPDATE one.

query.set("salary = salary * ?", 2)

[View source]
def set(**values : **U) : self forall U #

Add SET clauses from values and mark this query as UPDATE one. It's a type-safe method. However, it will call .not_nil! on references' primary keys, thus it can raise NilAssertionError in runtime:

Post.update.set(author: user) # Will raise NilAssertionError in runtime if `user.id` is `nil`

TODO Consider updating explicit reference keys instead, e.g. Post.update.set(author_id: user.id.not_nil!) (when Model.db_values allows to).

Example:

query = User.update.set(name: "Jake", age: 17)
query.build # => {"UPDATE users SET name = ?, age = ?", {"Jake", 17}}

[View source]
def type : Type #

The query type. It's automatically updated on appropriate methods call (e.g. it changes to :insert on #insert call)


[View source]
def update #

Mark this query as UPDATE one. This is for convenience only, and the #build would raise if there are no SET clauses in the query.

User.update.build             # Runtime error: No values to SET in the UPDATE query
User.update.set(name: "Jane") # OK

Models have a handy Model#update shortcut:

changeset = user.changeset
changeset.update(name: "Jake")
user.update(changeset) == User.update.set(name: "Jake").where(id: user.id)

[View source]
def where(or : Bool = false, not : Bool = false, **values : **U) : self forall U #

Add WHERE clause with values. All clauses in a single call are concatenated with AND.

It's a type-safe method. However, it will call .not_nil! on references' primary keys, thus it can raise NilAssertionError in runtime:

Post.where(author: user) # Will raise NilAssertionError in runtime if `user.id` is `nil`

Consider using explicit reference keys instead in this case, e.g.

Post.where(author_id: user.id.not_nil!)

Example:

query = User.where(name: "John", age: 18)
query.build # => {"SELECT ... FROM users WHERE (name = ? AND age = ?)", {"John", 18}}

[View source]
def where(clause : String, params : Enumerable(DB::Any), or : Bool = false, not : Bool = false) #

Add WHERE clause with params.

query = User.where("foo = ?", "bar")
query.build # => {"WHERE (foo = ?)", {"bar"}}

Multiple calls concatenate clauses with AND:

query = User.where("foo = ?", "bar").where("baz = ?", 42)
query.build # => {"WHERE (foo = ?) AND (baz = ?)", {"bar", 42}}

[View source]
def where(clause : String, or : Bool = false, not : Bool = false) #

Add WHERE clause without params.

query = User.where("foo IS NULL")
query.build # => {"WHERE (foo IS NULL)", {}}

Multiple calls concatenate clauses with AND:

query = User.where("foo IS NULL").where("bar IS NOT NULL")
query.build # => {"WHERE (foo IS NULL) AND (bar IS NOT NULL)", {}}

[View source]
def where(clause : String, *params : DB::Any, or : Bool = false, not : Bool = false) #

Add WHERE clause with params.

query = User.where("foo = ?", "bar")
query.build # => {"WHERE (foo = ?)", {"bar"}}

Multiple calls concatenate clauses with AND:

query = User.where("foo = ?", "bar").where("baz = ?", 42)
query.build # => {"WHERE (foo = ?) AND (baz = ?)", {"bar", 42}}

[View source]
def where_not(clause) #

Add NOT clause to WHERE.

query.where_not("foo IS NULL") # => "WHERE (...) AND NOT (foo IS NULL)"

[View source]
def where_not(clause, *params) #

Add NOT clause with params to WHERE.

query.where_not("foo = ?", "bar") # => "WHERE (...) AND NOT (foo = ?)"

[View source]
def where_not(**values) #

Add NOT clause with values to WHERE.

query.where_not(id: 42) # => "WHERE (...) AND NOT (id = ?)"

[View source]