module

Lustra::SQL

Inherits Lustra::SQL::Transaction < Lustra::SQL::Logger

Lustra::SQL

Lustra is made like an onion:

+------------------------------------+
|          THE ORM STACK             +
+------------------------------------+
|  Model | DB Views |  Migrations    | < High Level Tools
+---------------+--------------------+
|  Columns | Validation | Converters | < Mapping system
+---------------+--------------------+
|  Lustra::SQL  | Lustra::Expression | < Low Level SQL Builder
+------------------------------------+
|  Crystal DB   |    Crystal PG      | < Low Level connection
+------------------------------------+

On the bottom stack, Lustra offer SQL query building. Theses features are then used by top level parts of the engine.

The SQL module provide a simple API to generate delete, insert, select and update methods.

Each requests can be duplicated then modified and executed.

Note: Each request object is mutable. Therefore, to update and store a request, you must use manually the dup method.

Class methods

lock(table : String | Symbol, mode = "ACCESS EXCLUSIVE", connection = "default", &)

Lock completetly a table.

Lustra::SQL.lock("my_table") do
end

Optional parameter mode allow you to decide over the lock level Modes are:

  • ACCESS EXCLUSIVE (default)
  • ACCESS SHARE
  • ROW SHARE
  • ROW EXCLUSIVE
  • SHARE UPDATE EXCLUSIVE
  • SHARE
  • SHARE ROW EXCLUSIVE
  • EXCLUSIVE

See Official PG documentation for more informations

Source
truncate(tablename : Lustra::Model.class | String | Symbol, restart_sequence : Bool = false, cascade : Bool = false, truncate_inherited : Bool = true, connection_name : String = "default") forall T

Truncate a table or a model

User.query.count           # => 200
Lustra::SQL.truncate(User) # equivalent to Lustra::SQL.truncate(User.table, connection_name: User.connection)
User.query.count           # => 0

SEE https://www.postgresql.org/docs/current/sql-truncate.html for more information.

  • restart_sequence set to true will append RESTART IDENTITY to the query
  • cascade set to true will append CASCADE to the query
  • truncate_inherited set to false will append ONLY to the query
  • connection_name will be: Model.connection or default unless optionally defined.
Source

Instance methods

add_connection(name : String, url : String)
Source
delete(table : Symbolic)

Start a DELETE table query

Source
escape(x : String | Symbol)

Escape the expression, double quoting it.

It allows use of reserved keywords as table or column name NOTE: Escape is used for escaping postgresql keyword. For example if you have a column named order (which is a reserved word), you want to escape it by double-quoting it.

For escaping STRING value, please use Lustra::SQL.sanitize

Source
execute(connection_name : String, sql)

Execute a SQL statement on a specific connection.

Usage: Lustra::SQL.execute("seconddatabase", "SELECT 1 FROM users")

Source
execute(sql)

Execute a SQL statement.

Usage: Lustra::SQL.execute("SELECT 1 FROM users")

Source
init(name : String, url : String)
Source
init(url : String)
Source
init(connections : Hash(Symbolic, String))
Source
insert(table, args : NamedTuple)
Source
insert(table, *args)

Alias of insert_into, for hurry developers

Source
insert

Create a new INSERT query

Source
insert_into(table : Symbolic)

Prepare a new INSERT INTO table query :ditto:

Source
insert_into(table : Symbolic, *args)

Start an INSERT INTO table query

Lustra::SQL.insert_into("table", {id: 1, name: "hello"}, {id: 2, name: "World"})
Source
raw(x, *params)

This provide a fast way to create SQL fragment while escaping items, both with ? and :key system:

query = Model.query.select(Lustra::SQL.raw("CASE WHEN x=:x THEN 1 ELSE 0 END AS check", x: "blabla"))
query = Model.query.select(Lustra::SQL.raw("CASE WHEN x=? THEN 1 ELSE 0 END AS check", "blabla"))
Source
raw(__template, **params)
Source
raw_enum(x, params : Enumerable(T)) forall T

See self.raw Can pass an array to this version

Source
rollback

Raise a rollback, in case of transaction

Source
sanitize(x)

Sanitize string and convert some literals (e.g. Time)

Source
select(*args)

Start a SELECT FROM table query

Source
unsafe(x)
Source
update(table)

Start a UPDATE table query

Source
with_savepoint(connection_name = "default", &)

Create a transaction, but this one is stackable using savepoints.

Example:

Lustra::SQL.with_savepoint do
  # do something
  Lustra::SQL.with_savepoint do
    rollback # < Rollback only the last `with_savepoint` block
  end
end
Source

Nested types