+

tl;dr - Overly complex use of postgres DOMAIN and composite TYPEs (with CASTing to jsonb) used to support the better-than-naive definition of a price as a currency + natural number. For actual production usage do yourself and your team a favor and use a currency type column + integer for amount (ex. cents in USD). For those who like hacking I also got TypeORM to read/work with the composite TYPE with even more dirty dirty hacks.

While working on a project that happens to do some price munging recently I had some fun nailing down the data model when dealing with the “price” of certain goods. There are a few important notoriously sticky areas of writing code and one of them is definitely dealing with money. It’s not as bad as dealing with time, but early architectural decisions and a misunderstanding of the scope and inherent difficulty of translating the thing we casually refer to as “money” into a consistent data model.

The basics of dealing with money in software

I’ll spare you the long diatribe on how you should deal with money – I’m not the expert. I do know a few basic rules at this point though:

• Use integers to store prices
• Store the currency for a given price as close as possible to the integer that represents the amount
• Always assume the lowest common denominator for every currency
• (optional) Constrain the list of currencies you allow
• (optional) Check that you don’t have negative prices if you don’t intend to
• (Postgres-specific) Don’t use the postgres money type

I’ve seen large organizations (who manage A LOT of money) do things like use BigDecimal for dealing with monetary amounts, so don’t worry if you’ve never run into these principles before – it happens to the best of us at least once).

Keep in mind these basic pointers don’t even touch important areas like how you deal with exchange rates between currency, but for many applications it’s enough to just know this.

The progression

I realized (shortly before making this post, which is why I made it) that I went through a progression while dealing with this issue – hopefully seeing the progression laid out will be interesting/helpful to others.

Starting point: monthlyPriceCents

The current project I’m working on is a Typescript-based Javascript app, and I’m using the excellent TypeORM (which is just about the best ORM I’ve ever used in any language, but that’s a post for another day). I started out with something like this:

import { Column } from "typeorm";

@Entity({name: "plans"})
export default class Plan {
  // ... other code ...

  @Column({name: "monthly_price_cents"})
  monthlyPriceCents: number = 0;

  // ... other code ...
}

Now this isn’t bad, but as I sat and thought about it more the more I wondered how last week/yesterday me could have been so naive – why am I just assuming American cents to be the currency? And for that matter, why the assumption of the payment period being monthly?

JS: Making assumptions explicit

If we add the currency we get something like this:

import { Column } from "typeorm";

// ISO 4217 currency code, lowercased
// see: https://www.xe.com/iso4217.php
export inteface currency {
  USD = "usd",
}

export inteface PaymentInterval {
  Day = "day",
  Week = "week",
  Month = "month",
  Quarter = "quarter",
  Year = "year",
}

@Entity({name: "plans"})
export default class Plan {
  // ... other code ...

  @Column({name: "currency"})
  currency: Currency = Currency.USD;

  @Column({name: "payment_interval"})
  paymentInterval: PaymentInterval = PaymentInterval.Monthly;

  @Column({name: "price"})
  price: number = 0;

  // ... other code ...
}

OK so this is a little better – much more flexible and no loss of correctness. This isn’t bad, and is normally where most people would stop. Since I am an expert yak-shaver, I kept going, with two primary motivations:

• Actually, price means nothing without currency – this is like measurement without units, why not make it impossible for them to be apart?
• How can I get the DB to ensure this is never wrong?
• price should never be negative, how can I ensure it won’t be?

THIS IS WHERE YOU SHOULD STOP IF YOU’RE WRITING REASONABLE CODE WITH A TEAM.

Just do the simple thing, add checks on the currency and price columns (make sure price is nonzero if that makes sense for you, make sure currency is a valid ISO 4217 value, etc).

The code that follows is a descent into madness complexity, continue at your own risk

JS: Modeling price

So here’s how we get a better model for price on the JS side:

export class Price {
  amount: number;
  currency: Currency;

  constructor(currency: string, amount: number) {
    if (typeof amount !== "number" || amount < 0) {
      throw new TypeError("invalid price, amount must be greater than zero");
    }
    if (!Object.values(Currency).includes(currency)) {
      throw new TypeError(`invalid currency (unknown value [${currency}])`);
    }

    this.currency = currency as Currency;
    this.amount = amount;
  }
}

