API with NestJS #173. Storing money with Drizzle ORM and PostgreSQL

November 4, 2024

This entry is part 173 of 173 in the API with NestJS

We can’t cut corners on certain aspects of web development, and storing monetary values is one of them. In this article, we explore various data types in PostgreSQL we could use and discuss which ones to avoid.

The drawbacks of floating point numbers

When thinking about money, we often have to deal with fractions – for example, $10.50 means ten dollars and fifty cents.

A common way that computers represent fractions is by using the floating-point format.

database-schema.ts

import { serial, pgTable, doublePrecision } from 'drizzle-orm/pg-core';

export const bankAccounts = pgTable('bank_accounts', {

id: serial('id').primaryKey(),

balance: doublePrecision('balance').notNull(),

});

export const databaseSchema = {

bankAccounts,

};

In PostgreSQL, doublePrecision is one of the available data types that use the floating-point representation.

Unfortunately, floating-point numbers can cause many issues when storing monetary values. Let’s run a simple SQL query to find out more.

1	SELECT 0.1::float + 0.2::float

In PostgreSQL, float is an alias for the double precision data type. By using ::float we’re forcing PostgreSQL to to treat our values as floating-point numbers.

Unfortunately, our query does not return 0.3.

Representing integer numbers in binary

To understand why the above happens, we must learn how the computer represents numbers in the binary format. Storing an integer in binary is relatively straightforward. The computer divides the number into a combination of smaller numbers, each one a power of 2.

Fort example, 53 = 1 + 4 + 16 + 32. The binary numbers use only two digits – zero and one. Every digit in the binary number represents a power of 2. Starting from the right, the first digit has a value of 2⁰, the next represents 2¹, then 2², and so forth.

We use 1 to say which powers of 2 we want to add to our number and 0 to signify which ones we don’t. With this approach, we can store any integer number.

The above system gets a bit more complex when storing negative numbers, but is built around the same concept.

Floating point numbers in binary

With the floating-point system, we use a similar approach to representing fractions. For instance, consider the number 53.625. To represent 0.625, we break it down into a sum of fractions, each being a power of ½. This gives us 0.625 as ½ + ⅛.

The floating point system applies further conversions to the number to determine the exponent and mantissa. This allows floating-point numbers to represent a broad range of values, from very small fractions to extremely large numbers.

What’s crucial to notice is that we can’t represent every fraction as a sum of numbers that are a power of ½. For instance, there is no binary fraction that can represent 0.1. The best the floating-point system can do is to create an approximation, such as 0.0001100110011001100110011. Converting it into a decimal number, we get 0.09999999403953552246, which is almost 0.1. While an approximation like that is suitable for some purposes, such as video game graphics, we need to be precise when storing monetary values.

Rounding errors

We can store 0.1 and 0.2 as approximations. However, when we try to add them up, we experience a rounding error.

The floating point system stores the above numbers in approximations as close to the original numbers as possible. When we add them together, we get a number that’s close to 0.3 but also an approximation. Even small rounding errors would disrupt a banking system.

Storing monetary values as integers

To avoid approximations and rounding errors, we can store monetary values as integers.

database-schema.ts

import { serial, pgTable, integer } from 'drizzle-orm/pg-core';

export const bankAccounts = pgTable('bank_accounts', {

id: serial('id').primaryKey(),

balance: integer('balance').notNull(),

});

export const databaseSchema = {

bankAccounts,

};

However, we must represent the money in the smallest currency unit, such as cents. For example, instead of storing $20.40, we store 2040 cents.

The integer data type stores numbers from -2,147,483,648 to 2,147,483,647. If we want more, we should use the bigint data type instead.

database-schema.ts

import { serial, pgTable, bigint } from 'drizzle-orm/pg-core';

export const bankAccounts = pgTable('bank_accounts', {

id: serial('id').primaryKey(),

balance: bigint('balance', {

mode: 'bigint',

}).notNull(),

});

export const databaseSchema = {

bankAccounts,

};

When we set the mode parameter to bigint, the Drizzle ORM uses the BigInt type built into JavaScript.

With the bigint type, we can store numbers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (over nine quintillion).

The numeric data type

Alternatively, we can use the numeric data type that allows us to store numbers with fractions. However, we must specify how many digits we want to use – using a total of nineteen digits with four after the decimal point is a common solution.

database-schema.ts

import { serial, pgTable, numeric } from 'drizzle-orm/pg-core';

export const bankAccounts = pgTable('bank_accounts', {

id: serial('id').primaryKey(),

balance: numeric('balance', { precision: 19, scale: 4 }).notNull(),

});

export const databaseSchema = {

bankAccounts,

};

In PostgreSQL, the precision is the total number of digits in the numeric number, and the scale is the count of the digits in the fractional part.

When we fetch the data that uses the numeric type, Drizzle ORM represents the numbers as strings to maintain the precision. If we want to do some operations on them, we can use the decimal.js library.

The money data type

PostgreSQL also supports the MONEY data type. However, it’s heavily discouraged, and the Drizzle ORM does not support it. Its formatting and decimal precision depend on the locale settings of our database. If we migrate our data across databases with different locales, the representation and the behavior of the values stored using this type can change.

Summary

In this article, we learned how to store the monetary values using PostgreSQL and the Drizzle ORM. To understand why we shouldn’t use the floating-point data types, we had to learn about how they are stored in the binary format. We also discussed the MONEY data type built into PostgreSQL and why it is discouraged. As an alternative, we looked into more reliable options, such as using integers or the numeric data type. With them, we can store the monetary values safely without worrying about approximations and rounding errors.

Series Navigation<< API with NestJS #172. Database normalization with Drizzle ORM and PostgreSQL