API with NestJS #147. The data types to store money with PostgreSQL and Prisma

March 4, 2024

This entry is part 147 of 177 in the API with NestJS

Managing money is a part of developing web applications that we can’t cut corners on. This article explores various data types built into PostgreSQL and Prisma that we could use to handle finances. We also discuss which ones to avoid and why.

Why we should avoid floating point numbers

We are used to using fractions when dealing with money – for example, $15.20 means fifteen dollars and twenty cents.

Therefore, we might think of using floating-point types to store the monetary values. Using floating-point numbers is a very popular way to represent numbers that include a fraction.

schema.prisma

model User {

id Int @id @default(autoincrement())

email String @unique

name String

password String

bankAccounts BankAccount[]

}

model BankAccount {

id Int @id @default(autoincrement())

balance Float

owner User @relation(fields: [ownerId], references: [id])

ownerId Int

}

Unfortunately, using floating-point numbers can cause a wide variety of errors. Let’s run the following SQL query:

1	SELECT 0.1::float + 0.2::float

By using ::float we are telling PostgreSQL to treat those values as floating-point numbers.

Unfortunately, the result of our query is not 0.3.

Binary representation of integer numbers

To understand why that’s the case, we must dive into how a computer represents numbers.

It is very straightforward for a computer to store an integer. It does that by dividing the number into a combination of smaller numbers, each one a power of 2. For example, 53 = 1 + 4 + 16 + 32. Under the hood, the computers use binary numbers that use only two digits – 0 and 1. Each digit in a binary number represents a power of 2. The rightmost digit has a value of 2⁰, the next digit to the left represents 2¹, and so on.

The number 53 can be represented by adding numbers 1, 4, 16, and 32. We use the number 1 to signify which powers of 2 we need and 0 to say which ones we don’t. This way, we can store any integer number we want.

The above system is slightly more complicated if we want to store negative numbers as well, but is built around the same core concept.

Floating-point numbers

The floating-point system uses a similar system to represent fractions of a number. Let’s use the number 53.625 as an example. We could store the 0.625 number as a sum of fractions that are a power of ½. When we do that, 0.625 becomes ½ + ⅛.

The floating point system performs additional conversions on the above number to determine the so-called exponent and the mantissa. Thanks to that, the floating-point numbers can represent a vast range of values from from very small fractions to very large numbers.

Unfortunately, not every fraction can be stored as a sum of numbers that are a power of ½. For example, there is no binary fraction that equals 0.1. Instead, the floating-point system would create an approximation. When we convert 0.0001100110011001100110011 in binary into decimal, we get 0.09999999403953552246, which is almost 0.1. The approximation is more precise if we use more bits.

This approximation is good enough for a lot of purposes such as video games graphics.

The rounding errors

Both 0.1 and 0.2 can only be stored as approximations when using the floating-point numbers system. When we try to add them up, we experience a rounding error:

both 0.1 and 0.2 are stored as their closest binary approximations in the floating-point system,
when we add those approximations together, we get a number that’s close to 0.3 but also an approximation.

Because of the characteristics of the floating-point numbers, we can’t use them to store money. Even minor rounding errors would disrupt a banking system, for example.

Storing money as integers

A valid solution to storing money is to use integers instead of floats.

schema.prisma

model BankAccount {

id Int @id @default(autoincrement())

balance Int

owner User @relation(fields: [ownerId], references: [id])

ownerId Int

}

The crucial thing about this approach is that we need to represent all money values in the smallest currency unit, such as cents. Instead of storing $15.20, we would store 1520 cents. When performing various calculations, we need to remember that all values are in the smallest unit.

After creating the migration, we can see the following SQL:

migration.sql

-- CreateTable

CREATE TABLE "BankAccount" (

"id" SERIAL NOT NULL,

"balance" INTEGER NOT NULL,

"ownerId" INTEGER NOT NULL,

CONSTRAINT "BankAccount_pkey" PRIMARY KEY ("id")

);

-- AddForeignKey

ALTER TABLE "BankAccount" ADD CONSTRAINT "BankAccount_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

We can see that Prisma generated a migration that uses the INTEGER data type. It can store numbers from -2,147,483,648 to 2,147,483,647.

If we want to store bigger numbers, we can use the BIGINT data type instead. To do that with Prisma, we must adjust our model a little.

schema.prisma

model BankAccount {

id Int @id @default(autoincrement())

balance BigInt

owner User @relation(fields: [ownerId], references: [id])

ownerId Int

}

Thanks to using the BIGINT type, we can store values from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. Over nine quintillion should be more than enough for most applications.

The numeric data type

Another possible solution is to use the numeric type. It allows us to store numbers with fractions, but we must specify how many digits we want to use. Using a total of nineteen digits and reserving four after the decimal point is a popular choice.

schema.prisma

model BankAccount {

id Int @id @default(autoincrement())

balance Decimal @db.Decimal(19, 4)

owner User @relation(fields: [ownerId], references: [id])

ownerId Int

}

Most currencies only require two digits to represent cents or the smallest unit, such as cents. However, in financial applications, especially those dealing with interest rates, loans, investments, and similar financial products, calculations can result in fractional cents.

Unfortunately, Prisma does not handle a situation where the user puts a number that’s too big very well. To support it, we need to handle the PrismaClientUnknownRequestError.

On the other hand, if we provide a number with too many decimals after the decimal point, they are ignored.

bank-accounts.service.ts

import { BadRequestException, Injectable } from '@nestjs/common';

import { PrismaService } from '../database/prisma.service';

import { CreateBankAccountDto } from './dto/create-bank-account.dto';

import { Prisma } from '@prisma/client';

@Injectable()

export class BankAccountsService {

constructor(private readonly prismaService: PrismaService) {}

// ...

async create(ownerId: number, bankAccountData: CreateBankAccountDto) {

try {

return await this.prismaService.bankAccount.create({

data: {

ownerId,

balance: bankAccountData.balance,

});

} catch (error) {

if (

error instanceof Prisma.PrismaClientUnknownRequestError &&

error.message.includes('22003')

) {

throw new BadRequestException('The provided balance is too big');

}

throw error;

}

Under the hood, Prisma uses the decimal.js library. Check out the official documentation if you want to know more.

1 2	const bankAccount = await this.bankAccountsService.getById(1); console.log(bankAccount.balance instanceof Prisma.Decimal); // true

The Money data type

Another type that can store money is the MONEY data type. While it might seem appropriate due to its name, it is heavily discouraged.

schema.prisma

model BankAccount {

id Int @id @default(autoincrement())

balance Decimal @db.Money

owner User @relation(fields: [ownerId], references: [id])

ownerId Int

}

The data formatting and decimal precision depend on the database’s locale settings. If we move our data across databases with different locales, the representation and behavior of monetary values can change.

Summary

In this article, we’ve covered various data types we can use with PostgreSQL and Prisma to store monetary values. To explain why we shouldn’t use floating-point numbers, we had to understand their binary format. As an alternative, we learned how to use integers and the numeric data type to handle money. We also found out about the MONEY data type and why it is discouraged. This helps us understand how to store money in databases safely and why some data types are better than others for keeping our numbers accurate.

Series Navigation<< API with NestJS #146. Polymorphic associations with PostgreSQL and PrismaAPI with NestJS #148. Understanding the injection scopes >>