Hopefully you don’t feel too strongly about throwing in constructors – I land on the side of the fence that believes if you try to create something invalid, the program should error hard and fast. In Rust or Haskell we’d be able to use a much betteer errors-as-values approach instead of unsightly exceptions but for now I’m fine with exceptions.

The benefits of this complex Price object are small but should compound over time – I’ll never have to deal with an invalid amount or a price missing a valid currency ever again. Here’s how it slots into the Plan object mentioned earlier:

@Entity({name: "plans"})
export default class Plan {
  // ... other code ...

  @Column({name: "payment_interval"})
  paymentInterval: PaymentInterval = PaymentInterval.Monthly;

  @Column({name: "price", type: "jsonb"})
  price: Price = new Price(Currency.USD, 0);

  // ... other code ...
}

OK, so somewhat better, but there’s something curious here – how can we store this complex type in the DB? jsonb is what I’ve picked because I know it’ll fit, but we actually have more choices, because we’re using Postgres, and it’s got an amazing feature set.

DB: Modeling price using DOMAIN

So here’s where things really get interesting and more important. While we can’t really trust Typescript to protect us at runtime, we can ensure that we never have bad data get into the database by making sure we have good validation in place. The jsonb postgres type doesn’t have validation built in, but one tool we can use for this is a DOMAIN. Here’s what this would look like

CREATE TYPE currency AS ENUM ('usd');

-- We're naming it 'price_domain' instead of 'price' to avoid naming conflicts with the actual solution which we'll get to later
CREATE DOMAIN price_domain
AS jsonb
DEFAULT '{"currency":"usd","amount":0}'::jsonb
CONSTRAINT is_valid_price
CHECK (
      jsonb_path_exists(VALUE, '$.currency')
  AND jsonb_path_exists(VALUE, '$.amount')
  AND jsonb_typeof(VALUE->'amount') = 'number'
  AND CAST(VALUE->>'amount' AS integer) > 0
  AND jsonb_typeof(VALUE->'currency') = 'string'
  AND ((VALUE->>'currency')::currency) = ANY(ENUM_RANGE(null::currency)::currency[])
);

COMMENT ON DOMAIN price IS 'a price consisting of a currency and amount (lowest denomination)';

And here’s how it performs:

postgres=# SELECT CAST('{"currency": "usd", "amount": 0}'::jsonb AS price_domain);
           price_domain
----------------------------------
 {"amount": 0, "currency": "usd"}
(1 row)

postgres=# SELECT CAST('{"currency": "usd", "amount": -1}'::jsonb AS price_domain);
ERROR:  value for domain price_domain violates check constraint "is_valid_price"

postgres=# SELECT CAST('{"currency": "nope", "amount": 0}'::jsonb AS price_domain);
ERROR:  invalid input value for enum currency: "nope"

postgres=# SELECT CAST('{"missing": "usd", "fields": -1}'::jsonb AS price_domain);
ERROR:  value for domain price_domain violates check constraint "is_valid_price"

postgres=# SELECT CAST('{}'::jsonb AS price_domain);
ERROR:  value for domain price_domain violates check constraint "is_valid_price"

postgres=# SELECT CAST('{"currency": "usd", "amount": 0, "extra_data": "oh no"}'::jsonb AS price_domain);
                      price_domain
---------------------------------------------------------
 {"amount": 0, "currency": "usd", "extra_data": "oh no"}
(1 row)

So this is not bad – we definitely get some protection, but one thing you might notice is that we actually can’t prevent (at least I can’t figure out how) the object from having more keys and values than the two we want. Any attempts I made to use jsonb_object_keys() or other functions to ensure that there were only 2 keys didn’t work due to the restrictions on what you can put in a DOMAIN’s CHECK constraints – setof values can’t be used, and of course you can’t use SELECTs (ex. so SELECT COUNT(*) FROM (jsonb_object_keys(VALUE)) wouldn’t work, for example).

