Money in Postgres

Once you have a Money object in your code, the next challenge is storing it in a database without losing its structure. This guide compares common storage methods and lands on Postgres custom composite types as the superior choice.

This approach allows you to keep your amount and currency bundled together at the database level, ensuring data integrity while using Drizzle ORM to automatically map those database records into clean, type-safe TypeScript objects.

At Numeric, we've been designing how our code interacts with money using Martin Fowler's money pattern. As part of this effort, we want to use an integer, lowest denomination representation of money in both our application code, as well as our database.

In our previous post, we discussed modeling money using BigInt in JavaScript. In Postgres, we have bigint which isn't exactly the same as JavaScript's BigInt but what matters is that any valid Postgres bigint can be deserialized into a JavaScript BigInt. We could handle this bridge ourselves in application code but it's much simpler to leverage an existing ORM or query builder such as drizzle-orm. While it has "ORM" in its name, Drizzle is just a query builder that provides a thin, type safe layer above SQL. This helps us catch errors without abstracting away the most powerful features of SQL.

When storing money, we have two components, the currency code and the amount. Traditionally, we would store these two values in two separate columns. This is fine, but when interacting with a money object that has amount and currency bundled together, it requires extra effort from the developer to break down the JavaScript fields into the Postgres columns. For example, let's try inserting a transaction record using drizzle-orm.

Then when we read our data back from the database, we have to reconstruct our money type.

This creates extra friction whenever querying the database or inserting records. When changing developer workflow across a large team, we want to make the transition as frictionless as possible in order to drive adoption. We should increase velocity, not slow ourselves down. Luckily, we can have drizzle handle serialization on our Money object using custom types. Unfortunately, we run into the limitation that custom types cannot span across two separate columns; we would need to put both amount and currency in the same column. Traditionally, SQL doesn't allow you to do this, but with Postgres, we can use JSONB to combine both fields together.

This is fine, but we lose some restrictions that we had before. We can no longer validate that the amount is an integer and we can no longer validate that the currency has a length of three. In addition, the query syntax for JSONB deviates from regular SQL syntax. For example, if I wanted to sum all my transactions, our original two-column approach would be easier to understand.

But what if we could have database-level validation and an approachable SQL syntax at the same time? Maybe we can use Postgres custom types! Using Postgres custom types, we can enforce that the amount is a bigint and our currency code as being three characters.

When summing the money_with_currency column, the SQL query looks pretty similar to our two-column approach.

In addition, a custom type defines the type for just one column, so it maps neatly to Drizzle's custom type. We can turn our JavaScript Money object into a composite type string that Postgres expects. We can also parse back Postgres' custom type string into our JavaScript Money object.

When using this custom type in our application code, drizzle will handle converting our Postgres custom type into our JavaScript Money object.

With our Postgres custom money_with_currency type, we get database-level validation, a simple, familiar SQL query syntax, and we get clean serialization and deserialization of our JavaScript Money object when interacting with the database.

Custom types are not supported with Drizzle’s default query helpers like eq and inArray, so we can create helpers to access our amount and currency fields.

And we can continue extending this idea to create other complex SQL string builders. For example, we could create a custom moneyEq helper that can check equivalency on both amount and currency.

This is a simple example where we can only compare a column against a JavaScript Money object, but you can extend the query helper to also be compatible with checking equivalency between money_with_currency columns on different tables.

Providing a set of well-tested query helpers that are equivalent to Drizzle’s query helpers can reduce errors from hand-writing SQL template strings and also take care of SQL edge cases that downstream developers may forget to consider.

In conclusion, using a Postgres custom type allows us to cleanly map our JavaScript money representation into the database. In comparison with our two-column approach, we remove the need to manually map queried data into our JavaScript money object. When comparing to our JSONB approach, we get database-level data validation and a simpler query syntax. Using our money_with_currency type provides a clean interface when moving between JavaScript and Postgres.