module DB::QueryMethods(Stmt)

Overview

Methods to allow querying a database. All methods accepts a query : String and a set arguments.

Three kind of statements can be performed:

  1. #exec waits no record response from the database. An ExecResult is returned.
  2. #scalar reads a single value of the response. A union of possible values is returned.
  3. #query returns a ResultSet that allows iteration over the rows in the response and column information.

Arguments can be passed by position or as an array.

db.query("SELECT name FROM ... WHERE age > ?", age)
db.query("SELECT name FROM ... WHERE age > ?", args: [age])

Convention of mapping how arguments are mapped to the query depends on each driver.

Including QueryMethods requires a build(query) : Statement method that is not expected to be called directly.

Direct including types

Defined in:

db/query_methods.cr

Instance Method Summary

Instance Method Detail

def exec(query, *args_, args : Array? = nil) #

Performs the #query and returns an ExecResult


[View source]
def query(query, *args_, args : Array? = nil) #

Executes a query and returns a ResultSet with the results. The ResultSet must be closed manually.

result = db.query "select name from contacts where id = ?", 10
begin
  if result.move_next
    id = result.read(Int32)
  end
ensure
  result.close
end

Note: to use a dynamic list length of arguments use args: keyword argument.

result = db.query "select name from contacts where id = ?", args: [10]

[View source]
def query(query, *args_, args : Array? = nil, &) #

Executes a query and yields a ResultSet with the results. The ResultSet is closed automatically.

db.query("select name from contacts where age > ?", 18) do |rs|
  rs.each do
    name = rs.read(String)
  end
end

[View source]
def query_all(query, *args_, args : Array? = nil, as type : Class) #

Executes a query and returns an array where the value of each row is read as the given type.

names = db.query_all "select name from contacts", as: String

[View source]
def query_all(query, *args_, args : Array? = nil, as types : NamedTuple) #

Executes a query and returns an array where each row is read as a named tuple of the given types (the keys of the named tuple are not necessarily the column names).

contacts = db.query_all "select name, age from contacts", as: {name: String, age: Int32}

[View source]
def query_all(query, *args_, args : Array? = nil, &block : ResultSet -> U) : Array(U) forall U #

Executes a query and yield a ResultSet positioned at the beginning of each row, returning an array of the values of the blocks.

names = db.query_all "select name from contacts", &.read(String)

[View source]
def query_all(query, *args_, args : Array? = nil, as types : Tuple) #

Executes a query and returns an array where each row is read as a tuple of the given types.

contacts = db.query_all "select name, age from contacts", as: {String, Int32}

[View source]
def query_each(query, *args_, args : Array? = nil, &) #

Executes a query and yields the ResultSet once per each row. The ResultSet is closed automatically.

db.query_each "select name from contacts" do |rs|
  puts rs.read(String)
end

[View source]
def query_one(query, *args_, args : Array? = nil, &block : ResultSet -> U) : U forall U #

Executes a query that expects a single row and yields a ResultSet positioned at that first row.

The given block must not invoke move_next on the yielded result set.

Raises DB::NoResultsError if there were no rows. Raises DB::Error if there were more than one row.

name = db.query_one "select name from contacts where id = ?", 18, &.read(String)

[View source]
def query_one(query, *args_, args : Array? = nil, as types : Tuple) #

Executes a query that expects a single row and returns it as a tuple of the given types.

Raises DB::NoResultsError if there were no rows. Raises DB::Error if there were more than one row.

db.query_one "select name, age from contacts where id = ?", 1, as: {String, Int32}

[View source]
def query_one(query, *args_, args : Array? = nil, as types : NamedTuple) #

Executes a query that expects a single row and returns it as a named tuple of the given types (the keys of the named tuple are not necessarily the column names).

Raises DB::NoResultsError if there were no rows. Raises DB::Error if there were more than one row.

db.query_one "select name, age from contacts where id = ?", 1, as: {name: String, age: Int32}

[View source]
def query_one(query, *args_, args : Array? = nil, as type : Class) #

Executes a query that expects a single row and returns the first column's value as the given type.

Raises DB::NoResultsError if there were no rows. Raises DB::Error if there were more than one row.

db.query_one "select name from contacts where id = ?", 1, as: String

[View source]
def query_one?(query, *args_, args : Array? = nil, as type : Class) #

Executes a query that expects a single row and returns the first column's value as the given type.

Returns nil if there were no rows.

Raises DB::Error if there were more than one row.

name = db.query_one? "select name from contacts where id = ?", 1, as: String
typeof(name) # => String?

[View source]
def query_one?(query, *args_, args : Array? = nil, as types : NamedTuple) #

Executes a query that expects a single row and returns it as a named tuple of the given types (the keys of the named tuple are not necessarily the column names).

Returns nil if there were no rows.

Raises DB::Error if there were more than one row.

result = db.query_one? "select name, age from contacts where id = ?", 1, as: {age: String, name: Int32}
typeof(result) # => NamedTuple(age: String, name: Int32) | Nil

[View source]
def query_one?(query, *args_, args : Array? = nil, as types : Tuple) #

Executes a query that expects a single row and returns it as a tuple of the given types.

Returns nil if there were no rows.

Raises DB::Error if there were more than one row.

result = db.query_one? "select name, age from contacts where id = ?", 1, as: {String, Int32}
typeof(result) # => Tuple(String, Int32) | Nil

[View source]
def query_one?(query, *args_, args : Array? = nil, &block : ResultSet -> U) : U? forall U #

Executes a query that expects at most a single row and yields a ResultSet positioned at that first row.

Returns nil, not invoking the block, if there were no rows.

Raises DB::Error if there were more than one row (this ends up invoking the block once).

name = db.query_one? "select name from contacts where id = ?", 18, &.read(String)
typeof(name) # => String | Nil

[View source]
def scalar(query, *args_, args : Array? = nil) #

Performs the #query and returns a single scalar value

puts db.scalar("SELECT MAX(name)").as(String) # => (a String)

[View source]