class Onyx::SQL::BulkQuery(T)

Overview

Bulk query builder. It allows to #insert and #delete multiple instances in one SQL query. Its API is similar to Query — you can #build it and turn #to_s, as well as pass it to a Repository.

Enumerable is monkey-patched with argless .insert and update methods.

users = [User.new(name: "Jake"), User.new(name: "John")]

query = BulkQuery.insert(users)
# Or
query = users.insert

query.build == {"INSERT INTO users (name) VALUES (?), (?)", ["Jake", "John"]}

users = repo.query(users.insert.returning(:id))

Defined in:

onyx-sql/bulk_query/delete.cr
onyx-sql/bulk_query/insert.cr
onyx-sql/bulk_query/returning.cr
onyx-sql/bulk_query/where.cr
onyx-sql/bulk_query.cr

Constructors

Class Method Summary

Instance Method Summary

Constructor Detail

def self.new(type : Type, instances) #

[View source]

Class Method Detail

def self.delete(instances : Enumerable(T)) #

Create a bulk deletion query. Enumerable has this method too.

NOTE Deletion relies on instances' primary key values. The query would raise NilAssertionError upon building if any of the instances has its primary key nil.

BulkQuery.delete(users) == users.delete

[View source]
def self.insert(instances : Enumerable(T)) #

Create a bulk insertion query. Enumerable has this method too.

The resulting query would have only those columns to insert which have a non-nil value in at least one instance.

NOTE Similar to Model#insert, this method would raise NilAssertionError if any of not_null model variables is actually nil.

NOTE SQLite3 does not support DEFAULT keyword as an insertion value. A query would raise near "DEFAULT": syntax error (SQLite3::Exception) if any of the instances has a model variable with default: true and actual nil value. However, if all instances has this variable nil, the column is not inserted at all, therefore no error is raised.

BulkQuery.insert(users) == users.insert
user1 = User.new(name: "Jake", settings: nil)
user2 = User.new(name: "John", settings: "foo")

# Assuming that user has "settings" with DB `default: true`,
# this query would raise if using SQLite3 as database
repo.exec([user1, user2].insert)

# To avoid that, split the insertion queries
repo.exec(user1.insert)
repo.exec(user2.insert)

[View source]

Instance Method Detail

def build(index_params = false) : Tuple(String, Enumerable(DB::Any)) #

Build this bulk 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 instances : Enumerable(T) #

Model instances associated with this query.


[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 = users.insert.returning(:id, :name)
q.build # => {"INSERT INTO users ... RETURNING id, name"}

q = users.delete.returning("foo")
q.build # => {"DELETE FROM 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.

posts.insert.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.

posts.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 = users.insert.returning(:id, :name)
q.build # => {"INSERT INTO users ... RETURNING id, name"}

q = users.delete.returning("foo")
q.build # => {"DELETE FROM users ... RETURNING foo"}

[View source]
def type : Type #

Query type.


[View source]