Create elixir functions with SQL as a body.
If available in Hex, the package can be installed
by adding defql
to your list of dependencies in mix.exs
:
defp deps do
[
{:defql, "~> 0.1.1"},
{:postgrex, ">= 0.13.0"}, # optional
]
end
It requires adapter
key, and adapter specific options.
Use with ecto:
config :defql, connection: [
adapter: Defql.Adapter.Ecto.Postgres,
repo: YourApp.Repo
]
Use standalone connection:
config :defql, connection: [
adapter: Defql.Adapter.Postgres,
hostname: "localhost",
username: "username",
password: "password",
database: "my_db",
pool: DBConnection.Poolboy,
pool_size: 1
]
We can define module to have access to our database:
defmodule UserQuery do
use Defql
defselect get(conds), table: :users, columns: [:name, :email]
definsert add(params), table: :users
defupdate update(params, conds), table: :users
defdelete delete(conds), table: :users
defquery get_by_name(name, limit) do
"SELECT * FROM users WHERE name = $name LIMIT $limit"
end
end
Right now we have easy access to users
in database:
UserQuery.get(id: "3") # => {:ok, [%{...}]}
UserQuery.add(name: "Smbdy") # => {:ok, [%{...}]}
UserQuery.update([name: "Other"],[id: "2"]) # => {:ok, [%{...}]}
UserQuery.delete(id: "2") # => {:ok, [%{...}]}
UserQuery.get_by_name("Ela", 4) # => {:ok, [%{...}, %{...}]}
We can also define common table for the whole module.
defmodule UserQuery do
use Defql, table: :users
defselect get(conds), columns: [:name, :email]
definsert add(params)
defupdate update(params, conds)
defdelete delete(conds)
end
%{...}
It's a hash with user properties straight from database.
Supported condition statements:
user_id: [1,2,3,4]
user_id: {:in, [1,2,3,4,5]}
name: {:like, "%john%"}
name: {:ilike, "%john"}
- MySQL support
- Cleanup ECTO adapter
- Support database errors
- Transactions