Outside of these limitations everything is actually pretty good – we ensure the currency is valid, we ensure the number is greater than zero, etc. The problem of having an unbounded size is pretty bad though, what if someone figured out a way to inject extra data into prices on the API? Since we know the rough shape of this value it seems like a silly hole to leave… Let’s take a look at Postgres’s composite TYPEs instead

DB: Modeling price using TYPE

If we want to determine the shape of some data, we’ll need to use a “composite” TYPE declaration in Postgres. Since TYPEs don’t have CHECK constraints, we’ll have to actually combine the DOMAIN concept to get what we want:

CREATE TYPE currency AS ENUM ('usd');

CREATE DOMAIN natural_number
AS integer
CONSTRAINT is_gte_zero
CHECK ( VALUE >= 0 );

CREATE TYPE price AS (currency currency, amount natural_number);

COMMENT ON TYPE price IS 'price that consists of a currency (smallest denomination) and amount';

And here’s how it performs:

postgres=# SELECT ('usd'::currency, 0)::price;
   row
---------
 (usd,0)
(1 row)


postgres=# SELECT ('nope'::currency, 0)::price;
ERROR:  invalid input value for enum currency: "nope"
LINE 1: SELECT ('nope'::currency, 0)::price;

postgres=# SELECT ('usd'::currency, -1)::price;
ERROR:  value for domain natural_number violates check constraint "is_gte_zero"

This is great on the DB side, but there’s a bit of a problem – TypeORM doesn’t seem to suppport composite types (and a lot of ORMs probably wouldn’t either), so it’s going to be a bit hard for us to use the price composite type we created.

DB: Modeling price using DOMAIN, TYPE, and jsonb CASTsupport

Turns out we can get past by defining some CASTs for our new price type so that we can use it as a jsonb when neeeded (which TypeORM does handle easily):

-- Create function to convert jsonb objects to price composite types
CREATE OR REPLACE FUNCTION price_from_jsonb(obj jsonb) RETURNS price
STRICT IMMUTABLE
AS $$
BEGIN
  IF NOT jsonb_path_exists(obj, '$.currency') THEN RAISE EXCEPTION 'currency not present'; END IF;
  IF NOT jsonb_path_exists(obj, '$.amount') THEN RAISE EXCEPTION 'amount not present'; END IF;
  IF obj->>'amount' < 0 THEN RAISE EXCEPTION 'invalid amount (negative)'; END IF;

  RETURN (CAST(obj->>'currency' AS currency), CAST(obj->>'amount' as integer)::natural_number);
END;
$$ LANGUAGE plpgsql;

-- Create jsonb->price cast
CREATE CAST (jsonb as price)
  WITH FUNCTION price_from_jsonb(jsonb);

-- Create function to convert price composite types into jsonb
CREATE OR REPLACE FUNCTION jsonb_from_price(p price) RETURNS jsonb
STRICT IMMUTABLE
AS $$
BEGIN
  RETURN jsonb_object('currency', p.currency, 'amount', p.amount);
END;
$$ LANGUAGE plpgsql;

-- Create price->jsonb cast
CREATE CAST (price as jsonb)
  WITH FUNCTION jsonb_from_price(price);

And here’s how it performs:

-- Casting jsonb to price

postgres=# SELECT CAST('{"currency": "usd", "amount": 0, "extra_data": "oh no"}'::jsonb AS price);
  price
---------
 (usd,0)
(1 row)


postgres=# SELECT CAST('{"currency": "nope", "amount": 0}'::jsonb AS price);
ERROR:  invalid input value for enum currency: "nope"
CONTEXT:  PL/pgSQL function price_from_jsonb(jsonb) line 7 at RETURN

postgres=# SELECT CAST('{"currency": "usd", "amount": -1}'::jsonb AS price);
ERROR:  invalid amount (negative)
CONTEXT:  PL/pgSQL function price_from_jsonb(jsonb) line 5 at RAISE

postgres=# SELECT CAST('{}'::jsonb AS price);
ERROR:  currency not present
CONTEXT:  PL/pgSQL function price_from_jsonb(jsonb) line 3 at RAISE

-- Casting prices to jsonb

postgres=# SELECT CAST(('usd'::currency, 0)::price as jsonb);
               row
----------------------------------
 {"amount": 0, "currency": "usd"}
(1 row)


