Egon ORM

Appearance

Money ​

Amounts of money must not be JavaScript floats: 0.1 + 0.2 is not 0.3. Two safe choices are a decimal string (what t.decimal() gives) or a whole number of cents. This recipe defines a custom type that keeps cents in your code and a numeric column in the database.

Setup ​

The code of this recipe depends on these declarations: the imports, the schema classes, the repo, and the migration that creates the tables. Run the recipe in an empty database of its own: each recipe creates its own tables, and some have the same names (posts) with other columns.

ts
import { postgres } from "@egon-orm/postgres";
import { cast, createRepo, defineType, from, Schema, sum, t } from "egon-orm";
import { createMigrator, defineMigration } from "egon-orm/migration";

const repo = createRepo({ adapter: postgres({ connectionString: process.env.DATABASE_URL }) });

// The tables, created by a migration.
const tables = defineMigration({
  up(m) {
    m.createTable("products", (table) => {
      table.id({ type: "integer" });
      table.text("name").notNull();
      table.decimal("price", { precision: 12, scale: 2 }).notNull();
    });
  },
  down(m) {
    m.dropTable("products");
  },
});

/** Creates the tables, in the database that DATABASE_URL names. */
export async function createTables(): Promise<void> {
  const admin = postgres({ connectionString: process.env.DATABASE_URL });
  await createMigrator({
    adapter: admin,
    migrations: [{ version: 20261021000601, name: "money_tables", migration: tables }],
  }).up();
  await admin.close();
}

The type ​

ts
const AMOUNT = /^(-?)(\d{1,10})(?:\.(\d{1,2}))?$/;

/** "19.99" -> 1999, read digit by digit so that no floating-point error creeps in. */
function toCents(text: string): number | undefined {
  const match = AMOUNT.exec(text.trim());
  if (match === null) return undefined;
  const cents = Number(match[2]) * 100 + Number((match[3] ?? "").padEnd(2, "0"));
  return match[1] === "-" ? -cents : cents;
}

/** An amount of money as a whole number of cents, stored in a numeric(12, 2) column. */
export const Cents = defineType<number, string>({
  name: "cents",
  db: { kind: "decimal" },
  cast(input) {
    const cents = typeof input === "string" || typeof input === "number" ? toCents(String(input)) : undefined;
    return cents === undefined
      ? { success: false, message: "is not an amount" }
      : { success: true, data: cents };
  },
  fromDb: (stored) => toCents(stored) ?? Number.NaN, // "19.99" -> 1999
  toDb: (cents) =>
    `${cents < 0 ? "-" : ""}${Math.trunc(Math.abs(cents) / 100)}.${String(Math.abs(cents) % 100).padStart(2, "0")}`,
});
  • cast accepts what forms and JSON send ("19.99", 19.99) and refuses anything with more than two decimals, instead of rounding.
  • fromDb and toDb convert between the driver's decimal string and cents, digit by digit.
  • equal is left out: cents are numbers, and Object.is compares them.

Using it ​

ts
class Product extends Schema("products", {
  id: t.integer().primaryKey().dbGenerated(),
  name: t.string(),
  price: t.type(Cents),
}) {
  static changeset(product: Product | typeof Product, params: unknown) {
    return cast(product, params, ["name", "price"]).validateRequired(["name", "price"]);
  }

  get displayPrice(): string {
    return new Intl.NumberFormat("en-US", { style: "currency", currency: "USD" }).format(this.price / 100);
  }
}

The examples below start from these rows, after creating the tables:

ts
await createTables();
ts
const pen = await repo.insertOrThrow(Product.changeset(Product, { name: "Pen", price: "19.99" }));
pen.price; // => 1999
pen.displayPrice; // => "$19.99"

Product.changeset(Product, { name: "Pen", price: "19.999" }).errorMessages(); // => { price: ["is not an amount"] }

The database does the arithmetic on the column, and the result is read back with the type:

ts
const total = await repo.one(from(Product, "p").select(({ p }) => sum(p.price))); // summed by the database

Good to know

  • A number of cents is exact up to Number.MAX_SAFE_INTEGER (about 90 trillion dollars). For larger amounts, or for exchange rates, use t.decimal() and a decimal library.
  • Store the currency next to the amount when there is more than one. A parameterized type can carry it as an option.
  • On SQLite a decimal is stored as text and compares as text; integer cents in an integer column sort correctly everywhere.

See also ​