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 {: .warning}

Please ensure that if you are using Ecto embedded schemas that include a money type that it is configured with the type Money.Ecto.Map.Type, NOT Money.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:

1. 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

2. 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

3. 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

4. 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

5. 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}}]

6. 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:

1. 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 either integer or float, neither of which would preserve precision of a decimal value.

2. 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.

3. Serializing the amount as a string means that SQL query arithmetic and equality operators will not work as expected. You may find that CASTing 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:

1. Apply a default currency to a field input representing a money amount.
2. Add formatting options to the returned t:Money that will be applied when calling Money.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 with Money.to_string/2

• :default is the t:Money that is used if the :value field is nil 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:

1. Generate the migration by executing mix money.gen.postgres.plus_operator

2. Migrate the database by executing mix ecto.migrate

3. Formulate an Ecto query to use the + operator

  iex> 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:

1. Generate the migration by executing mix money.gen.postgres.sum_function

2. Migrate the database by executing mix ecto.migrate

3. 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:

1. Generate the migration by executing mix money.gen.postgres.min_max_functions

2. Migrate the database by executing mix ecto.migrate

3. Formulate an Ecto query to use the aggregate function min() or max()

  # 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.