EctoRawSQLHelpers

Working with raw SQL in Ecto may be disapointing, since the lib offers little support to it. EctoRawSQLHelpers aims to close that gap by providing helper functions when dealing with raw SQL, such as:

Named parameters support (aka "SELECT :one, :two" binding %{one: 1, two: 2} instead of "SELECT $1, $2" binding [1, 2])
Response parsing (getting query results as lists of maps, number of rows affected on statements, etc )
Stream support for dealing with big result sets

Usage

Runnings Queries (eager)

query(Repo, SQL, parameters, options) is the go-to function for queries, it will return a list of maps, where the keys will be the database columns.

alias EctoRawSQLHelpers.SQL

SQL.query(MyApplication.Repo, "SELECT * FROM table")
#> [%{"id" => 1, "column_name" => "some value"}, %{"id" => 2, "column_name" => "some value"}]

For single result queries, the function query_get_single_result/4 might be useful. This function will return either:

a map, when the query returned exactly one row
nil, when the query result set was empty
{:error, "string"}, when the result set had more than one row (use the LIMIT clause if you need)

alias EctoRawSQLHelpers.SQL

SQL.query_get_single_result(MyApplication.Repo, "SELECT * FROM table WHERE id = :id", %{id: 2}, column_names_as_atoms: true)
#> %{id: 2, column_name: "some value"}
#> nil
#> {:error, "query_get_single_result_as_map returned more than one row, 2 rows returned"}

Running Queries (Lazy evaluation / Stream)

When you wish to run queries that can return large result sets, it might be wise to use Streams when dealing with such data. This lib offers two ways of Streaming result sets from the database

SQLStream.query/4
SQLStream.cursor/4

SQLStream.query/4 will query the whole dataset from the database and then stream each row individually. Because the query is fetched from the databse all at once, it can still be quite memory intensive.

SQLStream.cursor/4 will leverage Ecto cursors to Stream data directly from the database. By doing so, only chunks of data will be queried at a time and the memory usage might be dramatically lower. Keep in mind though, that internally, this function will use a database transaction, which may lead to performance issues in your database server.

alias EctoRawSQLHelpers.SQLStream

SQLStream.query(MyApplication.Repo, "SELECT * FROM table")
|> Stream.map(&transformation/1)
|> Enum.reduce(&sum/2)

SQLStream.cursor(MyApplication.Repo, "SELECT * FROM table")
|> Stream.map(&transformation/1)
|> Enum.reduce(&sum/2)

Both methods will yield the same results. Considering the table has many rows (> 100k)

using SQLStream.query/4 will generally be faster, since there is no overhead of using database cursors.
using SQLStream.cursor/4 will generally be slower, but will use dramatically less memory

Options

The last argument of the functions in this lib is the options parameter. It is a Keyword List that have the following tweaks:

:column_names_as_atoms This option controls whether the column names returned will be strings or atoms. Defaults to false. Can be controlled using a ENV with the same name.
:adapter_sql_function This option controls the function used to run the query in the database, it defaults to &Ecto.Adapters.SQL.query/4. If you wish to do some pre-processing just before querying the database, you can use this option to do so. You may also use it to return mocked responses.

Affecting statements (INSERTs, UPDATEs, DELETEs, etc)

When running affecting statments such as a UPDATE or DELETE, it's sometimes useful to know the number of rows affected in the database.

This lib also offers functions for that matter:

SQL.affecting_statement/4
SQL.affecting_statement_and_return_rows/4 (postgres only)
SQL.affecting_statement_and_return_single_row/4 (postgres only)

alias EctoRawSQLHelpers.SQL

SQLStream.affecting_statement(
  MyApplication.Repo,
  "INSERT INTO table (id) VALUES (:first_value), (:second_value)",
  %{"first_value" => 1, "second_value" => 2}
)
#> 2

# the RETURNING clause is not available in MySQL,
# instead you would use the LAST_INSERT_ID() function
SQLStream.affecting_statement_and_return_rows(
  MyApplication.Repo,
  "INSERT INTO table (value) VALUES (:first_value), (:second_value) RETURNING id",
  %{first_value: "value", second_value: "value2"}
)
#> [%{"id" => 1}, %{"id" => 2}]

Parameter Binding

This lib providers named parameter binding. When running a query, you may specify parameters using the following syntax WHERE value = :parameter_name and then send the parameter as a map %{"parameter_name" => "value"}. The parameters can be both atoms or strings, so %{parameter_name: "value"} is also accepted. (if the same key is sent twice, the string version will be used)

Array binding

Postgres supports array binding for some queries, example:

SQL.query(MyApplication.Repo, "SELECT * FROM table WHERE id = ANY(:ids)", %{ids: [1, 2, 3]})

However, if you are using MySQL or prefer to use the IN sql clause, we got your back 🙂

SQL.query(MyApplication.Repo, "SELECT * FROM table WHERE id IN (:ids)", %{ids: {:in, [1, 2, 3]}})

Both will yield the same results as expected.

Config

You may also use the following configs in your config.exs file:

config :ecto_raw_sql_helper, [
  column_names_as_atoms: true
]

Postgres UUID

An UUID value returned by a postgres column will be a binary value that cannot be differenciated from a regular string. This lib also provides a custom UUID handler that can be activated by including the following option in your Repo config:

  config :myapp, MyApp.Repo,
       #...
       types: EctoRawSQLHelpers.Postgrex.CustomUUIDType

By using the CustomUUIDType, UUID fields will cast UUIDs to strings automatically:

SQL.query_get_single_result(MyApplication.Repo, "SELECT id FROM table_uuid WHERE id = :id", %{id: "7c5aa420-3245-4188-a9c3-2c16357afddd"})
#> %{id: "7c5aa420-3245-4188-a9c3-2c16357afddd"}

Contributors

Special thanks to these un-oficial contributors for code reviews and pair-programming:

Installation

Add ecto_raw_sql_helpers to your list of dependencies in mix.exs:

def deps do
  [
     {:ecto_raw_sql_helpers, git: "https://github.com/leveexpress/ecto_raw_sql_helpers.git", branch: "main"},
  ]
end

thiagopromano/ecto-raw-sql-helpers