View Source Introduction to Money SQL
Money_SQL implements a set of functions to store and retrieve data structured as a %Money{}
type that is composed of an ISO 4217 currency code and a currency amount. See ex_money for details of using Money
. Note that ex_money_sql
depends on ex_money
.
Embedded Schema Configuration from ex_money_sql 1.9.2
Please ensure that if you are using Ecto embedded schemas that include a
money
type that it is configured with the typeMoney.Ecto.Map.Type
, NOTMoney.Ecto.Composite.Type
.In previous releases the misconfiguration of the type worked by accident. From
ex_money_sql
version 1.9.2 and subsequent releases an exception like** (Protocol.UndefinedError) protocol Jason.Encoder not implemented for {"USD", Decimal.new("50.00")} of type Tuple
will be raised. This is most likely an indication of type misconfiguration in an embedded schema.
Installation
ex_money_sql
can be installed by adding ex_money_sql
to your list of dependencies in mix.exs
and then executing mix deps.get
def deps do
[
{:ex_money_sql, "~> 1.0"},
...
]
end
Note that ex_money_sql
is supported on Elixir 1.11 and later only.
Serializing to a Postgres database with Ecto
Money_SQL
provides custom Ecto data types and a custom Postgres data type to provide serialization of Money.t
types without losing precision whilst also maintaining the integrity of the {currency_code, amount}
relationship. To serialise and retrieve money types from a database the following steps should be followed:
- First generate the migration to create the custom type:
mix money.gen.postgres.money_with_currency
* creating priv/repo/migrations
* creating priv/repo/migrations/20161007234652_add_money_with_currency_type_to_postgres.exs
- Then migrate the database:
mix ecto.migrate
07:09:28.637 [info] == Running MoneyTest.Repo.Migrations.AddMoneyWithCurrencyTypeToPostgres.up/0 forward
07:09:28.640 [info] execute "CREATE TYPE public.money_with_currency AS (currency_code char(3), amount numeric)"
07:09:28.647 [info] == Migrated in 0.0s
- Create your database migration with the new type (don't forget to
mix ecto.migrate
as well):
defmodule MoneyTest.Repo.Migrations.CreateLedger do
use Ecto.Migration
def change do
create table(:ledgers) do
add :amount, :money_with_currency
timestamps()
end
end
end
- Create your schema using the
Money.Ecto.Composite.Type
ecto type:
defmodule Ledger do
use Ecto.Schema
schema "ledgers" do
field :amount, Money.Ecto.Composite.Type
timestamps()
end
end
- Insert into the database:
iex> Repo.insert %Ledger{amount: Money.new(:USD, "100.00")}
[debug] QUERY OK db=4.5ms
INSERT INTO "ledgers" ("amount","inserted_at","updated_at") VALUES ($1,$2,$3)
[{"USD", #Decimal<100.00>}, {{2016, 10, 7}, {23, 12, 13, 0}}, {{2016, 10, 7}, {23, 12, 13, 0}}]
- Retrieve from the database:
iex> Repo.all Ledger
[debug] QUERY OK source="ledgers" db=5.3ms decode=0.1ms queue=0.1ms
SELECT l0."amount", l0."inserted_at", l0."updated_at" FROM "ledgers" AS l0 []
[%Ledger{__meta__: #Ecto.Schema.Metadata<:loaded, "ledgers">, amount: #<:USD, 100.00>,
inserted_at: ~N[2017-02-21 00:15:40.979576],
updated_at: ~N[2017-02-21 00:15:40.991391]}]
Serializing to a MySQL (or other non-Postgres) database with Ecto
Since MySQL does not support composite types, the :map
type is used which in MySQL is implemented as a JSON
column. The currency code and amount are serialised into this column.
defmodule MoneyTest.Repo.Migrations.CreateLedger do
use Ecto.Migration
def change do
create table(:ledgers) do
add :amount, :map
timestamps()
end
end
end
Create your schema using the Money.Ecto.Map.Type
ecto type:
defmodule Ledger do
use Ecto.Schema
schema "ledgers" do
field :amount, Money.Ecto.Map.Type
timestamps()
end
end
Insert into the database:
iex> Repo.insert %Ledger{amount_map: Money.new(:USD, 100)}
[debug] QUERY OK db=25.8ms
INSERT INTO "ledgers" ("amount_map","inserted_at","updated_at") VALUES ($1,$2,$3)
RETURNING "id" [%{amount: "100", currency: "USD"},
{{2017, 2, 21}, {0, 15, 40, 979576}}, {{2017, 2, 21}, {0, 15, 40, 991391}}]
{:ok,
%MoneyTest.Thing{__meta__: #Ecto.Schema.Metadata<:loaded, "ledgers">,
amount: nil, amount_map: #Money<:USD, 100>, id: 3,
inserted_at: ~N[2017-02-21 00:15:40.979576],
updated_at: ~N[2017-02-21 00:15:40.991391]}}
Retrieve from the database:
iex> Repo.all Ledger
[debug] QUERY OK source="ledgers" db=16.1ms decode=0.1ms
SELECT t0."id", t0."amount_map", t0."inserted_at", t0."updated_at" FROM "ledgers" AS t0 []
[%Ledger{__meta__: #Ecto.Schema.Metadata<:loaded, "ledgers">,
amount_map: #Money<:USD, 100>, id: 3,
inserted_at: ~N[2017-02-21 00:15:40.979576],
updated_at: ~N[2017-02-21 00:15:40.991391]}]
Notes:
In order to preserve precision of the decimal amount, the amount part of the
%Money{}
struct is serialised as a string. This is done because JSON serializes numeric values as eitherinteger
orfloat
, neither of which would preserve precision of a decimal value.The precision of the serialized string value of amount is affected by the setting of
Decimal.get_context
. The default is 28 digits which should cater for your requirements.Serializing the amount as a string means that SQL query arithmetic and equality operators will not work as expected. You may find that
CAST
ing the string value will restore some of that functionality. For example:
CAST(JSON_EXTRACT(amount_map, '$.amount') AS DECIMAL(20, 8)) AS amount;
Casting Money with Changesets
Then the schema type is Money.Ecto.Composite.Type
then any option that is applicable to Money.parse/2
or Money.new/3
can be added to the field definition. These options will then be applied when Money.Ecto.Composite.Type.cast/2
or Money.Ecto.Composite.Type.load/3
is called. These functions are called with loading data from the database or when calling Ecto.Changeset.cast/3
is called. Typically this is useful to:
- Apply a default currency to a field input representing a money amount.
- Add formatting options to the returned
t:Money
that will be applied when callingMoney.to_string/2
Consider the following example where a money amount will be considered in a default currency if no currency is applied:
Schema Example
The example below has three columns defined as Money.Ecto.Composite.Type
.
:payroll
will be cast as with the default currency:JPY
if no currency field is provided. Note that if no:default_currency
option is defined, the default currency will be derived from the current locale or configured:locale
option.:tax
is defined with the option:fractional_digits
. This option will be applied when formatting:tax
withMoney.to_string/2
:default
is thet:Money
that is used if the:value
field isnil
both when casting and when loading from the database.
defmodule Organization do
use Ecto.Schema
import Ecto.Changeset
@primary_key false
schema "organizations" do
field :payroll, Money.Ecto.Composite.Type, default_currency: :JPY
field :tax, Money.Ecto.Composite.Type, fractional_digits: 4
field :value, Money.Ecto.Composite.Type, default: Money.new(:USD, 0)
field :name, :string
field :employee_count, :integer
timestamps()
end
def changeset(organization, params \\ %{}) do
organization
|> cast(params, [:payroll])
end
end
Embedded schema example
Embedded schemas are represented in Postgres as a jsobn
data type which, in Elixir, is represented as a map. Therefore to include money fields in an embedded scheam, the Money.Ecto.Map.Type
is used. Here is an example schema, extending the previous example:
defmodule Organization do
use Ecto.Schema
import Ecto.Changeset
@primary_key false
schema "organizations" do
field :payroll, Money.Ecto.Composite.Type, default_currency: :JPY
field :tax, Money.Ecto.Composite.Type, fractional_digits: 4
field :value, Money.Ecto.Composite.Type, default: Money.new(:USD, 0)
field :name, :string
field :employee_count, :integer
embeds_many :customers, Customer do
field :name, :string
field :revenue, Money.Ecto.Map.Type, default: Money.new(:USD, 0)
end
timestamps()
end
Changeset execution
In the following example, a default of :JPY
currency (using our previous schema example) will be applied when casting the changeset.
iex> changeset = Organization.changeset(%Organization{}, %{payroll: "0"})
iex> changeset.changes.payroll == Money.new(:JPY, 0)
true
Postgres Database functions
Since the datatype used to store Money
in Postgres is a composite type (called :money_with_currency
), the standard aggregation functions like sum
and average
are not supported and the order_by
clause doesn't perform as expected. Money
provides mechanisms to provide these functions.
Plus operator +
Money
defines a migration generator which, when migrated to the database with mix ecto.migrate
, supports the +
operator for :money_with_currency
columns. The steps are:
Generate the migration by executing
mix money.gen.postgres.plus_operator
Migrate the database by executing
mix ecto.migrate
Formulate an Ecto query to use the
+
operatoriex> q = Ecto.Query.select Item, [l], type(fragment("price + price"), l.price) #Ecto.Query<from l0 in Item, select: type(fragment("price + price"), l0.price)> iex> Repo.one q [debug] QUERY OK source="items" db=5.6ms queue=0.5ms SELECT price + price::money_with_currency FROM "items" AS l0 [] #Money<:USD, 200>]
Aggregate functions: sum()
Money
provides a migration generator which, when migrated to the database with mix ecto.migrate
, supports performing sum()
aggregation on Money
types. The steps are:
Generate the migration by executing
mix money.gen.postgres.sum_function
Migrate the database by executing
mix ecto.migrate
Formulate an Ecto query to use the aggregate function
sum()
# Formulate the query. Note the required use of the type()
# expression which is needed to inform Ecto of the return
# type of the function
iex> q = Ecto.Query.select Item, [l], type(sum(l.price), l.price)
#Ecto.Query<from l0 in Item, select: type(sum(l.price), l.price)>
iex> Repo.all q
[debug] QUERY OK source="items" db=6.1ms
SELECT sum(l0."price")::money_with_currency FROM "items" AS l0 []
[#Money<:USD, 600>]
The function Repo.aggregate/3
can also be used. However at least ecto version 3.2.4 is required for this to work correctly for custom ecto types such as :money_with_currency
.
iex> Repo.aggregate(Item, :sum, :price)
#Money<:USD, 600>
Note that to preserve the integrity of Money
it is not permissable to aggregate money that has different currencies. If you attempt to aggregate money with different currencies the query will abort and an exception will be raised:
iex> Repo.all q
[debug] QUERY ERROR source="items" db=4.5ms
SELECT sum(l0."price")::money_with_currency FROM "items" AS l0 []
** (Postgrex.Error) ERROR 22033 (): Incompatible currency codes. Expected all currency codes to be USD
Aggregate functions: min() and max()
Money
provides a migration generator which, when migrated to the database with mix ecto.migrate
, supports performing min()
and max()
aggregation on Money
types. The steps are:
Generate the migration by executing
mix money.gen.postgres.min_max_functions
Migrate the database by executing
mix ecto.migrate
Formulate an Ecto query to use the aggregate function
min()
ormax()
# Formulate the query. Note the required use of the type()
# expression which is needed to inform Ecto of the return
# type of the function
iex> q = Ecto.Query.select Item, [l], type(min(l.price), l.price)
#Ecto.Query<from l0 in Item, select: type(min(l.price), l.price)>
iex> Repo.all q
[debug] QUERY OK source="items" db=6.1ms
SELECT min(l0."price")::money_with_currency FROM "items" AS l0 []
[#Money<:USD, 600>]
The function Repo.aggregate/3
can also be used. However at least ecto version 3.2.4 is required for this to work correctly for custom ecto types such as :money_with_currency
.
iex> Repo.aggregate(Item, :min, :price)
#Money<:USD, 600>
Note that to preserve the integrity of Money
it is not permissable to aggregate money that has different currencies. If you attempt to aggregate money with different currencies the query will abort and an exception will be raised:
iex> Repo.all q
[debug] QUERY ERROR source="items" db=4.5ms
SELECT min(l0."price")::money_with_currency FROM "items" AS l0 []
** (Postgrex.Error) ERROR 22033 (): Incompatible currency codes. Expected all currency codes to be USD
Order_by with Money
Since :money_with_currency
is a composite type, the default order_by
results may surprise since the ordering is based upon the type structure, not the money amount. Postgres defines a means to access the components of a composite type and therefore sorting can be done in a more predictable fashion. For example:
# In this example we are decomposing the the composite column called
# `price` and using the sub-field `amount` to perform the ordering.
iex> q = from l in Item, select: l.price, order_by: fragment("amount(price)")
#Ecto.Query<from l in Item, order_by: [asc: fragment("amount(price)")],
select: l.amount>
iex> Repo.all q
[debug] QUERY OK source="items" db=2.0ms
SELECT l0."price" FROM "items" AS l0 ORDER BY amount(price) []
[#Money<:USD, 100.00000000>, #Money<:USD, 200.00000000>,
#Money<:USD, 300.00000000>, #Money<:AUD, 300.00000000>]
Note that the results may still be unexpected. The example above shows the correct ascending ordering by amount(price)
however the ordering is not currency code aware and therefore mixed currencies will return a largely meaningless order.