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.

Prerequisities

Money_SQL is supported on Elixir 1.6 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_migration
* 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 either integer or float, 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 CASTing the string value will restore some of that functionality. For example:

CAST(JSON_EXTRACT(amount_map, '$.amount') AS DECIMAL(20, 8)) AS amount;

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.

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.aggregate_functions
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 l 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

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.

Installation

Money 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

maxgronlund/money_sql