postgres=# SELECT CAST(('usd'::currency, -1)::price as jsonb);
ERROR:  value for domain natural_number violates check constraint "is_gte_zero"

postgres=# SELECT CAST(('nope'::currency, 0)::price as jsonb);
ERROR:  invalid input value for enum currency: "nope"
LINE 1: SELECT CAST(('nope'::currency, 0)::price as jsonb);

And if you have this in a migration (and a framework that supports reverting migrations), you might be curious what undoing this all looks like:

DROP CAST (jsonb as price);
DROP CAST (price as jsonb);
DROP FUNCTION price_from_jsonb(jsonb);
DROP FUNCTION jsonb_from_price(price);
DROP TYPE price;
DROP DOMAIN natural_number;

And there we have it! With the code above we’ve got everything we wanted – bounded size and validation on our composite type. Did we just create a huge maintenance burden/hurdle to entry for future DB administrators? That’s a topic for another post.

But here’s the rub – we can’t actually use this because TypeORM doesn’t support composite types – we’re stuck with the DOMAIN-based solution.

You should probably stop here and use the DOMAIN solution if you don’t want to get fired (probably you should just break apart the types again and use row-level CHECK constraints if you want more maintainable code…). If you’re willing to walk on the wild side and hack your way to the end, read on.

JS: Hacking TypeORM for composite type support

Here’s the error you’d see if you tried to query with the TYPE based solution from earlier and a @Column({type: 'jsonb'}):

QueryFailedError: malformed record literal: "{"currency":"usd","amount":543}"

What’s happening is that typeorm is sending a record literal which is epxected to be a jsonb and postgres doesn’t know what to do with the obviously stringified JSON it gets that’s supposed to go in a record spot. At present TypeORM doesn’t support specifying some sort of “raw” type that lets you just spit out a string to be spliced in the generated SQL query (though you could easily do it with a custom Query I want basic repository.save() to work). Turns out with some hacking we can actually get this to work, by using text, and adding some more casts.

First let’s hack the @Column definition in the Plan entity to be of type text.

  @Column({
    name: "price",
    type: "text",
    transformer: {
      from: Price.fromPGCompositeType,
      to: Price.toPGCompositeType,
    }
  })
  public price: Price = new Price(Currency.USD, 0);

Then we’ll implement these to/from PG composite type transformer functions:

export class Price {
  currency: Currency;
  amount: number;

  // Parse a stringified record literal
  public static fromPGCompositeType(v: string): Price {
    if (!(typeof v === "string")) { throw new TypeError(`composite type didn't return as string`); }

    const [currency, amountStr] = v.replace("(", "").replace(")","").split(",");
    if (!Object.values(Currency).includes(currency as Currency)) {
      throw new TypeError(`invalid currency in record literal: [${currency}]`);
    }

    const amount = parseInt(amountStr);
    if (typeof amount !== "number" || isNaN(amount) || amount < 0) {
      throw new TypeError(`invalid amount in record literal: [${amount}]`);
    }

    return new Price(currency as Currency, amount);
  }

  // Note: `type` on the @Column must be set to 'text'
  // convert a price into a stringified record literal
  public static toPGCompositeType(v: Price): string {
    if (!v) { throw new TypeError(`invalid Price object`); }
    return `(${v.currency},${v.amount})`;
  }

  constructor(currency: Currency, amount: number) {
    if (typeof amount !== "number" || amount < 0) {
      throw new TypeError("invalid price, amount must be greater than zero");
    }
    if (!Object.values(Currency).includes(currency)) {
      throw new TypeError(`invalid currency (unknown value [${currency}])`);
    }

    this.currency = currency as Currency;
    this.amount = amount;
  }
}

With this, we can successfully create and retrieve Plan objects that have the composite type price inside them! Just in case anyone wants to do this madness I added a comment to the GitHub issue. Can’t wait to see what the maintainers say.

I think this could actually get better if it’s possible to use another intermediate type/representation (binary?). Of course, proper library support via some sort of raw or castAs option would be good too but that is a pretty complex decision.

Wrapup

Hopefully you enjoyed exploring this subject as much as I enjoyed writing this code that I’m sure next week me will regret having to maintain.